예제 #1
0
 public ExcelDatabaseCriteria(ExcelDataProvider dataProvider, string range)
 {
     _dataProvider = dataProvider;
     var address = new ExcelAddressBase(range);
     _fromCol = address._fromCol;
     _toCol = address._toCol;
     _worksheet = address.WorkSheet;
     _fieldRow = address._fromRow;
     Initialize();
 }
예제 #2
0
 public RangeInfo(ExcelWorksheet ws, int fromRow, int fromCol, int toRow, int toCol)
 {
     _ws = ws;
     _fromRow = fromRow;
     _fromCol = fromCol;
     _toRow = toRow;
     _toCol = toCol;
     _address = new ExcelAddressBase(_fromRow, _fromCol, _toRow, _toCol);
     _address._ws = ws.Name;
     _values = new CellsStoreEnumerator<object>(ws._values, _fromRow, _fromCol, _toRow, _toCol);
     _cell = new CellInfo(_ws, _values);
 }
예제 #3
0
        /// <summary>
        /// 渲染多个单元格(一列数据)
        /// </summary>
        /// <param name="target"></param>
        /// <param name="parameters"></param>
        /// <param name="sheet"></param>
        /// <param name="insertRows"></param>
        /// <param name="tableKey"></param>
        /// <param name="rowCount"></param>
        /// <param name="cellString"></param>
        /// <param name="address"></param>
        private void RenderCells(Interpreter target, Parameter[] parameters, ExcelWorksheet sheet, int insertRows, string tableKey, int rowCount, string cellString, ExcelAddressBase address)
        {
            var dataVar = "\" + data." + tableKey + "[index].";

            //渲染一列单元格
            for (var i = 0; i < rowCount; i++)
            {
                var rowIndex      = address.Start.Row + i + insertRows;
                var targetAddress = new ExcelAddress(rowIndex, address.Start.Column, rowIndex, address.Start.Column);
                RenderCell(target, sheet, cellString, targetAddress.Address, dataVar, null, parameters, i);
            }
        }
 private static ExcelAddressBase DeleteSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift)
 {
     if (address.Addresses == null)
     {
         return(DeleteSplitIndividualAddress(address, range, effectedAddress, shift));
     }
     else
     {
         var newAddress = "";
         foreach (var a in address.Addresses)
         {
             var na = DeleteSplitIndividualAddress(a, range, effectedAddress, shift);
             if (na != null)
             {
                 newAddress += na.Address + ",";
             }
         }
         if (string.IsNullOrEmpty(newAddress))
         {
             return(null);
         }
         else
         {
             return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
         }
     }
 }
예제 #5
0
        internal ExcelTable(PackageRelationship rel, ExcelWorksheet sheet) : 
            base(sheet.NameSpaceManager)
        {
            WorkSheet = sheet;
            TableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
            RelationshipID = rel.Id;
            var pck = sheet._package.Package;
            Part=pck.GetPart(TableUri);

            TableXml = new XmlDocument();
            LoadXmlSafe(TableXml, Part.GetStream());
            init();
            Address = new ExcelAddressBase(GetXmlNodeString("@ref"));
        }
예제 #6
0
        private static void AdjustFormulasInsert(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
        {
            //Adjust formulas
            foreach (var ws in range._workbook.Worksheets)
            {
                var workSheetName = range.Worksheet.Name;
                var rowFrom       = range._fromRow;
                var columnFrom    = range._fromCol;
                var rows          = range.Rows;

                foreach (var f in ws._sharedFormulas.Values)
                {
                    if (workSheetName == ws.Name)
                    {
                        var a = new ExcelAddressBase(f.Address);
                        var c = effectedAddress.Collide(a);
                        if (c == ExcelAddressBase.eAddressCollition.Partly)
                        {
                            throw new Exception("Invalid shared formula"); //This should never happend!
                        }
                        if (f.StartCol >= columnFrom && c != ExcelAddressBase.eAddressCollition.No)
                        {
                            if (f.StartRow >= rowFrom)
                            {
                                f.StartRow += rows;
                            }
                            if (a._fromRow >= rowFrom)
                            {
                                a._fromRow += rows;
                                a._toRow   += rows;
                            }
                            else if (a._toRow >= rowFrom)
                            {
                                a._toRow += rows;
                            }
                            f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
                            f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
                        }
                    }
                    else if (f.Formula.Contains(workSheetName))
                    {
                        f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName);
                    }
                }

                var cse = new CellStoreEnumerator <object>(ws._formulas);
                while (cse.Next())
                {
                    if (cse.Value is string v)
                    {
                        if (workSheetName == ws.Name)
                        {
                            cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
                        }
                        else if (v.Contains(workSheetName))
                        {
                            cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName);
                        }
                    }
                }
            }
        }
        private static void DeleteConditionalFormatting(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress)
        {
            //Update Conditional formatting references
            var deletedCF = new List <IExcelConditionalFormattingRule>();

            foreach (var cf in ws.ConditionalFormatting)
            {
                var address = DeleteSplitAddress(cf.Address, range, effectedAddress, shift);
                if (address == null)
                {
                    deletedCF.Add(cf);
                }
                else
                {
                    ((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(address.Address);
                }
            }
            deletedCF.ForEach(cf => ws.ConditionalFormatting.Remove(cf));
        }
예제 #8
0
        private static void InsertSparkLinesAddress(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
        {
            foreach (var slg in range.Worksheet.SparklineGroups)
            {
                if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside)
                {
                    string address;
                    if (shift == eShiftTypeInsert.Down)
                    {
                        address = slg.DateAxisRange.AddRow(range._fromRow, range.Rows).Address;
                    }
                    else
                    {
                        address = slg.DateAxisRange.AddColumn(range._fromCol, range.Columns).Address;
                    }
                    slg.DateAxisRange = range.Worksheet.Cells[address];
                }

                foreach (var sl in slg.Sparklines)
                {
                    if (shift == eShiftTypeInsert.Down)
                    {
                        if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside ||
                            range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow))
                        {
                            sl.RangeAddress = sl.RangeAddress.AddRow(range._fromRow, range.Rows);
                        }

                        if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol)
                        {
                            sl.Cell = new ExcelCellAddress(sl.Cell.Row + range.Rows, sl.Cell.Column);
                        }
                    }
                    else
                    {
                        if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside ||
                            range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol))
                        {
                            sl.RangeAddress = sl.RangeAddress.AddColumn(range._fromCol, range.Columns);
                        }

                        if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow)
                        {
                            sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column + range.Columns);
                        }
                    }
                }
            }
        }
예제 #9
0
        private static void InsertPivottableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
        {
            foreach (var ptbl in ws.PivotTables)
            {
                if (shift == eShiftTypeInsert.Down)
                {
                    if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol)
                    {
                        ptbl.Address = ptbl.Address.AddRow(range._fromRow, range.Rows);
                    }
                }
                else
                {
                    if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow)
                    {
                        ptbl.Address = ptbl.Address.AddColumn(range._fromCol, range.Columns);
                    }
                }

                if (ptbl.CacheDefinition.SourceRange.Worksheet == ws)
                {
                    var address = ptbl.CacheDefinition.SourceRange;
                    if (shift == eShiftTypeInsert.Down)
                    {
                        if (address._fromCol >= range._fromCol && address._toCol <= range._toCol)
                        {
                            ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddRow(range._fromRow, range.Rows).Address];
                        }
                    }
                    else
                    {
                        if (address._fromRow >= range._fromRow && address._toRow <= range._toRow)
                        {
                            ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddColumn(range._fromCol, range.Columns).Address];
                        }
                    }
                }
            }
        }
예제 #10
0
        internal ExcelPivotTable(Packaging.ZipPackageRelationship rel, ExcelWorksheet sheet)
            : base(sheet.NameSpaceManager)
        {
            WorkSheet = sheet;
            PivotTableUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
            Relationship = rel;
            var pck = sheet._package.Package;
            Part=pck.GetPart(PivotTableUri);

            PivotTableXml = new XmlDocument();
            LoadXmlSafe(PivotTableXml, Part.GetStream());
            init();
            TopNode = PivotTableXml.DocumentElement;
            Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));

            _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);
            LoadFields();

            //Add row fields.
            foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager))
            {
                int x;
                if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0)
                {
                    RowFields.AddInternal(Fields[x]);
                }
                else
                {
                    rowElem.ParentNode.RemoveChild(rowElem);
                }
            }

            ////Add column fields.
            foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager))
            {
                int x;
                if(int.TryParse(colElem.GetAttribute("x"),out x) && x >= 0)
                {
                    ColumnFields.AddInternal(Fields[x]);
                }
                else
                {
                    colElem.ParentNode.RemoveChild(colElem);
                }
            }

            //Add Page elements
            //int index = 0;
            foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager))
            {
                int fld;
                if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0)
                {
                    var field = Fields[fld];
                    field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld);
                    PageFields.AddInternal(field);
                }
            }

            //Add data elements
            //index = 0;
            foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager))
            {
                int fld;
                if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0)
                {
                    var field = Fields[fld];
                    var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field);
                    DataFields.AddInternal(dataField);
                }
            }
        }
예제 #11
0
        private static void DeleteTableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
        {
            var deletedTbl = new List <ExcelTable>();

            foreach (var tbl in ws.Tables)
            {
                if (shift == eShiftTypeDelete.Up)
                {
                    if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol)
                    {
                        tbl.Address = tbl.Address.DeleteRow(range._fromRow, range.Rows);
                    }
                }
                else
                {
                    if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow)
                    {
                        tbl.Address = tbl.Address.DeleteColumn(range._fromCol, range.Columns);
                    }
                }
                if (tbl.Address == null)
                {
                    deletedTbl.Add(tbl);
                }
            }

            deletedTbl.ForEach(x => ws.Tables.Delete(x));
        }
예제 #12
0
        private void SetStyleAddress(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
        {
            if (address.Start.Column == 0 || address.Start.Row == 0)
            {
                throw new Exception("error address");
            }
            //Columns

            if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                int         col = address.Start.Column, row = 0;
                bool        isNew;
                //Get the startcolumn
                object o = null;
                if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
                {
                    column = ws.Column(address.Start.Column);
                    isNew  = true;
                }
                else
                {
                    //column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column);
                    column = (ExcelColumn)o;
                    isNew  = false;
                }

                var prevColumnMax = column.ColumnMax;
                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMin > prevColumnMax + 1)
                    {
                        ExcelColumn newColumn = ws.Column(prevColumnMax + 1);
                        newColumn.ColumnMax = column.ColumnMin - 1;
                        AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                    }

                    if (column.ColumnMax > address.End.Column)
                    {
                        ExcelColumn newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }

                    var s = ws.GetStyleInner(0, column.ColumnMin);
                    AddNewStyleColumn(sender, e, ws, styleCashe, column, s);

                    //index++;
                    prevColumnMax = column.ColumnMax;
                    if (!ws._values.NextCell(ref row, ref col) || row > 0)
                    {
                        if (column._columnMax == address.End.Column)
                        {
                            break;
                        }

                        if (isNew)
                        {
                            column._columnMax = address.End.Column;
                        }
                        else
                        {
                            ExcelColumn newColumn = ws.Column(column._columnMax + 1);
                            newColumn.ColumnMax = address.End.Column;
                            AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                            column = newColumn;
                        }

                        break;
                    }

                    column = ws.GetValueInner(0, col) as ExcelColumn;
                }

                if (column._columnMax < address.End.Column)
                {
                    ExcelColumn newCol = ws.Column(column._columnMax + 1);
                    newCol._columnMax = address.End.Column;

                    var s = ws.GetStyleInner(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyleInner(0, column.ColumnMin, newId);
                    }

                    column._columnMax = address.End.Column;
                }

                //Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
                var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
                while (cse.Next())
                {
                    if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column && cse.Value._styleId != 0)
                    {
                        if (styleCashe.ContainsKey(cse.Value._styleId))
                        {
                            ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cse.Value._styleId];
                            var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cse.Value._styleId, newId);
                            ws.SetStyleInner(cse.Row, cse.Column, newId);
                        }
                    }
                }

                if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
                {
                    //Update cells with styled columns
                    cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0);
                    while (cse.Next())
                    {
                        if (cse.Value._styleId == 0)
                        {
                            continue;
                        }

                        for (var c = address._fromCol; c <= address._toCol; c++)
                        {
                            if (!ws.ExistsStyleInner(cse.Row, c))
                            {
                                if (styleCashe.ContainsKey(cse.Value._styleId))
                                {
                                    ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
                                }
                                else
                                {
                                    ExcelXfs st    = CellXfs[cse.Value._styleId];
                                    var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                    styleCashe.Add(cse.Value._styleId, newId);
                                    ws.SetStyleInner(cse.Row, c, newId);
                                }
                            }
                        }
                    }
                }
            }

            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (var rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    var s = ws.GetStyleInner(rowNum, 0);
                    if (s == 0)
                    {
                        //iterate all columns and set the row to the style of the last column
                        var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
                        while (cse.Next())
                        {
                            s = cse.Value._styleId;
                            if (s == 0)
                            {
                                continue;
                            }

                            var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
                            if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
                            {
                                for (var col = c.ColumnMin; col < c.ColumnMax; col++)
                                {
                                    if (!ws.ExistsStyleInner(rowNum, col))
                                    {
                                        ws.SetStyleInner(rowNum, col, s);
                                    }
                                }
                            }
                        }

                        ws.SetStyleInner(rowNum, 0, s);
                        cse.Dispose();
                    }

                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyleInner(rowNum, 0, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyleInner(rowNum, 0, newId);
                    }
                }

                //Update individual cells
                var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol);
                while (cse2.Next())
                {
                    var s = cse2.Value._styleId;
                    if (s == 0)
                    {
                        continue;
                    }

                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyleInner(cse2.Row, cse2.Column, newId);
                    }
                }

                //Update cells with styled rows
                cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol);
                while (cse2.Next())
                {
                    if (cse2.Value._styleId == 0)
                    {
                        continue;
                    }

                    for (var r = address._fromRow; r <= address._toRow; r++)
                    {
                        if (!ws.ExistsStyleInner(r, cse2.Column))
                        {
                            var s = cse2.Value._styleId;
                            if (styleCashe.ContainsKey(s))
                            {
                                ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
                            }
                            else
                            {
                                ExcelXfs st    = CellXfs[s];
                                var      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(s, newId);
                                ws.SetStyleInner(r, cse2.Column, newId);
                            }
                        }
                    }
                }
            }
            else //Cell range
            {
                var tmpCache = styleCashe;
                var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
                var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1);
                ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column,
                                                (list, index, row, column, args) =>
                {
                    // Optimized GetStyleID
                    var s = list[index]._styleId;
                    if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s))
                    {
                        // get row styleId with cache
                        if (!rowCache.ContainsKey(row))
                        {
                            rowCache.Add(row, ws._values.GetValue(row, 0)._styleId);
                        }

                        s = rowCache[row];
                        if (s == 0)
                        {
                            // get column styleId with cache
                            if (!colCache.ContainsKey(column))
                            {
                                colCache.Add(column, ws._values.GetValue(0, column));
                            }

                            s = colCache[column]._styleId;
                            if (s == 0)
                            {
                                int r = 0, c = column;
                                if (ws._values.PrevCell(ref r, ref c))
                                {
                                    //var val = ws._values.GetValue(0, c);
                                    if (!colCache.ContainsKey(c))
                                    {
                                        colCache.Add(c, ws._values.GetValue(0, c));
                                    }

                                    ExcelCoreValue val = colCache[c];
                                    var colObj         = (ExcelColumn)val._value;
                                    if (colObj != null && colObj.ColumnMax >= column)     //Fixes issue 15174
                                    {
                                        s = val._styleId;
                                    }
                                }
                            }
                        }
                    }

                    if (tmpCache.ContainsKey(s))
                    {
                        //ws.SetStyleInner(row, column, tmpCache[s]);
                        list[index] = new ExcelCoreValue {
                            _value = list[index]._value, _styleId = tmpCache[s]
                        };
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        var newId   = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        tmpCache.Add(s, newId);
                        //ws.SetStyleInner(row, column, newId);
                        list[index] = new ExcelCoreValue {
                            _value = list[index]._value, _styleId = newId
                        };
                    }
                }, e);
            }
        }
예제 #13
0
        /// <summary>
        /// 渲染表格单元格
        /// </summary>
        /// <param name="target"></param>
        /// <param name="tbParameters"></param>
        /// <param name="sheet"></param>
        /// <param name="insertRows"></param>
        /// <param name="tableKey"></param>
        /// <param name="rowCount"></param>
        /// <param name="writer"></param>
        /// <param name="address"></param>
        private void RenderTableCells(Interpreter target, Parameter[] tbParameters, ExcelWorksheet sheet, int insertRows, string tableKey, int rowCount, IWriter writer, ExcelAddressBase address)
        {
            var cellString = writer.CellString;

            if (cellString.Contains("{{Table>>"))
            {
                //{{ Table >> BookInfo | RowNo}}
                cellString = "{{" + cellString.Split('|')[1].Trim();
            }
            else if (cellString.Contains(">>Table}}"))
            {
                //{{Remark|>>Table}}
                cellString = cellString.Split('|')[0].Trim() + "}}";
            }

            RenderCells(target, tbParameters, sheet, insertRows, tableKey, rowCount, cellString, address);
        }
예제 #14
0
 public ExcelFileReader(IDictionary <int, ExcelFilePropertySetter> propertySetters, ExcelAddressBase columnHeaderRange, ExcelAddressBase dataRange, DataOrientation datasetOrientation)
 {
     PropertySetters    = propertySetters;
     ColumnHeaderRange  = columnHeaderRange;
     DataRange          = dataRange;
     DatasetOrientation = datasetOrientation;
 }
예제 #15
0
 /// <summary>
 ///     Checks whether given range empty or not
 /// </summary>
 /// <param name="address">Excel cell range</param>
 /// <param name="hasHeaderRow">'false' as default</param>
 /// <returns>'true' or 'false'</returns>
 public static bool IsEmptyRange(this ExcelAddressBase address, bool hasHeaderRow = false) => !hasHeaderRow ? address.Start.Row == 0 : address.Start.Row == address.End.Row;
예제 #16
0
        private static void CopyDrawing(ExcelPackage pck, XmlNamespaceManager nsm, ExcelWorksheet Copy, ExcelWorksheet added)
        {
            //First copy the drawing XML
            string       xml           = Copy.Drawings.DrawingXml.OuterXml;
            var          uriDraw       = new Uri(string.Format("/xl/drawings/drawing{0}.xml", added.SheetId), UriKind.Relative);
            var          part          = pck.ZipPackage.CreatePart(uriDraw, "application/vnd.openxmlformats-officedocument.drawing+xml", pck.Compression);
            StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));

            streamDrawing.Write(xml);
            streamDrawing.Flush();

            XmlDocument drawXml = new XmlDocument();

            drawXml.LoadXml(xml);
            //Add the relationship ID to the worksheet xml.
            var        drawRelation = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, uriDraw), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing");
            XmlElement e            = added.WorksheetXml.SelectSingleNode("//d:drawing", nsm) as XmlElement;

            e.SetAttribute("id", ExcelPackage.schemaRelationships, drawRelation.Id);
            for (int i = 0; i < Copy.Drawings.Count; i++)
            {
                ExcelDrawing draw = Copy.Drawings[i];
                //draw.AdjustPositionAndSize();       //Adjust position for any change in normal style font/row size etc.
                if (draw is ExcelChart chart)
                {
                    xml = chart.ChartXml.InnerXml;

                    var          UriChart    = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/charts/chart{0}.xml");
                    var          chartPart   = pck.ZipPackage.CreatePart(UriChart, ContentTypes.contentTypeChart, pck.Compression);
                    StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write));
                    streamChart.Write(xml);
                    streamChart.Flush();
                    //Now create the new relationship to the copied chart xml
                    var          prevRelID = draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value;
                    var          rel       = part.CreateRelationship(UriHelper.GetRelativeUri(uriDraw, UriChart), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart");
                    XmlAttribute relAtt    = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute;
                    relAtt.Value = rel.Id;
                }
                else if (draw is ExcelPicture pic)
                {
                    IPictureContainer container = pic;
                    var uri = container.UriPic;
                    var img = PictureStore.ImageToByteArray(pic.Image);
                    var ii  = added.Workbook._package.PictureStore.AddImage(img, null, pic.ContentType);

                    var rel = part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, ii.Uri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/image");
                    //Fixes problem with invalid image when the same image is used more than once.
                    XmlNode relAtt =
                        drawXml.SelectSingleNode(
                            string.Format(
                                "//xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name[.='{0}']/../../../xdr:blipFill/a:blip/@r:embed",
                                pic.Name), Copy.Drawings.NameSpaceManager);

                    if (relAtt != null)
                    {
                        relAtt.Value = rel.Id;
                    }
                }
                else if (draw is ExcelTableSlicer slicer)
                {
                    var name      = pck.Workbook.GetSlicerName(slicer.Name);
                    var newSlicer = added.Drawings[i] as ExcelTableSlicer;
                    newSlicer.Name       = name;
                    newSlicer.SlicerName = name;
                    //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer.
                    newSlicer.CreateNewCache();
                }
                else if (draw is ExcelPivotTableSlicer ptSlicer)
                {
                    var name      = pck.Workbook.GetSlicerName(ptSlicer.Name);
                    var newSlicer = added.Drawings[i] as ExcelPivotTableSlicer;
                    newSlicer.Name       = name;
                    newSlicer.SlicerName = name;
                    //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer.
                    newSlicer.CreateNewCache(ptSlicer.Cache._field);
                }
                else if (draw is ExcelControl ctrl)
                {
                    var          UriCtrl     = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/ctrlProps/ctrlProp{0}.xml");
                    var          ctrlPart    = pck.ZipPackage.CreatePart(UriCtrl, ContentTypes.contentTypeControlProperties, pck.Compression);
                    StreamWriter streamChart = new StreamWriter(ctrlPart.GetStream(FileMode.Create, FileAccess.Write));
                    streamChart.Write(ctrl.ControlPropertiesXml.OuterXml);
                    streamChart.Flush();

                    var          prevRelID = ctrl._control.RelationshipId;
                    var          rel       = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, UriCtrl), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/ctrlProp");
                    XmlAttribute relAtt    = added.WorksheetXml.SelectSingleNode(string.Format("//d:control/@r:id[.='{0}']", prevRelID), added.NameSpaceManager) as XmlAttribute;
                    relAtt.Value = rel.Id;
                }
            }
            //rewrite the drawing xml with the new relID's
            streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
            streamDrawing.Write(drawXml.OuterXml);
            streamDrawing.Flush();

            //Copy the size variables to the copy.
            for (int i = 0; i < Copy.Drawings.Count; i++)
            {
                var draw = Copy.Drawings[i];
                var c    = added.Drawings[i];
                if (c != null)
                {
                    c._left   = draw._left;
                    c._top    = draw._top;
                    c._height = draw._height;
                    c._width  = draw._width;
                }
                if (c is ExcelChart chart)
                {
                    for (int j = 0; i < chart.Series.Count; i++)
                    {
                        var s = chart.Series[j];
                        var a = new ExcelAddressBase(s.Series);
                        if (a.WorkSheetName.Equals(Copy.Name))
                        {
                            s.Series = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress);
                        }
                        a = new ExcelAddressBase(s.XSeries);
                        if (a.WorkSheetName.Equals(Copy.Name))
                        {
                            s.XSeries = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress);
                        }
                    }
                }
            }
        }
예제 #17
0
        private static void FixFormulasDelete(ExcelRangeBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift)
        {
            foreach (var ws in range.Worksheet.Workbook.Worksheets)
            {
                var workSheetName = range.WorkSheetName;
                var rowFrom       = range._fromRow;
                var rows          = range.Rows;

                var delSF = new List <int>();
                foreach (var sf in ws._sharedFormulas.Values)
                {
                    if (workSheetName == ws.Name)
                    {
                        if (effectedRange.Collide(new ExcelAddressBase(sf.Address)) != ExcelAddressBase.eAddressCollition.No)
                        {
                            ExcelAddressBase a;
                            if (shift == eShiftTypeDelete.Up)
                            {
                                a = new ExcelAddress(sf.Address).DeleteRow(range._fromRow, rows);
                            }
                            else
                            {
                                a = new ExcelAddress(sf.Address).DeleteColumn(range._fromRow, rows);
                            }

                            if (a == null)
                            {
                                delSF.Add(sf.Index);
                            }
                            else
                            {
                                sf.Address = a.Address;
                                sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, range, effectedRange, shift, ws.Name, workSheetName);
                                if (sf.StartRow >= rowFrom)
                                {
                                    var r = Math.Max(rowFrom, sf.StartRow - rows);
                                    sf.StartRow = r;
                                }
                            }
                        }
                    }
                    else if (sf.Formula.Contains(workSheetName))
                    {
                        sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -rows, 0, rowFrom, 0, ws.Name, workSheetName);
                    }
                }

                foreach (var ix in delSF)
                {
                    ws._sharedFormulas.Remove(ix);
                }
                var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
                while (cse.Next())
                {
                    if (cse.Value is string v)
                    {
                        if (workSheetName == ws.Name || v.IndexOf(workSheetName, StringComparison.CurrentCultureIgnoreCase) >= 0)
                        {
                            cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedRange, shift, ws.Name, workSheetName);
                        }
                    }
                }
            }
        }
예제 #18
0
        internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
        {
            ValidateColumn(columnFrom, columns);
            lock (ws)
            {
                AdjustColumnMinMaxDelete(ws, columnFrom, columns);
                var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
                WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);

                DeleteCellStores(ws, 0, columnFrom, 0, columns);

                foreach (var wsToUpdate in ws.Workbook.Worksheets)
                {
                    FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
                }

                WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);

                foreach (var tbl in ws.Tables)
                {
                    if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
                    {
                        var node = tbl.Columns[0].TopNode.ParentNode;
                        var ix   = columnFrom - tbl.Address.Start.Column;
                        for (int i = 0; i < columns; i++)
                        {
                            if (node.ChildNodes.Count > ix)
                            {
                                node.RemoveChild(node.ChildNodes[ix]);
                            }
                        }
                        tbl._cols = new ExcelTableColumnCollection(tbl);
                    }

                    tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
                }

                foreach (var ptbl in ws.PivotTables)
                {
                    if (ptbl.Address.Start.Column >= columnFrom + columns)
                    {
                        ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
                    }
                    if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
                    {
                        ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
                    }
                }

                var range           = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
                var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
                DeleteDataValidations(range, eShiftTypeDelete.Left, ws, effectedAddress);
                DeleteConditionalFormatting(range, eShiftTypeDelete.Left, ws, effectedAddress);

                DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
                DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);

                //Adjust drawing positions.
                WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
            }
        }
예제 #19
0
        public override INameInfo GetName(string worksheet, string name)
        {
            ExcelNamedRange nameItem;
            ulong           id;
            ExcelWorksheet  ws;

            if (string.IsNullOrEmpty(worksheet))
            {
                if (_package._workbook.Names.ContainsKey(name))
                {
                    nameItem = _package._workbook.Names[name];
                }
                else
                {
                    return(null);
                }
                ws = null;
            }
            else
            {
                ws = _package._workbook.Worksheets[worksheet];
                if (ws != null && ws.Names.ContainsKey(name))
                {
                    nameItem = ws.Names[name];
                }
                else if (_package._workbook.Names.ContainsKey(name))
                {
                    nameItem = _package._workbook.Names[name];
                }
                else
                {
                    return(null);
                }
            }
            id = ExcelAddressBase.GetCellID(nameItem.LocalSheetId, nameItem.Index, 0);

            if (_names.ContainsKey(id))
            {
                return(_names[id]);
            }
            else
            {
                var ni = new NameInfo()
                {
                    Id        = id,
                    Name      = name,
                    Worksheet = nameItem.Worksheet == null ? nameItem._ws : nameItem.Worksheet.Name,
                    Formula   = nameItem.Formula
                };
                if (nameItem._fromRow > 0)
                {
                    ni.Value = new RangeInfo(nameItem.Worksheet ?? ws, nameItem._fromRow, nameItem._fromCol, nameItem._toRow, nameItem._toCol);
                }
                else
                {
                    ni.Value = nameItem.Value;
                }
                _names.Add(id, ni);
                return(ni);
            }
        }
예제 #20
0
        /// <summary>
        /// This method follows the calculation chain to get the order of the calculation
        /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas).
        /// </summary>
        /// <param name="depChain">The dependency chain object</param>
        /// <param name="lexer">The formula tokenizer</param>
        /// <param name="wb">The workbook where the formula comes from</param>
        /// <param name="ws">The worksheet where the formula comes from</param>
        /// <param name="f">The cell function object</param>
        /// <param name="options">Calcultaiton options</param>
        private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)
        {
            Stack <FormulaCell> stack = new Stack <FormulaCell>();

iterateToken:
            while (f.tokenIx < f.Tokens.Count)
            {
                var t = f.Tokens[f.tokenIx];
                if (t.TokenType == TokenType.ExcelAddress)
                {
                    var adr = new ExcelFormulaAddress(t.Value);
                    if (adr.Table != null)
                    {
                        adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column));
                    }

                    if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)) != ExcelAddressBase.eAddressCollition.No && !options.AllowCirculareReferences)
                    {
                        throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column))));
                    }

                    if (adr._fromRow > 0 && adr._fromCol > 0)
                    {
                        if (string.IsNullOrEmpty(adr.WorkSheet))
                        {
                            if (f.ws == null)
                            {
                                f.ws = ws;
                            }
                            else if (f.ws.SheetID != f.SheetID)
                            {
                                f.ws = wb.Worksheets.GetBySheetID(f.SheetID);
                            }
                        }
                        else
                        {
                            f.ws = wb.Worksheets[adr.WorkSheet];
                        }

                        if (f.ws != null)
                        {
                            f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column);
                            goto iterateCells;
                        }
                    }
                }
                else if (t.TokenType == TokenType.NameValue)
                {
                    string          adrWb, adrWs, adrName;
                    ExcelNamedRange name;
                    ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws == null ? "" : f.ws.Name);
                    if (!string.IsNullOrEmpty(adrWs))
                    {
                        if (f.ws == null)
                        {
                            f.ws = wb.Worksheets[adrWs];
                        }
                        if (f.ws.Names.ContainsKey(t.Value))
                        {
                            name = f.ws.Names[adrName];
                        }
                        else if (wb.Names.ContainsKey(adrName))
                        {
                            name = wb.Names[adrName];
                        }
                        else
                        {
                            name = null;
                        }
                        if (name != null)
                        {
                            f.ws = name.Worksheet;
                        }
                    }
                    else if (wb.Names.ContainsKey(adrName))
                    {
                        name = wb.Names[t.Value];
                        if (string.IsNullOrEmpty(adrWs))
                        {
                            f.ws = name.Worksheet;
                        }
                    }
                    else
                    {
                        name = null;
                    }

                    if (name != null)
                    {
                        if (string.IsNullOrEmpty(name.NameFormula))
                        {
                            if (name.NameValue == null)
                            {
                                f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, name.Start.Row,
                                                                               name.Start.Column, name.End.Row, name.End.Column);
                                goto iterateCells;
                            }
                        }
                        else
                        {
                            var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0);

                            if (!depChain.index.ContainsKey(id))
                            {
                                var rf = new FormulaCell()
                                {
                                    SheetID = name.LocalSheetId, Row = name.Index, Column = 0
                                };
                                rf.Formula = name.NameFormula;
                                rf.Tokens  = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList();

                                depChain.Add(rf);
                                stack.Push(f);
                                f = rf;
                                goto iterateToken;
                            }
                            else
                            {
                                if (stack.Count > 0)
                                {
                                    //Check for circular references
                                    foreach (var par in stack)
                                    {
                                        if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id && !options.AllowCirculareReferences)
                                        {
                                            throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name)));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                f.tokenIx++;
            }
            depChain.CalcOrder.Add(f.Index);
            if (stack.Count > 0)
            {
                f = stack.Pop();
                goto iterateCells;
            }
            return;

iterateCells:

            while (f.iterator != null && f.iterator.Next())
            {
                var v = f.iterator.Value;
                if (v == null || v.ToString().Trim() == "")
                {
                    continue;
                }
                var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column);
                if (!depChain.index.ContainsKey(id))
                {
                    var rf = new FormulaCell()
                    {
                        SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column
                    };
                    if (f.iterator.Value is int)
                    {
                        rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name);
                    }
                    else
                    {
                        rf.Formula = v.ToString();
                    }
                    rf.ws     = f.ws;
                    rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList();
                    ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens);
                    depChain.Add(rf);
                    stack.Push(f);
                    f = rf;
                    goto iterateToken;
                }
                else
                {
                    if (stack.Count > 0)
                    {
                        //Check for circular references
                        foreach (var par in stack)
                        {
                            if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id)
                            {
                                if (options.AllowCirculareReferences == false)
                                {
                                    throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
                                }
                                else
                                {
                                    f = stack.Pop();
                                    goto iterateCells;
                                }
                            }
                        }
                    }
                }
            }
            f.tokenIx++;
            goto iterateToken;
        }
예제 #21
0
 private static void InsertDataValidation(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws)
 {
     //Update data validation references
     foreach (var dv in ws.DataValidations)
     {
         ((ExcelDataValidation)dv).SetAddress(InsertSplitAddress(dv.Address, range, effectedAddress, shift).Address);
     }
 }
 public RangeInfo(ExcelWorksheet ws, ExcelAddressBase address)
 {
     _ws = ws;
     _fromRow = address._fromRow;
     _fromCol = address._fromCol;
     _toRow = address._toRow;
     _toCol = address._toCol;
     _address = address;
     _address._ws = ws.Name;
     _values = new CellsStoreEnumerator<ExcelCoreValue>(ws._values, _fromRow, _fromCol, _toRow, _toCol);
     _cell = new CellInfo(_ws, _values);
 }
예제 #23
0
 private static ExcelAddressBase InsertSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift)
 {
     if (address.Addresses == null)
     {
         return(InsertSplitIndividualAddress(address, range, effectedAddress, shift));
     }
     else
     {
         var newAddress = "";
         foreach (var a in address.Addresses)
         {
             newAddress += InsertSplitIndividualAddress(a, range, effectedAddress, shift) + ",";
         }
         return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
     }
 }
예제 #24
0
        internal void ConvertRichtext()
        {
            if (_cells == null)
            {
                return;
            }
            var isRt = _cells.Worksheet._flags.GetFlagValue(_cells._fromRow, _cells._fromCol, CellFlags.RichText);

            if (Count == 1 && isRt == false)
            {
                _cells.Worksheet._flags.SetFlagValue(_cells._fromRow, _cells._fromCol, true, CellFlags.RichText);
                var s = _cells.Worksheet.GetStyleInner(_cells._fromRow, _cells._fromCol);
                //var fnt = cell.Style.Font;
                var fnt = _cells.Worksheet.Workbook.Styles.GetStyleObject(s, _cells.Worksheet.PositionID, ExcelAddressBase.GetAddress(_cells._fromRow, _cells._fromCol)).Font;
                this[0].PreserveSpace = true;
                this[0].Bold          = fnt.Bold;
                this[0].FontName      = fnt.Name;
                this[0].Italic        = fnt.Italic;
                this[0].Size          = fnt.Size;
                this[0].UnderLine     = fnt.UnderLine;

                int hex;
                if (fnt.Color.Rgb != "" && int.TryParse(fnt.Color.Rgb, NumberStyles.HexNumber, null, out hex))
                {
                    this[0].Color = Color.FromArgb(hex);
                }
            }
        }
예제 #25
0
 private static void InsertTableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress)
 {
     foreach (var tbl in ws.Tables)
     {
         if (shift == eShiftTypeInsert.Down)
         {
             if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol)
             {
                 tbl.Address = tbl.Address.AddRow(range._fromRow, range.Rows);
             }
         }
         else
         {
             if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow)
             {
                 tbl.Address = tbl.Address.AddColumn(range._fromCol, range.Columns);
             }
         }
     }
 }
예제 #26
0
        private ExcelSparklineGroup AddGroup(eSparklineType type, ExcelAddressBase locationRange, ExcelAddressBase dataRange, bool isRows)
        {
            var group = NewSparklineGroup();

            group.Type = type;
            var row = locationRange._fromRow;
            var col = locationRange._fromCol;

            var drFromRow = dataRange._fromRow;
            var drFromCol = dataRange._fromCol;
            var drToRow   = isRows ? dataRange._fromRow : dataRange._toRow;
            var drToCol   = isRows ? dataRange._toCol : dataRange._fromCol;

            var cells = (locationRange._fromRow == locationRange._toRow ? locationRange._toCol - locationRange._fromCol: locationRange._toRow - locationRange._fromRow) + 1;
            var cell  = 0;

            while (cell < cells)
            {
                var f     = new ExcelCellAddress(row, col);
                var sqref = new ExcelAddressBase(dataRange.WorkSheet, drFromRow, drFromCol, drToRow, drToCol);
                group.Sparklines.Add(f, dataRange.WorkSheet, sqref);
                cell++;
                if (locationRange._fromRow == locationRange._toRow)
                {
                    col++;
                }
                else
                {
                    row++;
                }
                if (isRows)
                {
                    drFromRow++;
                    drToRow++;
                }
                else
                {
                    drFromCol++;
                    drToCol++;
                }
            }

            group.ColorSeries.Rgb   = "FF376092";
            group.ColorNegative.Rgb = "FFD00000";
            group.ColorMarkers.Rgb  = "FFD00000";
            group.ColorAxis.Rgb     = "FF000000";
            group.ColorFirst.Rgb    = "FFD00000";
            group.ColorLast.Rgb     = "FFD00000";
            group.ColorHigh.Rgb     = "FFD00000";
            group.ColorLow.Rgb      = "FFD00000";
            _lst.Add(group);
            return(group);
        }
        private static void DeleteSparkLinesAddress(ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
        {
            var delSparklineGroups = new List <ExcelSparklineGroup>();

            foreach (var slg in range.Worksheet.SparklineGroups)
            {
                if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside)
                {
                    string address;
                    if (shift == eShiftTypeDelete.Up)
                    {
                        address = slg.DateAxisRange.DeleteRow(range._fromRow, range.Rows).Address;
                    }
                    else
                    {
                        address = slg.DateAxisRange.DeleteColumn(range._fromCol, range.Columns).Address;
                    }

                    slg.DateAxisRange = address == null? null : range.Worksheet.Cells[address];
                }

                var delSparklines = new List <ExcelSparkline>();
                foreach (var sl in slg.Sparklines)
                {
                    if (range.Collide(sl.Cell.Row, sl.Cell.Column) >= ExcelAddressBase.eAddressCollition.Inside)
                    {
                        delSparklines.Add(sl);
                    }
                    else if (shift == eShiftTypeDelete.Up)
                    {
                        if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside ||
                            range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow))
                        {
                            sl.RangeAddress = sl.RangeAddress.DeleteRow(range._fromRow, range.Rows);
                        }

                        if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol)
                        {
                            sl.Cell = new ExcelCellAddress(sl.Cell.Row - range.Rows, sl.Cell.Column);
                        }
                    }
                    else
                    {
                        if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside
                            ||
                            range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol))
                        {
                            sl.RangeAddress = sl.RangeAddress.DeleteColumn(range._fromCol, range.Columns);
                        }

                        if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow)
                        {
                            sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column - range.Columns);
                        }
                    }
                }

                delSparklines.ForEach(x => slg.Sparklines.Remove(x));
                if (slg.Sparklines.Count == 0)
                {
                    delSparklineGroups.Add(slg);
                }
            }
            delSparklineGroups.ForEach(x => range.Worksheet.SparklineGroups.Remove(x));
        }
예제 #28
0
 /// <summary>
 /// Adds a new sparklinegroup to the collection
 /// </summary>
 /// <param name="type">Type of sparkline</param>
 /// <param name="locationRange">The location of the sparkline group. The range must have one row or column and must match the number of rows/columns in the datarange</param>
 /// <param name="dataRange">The data for the sparkline group</param>
 /// <returns></returns>
 public ExcelSparklineGroup Add(eSparklineType type, ExcelAddressBase locationRange, ExcelAddressBase dataRange)
 {
     if (locationRange.Rows == 1)
     {
         if (locationRange.Columns == dataRange.Rows)
         {
             return(AddGroup(type, locationRange, dataRange, true));
         }
         else if (locationRange.Columns == dataRange.Columns)
         {
             return(AddGroup(type, locationRange, dataRange, false));
         }
         else
         {
             throw (new ArgumentException("dataRange is not valid. dataRange columns or rows must match number of rows in locationRange"));
         }
     }
     else if (locationRange.Columns == 1)
     {
         if (locationRange.Rows == dataRange.Columns)
         {
             return(AddGroup(type, locationRange, dataRange, false));
         }
         else if (locationRange.Rows == dataRange.Rows)
         {
             return(AddGroup(type, locationRange, dataRange, true));
         }
         else
         {
             throw (new ArgumentException("dataRange is not valid. dataRange columns or rows must match number of columns in locationRange"));
         }
     }
     else
     {
         throw (new ArgumentException("locationRange is not valid. Range must be one Column or Row only"));
     }
 }
        private static void DeleteDataValidations(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress)
        {
            //Update data validation references
            var deletedDV = new List <IExcelDataValidation>();

            foreach (var dv in ws.DataValidations)
            {
                var address = DeleteSplitAddress(dv.Address, range, effectedAddress, shift);
                if (address == null)
                {
                    deletedDV.Add(dv);
                }
                else
                {
                    ((ExcelDataValidation)dv).SetAddress(address.Address);
                }
            }
            deletedDV.ForEach(dv => ws.DataValidations.Remove(dv));
        }
예제 #30
0
        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheetName">will be used if no worksheet name is specified in <paramref name="address"/></param>
        /// <param name="address">address of a range</param>
        /// <returns></returns>
        public RangeAddress Create(string worksheetName, string address)
        {
            Require.That(address).Named("range").IsNotNullOrEmpty();
            //var addressInfo = ExcelAddressInfo.Parse(address);
            var adr = new ExcelAddressBase(address);  
            var sheet = string.IsNullOrEmpty(adr.WorkSheet) ? worksheetName : adr.WorkSheet;
            var dim = _excelDataProvider.GetDimensionEnd(adr.WorkSheet);
            var rangeAddress = new RangeAddress()
            {
                Address = adr.Address,
                Worksheet = sheet,
                FromRow = adr._fromRow,
                FromCol = adr._fromCol,
                ToRow = (dim != null && adr._toRow > dim.Row) ? dim.Row : adr._toRow,
                ToCol = adr._toCol
            };

            //if (addressInfo.IsMultipleCells)
            //{
            //    HandleMultipleCellAddress(rangeAddress, addressInfo);
            //}
            //else
            //{
            //    HandleSingleCellAddress(rangeAddress, addressInfo);
            //}
            return rangeAddress;
        }
        private static ExcelAddressBase DeleteSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift)
        {
            if (address.CollideFullRowOrColumn(range))
            {
                if (range.CollideFullColumn(address._fromCol, address._toCol))
                {
                    return(address.DeleteColumn(range._fromCol, range.Columns));
                }
                else
                {
                    return(address.DeleteRow(range._fromRow, range.Rows));
                }
            }
            else
            {
                var collide = effectedAddress.Collide(address);
                if (collide == ExcelAddressBase.eAddressCollition.Partly)
                {
                    var addressToShift = effectedAddress.Intersect(address);
                    var shiftedAddress = ShiftAddress(addressToShift, range, shift);
                    var newAddress     = "";
                    if (address._fromRow < addressToShift._fromRow)
                    {
                        newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ",";
                    }
                    if (address._fromCol < addressToShift._fromCol)
                    {
                        var fromRow = Math.Max(address._fromRow, addressToShift._fromRow);
                        newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ",";
                    }

                    if (shiftedAddress != null)
                    {
                        newAddress += $"{shiftedAddress.Address},";
                    }

                    if (address._toRow > addressToShift._toRow)
                    {
                        newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ",";
                    }
                    if (address._toCol > addressToShift._toCol)
                    {
                        newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ",";
                    }
                    return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
                }
                else if (collide != ExcelAddressBase.eAddressCollition.No)
                {
                    return(ShiftAddress(address, range, shift));
                }
            }
            return(address);
        }
예제 #32
0
        private void SetExcelTableDefinition()
        {
            var address = new ExcelAddressBase(1, 1, this._collection.Count + 1, this._propertyCollection.Count);

            this.ExcelWorksheet.Tables.Add(address, this.ExcelWorksheet.Name);
        }
예제 #33
0
        internal ExcelTable(ExcelWorksheet sheet, ExcelAddressBase address, string name, int tblId) : 
            base(sheet.NameSpaceManager)
	    {
            WorkSheet = sheet;
            Address = address;
            TableXml = new XmlDocument();
            LoadXmlSafe(TableXml, GetStartXml(name, tblId)); 
            TopNode = TableXml.DocumentElement;

            init();

            //If the table is just one row we can not have a header.
            if (address._fromRow == address._toRow)
            {
                ShowHeader = false;
            }
        }
예제 #34
0
        internal ExcelPivotTable(PackageRelationship rel, ExcelWorksheet sheet) :
            base(sheet.NameSpaceManager)
        {
            WorkSheet     = sheet;
            PivotTableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
            Relationship  = rel;
            var pck = sheet._package.Package;

            Part = pck.GetPart(PivotTableUri);

            PivotTableXml = new XmlDocument();
            LoadXmlSafe(PivotTableXml, Part.GetStream());
            init();
            TopNode = PivotTableXml.DocumentElement;
            Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));

            _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);

            LoadFields();

            //Add row fields.
            foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager))
            {
                int x;
                if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0)
                {
                    RowFields.AddInternal(Fields[x]);
                }
                else
                {
                    rowElem.ParentNode.RemoveChild(rowElem);
                }
            }

            ////Add column fields.
            foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager))
            {
                int x;
                if (int.TryParse(colElem.GetAttribute("x"), out x) && x >= 0)
                {
                    ColumnFields.AddInternal(Fields[x]);
                }
                else
                {
                    colElem.ParentNode.RemoveChild(colElem);
                }
            }

            //Add Page elements
            //int index = 0;
            foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager))
            {
                int fld;
                if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0)
                {
                    var field = Fields[fld];
                    field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld);
                    PageFields.AddInternal(field);
                }
            }

            //Add data elements
            //index = 0;
            foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager))
            {
                int fld;
                if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0)
                {
                    var field     = Fields[fld];
                    var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field);
                    DataFields.AddInternal(dataField);
                }
            }
        }
예제 #35
0
        /// <summary>
        /// Add a new pivottable
        /// </summary>
        /// <param name="sheet">The worksheet</param>
        /// <param name="address">the address of the pivottable</param>
        /// <param name="sourceAddress">The address of the Source data</param>
        /// <param name="name"></param>
        /// <param name="tblId"></param>
        internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address,ExcelRangeBase sourceAddress, string name, int tblId)
            : base(sheet.NameSpaceManager)
        {
            WorkSheet = sheet;
            Address = address;
            var pck = sheet._package.Package;

            PivotTableXml = new XmlDocument();
            LoadXmlSafe(PivotTableXml, GetStartXml(name, tblId, address, sourceAddress), Encoding.UTF8);
            TopNode = PivotTableXml.DocumentElement;
            PivotTableUri =  GetNewUri(pck, "/xl/pivotTables/pivotTable{0}.xml", ref tblId);
            init();

            Part = pck.CreatePart(PivotTableUri, ExcelPackage.schemaPivotTable);
            PivotTableXml.Save(Part.GetStream());

            //Worksheet-Pivottable relationship
            Relationship = sheet.Part.CreateRelationship(UriHelper.ResolvePartUri(sheet.WorksheetUri, PivotTableUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable");

            _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress, tblId);
            _cacheDefinition.Relationship=Part.CreateRelationship(UriHelper.ResolvePartUri(PivotTableUri, _cacheDefinition.CacheDefinitionUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition");

            sheet.Workbook.AddPivotTable(CacheID.ToString(), _cacheDefinition.CacheDefinitionUri);

            LoadFields();

            using (var r=sheet.Cells[address.Address])
            {
                r.Clear();
            }
        }
예제 #36
0
 /// <summary>
 /// Create a pivottable on the supplied range
 /// </summary>
 /// <param name="Range">The range address including header and total row</param>
 /// <param name="PivotCacheDefinition">A pivot table cache shared with another pivot table</param>
 /// <param name="Name">The name of the pivottable. Must be unique </param>
 /// <returns>The pivottable object</returns>
 public ExcelPivotTable Add(ExcelAddressBase Range, ExcelPivotCacheDefinition PivotCacheDefinition, string Name)
 {
     return(Add(new ExcelPivotTable(_ws, Range, PivotCacheDefinition._cacheReference, Name, _ws.Workbook._nextPivotTableID++)));
 }
예제 #37
0
        private string GetStartXml(string name, int id, ExcelAddressBase address, ExcelAddressBase sourceAddress)
        {
            string xml = string.Format("<pivotTableDefinition xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" name=\"{0}\" cacheId=\"{1}\" dataOnRows=\"1\" applyNumberFormats=\"0\" applyBorderFormats=\"0\" applyFontFormats=\"0\" applyPatternFormats=\"0\" applyAlignmentFormats=\"0\" applyWidthHeightFormats=\"1\" dataCaption=\"Data\"  createdVersion=\"4\" showMemberPropertyTips=\"0\" useAutoFormatting=\"1\" itemPrintTitles=\"1\" indent=\"0\" compact=\"0\" compactData=\"0\" gridDropZones=\"1\">", name, id);

            xml += string.Format("<location ref=\"{0}\" firstHeaderRow=\"1\" firstDataRow=\"1\" firstDataCol=\"1\" /> ", address.FirstAddress);
            xml += string.Format("<pivotFields count=\"{0}\">", sourceAddress._toCol-sourceAddress._fromCol+1);
            for (int col = sourceAddress._fromCol; col <= sourceAddress._toCol; col++)
            {
                xml += "<pivotField showAll=\"0\" />"; //compact=\"0\" outline=\"0\" subtotalTop=\"0\" includeNewItemsInFilter=\"1\"
            }

            xml += "</pivotFields>";
            xml += "<pivotTableStyleInfo name=\"PivotStyleMedium9\" showRowHeaders=\"1\" showColHeaders=\"1\" showRowStripes=\"0\" showColStripes=\"0\" showLastColumn=\"1\" />";
            xml += "</pivotTableDefinition>";
            return xml;
        }
예제 #38
0
 private static void InsertConditionalFormatting(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws)
 {
     //Update Conditional formatting references
     foreach (var cf in ws.ConditionalFormatting)
     {
         ((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(InsertSplitAddress(cf.Address, range, effectedAddress, shift).Address);
     }
 }
예제 #39
0
        public RangeAddress Create(string range)
        {
            Require.That(range).Named("range").IsNotNullOrEmpty();
            //var addressInfo = ExcelAddressInfo.Parse(range);
            var adr = new ExcelAddressBase(range);
            var rangeAddress = new RangeAddress()
            {
                Address = adr.Address,
                Worksheet = adr.WorkSheet ?? "",
                FromRow = adr._fromRow,
                FromCol = adr._fromCol,
                ToRow = adr._toRow,
                ToCol = adr._toCol
            };

            //if (addressInfo.IsMultipleCells)
            //{
            //    HandleMultipleCellAddress(rangeAddress, addressInfo);
            //}
            //else
            //{
            //    HandleSingleCellAddress(rangeAddress, addressInfo);
            //}
            return rangeAddress;
        }
예제 #40
0
        private static void DeletePivottableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress)
        {
            var deletedPt = new List <ExcelPivotTable>();

            foreach (var ptbl in ws.PivotTables)
            {
                if (shift == eShiftTypeDelete.Up)
                {
                    if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol)
                    {
                        ptbl.Address = ptbl.Address.DeleteRow(range._fromRow, range.Rows);
                    }
                }
                else
                {
                    if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow)
                    {
                        ptbl.Address = ptbl.Address.DeleteColumn(range._fromCol, range.Columns);
                    }
                }
                if (ptbl.Address == null)
                {
                    deletedPt.Add(ptbl);
                }
                else
                {
                    foreach (var wsSource in ws.Workbook.Worksheets)
                    {
                        if (ptbl.CacheDefinition.SourceRange.Worksheet == wsSource)
                        {
                            var address = ptbl.CacheDefinition.SourceRange;
                            if (shift == eShiftTypeDelete.Up)
                            {
                                if (address._fromCol >= range._fromCol && address._toCol <= range._toCol)
                                {
                                    var deletedRange = ws.Cells[address.DeleteRow(range._fromRow, range.Rows).Address];
                                    if (deletedRange != null)
                                    {
                                        ptbl.CacheDefinition.SourceRange = deletedRange;
                                    }
                                }
                            }
                            else
                            {
                                if (address._fromRow >= range._fromRow && address._toRow <= range._toRow)
                                {
                                    var deletedRange = ws.Cells[address.DeleteColumn(range._fromCol, range.Columns).Address];
                                    if (deletedRange != null)
                                    {
                                        ptbl.CacheDefinition.SourceRange = deletedRange;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            deletedPt.ForEach(x => ws.PivotTables.Delete(x));
        }