private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (XSSFCell xssfCell in row)
{
CT_Cell ctCell = xssfCell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
string formula1 = f.Value;
if (formula1.Length > 0)
{
string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
if (str != null)
{
f.Value = str;
}
}
if (f.isSetRef())
{
string formula2 = f.@ref;
string str = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
if (str != null)
{
f.@ref = str;
}
}
}
}
}
public List <CellRangeAddress> ShiftMerged(int startRow, int endRow, int n)
{
List <CellRangeAddress> cellRangeAddressList = new List <CellRangeAddress>();
for (int index = 0; index < this.sheet.NumMergedRegions; ++index)
{
CellRangeAddress mergedRegion = this.sheet.GetMergedRegion(index);
if ((mergedRegion.FirstRow >= startRow || mergedRegion.LastRow >= startRow) && (mergedRegion.FirstRow <= endRow || mergedRegion.LastRow <= endRow) && (!XSSFRowShifter.ContainsCell(mergedRegion, startRow - 1, 0) && !XSSFRowShifter.ContainsCell(mergedRegion, endRow + 1, 0)))
{
mergedRegion.FirstRow += n;
mergedRegion.LastRow += n;
cellRangeAddressList.Add(mergedRegion);
this.sheet.RemoveMergedRegion(index);
--index;
}
}
foreach (CellRangeAddress region in cellRangeAddressList)
{
this.sheet.AddMergedRegion(region);
}
return(cellRangeAddressList);
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook workbook = this.sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex((ISheet)this.sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
List <CT_ConditionalFormatting> conditionalFormatting1 = this.sheet.GetCTWorksheet().conditionalFormatting;
for (int index1 = 0; conditionalFormatting1 != null && index1 < conditionalFormatting1.Count; ++index1)
{
CT_ConditionalFormatting conditionalFormatting2 = conditionalFormatting1[index1];
List <CellRangeAddress> cellRangeAddressList1 = new List <CellRangeAddress>();
foreach (object obj in conditionalFormatting2.sqref)
{
string str = obj.ToString();
char[] chArray = new char[1] {
' '
};
foreach (string reference in str.Split(chArray))
{
cellRangeAddressList1.Add(CellRangeAddress.ValueOf(reference));
}
}
bool flag = false;
List <CellRangeAddress> cellRangeAddressList2 = new List <CellRangeAddress>();
for (int index2 = 0; index2 < cellRangeAddressList1.Count; ++index2)
{
CellRangeAddress cra = cellRangeAddressList1[index2];
CellRangeAddress cellRangeAddress = XSSFRowShifter.ShiftRange(Shifter, cra, sheetIndex);
if (cellRangeAddress == null)
{
flag = true;
}
else
{
cellRangeAddressList2.Add(cellRangeAddress);
if (cellRangeAddress != cra)
{
flag = true;
}
}
}
if (flag)
{
if (cellRangeAddressList2.Count == 0)
{
conditionalFormatting1.RemoveAt(index1);
continue;
}
List <string> stringList = new List <string>();
foreach (CellRangeAddress cellRangeAddress in cellRangeAddressList2)
{
stringList.Add(cellRangeAddress.FormatAsString());
}
conditionalFormatting2.sqref = stringList;
}
foreach (CT_CfRule ctCfRule in conditionalFormatting2.cfRule)
{
List <string> formula = ctCfRule.formula;
for (int index2 = 0; index2 < formula.Count; ++index2)
{
Ptg[] ptgs = FormulaParser.Parse(formula[index2], (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
formula[index2] = formulaString;
}
}
}
}
}
/**
* Shifts rows between startRow and endRow n number of rows.
* If you use a negative number, it will shift rows up.
* Code ensures that rows don't wrap around
*
* <p>
* Additionally Shifts merged regions that are completely defined in these
* rows (ie. merged 2 cells on a row to be Shifted).
* <p>
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @param copyRowHeight whether to copy the row height during the shift
* @param reSetOriginalRowHeight whether to set the original row's height to the default
*/
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool reSetOriginalRowHeight)
{
List<int> rowsToRemove = new List<int>();
foreach (KeyValuePair<int,XSSFRow> rowDict in _rows)
{
XSSFRow row = rowDict.Value;
int rownum = row.RowNum;
if (rownum < startRow) continue;
if (!copyRowHeight)
{
row.Height = (short)-1;
}
if (RemoveRow(startRow, endRow, n, rownum))
{
// remove row from worksheet.GetSheetData row array
int idx = HeadMap(_rows, row.RowNum).Count;
if (n > 0) { idx -= rowsToRemove.Count; } else { idx += rowsToRemove.Count; } // compensate removed rows
worksheet.sheetData.RemoveRow(idx);
// remove row from _rows
//throw new NotImplementedException();
//it.Remove();
rowsToRemove.Add(rowDict.Key);
}
else if (rownum >= startRow && rownum <= endRow)
{
row.Shift(n);
}
if (sheetComments != null)
{
//TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
CT_CommentList lst = sheetComments.GetCTComments().commentList;
foreach (CT_Comment comment in lst.comment)
{
CellReference ref1 = new CellReference(comment.@ref);
if (ref1.Row == rownum)
{
ref1 = new CellReference(rownum + n, ref1.Col);
comment.@ref = ref1.FormatAsString();
}
}
}
}
foreach(int rowKey in rowsToRemove)
{
_rows.Remove(rowKey);
}
XSSFRowShifter rowShifter = new XSSFRowShifter(this);
int sheetIndex = Workbook.GetSheetIndex(this);
FormulaShifter Shifter = FormulaShifter.CreateForRowShift(sheetIndex, startRow, endRow, n);
rowShifter.UpdateNamedRanges(Shifter);
rowShifter.UpdateFormulas(Shifter);
rowShifter.ShiftMerged(startRow, endRow, n);
rowShifter.UpdateConditionalFormatting(Shifter);
// no need to sort because it is already sorted.
////rebuild the _rows map
//SortedDictionary<int, XSSFRow> map = new SortedDictionary<int, XSSFRow>();
//foreach (XSSFRow r in _rows.Values)
//{
// map.Add(r.RowNum, r);
//}
//_rows = map;
}
/**
* Shifts rows between startRow and endRow n number of rows.
* If you use a negative number, it will shift rows up.
* Code ensures that rows don't wrap around
*
* <p>
* Additionally Shifts merged regions that are completely defined in these
* rows (ie. merged 2 cells on a row to be Shifted).
* <p>
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @param copyRowHeight whether to copy the row height during the shift
* @param reSetOriginalRowHeight whether to set the original row's height to the default
*/
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool reSetOriginalRowHeight)
{
List<int> rowsToRemove = new List<int>();
foreach (KeyValuePair<int,XSSFRow> rowDict in _rows)
{
XSSFRow row = rowDict.Value;
int rownum = row.RowNum;
if (RemoveRow(startRow, endRow, n, rownum))
{
// remove row from worksheet.SheetData row array
int idx = rowDict.Key+1;
//if (n > 0)
//{
// idx -= rowsToRemove.Count;
//}
//else
//{
// idx += rowsToRemove.Count;
//}
// compensate removed rows
worksheet.sheetData.RemoveRow(idx);
// remove row from _rows
rowsToRemove.Add(rowDict.Key);
}
if (!copyRowHeight)
{
row.Height = (short)-1;
}
if (sheetComments != null && rownum >= startRow && rownum <= endRow)
{
//TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
CT_CommentList lst = sheetComments.GetCTComments().commentList;
foreach (CT_Comment comment in lst.comment)
{
CellReference ref1 = new CellReference(comment.@ref);
if (ref1.Row == rownum)
{
CellReference ref2 = new CellReference(rownum + n, ref1.Col);
string originRef = comment.@ref;
comment.@ref = ref2.FormatAsString();
break;
}
}
}
}
foreach(int rowKey in rowsToRemove)
{
_rows.Remove(rowKey);
}
if(sheetComments!=null)
sheetComments.RecreateReference();
foreach (XSSFRow row in _rows.Values)
{
int rownum = row.RowNum;
if (rownum >= startRow && rownum <= endRow)
{
row.Shift(n);
}
}
XSSFRowShifter rowShifter = new XSSFRowShifter(this);
int sheetIndex = Workbook.GetSheetIndex(this);
FormulaShifter Shifter = FormulaShifter.CreateForRowShift(sheetIndex, startRow, endRow, n);
rowShifter.UpdateNamedRanges(Shifter);
rowShifter.UpdateFormulas(Shifter);
rowShifter.ShiftMerged(startRow, endRow, n);
rowShifter.UpdateConditionalFormatting(Shifter);
//rebuild the _rows map
SortedList<int, XSSFRow> map = new SortedList<int, XSSFRow>();
foreach (XSSFRow r in _rows.Values)
{
map.Add(r.RowNum, r);
}
_rows = map;
}
/**
* Shifts rows between startRow and endRow n number of rows.
* If you use a negative number, it will shift rows up.
* Code ensures that rows don't wrap around
*
* <p>
* Additionally Shifts merged regions that are completely defined in these
* rows (ie. merged 2 cells on a row to be Shifted).
* <p>
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @param copyRowHeight whether to copy the row height during the shift
* @param reSetOriginalRowHeight whether to set the original row's height to the default
*/
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool reSetOriginalRowHeight)
{
// first remove all rows which will be overwritten
List<int> rowsToRemove = new List<int>();
foreach (KeyValuePair<int, XSSFRow> rowDict in _rows)
{
XSSFRow row = rowDict.Value;
int rownum = row.RowNum;
// check if we should remove this row as it will be overwritten by the data later
if (RemoveRow(startRow, endRow, n, rownum))
{
// remove row from worksheet.SheetData row array
int idx = rowDict.Key + 1;
// compensate removed rows
worksheet.sheetData.RemoveRow(idx);
// remove row from _rows
rowsToRemove.Add(rowDict.Key);
}
}
foreach (int rowKey in rowsToRemove)
{
_rows.Remove(rowKey);
}
// then do the actual moving and also adjust comments/rowHeight
foreach (KeyValuePair<int, XSSFRow> rowDict in _rows)
{
XSSFRow row = (XSSFRow)rowDict.Value;
int rownum = row.RowNum;
if (sheetComments != null && rownum >= startRow && rownum <= endRow)
{
//TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
CT_CommentList lst = sheetComments.GetCTComments().commentList;
foreach (CT_Comment comment in lst.comment)
{
String oldRef = comment.@ref;
CellReference ref1 = new CellReference(oldRef);
if (ref1.Row == rownum)
{
CellReference ref2 = new CellReference(rownum + n, ref1.Col);
comment.@ref = ref2.FormatAsString();
sheetComments.ReferenceUpdated(oldRef, comment);
}
}
}
if (rownum < startRow || rownum > endRow) continue;
if (!copyRowHeight)
{
row.Height = ((short)-1);
}
row.Shift(n);
}
//if(sheetComments!=null)
// sheetComments.RecreateReference();
//foreach (XSSFRow row in _rows.Values)
//{
// int rownum = row.RowNum;
// if (rownum >= startRow && rownum <= endRow)
// {
// row.Shift(n);
// }
//}
XSSFRowShifter rowShifter = new XSSFRowShifter(this);
int sheetIndex = Workbook.GetSheetIndex(this);
String sheetName = Workbook.GetSheetName(sheetIndex);
FormulaShifter shifter = FormulaShifter.CreateForRowShift(
sheetIndex, sheetName, startRow, endRow, n);
rowShifter.UpdateNamedRanges(shifter);
rowShifter.UpdateFormulas(shifter);
rowShifter.ShiftMerged(startRow, endRow, n);
rowShifter.UpdateConditionalFormatting(shifter);
//rebuild the _rows map
SortedList<int, XSSFRow> map = new SortedList<int, XSSFRow>();
foreach (XSSFRow r in _rows.Values)
{
map.Add(r.RowNum, r);
}
_rows = map;
}
