Npoi 导出操作多Sheet

 

 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="productfocusUAT.aspx.cs" Inherits="miniBI_productfocusUAT" %>

<%@ Import Namespace="System.Data" %>
<%
    if ( string.IsNullOrEmpty(Request.QueryString["product_code"]))
        return;
    var type = Request.QueryString["type"];

    string ConString = "Server=10.1.1.57;database=WingARCETL;uid=sa;pwd=Newcnbi01*";
    string BussConString = "Server=10.5.1.186;database=BusinessCenterService;uid=func_yao;pwd=funck123";
    string conString29 = @"Server=10.5.1.29\MSSQLSERVER2014;database=Distribute_IntelligenceDataCenter;uid=sa;pwd=!sasaadmin123";

    string productcode = Request.QueryString["product_code"];
    
   
    string productCmd = " select top 1 product_id  from etl_product where    product_code in ( '" + productcode.ToString().Trim() + "') ";
    var TempProduct_id = DataOperation.DataCenter.ExecuteScalar(productCmd, conString29);
   

    int product_id = Convert.ToInt32(TempProduct_id);   

    string trCmd = @"select transfer_to_store_id,store_code,product_id,sum(transfer_quantity)trQty  from etl_transfer a left join etl_store b on a.transfer_to_store_id=b.store_code_id
            where (transfer_status=2 or transfer_status=3) and store_type='S' and product_id=" + product_id + @"
            group by transfer_to_store_id,store_code,product_id";
    List<dynamic> TrQty = DataOperation.DataCenter.ExecuteReader(trCmd, conString29, true);

    string stockCmd = "select max(stockdate) stockdate from product_stock nolock where stockdate>='" + DateTime.Today.AddMonths(-1) + "'";
    var stockdate = DataOperation.DataCenter.ExecuteScalar(stockCmd, ConString);

    var cmdText = "EXEC QueryNewProductDeital @stock_date='" + stockdate + "', @product='" + productcode + "'";
    var ds = DataOperation.DataCenter.ExecuteDataSet(cmdText, ConString);

    //string strDt2 = string.Format("SELECT   po_number, product_code, store_code, po_qty, 'Open' AS po_status FROM po_list "
    //+ "WHERE (po_status = 'ou') AND (cancelled = 'N') AND (closed = 'n') AND (polinetype = 2) AND (product_code = {0}) ORDER BY store_code", productcode);
    string strDt2 = string.Format(@" select  t1.*,  POleadtime=  convert(varchar(10),
                                    dateadd(d,   re.dtt_store  ,creation_date)  ,23) from (SELECT  po.creation_date ,po_number, product_code, po.store_code, po_qty, 'Open' AS po_status 
                                    ,su.supplier_id,st.store_code_id
                                    FROM po_list po     left join     [etl_supplier] Su on po.supplier_code=su.supplier_code
                                    left join     [etl_store] St on po.store_code =st.store_code  
                                    WHERE (po_status = 'ou') AND (cancelled = 'N') AND (closed = 'n') AND (polinetype = 2)
                                    AND (product_code = '{0}') )T1                                     
                                    left join     (select    supplier_id,store_code_id , max(dtt_store) dtt_store from    etl_product_replenishment (nolock)
                                    group by   supplier_id,store_code_id ) 
                                    re on T1.supplier_id  =re.supplier_id and t1.store_code_id=re.store_code_id
 
                                    ORDER BY t1.store_code", productcode);

    var ds4 = DataOperation.DataCenter.ExecuteTable("SELECT ProductCode,ContractRtv,(case when PromotionKickback=1 then 'Y' when PromotionKickback=0 then 'N' else '/' end) as PromotionKickback,0.00 as CRetailPrice,0.00 as CGp  FROM L_NewProductAttr  WHERE ProductCode='" + productcode + "'", BussConString);
    var rs1 = ds.Tables[0];
    List<dynamic> rs2 = DataOperation.DataCenter.ExecuteReader(strDt2, ConString, true);
    var rs3 = ds.Tables[1];

    if (rs1.Rows.Count == 0 || (rs1.Rows.Count == 0 && rs3.Rows.Count == 0))
        return;
    if (rs1.Rows.Count > 0)
    {
        int tQty = rs3.AsEnumerable().Sum(t => Convert.ToInt32(t["netQTY"]));
        if ((Convert.ToInt32(rs1.Rows[0]["whTotal"]) == 0) && tQty == 0)
        {
           // return;
        }
    }
    DateTime? launchDate = rs1.Rows[0]["LaunchDate"] + "" != "" ? (DateTime?)DateTime.Parse(rs1.Rows[0]["LaunchDate"].ToString()) : null;
    decimal? saleMonths = null;
    if (launchDate.HasValue)
    {
        if (launchDate.Value.ToString("yyyy-MM") == DateTime.Today.AddDays(-1).ToString("yyyy-MM"))
            saleMonths = 1M;
        else if (launchDate.Value.ToString("yyyy-MM") == DateTime.Today.AddDays(-1).AddMonths(-1).ToString("yyyy-MM"))
            saleMonths = 2M;
        else
            saleMonths = 3M;
    }
    List<DataRow> removeRow = new List<DataRow>();
    for (int i = 0; i < rs3.Rows.Count; i++)
    {
        DataRow item = rs3.Rows[i];
        var closed = item["DateClosed"] + "";
        int OnhandQTY = item["OnhandQTY"] + "" != "" ? Convert.ToInt32(item["OnhandQTY"]) : 0;
        var avgQty = item["avgQTY"] + "" != "" ? Convert.ToDecimal(item["avgQTY"]) : 0M;
        if (closed != "")
        {
            if (DateTime.Parse(closed) < DateTime.Today.AddMonths(-3))  //关店超过3月的直接排除
            {
                removeRow.Add(item); continue;
            }
            else if (DateTime.Parse(closed) < DateTime.Today & DateTime.Parse(closed) >= DateTime.Today.AddMonths(-3)) //关店不到3个月的，库存和3个月销售为0的排除
            {
                if (OnhandQTY + avgQty == 0)
                {
                    removeRow.Add(item); continue;
                }
            }
        }
        int saleQty = Convert.ToInt32(item["MTD_QTY"]) + Convert.ToInt32(item["l_2m"]) + Convert.ToInt32(item["l_3m"]);
        item["avgQTY"] = saleMonths.HasValue ? Math.Round(saleQty / saleMonths.Value, 2) : 0;
        var storeCode = item["storecode"].ToString().Trim();
        var trqty = TrQty.SingleOrDefault(t => t.store_code == storeCode && t.product_id == product_id);
        if (trqty != null)
            item["trQty"] = trqty.trQty;
        int trnum = item["trQty"] + "" != "" ? Convert.ToInt32(item["trQty"]) : 0;
        var rcvDate = item["rcvdate"] + "" != "" ? Convert.ToDateTime(item["rcvdate"]) : DateTime.Today;
        var saleDays = (DateTime.Today - rcvDate).TotalDays;
        var netQty = Convert.ToInt32(item["netQTY"]);
        item["CurrentTurnday"] = saleMonths.HasValue ? saleQty > 0 ? Math.Round((trnum + OnhandQTY) / (saleQty / saleMonths.Value / 30.5M), 2) : 9999 : 0;
    }
    foreach (var item in removeRow)
    {
        rs3.Rows.Remove(item);
    }

    var mtdAmount = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["MTD_Aount"]));
    var mtdCost = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["MTD_Cost"]));
    var mtdQty = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["MTD_QTY"]));
    object CRetailPrice = null, CGp = null;
    if (mtdAmount == 0M || mtdCost == 0M)
    {
        var saleQty = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["netQTY"]));
        var netAmount = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["netAmount"]));
        var saleCost = rs3.AsEnumerable().Sum(t => Convert.ToDecimal(t["SalesCost"]));
        if (netAmount != 0)
        {
            CRetailPrice = saleQty != 0M ? Math.Round(netAmount / saleQty, 2) : 0;
            CGp = netAmount != 0M ? (netAmount - saleCost) / netAmount : 0;
        }
        else
        {
            CRetailPrice = rs1.Rows[0]["retailprice"];
            CGp = rs1.Rows[0]["GP"];
        }
    }
    else
    {
        CRetailPrice = mtdQty != 0M ? Math.Round(mtdAmount / mtdQty, 2) : 0;
        CGp = mtdAmount != 0M ? (mtdAmount - mtdCost) / mtdAmount : 0;
    }
    int rowi = 0;
    int coli = 0;
%>
<table width="891" cellpadding="0" cellspacing="0">
    <colgroup>
        <col width="104" style="width: 104px;" />
        <col width="99" style="width: 99px;" />
        <col width="65" style="width: 65px;" />
        <col width="82" style="width: 83px;" />
        <col width="37" style="width: 37px;" />
        <col width="75" style="width: 75px;" />
        <col width="64" style="width: 64px;" span="2" />
        <col width="72" style="width: 72px;" />
        <col width="60" style="width: 60px;" span="3" />
        <col width="48" style="width: 48px;" />
    </colgroup>
    <tbody>
        <tr class="firstRow" style="height: 28px; text-align: center">
            <td width="891" style="border-width: 1px 1px 0px; border-style: solid solid none;
                border-color: windowtext black steelblue windowtext; background-color: transparent;
                text-align: center" colspan="13"   rowspan='1' row='<%=rowi%>' col='<%=coli=0%>' colStyle='top1'>
                <strong><span style="color: rgb(70, 130, 180); font-family: 宋体; font-size: 24px;">新品报表</span></strong>
            </td>
        </tr>
        <tr style="height: 17px;">
            <td width="104" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1"   rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='yestop'> 
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">货号</span></strong>
            </td>
            <td width="99" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">品牌</span></strong>
            </td>
            <td width="185" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="3"   rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">名称</span></strong>
            </td>
            <td width="75" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center"  colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1+2)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">采购员</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center"  colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">供货源</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center"  colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">当前售价</span></strong>
            </td>
            <td width="72" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center"  colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">毛利率</span></strong>
            </td>
            <td style="border-width: 0px 1px 0px 0px; border-style: none solid none none; border-color: windowtext black windowtext windowtext;
                background-color: transparent;"  colspan="4"  rowspan='8'  row='<%=(rowi)%>' col='<%=(coli+=1)%>'  colStyle='no'>
            </td>
        </tr>
        <tr style="height: 40px;">
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent; vnd.ms-excel.numberformat: @;"  colspan="1"   rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["productcode"].ToString().Trim() %></span>
            </td>
            <td width="99" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: windowtext; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["brand_cn"] %></span>
            </td>
            <td width="185" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext; background-color: transparent;" colspan="3"   rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["chnname"]%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1+2)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["BuyerCode"] %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["supply_source"] %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["retailprice"].ToString() != "" ? Convert.ToDecimal(rs1.Rows[0]["retailprice"]).ToString("f2") : ""%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(rs1.Rows[0]["GP"].ToString()!=""?Convert.ToDecimal( rs1.Rows[0]["GP"]).ToString("p2"):"") %></span>
            </td>
        </tr>
        <tr style="height: 9px;">
            <td style="border-width: 0px 0px 0px 1px; border-style: none none none solid; border-color: windowtext;
                background-color: transparent;"  colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
            </td>
            <td width="99" style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td width="65" style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td width="83" style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
        </tr>
        <tr style="height: 17px;">
            <td width="104" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">货品状态</span></strong>
            </td>
            <td width="99" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">上市日期</span></strong>
            </td>
            <td width="65" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">退货率</span></strong>
            </td>
            <td width="83" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">促销补差</span></strong>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td width="75" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">BJLF</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">GZLF</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">SHLF</span></strong>
            </td>
            <td width="72" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">SHDG</span></strong>
            </td>
        </tr>
        <tr style="height: 17px;">
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;"  colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["status"] %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(rs1.Rows[0]["LaunchDate"].ToString()!=""?Convert.ToDateTime(rs1.Rows[0]["LaunchDate"]).ToShortDateString():"") %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(ds4.Rows.Count>0?ds4.Rows[0]["ContractRtv"]:"") %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(ds4.Rows.Count > 0 ? ds4.Rows[0]["PromotionKickback"] : "")%></span>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["BJLFW"].ToString()!=""?Convert.ToInt32(rs1.Rows[0]["BJLFW"]):0 %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["GZLFW"].ToString() != "" ? Convert.ToInt32(rs1.Rows[0]["GZLFW"]) : 0%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["SHLFW"].ToString() != "" ? Convert.ToInt32(rs1.Rows[0]["SHLFW"]) : 0%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs1.Rows[0]["SHDG"].ToString() != "" ? Convert.ToInt32(rs1.Rows[0]["SHDG"]) : 0%></span>
            </td>
        </tr>
        <tr style="height: 6px;">
            <td style="border-width: 0px 0px 0px 1px; border-style: none none none solid; border-color: windowtext;
                background-color: transparent;"  colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>'  colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
        </tr>
        <tr style="height: 17px;">
            <td width="104" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">有效零售价</span></strong>
            </td>
            <td width="99" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">有效毛利率</span></strong>
            </td>
            <td width="65" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">累计销售</span></strong>
            </td>
            <td width="83" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">有销售店铺数</span></strong>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>'  colStyle='no'>
            </td>
            <td width="75" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">采购单</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">收货位置</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>'  colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">采购数量</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                
                 <strong><span style="color: rgb(255, 255, 255); font-family: 宋体; font-size: 12px;">预计到货日期</span></strong>
                </td>
        </tr>
        <tr style="height: 17px;">
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=CRetailPrice!=null?Convert.ToDecimal(CRetailPrice).ToString("f2"):""%>
                </span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(CGp+"")!=""?Convert.ToDecimal(CGp).ToString("p2"):""%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t=>t["netQTY"].ToString()!="").Sum(t=>Convert.ToInt32(t["netQTY"])) %></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["SalesStoreCount"].ToString() != "").Sum(t => Convert.ToInt32(t["SalesStoreCount"]))%></span>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(rs2.Count > 0 ? rs2[0].po_number : "")%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(rs2.Count > 0 ? rs2[0].store_code : "")%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=(rs2.Count > 0 ? rs2[0].po_qty : "")%></span>
            </td>
            <td  style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;"> <%=(rs2.Count > 0 ? (!string.IsNullOrEmpty(rs2[0].POleadtime) ? rs2[0].POleadtime:"") : "")%>
                </span>
                </td>
        </tr>
        <%
            if (rs2.Count > 1)
            {
                for (int i = 1; i < rs2.Count; i++)
                {
        %>
        <tr style="height: 17px;">
            <td style="border-width: 0px 0px 0px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='no'>
            </td>
            <td colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=( rs2[i].po_number  )%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=( rs2[i].store_code  )%></span>
            </td>
            <td style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=( rs2[i].po_qty )%></span>
            </td>
            <td  style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            <span style="font-size: 12px;"> <%=(string.IsNullOrEmpty(rs2[i].POleadtime) ? "" : rs2[i].POleadtime.ToString())%>
                </span>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'></td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'></td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'></td>
            <td style="border-width: 0px 1px 0px 0px; border-style: none solid solid none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'></td>
             
        </tr>
        <%
                }

            }
        %>
        <tr style="height: 17px;">
            <td style="border-width: 0px 0px 0px 1px; border-style: none none none solid; border-color: windowtext;
                background-color: transparent;" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;"colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border: 0px windowtext; border-image: none; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
            <td style="border-width: 0px 1px 0px 0px; border-style: none solid none none; border-color: windowtext;
                background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
            </td>
        </tr>
        <tr style="height: 32px;">
            <td width="104" style="border: 1px solid windowtext; border-image: none; background-color: steelblue;
                text-align: center" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">ＯＭ</span></strong>
            </td>
            <td width="99" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">店铺</span></strong>
            </td>
            <td width="65" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">自动补货<br />
                </span><span style="color: rgb(255, 255, 255); font-size: 12px;">状态</span></strong>
            </td>
            <td width="83" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">店铺等级</span></strong>
            </td>
            <td width="37" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">面积</span></strong>
            </td>
            <td width="75" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">店铺库存</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">已建TR单</span></strong>
            </td>
            <td width="64" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">陈列量</span></strong>
            </td>
            <td width="72" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">上上月销售</span></strong>
            </td>
            <td width="60" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">上月销售</span></strong>
            </td>
            <td width="60" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">本月销售</span></strong>
            </td>
            <td width="60" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">3个月平均月销售</span></strong>
            </td>
            <td width="48" style="border-width: 1px 1px 1px 0px; border-style: solid solid solid none;
                border-color: windowtext windowtext windowtext white; background-color: steelblue;
                text-align: center" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='yestop'>
                <strong><span style="color: rgb(255, 255, 255); font-size: 12px;">周转天</span></strong>
            </td>
        </tr>
        <%
            var oms = rs3.AsEnumerable().Select(t => t["omstaffname"].ToString()).Distinct().ToArray();
            foreach (var om in oms)
            {
                int i = 0;
                var stores = rs3.AsEnumerable().Where(t => t["omstaffname"].ToString() == om).ToArray();
                foreach (var item in stores)
                {
        %>
        <tr style="height: 17px;">
            <% if (i == 0)
               { %>
            <td width="104" style="border-width: 0px 1px 1px 1px; border-style: none solid solid solid;
                border-color: black windowtext windowtext windowtext; background-color: white;
                vertical-align: middle"  colspan="1" rowspan='<%=stores.Length+1 %>'    row='<%=(rowi+=1)%>' col='<%=coli=0%>'  colStyle='no'>
                <span style="font-size: 12px;">
                    <%=om%></span>
            </td>
            <%} %>
            <td width="99" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(i == 0?rowi :rowi+=1)%>' col='<%=i == 0?(coli+=1):coli=1%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["storecode"] %></span>
            </td>
            <td width="65" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["XrsStatus"] %></span>
            </td>
            <td width="83" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["storegrade"] %></span>
            </td>
            <td width="37" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["squareft"] %></span>
            </td>
            <td width="75" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToInt32( item["OnhandQTY"]) %></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToInt32( item["trQty"] )%></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["mss"]!=DBNull.Value? Convert.ToInt32( item["mss"]):0 %></span>
            </td>
            <td width="72" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToInt32( item["l_3m"]) %></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToInt32( item["l_2m"]) %></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToInt32( item["MTD_QTY"]) %></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=item["avgQTY"] %></span>
            </td>
            <td width="48" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='no'>
                <span style="font-size: 12px;">
                    <%=Convert.ToDecimal( item["CurrentTurnday"]).ToString("f0") %></span>
            </td>
        </tr>
        <% 
               i++;
                }
        %>
        <tr style="height: 17px; font-weight: bold;">
            <td width="99" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1"  rowspan='1' row='<%=(rowi+= 1)%>' col='<%=coli=1%>' colStyle='total'>
                <span style="font-size: 12px; font-weight: bold">汇总</span>
            </td>
            <td width="65" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="83" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="37" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="75" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["OnhandQTY"].ToString() != "").Sum(t => Convert.ToInt32(t["OnhandQTY"]))%></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["trQty"].ToString() != "").Sum(t => Convert.ToInt32(t["trQty"]))%></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["mss"].ToString() != "").Sum(t => Convert.ToInt32(t["mss"]))%></span>
            </td>
            <td width="72" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["l_3m"].ToString() != "").Sum(t => Convert.ToInt32(t["l_3m"]))%></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["l_2m"].ToString() != "").Sum(t => Convert.ToInt32(t["l_2m"]))%></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["MTD_QTY"].ToString() != "").Sum(t =>Convert.ToInt32(t["MTD_QTY"]))%>
                </span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=stores.Where(t => t["avgQTY"].ToString() != "").Sum(t => Convert.ToDecimal( t["avgQTY"]))%>
                </span>
            </td>
            <td width="48" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=rowi%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%
                decimal? trqty = stores.Where(t => t["trQty"].ToString() != "").Sum(t => Convert.ToInt32(t["trQty"]));
                decimal? onhand = stores.Where(t => t["OnhandQTY"].ToString() != "").Sum(t => Convert.ToInt32(t["OnhandQTY"]));
                decimal? avgQty = stores.Where(t => t["avgQTY"].ToString() != "").Sum(t => Convert.ToDecimal(t["avgQTY"]));
                    %>
                    <%=(avgQty>0?((trqty+onhand)/(avgQty/30.5M)).Value.ToString("f0"):"") %>
                </span>
            </td>
        </tr>
        <%
            }
        %>
        <tr style="height: 17px; font-weight: bold;">
            <td width="104" style="border-style: none solid solid solid; border-width: 0px 1px 1px 1px;
                border-color: windowtext;" colspan="1"  rowspan='1' row='<%=(rowi+=1)%>' col='<%=coli=0%>' colStyle='total'>
                <strong><span style="font-size: 12px;">汇总</span></strong>
            </td>
            <td width="99" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="65" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="83" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="37" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
            </td>
            <td width="75" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["OnhandQTY"].ToString() != "").Sum(t => Convert.ToInt32(t["OnhandQTY"]))%></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["trQty"].ToString() != "").Sum(t => Convert.ToInt32(t["trQty"]))%></span>
            </td>
            <td width="64" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["mss"].ToString() != "").Sum(t => Convert.ToInt32(t["mss"]))%></span>
            </td>
            <td width="72" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["l_3m"].ToString() != "").Sum(t => Convert.ToInt32(t["l_3m"]))%></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["l_2m"].ToString() != "").Sum(t => Convert.ToInt32(t["l_2m"]))%></span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["MTD_QTY"].ToString() != "").Sum(t => Convert.ToInt32(t["MTD_QTY"]))%>
                </span>
            </td>
            <td width="60" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%=rs3.AsEnumerable().Where(t => t["avgQTY"].ToString() != "").Sum(t =>Convert.ToDecimal( t["avgQTY"]))%>
                </span>
            </td>
            <td width="48" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none;
                border-color: black windowtext windowtext black; background-color: transparent;
                background-color: #C0C0C0;" colspan="1" rowspan='1' row='<%=(rowi)%>' col='<%=(coli+=1)%>' colStyle='total'>
                <span style="font-size: 12px;">
                    <%
                        decimal? trqty1 = rs3.AsEnumerable().Where(t => t["trQty"].ToString() != "").Sum(t => Convert.ToInt32(t["trQty"]));
                        decimal? onhand1 = rs3.AsEnumerable().Where(t => t["OnhandQTY"].ToString() != "").Sum(t => Convert.ToInt32(t["OnhandQTY"]));
                        decimal? avgQty1 = rs3.AsEnumerable().Where(t => t["avgQTY"].ToString() != "").Sum(t => Convert.ToDecimal(t["avgQTY"]));
                    %>
                    <%=(avgQty1>0?((trqty1+onhand1)/(avgQty1/30.5M)).Value.ToString("f0"):"") %>
                </span>
            </td>
        </tr>
    </tbody>
</table>

以上是WEB页面设定。

 

 

  public static void htmlToExcleXLSX()
        { 
            #region
            var html = @"
<table width='891' cellpadding='0' cellspacing='0' border='1' style='font-size: 12px;'>
<tr>
                <td   style='border-width: 1px 1px 0px; border-style: solid solid none;
                border-color: windowtext black steelblue windowtext; background-color: transparent;
                text-align: center' rowspan='2' colspan='1' row='0' col='0'>名称1</td>
                <td rowspan='2' colspan='1' row='0' col='1'>名称2</td>
                <td rowspan='2' colspan='1' row='0' col='2'>名称3</td>
                <td rowspan='1' colspan='4' row='0' col='3'>名称4</td>
                <td rowspan='1' colspan='4' row='0' col='7'>名称5</td>
                <td rowspan='1' colspan='4' row='0' col='11'>名称6</td>
                <td rowspan='1' colspan='4' row='0' col='15'>名称7</td>
                <td rowspan='2' colspan='1' row='0' col='19'>名称8</td>
                <td rowspan='2' colspan='1' row='0' col='20'>名称9</td>
                <td rowspan='2' colspan='1' row='0' col='21'>备注</td>
            </tr>
            <tr>
                <td rowspan='1' colspan='1' row='1' col='3'>效果1</td>
                <td rowspan='1' colspan='1' row='1' col='4'>效果2</td>
                <td rowspan='1' colspan='1' row='1' col='5'>效果3</td>
                <td rowspan='1' colspan='1' row='1' col='6'>效果4</td>
                <td rowspan='1' colspan='1' row='1' col='7'>效果5</td>
                <td rowspan='1' colspan='1' row='1' col='8'>效果6</td>
                <td rowspan='1' colspan='1' row='1' col='9'>效果7</td>
                <td rowspan='1' colspan='1' row='1' col='10'>效果8</td>
                <td rowspan='1' colspan='1' row='1' col='11'>效果9</td>
                <td rowspan='1' colspan='1' row='1' col='12'>效果10</td>
                <td rowspan='1' colspan='1' row='1' col='13'>效果11</td>
                <td rowspan='1' colspan='1' row='1' col='14'>效果12</td>
                <td rowspan='1' colspan='1' row='1' col='15'>效果13</td>
                <td rowspan='1' colspan='1' row='1' col='16'>效果14</td>
                <td rowspan='1' colspan='1' row='1' col='17'>效果15</td>
                <td rowspan='1' colspan='1' row='1' col='18'>效果16</td>
            </tr>

</table>";
            #endregion
             
            IWorkbook workbook = new XSSFWorkbook();

            string conString186 = System.Configuration.ConfigurationManager.ConnectionStrings["conString186"].ConnectionString;
            List<dynamic> FocusProductList = new List<dynamic>();
            string reslutSql = string.Format(@" select   brand as BrandCode,product_code  ,product_id from [D_No5Report_Product] 
                                                where [Re_type]='HotSaleByWeek'  order by brand,product_code ", "");
            FocusProductList = DataOperation.DataCenter.ExecuteReader(reslutSql, conString186, true);
            foreach (var item in FocusProductList)
            {
                if (string.IsNullOrEmpty(item.product_code)) { continue; }
                var sheetName = item.product_code;
                var sheet = workbook.CreateSheet(sheetName);
                HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://10.5.120.15/miniBI/productfocusUAT.aspx?type=excel&product_code=" + item.product_code);//10101020300016-10101010102843
                req.Method = "get";
                HttpWebResponse response = (HttpWebResponse)req.GetResponse();
                StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
                var result = reader.ReadToEnd().Trim();
                html = result.Trim();
                html = Regex.Replace(html, "[\f\n\r\t\v]", "");
                html = Regex.Replace(html, " {2,}", " ");
                html = Regex.Replace(html, ">[ ]{1}", ">");
                html = Regex.Replace(html, @"\s", "");

                string rowContent = string.Empty;
                MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>",
                                   RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对tr进行筛选 
                #region//写在tr循环中
                for (int i = 0; i < rowCollection.Count; i++)
                {
                    IRow row = (IRow)sheet.CreateRow(i);
                    rowContent = rowCollection[i].Value;
                    MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>",
                          RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //对td进行筛选
                    //遍历td
                    for (int j = 0; j < columnCollection.Count; j++)
                    {
                        var match = Regex.Match(columnCollection[j].Value,
                                    "<td.*?colspan=\"(?<col>.*?)\".*?rowspan=\'(?<row>.*?)\'.*?row=\'(?<row1>.*?)\'.*?col=\'(?<col1>.*?)\'.*?colStyle=\'(?<colSty>.*?)\'>(?<value>.*?)<\\/td>",
                        RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);

                        var matchWith = Regex.Match(columnCollection[j].Value,
                                        "<tdwidth=\"(?<colWidth>.*?)\".*?colspan=\"(?<col>.*?)\".*?rowspan=\'(?<row>.*?)\'.*?row=\'(?<row1>.*?)\'.*?col=\'(?<col1>.*?)\'.*?colStyle=\'(?<colSty>.*?)\'>(?<value>.*?)<\\/td>",
                     RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
                        int tempWidth = 1;
                        if (matchWith.Success)
                        {
                            tempWidth = Convert.ToInt32(matchWith.Groups["colWidth"].Value);
                        }
                        if (!match.Success)
                        {
                            match = Regex.Match(columnCollection[j].Value,
                                     "<td.*?colspan=\"(?<col>.*?)\".*?rowspan=\'(?<row>.*?)\'.*?row=\'(?<row1>.*?)\'.*?col=\'(?<col1>.*?)\'><\\/td>",
                                     RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);
                        }
                        if (match.Success)
                        {
                            int rowspan = Convert.ToInt32(match.Groups["row"].Value);//表格跨行
                            int colspan = Convert.ToInt32(match.Groups["col"].Value);//表格跨列
                            int rowcount = Convert.ToInt32(match.Groups["row1"].Value);//所在行
                            int col = Convert.ToInt32(match.Groups["col1"].Value);//所在列
                            var colSty = match.Groups["colSty"].Value;//所在列的Style
                            string value = match.Groups["value"].Value;
                            value = Regex.Replace(value, ".*?<spanstyle=\".*?>", "");
                            value = Regex.Replace(value, "<\\/span>.*?([\\w\\W]*)", "");
                            if (value == "采购员")
                            {
                                value = "采购员";
                            }
                            if (colspan == 1)//判断是否跨列
                            {
                                var cell = row.CreateCell(col);//创建列 
                                // row.GetCell(col).CellStyle = Getcellstyle(workbook, (dynamic)colSty); //headStyle; ;
                                switch (colSty)
                                {
                                    case "yestop":
                                        row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.yestop); ;
                                        break;
                                    case "top1":
                                        row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.top1);
                                        break;
                                    case "total":
                                        row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.total);
                                        break;

                                    /* 您可以有任意数量的 case 语句 */
                                    default: /* 可选的 */
                                        ;
                                        break;
                                }
                                dynamic tempCellValue = value != null ? value.ToString() : string.Empty;
                                tempCellValue = GetTempCellValue(tempCellValue);
                                cell.SetCellValue(tempCellValue);//设置列的值

                                // cell.SetCellValue(value);//设置列的值
                                if (value.Length > 0)
                                {
                                    int width = value.Length * 25 / 6;
                                    if (width > 255)
                                        width = 250;
                                    sheet.SetColumnWidth(col, tempWidth > 0 ? tempWidth * 32 : width * 256);
                                }
                            }
                            //判断是否跨行、跨列
                            if (rowspan > 1 || colspan > 1)
                            {
                                int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0;
                                if (rowspan > 1)//跨行
                                {
                                    firstRow = rowcount;
                                    lastRow = firstRow + rowspan - 1;
                                }
                                else
                                {
                                    firstRow = lastRow = i;
                                }
                                if (colspan > 1)//跨列
                                {
                                    firstCol = col;
                                    int cols = col + colspan;
                                    for (; col < cols; col++)
                                    {

                                        var cell = row.CreateCell(col);
                                        //row.GetCell(col).CellStyle = Getcellstyle(workbook, (dynamic)colSty); //headStyle; ;
                                        switch (colSty)
                                        {
                                            case "yestop":
                                                row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.yestop); ;
                                                break;
                                            case "top1":
                                                row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.top1);
                                                break;
                                            case "total":
                                                row.GetCell(col).CellStyle = Getcellstyle(workbook, stylexls.total);
                                                break;

                                            /* 您可以有任意数量的 case 语句 */
                                            default: /* 可选的 */
                                                ;
                                                break;
                                        }
                                        dynamic tempCellValue = value != null ? value.ToString() : string.Empty;
                                        tempCellValue = GetTempCellValue(tempCellValue);
                                        cell.SetCellValue(tempCellValue);//设置列的值
                                        // cell.SetCellValue(value);
                                    }
                                    lastCol = col - 1;
                                }
                                else
                                {
                                    firstCol = lastCol = col;
                                }
                                //关键是这里，设置起始行数，结束行数；起始列数，结束列数
                                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
                            }

                        }

                    }

                }
                #endregion
            }

            string FilePath = AppDomain.CurrentDomain.BaseDirectory + "Files\\" + DateTime.Today.ToString("yyyy-MM-dd") + "\\";
            if (!Directory.Exists(FilePath)) Directory.CreateDirectory(FilePath);
            string name = "测试多表头(" + DateTime.Today.ToString("yyyy-MM-dd") + ").xlsx";
            string fileName = FilePath + name;

            string year = DateTime.Now.Year.ToString();

            FileStream file = new FileStream(fileName, FileMode.Create);
            workbook.Write(file);
            file.Close(); 


        }
        /// <summary>
        /// // 解决数据导出时纯数字字段被以字符形式生成到excel中，单元格左上角出现绿三角
        /// </summary>
        /// <param name="tempCellValue"></param>
        /// <returns></returns>
        public static dynamic GetTempCellValue(dynamic tempCellValue)
        {
            //prointNo = 0;
            if (!string.IsNullOrEmpty(tempCellValue))
            {
                try
                {
                    if (IsInteger(tempCellValue))
                    { return tempCellValue = int.Parse(tempCellValue); }//整数
                    else if (IsAllNumber(tempCellValue) == true && IsInteger(tempCellValue) == false)
                    {
                        //prointNo = tempCellValue.Length - tempCellValue.IndexOf('.') - 1;
                        return double.Parse(tempCellValue);
                    }//浮点数
                    else
                        return tempCellValue;
                }
                catch
                {
                    return tempCellValue;
                }

                //row.CreateCell(b).SetCellValue(IsAllNumber(tempCellValue) ? Double.Parse(tempCellValue) : 0);
            }
            return tempCellValue;
        }
        private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
        {

            //ICellStyle Top1ColStyle = workbook.CreateCellStyle();//第一行、列
            //        Top1ColStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //        XSSFFont top1font = (XSSFFont)workbook.CreateFont();
            //        top1font.IsBold = true;//加粗  
            //        top1font.Color = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;//颜色
            //        Top1ColStyle.SetFont(top1font);

            //ICellStyle TotalStyle = workbook.CreateCellStyle();//第一行、列
            //           TotalStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//设置表头颜色
            //           TotalStyle.FillPattern = FillPattern.SolidForeground;//设置表头颜色
            //           XSSFFont totalfont = (XSSFFont)workbook.CreateFont();
            //           totalfont.IsBold = true;//加粗  
            //           totalfont.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//颜色  
            //           TotalStyle.SetFont(totalfont);

            //    ICellStyle headStyle = workbook.CreateCellStyle();//设置表头颜色              
            //    headStyle.FillPattern = FillPattern.SolidForeground;//设置表头颜色
            //    headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;//设置表头颜色
            //    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //    XSSFFont font = (XSSFFont)workbook.CreateFont();
            //    font.IsBold = true;//加粗                  
            //    font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;//颜色 
            //    headStyle.SetFont(font);
               
            ICellStyle cellStyle = wb.CreateCellStyle();
            XSSFFont font = (XSSFFont)wb.CreateFont();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
            //自动换行  
            cellStyle.WrapText = true;

            //缩进;当设置为1时，前面留的空白太大了。改进。或者是我设置的不对  
            cellStyle.Indention = 0;

            //上面基本都是设共公的设置  
            //下面列出了常用的字段类型  
            switch (str)
            {

                case stylexls.top1:
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    font.Color = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;//颜色 
                    cellStyle.SetFont(font);
                    break;
                case stylexls.yestop:                 
                    cellStyle.FillPattern = FillPattern.SolidForeground;//设置表头颜色
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;//设置表头颜色
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; 
                    font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;//颜色 
                    cellStyle.SetFont(font);
                    break;
                case stylexls.total:
                   // cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//设置表头颜色
                    cellStyle.FillPattern = FillPattern.SolidForeground;//设置表头颜色                      
                    font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//颜色  
                    cellStyle.SetFont(font);
                    break;
                case stylexls.头:

                    cellStyle.SetFont(font);
                    break;
                case stylexls.时间:
                    IDataFormat datastyle = wb.CreateDataFormat();

                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.数字:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.钱:
                    IDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("￥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.url:
                   // fontcolorblue.Underline = 1;
                    cellStyle.SetFont(font);
                    break;
                case stylexls.百分比:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.中文大写:
                    IDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.科学计数法:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.默认:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;
        }
        #region 定义单元格常用到样式的枚举
        public enum stylexls
        {
            yestop,
            total,
            top1,
            头,
            url,
            时间,
            数字,
            钱,
            百分比,
            中文大写,
            科学计数法,
            默认
        }
        #endregion