现在提供转Excel表的几个函数-优快云博客

本文链接：https://blog.youkuaiyun.com/rjzou2006/article/details/1647438

本文介绍了一种将HTML表格数据导出到Excel的方法，包括不同格式化选项，如设置字体大小、字符集及特定列的文字格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

        #region To Excel

        /// <summary>
        /// 输出HTML head 部分， Default Language = "GB2312"
        /// Default Font size : 9pt
        /// </summary>
        /// <param name="title">网页标题</param>
        /// <returns></returns>
        public static string HtmlHead(string title)
        {
            return HtmlHead(title,"9pt");
        }
        /// <summary>
        /// 输出HTML head 部分， Default Language = "GB2312"
        /// 标题和body字体
        /// </summary>
        /// <param name="title"></param>
        /// <param name="fontSize">eg: xpt</param>
        /// <returns></returns>
        public static string HtmlHead(string title,string fontSize)
        {
            StringBuilder sb =new StringBuilder();
            sb.Append("<HTML>");
            sb.Append("<HEAD>/r/n");
            sb.Append("<meta http-equiv=/"Content-Type/" content=/"text/html; charset=gb2312/">/r/n");
            sb.Append("<style>td {font-size:"+ fontSize +";} .trtitle{background-color:lightgrey}</style>/r/n");
            sb.Append("<TITLE>"+ title +"</TITLE>/r/n");
            sb.Append("</HEAD>/r/n");
            sb.Append("<BODY>/r/n");
            return sb.ToString();
        }
        /// <summary>
        /// 输出HTML head 部分，
        /// 标题和body字体
        /// </summary>
        /// <param name="title"></param>
        /// <param name="fontSize">eg: xpt</param>
        /// <param name="charSet">eg: gb2312|Big5|utf-8</param>
        /// <returns></returns>
        public static string HtmlHead(string title,string fontSize,string charSet)
        {
            StringBuilder sb =new StringBuilder();
            sb.Append("<HTML>");
            sb.Append("<HEAD>/r/n");
            sb.Append("<meta http-equiv=/"Content-Type/" content=/"text/html; charset="+charSet+"/">/r/n");
            sb.Append("<style>td {font-size:"+ fontSize +";} .trtitle{background-color:lightgrey}</style>/r/n");
            sb.Append("<TITLE>"+ title +"</TITLE>/r/n");
            sb.Append("</HEAD>/r/n");
            sb.Append("<BODY>/r/n");
            return sb.ToString();
        }
        /// <summary>
        /// 输出HTML 结尾部分
        /// </summary>
        /// <returns></returns>
        public static string HtmlEnd()
        {
            return "</body></html>";
        }

        /// <summary>
        /// DataTable 转化成 /t 的 Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string ToExcelNoStyle(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            for (int i=0;i<dt.Columns.Count;i++)
            {
                sb.Append(dt.Columns[i].Caption +"/t");
            }
            for (int i=0;i<dt.Rows.Count;i++)
            {
                sb.Append("/r/n");
                for (int j=0;j<dt.Columns.Count;j++)
                {
                    sb.Append(dt.Rows[i][j].ToString()+"/t");
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 数据转换成HTML的 TR,TD格式，可用于EXCEL
        /// </summary>
        /// <param name="dt"></param>
        /// <remarks>此方法同<see cref="ToExcel" /></remarks>
        /// <returns></returns>
        public static string CreateTRContent(DataTable dt)
        {
            return ToExcel(dt);
        }
        /// <summary>
        /// 数据转换成HTML的 TR,TD格式，可用于EXCEL
        /// </summary>
        /// <param name="dt"></param>
        /// <example>
        /// <code>
        /// DataTable dt = new DataOption().GetDataSet(this.GetSQL_YingShou_Gather_CW_2(corpNo,yyyymm),"Table0").Tables[0];
        ///        方法一 : string outstr = this.ToExcelNoStyle(dt);
        ///        //OutPut RPT
        ///        方法二 : StringBuilder sb =new StringBuilder();
        ///        //Head
        ///        sb.Append(" <p align=center><font size=+2>应收汇总表(财务)</font></p> ");
        ///        sb.Append("<table width=100% border=1 frame=border borderColor=black cellSpacing=0 borderColorDark=white cellPadding=3>");
        ///        sb.Append(ToExcel(dt));
        ///        sb.Append("</Table>");
        ///        string outstr= this.HtmlHead("应收汇总表(财务)") + sb.ToString() + this.HtmlEnd();
        ///
        ///        new Message().OutPubDownloadPop(outstr,corpNo+yyyymm+"_YingShou.Xls","application/ms-excel");
        /// </code>
        /// </example>
        /// <returns>格式化后的字串</returns>
        public static string ToExcel(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<tr>");
            for (int i=0;i<dt.Columns.Count;i++)
            {
                sb.Append("<td>"+dt.Columns[i].Caption +"</td>");
            }
            sb.Append("</tr>");
            for (int i=0;i<dt.Rows.Count;i++)
            {
                sb.Append("<tr>");
                for (int j=0;j<dt.Columns.Count;j++)
                {
                    sb.Append("<td>"+dt.Rows[i][j].ToString()+"</td>");
                }
                sb.Append("</tr>");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 数据转换成HTML的 TR,TD格式，可用于EXCEL<BR/>
        /// 根据 txtFileds 中的列值置列格式为文字格式
        /// style='vnd.ms-excel.numberformat:@'
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="txtFileds">文字格式栏位的列值,从0开始</param>
        /// <returns>格式化后的字串</returns>
        public static string ToExcel(DataTable dt,int[] txtFileds)
        {

            // Begin
            StringBuilder sb = new StringBuilder();
            sb.Append("<tr>");
            for (int i=0;i<dt.Columns.Count;i++)
            {
                sb.Append("<td>"+dt.Columns[i].Caption +"</td>");
            }
            sb.Append("</tr>");
            for (int i=0;i<dt.Rows.Count;i++)
            {
                int cx = 0;            //计数器
                sb.Append("<tr>");
                for (int j=0;j<dt.Columns.Count;j++)
                {
                    //计算文字格式栏化
                    bool addTextFileds = false;
                    if ( cx < txtFileds.Length)
                    {
                        if (j==txtFileds[cx])
                        {
                            addTextFileds = true;
                            cx ++;
                        }
                    }
                    if(addTextFileds)
                    {
                        sb.Append("<td style='vnd.ms-excel.numberformat:@'>"+dt.Rows[i][j].ToString()+"</td>");
                    }
                    else
                    {
                        sb.Append("<td>"+dt.Rows[i][j].ToString()+"</td>");
                    }
                }
                sb.Append("</tr>");
            }
            return sb.ToString();
        }
        #endregion