使用NPOI将TABLE内容导出到EXCEL

公告

View Post

项目中需要将页面中的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代码如下：

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);
    }
}

　　这样就可以将HTML中的TABLE方便地导出EXCEL了～代码有点乱，别介意哈。

posted on 2014-12-02 21:35 gisai 阅读(352) 评论(0) 收藏举报

刷新页面返回顶部

gisoracle

公告

View Post

使用NPOI将TABLE内容导出到EXCEL