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