目的: xls套表列印, 分頁處理- Cookie - xls欄位格式設定 - 欄位合併, 插入圖片
處理說明: 1>由 SS_FILES 讀取套表檔案 , 存至 Server檔案目錄
2>xls分頁處理
2>xls分頁處理
3> xls欄位合併, 插入圖片
1>*.js - V120402D.js
//[列印]鈕 - 列印套表 - Txxxx 技令借用登記表 - Dxxx 文件借用登記簿
function sub_LookFlds_OkBtn() {
//1>將子畫面 store 的資料轉成 JSON 字串,當參數傳至後端 Ext.Ajax
var Tmp_sub_store = Ext.getCmp("sub_Grid").store;
var Tmp_JSONString = store2JSON(Tmp_sub_store);
var np = {};
np["JSONString"] = Tmp_JSONString;
//子畫面 submit
Ext.getCmp('mySubForm').submit({
//Ext.Ajax.request({
url: '../../api/V120402DAPI/DoPRNLIST',
method: 'POST',
async: false,
standardSubmit: true,// 非 Ajax 的方式 //若要傳送檔案至前端, standardSubmit必需設為 true
params: np, //把目前 store資料轉成JSON, 當成處理參數 np
});
//顯示訊息 - 請稍候
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '處理中,請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
var timer = setInterval(function () {
clearInterval(timer);
mask.hide();
timer = null;
//讀取 cookie的訊息
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 - SS_FILES 轉成 xls 處理, xls欄位格式設定, 加入圖片
//套表列印 - 借閱記錄 - 列印文件借閱登記簿
[HttpPost]
public void DoPRNLIST()
{
var c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
// 1>將 SS_FILES.FBOLD 存成 Local檔案
//取得目前子畫面的欄位值
string Tmp_JSONString = nvc["JSONString"];//Tmp_DataJSON: 將目前子畫面的資料. JSON字串
DataTable Tmp_dt = myfunc.JSON2dt(Tmp_JSONString);
//DataTable DataTable1 = (DataTable)JsonConvert.DeserializeObject(Tmp_DataString1, (typeof(DataTable)));
OracleConnection conn = new OracleConnection(DBService.ConnectionString(DBLINK));//
OracleCommand cmd = new OracleCommand();
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 = "V120402_文件借閱登記簿.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 = "V120402_文件借閱登記簿" + 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
//FileStream fs = File.Open(pathFName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
Worksheet ws = wk.Worksheets[0];//獲取第一個工作表
//寫入[SAPNO][PN][QTY][EOCND][RMK]
//int RowCnt = 0; //每頁 行
int PageRowIndex = 0; //本頁啟始 rowindex
int PageSize = 29; //每頁29行 , 含 頁首 行數
int DataSize = 21; //資料行數 , 資料row 行數
int PageTitleRows = 6; //頁首行數 (含資料標題行)
string Tmp_DOCNO, Tmp_STLCT, Tmp_BRP_, Tmp_TELNO;
string Tmp_BRDT, Tmp_RTDT, Tmp_REMARK;
string Tmp_PicName = "V120402_Pic.jpg";
string Tmp_pathPicName = documentPath + Tmp_PicName;
int Pages = 0;
int PageRow = -1;
//每筆資料列印後, 分頁印下一筆
for (var i = 0; i < Tmp_dt.Rows.Count; i++)
{
//每筆資料均分頁 & 複製樣板 rows
//每21筆資料,則分頁
if ((i % DataSize) ==0)
{
Pages = Pages + 1;
PageRow = -1;
if (Pages == 2)
Console.WriteLine("Pages", Pages);
PageRowIndex = PageRowIndex + PageSize;
//分頁
ws.HPageBreaks.Add(ws.Range[myfunc.GetExcelPos(0, PageRowIndex)]);
string Tmp_Range = "A" + string.Format("{0:D}", PageRowIndex + 1) + ":H" + string.Format("{0:D}", PageRowIndex + 1 + (PageSize - 1));
ws.Copy(ws.Range["A1:H29"], ws.Range[Tmp_Range], true);
Tmp_Range = "D" + string.Format("{0:D}", PageRowIndex + 2) + ":F" + string.Format("{0:D}", PageRowIndex + 2);
//文件借閱登記簿 merge - 欄位合併
//ws.Range[D2: F2].Merge();
ws.Range[Tmp_Range].Merge();
//設定列高度
ws.SetRowHeight(PageRowIndex + 2, 30);
//(Document On-loan Records) merge
Tmp_Range = "D" + string.Format("{0:D}", PageRowIndex + 3) + ":F" + string.Format("{0:D}", PageRowIndex + 3);
//ws.Range[D3: F3].Merge();
ws.Range[Tmp_Range].Merge();
ws.SetRowHeight(PageRowIndex + 3, 25);
//加入圖片
int Tmp_row = PageRowIndex + 1;
//string Tmp_pathPicName = "C:\\Users\\611292\\Desktop\\demo.jpg";
//將 SS_FILES 存成檔案(Tmp_PathFName)
Tmp_pathPicName = myfunc.SS_FILES2PathFName(Tmp_PicName, DBLINK);
ws.Pictures.Add(Tmp_row, 1, Tmp_pathPicName);
}
PageRow = PageRow + 1;
Tmp_DOCNO = Tmp_dt.Rows[i]["DOCNO"].ToString();
Tmp_STLCT = Tmp_dt.Rows[i]["STLCT"].ToString();
Tmp_BRP_ = Tmp_dt.Rows[i]["BRP_"].ToString();
Tmp_TELNO = Tmp_dt.Rows[i]["TELNO"].ToString();
if (Tmp_dt.Rows[i]["BRDT"].ToString().Length > 0)
Tmp_BRDT = DateTime.Parse(Tmp_dt.Rows[i]["BRDT"].ToString()).ToString("yyyy/MM/dd");
else
Tmp_BRDT = "";
if (Tmp_dt.Rows[i]["RTDT"].ToString().Length > 0)
Tmp_RTDT = DateTime.Parse(Tmp_dt.Rows[i]["RTDT"].ToString()).ToString("yyyy/MM/dd");
else
Tmp_RTDT = "";
Tmp_REMARK = Tmp_dt.Rows[i]["REMARK"].ToString();
ws.Range[myfunc.GetExcelPos(1, PageRowIndex + PageTitleRows + PageRow)].Text =Tmp_DOCNO;
ws.Range[myfunc.GetExcelPos(2, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_STLCT;
ws.Range[myfunc.GetExcelPos(3, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_BRP_;
ws.Range[myfunc.GetExcelPos(4, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_TELNO;
ws.Range[myfunc.GetExcelPos(5, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_BRDT;
ws.Range[myfunc.GetExcelPos(6, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_RTDT;
ws.Range[myfunc.GetExcelPos(7, PageRowIndex + PageTitleRows + PageRow)].Text = Tmp_REMARK;
}
//刪除樣板 rows
ws.DeleteRow(1, PageSize);
ws.Activate();
wk.SaveToStream(mstream, FileFormat.Version2007);
wk.SaveToFile(FileName1, FileFormat.Version2007);
//開啟 xls file
System.Diagnostics.Process.Start(FileName1);
//wk.SaveToFile(FileName, ExcelVersion.Version2007);
/* 開啟 Local xls 檔案
* using System.Diagnostics;
Process process = new Process();
process.StartInfo.FileName = "WINWORD.EXE";
process.StartInfo.Arguments = @"C:\Users\Shani\Desktop\process.docx";
process.Start();
*/
}
catch (Exception e)
{
var Tmp_ErrMsg = e.Message;
Tmp_Str = "開啟XLS檔案 , 錯誤訊息如下<br>"
+ "(" + FileName + ")報表<br>"
+ Tmp_ErrMsg;
HttpCookie MyCookie1 = new HttpCookie("EX_DFile", HttpUtility.UrlEncode(Tmp_Str));
HttpContext.Current.Response.Cookies.Add(MyCookie1);
//HttpContext.Current.Response.End();
//throw;
}
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.BinaryWrite(file);
//HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.BinaryWrite(mstream.ToArray());
//包裝標籤檔產生完成
Tmp_Str = "請開啟如下XLS檔案<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()
