首先在项目中引用NPOI,通过管理NuGet程序包，搜索NPOI，选择版本2.3.0（支持.NET Framework 4.0）根据自己项目选择适当版本。
1.NpoiExcelHelper.cs Npoi操作Excel类
using System.Data; using System.IO; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; /// <summary> /// Npoi操作Excel类 /// </summary> public static class NpoiExcelHelper { /// <summary> /// 根据Excel文件类型返回IWorkbook /// </summary> /// <param name="fileName">文件路径/文件名称（含后缀名）</param> /// <param name="rowNum">Excel行数</param> /// <param name="colNum">Excel列数</param> /// <param name="isFirstRowColumn">第一行是否是标题</param> /// <returns></returns> public static IWorkbook GetWorkbook(string fileName, out int rowNum, out int colNum, bool isFirstRowColumn = true) { bool isXlsx = Path.GetExtension(fileName).Equals(".xlsx"); if (isXlsx) { if (isFirstRowColumn) { rowNum = 1048575; } else { rowNum = 1048576; } colNum = 16384; } else { if (isFirstRowColumn) { rowNum = 65535; } else { rowNum = 65536; } colNum = 256; } if (File.Exists(fileName)) { using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { if (isXlsx) { return new XSSFWorkbook(fs); } else { return new HSSFWorkbook(fs); } } } else { if (isXlsx) { return new XSSFWorkbook(); } else { return new HSSFWorkbook(); } } } /// <summary> /// 将DataTable中的数据导入到excel中(第一行是标题) /// 支持根据Excel数据自动分页（多个Sheet） /// </summary> /// <param name="dt">DataTable</param> /// <param name="fileName">文件路径/文件名称（含后缀名）</param> /// <param name="columnFieldText">字段对应中文 顺序需要跟Excel中数据顺序一致</param> /// <param name="sheetName">Excel中Sheet名称（多个sheet时 名字后面自动加上数字序号）</param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable dt, string fileName, string[,] columnFieldText = null, string sheetName = null) { int rowNum = 0; int colNum = 0; IWorkbook workbook = GetWorkbook(fileName, out rowNum, out colNum); var recordNum = dt.Rows.Count; int totalPage = recordNum % rowNum == 0 ? recordNum / rowNum : recordNum / rowNum + 1; for (var p = 0; p < totalPage; p++) { if (string.IsNullOrEmpty(sheetName)) { sheetName = dt.TableName; } if (totalPage > 1) { if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet"; } sheetName = sheetName + (p + 1).ToString(); } else { if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; } } ISheet sheet = workbook.CreateSheet(sheetName);//创建工作表 #region 标题 IRow row = sheet.CreateRow(0);//在工作表中添加一行 if (columnFieldText != null) { var dataColumn = columnFieldText.GetLength(0); if (dataColumn <= colNum) { for (int m = 0; m < dataColumn; m++) { ICell cell = row.CreateCell(m);//在行中添加一列 cell.SetCellValue(columnFieldText[m, 1]);//设置列的内容 } } else { //数据列数超过了Excel的列数 } } else { var dataColumn = dt.Columns.Count; if (dataColumn <= colNum) { for (int i = 0; i < dataColumn; i++) { ICell cell = row.CreateCell(i);//在行中添加一列 cell.SetCellValue(dt.Columns[i].ColumnName);//设置列的内容 } } else { //数据列数超过了Excel的列数 } } #endregion #region 填充数据 int startIndex = p * rowNum; int endindex = (p + 1) * rowNum - 1; if (endindex >= recordNum) { endindex = recordNum - 1; } for (int i = startIndex; i <= endindex; i++)//遍历DataTable行 { DataRow dataRow = dt.Rows[i]; row = sheet.CreateRow(i - startIndex + 1);//在工作表中添加一行 if (columnFieldText != null) { var dataColumn = columnFieldText.GetLength(0); if (dataColumn <= colNum) { for (int m = 0; m < dataColumn; m++) { ICell cell = row.CreateCell(m);//在行中添加一列 cell.SetCellValue(dataRow[columnFieldText[m, 0]].ToString());//设置列的内容 } } else { //数据列数超过了Excel的列数 } } else { var dataColumn = dt.Columns.Count; if (dataColumn <= colNum) { for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列 { ICell cell = row.CreateCell(j);//在行中添加一列 cell.SetCellValue(dataRow[j].ToString());//设置列的内容 } } else { //数据列数超过了Excel的列数 } } } #endregion } #region 输出Excel using (MemoryStream stream = new MemoryStream()) { workbook.Write(stream); return stream.ToArray(); } #endregion } /// <summary> /// 将excel中的数据导入到DataTable中(第一行是标题) /// 支持多个sheet数据导入（建议多个sheet的数据格式保持一致，将没有数据的sheet删除） /// </summary> /// <param name="fileName">文件路径（含文件名称后缀名）</param> /// <param name="columnFieldText">字段对应中文 顺序需要跟Excel中数据顺序一致</param> /// <param name="sheetName">指定Excel中Sheet名称 如果为null时，读取所有sheet中的数据</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelToDataTable(string fileName, string[,] columnFieldText = null, string sheetName = null) { DataTable data = new DataTable(); int rowNum = 0; int colNum = 0; IWorkbook workbook = GetWorkbook(fileName, out rowNum, out colNum); for (int e = 0; e < workbook.NumberOfSheets; e++) { ISheet sheet = workbook.GetSheetAt(e); if (sheet != null) { var currentSheetIndex = 0; if (!string.IsNullOrEmpty(sheetName)) { if (sheet.SheetName == sheetName) { currentSheetIndex = e; } } IRow firstRow = sheet.GetRow(0); if (firstRow != null) { int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 var dataColumn = columnFieldText != null ? columnFieldText.GetLength(0) : cellCount; int startRow = sheet.FirstRowNum; if (dataColumn <= colNum) { if (e == currentSheetIndex) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn((columnFieldText != null ? columnFieldText[i, 0] : cellValue)); data.Columns.Add(column); } } } } startRow = sheet.FirstRowNum + 1; //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } else { //数据列数超过了Excel的列数 } } if (!string.IsNullOrEmpty(sheetName)) { if (sheet.SheetName == sheetName) { break; } } } } return data; } }
View Code
2.WEB项目的调用方法：
（1）数据导出到Excel中（支持根据DataTable数据及Excel自动分成多个Sheet）
调用方法：
int record = 500; DataTable data = CreateDataTable(record); string fileName = "客户明细_" + DateTime.Now.ToString("MMddhhmmss") + ".xls"; string sheetName = "客户明细"; string[,] columnFieldText = new[,]{ { "ID", "编号" }, { "Name", "姓名" }, { "CreateTime", "创建时间" } }; //string[,] columnFieldText = null; var buf = NpoiExcelHelper.DataTableToExcel(data, fileName, columnFieldText, sheetName); Response.Buffer = true; Response.Clear(); Response.ClearHeaders(); Response.ClearContent(); Response.Charset = "UTF8"; Response.ContentEncoding = Encoding.UTF8; Response.ContentType = "application/vnd.ms-excel"; string browser = Request.Browser.Browser; if (browser.Contains("InternetExplorer")) Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8)); else Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.AddHeader("Content-Length", buf.Length.ToString()); Response.Flush(); Response.BinaryWrite(buf);
/// <summary> /// 创建DataTable对象 /// </summary> public DataTable CreateDataTable(int record) { //创建DataTable DataTable dt = new DataTable("NewDt"); //创建自增长的ID列 DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32")); dc.AutoIncrement = true; //自动增加 dc.AutoIncrementSeed = 1; //起始为1 dc.AutoIncrementStep = 1; //步长为1 dc.AllowDBNull = false; //非空 //创建其它列表 dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String"))); dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime"))); DataRow dr; for (int i = 0; i < record; i++) { dr = dt.NewRow(); dr["Name"] = "名字" + i.ToString(); dr["CreateTime"] = DateTime.Now; dt.Rows.Add(dr); } return dt; }
（2）Excel中数据导入DataTable中（支持指定Sheet名称 / 多个数据格式一致的Shee）
string fileName = "客户明细_0213023109.xls"; string sheetName = "客户明细1"; string[,] columnFieldText = new[,]{ { "ID", "编号" }, { "Name", "姓名" }, { "CreateTime", "创建时间" } }; //string[,] columnFieldText = null; var dt = NpoiExcelHelper.ExcelToDataTable(Server.MapPath(fileName), columnFieldText, sheetName);
以上基本实现WEB通过NPOI操作Excel数据导入导出的功能。其他可自行研究。
