目的: 畫面的欄位值,直接匯出至套表, 不分頁
處理說明: 1>讀取套表檔案 SS_FILES
2>讀取 store 資料匯出至 XLS
1>*.js , 依所勾選的資料匯出轉成 JSONString 當參數, 傳至後端
//查詢 - 將目前所選的資料,匯出成報表
function LookBtn_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("請先選擇要查詢的資料");
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.Ajax, 必需使用 Form.submit
Ext.getCmp('s_form').submit({
url: '../../api/V20306API/DoPRNLIST',
method: 'POST',
async: false,
standardSubmit: true, //若要傳送檔案至前端, standardSubmit必需設為 true
params: np,
});
//顯示結果訊息..
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '處理中,請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
var timer = setInterval(function () {
clearInterval(timer);
mask.hide();
timer = null;
var r = r_cookies('EX_DFile');
console.log("r_cookies=", r);
if (!checkisnull(r)) {
mysuccessalert(r);
}
}, 5000); //3000ms = 3sec
} // end of function LookBtn_click() {
2>*.cs - 依傳入的資料參數, 匯出至 XLS
//加入JSON格式轉換參考 , JsonConvert 必需使用如下 library
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
//查詢 - 將所選的資料匯出至 xls - 套表
[HttpPost]
public void DoPRNLIST() {
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
// 1>將 SS_FILES.FBOLD 存成 Local檔案
//取得目前子畫面的欄位值
string Tmp_DataString1 = nvc["DataString1"]; //取得目前子畫面的資料. JSON字串
//將 table 字串轉成 DataTable
//DataTable DataTable1 = (DataTable)JsonConvert.DeserializeObject(Tmp_DataString1, (typeof(DataTable)));
DataTable DataTable1 = 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 = "V20306_列印報表.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 = "V20306_列印報表_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
string FileName1 = documentPath + FileName;
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];//獲取第一個工作表
//寫入[SAPNO][PN][QTY][EOCND][RMK]
string Tmp_ASPNO, Tmp_RPNOAF, Tmp_LCNO, Tmp_LRWET_;
string Tmp_MCNM, Tmp_ORDID_, Tmp_PCTP, Tmp_FICNMA, Tmp_STLCT;
string Tmp_PCQTY,Tmp_ASLV,Tmp_ASPRMK,Tmp_NGONOAF,Tmp_PN;
string Tmp_LCITM, Tmp_LCETDT, Tmp_LCCHID_, Tmp_LCORAO, Tmp_LRULT;
string Tmp_LRUWT, Tmp_LRUHG,Tmp_WTLV,Tmp_ITPR;
//每筆資料列印後, 不分頁列印下一筆
for (var i = 0; i < DataTable1.Rows.Count; i++)
{
Tmp_ASPNO = DataTable1.Rows[i]["ASPNO"].ToString();
Tmp_RPNOAF = DataTable1.Rows[i]["RPNOAF"].ToString();
Tmp_LCNO = DataTable1.Rows[i]["LCNO"].ToString();
//直接取得畫面.CaluField欄位值
Tmp_LRWET_ = DataTable1.Rows[i]["LRWET_"].ToString();//系統件重量說明
Tmp_ORDID_ = DataTable1.Rows[i]["ORDID_"].ToString(); //訂購者姓名
Tmp_MCNM = DataTable1.Rows[i]["MCNM"].ToString();
Tmp_PCTP = DataTable1.Rows[i]["PCTP"].ToString();
Tmp_FICNMA = DataTable1.Rows[i]["FICNMA"].ToString();
Tmp_STLCT = DataTable1.Rows[i]["STLCT"].ToString();
Tmp_PCQTY = DataTable1.Rows[i]["PCQTY"].ToString();
Tmp_ASLV = DataTable1.Rows[i]["ASLV"].ToString();
Tmp_ASPRMK = DataTable1.Rows[i]["ASPRMK"].ToString();
Tmp_NGONOAF = DataTable1.Rows[i]["NGONOAF"].ToString();
Tmp_PN = DataTable1.Rows[i]["PN"].ToString();
Tmp_LCITM = DataTable1.Rows[i]["LCITM"].ToString();
Tmp_LCETDT = DataTable1.Rows[i]["LCETDT"].ToString();
Tmp_LCCHID_ = DataTable1.Rows[i]["LCCHID_"].ToString();
Tmp_LCORAO = DataTable1.Rows[i]["LCORAO"].ToString();
Tmp_LRULT = DataTable1.Rows[i]["LRULT"].ToString();
Tmp_LRUWT = DataTable1.Rows[i]["LRUWT"].ToString();
Tmp_LRUHG = DataTable1.Rows[i]["LRUHG"].ToString();
Tmp_WTLV = DataTable1.Rows[i]["WTLV"].ToString();
Tmp_ITPR = DataTable1.Rows[i]["ITPR"].ToString();
//GetExcelPos(欄, 行) 以 0 開始,
ws.Range[myfunc.GetExcelPos(0, i+1)].Text = Tmp_ASPNO;
ws.Range[myfunc.GetExcelPos(1, i+1)].Text = Tmp_RPNOAF;
ws.Range[myfunc.GetExcelPos(2, i + 1)].Text = Tmp_LCNO;
ws.Range[myfunc.GetExcelPos(3, i + 1)].Text = Tmp_LRWET_;
ws.Range[myfunc.GetExcelPos(4, i + 1)].Text = Tmp_MCNM;
ws.Range[myfunc.GetExcelPos(5, i + 1)].Text = Tmp_ORDID_;
ws.Range[myfunc.GetExcelPos(6, i + 1)].Text = Tmp_PCTP;
ws.Range[myfunc.GetExcelPos(7, i + 1)].Text = Tmp_FICNMA;
ws.Range[myfunc.GetExcelPos(8, i + 1)].Text = Tmp_STLCT;
ws.Range[myfunc.GetExcelPos(9, i + 1)].Text = Tmp_PCQTY;
ws.Range[myfunc.GetExcelPos(10, i + 1)].Text = Tmp_ASLV;
ws.Range[myfunc.GetExcelPos(11, i + 1)].Text = Tmp_ASPRMK;
ws.Range[myfunc.GetExcelPos(12, i + 1)].Text = Tmp_NGONOAF;
ws.Range[myfunc.GetExcelPos(13, i + 1)].Text = Tmp_PN;
ws.Range[myfunc.GetExcelPos(14, i + 1)].Text = Tmp_LCITM;
ws.Range[myfunc.GetExcelPos(15, i + 1)].Text = Tmp_LCETDT;
ws.Range[myfunc.GetExcelPos(16, i + 1)].Text = Tmp_LCCHID_;
ws.Range[myfunc.GetExcelPos(17, i + 1)].Text = Tmp_LCORAO;
ws.Range[myfunc.GetExcelPos(18, i + 1)].Text = Tmp_LRULT;
ws.Range[myfunc.GetExcelPos(19, i + 1)].Text = Tmp_LRUWT;
ws.Range[myfunc.GetExcelPos(20, i + 1)].Text = Tmp_LRUHG;
ws.Range[myfunc.GetExcelPos(21, i + 1)].Text = Tmp_WTLV;
ws.Range[myfunc.GetExcelPos(22, i + 1)].Text = Tmp_ITPR;
}
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;
}
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>";
HttpCookie MyCookie = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
} // end of public void DoPRNLIST()
