erp/Web/EFinance/ImportSample.aspx.cs

using SQLData;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SiteCore;
using BizCom;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;


public partial class EFinance_ImportSample : ReportBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
        }
    }

    private bool checkFile(HttpPostedFile postedFile)
    {
        if (postedFile != null && postedFile.ContentLength > 0)
        {
            string fileName = postedFile.FileName;
            if (fileName.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) == -1)
                return false;
            string fileType = postedFile.ContentType;
            if (fileType == "application/vnd.ms-excel" || fileType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                return true;
        }
        return false;
    }

    protected void btnSure_Click(object sender, EventArgs e)
    {

        if (Request.Files.Count > 0 && Request.Files[0].FileName != "")
        {
            HttpPostedFile file = Request.Files[0];
            UploadFile(file);

            iSheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = iSheet.GetRowEnumerator();
            
            int i = 0;
            StringBuilder sql = new StringBuilder();
            List<object> keys = new List<object>();
            while (rows.MoveNext())
            {
                if (i == 0) { i++; continue; }
                IRow row = (HSSFRow)rows.Current;
                ICell cell = row.GetCell(0);
                if (cell == null) break;
                //订单号为空则当前行不导入
                string ctid = row.GetCell(0).ToString().Trim();
                if (ctid == "")
                    continue;
                string ReceiveDate = "";
                string dateStr = row.GetCell(1).ToString().Trim();
                if (dateStr.IndexOf("/") < 3 && dateStr.IndexOf("/") > 0)
                {
                    string[] datelist = dateStr.Split(' ');
                    if (datelist.Length > 1)
                    {
                        string needdate = datelist[0];
                        string[] dlist = needdate.Split('/');
                        string changedt = "";
                        for (int idx = dlist.Length - 1; idx >= 0; idx--)
                        {
                            changedt += dlist[idx];
                            if (idx != 0)
                            {
                                changedt += "-";
                            }
                        }
                        ReceiveDate = changedt + " " + datelist[1];
                    }
                    else
                        ReceiveDate = "";
                    
                }
                else
                {
                    ReceiveDate = row.GetCell(1).ToString().Trim();
                }

                string AdvanceFee = row.GetCell(2).ToString().Trim();
                string Refund = row.GetCell(3).ToString().Trim();
                string ServiceFee = row.GetCell(4).ToString().Trim();
                string Commission = row.GetCell(5).ToString().Trim();
                string LogisticsFee = row.GetCell(6).ToString().Trim();


                CeErpTradeCell entity = null;
                entity = CeErpTradeCell.GetByCtid(ctid);
                CeErpTradeSample TradeSampleEntity = null;
                TradeSampleEntity = CeErpTradeSample.GetBytid(ctid);
                int supplierId = Convert.ToInt32(webConfig.SampleOrderSupplier);
                if (entity != null && TradeSampleEntity == null)
                {
                   
                    //新增拿样子表数据
                    sql.Append(" insert into CE_ErpTradeSample(ctid,ReceiveDate,AdvanceFee,Refund,Commission,ServiceFee,LogisticsFee,SampleDate,SampleUserId)");
                    sql.AppendFormat(" Values('{0}','{6}',{1},{2},{3},{4},{5},getdate(),{7});", ctid, AdvanceFee, Refund, Commission, ServiceFee, LogisticsFee, ReceiveDate, CurrentUser.UserID);
                    sql.AppendFormat("update CE_ErpTradeCell set IsSample=1 ,SupplierId={1},OrderState=6,seller_memo='拿样订单【导入】' where ctid='{0}';", ctid, supplierId);

                    CeErpTrade TradeEntity = null;
                    TradeEntity = CeErpTrade.Get(entity.tid);
                    if (TradeEntity != null)
                    {
                        //taobaoHelper.TradeMemoUpdate(ctid, TradeEntity.seller_nick, 5, "拿样订单【导入】");//修改备注，5为紫色旗子
                        string res = apiHelper.API_TradeMemoUpdate(TradeEntity.tid, TradeEntity.posCode, "PURPLE", "拿样订单【导入】");
                    }

                    i++;
                }

                
            }
            if (i > 1)
            {
                try
                {
                    DbHelper.DbConn.ExecuteNonQuery(sql.ToString());
                }
                catch (Exception ex)
                {
                    XLog.SaveLog(0, "导入拿样" + "," + ex.Message);
                }

                litResult.Text = "导入成功!";
                ShowResult("导入成功", "closeFn()");
            }
            else
            {
                litResult.Text = "所有订单号已存在，无法重复导入!";
                ShowResult("所有订单号已存在，无法重复导入!");
            }
        }
    }

    public void UploadFile(HttpPostedFile file)
    {
        //FileStream f = new FileStream(
        Stream s = file.InputStream;
        hssfworkbook = new HSSFWorkbook(s);
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

        dsi.Company = "领淘";
        hssfworkbook.DocumentSummaryInformation = dsi;

        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = excelName;
        hssfworkbook.SummaryInformation = si;
    }

    //protected void btnSure_Click(object sender, EventArgs e)
    //{
    //    HttpPostedFile postedFile = xlsFile.PostedFile;
    //    if (!checkFile(postedFile))
    //    {
    //        litResult.Text = "选择上传的文件格式不正确,无法上传,格式是xls或xlsx!";
    //        return;
    //    }
    //    string exted = Path.GetExtension(postedFile.FileName);
    //    string filePath = System.Environment.GetEnvironmentVariable("TMP");

    //    string file = filePath + "\\Sample" + DateTime.Now.ToFileTime() + exted;
    //    postedFile.SaveAs(file);
    //    if (File.Exists(file))
    //    {
    //        DataSet ds = ExcelDataSource(file);
    //        DataTable dt = ds.Tables[0];
    //        StringBuilder str = new StringBuilder();

    //        int importCnt = 0;
    //        foreach (DataRow dr in dt.Rows)
    //        {
    //            //订单号为空则当前行不导入
    //            string ctid = dr[0].ToString().Trim();
    //            if (ctid == "")
    //                continue;
    //            string ReceiveDate = dr[1].ToString().Trim();
    //            string AdvanceFee = dr[2].ToString().Trim();
    //            string Refund = dr[3].ToString().Trim();
    //            string ServiceFee = dr[4].ToString().Trim();
    //            string Commission = dr[5].ToString().Trim();
    //            string LogisticsFee = dr[6].ToString().Trim();

    //            CeErpTradeCell entity = null;
    //            entity = CeErpTradeCell.GetByCtid(ctid);
    //            CeErpTradeSample TradeSampleEntity = null;
    //            TradeSampleEntity = CeErpTradeSample.GetBytid(ctid);
    //            int supplierId = getSupplierIDByName(webConfig.SampleOrderSupplier);
    //            if (entity != null && TradeSampleEntity == null)
    //            {
    //                str = new StringBuilder();
    //                //新增拿样子表数据
    //                str.Append(" insert into CE_ErpTradeSample(ctid,ReceiveDate,AdvanceFee,Refund,Commission,ServiceFee,LogisticsFee,SampleDate,SampleUserId)");
    //                str.AppendFormat(" Values('{0}','{6}',{1},{2},{3},{4},{5},getdate(),{7});", ctid, AdvanceFee, Refund, Commission, ServiceFee, LogisticsFee, ReceiveDate,CurrentUser.UserID);
    //                str.AppendFormat("update CE_ErpTradeCell set IsSample=1 ,SupplierId={1},OrderState=6,seller_memo='拿样订单【导入】' where ctid='{0}';", ctid,supplierId);
    //                DbHelper.DbConn.ExecuteNonQuery(str.ToString());

    //                CeErpTrade TradeEntity = null;
    //                TradeEntity = CeErpTrade.Get(entity.tid);
    //                if (TradeEntity != null)
    //                {
    //                    //taobaoHelper.TradeMemoUpdate(ctid, TradeEntity.seller_nick, 5, "拿样订单【导入】");//修改备注，5为紫色旗子
    //                    apiHelper.API_TradeMemoUpdate(TradeEntity.tid, TradeEntity.posCode, "PURPLE", "拿样订单【导入】");
    //                }

    //                importCnt++;
    //            }
    //        }
    //        if (importCnt > 0)
    //        {                                
    //            litResult.Text = "导入成功!";
    //            ShowResult("导入成功", "closeFn()");
    //        }
    //        else
    //        {
    //            litResult.Text = "所有订单号已存在，无法重复导入!";
    //            ShowResult("所有订单号已存在，无法重复导入!");
    //        }
    //    }
    //}
    public int getSupplierIDByName(string name)
    {
        StringBuilder sql2 = new StringBuilder();
        sql2.AppendFormat("select top 1 * from CE_ErpSupplier where ComName like '%{0}%';", name);
        DataTable dt2 = DbHelper.DbConn.ExecuteDataset(sql2.ToString()).Tables[0];
        if (dt2.Rows.Count > 0)
        {
            return Convert.ToInt32(dt2.Rows[0]["ID"]);
        }
        return 0;
    }

    public static DataSet ExcelDataSource(string filepath)
    {

        //string strConn = "Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=yes;IMEX=1;'";
        string strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            ArrayList al = new ArrayList();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + al[0] + "]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            conn.Close();
            return ds;
        }
    }
}