C# NPOI XSSFCellStyle设置Excel单元格样式
以下是先手动创建一个DataTable,利用NPOI导出Excel,在向Sheet导入数据后可以设置样式
static void Main() { DataTableToExcel(GetDt()); } public static void DataTableToExcel(DataTable dt) { IWorkbook workbook = null; IRow row = null; ISheet sheet = null; ICell cell = null; FileStream fs = null; if (dt != null && dt.Rows.Count > 0) { workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet("Sheet0"); int rowCount = dt.Rows.Count; int columnCount = dt.Columns.Count; //设置列头 row = sheet.CreateRow(0); for (int c = 0; c < columnCount; c++) { cell = row.CreateCell(c); cell.SetCellValue(dt.Columns[c].ColumnName); } //设置每行每列的单元格, for (int i = 0; i < rowCount; i++) { row = sheet.CreateRow(i + 1); for (int j = 0; j < columnCount; j++) { cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //背景色 XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.FillBackgroundColor = IndexedColors.LightGreen.Index; cellStyle.FillForegroundColor = IndexedColors.LightGreen.Index; sheet.GetRow(1).GetCell(3).CellStyle = cellStyle; //字体颜色 IFont font = workbook.CreateFont();//创建一个字体并且设置颜色 font.Color = IndexedColors.Red.Index; ICellStyle style = workbook.CreateCellStyle(); style.SetFont(font); sheet.GetRow(2).GetCell(3).CellStyle = style; using (fs = File.OpenWrite(@"D:\a.xlsx")) { workbook.Write(fs);//向打开的这个xls文件中写入数据 } System.Diagnostics.Process.Start(@"D:\a.xlsx"); } } static DataTable GetDt() { DataTable tblDatas = new DataTable("Datas"); DataColumn dc = null; dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32")); dc.AutoIncrement = true;//自动增加 dc.AutoIncrementSeed = 1;//起始为1 dc.AutoIncrementStep = 1;//步长为1 dc.AllowDBNull = false;// dc = tblDatas.Columns.Add("Product", Type.GetType("System.String")); dc = tblDatas.Columns.Add("Version", Type.GetType("System.String")); dc = tblDatas.Columns.Add("Description", Type.GetType("System.String")); DataRow newRow; newRow = tblDatas.NewRow(); newRow["Product"] = "VS"; newRow["Version"] = "2022"; newRow["Description"] = "又快又好用"; tblDatas.Rows.Add(newRow); newRow = tblDatas.NewRow(); newRow["Product"] = "VS"; newRow["Version"] = "2019"; newRow["Description"] = "好用稳定"; tblDatas.Rows.Add(newRow); return tblDatas; }
主要代码：
//背景色 XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.FillBackgroundColor = IndexedColors.LightGreen.Index; cellStyle.FillForegroundColor = IndexedColors.LightGreen.Index; sheet.GetRow(1).GetCell(3).CellStyle = cellStyle; //字体颜色 IFont font = workbook.CreateFont();//创建一个字体并且设置颜色 font.Color = IndexedColors.Red.Index; ICellStyle style = workbook.CreateCellStyle(); style.SetFont(font); sheet.GetRow(2).GetCell(3).CellStyle = style;
结果：
2022/03/24：17点48分-更新：
单元格数据格式
//设置千分位格式 row.CreateCell(j).SetCellValue(outputValue); XSSFCellStyle xSSFCellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat xSSFDataFormat = (XSSFDataFormat)workbook.CreateDataFormat(); xSSFCellStyle.DataFormat = xSSFDataFormat.GetFormat("#,##0");//#,##0.00 货币格式 row.GetCell(j).CellStyle = xSSFCellStyle;
//设置百分比格式 row.CreateCell(j).SetCellValue(double.Parse(col.Value?.ToString())); XSSFCellStyle xSSFCellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFDataFormat xSSFDataFormat = (XSSFDataFormat)workbook.CreateDataFormat(); xSSFCellStyle.DataFormat = xSSFDataFormat.GetFormat("0.00%"); row.GetCell(j).CellStyle = xSSFCellStyle;
导出自适应宽度（参考：C# NPOI导出Excel以及动态设置列宽 - LukeSteven - 博客园 (cnblogs.com)）
/// <summary> /// Datable导出成Excel /// </summary> /// <param name="dt"></param> /// <param name="file">导出路径(包括文件名与扩展名)</param> public static void TableToExcel(DataTable dt, string file) { IWorkbook workbook; string fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { return; } // 标题及内容单元格样式 var headCellStyle = CreateCellStyle(workbook, true); var contentCellStyle = CreateCellStyle(workbook, false); // 每列列宽字典 var dic = new Dictionary<int, int>(); ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); cell.CellStyle = headCellStyle; dic.Add(i, Encoding.Default.GetBytes(cell.StringCellValue).Length * 256 + 200); } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); // 行高，避免自动换行的内容将行高撑开 row1.HeightInPoints = 20f; for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); cell.CellStyle = contentCellStyle; int length = Encoding.Default.GetBytes(cell.StringCellValue).Length * 256 + 200; length = length > 15000 ? 15000 : length; // 若比已存在列宽更宽则替换，Excel限制最大宽度为15000 if (dic[j] < length) { dic[j] = length; } } } for (int i = 0; i < dt.Columns.Count; i++) { sheet.SetColumnWidth(i, dic[i]); } //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } /// <summary> /// 单元格样式 /// </summary> /// <param name="workbook"></param> /// <param name="isHead"></param> /// <returns></returns> private static ICellStyle CreateCellStyle(IWorkbook workbook, bool isHead) { var cellStyle = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.IsBold = isHead; // 粗体 cellStyle.SetFont(font); if (isHead) { cellStyle.Alignment = HorizontalAlignment.Center; // 水平居中 cellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中 } cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.WrapText = true;//内容自动换行，避免存在换行符的内容合并成单行 return cellStyle; }
