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 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._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);
}
}
}
}
}
}
//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);
}
}
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);
}
}
}
}
}