使用NPOI操纵Excle，并输入到客户端
NPOI下载：https://files.cnblogs.com/files/gosky/NPOI_2.2.0.0.zip
导入以下5个引用：
ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll
1.将Excle数据导入到DataTable中
新建工具类： 根据上传文件后缀名判断 xls使用 ImportExcelFile2003方法 xlsx使用ImportExcelFile2007方法
using NPOI.XSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using NPOI.SS.Formula.Eval; /// <summary> /// NPOIExcelHelper 的摘要说明 /// </summary> public class NPOIExcelHelper { public static DataTable ImportExcelFile2007(string filePath) { XSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file);; } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);; System.Collections.IEnumerator rows = sheet.GetRowEnumerator();; DataTable dt = new DataTable();; rows.MoveNext();; XSSFRow row = (XSSFRow)rows.Current; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());; //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString());; } while (rows.MoveNext()) { row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow();; for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i);; if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString();; } } dt.Rows.Add(dr);; } return dt; } public static DataTable ImportExcelFile2003(string filePath) { HSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file);; } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);; System.Collections.IEnumerator rows = sheet.GetRowEnumerator();; DataTable dt = new DataTable();; rows.MoveNext();; HSSFRow row = (HSSFRow)rows.Current; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());; //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString());; } while (rows.MoveNext()) { row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow();; for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i);; if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString();; } } dt.Rows.Add(dr);; } return dt; } }
2.将数据导出到Excle
引用命名空间：
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
代码实例：
//创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //创建标签 ISheet sheet = workbook.CreateSheet("sheet1"); //新建表头 IRow rowHead = sheet.CreateRow(0); rowHead.CreateCell(0, CellType.String).SetCellValue("序号"); rowHead.CreateCell(1, CellType.String).SetCellValue("学生姓名"); rowHead.CreateCell(2, CellType.String).SetCellValue("性别"); rowHead.CreateCell(3, CellType.String).SetCellValue("生源地"); rowHead.CreateCell(4, CellType.String).SetCellValue("身份证"); rowHead.CreateCell(5, CellType.String).SetCellValue("准考证"); rowHead.CreateCell(6, CellType.String).SetCellValue("专业名称"); rowHead.CreateCell(7, CellType.String).SetCellValue("专业方向"); rowHead.CreateCell(8, CellType.String).SetCellValue("来源"); rowHead.CreateCell(9, CellType.String).SetCellValue("生源学校"); rowHead.CreateCell(10, CellType.String).SetCellValue("审核状态"); rowHead.CreateCell(11, CellType.String).SetCellValue("录取状态"); //循环填充内容 for (int i = 0; i < table.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); DataRow datarow = table.Rows[i]; row.CreateCell(0, CellType.String).SetCellValue(Convert.ToString(datarow["rn"])); row.CreateCell(1, CellType.String).SetCellValue(Convert.ToString(datarow["StuName"])); row.CreateCell(2, CellType.String).SetCellValue(Constants.sex[datarow["StuSex"].ToString()]); row.CreateCell(3, CellType.String).SetCellValue(Constants.stuAddr[datarow["StuProvince"].ToString()] + Constants.stuAddr[datarow["StuCity"].ToString()]); row.CreateCell(4, CellType.String).SetCellValue(Convert.ToString(datarow["StuCardNo"])); row.CreateCell(5, CellType.String).SetCellValue(Convert.ToString(datarow["StuZKNo"])); row.CreateCell(6, CellType.String).SetCellValue(Convert.ToString(datarow["zyname"])); row.CreateCell(7, CellType.String).SetCellValue(Convert.ToString(datarow["zyfx"])); row.CreateCell(8, CellType.String).SetCellValue(Constants.stuFrom[datarow["StuFrom"].ToString()]); row.CreateCell(9, CellType.String).SetCellValue(Convert.ToString(datarow["StuSchool"])); row.CreateCell(10, CellType.String).SetCellValue(Constants.stuState[datarow["State"].ToString()]); row.CreateCell(11, CellType.String).SetCellValue(Constants.stuAdmit[datarow["Admit"].ToString()]); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); //写入内存 workbook.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); //输出到客户端 Response.BinaryWrite(ms.ToArray()); workbook = null; ms.Close(); ms.Dispose();
导出结果如图：
3.将服务器上的文件输出到客户端
//读取文件 FileStream fileStream = new FileStream(Server.MapPath("~/Uploads/test.xlsx"), FileMode.Open); long fileSize = fileStream.Length; byte[] fileBuffer = new byte[fileSize]; fileStream.Read(fileBuffer, 0, (int)fileSize); //如果不写fileStream.Close()语句，用户在下载过程中选择取消，将不能再次下载 fileStream.Close(); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMdd"))); Response.AddHeader("Content-Length", fileSize.ToString()); Response.BinaryWrite(fileBuffer); Response.Flush(); Response.Close();
将服务器上 /Uploads/test.xlsx 文件 输出到客户端并使用日期命名
