前台导出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 + "
");
return "/excel/" + filename;
}
}
}
存储相对简单很多,在服务器生成文件后吧地址返回给前台下载就可以了。
结束、。。