void btnOrgExport_onStart(object sender, EventArgs e)
{
//產生匯出資料
string strFileName = "Org.xlsx";
byte[] oBytes = Util.getBytes(Util.getExcelOpenXml(OrgInfo.getOrgData()));
//將匯出資料設定為 FileInfoObj 物件
if (FileInfoObj.setFileInfoObj(strFileName, oBytes, true))
{
//資料直接下載
if (FileInfoObj.DirectDownload())
{
//下載正常
//btnOrgExport.Complete(); //不顯示訊息
btnOrgExport.Complete(RS.Resources.Msg_ExportDataReadyToDownload); //匯出資料準備完成，請按[存檔]下載
}
else
{
//下載錯誤
btnOrgExport.Complete(RS.Resources.Msg_ExportDataNotFound, Util.NotifyKind.Error); //查無可供匯出的資料
}
}
else
{
//設定 FileInfoObj 物件失敗
Util.NotifyMsg(RS.Resources.Msg_ExportDataError, Util.NotifyKind.Error); //匯出資料發生錯誤
}
}
/// <summary>
/// 產生並下載測試資料
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCreate_onStart(object sender, EventArgs e)
{
//產生測試資料結構
DataTable dt = new DataTable();
dt.Columns.Add("Field01");
dt.Columns.Add("Field02");
dt.Columns.Add("Field03");
//產生指定數量測試資料
int i;
for (i = 0; i < _CreateDataQty; i++)
{
DataRow dr = dt.NewRow();
dr[0] = "AA" + i.ToString().PadLeft(6, '0');
dr[1] = "BB" + i.ToString().PadLeft(6, '0');
dr[2] = "CC" + i.ToString().PadLeft(6, '0');
dt.Rows.Add(dr);
}
//將匯出資料
byte[] oBytes = Util.getBytes(Util.getExcelOpenXml(dt));
FileInfoObj.setFileInfoObj(_CreateFileName, oBytes, true);
FileInfoObj.DirectDownload();
btnCreate.Complete("匯出資料準備完成，請按[存檔]下載");
}
void btnProcess1_onStart(object sender, EventArgs e)
{
//設定替換清單
Dictionary <string, string> oReplaceList = new Dictionary <string, string>();
oReplaceList.Add("[$King$]", "柯潔");
oReplaceList.Add("[$Master$]", "李世乭");
try
{
//讀取套版範本
//Stream oTemplate = Util.getStream(Util.getAttachFileBody("AttachDBName", "AttachID")); //從附件資料庫取得版型範本
Stream oTemplate = File.OpenRead(Server.MapPath("Test.docx")); //從檔案系統取得版型範本
//套印成 DocX
Stream oOutput = Util.getDocOpenXml(oTemplate, oReplaceList); //從檔案系統取得版型範本
if (!oOutput.IsNullOrEmpty())
{
//檔案匯出
FileInfoObj.setFileInfoObj(string.Format("Sample_{0}.docx", DateTime.Today.ToString("yyyyMMdd")), Util.getBytes(oOutput));
FileInfoObj.DirectDownload();
btnProcess1.Complete(RS.Resources.Msg_ExportDataReadyToDownload);
}
else
{
btnProcess1.Complete(RS.Resources.Msg_ExportDataNotFound, Util.NotifyKind.Error);
}
}
catch
{
btnProcess1.Complete(RS.Resources.Msg_ExportDataError, Util.NotifyKind.Error);
}
}
/// <summary>
/// 上傳匯入資料
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpload_onClose(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(btnUpload.ucUploadedFileName) && btnUpload.ucUploadedFileBody.Length > 0)
{
FileInfoObj.setFileInfoObj(btnUpload.ucUploadedFileName, btnUpload.ucUploadedFileBody, true);
txtAction.Value = "SaveQueue";
ucLightBox.ucLightBoxMsg = "資料暫存中。。。";
ucLightBox.Show(true);
}
else
{
ShowMsg("未收到檔案");
}
}
protected void Page_Load(object sender, EventArgs e)
{
//直接下載 FileInfoObj 物件並自動清除 2016.11.18
FileInfoObj oFile = FileInfoObj.getFileInfoObj();
if (oFile != null && oFile.FileSize > 0)
{
string strFileNme = oFile.FileName;
byte[] binFileBody = oFile.FileBody;
FileInfoObj.Clear();
Util.ExportBinary(binFileBody, strFileNme);
}
else
{
labMsg.Text = Util.getHtmlMessage(Util.HtmlMessageKind.ParaError, RS.Resources.Msg_ParaError);
}
}
protected void gvAttach_RowCommand(object sender, GridViewCommandEventArgs e)
{
//處理使用 ImageButton 物件發動的自訂命令
if (e.CommandSource.GetType() == typeof(ImageButton))
{
int intIndex = ((GridViewRow)((ImageButton)e.CommandSource).NamingContainer).RowIndex;
if (intIndex >= 0)
{
//從 CommandArgument 取出鍵值陣列
string[] DataKeyValues = new string[e.CommandArgument.ToString().Split(',').Count()];
string strAttachDB = e.CommandArgument.ToString().Split(',')[0];
string strAttachID = e.CommandArgument.ToString().Split(',')[1];
int intSeqNo = int.Parse(e.CommandArgument.ToString().Split(',')[2]);
string strAnonymousAccess = e.CommandArgument.ToString().Split(',')[3];
string strIsEditMode = e.CommandArgument.ToString().Split(',')[4];
//處理自訂命令，AP 可視需要自行增加想要的 CommandName
switch (e.CommandName)
{
case "cmdSelect":
//選擇檔案
string strUserID = "";
if (Session["UserID"] != null)
{
strUserID = Session["UserID"].ToString().Trim();
}
bool IsAllowAccess = true;
bool IsAccessMD5 = false;
//檢查是否許匿名存取
if (strAnonymousAccess.ToUpper() == "N" && string.IsNullOrEmpty(strUserID))
{
IsAllowAccess = false;
}
//當不能匿名存取，但傳入參數符合「存取檢核碼」規範時，則允許存取 2013.07.25 新增
//「存取檢核碼」適用當 URL 放到 Mail ，又允許下載的情況中
if (IsAllowAccess == false)
{
if (!string.IsNullOrEmpty(Util.getRequestQueryStringKey("UserID")) && !string.IsNullOrEmpty(Util.getRequestQueryStringKey("AccessMD5")))
{
//產生檢核碼( yyyyMMdd + UserID + AttachDB + AttachID)
string strMD5Chk = Util.getMD5Hash(DateTime.Today.ToString("yyyyMMdd") + Util.getRequestQueryStringKey("UserID") + strAttachDB + strAttachID);
if (Util.getRequestQueryStringKey("AccessMD5", "", true) == strMD5Chk.ToUpper())
{
Session["UserID"] = Util.getRequestQueryStringKey("UserID");
IsAccessMD5 = true;
IsAllowAccess = true;
}
}
}
if (IsAllowAccess)
{
if (Util.IsAttachInfoLog(strAttachDB, strAttachID, intSeqNo))
{
string strSQL = string.Format("Select * from AttachInfo Where AttachID = '{0}' And SeqNo ='{1}' ;", strAttachID, intSeqNo);
DbHelper db = new DbHelper(strAttachDB);
DataTable dt = db.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
string strFileName = dt.Rows[0]["Filename"].ToString();
Byte[] binFileBody = (Byte[])dt.Rows[0]["FileBody"];
//將下載檔案設定為 FileInfoObj 物件 2016.11.24
if (FileInfoObj.setFileInfoObj(strFileName, binFileBody, true))
{
//直接下載
//Util.setJSContent(_defPopCloseClientJS, this.ClientID + "PopBody_Close");
if (!FileInfoObj.DirectDownload())
{
Util.NotifyMsg(RS.Resources.Msg_ExportDataNotFound, Util.NotifyKind.Error);
}
}
else
{
//匯出資料設為 FileInfoObj 失敗
Util.NotifyMsg(RS.Resources.Msg_ExportDataError, Util.NotifyKind.Error);
}
}
}
//若為暫時允許匿名存取，需還原Session
if (IsAccessMD5)
{
Session["UserID"] = null;
}
}
else
{
Util.NotifyMsg(RS.Resources.Attach_AccessDenied, Util.NotifyKind.Error);
}
ucLightBox.Hide(); //燈箱控制項 2017.01.13
break;
case "cmdDelete":
//刪除檔案
string strDelSQL = string.Format("Update AttachInfo Set FileSize = -1 ,FileBody = null Where AttachID = '{0}' And SeqNo = '{1}' ;", strAttachID, intSeqNo);
DbHelper dbDel = new DbHelper(strAttachDB);
if (dbDel.ExecuteNonQuery(CommandType.Text, strDelSQL) >= 0)
{
Util.IsAttachInfoLog(strAttachDB, strAttachID, intSeqNo, "Delete");
Refresh(strAttachDB, strAttachID, strIsEditMode == "Y" ? true : false);
}
//事件處理
AttachDeletedEventArgs eArgs = new AttachDeletedEventArgs();
eArgs.AttachDB = strAttachDB;
eArgs.AttachID = strAttachID;
eArgs.SeqNo = intSeqNo;
if (AttachDeleted != null)
{
AttachDeleted(this, eArgs);
}
break;
default:
//未定義的命令
Util.MsgBox(string.Format("[{0}] - {1}", e.CommandName, RS.Resources.Msg_Undefined));
break;
}
}
}
}
protected void gvAttach_SelectedIndexChanged(object sender, EventArgs e)
{
int intIndex = gvAttach.SelectedIndex;
string strAttachDB = gvAttach.DataKeys[intIndex].Values[0].ToString();
string strAttachID = gvAttach.DataKeys[intIndex].Values[1].ToString();
int intSeqNo = int.Parse(gvAttach.DataKeys[intIndex].Values[2].ToString());
string strAnonymousAccess = gvAttach.DataKeys[intIndex].Values[3].ToString();
string strUserID = "";
if (Session["UserID"] != null)
{
strUserID = Session["UserID"].ToString().Trim();
}
bool IsAllowAccess = true;
bool IsAccessMD5 = false;
//檢查是否許匿名存取
if (strAnonymousAccess.ToUpper() == "N" && string.IsNullOrEmpty(strUserID))
{
IsAllowAccess = false;
}
//當不能匿名存取，但傳入參數符合「檢核碼」規範時，則允許存取 2013.07.25 新增
if (IsAllowAccess == false)
{
if (!string.IsNullOrEmpty(Util.getRequestQueryStringKey("UserID")) && !string.IsNullOrEmpty(Util.getRequestQueryStringKey("AccessMD5")))
{
//產生檢核碼( yyyyMMdd + UserID + AttachDB + AttachID)
string strMD5Chk = Util.getMD5Hash(DateTime.Today.ToString("yyyyMMdd") + Util.getRequestQueryStringKey("UserID") + strAttachDB + strAttachID);
if (Util.getRequestQueryStringKey("AccessMD5", "", true) == strMD5Chk.ToUpper())
{
Session["UserID"] = Util.getRequestQueryStringKey("UserID");
IsAccessMD5 = true;
IsAllowAccess = true;
}
}
}
if (IsAllowAccess)
{
if (Util.IsAttachInfoLog(strAttachDB, strAttachID, intSeqNo))
{
string strSQL = string.Format("Select * from AttachInfo Where AttachID = '{0}' And SeqNo ='{1}' ;", strAttachID, intSeqNo);
DbHelper db = new DbHelper(strAttachDB);
DataTable dt = db.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
string strFileName = dt.Rows[0]["Filename"].ToString();
Byte[] binFileBody = (Byte[])dt.Rows[0]["FileBody"];
//將下載檔案設定為 FileInfoObj 物件 2016.11.24
if (FileInfoObj.setFileInfoObj(strFileName, binFileBody, true))
{
//直接下載
//Util.setJSContent(_defPopCloseClientJS, this.ClientID + "PopBody_Close");
if (!FileInfoObj.DirectDownload())
{
Util.NotifyMsg(RS.Resources.Msg_ExportDataNotFound, Util.NotifyKind.Error);
}
}
else
{
//匯出資料設為 FileInfoObj 失敗
Util.NotifyMsg(RS.Resources.Msg_ExportDataError, Util.NotifyKind.Error);
}
}
}
//若為暫時允許匿名存取，需還原Session
if (IsAccessMD5)
{
Session["UserID"] = null;
}
}
else
{
Util.NotifyMsg(RS.Resources.Attach_AccessDenied, Util.NotifyKind.Error);
}
}
void btnGrpMerge_onStart(object sender, EventArgs e)
{
//throw new NotImplementedException();
//將資料表先加上表頭、表尾，再加上兩階的群組分類後匯出 Excel
//來源資料表
DataTable dt = _MainData;
//資料欄位樣式
Dictionary <string, string> dicColum = _dicDisp;
//資料群組分類
Dictionary <string, string> dicGrp = _dicGrp;
//表頭
string strHeader = "永豐金控大額交易申報表";
//表尾
string strFooter = "●金額單位：新台幣(萬)元\n●每月10號前申報上個月交易金額";
//試算表名稱
string strSheetName = "[YYYY/MM]申報資料";
//匯出檔名
string strFileName = strHeader + ".xlsx";
//產生 Excel
using (ExcelPackage package = new ExcelPackage(Util.getExcelOpenXml(dt, strSheetName, null, dicColum, strHeader, strFooter)))
{
//初始變數
ExcelWorksheet oSheet = package.Workbook.Worksheets[1]; //試算表物件
int RowPos = 3; //起始列數 (跳過 [表頭] 及 [欄位抬頭] 列)
int GrpQty = 0; //單一群組資料筆數
string[] CustGrpList = null; //單一群組清單
//處理群組分類
foreach (var pair in dicGrp)
{
//群組表頭
oSheet.InsertRow(RowPos, 1);
oSheet.Cells[RowPos, 1].Value = pair.Key; //例：A.法 人
ExcelRange oRange = oSheet.Cells[RowPos, 1, RowPos, dt.Columns.Count]; //儲存格選取範圍
oRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
oRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
oRange.Style.Font.Color.SetColor(ColorTranslator.FromHtml("#FFFFFF"));
oRange.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#34466A"));
oRange.Style.Font.Bold = true;
oRange.Merge = true;
RowPos += 1;
CustGrpList = pair.Value.Split(','); //例： [台灣電力,中華票券]
//群組明細
for (int g = 0; g < CustGrpList.Count(); g++)
{
string strCustName = CustGrpList[g];
strCustName = strCustName.Replace("'", "''"); //預防 CustName 包含單引號 2017.04.24
GrpQty = dt.Select(string.Format("CustName = '{0}' ", strCustName)).Length; //例：CustName = [台灣電力] 的資料筆數
if (GrpQty > 0)
{
//欄一：客戶名稱
oSheet.Cells[RowPos, 1, RowPos + GrpQty - 1, 1].Merge = true;
oSheet.Cells[RowPos, 1, RowPos + GrpQty - 1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//欄二：總交易金額
oSheet.Cells[RowPos, 2, RowPos + GrpQty - 1, 2].Merge = true;
oSheet.Cells[RowPos, 2, RowPos + GrpQty - 1, 2].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//加上邊框
oSheet.Cells[RowPos + GrpQty - 1, 1, RowPos + GrpQty - 1, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//加上背景色
if ((g % 2) == 0)
{
oSheet.Cells[RowPos, 1, RowPos + GrpQty - 1, 2].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#999999"));
}
else
{
oSheet.Cells[RowPos, 1, RowPos + GrpQty - 1, 2].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#EEEEEE"));
}
RowPos += GrpQty;
}
}
}
//將匯出資料設定為 FileInfoObj 物件
byte[] oBytes = package.GetAsByteArray();
if (FileInfoObj.setFileInfoObj(strFileName, oBytes, true))
{
//資料直接下載
if (FileInfoObj.DirectDownload())
{
//下載正常
btnGrpMerge.Complete(RS.Resources.Msg_ExportDataReadyToDownload); //匯出資料準備完成，請按[存檔]下載
}
else
{
//下載錯誤
btnGrpMerge.Complete(RS.Resources.Msg_ExportDataNotFound, Util.NotifyKind.Error); //查無可供匯出的資料
}
}
else
{
//設定 FileInfoObj 物件失敗
Util.NotifyMsg(RS.Resources.Msg_ExportDataError, Util.NotifyKind.Error); //匯出資料發生錯誤
}
}
}
/// <summary>
/// 儲存暫存資料
/// </summary>
protected void SaveQueue()
{
FileInfoObj oFileInfo = FileInfoObj.getFileInfoObj();
if (oFileInfo.FileSize > 0)
{
//將上傳 Excel 內容轉成DataSet
DataSet ds = Util.getDataSetFromExcel(Util.getStream(oFileInfo.FileBody));
FileInfoObj.Clear();
try
{
//將上傳結果暫存到資料庫
if (ds != null && !ds.Tables[0].IsNullOrEmpty())
{
DataTable dt = ds.Tables[0];
//計算 PKey
var PKeys = Util.getAppKey(_DBName, _AppKeyID, dt.Rows.Count);
//更名及新增所需欄位
dt.Columns[0].ColumnName = "Col01";
dt.Columns[1].ColumnName = "Col02";
dt.Columns[2].ColumnName = "Col03";
dt.Columns.Add("PUser");
dt.Columns.Add("PKey");
dt.Columns.Add("UpdUser");
dt.Columns.Add("UpdTime");
string strUserID = UserInfo.getUserInfo().UserID;
DateTime dateNow = DateTime.Now;
for (int i = 0; i < dt.Rows.Count; i++)
{
//處理自訂欄位的值
dt.Rows[i]["PUser"] = strUserID; //記錄暫存資料是由那個 UserID 上傳
dt.Rows[i]["PKey"] = PKeys.Item1[i];
dt.Rows[i]["UpdUser"] = strUserID;
dt.Rows[i]["UpdTime"] = dateNow;
}
//批量存入 MS-SQL
SqlConnection conn = new SqlConnection(DbHelper.getConnectionStrings(_DBName).ConnectionString);
conn.Open();
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
//設定一批寫入筆數
sqlBC.BatchSize = 1000;
//設定逾時的秒數
sqlBC.BulkCopyTimeout = 60;
//設定要寫入的資料表
sqlBC.DestinationTableName = "TestBulkQueue";
//資料欄位對應
sqlBC.ColumnMappings.Add("PUser", "PUser");
sqlBC.ColumnMappings.Add("PKey", "PKey");
sqlBC.ColumnMappings.Add("Col01", "TabName");
sqlBC.ColumnMappings.Add("Col02", "FldName");
sqlBC.ColumnMappings.Add("Col03", "Value");
sqlBC.ColumnMappings.Add("UpdUser", "UpdUser");
sqlBC.ColumnMappings.Add("UpdTime", "UpdTime");
//** 進階應用 **
//設定 NotifyAfter 屬性，以便在每複製指定筆數後自動呼叫指定事件
//sqlBC.NotifyAfter = 10000;
//sqlBC.SqlRowsCopied += sqlBC_SqlRowsCopied;
//開始寫入
sqlBC.WriteToServer(dt);
}
conn.Dispose();
txtAction.Value = "WaitDataVerify";
ucLightBox.Show(true);
}
else
{
ShowMsg("Excel 無資料");
}
}
catch (Exception ex)
{
ShowMsg(ex.Message);
}
}
}
