【ASP.NET】C# 将HTML中Table导出到Excel（TableToExcel）

　　首先，说下应用场景 就是，把页面呈现的Table 导出到Excel中。其中使用的原理是 前台使用ajax调用aspx后台，传递过去参数值，导出。使用的组件是NPOI。

　　前台调用：

<script type="text/javascript">
        function toExcel() {
            post("../TableToExcel.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;
        }
    </script>

View Code

　　HTML页面内容示例：

 <div class="formbody" id="excelTable">
            <div class="formtitle"><span>报告详情页</span></div>
            <table style="width: 100%;" id="tableCriHead" runat="server">
                <tr>
<td>TEST</td>
                    </tr>

            </table>
        </div>

View Code

　　后台生成代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.HSSF.Util;
using System.Text.RegularExpressions;
using System.IO;

namespaceDemo
{
    public partial class TableToExcel : 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")
            {
                TableToExcelMethod();
            }
        }
        public void TableToExcelMethod()
        {
            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(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(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 = ConvertToString(Request.QueryString[name], "");
            if (string.IsNullOrEmpty(result))
            {
                result = ConvertToString(Request.Form[name], "");
            }
            return FilterSpecial(result);
        }

        public static string ConvertToString(Object obj, string strDefault)
        {
            try
            {
                if (obj == null)
                {
                    return strDefault;
                }
                return obj.ToString();
            }
            catch
            {
            }
            return strDefault;
        }

        /// <summary>
        /// HTML转行成TEXT
        /// </summary>
        /// <param name="strHtml"></param>
        /// <returns></returns>
        public static string HtmlToTxt(string strHtml)
        {
            string[] aryReg ={
            @"<script[^>]*?>.*?</script>",
            @"<(\/\s*)?!?((\w+:)?\w+)(\w+(\s*=?\s*(([""'])(\\[""'tbnr]|[^\7])*?\7|\w+)|.{0})|\s)*?(\/\s*)?>",
            @"([\r\n])[\s]+",
            @"&(quot|#34);",
            @"&(amp|#38);",
            @"&(lt|#60);",
            @"&(gt|#62);", 
            @"&(nbsp|#160);", 
            @"&(iexcl|#161);",
            @"&(cent|#162);",
            @"&(pound|#163);",
            @"&(copy|#169);",
            @"&#(\d+);",
            @"-->",
            @"<!--.*\n"
            };
            string newReg = aryReg[0];
            string strOutput = strHtml;
            for (int i = 0; i < aryReg.Length; i++)
            {
                Regex regex = new Regex(aryReg[i], RegexOptions.IgnoreCase);
                strOutput = regex.Replace(strOutput, string.Empty);
            }
            strOutput.Replace("<", "");
            strOutput.Replace(">", "");
            strOutput.Replace("\r\n", "");
            return strOutput;
        }

        /// <summary>
        /// 过滤特殊字符
        /// 如果字符串为空，直接返回。
        /// </summary>
        /// <param name="str">需要过滤的字符串</param>
        /// <returns>过滤好的字符串</returns>
        public static string FilterSpecial(string str)
        {
            if (str == "")
            {
                return str;
            }
            else
            {
                str = str.Replace("'", "");
                str = str.Replace("<", "");
                str = str.Replace(">", "");
                str = str.Replace("%", "");
                str = str.Replace("'delete", "");
                str = str.Replace("''", "");
                str = str.Replace("\"\"", "");
                str = str.Replace(",", "");
                str = str.Replace(".", "");
                str = str.Replace(">=", "");
                str = str.Replace("=<", "");
                str = str.Replace("-", "");
                str = str.Replace("_", "");
                str = str.Replace(";", "");
                str = str.Replace("||", "");
                str = str.Replace("[", "");
                str = str.Replace("]", "");
                str = str.Replace("&", "");
                str = str.Replace("#", "");
                str = str.Replace("/", "");
                str = str.Replace("-", "");
                str = str.Replace("|", "");
                str = str.Replace("?", "");
                str = str.Replace(">?", "");
                str = str.Replace("?<", "");
                str = str.Replace(" ", "");
                return str;
            }
        }
    }
}

View Code

 　　附上NPOI的DLL下载地址： 点 击 下 载

　　最后，整体来说能够导出table到excel，但是同时存在一个问题，就是 导出后没有table的样式。

转载于:https://www.cnblogs.com/ruicky/p/4429165.html