.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

public string exportExcelsheets(string typeName)
{
PostgreHelper helper = new PostgreHelper();
ProTools Dic = new ProTools();
string dic = Dic.ReturnDic();
JObject jo = (JObject)JsonConvert.DeserializeObject(dic);

 

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);
excelstream.Close();
{
string sql = $"这里是SQL语句";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
//XSSFSheet workSheet = workBook.GetSheetAt(0) as XSSFSheet;
workBook.RemoveSheetAt(0);// 这个是必须得，要不然，一直存在sheet1
XSSFSheet workSheet1 = (XSSFSheet)workBook.CreateSheet("工作簿1");

//标题
IRow frow = workSheet1.CreateRow(0);
frow.CreateCell(0).SetCellValue( typeName + "- 结果导出");
workSheet1.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 = workSheet1.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());

}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet1.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{

_frow.CreateCell(j).SetCellValue(dt.Rows[jj][j].ToString());
}
}
}
}
{
string sql = $"这里是SQL语句'";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet2 = (XSSFSheet)workBook.CreateSheet("工作簿2");
//标题
IRow frow = workSheet2.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet2.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 = workSheet2.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

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

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

}
{
string sql = $"这里是sql语句'";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet3 = (XSSFSheet)workBook.CreateSheet("工作簿3");
//标题
IRow frow = workSheet3.CreateRow(0);
frow.CreateCell(0).SetCellValue(typeName + "- 结果导出");
workSheet3.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 = workSheet3.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

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

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

}
{
string sql = $"这里是sql语句";
DataTable dt = helper.GetDataTable(sql);

//得到WorkSheet对象
XSSFSheet workSheet4 = (XSSFSheet)workBook.CreateSheet("工作簿4");
//标题
IRow frow = workSheet4.CreateRow(0);
frow.CreateCell(0).SetCellValue( typeName + "- 结果导出");
workSheet4.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 = workSheet4.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{

_frow1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());

}
if (dt != null || dt.Rows.Count > 0)
{
//表格内容
for (int i = 0, jj = dt.Rows.Count - 1; i < dt.Rows.Count; i++, jj--)
{
IRow _frow = workSheet4.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 + "\\" + xqname + typeName + ".xlsx";

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

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