使用NPOI将TABLE内容导出到EXCEL

时间：2014-03-08 07:45:43 阅读：583 评论：0 收藏：0 [点我收藏+]

项目中需要将页面中的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);
    }
}

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

使用NPOI将TABLE内容导出到EXCEL,布布扣,bubuko.com

使用NPOI将TABLE内容导出到EXCEL

原文：http://www.cnblogs.com/relax/p/3586355.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)