使用 NPOI 插件
//不带样式 public static string ToExcle(List<DataTable> datas, string fileName = "report", string exportpath = "", int from = 1) { //获取文件物理路径 string xlsPath = Path.Combine(Environment.CurrentDirectory, exportpath, fileName); //判断路径，不存在则创建 if (!Directory.Exists(Path.Combine(Environment.CurrentDirectory, exportpath))) //存在则删除 { Directory.CreateDirectory(Path.Combine(Environment.CurrentDirectory, exportpath)); } FileStream file = new FileStream(xlsPath, FileMode.OpenOrCreate, FileAccess.ReadWrite); //获取文件虚拟路径 string saveFileName = Path.Combine(exportpath, fileName); //创建文件 StreamWriter objStreamWriter = new StreamWriter(file, Encoding.Unicode); IWorkbook hssfworkbook = new XSSFWorkbook(); //遍历Excel标签 var n = 0; foreach (DataTable data in datas) { if (data.Rows.Count > 0) { if (string.IsNullOrEmpty(data.TableName)) data.TableName = "Sheet1"; //标签页名称 ISheet sheet = hssfworkbook.CreateSheet(data.TableName); n++; //标题行 XSSFRow dataRow = sheet.CreateRow(0) as XSSFRow; for (int i = 0; i < data.Columns.Count; i++) { if (!string.IsNullOrWhiteSpace(data.Columns[i].ColumnName)) { dataRow.CreateCell(i).SetCellValue(data.Columns[i].ColumnName); //列值 } } //内容行 for (int i = 0; i < data.Rows.Count; i++) { dataRow = sheet.CreateRow(i + from) as XSSFRow; for (int j = 0; j < data.Columns.Count; j++) { Regex regex = new Regex(@"<(.| )+?>"); var result = regex.Replace(data.Rows[i][j].ToString(), ""); dataRow.CreateCell(j).SetCellValue(result); //列值 } } sheet.ForceFormulaRecalculation = true; if (data.Columns.Contains("ID（勿删）")) sheet.SetColumnHidden(0, true); if (data.Columns.Contains("PID（勿删）")) sheet.SetColumnHidden(1, true); } } //hssfworkbook.RemoveSheetAt(0); //file.Dispose(); objStreamWriter.Dispose(); using (FileStream fileWrite = new FileStream(xlsPath, FileMode.Create)) { hssfworkbook.Write(fileWrite); fileWrite.Dispose(); } return saveFileName; }
//带样式 public static string ToStyleExcle(List<DataTable> datas, int type, string fileName = "report", string exportpath = "", int from = 1, int selection = 0, string usercode = "") { //获取文件物理路径 string xlsPath = Path.Combine(Environment.CurrentDirectory, exportpath, fileName); //判断路径，不存在则创建 if (!Directory.Exists(Path.Combine(Environment.CurrentDirectory, exportpath))) //存在则删除 { Directory.CreateDirectory(Path.Combine(Environment.CurrentDirectory, exportpath)); } FileStream file = new FileStream(xlsPath, FileMode.OpenOrCreate, FileAccess.ReadWrite); //获取文件虚拟路径 string saveFileName = Path.Combine(exportpath, fileName); //创建文件 StreamWriter objStreamWriter = new StreamWriter(file, Encoding.Unicode); IWorkbook hssfworkbook = new XSSFWorkbook(); //遍历Excel标签 var n = 0; foreach (DataTable data in datas) { if (data.Rows.Count > 0) { //标签页名称 ISheet sheet = hssfworkbook.CreateSheet(data.TableName); n++; //背景 ICellStyle style = hssfworkbook.CreateCellStyle(); style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; style.FillPattern = FillPattern.SolidForeground; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; ICellStyle style1 = hssfworkbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index; style1.FillPattern = FillPattern.SolidForeground; style1.BorderBottom = BorderStyle.Thin; style1.BorderLeft = BorderStyle.Thin; style1.BorderRight = BorderStyle.Thin; style1.BorderTop = BorderStyle.Thin; style1.Alignment = HorizontalAlignment.Center; style1.VerticalAlignment = VerticalAlignment.Center; ICellStyle style2 = hssfworkbook.CreateCellStyle(); style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; style2.FillPattern = FillPattern.SolidForeground; style2.BorderBottom = BorderStyle.Thin; style2.BorderLeft = BorderStyle.Thin; style2.BorderRight = BorderStyle.Thin; style2.BorderTop = BorderStyle.Thin; style2.Alignment = HorizontalAlignment.Center; style2.VerticalAlignment = VerticalAlignment.Center; //边框 XSSFCellStyle cellStyleTitleNew = hssfworkbook.CreateCellStyle() as XSSFCellStyle; cellStyleTitleNew.BorderBottom = BorderStyle.Thin; cellStyleTitleNew.BorderLeft = BorderStyle.Thin; cellStyleTitleNew.BorderRight = BorderStyle.Thin; cellStyleTitleNew.BorderTop = BorderStyle.Thin; //设置单元格的样式：水平对齐居中 cellStyleTitleNew.Alignment = HorizontalAlignment.Center; cellStyleTitleNew.VerticalAlignment = VerticalAlignment.Center; //标题行 XSSFRow dataRow = sheet.CreateRow(0) as XSSFRow; for (int i = 0; i < data.Columns.Count; i++) { sheet.SetColumnWidth(i, 100 * 60); if (!string.IsNullOrWhiteSpace(data.Columns[i].ColumnName)) { dataRow.CreateCell(i).SetCellValue(data.Columns[i].ColumnName); //列值 int width = data.Columns[i].ColumnName.Length; dataRow.GetCell(i).CellStyle = cellStyleTitleNew; } if (selection == 1) { if (i > 1) { dataRow.GetCell(i).CellStyle = style; } } } if (type == 3) { dataRow.Height = 25 * 20;//制定行高25 } if (selection == 1) { dataRow.Height = 25 * 20;//制定行高25 } string analyte = ""; //内容行 for (int i = 0; i < data.Rows.Count; i++) { dataRow = sheet.CreateRow(i + from) as XSSFRow; for (int j = 0; j < data.Columns.Count; j++) { //列值 Regex regex = new Regex(@"<(.| )+?>"); var result = regex.Replace(data.Rows[i][j].ToString(), ""); var res = data.Rows[i][j]; dataRow.CreateCell(j).SetCellValue(result); dataRow.GetCell(j).CellStyle = cellStyleTitleNew; if (type == 3) { if (i == 2 && j > 2) { dataRow.GetCell(j).CellStyle = style; } } //if (i > 2 && j > 3) //{ // var aa = data.Rows[i][9].ToString(); //} if (selection == 1) { if (i >= 0 && j > 1 && !string.IsNullOrEmpty(result) && result == "/") { dataRow.GetCell(j).CellStyle = style1; } } try { analyte = data.Rows[2][j].ToString(); //1。实验室空白：分析项"干物质含量"为空时，标底色 //2。非实验室空白：除"干物质含量"分析项外，标底色 if (i > 2 && j > 2 && string.IsNullOrEmpty(result)) { if (Convert.IsDBNull(res)) { result = "-"; dataRow.CreateCell(j).SetCellValue(result); dataRow.GetCell(j).CellStyle = style2; } else if (!(data.Rows[i]["监测点名称/QC类型"].ToString() != "实验室空白" && data.Rows[2][j].ToString().Contains("干物质含量"))) { dataRow.GetCell(j).CellStyle = style1; } } else if (i > 2 && j > 2 && usercode.Equals("luoy") && (analyte.IndexOf("称量样品质量") > -1 || analyte.IndexOf("萃取液体积") > -1)) { dataRow.GetCell(j).CellStyle = style1; } } catch { } } if (type == 3) { dataRow.Height = 25 * 20;//制定行高25 } if (selection == 1) { dataRow.Height = 25 * 20;//制定行高25 } } ////设置自适应宽度 //for (int columnNum = 0; columnNum <= data.Columns.Count; columnNum++) //{ // int columnWidth = (sheet.GetColumnWidth(columnNum) / 256) + 10; // for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) // { // IRow currentRow = sheet.GetRow(rowNum); // if (currentRow.GetCell(columnNum) != null) // { // NPOI.SS.UserModel.ICell currentCell = currentRow.GetCell(columnNum); // int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; // if (columnWidth < length) // { // columnWidth = length; // } // } // } // sheet.SetColumnWidth(columnNum, columnWidth * 256); //} sheet.ForceFormulaRecalculation = true; if (data.Columns.Contains("ID（勿删）")) sheet.SetColumnHidden(0, true); if (data.Columns.Contains("PID（勿删）")) sheet.SetColumnHidden(1, true); } } //hssfworkbook.RemoveSheetAt(0); //file.Dispose(); objStreamWriter.Dispose(); using (FileStream fileWrite = new FileStream(xlsPath, FileMode.Create)) { hssfworkbook.Write(fileWrite); fileWrite.Dispose(); } return saveFileName; }