Asp.Net 生成.xls文件 C#生成Excel文件下载

前台导出Excel表格功能，主要是给查询时间和后台返回数据下载。

       $("#something").click(function () {
　　　　　　  var queryjson = {startTime:"",endTime:""};
            $.ajax({
                url: "/test/testExcel/exportExcel?queryJson=" + JSON.stringify(queryJson),
                dataType: "json",
                type: "GET",
                success: function (data) {if (data.code == 1) {
                        location.href = data.address;//前台的默认下载就要看这里
                    } else {
                        alert("数据异常");
                    }
                }
            });
        })

下面主要是看后台了，后台主要是凭借html标签然后写入文件

这里主要是展示一个表格的画法和循环带入，内容数据有改动，直接拷贝会对不上数据。

这是一个Excel中多表格画法，只是增加多次表格内容。

        [HttpGet]
        public ActionResult exportExcel(string queryJson)
        {
          
            var jihe = GetReportCompanyTicketTypejson(queryJson).ToList();
            //如没有数据，返回前台提示无数据导出
            if (jihe.Count <= 0)
            {
                return ToJsonResult(new { code = 3, address = "" });
            }//else nothing
            //获取统计数据
            var ZhiFulist = GetReportCompanyPayjson(queryJson).ToList();
            var html = "";
            string mark = "";
            var resList = ss_ticketlevelbll.GetList(new { }.ToJson());
            List<Entity.FoundManage.SS_TicketLevelEntity> createlogino = resList.Where(t => t.CreateLoginNo != null).ToList();
            string Cid = string.Empty, tdid = string.Empty;
try
            {
                #region 表头
                html += " <h3 style=\"text-align:center\">统计表</h3>";
                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody><tr><td> 统计区间 </td><td>" + startTime + "至" + endTime + "</td><td> 统计时间 </td><td>" + shijian + "</td><td></td></tr>";
                html += " <tr><td> 单位 </td><td>" + jihe[0].entity.CDefault8 + "</td ></tr>";
                html += "</tbody></table>";
                #endregion

                #region 统计

                html += "<h3 style=\"text-align:center\">销售统计</h3>";
                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody><tr><td>somgthing</td><td class=\"td2\">somgthing</td><td>somgthing</td><td style=\"text-align: right;\">somgthing</td><td style=\"text-align: right;\">金额</td></tr></tbody></table>";
                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody>";
                var itemcount = 0;
                decimal itemprice = 0;
                foreach (var item1 in jihe)
                {
                    html += "<tr><td>" + item1.Something + "</td><td><table border=\"1\" style=\"font-size: 10px;\">";
                    foreach (var item2 in item1.Something)
                    {
                        html += "<tr><td>" + item2.Something+ "</td>" + mark + "<td><table border=\"1\" style=\"font-size: 10px;\">";
                        foreach (var item3 in item2.Something)
                        {
                            itemcount = itemcount + item3.Count;
                            itemprice = itemprice + item3.Price;
                            html += "<tr><td>" + item3.PayType + "</td><td>" + item3.Count + "</td><td>" + item3.Price + "</td></tr>";
                        }
                        html += "</table></td></tr>";
                    }
                    html += "</table></td></tr>";
                }
                html += "</tbody></table>";

                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody><tr><td>合计</td><td></td><td></td><td>" + itemcount + "</td><td>" + itemprice + "</td></tr></tbody></table>";
                #endregion

                #region 支付统计
                html += "<h3 style=\"text-align:center\">统计</h3>";
                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody><tr><td></td><td>Something</td><td>Something</td><td style=\"text-align: right;\">Something</td><td style=\"text-align: right;\">金额</td></tr></tbody></table>";
                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody>";
                var zhifucount = 0;
                decimal zhifuprice = 0;
                foreach (var zhifu1 in ZhiFulist)
                {
                    html += "<tr><td>" + zhifu1.Something + "</td><td><table border=\"1\" style=\"font-size: 10px;\">";
                    foreach (var zhifu2 in zhifu1.Something )
                    {
                        html += "<tr><td>" + zhifu2.User + "</td><td><table border=\"1\" style=\"font-size: 10px;\">";
                        foreach (var zhifu3 in zhifu2.Something )
                        {
                            zhifucount = zhifucount + zhifu3.Count;
                            zhifuprice = zhifuprice + zhifu3.Price;
                            html += "<tr><td>" + zhifu3.PayType + "</td><td>" + zhifu3.Count + "</td><td>" + zhifu3.Price + "</td></tr>";
                        }
                        html += "</table></td></tr>";
                    }
                    html += "</table></td></tr>";
                }
                html += "</tbody></table>";

                html += "<table border=\"1\" style=\"font-size: 10px;\"><tbody><tr><td>合计</td><td></td><td></td><td>" + zhifucount + "</td><td>" + zhifuprice + "</td></tr></tbody></table>";
                #endregion
            }
            catch (Exception e)
            {
                return Error("数据异常," + e.Message);
                throw;
            }//创建表格
            var dizhi = WriteLog(html, "TestName");
            var result = new { code = 1, address = dizhi };
            return ToJsonResult(result);
        }

是不是看头晕了，其实很简单，把前台的Html拿过来就好了

现在表格已经画好了，就差存储了，存储可以直接拿过去，比较简单啦

  public string WriteToExcle(string content, string singleMark)
        {
            string filename = singleMark + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            //Get relative route of IIS
            string folder = System.Web.HttpContext.Current.Server.MapPath("~/excel");
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }
            using (FileStream fs = new FileStream(folder + "/" + filename, System.IO.FileMode.Create, System.IO.FileAccess.Write))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8))
                {
                    sw.WriteLine(content + "\r\n");
                    return "/excel/" + filename;
                }
            }
        }