public void TestWriteModifySheetSimple() { string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls"); FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate); HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = null; ICell c = null; for (int rownum = 0; rownum < 100; rownum++) { r = s.CreateRow(rownum); for (int cellnum = 0; cellnum < 50; cellnum += 2) { c = r.CreateCell(cellnum); c.SetCellValue(rownum * 10000 + cellnum + (((double)rownum / 1000) + ((double)cellnum / 10000))); c = r.CreateCell(cellnum + 1); c.SetCellValue(new HSSFRichTextString("TEST")); } } for (int rownum = 0; rownum < 25; rownum++) { r = s.GetRow(rownum); s.RemoveRow(r); } for (int rownum = 75; rownum < 100; rownum++) { r = s.GetRow(rownum); s.RemoveRow(r); } wb.Write(out1); out1.Close(); sanityChecker.CheckHSSFWorkbook(wb); Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74"); Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25"); }
/// <summary> /// 删除指定工作表名的所有行 /// </summary> /// <param name="sheetName">工作表名称</param> private void sheetClear(string sheetName) { xlSheet = xlBook.GetSheet(sheetName); NPOI.SS.UserModel.IRow row = null; for (int i = 0; i < xlSheet.LastRowNum + 1; i++) { if (xlSheet.GetRow(i) != null) { row = xlSheet.GetRow(i); xlSheet.RemoveRow(row); } } }
/// <summary> /// 移除行 /// </summary> /// <param name="sheet">NPOI工作表</param> /// <param name="startRowIndex">起始行索引</param> /// <param name="endRowIndex">结束行索引</param> public static int RemoveRows(this NPOI.SS.UserModel.ISheet sheet, int startRowIndex, int endRowIndex) { var span = endRowIndex - startRowIndex + 1; sheet.RemoveMergedRegions(startRowIndex, endRowIndex, null, null); sheet.RemovePictures(startRowIndex, endRowIndex, null, null); for (var i = endRowIndex; i >= startRowIndex; i--) { var row = sheet.GetRow(i); sheet.RemoveRow(row); } if (endRowIndex + 1 <= sheet.LastRowNum) { sheet.ShiftRows(endRowIndex + 1, sheet.LastRowNum, -span, true, false); sheet.MovePictures(endRowIndex + 1, null, null, null, moveRowCount: -span); } return(span); }
public void TestRemoveZeroRow() { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); try { sheet.RemoveRow(row); } catch (ArgumentException e) { if (e.Message.Equals("Invalid row number (-1) outside allowable range (0..65535)")) { throw new AssertionException("Identified bug 45367"); } throw e; } }