今天做了一个关于订单得导出得,一个订单一般都是多个商品,那么就会出现单元格合并得情况,还有复杂表头得情况,现在拿我做得,给大家一个小demo
先看最终效果
后台实现:
代码:
[HttpGet]
[ValidateInput(false)]
public ActionResult Order_Export()
{
int showType;
int ws_userid;
int.TryParse(Request["ws_userid"], out ws_userid);
int.TryParse(Request["showType"], out showType);
string keywords = Request["keywords"];
string starttime = Request["starttime"];
string endtime = Request["endtime"];
ws_orderManager ws_OrderManager = new ws_orderManager();
string fileName = "订单导出";
int count = 0;
var data = ws_OrderManager.GetOrderExportList(showType, ws_userid, keywords, 1, 1000, starttime, endtime, ref count);
switch (showType)
{
case 1:
fileName += "-待付款";
break;
case 2:
fileName += "-待发货";
break;
case 3:
fileName += "-待收货";
break;
case 4:
fileName += "-交易成功";
break;
case -1:
fileName += "-交易关闭";
break;
default:
fileName += "-全部订单";
break;
}
if (!string.IsNullOrWhiteSpace(starttime) && !string.IsNullOrWhiteSpace(endtime))
{
fileName += "-" + starttime + "——" + endtime;
}
return ExportFundDataToExcel(data, fileName);
}
public ActionResult ExportFundDataToExcel(List<OrderViewModel> listData, string filename)
{
var fileHtml = getFundDataCollect(listData, filename);
if (fileHtml == "")
{
return Json(JsonHelper.LayuiRsulit("当前没有数据需要导出", false));
}
byte[] fileContents = Encoding.UTF8.GetBytes(fileHtml);
return File(fileContents, "application/ms-excel; charset=UTF8", filename + ".xls");
}
public string AddExcelbottom()
{
StringBuilder sb = new StringBuilder();
sb.Append("</body>");
sb.Append("</html>");
return sb.ToString();
}
public string AddExcelHead()
{
StringBuilder sb = new StringBuilder();
sb.Append("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sb.Append(" <head>");
sb.Append(" <!--[if gte mso 9]><xml>");
sb.Append("<x:ExcelWorkbook>");
sb.Append("<x:ExcelWorksheets>");
sb.Append("<x:ExcelWorksheet>");
sb.Append("<x:Name></x:Name>");
sb.Append("<x:WorksheetOptions>");
sb.Append("<x:Print>");
sb.Append("<x:ValidPrinterInfo />");
sb.Append(" </x:Print>");
sb.Append("</x:WorksheetOptions>");
sb.Append("</x:ExcelWorksheet>");
sb.Append("</x:ExcelWorksheets>");
sb.Append("</x:ExcelWorkbook>");
sb.Append("</xml>");
sb.Append("<![endif]-->");
sb.Append(" </head>");
sb.Append("<body>");
return sb.ToString();
}
public string getFundDataCollect(List<OrderViewModel> dt, string taskname)
{
if (null == dt)
{
return "";
}
if (dt.Count < 1)
{
return "";
}
var fileHtml = new StringBuilder();
fileHtml.Append(AddExcelHead());
fileHtml.Append("<table border=\"1\" style=\"table-layout:fixed;\" cellspacing='0' cellpadding='0'>");
fileHtml.Append("<tr>");
fileHtml.Append("<td colspan=\"9\" style=\"font-size: 16px; font-family: 宋体; text-align: center; height: 30px;\">");
fileHtml.AppendFormat(" <strong> {0}</strong>", taskname);
fileHtml.Append("</td>");
fileHtml.Append(" </tr>");
fileHtml.Append("<tr>");
fileHtml.Append("<td>订单号</td>" );
fileHtml.Append("<td>下单时间</td>");
fileHtml.Append("<td>商品名称</td>");
fileHtml.Append("<td>商品数量</td>");
fileHtml.Append("<td>商品SKU</td>");
fileHtml.Append("<td>总金额</td>");
fileHtml.Append("<td>收货人</td>");
fileHtml.Append("<td>收货电话</td>");
fileHtml.Append("<td>收货地址</td>");
fileHtml.Append(" </tr>");
for (var j = 0; j < dt.Count; j++)
{
fileHtml.Append("<tr>");
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">'{1}</td>",dt[j].goodsList.Count, dt[j].order_sn);
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">'{1}</td>", dt[j].goodsList.Count, dt[j].add_time);
StringBuilder nextStr = new StringBuilder();
if(dt[j].goodsList.Count == 1)
{
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].goods_name);
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].number);
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].goods_specifition_name_value);
}
else
{
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].goods_name);
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].number);
fileHtml.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[0].goods_specifition_name_value);
for (int i = 1; i < dt[j].goodsList.Count; i++)
{
nextStr.Append("<tr>");
nextStr.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[i].goods_name);
nextStr.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[i].number);
nextStr.AppendFormat("<td style=\"text-align: center;\">{0}</td>", dt[j].goodsList[i].goods_specifition_name_value);
nextStr.Append("</tr>");
}
}
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">{1}</td>", dt[j].goodsList.Count, dt[j].actual_price);
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">{1}</td>", dt[j].goodsList.Count, dt[j].name);
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">{1}</td>", dt[j].goodsList.Count, dt[j].mobile);
fileHtml.AppendFormat("<td rowspan=\"{0}\" style=\"text-align: center;\">{1}</td>", dt[j].goodsList.Count, dt[j].ProvinceName + dt[j].CityName + dt[j].DistrictName + dt[j].address);
fileHtml.Append("</tr>");
fileHtml.Append(nextStr.ToString());
}
fileHtml.Append("</table>");
fileHtml.Append(AddExcelbottom());
return fileHtml.ToString();
}
我得数据结构是这样得:
最外层是订单信息,里面有一个数组,goodsList,这个是订单中得商品
前台请求,这样访问:
window.location='/@ViewContext.RouteData.DataTokens["area"]/@ViewContext.RouteData.Values["controller"].ToString()/Order_Export?page=1&limit=9999999&showType=' + showType+'&keywords='+$("#keywords").val() + '&starttime='+$("#starttime").val()+'&endtime=' + $("#endtime").val();
这样就会自动进行下载了,欢迎各位评论!