需求是如果文件不存在则创建,如果存在则打开并追加。主要逻辑为,判断文件是否存在,不存在时则创建并添加表头,如果文件已存在则首先打开,赋值给workbook,并获取到最大的行数,然后再次读取文件,添加行数时加上最大行数则可往下追加新的数据。以下为源码,已测试。
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle styleHeader = (HSSFCellStyle)workbook.CreateCellStyle();
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
styleHeader.SetFont(font);
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
if (!File.Exists("demo.xls")) //判断文件是否存在,不存在时则创建并添加表头
{
using (FileStream fs = new FileStream( "demo.xls", FileMode.OpenOrCreate))//读取流
{
//创建sheet
Sheet sheet = workbook.CreateSheet("log");
Row rowHeader = sheet.CreateRow(0);
//添加表头
for (int col = 0; col < dt.Columns.Count; col++)
{
Cell cellHeader = rowHeader.CreateCell(col);
cellHeader.SetCellValue(dt.Columns[col].ColumnName);
sheet.SetColumnWidth(col, 15 * 256);
cellHeader.CellStyle = styleHeader;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
Row row = sheet.CreateRow(i + 1);//如果无数据则+1留出表头位置
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
workbook.Write(fs)
fs.Close();
}
}
else
{ //如果文件已存在则首先打开,并且获取到最大的行数
using (FileStream fs = new FileStream("demo.xls", FileMode.OpenOrCreate))//读取流
{
workbook = new HSSFWorkbook(fs);
Sheet sheet1 = workbook.GetSheetAt(0);
int num = sheet1.LastRowNum + 1;//获取最大行数
FileStream fout = new FileStream("demo.xls", FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
//获取到已存在的sheet
Sheet sheet = workbook.GetSheet("log");
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行数时+num
Row row = sheet.CreateRow(i + num);
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
workbook.Write(fout);
fs.Close();
fout.Close();
}
}