/**
* 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)
{
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)
{
// 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;
}
