我用SQL在几张table里面抓了一些数据放到EXCLE里,现在表里面有CBRS_REQUIRED这个列,通过判断CBRS_REQUIRED这个列里面的值是否是‘yes’,如果是‘yes‘的话则给yes的这一行底色改成红色。我现在只能给excel所有的行都加上红色底色,希望大家帮我加上这个判断。谢谢,下面是代码,底色我写在最后。
using System;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.Data;
using System.Data.OleDb;
using Microsoft.Win32;
namespace NPOIExcel
{
class NPOIExcelwork
{
static HSSFWorkbook hssfworkbook;
static OleDbConnection oleCon;
static ICellStyle cellStyle;
public void excelwork()
{
//正式环境
string Maskfile = @"D:\SRC_CODE\Release_List.xls";
string time = DateTime.Now.ToString("yyyyMMdd");
string ReportFile = @"D:\EBAUTOSEND\Release_List" + time + ".xls";
#region//取得模板
if (File.Exists(Maskfile) == true)
{
FileStream file = new FileStream(Maskfile, FileMode.Open, FileAccess.Read);
hssfworkbook = new HSSFWorkbook(file);
file.Close();
cellStyle = hssfworkbook.CreateCellStyle();
}
else
{
return;
}
#endregion
#region//建立Sheet,可以多个
ISheet sheet = hssfworkbook.GetSheetAt(0);//建立Sheet
sheet.ForceFormulaRecalculation = true;//保持Sheet公式
#endregion
#region//填值部分
DataTable DT = DB();
if (DT.Rows.Count > 0)
{
for (int i = 0; i < DT.Rows.Count; i++)
{
sheet.CreateRow(i + 1);
for (int j = 0; j < DT.Columns.Count; j++)
{
judge(DT.Rows[i][j], i + 1, j, sheet);
}
}
DT.Dispose();
}
else
{
return;
}
#endregion
#region//文件输出
try
{
FileStream FileTo = new FileStream(ReportFile, FileMode.Create);
hssfworkbook.Write(FileTo);
FileTo.Close();
}
catch
{
return;
}
#endregion
}
#region//取得数据库资料
##
= @"sql语句";
try
{
OleDbDataAdapter Oda = new OleDbDataAdapter(SQL, oleCon);
DataTable tab = new DataTable("TB");
Oda.Fill(tab);
Oda.Dispose();
oleCon.Dispose();
return tab;
}
catch
{
return new DataTable();
}
}
#endregion
#region//放入值,行数,列数,Sheet对象
static void judge(object DT, int i, int j, ISheet sheet1)
{
switch (DT.GetType().ToString())
{
//case "System.Decimal":
// Double value = 0;
// Double.TryParse(DT.ToString(), out value);
// sheet1.GetRow(i).CreateCell(j).SetCellValue(value);
// break;
//case "System.DateTime":
// DateTime TM;
// DateTime.TryParse(DT.ToString(), out TM);
// sheet1.GetRow(i).CreateCell(j).SetCellValue(TM);
// break;
//case "System.Double":
// sheet1.GetRow(i).CreateCell(j).SetCellValue(Double.Parse(DT.ToString()));
// break;
default:
ICell cell = sheet1.GetRow(i).CreateCell(j);
cell.SetCellValue(DT.ToString());
cellStyle = cell.CellStyle as HSSFCellStyle;
cellStyle.FillForegroundColor = HSSFColor.Red.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = cellStyle;
break;
}
}
#endregion
}
}