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);
}
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();
}
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);
}
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
});
}
}
}
}
}
}
}
}
}
/// <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;
}
}
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;
}
/// <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);
//}
}
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);
}
}
}
}
}
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;
}
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);
}
}
}
}
}
}
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);
}
}
}
}
}
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);
}
}
}
}
}
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();
}
}
/// <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;
}
}
public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress) :
base(Address, package, referenceAddress)
{
}
/// <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;
}
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++;
}
}
}
}
}
}
}
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);
}
}
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();
}
}
/// <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);
}
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);
}
}
}
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");
}
}
}
}
/// <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;
}
}
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;
}
}
}
}
}
/// <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);
}
}
public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress)
: base(Address, package, referenceAddress)
{
}
/// <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);
}
}
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++;
}
}
}
}
}
}
}
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;
}
}
}
}
}
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;
}
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);
}
}
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;
}
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));
}
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);
}
}
}
}
}
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));
}