C# 使用NPOI操作EXCEL

1.添加NOPI

        引用->管理NuGet程序包->添加NOPI

2.相关程序集

3.添加命名空间

using NPOI.HSSF;
using NPOI.XSSF;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

4.从Excel导入的dgv样例

//NPOI读入dgv
private void button1_Click(object sender, EventArgs e)
{
    DataTable dtNpoi = new DataTable();
    string fileName = @"D:\desktop\tmp\test.xlsx";
    string sheetName = "sheet1";
    bool isColumnName = true;
    IWorkbook workBook;

    string fileExt = Path.GetExtension(fileName).ToLower(); //获取文件拓展名
    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        if(fileExt == ".xlsx")  //新版
        {
            workBook = new XSSFWorkbook(fs);
        }
        else if(fileExt == ".xls")  //旧版
        {
            workBook = new HSSFWorkbook(fs);
        }
        else
        {
            workBook=null;
        }

        //实例化sheet（指定名称没加载出来就通过索引加载sheet）
        ISheet sheet = null;
        if(sheetName != null &&  sheetName.Length > 0) 
        {
            sheet = workBook.GetSheet(sheetName);   //获取指定名称的工作表
            if(sheet == null )
            {
                sheet = workBook.GetSheetAt(0); //工作簿没有内容，通过索引0返回空worksheet对象，不会null报错
            }
        }
        else
        {
            sheet = workBook.GetSheetAt(0); //获取第一个工作表
        }


        //获取表头FirstRowNum第一行索引就是0
        IRow header = sheet.GetRow(sheet.FirstRowNum);  //获取第一行
        int startRow = 0;  //数据的第一行索引
        if(isColumnName)    //表示第一行是列名信息
        {
            startRow = sheet.FirstRowNum + 1;   //第一行应该是从1开始的
            //遍历第一行的单元格 列名 0
            for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
            {
                //获取指定索引单元格
                ICell cell = header.GetCell(i);
                if(cell != null)
                {
                    //获取列名的值
                    string cellValue = cell.ToString();
                    if(cellValue != null)
                    {
                        DataColumn col = new DataColumn(cellValue); //记住可以这样子添加
                        dtNpoi.Columns.Add(col);
                    }
                    else
                    {
                        DataColumn col = new DataColumn();
                        dtNpoi.Columns.Add(col);
                    }
                }
            }
        }

        //数据    LastRowNum最后一行的索引 如第九行索引是8
        for (int i = startRow; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i); //获取第i行
            if(row == null )
            {
                continue;
            }

            DataRow dr = dtNpoi.NewRow();

            //遍历每行的单元格
            for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
            {
                if(row.GetCell(j) != null)
                    dr[j] = row.GetCell(j).ToString();
            }
            dtNpoi.Rows.Add(dr);
        }

    }
    this.dataGridView1.DataSource = dtNpoi;
    
}

5.从dgv导入到Excel

DataTable dtTable = this.dataGridView1.DataSource as DataTable; //表1转换为DataTable
string sheetName = "sheet1";
//创建工作簿
IWorkbook wb = new HSSFWorkbook();
//这个创建sheet很巧妙
ISheet sheet = string.IsNullOrEmpty(sheetName) ? wb.CreateSheet("sheet1") : wb.CreateSheet(sheetName);
int rowIndex = 0;
if(dtTable.Columns.Count > 0)
{
    IRow header = sheet.CreateRow(rowIndex);    //创建第一行
    for (int i = 0; i < dtTable.Columns.Count; i++)
    {
        ICell cell = header.CreateCell(i);  //给第一行赋值
        cell.SetCellValue(dtTable.Columns[i].ColumnName);
    }
}

//添加数据
if(dtTable.Rows.Count > 0)
{
    for (int i = 0; i < dtTable.Rows.Count; i++)
    {
        rowIndex++;
        IRow row = sheet.CreateRow(rowIndex);
        for (int j = 0; j < dtTable.Columns.Count; j++)
        {
            ICell cell = row.CreateCell(j);
            cell.SetCellValue(dtTable.Rows[i][j].ToString());
        }
    }
}


//列的大小为自动
for (int i = 0; i < dtTable.Columns.Count; i++)
{
    //sheet.AutoSizeColumn(i);
}

string fileName = @"D:\desktop\tmp\NPOI导出.xls";
using(FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
    wb.Write(fs);   //这个workbook竟然可以直接写
}

MessageBox.Show("导出成功！");