/// <summary> /// 文件导出,不需要模板 /// </summary> /// <typeparam name="T">类名</typeparam> /// <param name="title">文件标题</param> /// <param name="head">表头</param> /// <param name="list">内容</param> /// <param name="fileName">文件名</param> /// <returns></returns> public static MemoryStream StatisticalReport_Exl <T>(string title, List <string> head, List <T> list, string fileName) { IWorkbook workbook = null; string fileExt = Path.GetExtension(fileName).ToLower(); if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return(new MemoryStream());; } ISheet sheet = workbook.CreateSheet("Sheet0"); var titlestyle = GetDefaultStyle(workbook); var titlefont = workbook.CreateFont(); titlefont.Boldweight = short.MaxValue; titlefont.FontHeightInPoints = 14; titlestyle.SetFont(titlefont); titlestyle.Alignment = HorizontalAlignment.Center;//水平对齐 var indexcellstyle = GetDefaultStyle(workbook); var cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; cellfont.FontHeightInPoints = 10; indexcellstyle.SetFont(cellfont); var valuecellstyle = GetDefaultStyle(workbook); var cellfontv = workbook.CreateFont(); cellfontv.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal; cellfontv.FontHeightInPoints = 10; valuecellstyle.Alignment = HorizontalAlignment.Center; valuecellstyle.SetFont(cellfontv); int valueIndex = 0; //标题 IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); CreatAddCell(row, title, titlestyle, 0); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, head.Count - 1)); //表头 int rowHead = 1; IRow rowhead = sheet.CreateRow(rowHead); foreach (string cellhead in head) { CreatAddCell(rowhead, cellhead, indexcellstyle, valueIndex); valueIndex++; } DataTable dt = TypeHelper.ListToDataTable <T>(list); //内容绑定 int rowIndex = 2; foreach (DataRow dataRow in dt.Rows) { IRow rowContent = sheet.CreateRow(rowIndex); valueIndex = 0; for (var i = 0; i < dt.Columns.Count; i++) { var value = dataRow[i]; if (value == null) { value = ""; } else { value = value.GetType() == typeof(DateTime) ? Convert.ToDateTime(value).ToString("yyyy-MM-dd") : value.ToString(); } CreatAddCell(rowContent, value.ToString(), valuecellstyle, valueIndex); valueIndex++; } rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); }
/// <summary> /// Excel文件导出,引入模板 /// </summary> /// <param name="dt">数据集</param> /// <param name="TempletFileName">模板文件</param> /// <param name="sheetName">sheet名称</param> /// <param name="rowBody">内容开始行索引</param> /// <returns></returns> public static MemoryStream DataTableToExcel(DataTable dt, string TempletFileName, string sheetName, int rowBody) { try { FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet(sheetName); if (dt != null && dt.Rows.Count > 0) { int row = dt.Rows.Count; int cell = dt.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < cell; j++) { /* * CELL_TYPE_NUMERIC 数值型 0 * CELL_TYPE_STRING 字符串型 1 * CELL_TYPE_FORMULA 公式型 2 * CELL_TYPE_BLANK 空值 3 * CELL_TYPE_BOOLEAN 布尔型 4 * CELL_TYPE_ERROR 错误 */ if (dt.Columns[j].DataType == typeof(decimal)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToDouble(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(DateTime)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToDateTime(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(bool)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Boolean); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToBoolean(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(byte)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToByte(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(Int16)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt16(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(Int32)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt32(dt.Rows[i][j])); } else if (dt.Columns[j].DataType == typeof(Int64)) { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt64(dt.Rows[i][j])); } else { ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String); ws.GetRow(rowBody + i).GetCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } } } ws.ForceFormulaRecalculation = true; NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; hssfworkbook.Write(ms); ms.Flush(); ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); } catch (Exception ex) { return(new MemoryStream()); } }