erp/Web/EAfterSale/AfterSaleImportUpload.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 EAfterSale_AfterSaleImportUpload : 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 str = new StringBuilder();
            List<object> keys = new List<object>();
            string ctids_add = "";
            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;
                if (ctids_add.IndexOf(ctid) != -1)
                    continue;
                ctids_add += ctid;
                ctids_add += ",";
                string reason = row.GetCell(1).ToString().Trim();
                string method = "";
                if (row.Cells.Count >= 3)
                {
                    method = row.GetCell(2).ToString().Trim();
                }
                

                CeErpTradeCell entity = null;
                entity = CeErpTradeCell.GetByCtid(ctid);

                if (entity != null)
                {
                    str.AppendFormat("update ce_erptradecell with(rowlock) set AfterSaleState=1,AfterSaleReason={1},AfterSaleMethod={2},AfterSaleTime=getdate() where ctid={0} ;", ctid, reason, method);
                    i++;
                }
                

            }
            if (i > 1)
            {
                try
                {
                    DbHelper.DbConn.ExecuteNonQuery(str.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;
    }

    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;
        }
    }
}