目的: V120502 - 套表列印, 並自動分頁
處理說明 :
1>前端:
1>>將子畫面的Grid資料存成 JSON字串, 當np參數傳給 Ext.Ajax
2>後端
1>>讀取 SS_FILES 套表(using Spire.Xls;)
2>>讀取 np 參數中的子畫面資料
3>>開啟 *.xlsx 檔案,填入欄位值, 若筆數>每頁筆數,則跳頁
3>>開啟 *.xlsx 檔案,填入欄位值, 若筆數>每頁筆數,則跳頁
1>*.js 將子畫面的資料轉成 JSON 字串, 並存成 np參數 , 傳給 Ext.Ajax
//[確定]鈕 - 套表列印
function mySub1_OkBtn_click() {
//0 > 將目前畫面的store資料, 存成 np(多筆資料) 當參數, 傳給 處理 url
console.log("0 mySub1_OkBtn_click - 列印包裝標籤.確定");
var Tmp_sub_store = Ext.getCmp('sub_Grid').store;
var 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="";
for (i = 0; i < Tmp_sub_store.getCount(); i++) {
cur_rec = Tmp_sub_store.getAt(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 Tmp_CHKDT = Ext.getCmp("sub_DT").getValue();
console.log("Tmp_CHKDT:", Tmp_CHKDT);
var np = {};
np = {
DataString1: Tmp_DataString1,
CHKDT: Tmp_CHKDT,
};
Ext.getCmp('mySubForm').submit({
//Ext.Ajax.request({
url: '../../api/V120502API/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 mySub1_OkBtn_click()
2>*.cs 處理
[HttpPost]
public void DoPRNLIST()
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
// 1>將 SS_FILES.FBOLD 存成 Local檔案
//取得目前子畫面的欄位值
string Tmp_DataString1 = nvc["DataString1"]; //Tmp_DataString: 將目前子畫面的資料. JSON字串
string Tmp_CHKDT = nvc["CHKDT"]; //[ ]不印日期
//將 table 字串轉成 DataTable
DataTable DataTable1 = (DataTable)JsonConvert.DeserializeObject(Tmp_DataString1, (typeof(DataTable)));
string Tmp_SAPNO, Tmp_PN, Tmp_EOCND, Tmp_RMK;
string Tmp_QTY;
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 = "V120502_包裝標籤_格式檔.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 = "V120502_包裝標籤_格式檔_" + 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]
int RowCnt = 0; //每頁 行
int PageRowIndex = 0; //本頁啟始 rowindex
int PageSize = 8; //每頁8行
//每筆資料列印後, 分頁印下一筆
for (var i = 0; i < DataTable1.Rows.Count; i++)
{
//每筆資料均分頁 & 複製樣板 rows
PageRowIndex = PageRowIndex + PageSize;
ws.HPageBreaks.Add(ws.Range[myfunc.GetExcelPos(0, PageRowIndex)]);
string Tmp_Range = "A" + string.Format("{0:D}", PageRowIndex + 1) + ":B" + string.Format("{0:D}", PageRowIndex + 1 + (PageSize - 1));
ws.Copy(ws.Range["A1:B8"], ws.Range[Tmp_Range], true);
Tmp_SAPNO = DataTable1.Rows[i]["SAPNO"].ToString();
Tmp_PN = DataTable1.Rows[i]["PN"].ToString();
Tmp_QTY = DataTable1.Rows[i]["QTY"].ToString();
Tmp_EOCND = DataTable1.Rows[i]["EOCND"].ToString();
Tmp_RMK = DataTable1.Rows[i]["RMK"].ToString();
ws.Range[myfunc.GetExcelPos(1, PageRowIndex)].Text = Tmp_PN;
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 1)].Text = Tmp_EOCND;
if (Tmp_CHKDT == "true") //不印日期
{
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 2)].Text = "";
}
else
{
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 2)].Text = myfunc.cnow();
}
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 3)].Text = Tmp_QTY;
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 4)].Text = Tmp_SAPNO;
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 5)].Text = ""; //品保欄位空白
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + 6)].Text = Tmp_RMK;
}
//刪除樣板 rows
ws.DeleteRow(1, PageSize);
//ws.AllocatedRange.AutoFitColumns();
//wk.SaveToStream(mstream, FileFormat.Version2007);
//wk.SaveToFile(FileName1, FileFormat.Version2007);
wk.SaveToStream(mstream, FileFormat.Version2013);
wk.SaveToFile(FileName1, FileFormat.Version2013);
//wk.SaveToFile(FileName, ExcelVersion.Version2007);
}
catch (Exception e)
{
var Tmp_ErrMsg = e.Message;
}
finally
{
//reader.Close();
//bw.Close();
//fs.Close();
wk.Dispose();
conn.Close();
//wk.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 = "請開啟如下XLS檔案<br>"
+"(" + FileName + ")<br>"
+"並挑選[標籤印表機(TSC_ME240)]印表機<br>"
+"標籤樣式100mm*60mm(寬*高)列印<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()
