ReadExcel and doExport & doUpdate

来源：互联网发布：网络回路测试编辑：程序博客网时间：2024/06/06 18:10

/// <summary>
    /// 使用頻率：低
    /// 讀取EXCEL內容
    /// </summary>
    /// <param name="excelPath">EXCEL路徑</param>
    /// <param name="exceltableName">EXCEL工作表名如sheet1,sheet2，不要加$</param>
    /// <returns>返回DataTable</returns>
    public static DataTable ReadExcel(string excelPath, string exceltableName)
    {
        string strConn = "";

OleDbConnection conn = null;

        if (String.IsNullOrEmpty(excelPath))
        {
            return null;
        }

        if (!System.IO.File.Exists(excelPath))
        {
            return null;
        }

        try
        {
            if (excelPath.EndsWith(".xls"))
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;";
            }
            if (excelPath.EndsWith(".xlsx"))
            {
                strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";

            }
            if (strConn.Equals(""))
            {
                throw new Exception("無效的EXCEL文件！");
            }
            conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [" + exceltableName + "$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "TEMP");
            conn.Close();
            return ds.Tables[0];
        }
        catch
        {
            conn.Close();
            throw new Exception("解析Excel出錯。可能原因有：\\n\\n1：選擇文件類型非Excel格式文件，正確格式為：XXXX.xls！\\n\\n2：該文件被其他進程佔用！\\n\\n3：该文件不存在！");
        }
    }

    /// <summary>
    /// 匯出Excel
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    private bool doExport(DataTable dt)
    {
        bool exportok = false;

//創建文件
string fn = string.Empty;

fn =DateTime.Now.ToString("yyyyMMdd")+"_銷退拆單.xls";

string SaveLocation = Server.MapPath("~/tempFolder") + "\\" + fn;

        if (System.IO.File.Exists(SaveLocation))
        {
            try
            {
                System.IO.File.Delete(SaveLocation);
            }
            catch (Exception ex)
            {
                writeLog(ex);
                throw ex;
            }

}

#region 產生Excel檔

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SaveLocation + ";Extended Properties=Excel 8.0;";
        OleDbConnection Conn = new OleDbConnection(strConn);
        try
        {
            Conn.Open();
        }
        catch (Exception ex)
        {
            writeLog(ex);
            throw ex;
        }
        OleDbCommand odbcmd = null;
        StringBuilder sb = new StringBuilder();

        try
        {
            //創建表
            sb.Append("CREATE TABLE RECEIPT(銷退單號 NVARCHAR, 原始單 NVARCHAR, 拆單 NVARCHAR, 料號 NVARCHAR, 客代 NVARCHAR, 通知量 NVARCHAR, ");
            sb.Append("銷退量 NVARCHAR, 不允收量 NVARCHAR, 拆封量 NVARCHAR) ");
            odbcmd = new OleDbCommand(sb.ToString(), Conn);
            odbcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            writeLog(ex);
            MessageBox(ex.Message);
        }
        finally
        {
            //Conn.Close();
        }
        #endregion

#region 將資料新增至Excel檔

        OleDbCommand olecmd = null;
        try
        {
            sb.Length = 0;
            sb.Append("Insert into RECEIPT (銷退單號,原始單,拆單,料號,客代,通知量,");
            sb.Append("銷退量,不允收量,拆封量) ");
            sb.Append("values (@RECEIPTKEY,@EXTERNRECEIPTKEY, @CHAIDAN, @SKU, @STORERKEYCUSTID, @INFORMEDQTY,");
            sb.Append("@SALERETQTY, @UNPPQTY, @UNPACKQTY)");
            olecmd = new OleDbCommand(sb.ToString(), Conn);
            olecmd.Parameters.Add("@RECEIPTKEY", OleDbType.VarChar);
            olecmd.Parameters["@RECEIPTKEY"].SourceColumn = "RECEIPTKEY";

olecmd.Parameters.Add("@EXTERNRECEIPTKEY", OleDbType.VarChar);
olecmd.Parameters["@EXTERNRECEIPTKEY"].SourceColumn = "EXTERNRECEIPTKEY";

olecmd.Parameters.Add("@CHAIDAN", OleDbType.VarChar);
olecmd.Parameters["@CHAIDAN"].SourceColumn = "CHAIDAN";

olecmd.Parameters.Add("@SKU", OleDbType.VarChar);
olecmd.Parameters["@SKU"].SourceColumn = "SKU";

olecmd.Parameters.Add("@STORERKEYCUSTID", OleDbType.VarChar);
olecmd.Parameters["@STORERKEYCUSTID"].SourceColumn = "STORERKEYCUSTID";

olecmd.Parameters.Add("@INFORMEDQTY", OleDbType.VarChar);
olecmd.Parameters["@INFORMEDQTY"].SourceColumn = "INFORMEDQTY";

olecmd.Parameters.Add("@SALERETQTY", OleDbType.VarChar);
olecmd.Parameters["@SALERETQTY"].SourceColumn = "SALERETQTY";

olecmd.Parameters.Add("@UNPPQTY", OleDbType.VarChar);
olecmd.Parameters["@UNPPQTY"].SourceColumn = "UNPPQTY";

olecmd.Parameters.Add("@UNPACKQTY", OleDbType.VarChar);
olecmd.Parameters["@UNPACKQTY"].SourceColumn = "UNPACKQTY";

            if (doUpdate(olecmd, dt))
            {
                exportok = true;
            }
            else
            {
                exportok = false;
                throw new Exception("Export Failed!");
            }
            Conn.Close();

        }
        catch (Exception ex)
        {
            Conn.Close();
            writeLog(ex);
            MessageBox(ex.Message);
        }

return exportok;

#endregion
}

    /// <summary>
    /// 匯出Excel
    /// </summary>
    /// <param name="olecmd"></param>
    /// <param name="dt"></param>
    /// <returns></returns>
    protected bool doUpdate(OleDbCommand olecmd, DataTable dt)
    {
        bool updateOK = false;
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.InsertCommand = olecmd;
        try
        {
            da.Update(dt);
            updateOK = true;
        }
        catch (Exception err)
        {
            writeLog(err);
            updateOK = false;
        }
        return updateOK;
    }