npoi使用Excel模板下载数据
C# 使用npoi下载Excel文件时,在有些复杂场景下需要对Excel数据及表头进行特殊处理。如合并表头,对某些列设置特殊数值格式。 这就需要提前定制Excel模板,做好特殊处理,程序只需要填入数据即可达到想要的效果。
效果如下:
Excel模板:
Excel下载:
代码如下:
//打开Excel模板
HSSFWorkbook work = customSheet.OpenSheet(Server.MapPath("Files/Template.xls"));
ISheet sheet = work.GetSheetAt(0);ICellStyle cellstyle;
IRow row;
ICell cell;
Random random = new Random();
for (int i = 2; i <= 4; i++)
{
row = sheet.GetRow(i);
for (int k = 1; k <= 10; k++)
{
Double RandomNum = random.Next(100, 900) / 130.0000;
cell = row.GetCell(k);
cell.SetCellValue(RandomNum);
cellstyle = work.CreateCellStyle(); //初始化单元格样式
//获取模板单元格样式(不使用Excel模板,不需要使用该行代码)
if (k % 5 == 0)
{
HSSFFont ffont = (HSSFFont)work.CreateFont();
ffont.FontHeight = 14 * 14;
ffont.FontName = "宋体";
ffont.Color = HSSFColor.Red.Index;
cellstyle.SetFont(ffont);
cellstyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
}
cell.CellStyle = cellstyle; //给单元格追加格式
}
}
customSheet.SaveSheet(work, Server.MapPath("Files/demo2.xls"));
/// <summary>
/// 保存excel文件到指定位置
/// </summary>
/// <param name="book"></param>
/// <param name="FilePath"></param>
public void SaveSheet(HSSFWorkbook book, string FilePath)
{
FileStream file = new FileStream(FilePath, FileMode.Create);
book.Write(file);
file.Close();
}
/// <summary>
/// 打开Excel读取到sheet中(可将Excel格式、样式及数据读取到sheet中,减少通程序属性添加样式)
/// </summary>
/// <param name="FilePath"></param>
/// <returns></returns>
public HSSFWorkbook OpenSheet(string FilePath)
{
HSSFWorkbook workbook;
FileInfo File = new FileInfo(FilePath);
FileStream fs;
using (fs = File.OpenRead())
{
workbook = new HSSFWorkbook(fs);
fs.Close();
}
return workbook;
}