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