1、操作过程流程为 单击导出按钮弹出一个网页，下载完之后关闭
2、特点：可以随意修改样式、背景颜色、方便简单、不需要引用第三方的dll等
废话不多说，直接上代码
前端 采用的form表单提交的方式 说明 一下getFromData这个方法我测试的时候发现全用Input上传没问题 用textarea 可能会丢数据
1 //导出excel 2 outputFrom("/STKPurchaseInfo/ExportIndexPage", conditionFilter()); 3 4 //条件判断 5 function conditionFilter() { 6 var postData = { 7 8 , sortField: "" 9 , departmentId: "" /* 供应商ID*/ 10 , mer_Id: "" /* 商户号*/ 11 , user_Name: "" /* 手机号*/ 12 } 13 return postData; 14 } 15 16 //from post 请求 17 function outputFrom(url, data, target) { 18 var t = target || "_blank"; 19 20 var fromHtml = $('<div style="display:none">' + 21 '<form id="fromName" action="' + url + '" method="post" target="' + target + '">' + getFromData(data) + '</form>' + 22 '<script>$("#fromName")[0].submit();<\/script>' + 23 '</div>'); 24 $("body").append(fromHtml); 25 fromHtml.remove(); 26 function getFromData(data) { 27 var str = ""; 28 for (var n in data) { 29 if (false) { 30 str += ' <textarea name="' + n + '">' + (data[n] || '') + '</textarea>'; 31 } else { 32 str += ' <input type = "text" name = "' + n + '" value =\'' + (data[n] || '') + '\' />'; 33 } 34 } 35 return str; 36 } 37 }
后端 ExportHelper 类 可自行设置表头表尾，我没用到就注释了
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Web; 7 8 namespace Common 9 { 10 /// <summary> 11 /// excel 导出类 12 /// </summary> 13 public class ExportHelper 14 { 15 /// <summary> 16 /// 导出配置数据 17 /// </summary> 18 /// <param name="FileName">导出的文件名称</param> 19 /// <param name="TitleList">sheet显示的列名</param> 20 /// <param name="DataList">数据载体 </param> 21 public static void ExportConfig(string FileName, List<ExportModel> TitleList, List<ExportModel> DataList) 22 { 23 //命名导出表格的StringBuilder变量 24 StringBuilder sHtml = new StringBuilder(string.Empty); 25 //打印表头 26 sHtml.Append("<table border=\"1\" width=\"100%\">"); 27 //sHtml.Append("<tr height=\"40\"><td colspan=\"5\" align=\"center\" style='font-size:24px'><b>XXXXXXX报价表" + "</b></td></tr>"); 表头 28 //打印列名 29 sHtml.Append("<tr height=\"20\" align=\"center\" style='background-color:#CD0000'>"); 30 //注：按顺序排列 31 foreach (var ExportModel in TitleList) 32 { 33 sHtml.Append(string.Format("<td>{0}</td>", ExportModel.Name)); 34 } 35 sHtml.Append("</tr>"); 36 37 //循环数据 38 foreach (var item in DataList) 39 { 40 sHtml.Append("<tr height=\"20\" align=\"left\">"); 41 foreach (var it in item.SubList) 42 { 43 sHtml.Append(string.Format("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", it.Name)); 44 } 45 sHtml.Append("</tr>"); 46 } 47 // 打印表尾 48 //sHtml.Append("<tr height=\"40\"><td align=\"center\" colspan=\"5\" style='background-color:#CD0000;font-size:24px'><b>XXXXXXXX</a> </b></td></tr>"); 49 sHtml.Append("</table>"); 50 //调用输出Excel表的方法 51 ExportToExcel("application/ms-excel", FileName + ".xls", sHtml.ToString()); 52 } 53 54 /// <summary> 55 /// 输入HTTP头，然后把指定的流输出到指定的文件名，然后指定文件类型 56 /// 打印表格的方法 57 /// </summary> 58 /// <param name="FileType"></param> 59 /// <param name="FileName"></param> 60 /// <param name="ExcelContent"></param> 61 public static void ExportToExcel(string FileType, string FileName, string ExcelContent) 62 { 63 System.Web.HttpContext.Current.Response.ContentType = FileType; 64 System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; 65 System.Web.HttpContext.Current.Response.Charset = "utf-8"; 66 System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()); 67 68 System.IO.StringWriter tw = new System.IO.StringWriter(); 69 System.Web.HttpContext.Current.Response.Output.Write(ExcelContent.ToString()); 70 /*乱码BUG修改 20140505*/ 71 //如果采用以上代码导出时出现内容乱码，可将以下所注释的代码覆盖掉上面【System.Web.HttpContext.Current.Response.Output.Write(ExcelContent.ToString());】即可实现。 72 //System.Web.HttpContext.Current.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=utf-8\"/>" + ExcelContent.ToString()); 73 System.Web.HttpContext.Current.Response.Flush(); 74 System.Web.HttpContext.Current.Response.End(); 75 } 76 77 } 78 /// <summary> 79 /// 导出类数据配置 80 /// </summary> 81 public class ExportModel 82 { 83 /// <summary> 84 /// 名称 85 /// </summary> 86 public string Name { get; set; } 87 public List<ExportModel> SubList { get; set; } 88 } 89 90 }
数据导出 查询数据那段可以不看，只需要看查询结果后怎么赋值的
注：按顺序添加的，表名和表数据字段顺序写的时候不要少写或反了
1 /// <summary> 2 /// 采购列表打印页面带出 3 /// </summary> 4 /// <returns></returns> 5 public void ExportSTKPurchaseORDPrintPage() 6 { 7 SAASReq.STKPurchaseORDPrintListRequest request = new SAASReq.STKPurchaseORDPrintListRequest(); 8 request.USER_NAME = CommonCheckUtil.TypeCheck<string>(Request.Form["user_Name"], string.Empty); /* 手机号*/ 9 request.DEPARTMENTID = CommonCheckUtil.TypeCheck<string>(Request.Form["departmentId"], string.Empty); /* 供应商ID*/ 10 request.MER_ID = CommonCheckUtil.TypeCheck<int>(Request.Form["mer_Id"], 0); /* 商户号*/ 11 if (Request.Form["PageInfo"] != null) 12 { 13 request.PageInfoDictionary = (Dictionary<string, int>)JsonHelp.Decode(Request["PageInfo"], typeof(Dictionary<string, int>)); 14 request.PageInfoDictionary["PageSize"] = 1; 15 request.PageInfoDictionary["PageSize"] = 10000000; 16 } 17 if (Request.Form["data"] != null) 18 { 19 request.pCondtion = (Dictionary<string, object>)JsonHelp.Decode(Request["data"], typeof(Dictionary<string, object>)); 20 } 21 request.SortFieldName = CommonCheckUtil.TypeCheck<String>(Request.Form["sortField"], String.Empty); 22 23 SAASRes.STKPurchaseORDPrintListResponse response = BusinessClient.GetCryptionAPIResponse<SAASRes.STKPurchaseORDPrintListResponse>(request, SubmitType.Content); 24 25 if (response.Status == 0) 26 { 27 if (response.STKPurchaseORDPrintInfoList == null && response.STKPurchaseORDPrintInfoList.Count == 0) 28 { 29 FileOutPutErrorResponse("数据为空!"); 30 return; 31 } 32 //sheet显示的列名 33 List<ExportModel> TitleList = new List<ExportModel>(); 34 TitleList.Add(new ExportModel { Name = "序号" }); 35 TitleList.Add(new ExportModel { Name = "门店" }); 36 TitleList.Add(new ExportModel { Name = "供应商" }); 37 TitleList.Add(new ExportModel { Name = "物资编码" }); 38 TitleList.Add(new ExportModel { Name = "物资名称" }); 39 TitleList.Add(new ExportModel { Name = "规格" }); 40 TitleList.Add(new ExportModel { Name = "单位" }); 41 TitleList.Add(new ExportModel { Name = "数量" }); 42 TitleList.Add(new ExportModel { Name = "备注" }); 43 //数据载体 44 List<ExportModel> DataList = new List<ExportModel>(); 45 int num = 1; 46 foreach (var item in response.STKPurchaseORDPrintInfoList) 47 { 48 49 List<ExportModel> subList = new List<ExportModel>(); 50 subList.Add(new ExportModel { Name = Convert.ToString(num) }); 51 subList.Add(new ExportModel { Name = Convert.ToString(item.DEPARTMENT_NAME) }); 52 subList.Add(new ExportModel { Name = Convert.ToString(item.SUPR_NAME) }); 53 subList.Add(new ExportModel { Name = Convert.ToString(item.ITEM_CODE) }); 54 subList.Add(new ExportModel { Name = Convert.ToString(item.ITEM_NAME) }); 55 subList.Add(new ExportModel { Name = Convert.ToString(item.ITEM_GG) }); 56 subList.Add(new ExportModel { Name = Convert.ToString(item.APPLYUNIT) }); 57 subList.Add(new ExportModel { Name = Convert.ToString(item.CENTRE_NUM.ToString("f2")) }); 58 subList.Add(new ExportModel { Name = Convert.ToString(item.REMARK) }); 59 num++; 60 DataList.Add(new ExportModel { SubList = subList }); 61 } 62 ExportHelper.ExportConfig("采购订单打印", TitleList, DataList); 63 } 64 else 65 { 66 FileOutPutErrorResponse("导出失败!错误原因：" + response.Message); 67 } 68 return; 69 } 70 /// <summary> 71 /// 导出后关闭页面 72 /// </summary> 73 /// <param name="pError"></param> 74 protected void FileOutPutErrorResponse(string pError) 75 { 76 Response.Write("<script>alert('" + pError + "');opener=null;self.close();</script>"); 77 Response.End(); 78 }
总结：1、单元格里面的格式最好在传进去时的时候就格式化好
2、ExportHelper类拼接的时候有时候可能会需要设置单元格格式，不过我测试的时候认为文本格式最好用，日期什么的当成文本导出也不会有什么问题，如果不用文本可能会出问题，格式化时分秒的时候 会有问题，亲测 。用文本格式请看总结1
1） 文本：vnd.ms-excel.numberformat:@
2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd
3） 数字：vnd.ms-excel.numberformat:#,##0.00
4） 货币：vnd.ms-excel.numberformat:￥#,##0.00
5） 百分比：vnd.ms-excel.numberformat: #0.00%
如：
<td style=“vnd.ms-excel.numberformat:@ ”>122414323543254354353</td>
3、代码基本上都可以复制过去就可以用了，只需要改一下传值和操作数据那一块就行了，暂时只是做的单表头，如果需要导出多表头的话，可以先自己去看看table 行|列合并，再拼接表头处自己拼一下代码，计算好需要拼的列数或行数拼接就行。
有什么疑问可以提出来
