.net单个sheet使用XSSFWorkbook导出excel表格

1.添加引用

NPOI.dll

NPOI.OOXML.dll

2.引用

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;

3.新建一个excel模板放到项目中。例如：templete.xlsx

4.实现方式

自定义参数1：typeName

自定义参数2：dt，数据库查询内容

public string exportExcel(string typeName, DataTable dt)
{

if (dt == null || dt.Rows.Count == 0) return "";
string daochu_result = "";
try
{
string fileNameDir = System.Web.HttpContext.Current.Server.MapPath("~");
string filePath = fileNameDir + "\\Upload\\templete.xlsx";

//打开模板文件，得到WorkBook对象
using (FileStream excelstream = new FileStream(Path.GetFullPath(filePath), FileMode.Open))
{
XSSFWorkbook workBook = new XSSFWorkbook(excelstream);
//得到WorkSheet对象
XSSFSheet workSheet = workBook.GetSheetAt(0) as XSSFSheet;
//标题
IRow frow = workSheet.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
frow.Height = 3 * 256;
IFont font0 = workBook.CreateFont();
font0.FontHeightInPoints = 22;
font0.Boldweight = 300;
ICellStyle title0Style = workBook.CreateCellStyle();
title0Style.Alignment = HorizontalAlignment.Left;
title0Style.VerticalAlignment = VerticalAlignment.Center;
title0Style.SetFont(font0);
frow.GetCell(0).CellStyle = title0Style;
//属性列
IRow _frow1 = workSheet.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}

if (!Directory.Exists(fileNameDir + "\\Upload\\SearchFile\\" + typeName))
{
Directory.CreateDirectory(fileNameDir + "\\Upload\\SearchFile\\" + typeName);
}
string saveFileName = fileNameDir + "\\Upload\\SearchFile\\" + typeName + "\\" +typeName + ".xlsx";

if (System.IO.File.Exists(saveFileName))
{
//如果存在则删除
System.IO.File.Delete(saveFileName);
}
FileStream wexcelstrem = new FileStream(Path.GetFullPath(saveFileName), FileMode.OpenOrCreate);
workBook.Write(wexcelstrem);
excelstream.Close();
wexcelstrem.Close();
daochu_result = "Success";
}

}
catch (Exception e)
{
daochu_result = "Error";
}
return daochu_result;
}

 

转载于:https://www.cnblogs.com/nn1314/p/10461230.html