通过NPOI操作Excel
最近在做的一个项目中需要生成Excel，通过学习使用NPOI实现了相关需求，写了一个简便操作的类，记录如下：
public class NPOIHelperForExcel { #region excel文件属性 //作者 public string Author { get; set; } //标题 public string Title { get; set; } //主题 public string Subject { get; set; } //标记 public string Keywords { get; set; } //创建程序信息 public string ApplicationName { get; set; } //最后一次保存者 public string LastAuthor { get; set; } //备注 public string Comments { get; set; } //创建内容的时间 public DateTime? CreateDateTime { get; set; } //最后一次打印的时间 public DateTime? LastPrinted { get; set; } //最后一次保存的时间 public DateTime? LastSaveDateTime { get; set; } //公司 public string Company { get; set; } //管理者 public string Manager { get; set; } //比例 public bool Scale { get; set; } #endregion #region 导出，将DataTable导出为Excel文件 /// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="headerTextList">表头摘要信息</param> /// <param name="strFileName">保存位置</param> public void Export(DataTable dtSource, List<String> headerTextList, string strFileName) { using (MemoryStream ms = Export(dtSource, headerTextList)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="headerTextList">表头摘要信息</param> public MemoryStream Export(DataTable dtSource, List<String> headerTextList) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); //设置Excel文件属性信息 SetFileProperty(workbook); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //计算列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } //获取每一列的最大列宽 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 for (int i = 0; i < headerTextList.Count; i++) { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i); headerRow.HeightInPoints = 18; headerRow.CreateCell(0).SetCellValue(headerTextList[i]); HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Left; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 14; //font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = headerTextList.Count + 1; } #endregion #region 填充表格内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } /// <summary> /// 用于Web导出 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="headerTextList">表头摘要信息</param> /// <param name="strFileName">文件名</param> public void ExportByWeb(DataTable dtSource, List<String> headerTextList, string strFileName) { HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(Export(dtSource, headerTextList).GetBuffer()); curContext.Response.End(); } #endregion #region 导入，将excel读取到DataTable中 /// <summary> /// 读取excel,默认第一行为表头 /// </summary> /// <param name="strFileName">excel文件路径</param> /// <returns>DataTable</returns> public DataTable Import(string strFileName) { return Import(strFileName, 0); } /// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheetNum">sheet索引，以0开始</param> /// <returns>DataTable</returns> public DataTable Import(string strFileName, int sheetNum) { return Import(strFileName, sheetNum, 1, 1); } /// <summary> /// 读取excel /// </summary> /// <param name="strFileName">excel文件路径</param> /// <param name="sheetNum">sheet索引，以0开始</param> /// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param> /// <param name="startColNum">起始列号</param> /// <returns>DataTable</returns> public DataTable Import(string strFileName, int sheetNum, int startRowNum, int startColNum) { return Import(strFileName, sheetNum, startRowNum, -1, startColNum, -1); } /// <summary> /// 读取excel /// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1； /// headerRow.LastCellNum属性获取的是Excel中该行的列数 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <param name="sheetNum">工作表索引，以0开始</param> /// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param> /// <param name="endRowNum">结束行号</param> /// <param name="startColNum">起始列号</param> /// <param name="endColNum">结束列号</param> /// <returns>DataTable</returns> public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } int sheetCount = hssfworkbook.NumberOfSheets; sheetNum = sheetNum < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum; HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum); HSSFRow headerRow = null; #region 行列号范围验证 startColNum = startColNum < 0 ? 0 : startColNum; startRowNum = startRowNum < 1 ? 1 : startRowNum; headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1); endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum; endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum; #endregion //添加列 for (int j = startColNum - 1; j < endColNum; j++) { HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } //添加行 for (int i = startRowNum; i <= endRowNum - 1; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = startColNum - 1; j < endColNum; j++) { if (row.GetCell(j) != null) dataRow[j - startColNum + 1] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } #endregion #region 单元格写入 ///// <summary> ///// 给指定单元格写入内容 ///// </summary> ///// <param name="workBook"></param> ///// <param name="sheetName"></param> ///// <param name="rowNum"></param> ///// <param name="colNum"></param> ///// <param name="content"></param> //public void WriteCell(HSSFWorkbook workBook, string sheetName, int rowNum, int colNum, string content) //{ // if (workBook == null) // { // throw new Exception("workBook不能为null"); // } // WriteCell(workBook, workBook.GetSheetIndex(sheetName), rowNum, colNum, content); //} ///// <summary> ///// 给指定单元格写入内容 ///// </summary> ///// <param name="workBook"></param> ///// <param name="sheetNum"></param> ///// <param name="rowNum"></param> ///// <param name="colNum"></param> ///// <param name="content"></param> ///// <returns></returns> //public void WriteCell(HSSFWorkbook workBook, int sheetNum, int rowNum, int colNum, string content) //{ // if (workBook == null) // { // throw new Exception("workBook不能为null"); // } // if (workBook.NumberOfSheets < sheetNum || sheetNum < 0) // { // throw new Exception("指定的sheet不存在"); // } // ISheet sheet = workBook.GetSheetAt(sheetNum - 1); // HSSFRow row = (HSSFRow)sheet.GetRow(rowNum) ?? (HSSFRow)sheet.CreateRow(rowNum - 1); // HSSFCell cell = (HSSFCell)row.CreateCell(6); // cell.SetCellValue(content); // //using (MemoryStream ms = new MemoryStream()) // //{ // // workBook.Write(ms); // // ms.Flush(); // // ms.Position = 0; // // using (FileStream fs = new FileStream("测试行列写入.xls", FileMode.Create, FileAccess.Write)) // // { // // byte[] data = ms.ToArray(); // // fs.Write(data, 0, data.Length); // // fs.Flush(); // // } // //} // //return workBook; //} #endregion /// <summary> /// 设置Excel文件属性信息 /// </summary> /// <param name="workbook"></param> private void SetFileProperty(HSSFWorkbook workbook) {
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = this.Company ?? "";
dsi.Scale = this.Scale;
dsi.Manager = this.Manager ?? "";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = this.Author ?? "";
si.ApplicationName = this.ApplicationName ?? "";
si.LastAuthor = this.LastAuthor ?? "";
si.Comments = this.Comments ?? "";
si.Title = this.Title ?? "";
si.Subject = this.Subject ?? "";
si.CreateDateTime = this.CreateDateTime ?? DateTime.Now;
si.Keywords = this.Keywords ?? "";
si.LastAuthor = this.LastAuthor ?? "";
si.LastPrinted = this.LastPrinted ?? DateTime.Now;
si.LastSaveDateTime = this.LastSaveDateTime ?? DateTime.Now;
//上面的属性必须赋值，不能为null，不然执行到Export方法的"workbook.Write(ms);"处会导致空引用错误。
workbook.SummaryInformation = si;
}
}
