【C#】NPOI导出Excel格式设置
NPOI.HSSF.UserModel.HSSFWorkbook wb = new HSSFWorkbook(ExcelFile); //获取工作簿
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0); //获取第一个Sheet
HSSFRow row = (HSSFRow)sheet.GetRow(0); //获取第一个Sheet的第一行
HSSFCell cell = (HSSFCell)sheet.GetRow(0).GetCell(0); //获取第一个Sheet第一行的第一列,即第一个单元格
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle(); //定义一个单元格样式
// 设置单元格背景色
cellStyle.FillForegroundColor = HSSFColor.Red.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
//设置文字对齐方式
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//设置字体格式开始
HSSFFont firstFont = (HSSFFont)wb.CreateFont();
//设置字体的颜色为自定义颜色
HSSFPalette palette = wb.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Lime.Index, (byte)205, (byte)133, (byte)63); //RGB颜色值
firstFont.Color = HSSFColor.Lime.Index;
//字体属性
firstFont.FontName = "宋体";
firstFont.FontHeightInPoints = (short)12; //设置字体大小
firstFont.Boldweight = (short)FontBoldWeight.Bold; //加粗
cellStyle .SetFont(firstFont); //设置cellStyle 样式的字体
//设置字体格式结束
//边框(Thin是单线——)
cellStyle .BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle .BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle .BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle .BorderTop = NPOI.SS.UserModel.BorderStyle.None;
具体实例:
(要求如果基本工资大于5000,该行的背景色要设置为红色。)
protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
BLL.BLL_Test bll = new BLL.BLL_Test();
DataTable dt = bll.getData().Tables[0];
string path = System.Web.HttpContext.Current.Request.MapPath("Files/");
FileStream ExcelFile = new FileStream(path + "text.xls", FileMode.Open, FileAccess.ReadWrite);
NPOI.HSSF.UserModel.HSSFWorkbook wb = new HSSFWorkbook(ExcelFile);
//获取第一个工作表
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
HSSFRow row;
HSSFCell cell;
//设置首行内容
cell = (HSSFCell)sheet.GetRow(0).GetCell(0);
cell.SetCellValue(System.DateTime.Now.ToString("yyyy-mm") + "测试");
//1、如果基本工资>5000,该行填充为红色。(排除最后一行)
bool cellStyleFlag = false;
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
cellStyle.FillForegroundColor = HSSFColor.Red.Index;// 设置背景色
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center;
//2、设置首行格式
HSSFFont firstFont = (HSSFFont)wb.CreateFont();
HSSFPalette palette = wb.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Lime.Index, (byte)205, (byte)133, (byte)63);
firstFont.Color = HSSFColor.Lime.Index;
firstFont.FontName = "宋体";
firstFont.FontHeightInPoints = (short)12;
firstFont.Boldweight = (short)FontBoldWeight.Bold;
HSSFCellStyle firstTitleStyle = (HSSFCellStyle)wb.CreateCellStyle();
firstTitleStyle.SetFont(firstFont);
firstTitleStyle.Alignment = HorizontalAlignment.Center;
firstTitleStyle.VerticalAlignment = VerticalAlignment.Center;
cell.CellStyle = firstTitleStyle;
//3、设置标题行格式
HSSFFont font = (HSSFFont)wb.CreateFont();
font.Color = HSSFColor.Lime.Index;
font.FontName = "宋体";
font.FontHeightInPoints = (short)10;
font.Boldweight = (short)FontBoldWeight.Bold;
HSSFCellStyle titleStyle = (HSSFCellStyle)wb.CreateCellStyle();
titleStyle.SetFont(font);
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
//边框
titleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
titleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
for (int r = 0; r < dt.Rows.Count+2; r++) //因为有两行标题行,所以多循环两行
{
row = (HSSFRow)sheet.GetRow(r+1); //由于首行已经设置过样式,所以行数从第2行开始
if (row == null)
row = (HSSFRow)sheet.CreateRow(r+1);
cell = (HSSFCell)row.GetCell(0);
if (cell == null)
cell = (HSSFCell)row.CreateCell(0);
cellStyleFlag = false; //每次循环一行都要将cellStyleFlag初始为false,否则一行变红之后,后面的行都是红色
if (r > 1 && r != dt.Rows.Count + 1)
{
cell.SetCellValue(r - 1); //序号
if (int.Parse(dt.Rows[r-2]["基本工资"].ToString()) > 5000) //要在循环列之前先判断,这样才能根据cellStyleFlag来循环设定该行的列
cellStyleFlag = true;
}
for (int c = 0; c < dt.Columns.Count; c++)
{
cell = (HSSFCell)row.GetCell(c);
if (cell == null)
cell = (HSSFCell)row.CreateCell(c);
if (r < 2) //设置标题行的样式
cell.CellStyle = titleStyle;
else
{
if (c > 0) //第一列是序号,所以第一列不绑值
cell.SetCellValue(dt.Rows[r-2][c].ToString());
if (cellStyleFlag)
cell.CellStyle = cellStyle;
}
}
}
string NewFileName = "text" + "_" + System.DateTime.Now.ToString("yyyymmddhhmmss") + ".xls";
if (Common.FileHelper.IsExistFile(path + "temp/" + NewFileName))
{
Common.FileHelper.DeleteFile(path + "temp/" + NewFileName);
}
FileStream Excel_NewFile = new FileStream(path + "temp/" + NewFileName, FileMode.Create);
wb.Write(Excel_NewFile);
Excel_NewFile.Flush();
ExcelFile.Close();
Excel_NewFile.Close();
path = path + "/temp/" + NewFileName;
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
导出的Excel:
**备注:**导出Excel取的是模板,标题行的样式其实已经在模板里已经设置好了。之所以在程式里又用代码去控制标题行的样式,是因为加上“如果基本工资大于0,该行的背景色要设置为红色”的设置后,标题行的字体颜色会自动变成绿色的!!! 我也不知道为什么,后来想要保持标题行颜色为橙色,就在代码里进行控制了。
模板如下图: