使用NPOI将TABLE内容导出到EXCEL

项目中需要将页面中的table内容导出到EXCEL，在用了几种方法后发现NPO是最快&最好的

需要应用 NPOI.dll 还有个Ionic.Zip.dll不知道有用没，没去研究，两个DLL都放到bin目录里了

假设页面中有个<div id="excelTable"><table>.....</table></div>需要导出到EXCEL

在页面中加一个button

<input type="button" name="excelBut" value="导出Excel" onclick="toExcel()" class="sgbtn" />

页面任意部分插入一段javascript：

function toExcel()
{
    post("tools/toExcel.aspx", {act:'tabletoexcel', html:$('.excelTable').html() });
}
function post(URL, PARAMS) {
    var temp = document.createElement("form");
    temp.action = URL;
    temp.method = "post";
    temp.style.display = "none";
    for (var x in PARAMS) {
        var opt = document.createElement("textarea");
        opt.name = x;
        opt.value = PARAMS[x];
        temp.appendChild(opt);
    }
    document.body.appendChild(temp);
    temp.submit();
    return temp;
}

toExcel.aspx文件空白即可

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="toExcel.aspx.cs" Inherits="tools_toExcel" %>

toExcel.aspx.cs代码如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Text;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.HSSF.Util;
using System.Text.RegularExpressions;
using System.IO;
 
public partial class tools_toExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string act = GetValue("act");
 
        //toExcel.aspx?act=tabletoexcel&html=<table class="reportstable">..</table>
        if (act == "tabletoexcel")
        {
            TableToExcel();
        }
    }
 
    public void TableToExcel()
    {
        string tableHtml = Request.Form["html"];    //接受前台table 数值字符串
        if (string.IsNullOrEmpty(tableHtml)) { return; }
 
        InitializeWorkbook();
        HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");
 
        string rowContent = string.Empty;
        MatchCollection rowCollection = Regex.Matches(tableHtml, @"<tr[^>]*>[\s\S]*?<\/tr>", 
            RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选
 
        NPOI.SS.UserModel.IFont fontSubTitle = hssfworkbook.CreateFont();
        fontSubTitle.Boldweight = 800;//加粗
 
        NPOI.SS.UserModel.IFont fontBody = hssfworkbook.CreateFont();
        fontBody.Boldweight = 500;//加粗
 
        for (int i = 0; i < rowCollection.Count; i++)
        {
            HSSFRow row = (HSSFRow)sheet1.CreateRow(i);
            rowContent = rowCollection[i].Value;
 
            MatchCollection columnCollection = Regex.Matches(rowContent, @"<th[^>]*>[\s\S]*?<\/th>", 
                RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
            for (int td = 0; td < columnCollection.Count; td++)
            {               
                row.CreateCell(td).SetCellValue(StrTools.HtmlToTxt(columnCollection[td].Value));
            }
 
            columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", 
                RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
            for (int td = 0; td < columnCollection.Count; td++)
            {
                row.CreateCell(td).SetCellValue(StrTools.HtmlToTxt(columnCollection[td].Value));
            }
        }
        WriteToFile();
        downFile(ppath);
    }
 
    static HSSFWorkbook hssfworkbook;
    public string ppath;
 
    public void WriteToFile()
    {
        string year = DateTime.Now.Year.ToString();
        ppath = HttpContext.Current.Server.MapPath(DateTime.Now.ToString("yyyyMMddmmss") + ".xls");
        FileStream file = new FileStream(ppath, FileMode.Create);
        hssfworkbook.Write(file);
        file.Close();
    }
 
    public void InitializeWorkbook()
    {
        hssfworkbook = new HSSFWorkbook();
        ////create a entry of DocumentSummaryInformation
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "company";
        hssfworkbook.DocumentSummaryInformation = dsi;
        ////create a entry of SummaryInformation
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = "xxx";
        hssfworkbook.SummaryInformation = si;
    }
 
    public void downFile(string ppath)
    {
        if (File.Exists(ppath))
        {
            Response.ClearHeaders();
            Response.Clear();
            Response.Expires = 0;
            Response.Buffer = true;
            Response.AddHeader("Accept-Language", "zh-cn");
            string name = System.IO.Path.GetFileName(ppath);
            System.IO.FileStream files = new FileStream(ppath, FileMode.Open, FileAccess.Read, FileShare.Read);
            byte[] byteFile = null;
            if (files.Length == 0)
            {
                byteFile = new byte[1];
            }
            else
            {
                byteFile = new byte[files.Length];
            }
            files.Read(byteFile, 0, (int)byteFile.Length);
            files.Close();
            File.Delete(files.Name);
            Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8));
            Response.ContentType = "application/octet-stream;charset=gbk";
            Response.BinaryWrite(byteFile);
            Response.End();
        }
    }
 
    /// <summary>
    /// POST/GET 参数获取
    /// </summary>
    /// <param name="context"></param>
    /// <param name="name"></param>
    /// <returns></returns>
    private string GetValue(string name)
    {
        string result = ConvertData.ConvertToString(Request.QueryString[name], "");
        if (string.IsNullOrEmpty(result))
        {
            result = ConvertData.ConvertToString(Request.Form[name], "");
        }
        return StrTools.SafeSqlstr(result);
    }
}