internal ExcelColor(ExcelStyles styles, OfficeOpenXml.XmlHelper.ChangedEventHandler ChangedEvent, int worksheetID, string address, eStyleClass cls, StyleBase parent) :
base(styles, ChangedEvent, worksheetID, address)
{
_parent = parent;
_cls = cls;
}
internal ExcelColor(ExcelStyles styles, OfficeOpenXml.XmlHelper.ChangedEventHandler ChangedEvent, int worksheetID, string address, eStyleClass cls, StyleBase parent) :
base(styles, ChangedEvent, worksheetID, address)
{
_parent = parent;
_cls = cls;
}
private void AddNewStyleColumn(StyleBase sender, StyleChangeEventArgs e, ExcelWorksheet ws, Dictionary <int, int> styleCashe, ExcelColumn column, int s)
{
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
}
/// <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, StyleChangeEventArgs e)
{
var address = new ExcelAddressBase(e.Address);
ExcelWorksheet ws = _wb.Worksheets[e.PositionID];
var styleCashe = new Dictionary <int, int>();
//Set single address
lock (ws._values)
{
SetStyleAddress(sender, e, address, ws, ref styleCashe);
if (address.Addresses != null)
{
//Handle multi-addresses
foreach (ExcelAddress innerAddress in address.Addresses)
{
SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
}
}
}
return(0);
}
/// <summary>
/// Handles property changes on Named styles.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <returns></returns>
internal int NamedStylePropertyChange(StyleBase sender, StyleChangeEventArgs e)
{
var index = NamedStyles.FindIndexById(e.Address);
if (index >= 0)
{
var newId = CellStyleXfs[NamedStyles[index].StyleXfId].GetNewID(CellStyleXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
var prevIx = NamedStyles[index].StyleXfId;
NamedStyles[index].StyleXfId = newId;
NamedStyles[index].Style.Index = newId;
NamedStyles[index].XfId = int.MinValue;
foreach (ExcelXfs style in CellXfs)
{
if (style.XfId == prevIx)
{
style.XfId = newId;
}
}
}
return(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);
}
}
}
}
}
/// <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;
}
/// <summary>
/// Handles property changes on Named styles.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <returns></returns>
internal int NamedStylePropertyChange(StyleBase sender, Style.StyleChangeEventArgs e)
{
int index = NamedStyles.FindIndexByID(e.Address);
if (index >= 0)
{
int newId = CellStyleXfs[NamedStyles[index].StyleXfId].GetNewID(CellStyleXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
int prevIx=NamedStyles[index].StyleXfId;
NamedStyles[index].StyleXfId = newId;
NamedStyles[index].Style.Index = newId;
NamedStyles[index].XfId = int.MinValue;
foreach (var style in CellXfs)
{
if (style.XfId == prevIx)
{
style.XfId = newId;
}
}
}
return 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;
}
}
}
}
}
private void AddNewStyleColumn(StyleBase sender, StyleChangeEventArgs e, ExcelWorksheet ws, Dictionary<int, int> styleCashe, ExcelColumn column, int s)
{
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);
}
}
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);
}
}
}
}
}
/// <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)
{
int rowFrom,rowTo, colFrom, colTo;
Dictionary<int, int> styleCashe = new Dictionary<int, int>();
ExcelCell.GetRowColFromAddress(e.Address, out rowFrom, out colFrom, out rowTo, out colTo);
var ws = _wb.Worksheets[e.PositionID];
//Cellrange
if (colFrom > 0 && rowFrom > 0)
{
for (int col = colFrom; col <= colTo; col++)
{
for (int row = rowFrom; row <= rowTo; 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;
}
}
}
}
else if (colFrom > 0)
{
for (int col = colFrom; col <= colTo; col++)
{
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, col);
var column = ws._columns[colID] as ExcelColumn;
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;
}
}
}
else
{
for (int rowNum = rowFrom; rowNum <= rowTo; 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;
}
}
}
return 0;
}
private void SetStyleAddress(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw new Exception("error address");
}
//Columns
if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
//column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column);
column = (ExcelColumn)o;
isNew = false;
}
var prevColumnMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumnMax + 1)
{
ExcelColumn newColumn = ws.Column(prevColumnMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
ExcelColumn newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumnMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
ExcelColumn newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
column = newColumn;
}
break;
}
column = ws.GetValueInner(0, col) as ExcelColumn;
}
if (column._columnMax < address.End.Column)
{
ExcelColumn newCol = ws.Column(column._columnMax + 1);
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column && cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0);
while (cse.Next())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (var c = address._fromCol; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (var rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws.GetStyleInner(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value._styleId;
if (s == 0)
{
continue;
}
var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (var col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws.ExistsStyleInner(rowNum, col))
{
ws.SetStyleInner(rowNum, col, s);
}
}
}
}
ws.SetStyleInner(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value._styleId;
if (s == 0)
{
continue;
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(cse2.Row, cse2.Column, newId);
}
}
//Update cells with styled rows
cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol);
while (cse2.Next())
{
if (cse2.Value._styleId == 0)
{
continue;
}
for (var r = address._fromRow; r <= address._toRow; r++)
{
if (!ws.ExistsStyleInner(r, cse2.Column))
{
var s = cse2.Value._styleId;
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(r, cse2.Column, newId);
}
}
}
}
}
else //Cell range
{
var tmpCache = styleCashe;
var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1);
ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column,
(list, index, row, column, args) =>
{
// Optimized GetStyleID
var s = list[index]._styleId;
if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s))
{
// get row styleId with cache
if (!rowCache.ContainsKey(row))
{
rowCache.Add(row, ws._values.GetValue(row, 0)._styleId);
}
s = rowCache[row];
if (s == 0)
{
// get column styleId with cache
if (!colCache.ContainsKey(column))
{
colCache.Add(column, ws._values.GetValue(0, column));
}
s = colCache[column]._styleId;
if (s == 0)
{
int r = 0, c = column;
if (ws._values.PrevCell(ref r, ref c))
{
//var val = ws._values.GetValue(0, c);
if (!colCache.ContainsKey(c))
{
colCache.Add(c, ws._values.GetValue(0, c));
}
ExcelCoreValue val = colCache[c];
var colObj = (ExcelColumn)val._value;
if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174
{
s = val._styleId;
}
}
}
}
}
if (tmpCache.ContainsKey(s))
{
//ws.SetStyleInner(row, column, tmpCache[s]);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = tmpCache[s]
};
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
tmpCache.Add(s, newId);
//ws.SetStyleInner(row, column, newId);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = newId
};
}
}, e);
}
}
