erp/Web/Reports/comExports13.aspx.cs.exclude

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.SS.UserModel;
using SiteCore;
using SiteCore.Handler;
using Utils;

public partial class Reports_comExports13 : ReportBase
{
    static int exp_page_size = 9999;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (CurrentUser == null)
        {
            Response.Write("账户过期无法导出,请重新登录!");
            return;
        }
        CommonHelper.PageExpires();
        String type = Request["type"];
        try
        {
            switch (type)
            {
                case "bexcel":
                    {
                        string extName = Request["extname"];

                        string exData = GetPostString("hExData");
                        string exSdate = GetPostString("hExSdate");
                        string exEdate = GetPostString("hExEdate");

                        //数据来源
                        List<Dictionary<string, object>> data = Utils.Serialization.JSON.ToDictionaryList(exData);

                        String json = Request["columns"];
                        List<Dictionary<string, object>> columns = Utils.Serialization.JSON.ToDictionaryList(json);

                        ExportExcel(exSdate,exEdate,extName, columns, data);
                        break;
                    }
                case "excel2":
                    {
                        string extName = Request["extname"];
                        string exSdate = GetPostString("hExSdate");
                        string exEdate = GetPostString("hExEdate");
                        //数据来源
                        List<Dictionary<string, object>> data = SearchData();
                        String json = Request["columns"];
                        List<Dictionary<string, object>> columns = Utils.Serialization.JSON.ToDictionaryList(json);
                        ExportNopi(exSdate, exEdate, extName,ref columns, ref data);
                        //ExportExcel(exSdate, exEdate, extName, columns, data);
                        break;
                    }
                case "excel":
                    {
                        string extName = Request["extname"];
                        string exSdate = GetPostString("hExSdate");
                        string exEdate = GetPostString("hExEdate");
                        //数据来源
                        DataTable data = GetDtData();// SearchData();
                        int c = data.Rows.Count;
                        String json = Request["columns"];
                        List<Dictionary<string, object>> columns = Utils.Serialization.JSON.ToDictionaryList(json);
                        ExportNopi(exSdate, exEdate, extName, ref columns, data);
                        if (data != null)
                        {
                            data.Clear();
                            data.Dispose();
                        }
                        if (c > 1000) GC.Collect();
                        //ExportExcel(exSdate, exEdate, extName, columns, data);
                        break;
                    }
                default:
                    break;
            }
        }
        catch(Exception ex)
        {
            //Response.Write("无法导出" + ex.Message);
            //Response.End();
        }
    }

    private void enableQueryStringWriting()
    {
        var qs = Request.QueryString;

        var queryString = (NameValueCollection)Request.GetType().GetField("_queryString",BindingFlags.NonPublic | BindingFlags.Instance).GetValue(Request);

        PropertyInfo readOnlyInfo = queryString.GetType().GetProperty("IsReadOnly",BindingFlags.NonPublic | BindingFlags.Instance);

        readOnlyInfo.SetValue(queryString, false, null);
    }

    public void setQueryString(string parms)
    {
        //Request.QueryString.Add(;
        enableQueryStringWriting();
        NameValueCollection nvc = new NameValueCollection();
        
        parms = parms.TrimStart('&');
        string[] pArr = parms.Split('&');
        string[] tArr;
        foreach(string p in pArr)
        {
            tArr = p.Split('=');
            //nvc.Add(tArr[0], tArr[1]);
            Request.QueryString.Set(tArr[0], tArr[1]);
        }
        
    }

    public DataTable GetDtData()
    {
        string url=GetPostString("url");
        url = HttpUtility.UrlDecode(url);
        int idx = url.IndexOf("t=");
        url = url.Substring(idx + 2);//.Replace("t=", "");
        string mname = "";
        if (url.IndexOf("&") != -1)
        {
            int ui = url.IndexOf("&");
            string parms = url.Substring(ui+1);
            setQueryString(parms);
            mname = url.Substring(0,ui).Trim();
        }
        else mname = url.Trim();

        sync sentity = new sync();
        Type t = sentity.GetType();
        MethodInfo mi = t.GetMethod(mname);       //根据字符串名称获得对应的方法
        t.GetField("ex_psize").SetValue(sentity, 2000000);
        mi.Invoke(sentity, null);
        //DataTable dt=(DataTable)t.GetField("ex_dtable").GetValue(sentity);
        return sentity.ex_dtable;
        //sentity.ex_psize = 2014091;// exp_page_size;
        //sentity.get_erp_orderlist();
        //return sentity.ex_dtable;
    }


    public void ExportNopi(string exSdate, string exEdate, string extName,ref List<Dictionary<string, object>> columns,ref List<Dictionary<string, object>> data)
    {
        excelName = "导出文件";

        createExcel();
        int ops = 60000;
        int dc = data.Count;
        int cpg = (dc - 1) / ops + 1;
        if (cpg == 0) cpg = 1;
        int ps = 0;
        List<Dictionary<string, object>> curLst = null;
        List<string> cols = new List<string>();
        for (int i = 0; i < cpg; i++)
        {
            if (i + 1 == cpg)//最后一页
            {
                if (i == 0) ops = dc;
                ps = dc - ops * (cpg - 1);
            }
            else
            {
                ps = ops;
            }

            if (i > 0)
            {
                hssfworkbook.CreateSheet((i+1).ToString());
            }
            iSheet = hssfworkbook.GetSheetAt(i);
            iSheet.DefaultRowHeight = 22 * 20;
            iSheet.CreateRow(0);

            IRow row = iSheet.CreateRow(1);
            int c = 0;
            row.Height = 22 * 20;
            //row.RowStyle = GetExHeadStyle();
            
                foreach (Dictionary<string, object> dic in columns)
                {
                    if (i == 0)cols.Add(dic["field"].ToString());
                    createHeaderCell(row, c, dic["header"].ToString());
                    iSheet.AutoSizeColumn(c);
                    iSheet.SetColumnWidth(c, Convert.ToInt32(dic["width"].ToString().Replace("px", "")) * 65);

                    //row.CreateCell(c).SetCellValue(dic["header"].ToString());
                    c++;
                }
            
            int r = 2;
            int bc = 0;
            iSheet.CreateRow(r);
            insertRow(iSheet, r, ps, iSheet.GetRow(1));
            curLst = data.Skip(i * ops).Take(ps).ToList();
            foreach (Dictionary<string, object> da_dic in curLst)
            {
                bc = 0;
                foreach (string col in cols)
                {
                    createItemCell(iSheet.GetRow(r), bc, getFS(col, da_dic[col]));
                    //setStringCell(r, bc, );
                    bc++;
                }
                r++;
            }
            curLst.Clear();
        }
        
        columns.Clear();
        data.Clear();
        DownLoadFile_Ex();
    }

    public void ExportNopi(string exSdate, string exEdate, string extName, ref List<Dictionary<string, object>> columns, DataTable data)
    {
        excelName = "导出文件";

        createExcel();
        int ops = 60000;
        int dc = data.Rows.Count;
        int cpg = (dc - 1) / ops + 1;
        if (cpg == 0) cpg = 1;
        int ps = 0;
        DataTable curDt = null;
        List<string> cols = new List<string>();
        for (int i = 0; i < cpg; i++)
        {
            curDt = new DataTable();
            if (i + 1 == cpg)//最后一页
            {
                if (i == 0) ops = dc;
                ps = dc - ops * (cpg - 1);
            }
            else
            {
                ps = ops;
            }

            if (i > 0)
            {
                hssfworkbook.CreateSheet((i + 1).ToString());
            }
            iSheet = hssfworkbook.GetSheetAt(i);
            iSheet.DefaultRowHeight = 22 * 20;
            iSheet.CreateRow(0);

            IRow row = iSheet.CreateRow(1);
            int c = 0;
            row.Height = 22 * 20;
            //row.RowStyle = GetExHeadStyle();

            foreach (Dictionary<string, object> dic in columns)
            {
                if (i == 0) cols.Add(dic["field"].ToString());
                createHeaderCell(row, c, dic["header"].ToString());
                iSheet.AutoSizeColumn(c);
                iSheet.SetColumnWidth(c, Convert.ToInt32(dic["width"].ToString().Replace("px", "")) * 65);

                //row.CreateCell(c).SetCellValue(dic["header"].ToString());
                c++;
            }

            int r = 2;
            int bc = 0;
            iSheet.CreateRow(r);
            insertRow(iSheet, r, ps, iSheet.GetRow(1));
            curDt= data.AsEnumerable().Skip(i * ops).Take(ps).CopyToDataTable();
            foreach (DataRow da_dic in curDt.Rows)
            {
                bc = 0;
                foreach (string col in cols)
                {
                    createItemCell(iSheet.GetRow(r), bc, getFS(col, da_dic[col]));
                    //setStringCell(r, bc, );
                    bc++;
                }
                r++;
            }
           
        }
        if (curDt != null)
        {
            curDt.Clear();
            curDt.Dispose();
        }
        columns.Clear();
        data.Clear();
        data.Dispose();
        DownLoadFile_Ex();
    }

    public void ExportNopiXLSX(string exSdate, string exEdate, string extName, ref List<Dictionary<string, object>> columns, DataTable data)
    {
        excelName = "导出文件";

        createExcel();
        int i = 0;
        hssfworkbook.CreateSheet(i.ToString());
        iSheet = hssfworkbook.GetSheetAt(i);
        iSheet.DefaultRowHeight = 22 * 20;
        iSheet.CreateRow(0);

        IRow row = iSheet.CreateRow(1);
        int c = 0;
        row.Height = 22 * 20;
        //row.RowStyle = GetExHeadStyle();
        List<string> cols = new List<string>();
        foreach (Dictionary<string, object> dic in columns)
        {
            if (i == 0) cols.Add(dic["field"].ToString());
            createHeaderCell(row, c, dic["header"].ToString());
            iSheet.AutoSizeColumn(c);
            iSheet.SetColumnWidth(c, Convert.ToInt32(dic["width"].ToString().Replace("px", "")) * 65);

            //row.CreateCell(c).SetCellValue(dic["header"].ToString());
            c++;
        }

        int r = 2;
        int bc = 0;
        int rc = data.Rows.Count;
        iSheet.CreateRow(r);
        insertRow(iSheet, r, rc, iSheet.GetRow(1));
        foreach (DataRow da_dic in data.Rows)
        {
            bc = 0;
            foreach (string col in cols)
            {
                createItemCell(iSheet.GetRow(r), bc, getFS(col, da_dic[col]));
                //setStringCell(r, bc, );
                bc++;
            }
            r++;
        }

     
        DownLoadFile_Ex();
    }

    public void DownLoadFile_Ex()
    {
        using (MemoryStream ms = new MemoryStream())
        {
            hssfworkbook.Write(ms);
            Response.Clear();
            Response.Buffer = true;
            Response.ExpiresAbsolute = DateTime.Now.AddSeconds(-1);
            Response.Expires = 0;
            Response.AddHeader("pragma", "no-cache");
            Response.AddHeader("cache-control", "private");
            Response.CacheControl = "no-cache";
            //Response.Charset = "GB2312";
            Response.Charset = "UTF-8";
            //Response.AppendHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName + ".xls", System.Text.Encoding.UTF8));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            ms.WriteTo(Response.OutputStream);
            Response.Flush();
            hssfworkbook = null;
            iSheet = null;
        }
        
        Response.End();
        //Response.Write(ExportTable(data, columns));
    }



    public List<Dictionary<string, object>> SearchData()
    {
        //查询条件
        /*string key = Request["key"];
        //字段排序
        String sortField = Request["sortField"];
        String sortOrder = Request["sortOrder"];
        */
        string url = Request["url"];
        url = HttpUtility.UrlDecode(url);
        List<string> parms = new List<string>();
        foreach (string key in Request.Form)
        {
            if (key == "url") continue;
            if (key == "columns") continue;
            if (key == "pageSize") continue;
            parms.Add(key + "=" + Request.Form[key]);
        }

        string fstr = string.Join("&", parms.ToArray());//.ToString();
        fstr = HttpUtility.UrlDecode(fstr);

        List<Dictionary<string, object>> lists = getComReportData(getUrl(url), fstr);
        //Newtonsoft.Json.JsonConvert.DeserializeObject<Hashtable>()

        //Hashtable result = 
        return lists;// result["data"] as ArrayList;
    }

    public List<Dictionary<string, object>> getComReportData(string url, string parms)
    {
        HttpCookie lcCookie = HttpContext.Current.Request.Cookies[webConfig.CookieName];
        string name = HttpUtility.UrlDecode(lcCookie.Values["User"]);
        string ticket = lcCookie.Values["Ticket"];
        if (parms == "") parms = "pageSize=" + exp_page_size;
        else parms += "&pageSize=" + exp_page_size;
        parms += "&_expt=1";
        //parms = HttpUtility.UrlEncode(parms);
        HttpHelper http = new HttpHelper();
        HttpItem item = new HttpItem()
        {
            URL = url,
            Method = "POST",
            ContentType = "application/x-www-form-urlencoded; charset=UTF-8",
            Cookie = webConfig.CookieName + "=user=" + name + "&ticket=" + ticket,
            Postdata = parms
        };
        item.PostEncoding = Encoding.UTF8;
        HttpResult hresult = http.GetHtml(item);
        string html = hresult.Html;
        JObject jobj = (JObject)Newtonsoft.Json.JsonConvert.DeserializeObject(html);
        if (jobj["total"] != null)
        {
            List<Dictionary<string, object>> lists_sec;
            List<Dictionary<string, object>> lists = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(jobj["data"].ToString());

            int pg = (Convert.ToInt32(jobj["total"]) - 1) / exp_page_size + 1; // exp_page_size;
            if (pg > 1)
            {
                for (int i = 2; i <= pg; i++)
                {
                    if (i == 2) parms = parms.Replace("pageIndex=0", "pageIndex=1");
                    else parms = parms.Replace("pageIndex=" + (i - 3), "pageIndex=" + (i - 1));
                    item = new HttpItem()
                    {
                        URL = url,
                        Method = "POST",
                        ContentType = "application/x-www-form-urlencoded; charset=UTF-8",
                        Cookie = webConfig.CookieName + "=user=" + name + "&ticket=" + ticket,
                        Postdata = parms
                    };
                    hresult = http.GetHtml(item);
                    html = hresult.Html;
                    jobj = (JObject)Newtonsoft.Json.JsonConvert.DeserializeObject(html);
                    html = string.Empty;
                    if (jobj["total"] != null)
                    {
                        lists_sec = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(jobj["data"].ToString());
                        lists.AddRange(lists_sec);
                        //lists_sec.Clear();
                    }
                    jobj = null;
                }
            }
            return lists;
            //Hashtable htable= JsonConvert.DeserializeObject<Hashtable>(jobj["data"].ToString());
        }
        return null;
    }

    public void ExportExcel(string exSdate, string exEdate, string extName, List<Dictionary<string, object>> columns, List<Dictionary<string, object>> data)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        //Response.Charset = "UTF-8";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + extName + ".xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        EnableViewState = false;
        string _exdata= "";
        try
        {
            _exdata = ExportTable(exSdate, exEdate, extName, data, columns);
        }
        catch (Exception ex)
        {
            string ex2 = ex.Message;
        }
        byte[] array = Encoding.Default.GetBytes(_exdata);

        int dataBufferSize = 5 * 1024 * 1024;
        byte[] buffer = null;
        long hasRead = 0;
        using (MemoryStream stream = new MemoryStream(array))
        {
            long contentLength = stream.Length;
            int currentRead = 0;
            while (hasRead < contentLength)
            {
                buffer = new byte[dataBufferSize];
                currentRead = stream.Read(buffer, 0, dataBufferSize);
                Response.OutputStream.Write(buffer, 0, currentRead);
                Response.Flush();
                hasRead += currentRead;
            }
        }
        array = null;
        Response.End();

    }

    #region table
    public static string ExportTable(string exSdate, string exEdate, string extName, List<Dictionary<string, object>> data, List<Dictionary<string, object>> columns)
    {

        ArrayList columnsBottom = getColumnsBottom(columns);

        ArrayList columnsTable = getColumnsTable(columns);

        StringBuilder sb = new StringBuilder();
        //data = ds.DataSetName + "\n";

        //dg.Cells[10].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");

        //data += tb.TableName + "\n";
        sb.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">");
        sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
        sb.AppendLine("<tr style=\"border-right:none;border-bottom:none\"><td style=\"height:30px;text-align:center;font-size:15.0pt;font-weight:700;border-right:none;border-bottom:none\" colspan=\"" + columnsBottom.Count + "\">" + extName + "</td></tr>");
        sb.AppendLine("<tr style=\"border-right:none;border-bottom:none\"><td style=\"height:25px;text-align:center;font-size:10.5pt;border-right:none;border-bottom:none\" colspan=\"" + columnsBottom.Count + "\">" + getTime(exSdate, exEdate) + "</td></tr>");

        //写出列名
        for (int i = 0; i < columnsTable.Count; i++)
        {
            ArrayList columnsRow = (ArrayList)columnsTable[i];
            sb.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;height:25px;\">");
            foreach (Dictionary<string, object> column in columnsRow)
            {
                if (!Convert.ToBoolean(column["visible"])) continue;
                if (column.ContainsKey("type"))
                    sb.AppendLine("<td style=\"background-color:#ADD8E6\" > 序号</td>");
                if (column.ContainsKey("header"))
                    sb.AppendLine("<td style=\"background-color:#ADD8E6\" " + (column.ContainsKey("colspan") ? "colspan='" + column["colspan"] + "'" : "") + " " + (column.ContainsKey("rowspan") ? "rowspan='" + column["rowspan"] + "'" : "") + ">" + column["header"] + "</td>");
            }
            sb.AppendLine("</tr>");
        }
        //写出数据

        int count = 0;
        string fv = "";
        foreach (Dictionary<string, object> row in data)
        {
            sb.Append("<tr>");
            foreach (Dictionary<string, object> column in columnsBottom)
            {
                if (!Convert.ToBoolean(column["visible"])) continue;
                Object value = "";
                if (column.ContainsKey("type"))
                {
                    if (Convert.ToString(column["type"]) == "indexcolumn") value = count + 1;
                }
                else
                {
                    if (column.ContainsKey("field") && row.ContainsKey(column["field"].ToString()))
                    {
                        //renderer
                        value = row[column["field"].ToString()];
                        fv = value.ToString();
                        if (fv.Length == 19 && fv.Substring(10, 1) == "T")
                        {
                            value = fv.Replace("T", " ");
                        }
                    }
                    else
                    {
                        value = "";
                    }
                }
                if (column.ContainsKey("field"))
                {
                    if (column["field"].ToString() == "OrderState")
                    {
                        value = getOrderStateStr(Convert.ToInt32(value));
                    }
                    else if (column["field"].ToString() == "cashstate")
                    {
                        value = getOrderCashStateStr(Convert.ToInt32(value));
                    }
                }
                sb.AppendLine("<td style=\"height:25px;vnd.ms-excel.numberformat: @;\" >" + value + "</td>");//;vnd.ms-excel.numberformat: @;
            }
            sb.AppendLine("</tr>");
            count++;
        }
        sb.AppendLine("</table>");
        return sb.ToString();
    }

  

    public static ArrayList getColumnsBottom(List<Dictionary<string, object>> columns)
    {
        ArrayList columnsBottom = new ArrayList();

        for (int i = 0; i < columns.Count; i++)
        {
            Dictionary<string, object> column = (Dictionary<string, object>)columns[i];

            if (column.ContainsKey("columns"))// != null
            {
                List<object> childColumns = (List<object>)column["columns"];
                columnsBottom.AddRange(getColumnsBottomArray(childColumns));
            }
            else
            {
                columnsBottom.Add(column);
            }

        }
        return columnsBottom;
    }

    public static ArrayList getColumnsBottomArray(List<object> columns)
    {
        ArrayList columnsBottom = new ArrayList();

        for (int i = 0; i < columns.Count; i++)
        {
            Dictionary<string, object> column = (Dictionary<string, object>)columns[i];

            if (column.ContainsKey("columns"))// != null
            {
                List<object> childColumns = (List<object>)column["columns"];
                columnsBottom.AddRange(getColumnsBottomArray(childColumns));
            }
            else
            {
                columnsBottom.Add(column);
            }

        }
        return columnsBottom;
    }

    public static ArrayList getColumnsTable(List<Dictionary<string, object>> columns)
    {
        ArrayList table = new ArrayList();

        getColumnsRows(columns, 0, table);

        createTableSpan(table);

        return table;

    }

    public static void getColumnsRows(List<Dictionary<string, object>> columns, int level, ArrayList table)
    {
        ArrayList row = null;
        if (table.Count > level)
        {
            row = (ArrayList)table[level];
        }
        else
        {
            row = new ArrayList();
            table.Add(row);
        }

        for (int i = 0; i < columns.Count; i++)
        {

            Dictionary<string, object> column = (Dictionary<string, object>)columns[i];
            row.Add(column);
            if (column.ContainsKey("columns"))
            {
                List<object> childColumns = (List<object>)column["columns"];
                if (childColumns != null)
                {
                    getColumnsRowsObject(childColumns, level + 1, table);
                }
            }
            
        }
    }

    public static void getColumnsRowsObject(List<object> columns, int level, ArrayList table)
    {
        ArrayList row = null;
        if (table.Count > level)
        {
            row = (ArrayList)table[level];
        }
        else
        {
            row = new ArrayList();
            table.Add(row);
        }

        for (int i = 0; i < columns.Count; i++)
        {

            Dictionary<string, object> column = (Dictionary<string, object>)columns[i];
            row.Add(column);
            if (column.ContainsKey("columns"))
            {
                List<object> childColumns = (List<object>)column["columns"];
                if (childColumns != null)
                {
                    getColumnsRowsObject(childColumns, level + 1, table);
                }
            }

        }
    }

    public static void createTableSpan(ArrayList table)
    {
        for (int i = 0; i < table.Count; i++)
        {
            ArrayList row = (ArrayList)table[i];  //row
            for (int l = 0; l < row.Count; l++)
            {
                Dictionary<string, object> cell = (Dictionary<string,object>)row[l];   //column

                int colSpan = getColSpan(cell);
                cell["colspan"] = colSpan;
                if (colSpan > 1)
                {
                    cell["rowspan"] = 1;
                }
                else
                {
                    cell["rowspan"] = table.Count - i;
                }

            }
        }
    }

    public static int getColSpan(Dictionary<string, object> column)
    {
        int colSpan = 0;
        if (column.ContainsKey("columns"))
        {
            List<object> childColumns = (List<object>)column["columns"];
            if (childColumns != null)
            {
                for (int i = 0; i < childColumns.Count; i++)
                {
                    Dictionary<string, object> child = (Dictionary<string, object>)childColumns[i];
                    colSpan += getColSpan(child);
                }
            }
        }
        else
        {
            colSpan = 1;
        }
        return colSpan;
    }
    #endregion

    #region npoi
    public void createHeaderCell(IRow row, int cIdx, object v)
    {
        ICell cell = row.CreateCell(cIdx);
        cell.CellStyle = GetExHeadStyle();
        cell.SetCellValue(v.ToString());
    }
    ICellStyle ex_headStyle = null;
    private ICellStyle GetExHeadStyle()
    {
        if (ex_headStyle == null)
        {
            ex_headStyle = hssfworkbook.CreateCellStyle();
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            font.Boldweight = 700;
            ex_headStyle.SetFont(font);
            ex_headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_headStyle.Alignment = HorizontalAlignment.CENTER;
            ex_headStyle.VerticalAlignment = VerticalAlignment.CENTER;
            ex_headStyle.FillForegroundColor = IndexedColors.AQUA.Index;
            ex_headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

        }
        return ex_headStyle;
    }
    public void createItemCell(IRow row, int cIdx, object v)
    {
        ICell cell = row.CreateCell(cIdx);
        cell.CellStyle = GetExItemStyle();
        cell.SetCellValue(v.ToString());
    }
    ICellStyle ex_itemStyle = null;
    private ICellStyle GetExItemStyle()
    {
        if (ex_itemStyle == null)
        {
            ex_itemStyle = hssfworkbook.CreateCellStyle();
            IFont font = hssfworkbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            ex_itemStyle.SetFont(font);
            ex_itemStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_itemStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_itemStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_itemStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            ex_itemStyle.Alignment = HorizontalAlignment.CENTER;
            ex_itemStyle.VerticalAlignment = VerticalAlignment.CENTER;
            //ex_itemStyle.FillForegroundColor = IndexedColors.AQUA.Index;
            //ex_itemStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
        }
        return ex_itemStyle;
    }
    #endregion

    #region private
    private string getFS(string col, object v)
    {
        if (v.ToString() == "") return "";
        switch (col.ToLower())
        {
            case "orderstate":
                {
                    return getOrderStateStr(Convert.ToInt32(v));
                }
            case "cashstate":
                {
                    return getOrderCashStateStr(Convert.ToInt32(v));
                }
            case "status":
                {
                    return getTbStatusByState(Convert.ToString(v));
                }
            case "aftersalestate":
                {
                    return getAfterSaleByState(Convert.ToInt32(v));
                }
        }
        return v.ToString();
    }
    private string getUrl(string url)
    {
        url = HttpUtility.UrlDecode(url);
        string host = Request.Url.Host;
        string port = Request.Url.Port.ToString();
        int idx = url.IndexOf("handler/", StringComparison.OrdinalIgnoreCase);
        if (idx != -1)
        {
            url = "http://" + host + ":" + port + "/" + url.Substring(idx);
        }
        else
        {
            url = "http://" + host + ":" + port + "/" + url;
        }
        return url;
    }
    private static string getTime(string s, string e)
    {
        if (s != "" && e != "") return "时间：" + s + " 至 " + e;
        else if (s != "") return "时间：" + s;
        else if (e != "") return "时间：" + e;
        return "";
    }

    private static string getTbStatusByState(string st)
    {
        string stateStr = "待发货";
        if (st == "NOT_SHIPPED")
        {
            stateStr = "待发货";
        }
        else if (st == "PART_SHIPPED")
        {
            stateStr = "部分发货";
        }
        else if (st == "SHIPPED")
        {
            stateStr = "已发货";
        }
        else if (st == "COMPLETE")
        {
            stateStr = "已完成";
        }
        else if (st == "CLOSE")
        {
            stateStr = "已关闭";
        }
        else if (st == "REFUNDED")
        {
            stateStr = "退款成功";
        }
        else if (st == "PART_REFUNDED")
        {
            stateStr = "部分退款";
        }
        else if (st == "WAIT_BUYER_RETURN_GOODS")
        {
            stateStr = "等待买家退货";
        }
        else if (st == "REJECT_REFUNDED")
        {
            stateStr = "拒绝退货";
        }

        return stateStr;
    }

    private static string getOrderStateStr(int orderState)
    {
        var stateStr = "";
        if (orderState == 0)
        {
            stateStr = "待领单";
        }
        else if (orderState == 1)
        {
            stateStr = "待发布";
        }
        else if (orderState == 2)
        {
            stateStr = "待抢单";
        }
        else if (orderState == 3)
        {
            stateStr = "待设计";
        }
        else if (orderState == 4)
        {
            stateStr = "设计中";
        }
        else if (orderState == 5)
        {
            stateStr = "设计完成";
        }
        else if (orderState == 6)
        {
            stateStr = "下单完成";
        }
        else if (orderState == 7)
        {
            stateStr = "已发货";
        }
        else if (orderState == 8)
        {
            stateStr = "已完成";
        }
        else if (orderState == 9)
        {
            stateStr = "已关闭";
        }
        return stateStr;
    }

    private static string getOrderCashStateStr(int orderState)
    {
        var stateStr = "";
        if (orderState == 0)
        {
            stateStr = "待审核";
        }
        else if (orderState == 1)
        {
            stateStr = "已审核";
        }
        else if (orderState == 2)
        {
            stateStr = "已退回";
        }

        return stateStr;
    }

    public static string getAfterSaleByState(int orderState)
    {
        var stateStr = "";
        if (orderState == 1)
        {
            stateStr = "待处理";
        }
        else if (orderState == 2)
        {
            stateStr = "售后主管审核";
        }
        else if (orderState == 3)
        {
            stateStr = "责任主管审核";
        }
        else if (orderState == 4)
        {
            stateStr = "已完成";
        }

        return stateStr;
    }

    #endregion
}