c#excel创建sheet并设置表样

private void NewSheet(string excelFilePath, string sheetName)
        {
            excelFilePath = excelFilePath + $"\\XBOM for {XBOM.Compare.Service.CompareServicecs.inDMIECP}.xlsx";
            IWorkbook workbook;
            if (File.Exists(excelFilePath))
            {
                using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = new XSSFWorkbook(file);
                }
            }
            else
            {
                workbook = new XSSFWorkbook();  
            }
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow row = sheet.CreateRow(0);
            ICell cell0 = row.CreateCell(0);
            cell0.SetCellValue("列名1");
            ICell cell1 = row.CreateCell(1);
            cell1.SetCellValue("列名2");
            ICell cell2 = row.CreateCell(2);
            cell2.SetCellValue("列名3");
            ICell cell3 = row.CreateCell(3);
            cell3.SetCellValue("列名4");
            ICell cell4 = row.CreateCell(4);
            cell4.SetCellValue("列名5");
            ICell cell5 = row.CreateCell(5);
            cell5.SetCellValue("列名6");
            ICell cell6 = row.CreateCell(6);
            cell6.SetCellValue("列名7");
            ICell cell7 = row.CreateCell(7);
            cell7.SetCellValue("列名8");
            ICell cell8 = row.CreateCell(8);
            cell8.SetCellValue("列名9");
            ICell cell9 = row.CreateCell(9);
            cell9.SetCellValue("列名10");
            ICell cell10 = row.CreateCell(10);
            cell10.SetCellValue("列名11");

            // 修改样式  
            ICellStyle style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = "Calibri";
            font.FontHeightInPoints = 11;
            style.SetFont(font);
            style.Alignment = HorizontalAlignment.Center; // 水平居中  
            style.VerticalAlignment = VerticalAlignment.Center; // 垂直居中  
            // 设置边框  
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.WrapText = true;
            row.HeightInPoints = 30;

            // 应用样式到单元格  
            cell0.CellStyle = style;
            cell1.CellStyle = style;
            cell2.CellStyle = style;
            cell3.CellStyle = style;
            cell4.CellStyle = style;
            cell5.CellStyle = style;
            cell6.CellStyle = style;
            cell7.CellStyle = style;
            cell8.CellStyle = style;
            cell9.CellStyle = style;
            cell10.CellStyle = style;

            // 设置列宽  
            int columnWidth = 256 ; // 大约1个字符的宽度  
            sheet.SetColumnWidth(0, columnWidth*13);
            sheet.SetColumnWidth(1, columnWidth*21);
            sheet.SetColumnWidth(2, columnWidth*17);
            sheet.SetColumnWidth(3, columnWidth*22);
            sheet.SetColumnWidth(4, columnWidth*28);
            sheet.SetColumnWidth(5, columnWidth*18);
            sheet.SetColumnWidth(6, columnWidth*20);
            sheet.SetColumnWidth(7, columnWidth*15);
            sheet.SetColumnWidth(8, columnWidth*20);
            sheet.SetColumnWidth(9, columnWidth*16);
            sheet.SetColumnWidth(10, columnWidth*26);
            // 保存工作簿  
            using (FileStream file = new FileStream(excelFilePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file);
            }
        }

数据插入自适应长度

在后续数据插入时，实时判定字符串长度然后对需要自适应的列进行赋值。

sheet.SetColumnWidth(1, BMaxLenth * 256 + 2 * 256); // +2*256是为了增加一些额外的空间  
sheet.SetColumnWidth(6, GMaxLenth * 256 + 2 * 256); // +2*256是为了增加一些额外的空间