| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237 |
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using System.Text;
- using System.Web;
- namespace Ant.Service.Common
- {
- /// <summary>
- /// NPOI导出Excel
- /// </summary>
- public class ExportExcel
- {
- #region 数据导出为EXCEL
- public static void CreateExcel(DataTable dt, string fileName)
- {
- StringBuilder strb = new StringBuilder();
- strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
- strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
- strb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\">");
- strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=utf-8'>");
- strb.Append(" <style>");
- strb.Append(".xl26");
- strb.Append(" {mso-style-parent:style0;");
- strb.Append(" font-family:\"Times New Roman\", serif;");
- strb.Append(" mso-font-charset:0;");
- strb.Append(" mso-number-format:\"@\";}");
- strb.Append(" </style>");
- strb.Append(" <xml>");
- strb.Append(" <x:ExcelWorkbook>");
- strb.Append(" <x:ExcelWorksheets>");
- strb.Append(" <x:ExcelWorksheet>");
- strb.Append(" <x:Name>" + fileName + "</x:Name>");
- strb.Append(" <x:WorksheetOptions>");
- strb.Append(" <x:DefaultRowHeight>285</x:DefaultRowHeight>");
- strb.Append(" <x:Selected/>");
- strb.Append(" <x:Panes>");
- strb.Append(" <x:Pane>");
- strb.Append(" <x:Number>3</x:Number>");
- strb.Append(" <x:ActiveCol>1</x:ActiveCol>");
- strb.Append(" </x:Pane>");
- strb.Append(" </x:Panes>");
- strb.Append(" <x:ProtectContents>False</x:ProtectContents>");
- strb.Append(" <x:ProtectObjects>False</x:ProtectObjects>");
- strb.Append(" <x:ProtectScenarios>False</x:ProtectScenarios>");
- strb.Append(" </x:WorksheetOptions>");
- strb.Append(" </x:ExcelWorksheet>");
- strb.Append(" <x:WindowHeight>6750</x:WindowHeight>");
- strb.Append(" <x:WindowWidth>10620</x:WindowWidth>");
- strb.Append(" <x:WindowTopX>480</x:WindowTopX>");
- strb.Append(" <x:WindowTopY>75</x:WindowTopY>");
- strb.Append(" <x:ProtectStructure>False</x:ProtectStructure>");
- strb.Append(" <x:ProtectWindows>False</x:ProtectWindows>");
- strb.Append(" </x:ExcelWorkbook>");
- strb.Append(" </xml>");
- strb.Append("");
- strb.Append(" </head> <body> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'>");
- if (dt.Rows.Count > 0)
- {
- strb.Append("<tr>");
- //写列标题
- int columncount = dt.Columns.Count;
- for (int columi = 0; columi < columncount; columi++)
- {
- strb.Append(" <td style='text-align:center;'><b>" + ColumnName(dt.Columns[columi].ToString()) + "</b></td>");
- }
- strb.Append(" </tr>");
- //写数据
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- strb.Append(" <tr>");
- for (int j = 0; j < dt.Columns.Count; j++)
- {
- strb.Append(" <td class='xl26'>" + dt.Rows[i][j].ToString() + "</td>");
- }
- strb.Append(" </tr>");
- }
- }
- strb.Append("</table> </body> </html>");
- HttpContext.Current.Response.Clear();
- HttpContext.Current.Response.Buffer = true;
- HttpContext.Current.Response.Charset = "utf-8";
- HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
- HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;//
- HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
- //HttpContext.Current.p.EnableViewState = false;
- HttpContext.Current.Response.Write(strb);
- HttpContext.Current.Response.End();
- }
- #endregion
- #region 列的命名
- private static string ColumnName(string column)
- {
- switch (column)
- {
- case "area":
- return "地区";
- case "tongxun":
- return "通讯费";
- case "jietong":
- return "接通";
- case "weijietong":
- return "未接通";
- case "youxiao":
- return "有效电话";
- case "shangji":
- return "消耗商机费";
- case "zongji":
- return "总机费";
- case "account":
- return "帐号";
- case "extensionnum":
- return "分机";
- case "accountname":
- return "商户名称";
- case "transfernum":
- return "转接号码";
- case "calledcalltime":
- return "通话时长(秒)";
- case "callerstarttime":
- return "通话时间";
- case "caller":
- return "主叫号码";
- case "callerlocation":
- return "归属地";
- case "callresult":
- return "结果";
- case "Opportunitycosts":
- return "商机费";
- case "memberfee":
- return "通讯费";
- case "licenid":
- return "客服编号";
- case "servicename":
- return "客服名称";
- case "serviceaccount":
- return "客服帐号";
- case "messageconsume":
- return "短信消耗";
- case "receivingrate":
- return "接听率";
- case "youxiaop":
- return "有效接听率";
- case "telamount":
- return "电话量";
- case "extennum":
- return "拨打分机个数";
- case "telconnum":
- return "继续拨打分机次数";
- case "listenarea":
- return "接听区域";
- case "specialfield":
- return "专业领域";
- case "calltime":
- return "接听时间";
- case "userstart":
- return "当前状态";
- case "currentbalance":
- return "当前余额";
- case "call400all":
- return "400电话总量";
- case "call400youxiao":
- return "400有效电话量";
- case "call400consume":
- return "400消耗额";
- case "call400avgopp":
- return "400平均商机费";
- case "call800all":
- return "800电话总量";
- case "call800youxiao":
- return "800有效电话量";
- case "call800consume":
- return "800消耗额";
- case "call800avgopp":
- return "800平均商机费";
- case "callall":
- return "电话总量";
- case "callyouxiao":
- return "总有效电话量";
- case "callconsume":
- return "总消耗额";
- case "callavgoppo":
- return "总平均商机费";
- case "hr":
- return "小时";
- case "shangji400":
- return "400商机费";
- case "shangji800":
- return "800商机费";
- case "tongxun400":
- return "400通讯费";
- case "tongxun800":
- return "800通讯费";
- case "zongji400":
- return "400总机费";
- case "zongji800":
- return "800总机费";
- case "datet":
- return "日期";
- case "opentime":
- return "开通时间";
- case "allrecharge":
- return "充值金额";
- case "Userstart":
- return "状态";
- case "allnum":
- return "总接听量";
- case "cbalance":
- return "合作金额";
- case "allmoney":
- return "续费额";
- case "username":
- return "商户账号";
- case "isguoqi":
- return "是否过期";
- case "accounttype":
- return "商户类型";
- case "mphone":
- return "客户手机号";
- case "specialText":
- return "专长";
- case "uuname":
- return "客服";
- case "opentimes":
- return "合作时间";
- case "shangjifei":
- return "商机费";
- }
- return "";
- }
- #endregion
- }
- }
|
