C#導出生成excel文件的方法小結(xml,html方式)

2020-01-24 03:06:00

字體：大中小

來源：轉載

供稿：網友

直接貼上代碼，里面都有注釋

/// <summary>

        /// xml格式生成excel文件并存盤;
        /// </summary>
        /// <param name="page">生成報表的頁面，沒有傳null</param>
        /// <param name="dt">數據表</param>
        /// <param name="TableTitle">報表標題，sheet1名</param>
        /// <param name="fileName">存盤文件名，全路徑</param>
        /// <param name="IsDown">生成文件后是否提示下載,只有web下才有效</param>
        public static void CreateExcelByXml(System.Web.UI.Page page, DataTable dt, String TableTitle, string fileName, bool IsDown)
        {
            StringBuilder strb = new StringBuilder();
            strb.Append(" <html xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
            strb.Append("xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
            strb.Append("xmlns=/"");
            strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>");
            strb.Append(" <style>");
            strb.Append("body");
            strb.Append(" {mso-style-parent:style0;");
            strb.Append(" font-family:/"Times New Roman/", serif;");
            strb.Append(" mso-font-charset:0;");
            strb.Append(" mso-number-format:/"@/";}");
            strb.Append("table");
            //strb.Append(" {border-collapse:collapse;margin:1em 0;line-height:20px;font-size:12px;color:#222; margin:0px;}");
            strb.Append(" {border-collapse:collapse;margin:1em 0;line-height:20px;color:#222; margin:0px;}");
            strb.Append("thead tr td");
            strb.Append(" {background-color:#e3e6ea;color:#6e6e6e;text-align:center;font-size:14px;}");
            strb.Append("tbody tr td");
            strb.Append(" {font-size:12px;color:#666;}");
            strb.Append(" </style>");
            strb.Append(" <xml>");
            strb.Append(" <x:ExcelWorkbook>");
            strb.Append(" <x:ExcelWorksheets>");
            strb.Append(" <x:ExcelWorksheet>");
            //設置工作表 sheet1的名稱
            strb.Append(" <x:Name>" + TableTitle + " </x:Name>");
            strb.Append(" <x:WorksheetOptions>");
            strb.Append(" <x:DefaultRowHeight>285 </x:DefaultRowHeight>");
            strb.Append(" <x:Selected/>");
            strb.Append(" <x:Panes>");
            strb.Append(" <x:Pane>");
            strb.Append(" <x:Number>3 </x:Number>");
            strb.Append(" <x:ActiveCol>1 </x:ActiveCol>");
            strb.Append(" </x:Pane>");
            strb.Append(" </x:Panes>");
            strb.Append(" <x:ProtectContents>False </x:ProtectContents>");
            strb.Append(" <x:ProtectObjects>False </x:ProtectObjects>");
            strb.Append(" <x:ProtectScenarios>False </x:ProtectScenarios>");
            strb.Append(" </x:WorksheetOptions>");
            strb.Append(" </x:ExcelWorksheet>");
            strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
            strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
            strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
            strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
            strb.Append(" <x:ProtectStructure>False </x:ProtectStructure>");
            strb.Append(" <x:ProtectWindows>False </x:ProtectWindows>");
            strb.Append(" </x:ExcelWorkbook>");
            strb.Append(" </xml>");
            strb.Append("");
            strb.Append(" </head> <body> ");
            strb.Append(" <table style=/"border-right: 1px solid #CCC;border-bottom: 1px solid #CCC;text-align:center;/"> <thead><tr>");
            //合格所有列并顯示標題
            strb.Append(" <td style=/"text-align:center;background:#d3eeee;font-size:18px;/" colspan=/"" + dt.Columns.Count + "/" ><b>");
            strb.Append(TableTitle);
            strb.Append(" </b></td> ");
            strb.Append(" </tr>");
            strb.Append(" </thead><tbody><tr style=/"height:20px;/">");
            if (dt != null)
            {
                //寫列標題
                int columncount = dt.Columns.Count;
                for (int columi = 0; columi < columncount; columi++)
                {
                    strb.Append(" <td style=/"width:110px;;text-align:center;background:#CCC;/"> <b>" + dt.Columns[columi] + " </b> </td>");
                }
                strb.Append(" </tr>");
                //寫數據
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strb.Append(" <tr style=/"height:20px;/">");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        strb.Append(" <td style=/"width:110px;;text-align:center;/">" + dt.Rows[i][j].ToString() + " </td>");
                    }
                    strb.Append(" </tr>");
                }
            }
            strb.Append(" </tbody> </table>");
            strb.Append(" </body> </html>");

            string ExcelFileName = fileName;
            //string ExcelFileName = Path.Combine(page.Request.PhysicalApplicationPath, path+"/guestData.xls");
            //報表文件存在則先刪除
            if (File.Exists(ExcelFileName))
            {
                File.Delete(ExcelFileName);
            }
            StreamWriter writer = new StreamWriter(ExcelFileName, false);
            writer.WriteLine(strb.ToString());
            writer.Close();
            //如果需下載則提示下載對話框
            if (IsDown)
            {
                DownloadExcelFile(page, ExcelFileName);
            }
        }
---------
/// <summary>
        /// web下提示下載
        /// </summary>
        /// <param name="page"></param>
        /// <param name="filename">文件名，全路徑</param>
        public static void DownloadExcelFile(System.Web.UI.Page page, string FileName)
        {
            page.Response.Write("path:" + FileName);
            if (!System.IO.File.Exists(FileName))
            {
                MessageBox.ShowAndRedirect(page, "文件不存在！", FileName);
            }
            else
            {
                FileInfo f = new FileInfo(FileName);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + f.Name);
                HttpContext.Current.Response.AddHeader("Content-Length", f.Length.ToString());
                HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.WriteFile(f.FullName);
                HttpContext.Current.Response.End();
            }