A range address
Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5"
Inheritance: ExcelCellBase
Example #1
0
        public void SplitAddress()
        {
            var addr = new ExcelAddressBase("C3:F8");

            addr.Insert(new ExcelAddressBase("G9"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("G3"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("C9"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("B2"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("B3"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("D:D"), ExcelAddressBase.eShiftType.Right);
            addr.Insert(new ExcelAddressBase("5:5"), ExcelAddressBase.eShiftType.Down);
        }
Example #2
0
        public IEnumerable<IDictionary<string, object>> ExtractData(Stream fileStream)
        {
            Package = new ExcelPackage(fileStream);
            Workbook = Package.Workbook;
            Worksheet = (string.IsNullOrEmpty(SheetName)) ? Workbook.Worksheets[1] : Workbook.Worksheets[SheetName];
            Dimension = Worksheet.Dimension;
            if (Dimension == null) return Enumerable.Empty<IDictionary<string, object>>();
            EndAddress = Dimension.End;

            ReadHeaders();
            return ReadData();
        }
Example #3
0
        public void InsertDeleteTest()
        {
            var addr = new ExcelAddressBase("A1:B3");

            Assert.AreEqual(addr.AddRow(2, 4).Address, "A1:B7");
            Assert.AreEqual(addr.AddColumn(2, 4).Address, "A1:F3");
            Assert.AreEqual(addr.DeleteColumn(2, 1).Address, "A1:A3");
            Assert.AreEqual(addr.DeleteRow(2, 2).Address, "A1:B1");

            Assert.AreEqual(addr.DeleteRow(1, 3), null);
            Assert.AreEqual(addr.DeleteColumn(1, 2), null);
        }
Example #4
0
        private void FilesParse(HttpPostedFileBase file_)
        {
            using (ExcelPackage p = new ExcelPackage(file_.InputStream))
            {
                foreach (ExcelWorksheet ws in p.Workbook.Worksheets)
                {
                    OfficeOpenXml.ExcelAddressBase dim = ws.Dimension;
                    if (dim != null)
                    {
                        int           rowSt = dim.Start.Row;
                        int           colSt = dim.Start.Column;
                        int           rowFn = dim.End.Row;
                        int           colFn = dim.End.Column;
                        StringBuilder sb    = new StringBuilder(100, 500);

                        for (int i2 = colSt; i2 <= colFn; i2++)
                        {
                            if (ws.Cells[1, i2].Value != null && ws.Cells[1, i2].Value.ToString().Contains("merchant"))
                            {
                                for (int i = rowSt + 1; i <= rowFn; i++)
                                {
                                    if (ws.Cells[i, i2].Value != null)
                                    {
                                        if (!parsedValues.Any(s => s.ITEM_ID == ws.Cells[i, i2].Value.ToString() && s.USER_ID == CurrentUser))
                                        {
                                            parsedValues.Add(new DAL.parsedValues {
                                                ITEM_ID = ws.Cells[i, i2].Value.ToString(), USER_ID = CurrentUser
                                            });
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
Example #5
0
        /// <summary>
        /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
        /// if they fall after the afterRow and afterColumn.
        /// Supports inserting rows and columns into existing templates.
        /// </summary>
        /// <param name="Formula">The Excel formula</param>
        /// <param name="rowIncrement">The amount to increment the cell reference by</param>
        /// <param name="colIncrement">The amount to increment the cell reference by</param>
        /// <param name="afterRow">Only change rows after this row</param>
        /// <param name="afterColumn">Only change columns after this column</param>
        /// <param name="setFixed">Fixed address</param>
        /// <returns></returns>
        internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed=false)
        {
            //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
            var d=new Dictionary<string, object>();
            try
            {
                var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var tokens = sct.Tokenize(Formula);
                String f = "";
                foreach (var t in tokens)
                {
                    if (t.TokenType == TokenType.ExcelAddress)
                    {
                        var a = new ExcelAddressBase(t.Value);
                        if (!string.IsNullOrEmpty(a._ws) || !string.IsNullOrEmpty(a._wb))
                        {
                            // This address is in a different worksheet or workbook, thus no update is required
                            f += a.Address;
                            continue;
                        }                       
                        if (rowIncrement > 0)
                        {
                            a = a.AddRow(afterRow, rowIncrement, setFixed);
                        }
                        else if (rowIncrement < 0)
                        {
                            a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
                        }
                        if (colIncrement > 0)
                        {
                            a = a.AddColumn(afterColumn, colIncrement, setFixed);
                        }
                        else if (colIncrement < 0)
                        {
                            a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
                        }
                        if (a == null || !a.IsValidRowCol())
                        {
                            f += "#REF!";
                        }
                        else
                        {
                            f += a.Address;
                        }


                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return f;
            }
            catch //Invalid formula, skip updateing addresses
            {
                return Formula;
            }
        }
Example #6
0
        internal ExcelAddressBase Insert(ExcelAddressBase address, eShiftType Shift/*, out ExcelAddressBase topAddress, out ExcelAddressBase leftAddress, out ExcelAddressBase rightAddress, out ExcelAddressBase bottomAddress*/)
        {
            //Before or after, no change
            //if ((_toRow > address._fromRow && _toCol > address.column) ||
            //    (_fromRow > address._toRow && column > address._toCol))
            if(_toRow < address._fromRow || _toCol < address._fromCol || (_fromRow > address._toRow && _fromCol > address._toCol))
            {
                //topAddress = null;
                //leftAddress = null;
                //rightAddress = null;
                //bottomAddress = null;
                return this;
            }

            int rows = address.Rows;
            int cols = address.Columns;
            string retAddress = "";
            if (Shift==eShiftType.Right)
            {
                if (address._fromRow > _fromRow)
                {
                    retAddress = GetAddress(_fromRow, _fromCol, address._fromRow, _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
                }
                if(address._fromCol > _fromCol)
                {
                    retAddress = GetAddress(_fromRow < address._fromRow ? _fromRow : address._fromRow, _fromCol, address._fromRow, _toCol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
                }
            }
            if (_toRow < address._fromRow)
            {
                if (_fromRow < address._fromRow)
                {

                }
                else
                {
                }
            }
            return null;
        }
Example #7
0
 /// <summary>
 /// Creates an Address object
 /// </summary>
 /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
 /// <param name="address">The Excel Address</param>
 /// <param name="pck">Reference to the package to find information about tables and names</param>
 /// <param name="referenceAddress">The address</param>
 public ExcelAddressBase(string address, ExcelPackage pck, ExcelAddressBase referenceAddress)
 {
     SetAddress(address);
     SetRCFromTable(pck, referenceAddress);
 }
 /// <summary>
 /// Shifts all comments based on their address and the location of inserted rows and columns.
 /// </summary>
 /// <param name="fromRow">The start row.</param>
 /// <param name="fromCol">The start column.</param>
 /// <param name="rows">The number of rows to insert.</param>
 /// <param name="columns">The number of columns to insert.</param>
 public void Insert(int fromRow, int fromCol, int rows, int columns)
 {
     //List<ExcelComment> commentsToShift = new List<ExcelComment>();
       foreach (ExcelComment comment in _list)
       {
       var address = new ExcelAddressBase(comment.Address);
       if (rows > 0 && address._fromRow >= fromRow)
       {
           comment.Reference = comment.Range.AddRow(fromRow, rows).Address;
       }
       if(columns>0 && address._fromCol >= fromCol)
       {
          comment.Reference = comment.Range.AddColumn(fromCol, columns).Address;
       }
       }
       //foreach (ExcelComment comment in commentsToShift)
       //{
       //  Remove(comment);
       //  var address = new ExcelAddressBase(comment.Address);
       //  if (address._fromRow >= fromRow)
       //    address._fromRow += rows;
       //  if (address._fromCol >= fromCol)
       //    address._fromCol += columns;
       //  Add(Worksheet.Cells[address._fromRow, address._fromCol], comment.Text, comment.Author);
       //}
 }
Example #9
0
        private void SetStyleAddress(StyleBase sender, Style.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
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                int col = address.Start.Column, row = 0;
                bool isNew;
                //Get the startcolumn
                if (!ws._values.Exists(0, address.Start.Column))
                {
                    column = ws.Column(address.Start.Column);
                    isNew = true;
                }
                else
                {
                    column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
                    isNew = false;
                }
                var prevColumMax = column.ColumnMax;
                while (column.ColumnMin <= address.End.Column)
                {
                    if(column.ColumnMin > prevColumMax+1)
                    {
                        var newColumn = ws.Column(prevColumMax + 1);
                        newColumn.ColumnMax = column.ColumnMin-1;
                        AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                    }
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }
                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    AddNewStyleColumn(sender, e, ws, styleCashe, column, s);

                    //index++;
                    prevColumMax = 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
                        {
                            var newColumn = ws.Column(column._columnMax + 1);
                            newColumn.ColumnMax = address.End.Column;
                            AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                        }
                        break;
                    }
                    else
                    {
                        column = (ws._values.GetValue(0, col) as ExcelColumn);
                    }
                }

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

                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyle(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<int>(ws._styles, 1, address._fromCol, address._toRow, address._toCol);
                while (cse.Next())
                {
                    if (cse.Column >= address.Start.Column &&
                        cse.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cse.Value))
                        {
                            ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[cse.Value];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cse.Value, newId);
                            cse.Value = newId;
                            //ws.SetStyle(cse.Row, cse.Column, newId);
                        }
                    }
                }

                if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
                {
                    //Update cells with styled columns
                    cse = new CellsStoreEnumerator<int>(ws._styles, 1, 0, address._toRow, 0);
                    while (cse.Next())
                    {
                        for (int c = address._fromRow; c <= address._toCol; c++)
                        {
                            if (!ws._styles.Exists(cse.Row, c))
                            {
                                if (styleCashe.ContainsKey(cse.Value))
                                {
                                    ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
                                }
                                else
                                {
                                    ExcelXfs st = CellXfs[cse.Value];
                                    int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                    styleCashe.Add(cse.Value, newId);
                                    ws.SetStyle(cse.Row, c, newId);
                                }
                            }
                        }
                    }
                }
            }

            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    var s = ws._styles.GetValue(rowNum, 0);
                    if (s == 0)
                    {
                        //iterate all columns and set the row to the style of the last column
                        var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
                        while (cse.Next())
                        {
                            s = cse.Value;
                            var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
                            if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
                            {
                                for (int col = c.ColumnMin; col < c.ColumnMax; col++)
                                {
                                    if (!ws._styles.Exists(rowNum, col))
                                    {
                                        ws._styles.SetValue(rowNum, col, s);
                                    }
                                }
                            }
                        }
                        ws.SetStyle(rowNum, 0, s);
                        cse.Dispose();
                    }
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(rowNum, 0, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws._styles.SetValue(rowNum, 0, newId);
                        ws.SetStyle(rowNum, 0, newId);
                    }
                }

                //Update individual cells 
                var cse2 = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
                while (cse2.Next())
                {
                    var s = cse2.Value;
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        cse2.Value = newId;
                    }
                }

                //Update cells with styled rows
                cse2 = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, address._toCol);
                while (cse2.Next())
                {
                    for (int r = address._fromRow; r <= address._toRow; r++)
                    {
                        if (!ws._styles.Exists(r, cse2.Column))
                        {
                            var s = cse2.Value;
                            if (styleCashe.ContainsKey(s))
                            {
                                ws.SetStyle(r, cse2.Column, styleCashe[s]);
                            }
                            else
                            {
                                ExcelXfs st = CellXfs[s];
                                int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(s, newId);
                                ws.SetStyle(r, cse2.Column, newId);
                            }
                        }
                    }
                }
            }
            else             //Cellrange
            {
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        var s = GetStyleId(ws, row, col);
                        if (styleCashe.ContainsKey(s))
                        {
                            ws.SetStyle(row, col, styleCashe[s]);
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[s];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(s, newId);
                            ws.SetStyle(row, col, newId);
                        }
                    }
                }
            }
        }
Example #10
0
        private void ExportSellingCommission(out byte[] fileContent)
        {
            ucSellingCommission ucBase = (this.Parent.Parent.Parent as ucSellingCommission);
            DAL.Filters.SellingCommissionFilter filter = ucBase.Filter;
            bool isLiquidateView = filter.Export_Query == "Vendedores Liquidados";

            GridView gv = this.Parent.FindControl(this.GridViewId) as GridView;
            DataTable dt = App_Code.Helpers.ControlsHelper.GridviewToDataTable(gv, this.TypeExport, true);
            if (!isLiquidateView)
                dt.Columns.RemoveAt(0);

            using (ExcelPackage pck = new ExcelPackage())
            {
                // configuraciones
                pck.Compression = CompressionLevel.BestSpeed;
                pck.DoAdjustDrawings = true;

                // libro
                ExcelWorkbook wb = pck.Workbook;
                wb.Properties.Title = this.FileName;

                // hoja
                ExcelWorksheet ws = wb.Worksheets.Add(this.FileName);
                ws.TabColor = Color.FromArgb(30894);

                // cargo el filtro
                ws.Cells["A1"].Value = "Empresa Representada:";
                ws.Cells["A1"].Style.Font.Bold = true;
                ws.Cells["B1"].Value = filter.Export_Company;
                ws.Cells["A2"].Value = "Vendedor:";
                ws.Cells["A2"].Style.Font.Bold = true;
                ws.Cells["B2"].Value = filter.Export_Vendor;
                ws.Cells["A3"].Value = "Fecha de pedido desde:";
                ws.Cells["A3"].Style.Font.Bold = true;
                ws.Cells["B3"].Value = (filter.ORDR_DateFrom.HasValue) ? filter.ORDR_DateFrom.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["C3"].Value = "hasta";
                ws.Cells["D3"].Value = (filter.ORDR_DateTo.HasValue) ? filter.ORDR_DateTo.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["A4"].Value = "Selección de Consulta:";
                ws.Cells["A4"].Style.Font.Bold = true;
                ws.Cells["B4"].Value = filter.Export_Query;
                if (isLiquidateView)
                {
                    ws.Cells["A5"].Value = "Fecha de Liquidación Real desde:";
                    ws.Cells["A5"].Style.Font.Bold = true;
                    ws.Cells["B5"].Value = (filter.ORDR_LiquidateDateRegisteredFrom.HasValue) ? filter.ORDR_LiquidateDateRegisteredFrom.Value.ToString("dd-MM-yyyy") : "-";
                    ws.Cells["C5"].Value = "hasta";
                    ws.Cells["D5"].Value = (filter.ORDR_LiquidateDateRegisteredTo.HasValue) ? filter.ORDR_LiquidateDateRegisteredTo.Value.ToString("dd-MM-yyyy") : "-";
                }

                // cargo la grilla
                ws.Cells[isLiquidateView ? "A7" : "A6"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium2);
                ExcelAddressBase addressTable = ws.Tables["Table1"].Address;

                // cargo los totales
                ExcelAddressBase addressTotals = new ExcelAddressBase(addressTable.End.Row + 2, 1, addressTable.End.Row + 2, 1);
                ws.Cells[addressTotals.Start.Row, 1].Value = "Total T.P.:";
                ws.Cells[addressTotals.Start.Row, 1].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 2].Value = ucBase.TP;
                ws.Cells[addressTotals.Start.Row, 3].Value = "Total AMLA:";
                ws.Cells[addressTotals.Start.Row, 3].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 4].Value = ucBase.AMLA;
                ws.Cells[addressTotals.Start.Row, 5].Value = "Total OP:";
                ws.Cells[addressTotals.Start.Row, 5].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 6].Value = ucBase.OPE;
                ws.Cells[addressTotals.Start.Row, 7].Value = "Total Gs. Export:";
                ws.Cells[addressTotals.Start.Row, 7].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 8].Value = ucBase.GsExport;
                ws.Cells[addressTotals.Start.Row, 9].Value = "Total AMLA Vend.:";
                ws.Cells[addressTotals.Start.Row, 9].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 10].Value = ucBase.Vendor;

                // estilos y formato
                ws.Cells.Style.Numberformat.Format = _format_pesos;
                ws.Cells.AutoFitColumns();

                // devuelvo el contenido del archivo
                fileContent = pck.GetAsByteArray();
            }
        }
 private bool IsActiveCellInSelection(ExcelAddressBase ac, ExcelAddressBase sd)
 {
     var c = sd.Collide(ac);
     if (c == ExcelAddressBase.eAddressCollition.Equal || c == ExcelAddressBase.eAddressCollition.Inside)
     {
         return true;
     }
     else
     {
         if (sd.Addresses != null)
         {
             foreach (var sds in sd.Addresses)
             {
                 c = sds.Collide(ac);
                 if (c == ExcelAddressBase.eAddressCollition.Equal || c == ExcelAddressBase.eAddressCollition.Inside)
                 {
                     return true;
                 }
             }
         }
     }
     return false;
 }
Example #12
0
        private void SetStyleFullColumn(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
        {
            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)o;
                isNew  = false;
            }
            var prevColumMax = column.ColumnMax;

            while (column.ColumnMin <= address.End.Column)
            {
                if (column.ColumnMin > prevColumMax + 1)
                {
                    var newColumn = ws.Column(prevColumMax + 1);
                    newColumn.ColumnMax = column.ColumnMin - 1;
                    AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                }
                if (column.ColumnMax > address.End.Column)
                {
                    var 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++;
                prevColumMax = 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
                    {
                        var newColumn = ws.Column(column._columnMax + 1);
                        newColumn.ColumnMax = address.End.Column;
                        AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                        column = newColumn;
                    }
                    break;
                }
                else
                {
                    column = (ws.GetValueInner(0, col) as ExcelColumn);
                }
            }

            if (column._columnMax < address.End.Column)
            {
                var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
                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];
                    int      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 CellStoreEnumerator <ExcelValue>(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];
                        int      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 CellStoreEnumerator <ExcelValue>(ws._values, 1, 0, address._toRow, 0);
                while (cse.Next())
                {
                    if (cse.Value._styleId == 0)
                    {
                        continue;
                    }
                    for (int 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];
                                int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(cse.Value._styleId, newId);
                                ws.SetStyleInner(cse.Row, c, newId);
                            }
                        }
                    }
                }
            }
        }
Example #13
0
        private void SetStyleFullRow(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
        {
            for (int 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 CellStoreEnumerator <ExcelValue>(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 (int 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];
                    int      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 CellStoreEnumerator <ExcelValue>(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];
                    int      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 CellStoreEnumerator <ExcelValue>(ws._values, 0, 1, 0, address._toCol);
            while (cse2.Next())
            {
                if (cse2.Value._styleId == 0)
                {
                    continue;
                }
                for (int 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];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(s, newId);
                            ws.SetStyleInner(r, cse2.Column, newId);
                        }
                    }
                }
            }
        }
Example #14
0
        private void SetStyleCells(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
        {
            var rowCache     = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
            var colCache     = new Dictionary <int, ExcelValue>(address.End.Column - address.Start.Column + 1);
            var cellEnum     = new CellStoreEnumerator <ExcelValue>(ws._values, address.Start.Row, address.Start.Column, address.End.Row, address.End.Column);
            var hasEnumValue = cellEnum.Next();

            for (int row = address._fromRow; row <= address._toRow; row++)
            {
                for (int col = address._fromCol; col <= address._toCol; col++)
                {
                    ExcelValue value;
                    if (hasEnumValue && row == cellEnum.Row && col == cellEnum.Column)
                    {
                        value        = cellEnum.Value;
                        hasEnumValue = cellEnum.Next();
                    }
                    else
                    {
                        value = new ExcelValue {
                            _styleId = 0
                        };
                    }
                    var s = value._styleId;
                    if (s == 0)
                    {
                        // get row styleId with cache
                        if (rowCache.ContainsKey(row))
                        {
                            s = rowCache[row];
                        }
                        else
                        {
                            s = ws._values.GetValue(row, 0)._styleId;
                            rowCache.Add(row, s);
                        }
                        if (s == 0)
                        {
                            // get column styleId with cache
                            if (colCache.ContainsKey(col))
                            {
                                s = colCache[col]._styleId;
                            }
                            else
                            {
                                var v = ws._values.GetValue(0, col);
                                colCache.Add(col, v);
                                s = v._styleId;
                            }
                            if (s == 0)
                            {
                                int r = 0, c = col;
                                if (ws._values.PrevCell(ref r, ref c))
                                {
                                    if (!colCache.ContainsKey(c))
                                    {
                                        colCache.Add(c, ws._values.GetValue(0, c));
                                    }
                                    var val    = colCache[c];
                                    var colObj = (ExcelColumn)(val._value);
                                    if (colObj != null && colObj.ColumnMax >= col) //Fixes issue 15174
                                    {
                                        s = val._styleId;
                                    }
                                }
                            }
                        }
                    }
                    if (styleCashe.ContainsKey(s))
                    {
                        ws._values.SetValue(row, col, new ExcelValue {
                            _value = value._value, _styleId = styleCashe[s]
                        });
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws._values.SetValue(row, col, new ExcelValue {
                            _value = value._value, _styleId = newId
                        });
                    }
                }
            }
        }
        private void SetStyleAddress(StyleBase sender, Style.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
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                int         col = address.Start.Column, row = 0;
                //Get the startcolumn
                if (!ws._values.Exists(0, address.Start.Column))
                {
                    column = ws.Column(address.Start.Column);
                }
                else
                {
                    column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
                }


                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }
                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyle(0, column.ColumnMin, newId);
                    }

                    //index++;

                    if (!ws._values.NextCell(ref row, ref col) || row > 0)
                    {
                        column._columnMax = address.End.Column;
                        break;
                    }
                    else
                    {
                        column = (ws._values.GetValue(0, col) as ExcelColumn);
                    }
                }

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

                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws.SetStyle(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 <int>(ws._styles, 1, address._fromCol, address._toRow, address._toCol);
                while (cse.Next())
                {
                    if (cse.Column >= address.Start.Column &&
                        cse.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cse.Value))
                        {
                            ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cse.Value];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cse.Value, newId);
                            cse.Value = newId;
                            //ws.SetStyle(cse.Row, cse.Column, newId);
                        }
                    }
                }

                //Update cells with styled columns
                cse = new CellsStoreEnumerator <int>(ws._styles, 1, 0, address._toRow, 0);
                while (cse.Next())
                {
                    for (int c = address._fromRow; c <= address._toCol; c++)
                    {
                        if (!ws._styles.Exists(cse.Row, c))
                        {
                            if (styleCashe.ContainsKey(cse.Value))
                            {
                                ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
                            }
                            else
                            {
                                ExcelXfs st    = CellXfs[cse.Value];
                                int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(cse.Value, newId);
                                ws.SetStyle(cse.Row, c, newId);
                            }
                        }
                    }
                }
            }

            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    var s = ws._styles.GetValue(rowNum, 0);
                    if (s == 0)
                    {
                        //iterate all columns and set the row to the style of the last column
                        var cse = new CellsStoreEnumerator <int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
                        while (cse.Next())
                        {
                            s = cse.Value;
                            var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
                            if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
                            {
                                for (int col = c.ColumnMin; col < c.ColumnMax; col++)
                                {
                                    if (!ws._styles.Exists(rowNum, col))
                                    {
                                        ws._styles.SetValue(rowNum, col, s);
                                    }
                                }
                            }
                        }
                        ws.SetStyle(rowNum, 0, s);
                        cse.Dispose();
                    }
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(rowNum, 0, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws._styles.SetValue(rowNum, 0, newId);
                        ws.SetStyle(rowNum, 0, newId);
                    }
                }

                //Update individual cells
                var cse2 = new CellsStoreEnumerator <int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
                while (cse2.Next())
                {
                    var s = cse2.Value;
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[s];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        cse2.Value = newId;
                    }
                }

                //Update cells with styled rows
                cse2 = new CellsStoreEnumerator <int>(ws._styles, 0, 1, 0, address._toCol);
                while (cse2.Next())
                {
                    for (int r = address._fromRow; r <= address._toRow; r++)
                    {
                        if (!ws._styles.Exists(r, cse2.Column))
                        {
                            var s = cse2.Value;
                            if (styleCashe.ContainsKey(s))
                            {
                                ws.SetStyle(r, cse2.Column, styleCashe[s]);
                            }
                            else
                            {
                                ExcelXfs st    = CellXfs[s];
                                int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(s, newId);
                                ws.SetStyle(r, cse2.Column, newId);
                            }
                        }
                    }
                }
            }
            else             //Cellrange
            {
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        var s = GetStyleId(ws, row, col);
                        if (styleCashe.ContainsKey(s))
                        {
                            ws.SetStyle(row, col, styleCashe[s]);
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[s];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(s, newId);
                            ws.SetStyle(row, col, newId);
                        }
                    }
                }
            }
        }
Example #16
0
        private void ExportSupplyerCashFlow(out byte[] fileContent)
        {
            ucReportSupplyerCashFlow ucBase = (this.Parent.Parent.Parent as ucReportSupplyerCashFlow);
            DAL.Filters.SupplyerCashFlowFilter filter = ucBase.Filter;

            GridView gv = this.Parent.FindControl(this.GridViewId) as GridView;
            DataTable dt = App_Code.Helpers.ControlsHelper.GridviewToDataTable(gv, this.TypeExport, true);

            using (ExcelPackage pck = new ExcelPackage())
            {
                // configuraciones
                pck.Compression = CompressionLevel.BestSpeed;
                pck.DoAdjustDrawings = true;

                // libro
                ExcelWorkbook wb = pck.Workbook;
                wb.Properties.Title = this.FileName;

                // hoja
                ExcelWorksheet ws = wb.Worksheets.Add(this.FileName);
                ws.TabColor = Color.FromArgb(30894);

                // cargo el filtro
                ws.Cells["A1"].Value = "Período:";
                ws.Cells["A1"].Style.Font.Bold = true;
                ws.Cells["B1"].Value = (filter.ORDR_DateFrom.HasValue) ? filter.ORDR_DateFrom.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["C1"].Value = "al";
                ws.Cells["D1"].Value = (filter.ORDR_DateTo.HasValue) ? filter.ORDR_DateTo.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["A2"].Value = "Selección de Consulta:";
                ws.Cells["A2"].Style.Font.Bold = true;
                ws.Cells["B2"].Value = filter.Export_Query;
                ws.Cells["A3"].Value = "Empresa Representada:";
                ws.Cells["A3"].Style.Font.Bold = true;
                ws.Cells["B3"].Value = filter.Export_Company;
                ws.Cells["A4"].Value = "AMLA:";
                ws.Cells["A4"].Style.Font.Bold = true;
                ws.Cells["B4"].Value = filter.Export_AMLA;
                ws.Cells["A5"].Value = "Listar:";
                ws.Cells["A5"].Style.Font.Bold = true;
                ws.Cells["B5"].Value = filter.Export_Columns;

                // cargo la grilla
                ws.Cells["A7"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium2);
                ExcelAddressBase addressTable = ws.Tables["Table1"].Address;

                // cargo los totales
                ExcelAddressBase addressTotals = new ExcelAddressBase(addressTable.End.Row + 2, 1, addressTable.End.Row + 2, 1);
                ws.Cells[addressTotals.Start.Row, 1].Value = "Total T.P.:";
                ws.Cells[addressTotals.Start.Row, 1].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 2].Value = ucBase.TP;
                ws.Cells[addressTotals.Start.Row, 3].Value = "Total AMLA:";
                ws.Cells[addressTotals.Start.Row, 3].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 4].Value = ucBase.AMLA;
                ws.Cells[addressTotals.Start.Row, 5].Value = "Total OP:";
                ws.Cells[addressTotals.Start.Row, 5].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 6].Value = ucBase.OPE;
                ws.Cells[addressTotals.Start.Row, 7].Value = "Total Gs. Export:";
                ws.Cells[addressTotals.Start.Row, 7].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row, 8].Value = ucBase.GsExport;

                // estilos y formato
                ws.Cells.Style.Numberformat.Format = _format_pesos;
                ws.Cells.AutoFitColumns();

                // devuelvo el contenido del archivo
                fileContent = pck.GetAsByteArray();
            }
        }
Example #17
0
        /// <summary>
        /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
        /// if they fall after the afterRow and afterColumn.
        /// Supports inserting rows and columns into existing templates.
        /// </summary>
        /// <param name="Formula">The Excel formula</param>
        /// <param name="rowIncrement">The amount to increment the cell reference by</param>
        /// <param name="colIncrement">The amount to increment the cell reference by</param>
        /// <param name="afterRow">Only change rows after this row</param>
        /// <param name="afterColumn">Only change columns after this column</param>
        /// <returns></returns>
        internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn)
        {
            //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
            var d=new Dictionary<string, object>();
            try
            {
                var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var tokens = sct.Tokenize(Formula);
                String f = "";
                foreach (var t in tokens)
                {
                    if (t.TokenType == TokenType.ExcelAddress)
                    {
                        ExcelAddressBase a = new ExcelAddressBase(t.Value);
                        if (rowIncrement > 0)
                        {
                            a = a.AddRow(afterRow, rowIncrement);
                        }
                        else if (rowIncrement < 0)
                        {
                            a = a.DeleteRow(afterRow, -rowIncrement);
                        }
                        if (colIncrement > 0)
                        {
                            a = a.AddColumn(afterColumn, colIncrement);
                        }
                        else if (colIncrement > 0)
                        {
                            a = a.DeleteColumn(afterColumn, -colIncrement);
                        }
                        if (a == null)
                        {
                            f += "#REF!";
                        }
                        else
                        {
                            f += a.Address;
                        }

                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return f;
            }
            catch //Invalid formula, skip updateing addresses
            {
                return Formula;
            }
        }
Example #18
0
 public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress) :
     base(Address, package, referenceAddress)
 {
 }
Example #19
0
 /// <summary>
 /// Handels changes of properties on the style objects
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 /// <returns></returns>
 internal int PropertyChange(StyleBase sender, Style.StyleChangeEventArgs e)
 {
     var address = new ExcelAddressBase(e.Address);
     var ws = _wb.Worksheets[e.PositionID];
     Dictionary<int, int> styleCashe = new Dictionary<int, int>();
     //Set single address
     lock (ws._styles)
     {
         SetStyleAddress(sender, e, address, ws, ref styleCashe);
         if (address.Addresses != null)
         {
             //Handle multiaddresses
             foreach (var innerAddress in address.Addresses)
             {
                 SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
             }
         }
     }
     return 0;
 }
Example #20
0
        internal void SetRCFromTable(ExcelPackage pck, ExcelAddressBase referenceAddress)
        {
            if (string.IsNullOrEmpty(_wb) && Table != null)
            {
                foreach (var ws in pck.Workbook.Worksheets)
                {
                    foreach (var t in ws.Tables)
                    {
                        if (t.Name.Equals(Table.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            _ws = ws.Name;
                            if (Table.IsAll)
                            {
                                _fromRow = t.Address._fromRow;
                                _toRow   = t.Address._toRow;
                            }
                            else
                            {
                                if (Table.IsThisRow)
                                {
                                    if (referenceAddress == null)
                                    {
                                        _fromRow = -1;
                                        _toRow   = -1;
                                    }
                                    else
                                    {
                                        _fromRow = referenceAddress._fromRow;
                                        _toRow   = _fromRow;
                                    }
                                }
                                else if (Table.IsHeader && Table.IsData)
                                {
                                    _fromRow = t.Address._fromRow;
                                    _toRow   = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
                                }
                                else if (Table.IsData && Table.IsTotals)
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                                    _toRow   = t.Address._toRow;
                                }
                                else if (Table.IsHeader)
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow : -1;
                                    _toRow   = t.ShowHeader ? t.Address._fromRow : -1;
                                }
                                else if (Table.IsTotals)
                                {
                                    _fromRow = t.ShowTotal ? t.Address._toRow : -1;
                                    _toRow   = t.ShowTotal ? t.Address._toRow : -1;
                                }
                                else
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                                    _toRow   = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
                                }
                            }

                            if (string.IsNullOrEmpty(Table.ColumnSpan))
                            {
                                _fromCol = t.Address._fromCol;
                                _toCol   = t.Address._toCol;
                                return;
                            }
                            else
                            {
                                var col  = t.Address._fromCol;
                                var cols = Table.ColumnSpan.Split(':');
                                foreach (var c in t.Columns)
                                {
                                    if (_fromCol <= 0 && cols[0].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase))   //Issue15063 Add invariant igore case
                                    {
                                        _fromCol = col;
                                        if (cols.Length == 1)
                                        {
                                            _toCol = _fromCol;
                                            return;
                                        }
                                    }
                                    else if (cols.Length > 1 && _fromCol > 0 && cols[1].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
                                    {
                                        _toCol = col;
                                        return;
                                    }

                                    col++;
                                }
                            }
                        }
                    }
                }
            }
        }
Example #21
0
        private static string UpdateFormulaReferncesPrivate(string formula, ExcelAddressBase range, ExcelAddressBase effectedRange, string currentSheet, string modifiedSheet, bool setFixed, int rowIncrement, int colIncrement)
        {
            try
            {
                var afterRow    = range._fromRow;
                var afterColumn = range._fromCol;
                var sct         = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var tokens      = sct.Tokenize(formula);
                var f           = "";
                foreach (var t in tokens)
                {
                    if (t.TokenTypeIsSet(TokenType.ExcelAddress))
                    {
                        var address = new ExcelAddressBase(t.Value);
                        if (((!string.IsNullOrEmpty(address._wb) || !IsReferencesModifiedWorksheet(currentSheet, modifiedSheet, address)) && !setFixed) ||
                            address.Collide(effectedRange) == ExcelAddressBase.eAddressCollition.No)
                        {
                            f += address.Address;
                            continue;
                        }

                        if (!string.IsNullOrEmpty(address._ws)) //The address has worksheet.
                        {
                            if (t.Value.IndexOf("'!") >= 0)
                            {
                                f += $"'{address._ws}'!";
                            }
                            else
                            {
                                f += $"{address._ws}!";
                            }
                        }
                        if (!address.IsFullColumn)
                        {
                            if (rowIncrement > 0)
                            {
                                address = address.AddRow(afterRow, rowIncrement, setFixed);
                            }
                            else if (rowIncrement < 0)
                            {
                                if (address._fromRowFixed == false && (address._fromRow >= afterRow && address._toRow < afterRow - rowIncrement))
                                {
                                    address = null;
                                }
                                else
                                {
                                    address = address.DeleteRow(afterRow, -rowIncrement, setFixed);
                                }
                            }
                        }
                        if (address != null && !address.IsFullRow)
                        {
                            if (colIncrement > 0)
                            {
                                address = address.AddColumn(afterColumn, colIncrement, setFixed);
                            }
                            else if (colIncrement < 0)
                            {
                                if (address._fromColFixed == false && (address._fromCol >= afterColumn && address._toCol < afterColumn - colIncrement))
                                {
                                    address = null;
                                }
                                else
                                {
                                    address = address.DeleteColumn(afterColumn, -colIncrement, setFixed);
                                }
                            }
                        }

                        if (address == null || !address.IsValidRowCol())
                        {
                            f += "#REF!";
                        }
                        else
                        {
                            var ix = address.Address.LastIndexOf('!');
                            if (ix > 0)
                            {
                                f += address.Address.Substring(ix + 1);
                            }
                            else
                            {
                                f += address.Address;
                            }
                        }
                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return(f);
            }
            catch //Invalid formula, return formula
            {
                return(formula);
            }
        }
Example #22
0
        private void ExportRankings(out byte[] fileContent)
        {
            ucReportRanking ucBase = (this.Parent.Parent.Parent as ucReportRanking);
            DAL.Filters.RankingFilter filter = ucBase.Filter;

            GridView gv = this.Parent.FindControl(this.GridViewId) as GridView;
            DataTable dt = App_Code.Helpers.ControlsHelper.GridviewToDataTable(gv, this.TypeExport, true);

            using (ExcelPackage pck = new ExcelPackage())
            {
                // configuraciones
                pck.Compression = CompressionLevel.BestSpeed;
                pck.DoAdjustDrawings = true;

                // libro
                ExcelWorkbook wb = pck.Workbook;
                wb.Properties.Title = this.FileName;

                // hoja
                ExcelWorksheet ws = wb.Worksheets.Add(this.FileName);
                ws.TabColor = Color.FromArgb(30894);

                // cargo el filtro
                ws.Cells["A1"].Value = "Período:";
                ws.Cells["A1"].Style.Font.Bold = true;
                ws.Cells["B1"].Value = (filter.ORDR_DateFrom.HasValue) ? filter.ORDR_DateFrom.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["C1"].Value = "al";
                ws.Cells["D1"].Value = (filter.ORDR_DateTo.HasValue) ? filter.ORDR_DateTo.Value.ToString("dd-MM-yyyy") : "-";
                ws.Cells["A2"].Value = "Artículo:";
                ws.Cells["A2"].Style.Font.Bold = true;
                ws.Cells["B2"].Value = filter.Export_Article;
                ws.Cells["A3"].Value = "Empresa Representada:";
                ws.Cells["A3"].Style.Font.Bold = true;
                ws.Cells["B3"].Value = filter.Export_Company;
                ws.Cells["A4"].Value = "Cliente:";
                ws.Cells["A4"].Style.Font.Bold = true;
                ws.Cells["B4"].Value = filter.Export_Client;
                ws.Cells["A5"].Value = "Agrupar consulta por:";
                ws.Cells["A5"].Style.Font.Bold = true;
                ws.Cells["B5"].Value = filter.Export_GroupBy;

                // cargo la grilla
                ws.Cells["A7"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium2);
                ExcelAddressBase addressTable = ws.Tables["Table1"].Address;

                // cargo los totales
                ExcelAddressBase addressTotals = new ExcelAddressBase(addressTable.End.Row + 2, 1, addressTable.End.Row + 2, 1);
                ws.Cells[addressTotals.Start.Row, 1].Value = "Metros Pedidos:";
                ws.Cells[addressTotals.Start.Row, 2].Value = ucBase.MtsOrdered;
                ws.Cells[addressTotals.Start.Row, 1].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 1, 1].Value = "Kilos Pedidos:";
                ws.Cells[addressTotals.Start.Row + 1, 2].Value = ucBase.KgOrdered;
                ws.Cells[addressTotals.Start.Row + 1, 1].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 2, 1].Value = "Unidades Pedidos:";
                ws.Cells[addressTotals.Start.Row + 2, 2].Value = ucBase.UnitOrdered;
                ws.Cells[addressTotals.Start.Row + 2, 1].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 3, 1].Value = "Sin especificar Pedido:";
                ws.Cells[addressTotals.Start.Row + 3, 2].Value = ucBase.NullOrdered;
                ws.Cells[addressTotals.Start.Row + 3, 1].Style.Font.Bold = true;

                ws.Cells[addressTotals.Start.Row, 4].Value = "Metros Embarcados:";
                ws.Cells[addressTotals.Start.Row, 5].Value = ucBase.MtsShipped;
                ws.Cells[addressTotals.Start.Row, 4].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 1, 4].Value = "Kilos Embarcados:";
                ws.Cells[addressTotals.Start.Row + 1, 5].Value = ucBase.KgShipped;
                ws.Cells[addressTotals.Start.Row + 1, 4].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 2, 4].Value = "Unidades Embarcados:";
                ws.Cells[addressTotals.Start.Row + 2, 5].Value = ucBase.UnitShipped;
                ws.Cells[addressTotals.Start.Row + 2, 4].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 3, 4].Value = "Sin especificar Embarcado:";
                ws.Cells[addressTotals.Start.Row + 3, 5].Value = ucBase.NullShipped;
                ws.Cells[addressTotals.Start.Row + 3, 4].Style.Font.Bold = true;

                ws.Cells[addressTotals.Start.Row, 7].Value = "Dólares:";
                ws.Cells[addressTotals.Start.Row, 8].Value = ucBase.Dollars;
                ws.Cells[addressTotals.Start.Row, 7].Style.Font.Bold = true;
                ws.Cells[addressTotals.Start.Row + 1, 7].Value = "Gs. Export:";
                ws.Cells[addressTotals.Start.Row + 1, 8].Value = ucBase.AMLA;
                ws.Cells[addressTotals.Start.Row + 1, 7].Style.Font.Bold = true;

                // estilos y formato
                ws.Cells.Style.Numberformat.Format = _format_pesos;
                ws.Cells.AutoFitColumns();

                // devuelvo el contenido del archivo
                fileContent = pck.GetAsByteArray();
            }
        }
Example #23
0
 /// <summary>
 /// Creates an Address object
 /// </summary>
 /// <remarks>Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5" </remarks>
 /// <param name="address">The Excel Address</param>
 /// <param name="pck">Reference to the package to find information about tables and names</param>
 /// <param name="referenceAddress">The address</param>
 public ExcelAddressBase(string address, ExcelPackage pck, ExcelAddressBase referenceAddress)
 {
     SetAddress(address);
     SetRCFromTable(pck, referenceAddress);
 }
Example #24
0
        internal static AddressType IsValid(string Address)
        {
            string ws = "";

            if (Address.StartsWith("'"))
            {
                int ix = Address.IndexOf('\'', 1);
                if (ix > -1)
                {
                    ws      = Address.Substring(1, ix - 1);
                    Address = Address.Substring(ix + 2);
                }
            }
            if (Address.IndexOfAny(new char[] { '(', ')', '+', '-', '*', '/', '.', '=', '^', '&', '%', '\"' }) > -1)
            {
                return(AddressType.Invalid);
            }
            if (Address.IndexOf('!') > 0)
            {
                string[] split = Address.Split('!');
                if (split.Length == 2)
                {
                    ws      = split[0];
                    Address = split[1];
                }
                else if (split.Length == 3 && split[1] == "#REF" && split[2] == "")
                {
                    ws      = split[0];
                    Address = "#REF!";
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalAddress);
                    }
                    else
                    {
                        return(AddressType.InternalAddress);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
            int row, col;

            if (ExcelAddressBase.GetRowCol(Address, out row, out col, false))
            {
                if (row > 0 && col > 0 && row <= ExcelPackage.MaxRows && col <= ExcelPackage.MaxColumns)
                {
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalAddress);
                    }
                    else
                    {
                        return(AddressType.InternalAddress);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
            else
            {
                if (IsValidName(Address))
                {
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalName);
                    }
                    else
                    {
                        return(AddressType.InternalName);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
        }
Example #25
0
        internal void GetDefinedNames()
        {
            XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);

            if (nl != null)
            {
                foreach (XmlElement elem in nl)
                {
                    string fullAddress = elem.InnerText;

                    int            localSheetID;
                    ExcelWorksheet nameWorksheet;
                    if (!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
                    {
                        localSheetID  = -1;
                        nameWorksheet = null;
                    }
                    else
                    {
                        nameWorksheet = Worksheets[localSheetID + 1];
                    }
                    var             addressType = ExcelAddressBase.IsValid(fullAddress);
                    ExcelRangeBase  range;
                    ExcelNamedRange namedRange;

                    if (fullAddress.IndexOf("[") == 0)
                    {
                        int start = fullAddress.IndexOf("[");
                        int end   = fullAddress.IndexOf("]", start);
                        if (start >= 0 && end >= 0)
                        {
                            string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
                            int    index;
                            if (int.TryParse(externalIndex, out index))
                            {
                                if (index > 0 && index <= _externalReferences.Count)
                                {
                                    fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
                                }
                            }
                        }
                    }

                    if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType == ExcelAddressBase.AddressType.Formula || addressType == ExcelAddressBase.AddressType.ExternalAddress)                    //A value or a formula
                    {
                        double value;
                        range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
                        if (nameWorksheet == null)
                        {
                            namedRange = _names.Add(elem.GetAttribute("name"), range);
                        }
                        else
                        {
                            namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
                        }

                        if (Utils.ConvertUtil._invariantCompareInfo.IsPrefix(fullAddress, "\""))                         //String value
                        {
                            namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
                        }
                        else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
                        {
                            namedRange.NameValue = value;
                        }
                        else
                        {
                            //if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
                            //{
                            //    var r = new ExcelAddress(fullAddress);
                            //    namedRange.NameFormula = '\'[' + r._wb
                            //}
                            //else
                            //{
                            namedRange.NameFormula = fullAddress;
                            //}
                        }
                    }
                    else
                    {
                        ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
                        if (localSheetID > -1)
                        {
                            if (string.IsNullOrEmpty(addr._ws))
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
                            }
                            else
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
                            }
                        }
                        else
                        {
                            var ws = Worksheets[addr._ws];
                            namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
                        }
                    }
                    if (elem.GetAttribute("hidden") == "1" && namedRange != null)
                    {
                        namedRange.IsNameHidden = true;
                    }
                    if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
                    {
                        namedRange.NameComment = elem.GetAttribute("comment");
                    }
                }
            }
        }
Example #26
0
        /// <summary>
        /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
        /// if they fall after the afterRow and afterColumn.
        /// Supports inserting rows and columns into existing templates.
        /// </summary>
        /// <param name="formula">The Excel formula</param>
        /// <param name="rowIncrement">The amount to increment the cell reference by</param>
        /// <param name="colIncrement">The amount to increment the cell reference by</param>
        /// <param name="afterRow">Only change rows after this row</param>
        /// <param name="afterColumn">Only change columns after this column</param>
        /// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
        /// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
        /// <param name="setFixed">Fixed address</param>
        /// <returns>The updated version of the <paramref name="formula"/>.</returns>
        internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
        {
            var d = new Dictionary<string, object>();
            try
            {
                var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var tokens = sct.Tokenize(formula);
                String f = "";
                foreach (var t in tokens)
                {
                    if (t.TokenType == TokenType.ExcelAddress)
                    {
                        var a = new ExcelAddressBase(t.Value);
                        var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase);

                        if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet))
                        {
                            // This address is in a different worksheet or workbook; no update is required.
                            f += a.Address;
                            continue;
                        }
                        // Persist fully-qualified worksheet references.
                        if (!string.IsNullOrEmpty(a._ws))
                        {
                            f += $"'{a._ws}'!";
                        }
                        if (rowIncrement > 0)
                        {
                            a = a.AddRow(afterRow, rowIncrement, setFixed);
                        }
                        else if (rowIncrement < 0)
                        {
                            a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
                        }
                        if (colIncrement > 0)
                        {
                            a = a.AddColumn(afterColumn, colIncrement, setFixed);
                        }
                        else if (colIncrement < 0)
                        {
                            a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
                        }
                        if (a == null || !a.IsValidRowCol())
                        {
                            f += "#REF!";
                        }
                        else
                        {
                          // If the address was not shifted, then a.Address will still have the sheet name.
                          var address = a.Address.Split('!');
                          if (address.Length > 1)
                            f += address[1];
                          else
                            f += a.Address;
                        }

                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return f;
            }
            catch //Invalid formula, skip updating addresses
            {
                return formula;
            }
        }
Example #27
0
        private void SetStyleAddress(StyleBase sender, Style.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
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                //Get the startcolumn
                ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
                if (!ws._columns.ContainsKey(colID))
                {
                    column=ws.Column(address.Start.Column);
                }
                else
                {
                    column = ws._columns[colID] as ExcelColumn;
                }

                var index = ws._columns.IndexOf(colID);
                while(column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }

                    if (styleCashe.ContainsKey(column.StyleID))
                    {
                        column.StyleID = styleCashe[column.StyleID];
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[column.StyleID];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(column.StyleID, newId);
                        column.StyleID = newId;
                    }

                    index++;
                    if (index >= ws._columns.Count)
                    {
                        break;
                    }
                    else
                    {
                        column = (ws._columns[index] as ExcelColumn);
                    }
                }

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

                    if (styleCashe.ContainsKey(newCol.StyleID))
                    {
                        newCol.StyleID = styleCashe[newCol.StyleID];
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[column.StyleID];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(newCol.StyleID, newId);
                        newCol.StyleID = newId;
                    }

                    //column._columnMax = address.End.Column;
                }

                //Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
                foreach (ExcelCell cell in ws._cells)
                {
                    if (cell.Column >= address.Start.Column &&
                       cell.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[cell.StyleID];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }

                }
            }
            //Rows
            else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    ExcelRow row = ws.Row(rowNum);
                    if (row.StyleID == 0 && ws._columns.Count > 0)
                    {
                        //TODO: We should loop all columns here and change each cell. But for now we take style of column A.
                        foreach (ExcelColumn column in ws._columns)
                        {
                            row.StyleID = column.StyleID;
                            break;  //Get the first one and break.
                        }

                    }
                    if (styleCashe.ContainsKey(row.StyleID))
                    {
                        row.StyleID = styleCashe[row.StyleID];
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[row.StyleID];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(row.StyleID, newId);
                        row.StyleID = newId;
                    }
                }

                //Get Start Cell
                ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
                int index = ws._cells.IndexOf(rowID);

                index = ~index;
                while (index < ws._cells.Count)
                {
                    var cell = ws._cells[index] as ExcelCell;
                    if(cell.Row > address.End.Row)
                    {
                        break;
                    }
                    if (styleCashe.ContainsKey(cell.StyleID))
                    {
                        cell.StyleID = styleCashe[cell.StyleID];
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[cell.StyleID];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(cell.StyleID, newId);
                        cell.StyleID = newId;
                    }
                    index++;
                }
            }
            else             //Cellrange
            {
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        ExcelCell cell = ws.Cell(row, col);
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[cell.StyleID];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }
                }
            }
        }
Example #28
0
 /// <summary>
 /// Updates all the references to a renamed sheet in a formula.
 /// </summary>
 /// <param name="formula">The formula to updated.</param>
 /// <param name="oldSheetName">The old sheet name.</param>
 /// <param name="newSheetName">The new sheet name.</param>
 /// <returns>The formula with all cross-sheet references updated.</returns>
 internal static string UpdateFormulaSheetReferences(string formula, string oldSheetName, string newSheetName)
 {
     if (string.IsNullOrEmpty(oldSheetName))
     throw new ArgumentNullException(nameof(oldSheetName));
       if (string.IsNullOrEmpty(newSheetName))
     throw new ArgumentNullException(nameof(newSheetName));
       var d = new Dictionary<string, object>();
       try
       {
     var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
     var tokens = sct.Tokenize(formula);
     String f = "";
     foreach (var t in tokens)
     {
       if (t.TokenType == TokenType.ExcelAddress)
       {
         var a = new ExcelAddressBase(t.Value);
         if (a == null || !a.IsValidRowCol())
         {
           f += "#REF!";
         }
         else
         {
           a.ChangeWorksheet(oldSheetName, newSheetName);
           f += a.Address;
         }
       }
       else
       {
         f += t.Value;
       }
     }
     return f;
       }
       catch //Invalid formula, skip updating addresses
       {
     return formula;
       }
 }
 /// <summary>
 /// Shifts all comments based on their address and the location of inserted rows and columns.
 /// </summary>
 /// <param name="fromRow">The start row.</param>
 /// <param name="fromCol">The start column.</param>
 /// <param name="rows">The number of rows to insert.</param>
 /// <param name="columns">The number of columns to insert.</param>
 internal void Delete(int fromRow, int fromCol, int rows, int columns)
 {
     List<ExcelComment> deletedComments = new List<ExcelComment>();
     ExcelAddressBase address = null;
     foreach (ExcelComment comment in _list)
     {
         address = new ExcelAddressBase(comment.Address);
         if (fromCol>0 && address._fromCol >= fromCol)
         {
             address = address.DeleteColumn(fromCol, columns);
         }
         if(fromRow > 0 && address._fromRow >= fromRow)
         {
             address = address.DeleteRow(fromRow, rows);
         }
         if(address.Address=="#REF!")
         {
             deletedComments.Add(comment);
         }
         else
         {
             comment.Reference = address.Address;
         }
     }
     foreach(var comment in deletedComments)
     {
         Remove(comment);
     }
 }
        /// <summary>
        /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
        /// if they fall after the afterRow and afterColumn.
        /// Supports inserting rows and columns into existing templates.
        /// </summary>
        /// <param name="formula">The Excel formula</param>
        /// <param name="rowIncrement">The amount to increment the cell reference by</param>
        /// <param name="colIncrement">The amount to increment the cell reference by</param>
        /// <param name="afterRow">Only change rows after this row</param>
        /// <param name="afterColumn">Only change columns after this column</param>
        /// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
        /// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
        /// <param name="setFixed">Fixed address</param>
        /// <returns>The updated version of the <paramref name="formula"/>.</returns>
        internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
        {
            var d = new Dictionary <string, object>();

            try
            {
                var    sct    = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var    tokens = sct.Tokenize(formula);
                String f      = "";
                foreach (var t in tokens)
                {
                    if (t.TokenType == TokenType.ExcelAddress)
                    {
                        var a = new ExcelAddressBase(t.Value);
                        var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase);

                        if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet))
                        {
                            // This address is in a different worksheet or workbook; no update is required.
                            f += a.Address;
                            continue;
                        }
                        // Persist fully-qualified worksheet references.
                        if (!string.IsNullOrEmpty(a._ws))
                        {
                            f += $"'{a._ws}'!";
                        }
                        if (rowIncrement > 0)
                        {
                            a = a.AddRow(afterRow, rowIncrement, setFixed);
                        }
                        else if (rowIncrement < 0)
                        {
                            a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
                        }
                        if (colIncrement > 0)
                        {
                            a = a.AddColumn(afterColumn, colIncrement, setFixed);
                        }
                        else if (colIncrement < 0)
                        {
                            a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
                        }
                        if (a == null || !a.IsValidRowCol())
                        {
                            f += "#REF!";
                        }
                        else
                        {
                            // If the address was not shifted, then a.Address will still have the sheet name.
                            var address = a.Address.Split('!');
                            if (address.Length > 1)
                            {
                                f += address[1];
                            }
                            else
                            {
                                f += a.Address;
                            }
                        }
                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return(f);
            }
            catch //Invalid formula, skip updating addresses
            {
                return(formula);
            }
        }
Example #31
0
 public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress)
     : base(Address, package, referenceAddress)
 {
 }
Example #32
0
        /// <summary>
        /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
        /// if they fall after the afterRow and afterColumn.
        /// Supports inserting rows and columns into existing templates.
        /// </summary>
        /// <param name="formula">The Excel formula</param>
        /// <param name="rowIncrement">The amount to increment the cell reference by</param>
        /// <param name="colIncrement">The amount to increment the cell reference by</param>
        /// <param name="afterRow">Only change rows after this row</param>
        /// <param name="afterColumn">Only change columns after this column</param>
        /// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
        /// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
        /// <param name="setFixed">Fixed address</param>
        /// <returns>The updated version of the <paramref name="formula"/>.</returns>
        internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
        {
            try
            {
                var sct    = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
                var tokens = sct.Tokenize(formula);
                var f      = "";
                foreach (var t in tokens)
                {
                    if (t.TokenType == TokenType.ExcelAddress)
                    {
                        var address = new ExcelAddressBase(t.Value);

                        if ((!string.IsNullOrEmpty(address._wb) || !IsReferencesModifiedWorksheet(currentSheet, modifiedSheet, address)) && !setFixed)
                        {
                            f += address.Address;
                            continue;
                        }

                        if (!string.IsNullOrEmpty(address._ws)) //Address has worksheet.
                        {
                            f += $"'{address._ws}'!";
                        }
                        if (rowIncrement > 0)
                        {
                            address = address.AddRow(afterRow, rowIncrement, setFixed);
                        }
                        else if (rowIncrement < 0)
                        {
                            address = address.DeleteRow(afterRow, -rowIncrement, setFixed);
                        }
                        if (colIncrement > 0)
                        {
                            address = address.AddColumn(afterColumn, colIncrement, setFixed);
                        }
                        else if (colIncrement < 0)
                        {
                            address = address.DeleteColumn(afterColumn, -colIncrement, setFixed);
                        }
                        if (address == null || !address.IsValidRowCol())
                        {
                            f += "#REF!";
                        }
                        else
                        {
                            var ix = address.Address.LastIndexOf('!');
                            if (ix > 0)
                            {
                                f += address.Address.Substring(ix + 1);
                            }
                            else
                            {
                                f += address.Address;
                            }
                        }
                    }
                    else
                    {
                        f += t.Value;
                    }
                }
                return(f);
            }
            catch //Invalid formula, return formula
            {
                return(formula);
            }
        }
Example #33
0
        internal void SetRCFromTable(ExcelPackage pck, ExcelAddressBase referenceAddress)
        {
            if (string.IsNullOrEmpty(_wb) && Table != null)
            {
                foreach (var ws in pck.Workbook.Worksheets)
                {
                    foreach (var t in ws.Tables)
                    {
                        if (t.Name.Equals(Table.Name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            _ws = ws.Name;
                            if (Table.IsAll)
                            {
                                _fromRow = t.Address._fromRow;
                                _toRow = t.Address._toRow;
                            }
                            else
                            {
                                if (Table.IsThisRow)
                                {
                                    if (referenceAddress == null)
                                    {
                                        _fromRow = -1;
                                        _toRow = -1;
                                    }
                                    else
                                    {
                                        _fromRow = referenceAddress._fromRow;
                                        _toRow = _fromRow;
                                    }
                                }
                                else if (Table.IsHeader && Table.IsData)
                                {
                                    _fromRow = t.Address._fromRow;
                                    _toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
                                }
                                else if (Table.IsData && Table.IsTotals)
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                                    _toRow = t.Address._toRow;
                                }
                                else if (Table.IsHeader)
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow : -1;
                                    _toRow = t.ShowHeader ? t.Address._fromRow : -1;
                                }
                                else if (Table.IsTotals)
                                {
                                    _fromRow = t.ShowTotal ? t.Address._toRow : -1;
                                    _toRow = t.ShowTotal ? t.Address._toRow : -1;
                                }
                                else
                                {
                                    _fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
                                    _toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
                                }
                            }

                            if (string.IsNullOrEmpty(Table.ColumnSpan))
                            {
                                _fromCol = t.Address._fromCol;
                                _toCol = t.Address._toCol;
                                return;
                            }
                            else
                            {
                                var col = t.Address._fromCol;
                                var cols = Table.ColumnSpan.Split(':');
                                foreach (var c in t.Columns)
                                {
                                    if (_fromCol <= 0 && cols[0].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase))   //Issue15063 Add invariant igore case
                                    {
                                        _fromCol = col;
                                        if (cols.Length == 1)
                                        {
                                            _toCol = _fromCol;
                                            return;
                                        }
                                    }
                                    else if (cols.Length > 1 && _fromCol > 0 && cols[1].Equals(c.Name, StringComparison.InvariantCultureIgnoreCase)) //Issue15063 Add invariant igore case
                                    {
                                        _toCol = col;
                                        return;
                                    }

                                    col++;
                                }
                            }
                        }
                    }
                }
            }
        }
Example #34
0
        private void SetStyleAddress(StyleBase sender, Style.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
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                //Get the startcolumn
                ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
                if (!ws._columns.ContainsKey(colID))
                {
                    column = ws.Column(address.Start.Column);
                }
                else
                {
                    column = ws._columns[colID] as ExcelColumn;
                }

                var index = ws._columns.IndexOf(colID);
                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }

                    if (styleCashe.ContainsKey(column.StyleID))
                    {
                        column.StyleID = styleCashe[column.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[column.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(column.StyleID, newId);
                        column.StyleID = newId;
                    }

                    index++;
                    if (index >= ws._columns.Count)
                    {
                        break;
                    }
                    else
                    {
                        column = (ws._columns[index] as ExcelColumn);
                    }
                }

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

                    if (styleCashe.ContainsKey(newCol.StyleID))
                    {
                        newCol.StyleID = styleCashe[newCol.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[column.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(newCol.StyleID, newId);
                        newCol.StyleID = newId;
                    }

                    //column._columnMax = address.End.Column;
                }

                //Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
                foreach (ExcelCell cell in ws._cells)
                {
                    if (cell.Column >= address.Start.Column &&
                        cell.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cell.StyleID];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }
                }
            }
            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    ExcelRow row = ws.Row(rowNum);
                    if (row.StyleID == 0 && ws._columns.Count > 0)
                    {
                        //TODO: We should loop all columns here and change each cell. But for now we take style of column A.
                        foreach (ExcelColumn column in ws._columns)
                        {
                            row.StyleID = column.StyleID;
                            break;  //Get the first one and break.
                        }
                    }
                    if (styleCashe.ContainsKey(row.StyleID))
                    {
                        row.StyleID = styleCashe[row.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[row.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(row.StyleID, newId);
                        row.StyleID = newId;
                    }
                }

                //Get Start Cell
                ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
                int   index = ws._cells.IndexOf(rowID);

                index = ~index;
                while (index < ws._cells.Count)
                {
                    var cell = ws._cells[index] as ExcelCell;
                    if (cell.Row > address.End.Row)
                    {
                        break;
                    }
                    if (styleCashe.ContainsKey(cell.StyleID))
                    {
                        cell.StyleID = styleCashe[cell.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[cell.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(cell.StyleID, newId);
                        cell.StyleID = newId;
                    }
                    index++;
                }
            }
            else             //Cellrange
            {
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        ExcelCell cell = ws.Cell(row, col);
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cell.StyleID];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }
                }
            }
        }
Example #35
0
 internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol)
 {
     if (address.IndexOf("!") == -1 || address=="#REF!")
        {
            if (fullRowCol)
            {
                string[] cells = address.Split(':');
                if (cells.Length > 0)
                {
                    address = string.Format("'{0}'!{1}", worksheetName, cells[0]);
                    if (cells.Length > 1)
                    {
                        address += string.Format(":{0}", cells[1]);
                    }
                }
            }
            else
            {
                var a = new ExcelAddressBase(address);
                if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows) || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns))
                {
                    address = string.Format("'{0}'!{1}{2}:{3}{4}", worksheetName, ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow);
                }
                else
                {
                    address=GetFullAddress(worksheetName, address, true);
                }
            }
        }
        return address;
 }
Example #36
0
        private void SetStyleAddress(StyleBase sender, Style.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
            else 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 prevColumMax = column.ColumnMax;
                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMin > prevColumMax + 1)
                    {
                        var newColumn = ws.Column(prevColumMax + 1);
                        newColumn.ColumnMax = column.ColumnMin - 1;
                        AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                    }
                    if (column.ColumnMax > address.End.Column)
                    {
                        var 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++;
                    prevColumMax = 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
                        {
                            var newColumn = ws.Column(column._columnMax + 1);
                            newColumn.ColumnMax = address.End.Column;
                            AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
                        }
                        break;
                    }
                    else
                    {
                        column = (ws.GetValueInner(0, col) as ExcelColumn);
                    }
                }

                if (column._columnMax < address.End.Column)
                {
                    var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
                    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];
                        int      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];
                            int      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 (int c = address._fromRow; 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];
                                    int      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 (int 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 (int 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];
                        int      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];
                        int      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 (int 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];
                                int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                                styleCashe.Add(s, newId);
                                ws.SetStyleInner(r, cse2.Column, newId);
                            }
                        }
                    }
                }
            }
            else             //Cellrange
            {
                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 <ExcelCoreValue> list, int index, int row, int column, object 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));
                                    }
                                    var 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];
                        int 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);
            }
        }
Example #37
0
        internal eAddressCollition Collide(ExcelAddressBase address)
        {
            if (address.WorkSheet != WorkSheet)
            {
                return eAddressCollition.No;
            }

            if (address._fromRow > _toRow || address._fromCol > _toCol
                ||
                _fromRow > address._toRow || _fromCol > address._toCol)
            {
                return eAddressCollition.No;
            }
            else if (address._fromRow == _fromRow && address._fromCol == _fromCol &&
                    address._toRow == _toRow && address._toCol == _toCol)
            {
                return eAddressCollition.Equal;
            }
            else if (address._fromRow >= _fromRow && address._toRow <= _toRow &&
                     address._fromCol >= _fromCol && address._toCol <= _toCol)
            {
                return eAddressCollition.Inside;
            }
            else
                return eAddressCollition.Partly;
        }
Example #38
0
 private static bool IsReferencesModifiedWorksheet(string currentSheet, string modifiedSheet, ExcelAddressBase a)
 {
     return((string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) ||
            modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase));
 }
Example #39
0
        private void SetStyleAddress(StyleBase sender, Style.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
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                int col = address.Start.Column, row = 0;
                //Get the startcolumn
                //ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
                if (!ws._values.Exists(0, address.Start.Column))
                {
                    column = ws.Column(address.Start.Column);
                }
                else
                {
                    column = ws._values.GetValue(0, address.Start.Column) as ExcelColumn;
                }

                //var index = ws._columns.IndexOf(colID);
                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }
                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        //column.StyleID = styleCashe[s];
                        ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
                        ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        //column.StyleID = newId;
                        ws.SetStyle(0, column.ColumnMin, newId);
                    }

                    //index++;

                    if (!ws._values.NextCell(ref row, ref col) || row > 0)
                    {
                        column._columnMax = address.End.Column;
                        break;
                    }
                    else
                    {
                        column = (ws._values.GetValue(0, col) as ExcelColumn);
                    }
                }

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

                    var s = ws._styles.GetValue(0, column.ColumnMin);
                    if (styleCashe.ContainsKey(s))
                    {
                        //newCol.StyleID = styleCashe[s];
                        //ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
                        ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        //newCol.StyleID = newId;
                        ws.SetStyle(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<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
                while (cse.Next())
                {
                    if (cse.Column >= address.Start.Column &&
                        cse.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cse.Value))
                        {
                            ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[cse.Value];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cse.Value, newId);
                            //cse.Value = newId;
                            ws.SetStyle(cse.Row, cse.Column, newId);
                        }
                    }
                }
            }
            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    //ExcelRow row = ws.Row(rowNum);
                    var s = ws._styles.GetValue(rowNum, 0);
                    if (s == 0)
                    {
                        //iteratte all columns and set the row to the style of the last column
                        var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
                        while (cse.Next())
                        {
                            s = cse.Value;
                            var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
                            if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
                            {
                                for (int col = c.ColumnMin; col < c.ColumnMax; col++)
                                {
                                    if (!ws._styles.Exists(rowNum, col))
                                    {
                                        ws._styles.SetValue(rowNum, col, s);
                                    }
                                }
                            }
                        }
                        ws.SetStyle(rowNum, 0, s);
                        cse.Dispose();
                    }
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(rowNum, 0, styleCashe[s]);
                        //row.StyleID = styleCashe[s];
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        ws._styles.SetValue(rowNum, 0, newId);
                        ws.SetStyle(rowNum, 0, newId);
                    }
                }

                //Get Start Cell
                //ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
                //int index = ws._cells.IndexOf(rowID);

                //index = ~index;
                var cse2 = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
                //while (index < ws._cells.Count)
                while (cse2.Next())
                {
                    //var cell = ws._cells[index] as ExcelCell;
                    //if(cell.Row > address.End.Row)
                    //{
                    //    break;
                    //}
                    var s = cse2.Value;
                    if (styleCashe.ContainsKey(s))
                    {
                        ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
                    }
                    else
                    {
                        ExcelXfs st = CellXfs[s];
                        int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(s, newId);
                        cse2.Value = newId;
                        ws.SetStyle(cse2.Row, cse2.Column, newId);
                    }
                }
            }
            else             //Cellrange
            {
                //var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
                //while(cse.Next())
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        //ExcelCell cell = ws.Cell(row, col);
                        //int s = ws._styles.GetValue(row, col);
                        var s = GetStyleId(ws, row, col);
                        if (styleCashe.ContainsKey(s))
                        {
                            ws.SetStyle(row, col, styleCashe[s]);
                        }
                        else
                        {
                            ExcelXfs st = CellXfs[s];
                            int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(s, newId);
                            ws.SetStyle(row, col, newId);
                        }
                    }
                }
            }
        }
Example #40
0
        internal static string UpdateFormulaReferences(string formula, ExcelAddressBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift, string currentSheet, string modifiedSheet, bool setFixed = false)
        {
            int rowIncrement;
            int colIncrement;

            if (shift == eShiftTypeDelete.Up || shift == eShiftTypeDelete.EntireRow)
            {
                rowIncrement = -range.Rows;
                colIncrement = 0;
            }
            else
            {
                colIncrement = -range.Columns;
                rowIncrement = 0;
            }

            return(UpdateFormulaReferncesPrivate(formula, range, effectedRange, currentSheet, modifiedSheet, setFixed, rowIncrement, colIncrement));
        }