1.添加NOPI
引用->管理NuGet程序包->添加NOPI
2.相关程序集
3.添加命名空间
using NPOI.HSSF;
using NPOI.XSSF;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;4.样例
//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第一行索引就是0IRow header = sheet.GetRow(sheet.FirstRowNum); //获取第一行int startRow = 0; //数据的第一行索引if(isColumnName) //表示第一行是列名信息{startRow = sheet.FirstRowNum + 1; //第一行应该是从1开始的//遍历第一行的单元格 列名 0for (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最后一行的索引 如第九行索引是8for (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;}
