简单回顾NPOI读取Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
HSSFWorkbook workbook = null;
using (FileStream fs = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
    //把xls文件读入workbook变量里，之后就可以关闭了  
    workbook = new XSSFWorkbook(fs);//07版本(.xlsx)
    //workbook = new HSSFWorkbook(fs);//03版本(.xls)
    fs.Close();
}
//读取工作表
ISheet worksheet = workbook.GetSheet("sheet1");  //读取工作表"sheet1"
ISheet worksheet = workbook.GetSheetAt(0);       //读取第一个sheet

//总行数
allRows = worksheet.LastRowNum;//总行数-1  起始为0

//读取单元格
worksheet.GetRow(x).GetCell(y)
    
//Datatable生成Excel
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("sheet1");
//创建第一行标题
IRow firstrow = sheet.CreateRow(0);
for (int columns = 0; columns <= dt.Columns.Count - 1; columns++)
{
    firstrow.CreateCell(columns).SetCellValue(dt.Columns[columns].ColumnName);
}
for (int i = 1; i <= dt.Rows.Count - 1; i++)
{
    IRow dataRow = sheet.CreateRow(i);
    for (int j = 0; j <= dt.Columns.Count - 1; j++)
    {
        //创建列，并写入值
        ICell cell = dataRow.CreateCell(j);
        cell.SetCellValue(dt.Rows[i][j].ToString());
    }
}
//打开指定的xls文件，excelFullPath为待转换excel文件的全路径（包括路径和带后缀的名称，如：C:\Users\Bin\Desktop\txt\八里河景区.xls）
using (FileStream fs = File.OpenWrite(savefilename))
{
    xssfworkbook.Write(fs);
}