erp/Web/EFinance/ImportBill.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;

public partial class EFinance_ImportBill : BasePage
{
    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)
    {
        HttpPostedFile postedFile = xlsFile.PostedFile;
        if (!checkFile(postedFile))
        {
            litResult.Text = "选择上传的文件格式不正确,无法上传,格式是xls或xlsx!";
            return;
        }
        string exted = Path.GetExtension(postedFile.FileName);
        string filePath = System.Environment.GetEnvironmentVariable("TMP");
        //if (!Directory.Exists(filePath))
        //{
        //    Directory.CreateDirectory(filePath);
        //}
        string file = filePath + "\\ZhangDan" + DateTime.Now.ToFileTime() + exted;
        postedFile.SaveAs(file);
        if (File.Exists(file))
        {
            DataSet ds = ExcelDataSource(file);
            DataTable dt = ds.Tables[0];
            StringBuilder str = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                //订单号为空则当前行不导入
                string ctid = dr[0].ToString().Trim();
                if (ctid == "")
                    continue;
                string fileName = dr[4].ToString().Trim();
                string num = dr[5].ToString().Trim();
                string price = dr[6].ToString().Trim();
                string total = dr[7].ToString().Trim();
                string logistics = dr[8].ToString().Trim();
                string logisticsNo = dr[9].ToString().Trim();
                string logisticsAmount = dr[10].ToString().Trim();
                string weight = dr[11].ToString().Trim();
                string province = dr[12].ToString().Trim();
                string finishDate = dr[13].ToString().Trim();
                string suplierName = dr[14].ToString().Trim();

                CeErpTradeCell entity = null;
                entity = CeErpTradeCell.GetByCtid(ctid);
                string sProductName = "";
                if (entity != null)
                {
                    CeErpProduct eProduct = null;
                    eProduct = CeErpProduct.GetById(entity.ProductId);
                    if (eProduct != null)
                        sProductName = eProduct.PType;

                    CeErpZhangDan ZhangDanEntity = null;
                    ZhangDanEntity = CeErpZhangDan.GetBytid(ctid);

                    if (ZhangDanEntity == null)
                    {
                        str.Append(" insert into Ce_ErpZhangDan(num,tid,batchNo,price,total,logistics,logisticsNo,logisticsAmount,weight,fileName,province,supplierName,unusual,explain,importDate,importUserId,shopId,state,finishDate)");
                        str.AppendFormat(" Values({9},'{0}','',{1},{2},'{3}','{4}',{5},{6},'{7}','{8}','{11}','','',getdate(),{10},1,1,'{12}');", 
                                           ctid, price, total, logistics, logisticsNo, logisticsAmount, weight, fileName, province, num,CurrentUser.UserID, suplierName, finishDate);
                    }
                }                
            }
            if (str.Length > 0)
            {
                DbHelper.DbConn.ExecuteNonQuery(str.ToString());
                litResult.Text = "导入成功!";
                ShowResult("导入成功", "closeFn()");
            }
            else
            {
                litResult.Text = "所有账单已存在，无法重复导入!";
                ShowResult("所有账单已存在，无法重复导入!");
            }
        }
    }

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