NPOI 导出Execl 自己单独工具类
详见代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using System.Data; using System.IO; using System.Web; using System.Reflection; using System.ComponentModel; namespace NPOIHelper { public class NPOIHelper { /// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dt">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void ExportTableExecl(DataTable dt, string strHeaderText, string strFileName) { using (MemoryStream ms = Exprot(dt, strHeaderText)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// 用于Web 导出 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">文件名</param> public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName) { HttpContext context = HttpContext.Current; context.Response.ContentType = "application/vnd.ms-excel"; context.Response.ContentEncoding = Encoding.UTF8; context.Response.Charset = ""; context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); context.Response.BinaryWrite(Exprot(dtSource, strHeaderText).GetBuffer()); context.Response.End(); } /// <summary> /// 将泛型的list 集合导出Execl /// </summary> /// <typeparam name="T">实体对象</typeparam> /// <param name="list">集合</param> /// <param name="strHeaderText">表头</param> /// <param name="strFileName">文件名字</param> public static void ExportToList<T>(List<T> list, string strHeaderText,string strFileName) { using (MemoryStream ms = ExprotToList<T>(list,strHeaderText)) { 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="dt">数据源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <returns></returns> public static MemoryStream Exprot(DataTable dt, string strHeaderText) { HSSFWorkbook workBook = new HSSFWorkbook(); ISheet sheet = workBook.CreateSheet(); #region 右键属性 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workBook.DocumentSummaryInformation =dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; si.ApplicationName = "创建程序信息"; si.LastAuthor = "最后保存者信息"; si.Comments = "作者信息"; si.Title = "标题信息"; si.Subject = "主题信息"; si.CreateDateTime = DateTime.Now; workBook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workBook.CreateCellStyle(); IDataFormat dateFormat = workBook.CreateDataFormat(); dateStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd"); //获取列宽 int[] arrCollWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrCollWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length; if (intTemp > arrCollWidth[j]) { arrCollWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow item in dt.Rows) { #region 新建表 填充表头 列头 样式 { if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workBook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); //填充填表 ICellStyle headerStyle = workBook.CreateCellStyle(); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = workBook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); // headerRow.Dispose(); } #endregion #region 列头及样式 { IRow headRow = sheet.CreateRow(1); ICellStyle headerStyle = workBook.CreateCellStyle(); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = workBook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headerStyle.SetFont(font); foreach (DataColumn cl in dt.Columns) { headRow.CreateCell(cl.Ordinal).SetCellValue(cl.ColumnName); headRow.GetCell(cl.Ordinal).CellStyle = headerStyle; //设置列宽 sheet.SetColumnWidth(cl.Ordinal, (arrCollWidth[cl.Ordinal]+1)*256); } } #endregion rowIndex = 2; } } #endregion #region 填充内容 { IRow row = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { ICell cell = row.CreateCell(column.Ordinal); string drValue = item[column].ToString(); switch (column.DataType.ToString()) { case "System.String": cell.SetCellValue(drValue); break; case "System.DateTime": DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(dateV); cell.CellStyle = dateStyle; break; case "System.Boolean": bool boolV; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": case "System.Double": double doubleV = 0; double.TryParse(drValue, out doubleV); cell.SetCellValue(doubleV); break; case "System.DBNull": cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workBook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workBook.Dispose(); return ms; } } /// <summary> /// 将list 集合导处 Execl /// </summary> /// <param name="list">泛型集合</param> /// <param name="strHeaderName">表头名字</param> /// <returns></returns> public static MemoryStream ExprotToList<T>(List<T> list, string strHeaderName) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); //创建样式 ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat dataFormat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd"); //IRow headerRow = sheet.CreateRow(0); //headerRow.CreateCell(0).SetCellValue(strHeaderName); //表头样式 IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderName); //填充填表 ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, list[0].GetType().GetProperties().Length - 1)); IRow rows = sheet.CreateRow(1); //获取list属性值 PropertyInfo[] propertyInfo = list[0].GetType().GetProperties(); #region 获取 实体 中的 [DisplayName("姓名")] { //获取 实体 中的 [DisplayName("姓名")] Type entity = list[0].GetType(); for (int i = 0; i < propertyInfo.Length; i++) { var pName = entity.GetProperty(propertyInfo[i].Name.ToString()).GetCustomAttribute<DisplayNameAttribute>(); rows.CreateCell(i).SetCellValue(pName.DisplayName); } } #endregion #region 获取实体的属性值 { //for (int i = 0; i < propertyInfo.Length; i++) //{ // rows.CreateCell(i).SetCellValue(propertyInfo[i].Name.ToString()); //} } #endregion //填充内容 int propertyIndex = 2; foreach (var item in list) { IRow row = sheet.CreateRow(propertyIndex); for (int i = 0; i < propertyInfo.Length; i++) { var obj = propertyInfo[i].GetValue(item, null); row.CreateCell(i).SetCellValue(obj.ToString()); } propertyIndex++; } //宽度自适应 for (int i = 0; i < list.Count; i++) { sheet.AutoSizeColumn(i); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } /// <summary> /// 读取Execl 表格 /// </summary> /// <param name="fileName">文件路径</param> /// <returns></returns> public static DataTable GetReaderExecl(string fileName) { DataTable dt = new DataTable(); HSSFWorkbook workBook; using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { workBook = new HSSFWorkbook(fs); } ISheet sheet = workBook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int i = 0; i < cellCount; i++) { ICell cell = headerRow.GetCell(i); dt.Columns.Add(cell.ToString()); } for (int j = (sheet.FirstRowNum + 1); j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); DataRow dr = dt.NewRow(); for (int i = row.FirstCellNum; i <= cellCount; i++) { if (row.GetCell(i) != null) { dr[i] = row.GetCell(i).ToString(); } } dt.Rows.Add(dr); } return dt; } } }
解析一下 泛型集合导出Execl 中的一个问题
第一步：首先定义一个实体类
public class People { [DisplayName("姓名")] public string name { get; set; } [DisplayName("年龄")] public string Age { get; set; } [DisplayName("性别")] public string Sex { get; set; } }
第二步：直接调用
NPOIHelper.NPOIHelper.ExportToList<People>(this.GetLists(), "list","list.xls");
这样导出结果是这样的
表头显示的是 中文的 也就是实体类中的DisplayName 的值；如果不想使用这个值，想使用属性值得 也是可以的；将上面的代码 注释一下，将下面的 的代码解注一下 就OK 了
修改的代码如下：
/// <summary> /// 将list 集合导处 Execl /// </summary> /// <param name="list">泛型集合</param> /// <param name="strHeaderName">表头名字</param> /// <returns></returns> public static MemoryStream ExprotToList<T>(List<T> list, string strHeaderName) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); //创建样式 ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat dataFormat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd"); //IRow headerRow = sheet.CreateRow(0); //headerRow.CreateCell(0).SetCellValue(strHeaderName); //表头样式 IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderName); //填充填表 ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, list[0].GetType().GetProperties().Length - 1)); IRow rows = sheet.CreateRow(1); //获取list属性值 PropertyInfo[] propertyInfo = list[0].GetType().GetProperties(); #region 获取 实体 中的 [DisplayName("姓名")] { //获取 实体 中的 [DisplayName("姓名")] //Type entity = list[0].GetType(); //for (int i = 0; i < propertyInfo.Length; i++) //{ // var pName = entity.GetProperty(propertyInfo[i].Name.ToString()).GetCustomAttribute<DisplayNameAttribute>(); // rows.CreateCell(i).SetCellValue(pName.DisplayName); //} } #endregion #region 获取实体的属性值 { for (int i = 0; i < propertyInfo.Length; i++) { rows.CreateCell(i).SetCellValue(propertyInfo[i].Name.ToString()); } } #endregion //填充内容 int propertyIndex = 2; foreach (var item in list) { IRow row = sheet.CreateRow(propertyIndex); for (int i = 0; i < propertyInfo.Length; i++) { var obj = propertyInfo[i].GetValue(item, null); row.CreateCell(i).SetCellValue(obj.ToString()); } propertyIndex++; } //宽度自适应 for (int i = 0; i < list.Count; i++) { sheet.AutoSizeColumn(i); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }
效果图：
