////// NPOI帮助类 /// public class NPOIHelper { public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strFileName) { string fileExt = Path.GetExtension(strFileName).ToLower(); IWorkbook workbook; if (fileExt == ".xlsx") workbook = new XSSFWorkbook(); else workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); //#region 右击文件 属性信息 //不支持2007 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "文件作者信息"; //填加xls文件作者信息 // si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 // si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 // si.Comments = "作者信息"; //填加xls文件作者信息 // si.Title = "标题信息"; //填加xls文件标题信息 // si.Subject = "主题信息";//填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} //#endregion var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 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 表头及样式 { var headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { var headerRow = sheet.CreateRow(1); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var font = 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); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.CellStyle = dateStyle; break; case "System.DateTime"://日期类型 DateTime dateV; if (DateTime.TryParse(drValue, out dateV)) { newCell.SetCellValue(dateV); } else { //newCell.SetCellValue(""); } 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 (NpoiMemoryStream ms = new NpoiMemoryStream()) { ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; //ms.AllowClose = true; //sheet.CloneSheet(workbook); //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; } } public static MemoryStream Export(Listlist, string strHeaderText, string strFileName) { string fileExt = Path.GetExtension(strFileName).ToLower(); IWorkbook workbook; if (fileExt == ".xlsx") workbook = new XSSFWorkbook(); else workbook = new HSSFWorkbook(); for (int table_index = 0; table_index < list.Count; table_index++) { var dtSource = list[table_index].dt; ISheet sheet = workbook.CreateSheet(list[table_index].sheetname); //#region 右击文件 属性信息 //不支持2007 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "文件作者信息"; //填加xls文件作者信息 // si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 // si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 // si.Comments = "作者信息"; //填加xls文件作者信息 // si.Title = "标题信息"; //填加xls文件标题信息 // si.Subject = "主题信息";//填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} //#endregion var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 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(list[table_index].sheetname); } #region 表头及样式 { var headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { var headerRow = sheet.CreateRow(1); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; var font = 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); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.CellStyle = dateStyle; break; case "System.DateTime"://日期类型 DateTime dateV; if (DateTime.TryParse(drValue, out dateV)) { newCell.SetCellValue(dateV); } else { //newCell.SetCellValue(""); } 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 (NpoiMemoryStream ms = new NpoiMemoryStream()) { ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; //ms.AllowClose = true; //sheet.CloneSheet(workbook); //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; } } /// /// 用于Web导出 /// /// 源DataTable /// 表头文本 /// 文件名 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, HttpContext context = null) { HttpContext curContext = HttpContext.Current; if (context != null) curContext = context; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; //curContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 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, strHeaderText, strFileName).GetBuffer()); curContext.Response.Output.Flush(); curContext.Response.End(); } ////// 用于Web导出 /// /// 源DataTable /// 表头文本 /// 文件名 public static void ExportByWeb(ListdtSource, string strHeaderText, string strFileName, HttpContext context = null) { HttpContext curContext = HttpContext.Current; if (context != null) curContext = context; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; //curContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; 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, strHeaderText, strFileName).GetBuffer()); curContext.Response.Output.Flush(); curContext.Response.End(); } /// /// 实体列表转换成DataTable /// ///实体 /// 实体列表 ///public static DataTable ListToDataTable (IList list) where T : class { if (list == null || list.Count <= 0) { return null; } DataTable dt = new DataTable(typeof(T).Name); DataColumn column; DataRow row; PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); int length = myPropertyInfo.Length; bool createColumn = true; foreach (T t in list) { if (t == null) { continue; } row = dt.NewRow(); for (int i = 0; i < length; i++) { PropertyInfo pi = myPropertyInfo[i]; string name = pi.Name; if (createColumn) { try { column = new DataColumn(name, pi.PropertyType); dt.Columns.Add(column); } catch (Exception ex) { var n = ""; } } row[name] = pi.GetValue(t, null); } if (createColumn) { createColumn = false; } dt.Rows.Add(row); } return dt; } //新建类 重写Npoi流方法 (生成.xlsx 必须使用这个转换 否则无法打开，目前只有此方法可解决.xlsx问题) public class NpoiMemoryStream : MemoryStream { public NpoiMemoryStream() { AllowClose = true; } public bool AllowClose { get; set; } public override void Close() { if (AllowClose) base.Close(); } } public class MergeModel { /// /// Sheet名称 /// public string sheetname { get; set; } ////// 数据 /// public DataTable dt { get; set; } } }
//例子
var table_orders = bll.GetTable(where, "id"); var list = new List(); list.Add(new Models.NPOIHelper.MergeModel() { sheetname = "订单记录", dt = table_orders }); List ids = new List (); foreach (DataRow row in table_orders.Rows) { ids.Add(Models.Common.ObjectToInt(row["id"].ToString())); } var table_ordergoods = bll.GetTable_Order_Goods(" order_id in (" + string.Join(",", ids) + ") ", "id"); list.Add(new Models.NPOIHelper.MergeModel() { sheetname = "订单详情", dt = table_ordergoods }); //导出 Models.NPOIHelper.ExportByWeb(list, "订单", "订单" + DateTime.Now.ToString("yyyyMMdd") + ".xls");