目的: V20302 –[匯出EXCEL]鈕
處理說明: 一.前端(*.js)
1>將目前所選的資料s, 轉成 JSON字串, 當成 np 參數, 傳至後端
2>若要傳送檔案至前端, 1>>必需用 form.submit , 不可用 Ext.Ajax
2>>standardSubmit: true,
2>若要傳送檔案至前端, 1>>必需用 form.submit , 不可用 Ext.Ajax
2>>standardSubmit: true,
二.後端(*.cs)
1>將資料參數,轉成 Table
string Tmp_DataString1 = nvc["DataString1"]; //Tmp_DataString: 子畫面. JSON字串
DataTable dt1 = myfunc.JSON2dt(Tmp_DataString1);
2>讀取 SS_FILES 的 Template檔案 至 ws
3> ws 寫資料 , 存至檔案
4>將檔案傳送至前端 
//查詢 - 將目前所選的資料,匯出成報表
function XlsOutBtn_click() {
var Tmp_Grid = Ext.getCmp('grid_Single');
var cur_recs, cur_rec;
var Tmp_SAPNO, Tmp_PN, Tmp_QTY, Tmp_EOCND, Tmp_RMK;
var Tmp_data_JSON = ""; //將store資料轉成 JSON 的字串
var Tmp_sub_np = {};
let Tmp_DataAry1 = [];
var Tmp_Str = "";
cur_recs = Tmp_Grid.getView().selModel.getSelection();
//console.log("cur_recs:", cur_recs);
if (cur_recs.length == 0) {
mywarnalert("請先選擇要匯出 EXCEL 的資料");
return;
}
for (var i = 0; i < cur_recs.length; i++) {
cur_rec = cur_recs[i];
//console.log(Tmp_Str + "cur_rec:", cur_rec);
Tmp_DataAry1.push(cur_rec.data);
}
//console.log("Tmp_DataAry1:", Tmp_DataAry1);
// 將目前子畫面的資料(Table)轉成 JSON字串
let Tmp_DataString1 = JSON.stringify(Tmp_DataAry1);
//console.log("Tmp_DataString1:", Tmp_DataString1);
var np = {};
np = {
DataString1: Tmp_DataString1,
};
//Ext.Ajax.request({
1>*.js
{
xtype: 'button', text: '匯出EXCEL', id: 'XlsOutBtn',
listeners: {
click: function () {
//console.log(" 1 匯出EXCEL: ");
XlsOutBtn_click();
}
}
},
{
xtype: 'button', text: '匯出EXCEL', id: 'XlsOutBtn',
listeners: {
click: function () {
//console.log(" 1 匯出EXCEL: ");
XlsOutBtn_click();
}
}
},
//查詢 - 將目前所選的資料,匯出成報表
function XlsOutBtn_click() {
var Tmp_Grid = Ext.getCmp('grid_Single');
var cur_recs, cur_rec;
var Tmp_SAPNO, Tmp_PN, Tmp_QTY, Tmp_EOCND, Tmp_RMK;
var Tmp_data_JSON = ""; //將store資料轉成 JSON 的字串
var Tmp_sub_np = {};
let Tmp_DataAry1 = [];
var Tmp_Str = "";
cur_recs = Tmp_Grid.getView().selModel.getSelection();
//console.log("cur_recs:", cur_recs);
if (cur_recs.length == 0) {
mywarnalert("請先選擇要匯出 EXCEL 的資料");
return;
}
for (var i = 0; i < cur_recs.length; i++) {
cur_rec = cur_recs[i];
//console.log(Tmp_Str + "cur_rec:", cur_rec);
Tmp_DataAry1.push(cur_rec.data);
}
//console.log("Tmp_DataAry1:", Tmp_DataAry1);
// 將目前子畫面的資料(Table)轉成 JSON字串
let Tmp_DataString1 = JSON.stringify(Tmp_DataAry1);
//console.log("Tmp_DataString1:", Tmp_DataString1);
var np = {};
np = {
DataString1: Tmp_DataString1,
};
//Ext.Ajax.request({
Ext.getCmp('s_form').submit({
url: '../../api/V20302API/XlsOut',
method: 'POST',
async: false,
standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
});
//顯示結果訊息..
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '處理中,請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
url: '../../api/V20302API/XlsOut',
method: 'POST',
async: false,
standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
});
//顯示結果訊息..
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '處理中,請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
//若要傳送檔案到前端, 必需用 Cookie 判斷後端是否已完成
//每1秒檢核一次,是否已完成, 若已完成,則不再檢核
var timer = setInterval(function () {
var r = r_cookies('EX_DFile');
//console.log("r_cookies=", r);
if (!checkisnull(r)) {
mysuccessalert(r);
clearInterval(timer);
mask.hide();
timer = null;
}
}, 1000); //1000ms = 1sec
} // end of function LookBtn_click() {
2>*.cs
//查詢 - 將所選的資料匯出至 xls - 套表
[HttpPost]
public void XlsOut()
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
// 1>將 SS_FILES.FBOLD 存成 Local檔案
//取得目前子畫面的欄位值
string Tmp_DataString1 = nvc["DataString1"]; //Tmp_DataString: 子畫面的資料. JSON字串
//將 table 字串轉成 DataTable
//DataTable DataTable1 = (DataTable)JsonConvert.DeserializeObject(Tmp_DataString1, (typeof(DataTable)));
DataTable dt1 = myfunc.JSON2dt(Tmp_DataString1);
OracleConnection conn = new OracleConnection(DBService.ConnectionString(DBLINK));//
OracleCommand cmd = new OracleCommand();
//OracleDataAdapter adp = new OracleDataAdapter();
//DataSet ds = new DataSet();
//var response = Request.CreateResponse();
conn.Open();
conn.ClientInfo = User.Identity.Name;
conn.ModuleName = BaseSYS + "_" + BaseMODID;
conn.ActionName = ActionName;
cmd.Connection = conn;
string Tmp_Sql = "";
string Tmp_Str = "";
string Tmp_Str1 = "";
string Tmp_RtnMsg;
string Tmp_FName = "V20302_匯出報表.xlsx"; //套表的檔案名稱 ,
string documentPath = HttpContext.Current.Server.MapPath("~") + "document\\"; // 取得實實的路徑
string pathFName = documentPath + Tmp_FName;
//需要擷取大量資料時，DataReader 是很好的選擇，因為資料不會快取至記憶體。
OracleDataReader reader;
Workbook wk = null;
MemoryStream mstream = new MemoryStream();
string FileName = "V20302_匯出報表_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
string FileName1 = documentPath + FileName;
HttpCookie MyCookie;
try
{
wk = new Workbook();
//1>將 SS_FILES. FBODY , 存至Local目錄.檔案
if (File.Exists(pathFName))
{
File.Delete(pathFName);
}
Tmp_Sql = " SELECT FNAME,FBODY "
+ " FROM SS_FILES "
+ " WHERE 1 = 1 "
+ " AND FNAME = " + myfunc.AA(Tmp_FName);
cmd.CommandText = Tmp_Sql;
reader = cmd.ExecuteReader();
byte[] file = null;
//FileMode.OpenOrCreate: 開啟檔案若無檔案則新增
//FileAccess.ReadWrite: 允許檔案讀取/寫入
FileStream fs = File.Open(pathFName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs);
if (reader.HasRows)
{
if (reader.Read())
{
file = (byte[])reader["FBODY"];
//fs.Write(file,0,50000);
bw.Write(file);
wk.LoadFromStream(fs);
bw.Close();
fs.Close();
}
}
//2 > 主機.Local檔案 XLS 讀入 FStream
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
//寫入欄位
string Tmp_AMMNO, Tmp_RPNOAF, Tmp_MRPNOAF,Tmp_SAPNO_;
string Tmp_TYPEAF, Tmp_PN_, Tmp_C_NM, Tmp_SEQOAF, Tmp_MB1A;
string Tmp_RBTHAF, Tmp_OGONOAF, Tmp_NGONOAF, Tmp_WBSNO;
//系統件單號, 資料來源, 數量 料號 機種 承修工場 工單類型 單況 單況說明
string Tmp_FMNO, Tmp_DTSOR, Tmp_RQTYAF, Tmp_NSN, Tmp_EIAC_CUS;
string Tmp_FACWC, Tmp_FRTP, Tmp_STATFM, Tmp_STATFM_;
//每筆資料列印後, 不分頁列印下一筆
for (var i = 0; i < dt1.Rows.Count; i++)
{
Tmp_AMMNO = dt1.Rows[i]["AMMNO"].ToString();
Tmp_RPNOAF = dt1.Rows[i]["RPNOAF"].ToString();
Tmp_MRPNOAF = dt1.Rows[i]["MRPNOAF"].ToString();
Tmp_SAPNO_ = dt1.Rows[i]["SAPNO_"].ToString();
//string Tmp_TYPEAF, Tmp_PN_, Tmp_C_NM, Tmp_SEQOAF, Tmp_MB1A;
Tmp_TYPEAF = dt1.Rows[i]["TYPEAF"].ToString();
Tmp_PN_ = dt1.Rows[i]["PN_"].ToString();
Tmp_C_NM = dt1.Rows[i]["C_NM"].ToString();
Tmp_SEQOAF = dt1.Rows[i]["SEQOAF"].ToString();
Tmp_MB1A = dt1.Rows[i]["MB1A"].ToString();
//string Tmp_RBTHAF, Tmp_OGONOAF, Tmp_NGONOAF, Tmp_WBSNO;
Tmp_RBTHAF = dt1.Rows[i]["RBTHAF"].ToString();
Tmp_OGONOAF = dt1.Rows[i]["OGONOAF"].ToString();
Tmp_NGONOAF = dt1.Rows[i]["NGONOAF"].ToString();
Tmp_WBSNO = dt1.Rows[i]["WBSNO"].ToString();
//string Tmp_FMNO, Tmp_DTSOR, Tmp_RQTYAF, Tmp_NSN, Tmp_EIAC_CUS;
Tmp_FMNO = dt1.Rows[i]["FMNO"].ToString();
Tmp_DTSOR = dt1.Rows[i]["DTSOR"].ToString();
Tmp_RQTYAF = dt1.Rows[i]["RQTYAF"].ToString();
Tmp_NSN = dt1.Rows[i]["NSN"].ToString();
Tmp_EIAC_CUS = dt1.Rows[i]["EIAC_CUS"].ToString();
//string Tmp_FACWC, Tmp_FRTP, Tmp_STATFM, Tmp_STATFM_;
Tmp_FACWC = dt1.Rows[i]["FACWC"].ToString();
Tmp_FRTP = dt1.Rows[i]["FRTP"].ToString();
Tmp_STATFM = dt1.Rows[i]["STATFM"].ToString();
Tmp_STATFM_ = dt1.Rows[i]["STATFM_"].ToString();
//GetExcelPos(欄, 行) 以 0 開始,
//string Tmp_AMMNO, Tmp_RPNOAF, Tmp_MRPNOAF, Tmp_SAPNO_;
ws.Range[myfunc.GetExcelPos(0, i + 1)].Text = Tmp_AMMNO;
ws.Range[myfunc.GetExcelPos(1, i + 1)].Text = Tmp_RPNOAF;
ws.Range[myfunc.GetExcelPos(2, i + 1)].Text = Tmp_MRPNOAF;
ws.Range[myfunc.GetExcelPos(3, i + 1)].Text = Tmp_SAPNO_;
//string Tmp_TYPEAF, Tmp_PN_, Tmp_C_NM, Tmp_SEQOAF, Tmp_MB1A;
ws.Range[myfunc.GetExcelPos(4, i + 1)].Text = Tmp_TYPEAF;
ws.Range[myfunc.GetExcelPos(5, i + 1)].Text = Tmp_PN_;
ws.Range[myfunc.GetExcelPos(6, i + 1)].Text = Tmp_C_NM;
ws.Range[myfunc.GetExcelPos(7, i + 1)].Text = Tmp_SEQOAF;
ws.Range[myfunc.GetExcelPos(8, i + 1)].Text = Tmp_MB1A;
//string Tmp_RBTHAF, Tmp_OGONOAF, Tmp_NGONOAF, Tmp_WBSNO;
ws.Range[myfunc.GetExcelPos(9, i + 1)].Text = Tmp_RBTHAF;
ws.Range[myfunc.GetExcelPos(10, i + 1)].Text = Tmp_OGONOAF;
ws.Range[myfunc.GetExcelPos(11, i + 1)].Text = Tmp_NGONOAF;
ws.Range[myfunc.GetExcelPos(12, i + 1)].Text = Tmp_WBSNO;
//string Tmp_FMNO, Tmp_DTSOR, Tmp_RQTYAF, Tmp_NSN, Tmp_EIAC_CUS;
ws.Range[myfunc.GetExcelPos(13, i + 1)].Text = Tmp_FMNO;
ws.Range[myfunc.GetExcelPos(14, i + 1)].Text = Tmp_DTSOR;
ws.Range[myfunc.GetExcelPos(15, i + 1)].Text = Tmp_RQTYAF;
ws.Range[myfunc.GetExcelPos(16, i + 1)].Text = Tmp_NSN;
ws.Range[myfunc.GetExcelPos(17, i + 1)].Text = Tmp_EIAC_CUS;
//string Tmp_FACWC, Tmp_FRTP, Tmp_STATFM, Tmp_STATFM_;
ws.Range[myfunc.GetExcelPos(18, i + 1)].Text = Tmp_FACWC;
ws.Range[myfunc.GetExcelPos(19, i + 1)].Text = Tmp_FRTP;
ws.Range[myfunc.GetExcelPos(20, i + 1)].Text = Tmp_STATFM;
ws.Range[myfunc.GetExcelPos(21, i + 1)].Text = Tmp_STATFM_;
}
wk.SaveToStream(mstream, FileFormat.Version2007);
wk.SaveToFile(FileName1, FileFormat.Version2007);
//wk.SaveToStream(mstream, FileFormat.Version2013);
//wk.SaveToFile(FileName1, FileFormat.Version2013);
}
catch (Exception e)
{
var Tmp_ErrMsg = e.Message;
//匯出檔產生失敗
Tmp_Str = "產生匯出檔案失敗<br>"
+ Tmp_ErrMsg;
MyCookie = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie);
//HttpContext.Current.Server.ClearError();
HttpContext.Current.ApplicationInstance.CompleteRequest();
return;
}
finally
{
wk.Dispose();
conn.Close();
}
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode((string)FileName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.BinaryWrite(mstream.ToArray());
//匯出檔產生完成
Tmp_Str = "產生匯出檔案成功<br>"
+ "(" + FileName + ")<br>";
MyCookie = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
} // end of public void DoPRNLIST()
