asp.net 使用NPOI实现导出Excel功能

来源：互联网发布：网络学英语编辑：程序博客网时间：2024/06/05 22:48

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 { } }

4. 最终效果

0 0