1. 导入NPOI包
在VS中打开NuGet包管理器,搜索”NPOI”:
选择列表中的NPOI在相应项目中进行安装,安装成功后会在项目中自动引用以下DLL:
2. 前台
function Export()
{
location.href = "/Core/NPOIHandler.ashx?Type=Switch";
}
3. 后台处理
前台跳转至后台的一个一般处理程序,
3-1. 生成HSSFWorkbook数据源
/// <summary>
/// 生成交换机HSSFWorkbook数据源
/// </summary>
/// <param name="sSheetName"></param>
/// <param name="sAddress"></param>
/// <param name="sOrganizationID"></param>
/// <returns></returns>
private HSSFWorkbook BuildSwitchData(string sSheetName, string sAddress, string sOrganizationID)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(sSheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
row_Title.HeightInPoints = 19.5F; //设置列头行高
#region 设置列宽
sheet.SetColumnWidth(0, 50 * 256);
sheet.SetColumnWidth(1, 24 * 256);
sheet.SetColumnWidth(2, 37 * 256);
sheet.SetColumnWidth(3, 22 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
#endregion
#region 设置列头单元格样式
HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 12; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#endregion
#region 生成列头
for (int i = 0; i < 6; i++)
{
HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(i); //创建单元格
cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
switch (i)
{
case 0:
cell_Title.SetCellValue("安装地址");
break;
case 1:
cell_Title.SetCellValue("IP");
break;
case 2:
cell_Title.SetCellValue("下行设备");
break;
case 3:
cell_Title.SetCellValue("设备型号");
break;
case 4:
cell_Title.SetCellValue("所属机构");
break;
case 5:
cell_Title.SetCellValue("在线状态");
break;
}
}
#endregion
int iCount = 0;
List<SwitchModel> lS = ExportDataHandler.BuildSwitchDataList(out iCount, null, null, sAddress, sOrganizationID); //项目中的数据源生成方法,此示例中请忽略
if (lS != null)
{
for (int i = 0; i < lS.Count; i++)
{
#region 设置内容单元格样式
HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
#endregion
#region 生成内容单元格
HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 16;
for (int j = 0; j < 6; j++)
{
HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(j); //创建单元格
cell_Conent.CellStyle = cs_Content;
switch (j)
{
case 0:
cell_Conent.SetCellValue(lS[i].Address);
break;
case 1:
cell_Conent.SetCellValue(lS[i].IP);
break;
case 2:
cell_Conent.SetCellValue(lS[i].DownIP);
break;
case 3:
cell_Conent.SetCellValue(lS[i].SwitchTypeName);
break;
case 4:
cell_Conent.SetCellValue(lS[i].OrganizationName);
break;
case 5:
string sConnectState = Convert.ToString(lS[i].ConnecState);
if (sConnectState == null && sConnectState == "" || sConnectState == "1")
sConnectState = "正常";
else if (sConnectState == "0")
sConnectState = "不在线";
cell_Conent.SetCellValue(sConnectState);
break;
}
}
#endregion
}
}
return wb;
}
catch { }
return null;
}
3-2. 保存临时导出文件并Response至页面
public void ProcessRequest(HttpContext context)
{
try
{
string sExportFileName = ""; //导出的临时文件的名称
string sExportFilePath = ""; //导出的临时文件路径
try
{
HSSFWorkbook wb = null;
string sFileName = "";
string Type = context.Request.QueryString["Type"];
switch (Type)
{
case "Switch":
sFileName = "交换机列表";
wb = BuildSwitchData(sFileName, context.Request.QueryString["Address"], context.Request.QueryString["OrganizationID"]);
break;
default:
break;
}
string sWebBasePath = HttpContext.Current.Server.MapPath("~"); //获取网站根目录物理路径
string sExportDir = sWebBasePath + "/Export"; //临时保存文件夹
sExportFileName = sFileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
sExportFilePath = sExportDir + "/" + sExportFileName;
if (!Directory.Exists(sExportDir))
Directory.CreateDirectory(sExportDir);
using (FileStream file = new FileStream(sExportFilePath, FileMode.Create))
{
wb.Write(file);
}
}
catch
{
context.Response.Write("<script type='text/javascript'>alert('导出发生异常!');window.history.back();</script>");
if (File.Exists(sExportFilePath))
File.Delete(sExportFilePath);
return;
}
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + sExportFileName);
context.Response.TransmitFile(sExportFilePath); //将指定的文件直接写入 HTTP 响应输出流,而不在内存中缓冲该文件
context.Response.Flush();
if (File.Exists(sExportFilePath))
File.Delete(sExportFilePath);
context.Response.End();
}
catch { }
}