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