C#Web项目中导出数据为Excel

忘是亡心i 2022-06-15 07:21 245阅读 0赞

导出数据为html中表格的形式注入。

public  static string ExportExcelByAbs(DataTable DetailDT, string[,] ht, string subhead, string FileName, string userName = null)
            {
                StringBuilder OutTable = new StringBuilder();
                try
                {
                    if (DetailDT != null)
                    {
                
                        OutTable.Append("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
                        OutTable.Append("<tr style=\"height:50px; font-weight:bold;font-size:20pt\" align=\"center\">");
                        OutTable.Append("<td colspan='" + Convert.ToString(ht.Length / 2) + "'>");
                        OutTable.Append(FileName);
                        OutTable.Append("</td>");
                        OutTable.Append("</tr>");
                        if (subhead.Length > 0)
                        {
                            OutTable.Append("<tr style=\"height:30px;\" align=\"right\">");
                            OutTable.Append("<td colspan='" + Convert.ToString(ht.Length / 2) + "'>");
                            OutTable.Append(subhead);
                            OutTable.Append("</td>");
                            OutTable.Append("</tr>");
                        }
                        OutTable.Append("<tr style=\"height:30px;\">");
                        for (int i = 0; i < ht.Length / 2; i++)
                        {
                            OutTable.Append("<td>");
                            OutTable.Append(ht[i, 0]);
                            OutTable.Append("</td>");
                        }
    
                        OutTable.Append("</tr>");
                        for (int n = 0; n < DetailDT.Rows.Count; n++)
                        {
                            OutTable.Append("<tr style=\"height:30px;\">");
                            for (int ii = 0; ii < ht.Length / 2; ii++)
                            {
                                OutTable.Append("<td>");
                                string value = DetailDT.Rows[n][ht[ii, 1]].ToString();
                                if (ISNumber(value))
                                {
                                    value = Math.Abs(Convert.ToDecimal(value)).ToString();
                                }
                                OutTable.Append(value);
                                OutTable.Append("</td>");
                            }
                            OutTable.Append("</tr>");
                        }
                        OutTable.Append("<tr style=\"height:30px;\">");
                        OutTable.Append("<td colspan='" + Convert.ToString(ht.Length / 2) + "'><span style=\"width:50%\" align=\"left\">");
                        OutTable.Append("制表人:" + userName + "\t");
                        OutTable.Append("</span><span align=\"right\">");
                        OutTable.Append("制表时间" + DateTime.Now.ToShortDateString());
                        OutTable.Append("</span></td>");
                        OutTable.Append("</tr>");
                        OutTable.Append("</table>");
                     
                    }
                   
                }
                catch { }
                return OutTable.ToString();
            }

用到的解析是否为数据的方法

public static bool ISNumber(string numberString)
            {
                try
                {
                    double num;
                    num = double.Parse(numberString);
                    return true;
                }
                catch
                {
                    return false;
                }
            }

下面的正式的导出具体方法了。

protected void btnExcel_Click(object sender, EventArgs e)
    {
      DataTable dtList = new DataTable();
    string[,] ht = { { "提现单号", "WithdrawalNo" },
                                   { "账号类型", "accountName" },
                                   { "账号信息", "LogName" },
                                   { "商家名称", "BussinessName" },
                                   { "商家区域", "ProvinceName" },
                                   { "提现类型", "moneyName" },
                                   { "银行/卡号/开户行", "BankName" },
                                   { "申请金额", "WithdrawalMoney" },
                                   { "实际金额", "ActualMoney" },
                                   { "提现前余额", "AgoAccount" },
                                   { "提现后余额", "AfterAccount" },
                                   { "审核状态", "auditName" },
                                   { "打款状态", "flagName" },
                                   { "打印状态", "printName" },
                                   { "审核意见", "AuditRemark" },
                                   { "申请时间", "ApplyTime" },
                                   { "市场部审核时间", "AuditTime1" },
                                   { "财务部审核时间", "AuditTime2" }
                                 };
                string html = OutputExcel.ExportExcelByAbs(dtList, ht, "", "提现列表", AdminUser.RealName);
                ExportDsToXls("提现列表", html);
    
    
    }

protected void ExportDsToXls(string fileName, string html)
            {
    
                System.IO.StringWriter tw = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
                Response.Clear();
                Response.Charset = "gb2312";
                Response.ContentType = "application/vnd.ms-excel";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName) + ".xls");
                Response.Write("<html><head><META http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\"></head><body>");
    
                Response.Write(html);
    
                Response.Write(tw.ToString());
                Response.Write("</body></html>");
                Response.End();
                hw.Close();
                hw.Flush();
                tw.Close();
                tw.Flush();
            }