目的: V80202M – [Finding處置匯入]鈕 – 匯入資料,並回傳處理結果檔
處理說明: 1>匯入檔案 :
foreach (string cur_FName in Request.Files)
foreach (string cur_FName in Request.Files)
{
Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;
Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;
}
wk = new Workbook();
wk.LoadFromStream(Tmp_in_Stream);
2>回傳結果檔案:
wk.SaveToStream(stream, FileFormat.Version2007);
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
1>*.cs
[HttpPost]
public void UPDATE_QDRWKTP()
{
HttpContext c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//OracleConnection conn = new OracleConnection(DBService.ConnectionString(("AMMEU"));//
OracleConnection conn = new OracleConnection(DBService.ConnectionString((DBLINK)));//
OracleCommand cmd = new OracleCommand();
OracleDataReader reader;
DataSet ds = null;
//取得匯入檔案名稱 of filefield
string Tmp_FName = nvc["sub_FName"];
string Tmp_in_FName="", Tmp_out_FName="";
Workbook wk = null;
MemoryStream stream = new MemoryStream();
Worksheet ws=null;
HttpRequest Request = HttpContext.Current.Request;
var response = this.Request.CreateResponse();
//string file_Name = Tmp_in_FName;
string FILEDT = DateTime.Now.ToString("yyyy/MM/dd");
string Tmp_RtnMsg = "";
string Tmp_RtnMsg1 = "";
string Tmp_Sql = "", Tmp_Str = "";
int Tmp_cnt = 0;
int Tmp_rowcnt = 0;
HttpCookie MyCookie = new HttpCookie("Rtn_Msg");
//HttpCookie MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));
conn.Open();
cmd.Connection = conn;
try
{
foreach (string cur_FName in Request.Files)
{
string fileType = Request.Files[cur_FName].ContentType;
Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;
Tmp_in_FName = Path.GetFileName(Request.Files[cur_FName].FileName);
Tmp_out_FName = "Log_" + Tmp_in_FName;
int fileSize = Request.Files[cur_FName].ContentLength;
byte[] fileRcrd = new byte[fileSize];
//更新資料庫欄位值
//匯入的 xls
wk = new Workbook();
wk.LoadFromStream(Tmp_in_Stream);
ws = wk.Worksheets[0];
string Tmp_SAPNO, Tmp_QDRWKTP;
//STEP1 檢核 XLS 欄位名稱資料是否正確 -取得 標題 of 工號,處置說明
Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(0, 0)].Value;
Tmp_QDRWKTP = ws.Range[myfunc.GetExcelPos(1, 0)].Value;
//xls 欄位名稱有問題
if ((Tmp_SAPNO != "工號") || (Tmp_QDRWKTP != "處置說明"))
{
Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>";
//(Tmp_DTSOR != "資料來源") || (Tmp_OGONOAF != "拆檢提領編號") || (Tmp_NGONOAF != "維修提領編號") || (Tmp_RPNOAF != "拆檢交修單號") || (Tmp_MRPNOAF != "維修約交修單號") ||
if (Tmp_SAPNO != "工號")
Tmp_RtnMsg = Tmp_RtnMsg + "第1欄欄位名稱: [工號];";
if (Tmp_QDRWKTP != "處置說明")
Tmp_RtnMsg = Tmp_RtnMsg + "第2欄欄位名稱: [處置說明];";
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
return;
};
ws.Range[myfunc.GetExcelPos(2, 0)].Value = "匯入結果說明";
//STEP2 取得xls.[目前件號].報工資料
// Transaction SQL List - 存放 Transaction 的 SQL
List<string> SQL_List_A = new List<string>();
int RowsCount = ws.LastRow;
//int Tmp_cnt;
for (int i = 0; i < RowsCount; i++)
{
Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(0, i + 1)].Value;
Tmp_QDRWKTP = ws.Range[myfunc.GetExcelPos(1, i + 1)].Value;
if (myfunc.checkisnull(Tmp_SAPNO))
{ break; }
Tmp_Sql=" SELECT COUNT(*) "
+" FROM AMM_SRO "
+" WHERE SAPNO=" + myfunc.AA(Tmp_SAPNO.Trim());
Tmp_cnt = int.Parse(myfunc.SqlValue(Tmp_Sql));
if (Tmp_cnt == 0)
{
ws.Range[myfunc.GetExcelPos(2, i + 1)].Value = "工號不存在AMM系統";
}
else
{
Tmp_Sql = " UPDATE AMM_SRO "
+ " SET QDRWKTP=" + myfunc.AA(Tmp_QDRWKTP.Trim())
+ " WHERE SAPNO=" + myfunc.AA(Tmp_SAPNO.Trim());
//SQL_List_A.Add(Tmp_Sql);
myfunc.SqlExec(Tmp_Sql);
ws.Range[myfunc.GetExcelPos(2, i + 1)].Value = "更新完成";
}
Tmp_rowcnt = Tmp_rowcnt + 1;
} // end of for (int i = 0; i < RowsCount; i++)
//excuteSQLTran(SQL_List_A);
} //end of foreach (string cur_FName in Request.Files)
ws.AllocatedRange.AutoFitColumns();
wk.SaveToStream(stream, FileFormat.Version2007);
Tmp_Str = "Finding處置完成!!(" + Tmp_rowcnt.ToString() + "筆)<br>"
+ "匯入檔案名稱(" + Tmp_FName + ")";
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));
//MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_out_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.End();
} // end fo try{
catch (Exception e)
{
//Console.WriteLine(e);
Tmp_RtnMsg = "錯誤訊息如下:"
+ e.Message;
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
//throw;
}
finally
{
wk.Dispose();
conn.Close();
}
//return response;
//Tmp_Cnt = 6;
} // end of UPDATE_QDRWKTP()
沒有留言:
張貼留言