目的: 如何匯入檔案 且 傳回匯入結果
處理說明 : 1>如何匯入檔案
2>如何傳回匯入結果
2>如何傳回匯入結果
0>[挑選檔案]元件 & 匯入子畫面
//[匯入]子畫面
var J_formFields_Sub_In = [
{
xtype: 'filefield',
id: 'sub_FName',
name: 'sub_FName',
fieldLabel: '檔案名稱',
labelWidth: 60,
msgTarget: 'side',
allowBlank: false,
flex: 1,
anchor: '100%',
buttonText: '選擇檔案',
listeners: {
'change': function (fb, v) {
var fileName = getFileName(v.replace(/C:\\fakepath\\/g, ''));
Ext.getDom(fb.inputId).value = fileName;
//Ext.getDom(fb.inputId).value = v;
}
}
},
];
//[匯入]按鈕處理
function xlsInBtn_click() {
console.log(" 2 inside xlsInBtn_click() ");
var mySubForm = Ext.create('Ext.form.FormPanel', {
autoScroll: true,
fieldDefaults: {
labelAlign: 'right',
},
layout: {
type: 'vbox',
align: 'stretch'
},
id: "mySubForm_2",
form_id: "mySubForm_2",
items: J_formFields_Sub_In,
buttons: [
{
text: "確定匯入", id: "mySubForm_2_btnok", handler: function () {
mySubForm_2_btnokClick();
//this.up("window").close();
//this.up("window").destroy();
//Ext.getCmp('btn_Show').fireHandler();
}
},
{
text: "取消", id: "mySubForm_2_btncancel", handler: function () {
var Tmp_win = this.up("window");
if (Tmp_win == undefined) {
Ext.Msg.alert("關閉時未取到Window Object");
}
this.up("window").close();
this.up("window").destroy();
}
}
]
}
);
var win = getEditWindow_my("匯入", mySubForm, 1);
win.width = 600;
win.height = 100;
win.show();
};
1>匯入檔案: *.js
//[確認匯入]鈕 , 處理
function mySubForm_2_btnokClick() {
//取得 [檔案上傳].檔名
var btn_FileIn_Obj = Ext.getCmp('sub_FName');
var Tmp_Str = "";
var np = {
FName: Ext.getDom(btn_FileIn_Obj.inputId).value
};
console.log("FName: ", np.FName);
//submit會將fileUploadForm裡面input name送到後端
Ext.getCmp('mySubForm_2_btnok').up("form").submit({
//standardSubmit: false, //default:false
url: '../api/V120103API/uploadFileToDB',
method: 'POST',
async: false,
headers: { 'Content-type': 'multipart/form-data' },
params: np,
//[確認匯入]鈕 , 處理
function mySubForm_2_btnokClick() {
//取得 [檔案上傳].檔名
var btn_FileIn_Obj = Ext.getCmp('sub_FName');
var Tmp_Str = "";
var np = {
FName: Ext.getDom(btn_FileIn_Obj.inputId).value
};
console.log("FName: ", np.FName);
//submit會將fileUploadForm裡面input name送到後端
Ext.getCmp('mySubForm_2_btnok').up("form").submit({
//standardSubmit: false, //default:false
url: '../api/V120103API/uploadFileToDB',
method: 'POST',
async: false,
headers: { 'Content-type': 'multipart/form-data' },
params: np,
})
2>*.cs
//[匯入] 鈕 , 匯入 XLS 檔案
[HttpPost]
//public HttpResponseMessage uploadFileToDB()
public void uploadFileToDB()
{
HttpContext c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//匯入檔案名稱(含 *.xlsx)
string FName = nvc["FName"];
string FName0 =Path.GetFileNameWithoutExtension(FName) ; //不含 extension
string Tmp_FName = FName;
Workbook wk = null;
HttpRequest Request = HttpContext.Current.Request;
var response = this.Request.CreateResponse();
string file_Name = FName;
string FILEDT = DateTime.Now.ToString("yyyy-MM-dd");
string Tmp_RtnMsg = "";
string Tmp_Rtn_JSON = ""; //Tmp_Rtn_JSON = "{success: false,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) + "}";
string Tmp_Sql = "", Tmp_Str = "";
int Tmp_CNT = 0;
MemoryStream stream = new MemoryStream();
HttpCookie MyCookie = new HttpCookie("Rtn_Msg");
foreach (string cur_FName in Request.Files)
{
string fileType = Request.Files[cur_FName].ContentType;
Stream file_Strm = Request.Files[cur_FName].InputStream;
file_Name = Path.GetFileName(Request.Files[cur_FName].FileName);
int fileSize = Request.Files[cur_FName].ContentLength;
byte[] fileRcrd = new byte[fileSize];
//更新資料庫欄位值
int Tmp_curpos = 0;
bool Tmp_isOK = true;
try
{
wk = new Workbook();
wk.LoadFromStream(file_Strm);
Worksheet sheet1 = wk.Worksheets[0];//獲取第一個工作表
//共 10 欄
//string[] outFieldArray = { "計畫代碼", "父件件號", "件號", "件號中文名稱", "每機用量", "BOM量", "計量單位", "工廠", "類別","備註" };
//int RowsCount = sheet1.Rows.Count();
int RowsCount = sheet1.LastRow;
string Tmp_CLS,Tmp_ITM, Tmp_DITM, Tmp_NM, Tmp_REPN, Tmp_RESN, Tmp_INPN, Tmp_INSN, Tmp_RMK;
string Tmp_PLNC, Tmp_REWK, Tmp_INSC, Tmp_STLCT, Tmp_NOTE, Tmp_FACWC;
string Tmp_RESULT;
//STEP1 檢核 XLS 欄位名稱資料是否正確
//類別 排序項次 名稱 拆移件號 拆移序號 安裝件號 安裝序號 備考
// 拆移PlanningCard 修理REWORK 安裝InstallationCard 儲位 備註 拆移工廠
Tmp_CLS = sheet1.Range[myfunc.GetExcelPos(0, 0)].Value;
Tmp_DITM = sheet1.Range[myfunc.GetExcelPos(1, 0)].Value;
Tmp_NM = sheet1.Range[myfunc.GetExcelPos(2, 0)].Value;
Tmp_REPN = sheet1.Range[myfunc.GetExcelPos(3, 0)].Value;
Tmp_RESN = sheet1.Range[myfunc.GetExcelPos(4, 0)].Value;
Tmp_INPN = sheet1.Range[myfunc.GetExcelPos(5, 0)].Value;
Tmp_INSN = sheet1.Range[myfunc.GetExcelPos(6, 0)].Value;
Tmp_RMK = sheet1.Range[myfunc.GetExcelPos(7, 0)].Value;
// string Tmp_PLNC, Tmp_REWK, Tmp_INSC, Tmp_STLCT, Tmp_NOTE, Tmp_FACWC;
Tmp_PLNC = sheet1.Range[myfunc.GetExcelPos(8, 0)].Value;
Tmp_REWK = sheet1.Range[myfunc.GetExcelPos(9, 0)].Value;
Tmp_INSC = sheet1.Range[myfunc.GetExcelPos(10, 0)].Value;
Tmp_STLCT = sheet1.Range[myfunc.GetExcelPos(11, 0)].Value;
Tmp_NOTE = sheet1.Range[myfunc.GetExcelPos(12, 0)].Value;
Tmp_FACWC = sheet1.Range[myfunc.GetExcelPos(13, 0)].Value;
//新增[結果說明]欄位
Tmp_RESULT = "結果說明";
sheet1.Range[myfunc.GetExcelPos(14, 0)].Value=Tmp_RESULT;
//xls 欄位名稱有問題
//類別 排序項次 名稱 拆移件號 拆移序號 安裝件號 安裝序號 備考
// 拆移PlanningCard 修理REWORK 安裝InstallationCard 儲位 備註 拆移工廠
Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>";
if ((Tmp_CLS != "類別") || (Tmp_DITM != "排序項次") || (Tmp_NM != "名稱") || (Tmp_REPN != "拆移件號") || (Tmp_RESN != "拆移序號") ||
(Tmp_INPN != "安裝件號") || (Tmp_INSN != "安裝序號") || (!Tmp_RMK.Contains("備考")) ||
(Tmp_PLNC != "拆移PlanningCard") || (Tmp_REWK != "修理REWORK") || (Tmp_INSC != "安裝InstallationCard") ||
(Tmp_STLCT != "儲位") || (Tmp_NOTE != "備註") || (Tmp_FACWC != "拆移工場")
)
{
Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>"
+"'類別','排序項次','名稱','拆移件號','拆移序號',<br>"
+"'安裝件號','安裝序號','備考','拆移PlanningCard','修理REWORK',<br>"
+"'安裝InstallationCard','儲位','備註','拆移工場'<br>"
+"敬請檢核";
//Tmp_Rtn_JSON = "{success: false,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) + "}";
//response.Content = new StringContent(Tmp_Str); // 回應內容
//return response;
//Tmp_Str = "已匯出完成!! (" + Tmp_Cnt.ToString() + "筆) <br>"
// + "檔案名稱(" + Tmp_FName + ")";
// 傳回結果字串
MyCookie.Value= HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
return ;
};
int Tmp_index;
Tmp_isOK = true;
//STEP2 檢核資料
for (int i = 0; i < RowsCount; i++)
{
Tmp_curpos = i + 1;
//if (Tmp_curpos == 290)
//{
// Tmp_Str = " 第 290筆資料";
//};
Tmp_RESULT = "";
Tmp_CLS = sheet1.Range[myfunc.GetExcelPos(0, i + 1)].Value;
if (myfunc.checkisnull(Tmp_CLS))
{ break; }
Tmp_DITM = sheet1.Range[myfunc.GetExcelPos(1, i + 1)].Value;
Tmp_REPN = sheet1.Range[myfunc.GetExcelPos(3, i + 1)].Value;
Tmp_RMK = sheet1.Range[myfunc.GetExcelPos(7, i + 1)].Value;
if (Tmp_DITM.Length == 0)
{ Tmp_RESULT = Tmp_RESULT + "排序項次不可空白,"; Tmp_isOK = false; }
if (Tmp_REPN.Length == 0)
{ Tmp_RESULT = Tmp_RESULT + "拆移件號不可空白,"; Tmp_isOK = false; }
if ((Tmp_RMK!= "A") && (Tmp_RMK != "B") && (Tmp_RMK != "C") && (Tmp_RMK != "D") && (Tmp_RMK != "E") && (Tmp_RMK != "F"))
{ Tmp_RESULT = Tmp_RESULT + "備考欄位值必需在 A~F 之間"; Tmp_isOK = false; }
if (Tmp_RESULT.Length > 0)
sheet1.Range[myfunc.GetExcelPos(14, i + 1)].Value = Tmp_RESULT;
else
sheet1.Range[myfunc.GetExcelPos(14, i + 1)].Value = "check ok";
} // for i=0.. 檢查資料
//若資料不正確,則不寫入資料庫,只傳回檔案
if (Tmp_isOK == false)
{
Tmp_Str = "匯入檔案欄位值需修正!!<br>"
+ "請檢核如下檔案名稱(" + Tmp_FName + ")<br>";
MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
// 若匯入資料有問題, 則不寫入資料庫 , 傳回結果檔案(*_結果.xlsx)
Tmp_FName = FName0 + "_結果.xlsx";
wk.SaveToStream(stream, FileFormat.Version2007);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
return;
}
//STEP3 寫入資料庫
for (int i = 0; i < RowsCount; i++)
{
Tmp_curpos = i + 1;
//if (Tmp_curpos == 290)
//{
// Tmp_Str = " 第 290筆資料";
//};
Tmp_CLS = sheet1.Range[myfunc.GetExcelPos(0, i + 1)].Value;
if (myfunc.checkisnull(Tmp_CLS))
{ break; }
Tmp_DITM = sheet1.Range[myfunc.GetExcelPos(1, i + 1)].Value;
Tmp_NM = sheet1.Range[myfunc.GetExcelPos(2, i + 1)].Value;
Tmp_REPN = sheet1.Range[myfunc.GetExcelPos(3, i + 1)].Value;
Tmp_RESN = sheet1.Range[myfunc.GetExcelPos(4, i + 1)].Value;
Tmp_INPN = sheet1.Range[myfunc.GetExcelPos(5, i + 1)].Value;
Tmp_INSN = sheet1.Range[myfunc.GetExcelPos(6, i + 1)].Value;
//"'類別','排序項次','名稱','拆移件號','拆移序號','安裝件號','安裝序號','備考','拆移PlanningCard','修理REWORK','安裝InstallationCard','儲位','備註','拆移工廠'<br>"
Tmp_RMK = sheet1.Range[myfunc.GetExcelPos(7, i + 1)].Value;
Tmp_PLNC = sheet1.Range[myfunc.GetExcelPos(8, i + 1)].Value;
Tmp_REWK = sheet1.Range[myfunc.GetExcelPos(9, i+1)].Value;
Tmp_INSC = sheet1.Range[myfunc.GetExcelPos(10, i + 1)].Value;
Tmp_STLCT = sheet1.Range[myfunc.GetExcelPos(11, i + 1)].Value;
Tmp_NOTE = sheet1.Range[myfunc.GetExcelPos(12, i + 1)].Value;
Tmp_FACWC = sheet1.Range[myfunc.GetExcelPos(13, i + 1)].Value;
Tmp_Sql = " SELECT COUNT(*) "
+" FROM AMM_FRO3F16STD "
+ " WHERE CLS=" + myfunc.AA(Tmp_CLS)
+" AND DITM=" + myfunc.AA(Tmp_DITM);
Tmp_CNT=int.Parse(myfunc.SqlValue(Tmp_Sql).ToString());
if (Tmp_CNT==0)
{
Tmp_ITM = GET_MAX_ITM(Tmp_CLS).ToString();
Tmp_Sql = " INSERT INTO AMM_FRO3F16STD "
+ " (CLS,ITM,DITM,NM,REPN,RESN,INPN,INSN,RMK,PLNC,REWK,INSC,STLCT,NOTE,FACWC) "
+ " VALUES "
+ "("
+ myfunc.AA(Tmp_CLS) + ","
+ myfunc.AA(Tmp_ITM) + ","
+ myfunc.AA(Tmp_DITM) + ","
+ myfunc.AA(Tmp_NM) + ","
+ myfunc.AA(Tmp_REPN) + ","
+ myfunc.AA(Tmp_RESN) + ","
+ myfunc.AA(Tmp_INPN) + ","
+ myfunc.AA(Tmp_INSN) + ","
+ myfunc.AA(Tmp_RMK) + ","
+ myfunc.AA(Tmp_PLNC) + ","
+ myfunc.AA(Tmp_REWK) + ","
+ myfunc.AA(Tmp_INSC) + ","
+ myfunc.AA(Tmp_STLCT) + ","
+ myfunc.AA(Tmp_NOTE) + ","
+ myfunc.AA(Tmp_FACWC)
+ ") ";
myfunc.SqlExec(Tmp_Sql);
sheet1.Range[myfunc.GetExcelPos(14, i + 1)].Value = "新增 OK!!";
}
else
{
Tmp_Sql = " UPDATE AMM_FRO3F16STD "
+ " SET NM="+ myfunc.AA(Tmp_NM) + ","
+ " REPN=" + myfunc.AA(Tmp_REPN) + ","
+ " RESN=" + myfunc.AA(Tmp_RESN) + ","
+ " INPN=" + myfunc.AA(Tmp_INPN) + ","
+ " INSN=" + myfunc.AA(Tmp_INSN) + ","
+ " RMK=" + myfunc.AA(Tmp_RMK) + ","
+ " PLNC=" + myfunc.AA(Tmp_PLNC) + ","
+ " REWK=" + myfunc.AA(Tmp_REWK) + ","
+ " INSC=" + myfunc.AA(Tmp_INSC) + ","
+ " STLCT=" + myfunc.AA(Tmp_STLCT) + ","
+ " NOTE=" + myfunc.AA(Tmp_NOTE) + ","
+ " FACWC=" + myfunc.AA(Tmp_FACWC)
+ " WHERE CLS=" + myfunc.AA(Tmp_CLS)
+ " AND DITM=" + myfunc.AA(Tmp_DITM);
myfunc.SqlExec(Tmp_Sql);
sheet1.Range[myfunc.GetExcelPos(14, i + 1)].Value = "更新 OK!!";
}
}; //end of for i= , 寫入資料庫
Tmp_Str = "匯入成功!!";
MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
Tmp_FName = FName0 + "_結果.xlsx";
wk.SaveToStream(stream, FileFormat.Version2007);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
//Tmp_Str = "{success: true,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) + "}";
//response.Content = new StringContent(Tmp_Str); // 回應內容
//return response;
}// try
catch (Exception e)
{ //失敗
Tmp_Str = "批次匯入失敗!!<br>"
+ "第" + Tmp_curpos.ToString() + "筆資料<br>"
+ e.Message + "<br>"
+Tmp_Sql+"<br>";
MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
//+ Tmp_Sql ;
//Tmp_Str = "{success: false,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) +",Tmp_Sql:"+ myfunc.AA(Tmp_Sql) +"}";
//Tmp_Str = "{success: false,FName:" + myfunc.AA(FName) + ",Rtn_Msg:" + myfunc.AA(Tmp_RtnMsg) + "}";
//response.Content = new StringContent(Tmp_Str); // 回應內容
//return response;
}
} //end of foreach (string cur_FName in Request.Files)
return;
} // end of uploadFileToDB()
沒有留言:
張貼留言