NPOI控制excel绘制单元格颜色并导出

来源：互联网发布：淘宝上架教程编辑：程序博客网时间：2024/05/29 12:52

npoi操作excel文件，绘制背景颜色时，数据过大时，不知道什么原因，会有部分颜色绘制不上，应该是循环的问题，调整成现在的模式，先定义好颜色，然后一个一个选择

最后一列不显示，所以i-1,j-1

public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
ICellStyle backcolorred = book.CreateCellStyle();
backcolorred.FillForegroundColor = 10; //退票金额超过刷卡金额，红色
backcolorred.FillPattern = FillPattern.SolidForeground;
ICellStyle backcolorgreen = book.CreateCellStyle();
backcolorgreen.FillForegroundColor = 17; ////已经退过款,绿色,具体数字代表的颜色看NPOI颜色对照表
backcolorgreen.FillPattern = FillPattern.SolidForeground;
ICellStyle backcolororange = book.CreateCellStyle();
backcolororange.FillForegroundColor = 53; //退款金额大于实际刷卡金额，
backcolororange.FillPattern = FillPattern.SolidForeground;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
ICell cellN9 = row2.CreateCell(j);
if (dt.Columns[j].DataType.ToString() == "System.Int32")
{
cellN9.SetCellValue(Convert.ToInt32(dt.Rows[i][j]));
}
else
{
cellN9.SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
switch (Convert.ToString(dt.Rows[i][dt.Columns.Count - 1]))
{
case "0":
{

break;
}
case "1":
{
cellN9.CellStyle = backcolorgreen;
break;
}
case "2":
{

cellN9.CellStyle = backcolorred;
break;
}
case "3":
{
cellN9.CellStyle = backcolororange;
break;
}
}

}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}

阅读全文

0 0