.Net 将DataTable导出成Excel并设置单元格样式

1.首先先生成一个DataTable

DataTable dt = new DataTable("cart");
            DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("point", Type.GetType("System.Int16"));
            DataColumn dc3 = new DataColumn("number", Type.GetType("System.Int16"));
            DataColumn dc4 = new DataColumn("totalpoint", Type.GetType("System.Int64"));
            DataColumn dc5 = new DataColumn("prizeid", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            //以上代码完成了DataTable的构架，但是里面是没有任何数据的
            for (int i = 0; i < 10; i++)
            {
                DataRow dr = dt.NewRow();
                dr["prizename"] = "娃娃";
                dr["point"] = 10;
                dr["number"] = 1;
                dr["totalpoint"] = 10;
                dr["prizeid"] = "001";
                dt.Rows.Add(dr);
            }

            string excelFileName = "D:\\导入错误日志文件\\导入选手信息\\" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            ToExcel(dt, excelFileName);

然后再加入核心方法

 public bool ToExcel(DataTable table,string filePath)
        {
            try
            {


                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                IWorkbook workBook = new HSSFWorkbook();
                ISheet sheet = workBook.CreateSheet("异常数据");

                //处理表格列头
                var row = sheet.CreateRow(0);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }

                //处理数据内容
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    row = sheet.CreateRow(1 + i);
                    row.Height = 250;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                        sheet.SetColumnWidth(j, 256 * 15);

                        //判断当前的列的数据是否为空
                        if(j==0)
                        {
                            //选手名称不能为空
                            if (string.IsNullOrEmpty(table.Rows[i][j].ToString()))
                            {
                                ICellStyle cellStyle = workBook.CreateCellStyle();
                                cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
                                cellStyle.FillPattern = FillPattern.SolidForeground;//必须设置这个，否则样式无效
                                                                                    //设置当前行的第几列的样式
                                row.Cells[j].CellStyle = cellStyle;
                            }
                        }

                    }
                }

                //写入数据流
                workBook.Write(fs);
                fs.Flush();
                fs.Close();
            }
            catch(Exception ex)
            {
                LogFactory.GetLogger(ex.GetType()).Error(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "导入导出报错:：" + ex.Message + "\n");
            }
            return true;
        }

结果如下：如果有出现验证不通过的单元格会让背景设置为红色。