private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 30;
sheet.Columns(1).Width = 200;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "列數";
sheet[1, iRow].Value = "異常原因";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["ID"].ToString();
sheet[1, iRow].Value = dr["ERRORMEMO"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(10).Width = 65;
sheet.Columns(11).Width = 65;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
//sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "交易別";
sheet[1, iRow].Value = "原結帳年月";
sheet[2, iRow].Value = "結帳年月";
sheet[3, iRow].Value = "店群";
sheet[4, iRow].Value = "店群名稱";
sheet[5, iRow].Value = "通路";
sheet[6, iRow].Value = "店號";
sheet[7, iRow].Value = "店名";
sheet[8, iRow].Value = "入帳日";
sheet[9, iRow].Value = "簽收日";
sheet[10, iRow].Value = "單號";
sheet[11, iRow].Value = "未稅牌價金額";
sheet[12, iRow].Value = "牌價稅額";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["STAKind"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["AC_BALANCE_DATE_S"].ToString();
sheet[2, iRow].Value = dtTemp.Rows[i]["AC_BALANCE_DATE"].ToString();
sheet[3, iRow].Value = dtTemp.Rows[i]["GROUP_NO"].ToString();
sheet[4, iRow].Value = dtTemp.Rows[i]["GROUP_NAME"].ToString();
sheet[5, iRow].Value = dtTemp.Rows[i]["CHAN_NO"].ToString();
sheet[6, iRow].Value = dtTemp.Rows[i]["STORE"].ToString();
sheet[7, iRow].Value = dtTemp.Rows[i]["STORE_NAME"].ToString();
sheet[8, iRow].Value = dtTemp.Rows[i]["BUS_DATE"].ToString();
sheet[9, iRow].Value = dtTemp.Rows[i]["SIGN_DATE"].ToString();
sheet[10, iRow].Value = dtTemp.Rows[i]["BUS_NO"].ToString();
sheet[11, iRow].Value = decimal.Parse(dtTemp.Rows[i]["COST_DIS_SUM"].ToString());
sheet[12, iRow].Value = decimal.Parse(dtTemp.Rows[i]["COST_DIS_TAX_SUM"].ToString());
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(0).Width = 150;
sheet.Columns(7).Width = 200;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
//sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "分錄名稱";
sheet[1, iRow].Value = "傳票分組編號";
sheet[2, iRow].Value = "借D/貸C";
sheet[3, iRow].Value = "會計科目";
sheet[4, iRow].Value = "單位代碼";
sheet[5, iRow].Value = "對象";
sheet[6, iRow].Value = "單據號碼";
sheet[7, iRow].Value = "摘要";
sheet[8, iRow].Value = "金額";
sheet[9, iRow].Value = "備註";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["ACCT_NAME"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["GROUP_ID"].ToString();
sheet[2, iRow].Value = dtTemp.Rows[i]["T_TYPE"].ToString(); ;
sheet[3, iRow].Value = dtTemp.Rows[i]["ACCOUNT"].ToString();
sheet[4, iRow].Value = dtTemp.Rows[i]["UNIT"].ToString();
sheet[5, iRow].Value = dtTemp.Rows[i]["ACCT_OBJECT"].ToString();
sheet[6, iRow].Value = dtTemp.Rows[i]["ENTRY_NO"].ToString();
sheet[7, iRow].Value = dtTemp.Rows[i]["SUMMARY"].ToString();
sheet[8, iRow].Value = decimal.Parse(dtTemp.Rows[i]["AMT"].ToString());
sheet[9, iRow].Value = dtTemp.Rows[i]["MEMO"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(0).Width = 30;
sheet.Columns(1).Width = 30;
sheet.Columns(3).Width = 30;
sheet.Columns(4).Width = 70;
sheet.Columns(14).Width = 80;
sheet.Columns(15).Width = 95;
sheet.Columns(16).Width = 95;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
//sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
DateTime sub_date = DateTime.Today.AddDays(-25);
string sub_month = sub_date.Year.ToString() + sub_date.Month.ToString();
iRow = 0;
sheet[0, iRow].Value = "店號";
sheet[1, iRow].Value = "品號";
sheet[2, iRow].Value = "品名";
sheet[3, iRow].Value = "期別";
sheet[4, iRow].Value = "高見單號";
sheet[5, iRow].Value = "成本";
sheet[6, iRow].Value = "牌價";
sheet[7, iRow].Value = "牌價低於成本";
sheet[8, iRow].Value = "零售價";
sheet[9, iRow].Value = "出版社補貼%";
sheet[10, iRow].Value = "零售總額";
sheet[11, iRow].Value = "數量";
sheet[12, iRow].Value = "期別開始日";
sheet[13, iRow].Value = "期別結束日";
sheet[14, iRow].Value = sub_month + "月補貼未稅";
sheet[15, iRow].Value = "至" + sub_month + "月已請補貼";
sheet[16, iRow].Value = sub_month + "月欲請補貼";
sheet[17, iRow].Value = sub_month + "月圖書經銷實銷預估六成";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["STORE"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["item"].ToString();
sheet[2, iRow].Value = dtTemp.Rows[i]["item_name"].ToString();
sheet[3, iRow].Value = dtTemp.Rows[i]["period"].ToString();
sheet[4, iRow].Value = dtTemp.Rows[i]["BUS_NO"].ToString();
sheet[5, iRow].Value = decimal.Parse(dtTemp.Rows[i]["COST"].ToString());
sheet[6, iRow].Value = decimal.Parse(dtTemp.Rows[i]["out_cost_dis"].ToString());
sheet[7, iRow].Value = decimal.Parse(dtTemp.Rows[i]["UNDERCOST"].ToString());
sheet[8, iRow].Value = decimal.Parse(dtTemp.Rows[i]["OUT_PRICE"].ToString());
sheet[9, iRow].Value = decimal.Parse(dtTemp.Rows[i]["vendor_subsidy_percent"].ToString());
sheet[10, iRow].Value = decimal.Parse(dtTemp.Rows[i]["total_price"].ToString());
sheet[11, iRow].Value = decimal.Parse(dtTemp.Rows[i]["OUT_QTY"].ToString());
sheet[12, iRow].Value = Convert.ToDateTime(dtTemp.Rows[i]["period_start_date"]).ToShortDateString();
sheet[13, iRow].Value = Convert.ToDateTime(dtTemp.Rows[i]["period_end_date"]).ToShortDateString();
sheet[14, iRow].Value = decimal.Parse(dtTemp.Rows[i]["subsidy"].ToString());
sheet[15, iRow].Value = "";
sheet[16, iRow].Value = "";
sheet[17, iRow].Value = "";
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 18;
sheet.Columns(1).Width = 60;
sheet.Columns(2).Width = 90;
sheet.Columns(3).Width = 60;
sheet.Columns(4).Width = 60;
sheet.Columns(5).Width = 90;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "序號";
sheet[1, iRow].Value = "供應商代號";
sheet[2, iRow].Value = "供應商名稱";
sheet[3, iRow].Value = "品號";
sheet[4, iRow].Value = "期別";
sheet[5, iRow].Value = "品名";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = iRow.ToString();
sheet[1, iRow].Value = dr["VENDOR"].ToString();
sheet[2, iRow].Value = dr["VENDOR_NAME"].ToString();
sheet[3, iRow].Value = dr["ITEM"].ToString();
sheet[4, iRow].Value = dr["PERIOD"].ToString();
sheet[5, iRow].Value = dr["ITEM_NAME"].ToString();
iRow++;
}
}
private void AddSheetData3(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 40;
sheet.Columns(1).Width = 70;
sheet.Columns(2).Width = 40;
sheet.Columns(3).Width = 70;
sheet.Columns(4).Width = 40;
sheet.Columns(5).Width = 70;
sheet.Columns(6).Width = 50;
sheet.Columns(7).Width = 80;
sheet.Columns(8).Width = 50;
sheet.Columns(9).Width = 80;
sheet.Columns(10).Width = 70;
sheet.Columns(11).Width = 70;
sheet.Columns(12).Width = 70;
sheet.Columns(13).Width = 70;
sheet.Columns(14).Width = 70;
sheet.Columns(15).Width = 70;
sheet.Columns(16).Width = 70;
sheet.Columns(17).Width = 70;
sheet.Columns(18).Width = 70;
sheet.Columns(19).Width = 70;
sheet.Columns(20).Width = 70;
sheet.Columns(21).Width = 70;
sheet.Columns(22).Width = 70;
sheet.Columns(23).Width = 70;
sheet.Columns(24).Width = 70;
sheet.Columns(25).Width = 70;
sheet.Columns(26).Width = 70;
sheet.Columns(27).Width = 70;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 1;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "營業所";
sheet[1, iRow].Value = "營業所名稱";
sheet[2, iRow].Value = "通路";
sheet[3, iRow].Value = "通路名稱";
sheet[4, iRow].Value = "門市";
sheet[5, iRow].Value = "門市名稱";
sheet[6, iRow].Value = "品號";
sheet[7, iRow].Value = "品名";
sheet[8, iRow].Value = "期別";
sheet[9, iRow].Value = "大分類";
sheet[10, iRow].Value = "進貨數量";
sheet[11, iRow].Value = "進貨牌價";
sheet[12, iRow].Value = "進貨零售價";
sheet[13, iRow].Value = "退貨數量";
sheet[14, iRow].Value = "退貨牌價";
sheet[15, iRow].Value = "退貨零售價";
sheet[16, iRow].Value = "正補進數量";
sheet[17, iRow].Value = "補帳(進)牌價(+)";
sheet[18, iRow].Value = "補帳(進)零售價(+)";
sheet[19, iRow].Value = "負補進數量";
sheet[20, iRow].Value = "補帳(進)牌價(-)";
sheet[21, iRow].Value = "補帳(進)零售價(-)";
sheet[22, iRow].Value = "調撥(進)數量";
sheet[23, iRow].Value = "調撥(進)牌價";
sheet[24, iRow].Value = "調撥(進)零售價";
sheet[25, iRow].Value = "調撥(出)數量";
sheet[26, iRow].Value = "調撥(出)牌價";
sheet[27, iRow].Value = "調撥(出)零售價";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["Z_O"].ToString();
sheet[1, iRow].Value = dr["ZO_NAME"].ToString();
sheet[2, iRow].Value = dr["CHAN_NO"].ToString();
sheet[3, iRow].Value = dr["CHAN_NAME"].ToString();
sheet[4, iRow].Value = dr["STORE"].ToString();
sheet[5, iRow].Value = dr["STORE_NAME"].ToString();
sheet[6, iRow].Value = dr["ITEM"].ToString();
sheet[7, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[8, iRow].Value = dr["PERIOD"].ToString();
sheet[9, iRow].Value = dr["PMA"].ToString();
sheet[10, iRow].Value = dr["ST_ACCEPT_QTY"].ToString();
sheet[11, iRow].Value = dr["PATCH_A_QTY_P"].ToString();
sheet[12, iRow].Value = dr["ADJ_IN_QTY"].ToString();
sheet[13, iRow].Value = dr["BACKWARD_QTY"].ToString();
sheet[14, iRow].Value = dr["PATCH_A_QTY_N"].ToString();
sheet[15, iRow].Value = dr["ADJ_OUT_QTY"].ToString();
sheet[16, iRow].Value = dr["ST_ACCEPT_COST_DIS"].ToString();
sheet[17, iRow].Value = dr["PATCH_A_COST_DIS_P"].ToString();
sheet[18, iRow].Value = dr["ADJ_IN_COST_DIS"].ToString();
sheet[19, iRow].Value = dr["BACKWARD_COST_DIS"].ToString();
sheet[20, iRow].Value = dr["PATCH_A_COST_DIS_N"].ToString();
sheet[21, iRow].Value = dr["ADJ_OUT_COST_DIS"].ToString();
sheet[22, iRow].Value = dr["ST_ACCEPT_PRICE"].ToString();
sheet[23, iRow].Value = dr["PATCH_A_PRICE_P"].ToString();
sheet[24, iRow].Value = dr["ADJ_IN_PRICE"].ToString();
sheet[25, iRow].Value = dr["BACKWARD_PRICE"].ToString();
sheet[26, iRow].Value = dr["PATCH_A_PRICE_N"].ToString();
sheet[27, iRow].Value = dr["ADJ_OUT_PRICE"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(2).Width = 74;
sheet.Columns(7).Width = 74;
//日期欄位設寬一點
double dblWidth_Date = 74;
sheet.Columns(5).Width = dblWidth_Date;
sheet.Columns(6).Width = dblWidth_Date;
sheet.Columns(10).Width = dblWidth_Date;
sheet.Columns(11).Width = dblWidth_Date;
sheet.Columns(12).Width = dblWidth_Date;
sheet.Columns(13).Width = dblWidth_Date;
sheet.Columns(14).Width = dblWidth_Date;
sheet.Columns(17).Width = dblWidth_Date;
sheet.Columns(19).Width = dblWidth_Date;
sheet.Columns(21).Width = dblWidth_Date;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
for (int i = 0; i < dtTemp.Columns.Count; i++)
{
sheet[i, iRow].Value = dtTemp.Columns[i].Caption;
}
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
for (int j = 0; j < dtTemp.Columns.Count; j++)
{
sheet[j, iRow].Value = dtTemp.Rows[i][j].ToString();
}
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataRow[] FindRows)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 18;
sheet.Columns(1).Width = 104;
sheet.Columns(2).Width = 30;
sheet.Columns(3).Width = 59;
sheet.Columns(4).Width = 94;
sheet.Columns(5).Width = 17;
sheet.Columns(6).Width = 299;
sheet.Columns(7).Width = 35;
sheet.Columns(8).Width = 46;
sheet.Columns(9).Width = 57;
sheet.Columns(10).Width = 65;
sheet.Columns(11).Width = 94;
sheet.Columns(12).Width = 71;
sheet.Columns(13).Width = 18;
sheet.Columns(14).Width = 97;
sheet.Columns(15).Width = 34;
sheet.Columns(16).Width = 83;
sheet.Columns(17).Width = 46;
sheet.Columns(18).Width = 70;
sheet.Columns(19).Width = 34;
sheet.Columns(20).Width = 71;
sheet.Columns(21).Width = 57;
sheet.Columns(22).Width = 79;
sheet.Columns(23).Width = 113;
sheet.Columns(24).Width = 60;
sheet.Columns(25).Width = 90;
sheet.Columns(26).Width = 33;
sheet.Columns(27).Width = 61;
sheet.Columns(28).Width = 54;
//sheet.Columns(29).Width = 0;
//sheet.Columns(30).Width = 0;
int iRow = 0;
//即有品項
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 3;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "";
sheet[1, iRow].Value = "EAN";
sheet[2, iRow].Value = "";
sheet[3, iRow].Value = "廠商貨號";
sheet[4, iRow].Value = "通路對照號";
sheet[5, iRow].Value = "";
sheet[6, iRow].Value = "商品名稱";
sheet[7, iRow].Value = "售價";
sheet[8, iRow].Value = "";
sheet[9, iRow].Value = "售價幣別";
sheet[10, iRow].Value = "Cover Price";
sheet[11, iRow].Value = "Cover Price 幣別";
sheet[12, iRow].Value = "進貨基準價";
sheet[13, iRow].Value = "";
sheet[14, iRow].Value = "進貨基準價幣別";
sheet[15, iRow].Value = "折扣";
sheet[16, iRow].Value = "版別";
sheet[17, iRow].Value = "版別說明";
sheet[18, iRow].Value = "語言別";
sheet[19, iRow].Value = "語言別說明";
sheet[20, iRow].Value = "貨源";
sheet[21, iRow].Value = "主廠商編號";
sheet[22, iRow].Value = "出版商編號";
sheet[23, iRow].Value = "採購群組";
sheet[24, iRow].Value = "物料群組";
sheet[25, iRow].Value = "物料群組說明";
sheet[26, iRow].Value = "製造日期";
sheet[27, iRow].Value = "作者名";
sheet[28, iRow].Value = "材質";
sheet[29, iRow].Value = "尺寸";
sheet[30, iRow].Value = "錯誤訊息";
//sheet[31, iRow].Value = "匯入者";
//sheet[32, iRow].Value = "匯入日期";
iRow++;
foreach (DataRow dr in FindRows)
{
sheet[0, iRow].Value = "";
sheet[1, iRow].Value = dr["EAN_CODE"].ToString();
sheet[2, iRow].Value = "";
sheet[3, iRow].Value = dr["ITEM"].ToString();
sheet[4, iRow].Value = dr["CHAN_CODE"].ToString();
sheet[5, iRow].Value = "";
sheet[6, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[7, iRow].Value = dr["PRICES"].ToString();
sheet[8, iRow].Value = "";
sheet[9, iRow].Value = dr["PRICES_CURRENCY"].ToString();
sheet[10, iRow].Value = dr["COVER_PRICE"].ToString();
sheet[11, iRow].Value = dr["COVER_PRICE_CURRENCY"].ToString();
sheet[12, iRow].Value = dr["COST_PRICE"].ToString();
sheet[13, iRow].Value = "";
sheet[14, iRow].Value = dr["COST_PRICE_CURRENCY"].ToString();
sheet[15, iRow].Value = dr["DISCOUNT"].ToString();
sheet[16, iRow].Value = dr["PUBLISH_TYPE"].ToString();
sheet[17, iRow].Value = dr["PUBLISH_TYPE_MEMO"].ToString();
sheet[18, iRow].Value = dr["LANGUAGE_CODE"].ToString();
sheet[19, iRow].Value = dr["LANGUAGE_MEMO"].ToString();
sheet[20, iRow].Value = dr["SOURCE"].ToString();
sheet[21, iRow].Value = dr["MAIN_FACTORY_CODE"].ToString();
sheet[22, iRow].Value = dr["PUBLISH_CODE"].ToString();
sheet[23, iRow].Value = dr["BUY_GROUP"].ToString();
sheet[24, iRow].Value = dr["MATERIEL_GROUP"].ToString();
sheet[25, iRow].Value = dr["MATERIEL_GROUP_MEMO"].ToString();
sheet[26, iRow].Value = dr["MADE_DATE"].ToString();
sheet[27, iRow].Value = dr["AUTHOR"].ToString();
sheet[28, iRow].Value = dr["MATERIAL"].ToString();
sheet[29, iRow].Value = dr["SIZES"].ToString();
sheet[30, iRow].Value = dr["ERR"].ToString();
//sheet[31, iRow].Value = dr["USER_NAME"].ToString();
//sheet[32, iRow].Value = dr["IMPORT_DATE"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(0).Width = 70;
sheet.Columns(1).Width = 100;
sheet.Columns(2).Width = 50;
sheet.Columns(3).Width = 70;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "品號/ 期別";
sheet[1, iRow].Value = "品名";
sheet[2, iRow].Value = "路線";
sheet[3, iRow].Value = "門市進貨數量";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["ITEM"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["ITEM_NAME"].ToString();
sheet[2, iRow].Value = dtTemp.Rows[i]["ROUTE"].ToString();
sheet[3, iRow].Value = decimal.Parse(dtTemp.Rows[i]["ST_ACCEPT_QTY"].ToString());
iRow++;
}
}
private void AddSheetDataTrace_SKU(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 90;
sheet.Columns(1).Width = 90;
sheet.Columns(2).Width = 90;
sheet.Columns(3).Width = 90;
sheet.Columns(4).Width = 90;
sheet.Columns(5).Width = 90;
sheet.Columns(6).Width = 90;
sheet.Columns(7).Width = 90;
sheet.Columns(8).Width = 90;
sheet.Columns(9).Width = 90;
sheet.Columns(10).Width = 90;
sheet.Columns(11).Width = 90;
sheet.Columns(12).Width = 90;
sheet.Columns(13).Width = 90;
sheet.Columns(14).Width = 90;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "供應商編號";
sheet[1, iRow].Value = "供應商名稱";
sheet[2, iRow].Value = "結帳年月";
sheet[3, iRow].Value = "補收年月";
sheet[4, iRow].Value = "補收(退)金額";
sheet[5, iRow].Value = "新合約小計";
sheet[6, iRow].Value = "原合約小計";
sheet[7, iRow].Value = "獎勵金(新)";
sheet[8, iRow].Value = "物流費(新)";
sheet[9, iRow].Value = "折扣補貼(新)";
sheet[10, iRow].Value = "配送費(新)";
sheet[11, iRow].Value = "獎勵金";
sheet[12, iRow].Value = "物流費";
sheet[13, iRow].Value = "折扣補貼";
sheet[14, iRow].Value = "配送費";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["VENDOR"].ToString();
sheet[1, iRow].Value = dr["VENDOR_NAME"].ToString();
sheet[2, iRow].Value = dr["CHECK_MONTH"].ToString();
sheet[3, iRow].Value = dr["TRACE_MONTH"].ToString();
sheet[4, iRow].Value = dr["TRACE_AMT"];
sheet[5, iRow].Value = dr["SUM_AMT_TRACE"];
sheet[6, iRow].Value = dr["SUM_AMT"];
sheet[7, iRow].Value = dr["NOTES_1_TRACE"];
sheet[8, iRow].Value = dr["NOTES_2_TRACE"];
sheet[9, iRow].Value = dr["NOTES_3_TRACE"];
sheet[10, iRow].Value = dr["SUBSIDY_TRACE"];
sheet[11, iRow].Value = dr["NOTES_1"];
sheet[12, iRow].Value = dr["NOTES_2"];
sheet[13, iRow].Value = dr["NOTES_3"];
sheet[14, iRow].Value = dr["SUBSIDY"];
iRow++;
}
}
private int AddSheetData0(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 60;
sheet.Columns(1).Width = 60;
sheet.Columns(2).Width = 60;
sheet.Columns(3).Width = 60;
sheet.Columns(4).Width = 60;
sheet.Columns(5).Width = 60;
sheet.Columns(6).Width = 60;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 60;
sheet.Columns(9).Width = 60;
sheet.Columns(10).Width = 60;
sheet.Columns(11).Width = 60;
sheet.Columns(12).Width = 60;
sheet.Columns(13).Width = 60;
sheet.Columns(14).Width = 60;
sheet.Columns(15).Width = 60;
sheet.Columns(16).Width = 60;
sheet.Columns(17).Width = 60;
sheet.Columns(18).Width = 60;
sheet.Columns(19).Width = 60;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "進貨單號";
sheet[1, iRow].Value = "進貨日期";
sheet[2, iRow].Value = "結帳年月";
sheet[3, iRow].Value = "門市代碼";
sheet[4, iRow].Value = "門市名稱";
sheet[5, iRow].Value = "數量合計";
sheet[6, iRow].Value = "未稅成本合計";
sheet[7, iRow].Value = "成本稅額合計";
sheet[8, iRow].Value = "牌價稅額合計";
sheet[9, iRow].Value = "未稅零售價合計";
sheet[10, iRow].Value = "零售稅額合計";
sheet[11, iRow].Value = "牌價合計";
sheet[12, iRow].Value = "檔期編號";
sheet[13, iRow].Value = "品號";
sheet[14, iRow].Value = "品名";
sheet[15, iRow].Value = "期別";
sheet[16, iRow].Value = "數量";
sheet[17, iRow].Value = "成本價";
sheet[18, iRow].Value = "零售價";
sheet[19, iRow].Value = "牌價";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["ST_ACCEPT_NO"].ToString();
sheet[1, iRow].Value = dr["ST_ACCEPT_DATE"].ToString();
sheet[2, iRow].Value = dr["AC_BALANCE_DATE_S"].ToString();
sheet[3, iRow].Value = dr["STORE"].ToString();
sheet[4, iRow].Value = dr["STORE_NAME"].ToString();
sheet[5, iRow].Value = dr["QTY_SUM"].ToString();
sheet[6, iRow].Value = dr["COST_SUM"].ToString();
sheet[7, iRow].Value = dr["COST_TAX_SUM"].ToString();
sheet[8, iRow].Value = dr["COST_DIS_TAX_SUM"].ToString();
sheet[9, iRow].Value = dr["PRICE_SUM"].ToString();
sheet[10, iRow].Value = dr["PRICE_TAX_SUM"].ToString();
sheet[11, iRow].Value = dr["COST_DIS_SUM"].ToString();
sheet[12, iRow].Value = dr["PROMOTE_ID"].ToString();
sheet[13, iRow].Value = dr["ITEM"].ToString();
sheet[14, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[15, iRow].Value = dr["PERIOD"].ToString();
sheet[16, iRow].Value = dr["ST_ACCEPT_QTY"].ToString();
sheet[17, iRow].Value = dr["COST"].ToString();
sheet[18, iRow].Value = dr["PRICE"].ToString();
sheet[19, iRow].Value = dr["COST_DIS"].ToString();
iRow++;
}
return iRow;
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 90;
sheet.Columns(1).Width = 90;
sheet.Columns(2).Width = 90;
sheet.Columns(3).Width = 90;
sheet.Columns(4).Width = 90;
sheet.Columns(5).Width = 90;
sheet.Columns(6).Width = 90;
sheet.Columns(7).Width = 90;
sheet.Columns(8).Width = 90;
sheet.Columns(9).Width = 90;
sheet.Columns(10).Width = 90;
sheet.Columns(11).Width = 90;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "結帳年月";
sheet[1, iRow].Value = "補收年月";
sheet[2, iRow].Value = "供應商編號";
sheet[3, iRow].Value = "一般補收月結";
sheet[4, iRow].Value = "一般補收月結稅額";
sheet[5, iRow].Value = "折扣補貼(應稅)";
sheet[6, iRow].Value = "折扣補貼(應稅)稅額";
sheet[7, iRow].Value = "折扣補貼(免稅)";
sheet[8, iRow].Value = "離島配送費";
sheet[9, iRow].Value = "離島配送費稅額";
sheet[10, iRow].Value = "油價補貼";
sheet[11, iRow].Value = "油價補貼稅額";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["CHECK_MONTH"].ToString();
sheet[1, iRow].Value = dr["TRACE_MONTH"].ToString();
sheet[2, iRow].Value = dr["VENDOR"].ToString();
sheet[3, iRow].Value = dr["CONTRACT_AMT"];
sheet[4, iRow].Value = dr["CONTRACT_AMT_TAX"];
sheet[5, iRow].Value = dr["SUBSIDY_TAX_AMT"];
sheet[6, iRow].Value = dr["SUBSIDY_TAX_AMT_TAX"];
sheet[7, iRow].Value = dr["SUBSIDY_UTAX_AMT"];
sheet[8, iRow].Value = dr["ISLAND_AMT"];
sheet[9, iRow].Value = dr["ISLAND_AMT_TAX"];
sheet[10, iRow].Value = dr["OIL_AMT"];
sheet[11, iRow].Value = dr["OIL_AMT_TAX"];
iRow++;
}
}
private void AddSheetData1(ref Worksheet sheet, string strSheetName, DataTable dtTab)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 55;
sheet.Columns(1).Width = 190;
sheet.Columns(2).Width = 100;
sheet.Columns(3).Width = 50;
sheet.Columns(4).Width = 60;
sheet.Columns(5).Width = 65;
sheet.Columns(6).Width = 50;
sheet.Columns(7).Width = 450;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "TABLE 種類";
sheet[1, iRow].Value = "Table 英文名稱";
sheet[2, iRow].Value = "欄位名稱";
sheet[3, iRow].Value = "是否為PK";
sheet[4, iRow].Value = "可否為NULL";
sheet[5, iRow].Value = "型態";
sheet[6, iRow].Value = "欄位長度";
sheet[7, iRow].Value = "欄位說明";
iRow++;
int iTab = 0;
DataTable dtData = new DataTable();
DataTable dtTemp = new DataTable();
IFM06_BCO BCO = new IFM06_BCO(ConntionDB);
//有選擇模組代碼
foreach (DataRow dr in dtTab.Rows)
{
//V_TABTYPE varchar2,
//V_TABLE_NAME varchar2,
ParameterList.Clear();
ParameterList.Add(dr["TABLE_TYPE"].ToString().Trim());
ParameterList.Add(dr["TABLE_NAME"].ToString().Trim());
dtTemp = BCO.QuerytTabColumn(ParameterList);
if (iTab == 0)
dtData = dtTemp;
else
dtData.Merge(dtTemp);
iTab++;
}
foreach (DataRow dr in dtData.Rows)
{
sheet[0, iRow].Value = dr["TABLE_TYPE"].ToString();
sheet[1, iRow].Value = dr["TABLE_NAME"].ToString();
sheet[2, iRow].Value = dr["COLUMN_NAME"].ToString();
sheet[3, iRow].Value = dr["CONSTRAINT_TYPE"].ToString();
sheet[4, iRow].Value = dr["NULLABLE"].ToString();
sheet[5, iRow].Value = dr["DATA_TYPE"].ToString();
sheet[6, iRow].Value = dr["LENGTH"].ToString();
sheet[7, iRow].Value = dr["COMMENTS"].ToString();
iRow++;
}
}
private DataTable AddSheetData0(ref Worksheet sheet, string strSheetName)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 55;
sheet.Columns(1).Width = 55;
sheet.Columns(2).Width = 190;
sheet.Columns(3).Width = 450;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "TABLE 種類";
sheet[1, iRow].Value = "模組代碼";
sheet[2, iRow].Value = "Table 英文名稱";
sheet[3, iRow].Value = "Table 中文名稱";
iRow++;
int iSel = 0;
DataTable dtData = new DataTable();
DataTable dtTemp = new DataTable();
IFM06_BCO BCO = new IFM06_BCO(ConntionDB);
//有選擇模組代碼
for (int i = 0; i < cblModule.Items.Count; i++)
{
if (cblModule.Items[i].Selected == true)
{
//V_TABTYPE varchar2,
//V_MODULE varchar2,
//V_TABLE_NAME varchar2,
//V_COMMENTS varchar2,
ParameterList.Clear();
ParameterList.Add(ddlTABTYPE.Text.Trim());
ParameterList.Add(cblModule.Items[i].Value.ToString());
ParameterList.Add("%" + txtTABLE_NAME.Text.Trim() + "%");
ParameterList.Add("%" + txtCOMMENTS.Text.Trim() + "%");
dtTemp = BCO.QueryModuleTab(ParameterList);
if (iSel == 0)
dtData = dtTemp;
else
dtData.Merge(dtTemp);
iSel++;
}
}
//完全沒選擇模組代碼
if (iSel == 0)
{
ParameterList.Clear();
ParameterList.Add(ddlTABTYPE.Text.Trim());
ParameterList.Add(DBNull.Value);
ParameterList.Add("%" + txtTABLE_NAME.Text.Trim() + "%");
ParameterList.Add("%" + txtCOMMENTS.Text.Trim() + "%");
dtData = BCO.QueryModuleTab(ParameterList);
}
dtData.DefaultView.Sort = "TABLE_TYPE,CODE";
dtData = dtData.DefaultView.ToTable();
foreach (DataRow dr in dtData.Rows)
{
sheet[0, iRow].Value = dr["TABLE_TYPE"].ToString();
sheet[1, iRow].Value = dr["CODE"].ToString();
sheet[2, iRow].Value = dr["TABLE_NAME"].ToString();
sheet[3, iRow].Value = dr["COMMENTS"].ToString();
iRow++;
}
return dtData;
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 65;
sheet.Columns(1).Width = 250;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 0;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "廠商統編";
sheet[1, iRow].Value = "廠商名稱";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["VENDOR"].ToString();
sheet[1, iRow].Value = dr["VENDOR_NAME"].ToString();
iRow++;
}
}
private int AddSheetData0(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 60;
sheet.Columns(1).Width = 60;
sheet.Columns(2).Width = 60;
sheet.Columns(3).Width = 60;
sheet.Columns(4).Width = 60;
sheet.Columns(5).Width = 60;
sheet.Columns(6).Width = 60;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 60;
sheet.Columns(9).Width = 60;
sheet.Columns(10).Width = 60;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "進貨單號";
sheet[1, iRow].Value = "進貨日期";
sheet[2, iRow].Value = "通路";
sheet[3, iRow].Value = "店號";
sheet[4, iRow].Value = "店名";
sheet[5, iRow].Value = "品號";
sheet[6, iRow].Value = "品名";
sheet[7, iRow].Value = "期別";
sheet[8, iRow].Value = "數量";
sheet[9, iRow].Value = "出庫儲位";
sheet[10, iRow].Value = "出庫儲區";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["ST_ACCEPT_NO"].ToString();
sheet[1, iRow].Value = dr["ST_ACCEPT_DATE"].ToString();
sheet[2, iRow].Value = dr["CHAN_NO"].ToString();
sheet[3, iRow].Value = dr["STORE"].ToString();
sheet[4, iRow].Value = dr["STORE_NAME"].ToString();
sheet[5, iRow].Value = dr["ITEM"].ToString();
sheet[6, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[7, iRow].Value = dr["PERIOD"].ToString();
sheet[8, iRow].Value = dr["ST_ACCEPT_QTY"].ToString();
sheet[9, iRow].Value = dr["LOCATE_SECTION"].ToString();
sheet[10, iRow].Value = dr["LOCATE_NO"].ToString();
iRow++;
}
return iRow;
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//名次(如果實銷數相同時，依品號 降冪+期別降冪 排序)
//商品群
//大分類
//中分類
//品號
//品名
//期別
//實銷數
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 30;
sheet.Columns(1).Width = 40;
sheet.Columns(2).Width = 70;
sheet.Columns(3).Width = 40;
sheet.Columns(4).Width = 70;
sheet.Columns(5).Width = 40;
sheet.Columns(6).Width = 70;
sheet.Columns(7).Width = 40;
sheet.Columns(8).Width = 120;
sheet.Columns(9).Width = 40;
sheet.Columns(10).Width = 40;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 1;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "名次";
sheet[1, iRow].Value = "商品群";
sheet[2, iRow].Value = "商品群名稱";
sheet[3, iRow].Value = "大分類";
sheet[4, iRow].Value = "大分類名稱";
sheet[5, iRow].Value = "中分類";
sheet[6, iRow].Value = "中分類名稱";
sheet[7, iRow].Value = "品號";
sheet[8, iRow].Value = "品名";
sheet[9, iRow].Value = "期別";
sheet[10, iRow].Value = "實銷數";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["RANK_NO"].ToString();
sheet[1, iRow].Value = dr["ROOT_NO"].ToString();
sheet[2, iRow].Value = dr["ROOT_NAME"].ToString();
sheet[3, iRow].Value = dr["PMA"].ToString();
sheet[4, iRow].Value = dr["PMA_NAME"].ToString();
sheet[5, iRow].Value = dr["CATEGORY"].ToString();
sheet[6, iRow].Value = dr["CATEGORY_NAME"].ToString();
sheet[7, iRow].Value = dr["ITEM"].ToString();
sheet[8, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[9, iRow].Value = dr["PERIOD"].ToString();
sheet[10, iRow].Value = dr["SALE_QTY"].ToString();
iRow++;
}
}
private void AddSheetData1(ref Worksheet sheet, string strSheetName ,DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 27;
sheet.Columns(1).Width = 57;
sheet.Columns(2).Width = 126;
sheet.Columns(3).Width = 48;
sheet.Columns(4).Width = 69;
sheet.Columns(5).Width = 107;
sheet.Columns(6).Width = 147;
sheet.Columns(7).Width = 72;
sheet.Columns(8).Width = 75;
sheet.Columns(9).Width = 82;
int iRow = 0;
//即有品項
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 0; //固定左邊欄位
sheet.FreezeTopRows = 1; //固定第一欄
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "項次";
sheet[1, iRow].Value = "品號";
sheet[2, iRow].Value = "品名";
sheet[3, iRow].Value = "期別";
sheet[4, iRow].Value = "異動序號";
sheet[5, iRow].Value = "異動日期時間";
sheet[6, iRow].Value = "異動原因";
sheet[7, iRow].Value = "異動欄位";
sheet[8, iRow].Value = "異動前值";
sheet[9, iRow].Value = "異動後值";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["ROWNUM"].ToString();
sheet[1, iRow].Value = dr["ITEM"].ToString();
sheet[2, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[3, iRow].Value = dr["PERIOD"].ToString();
sheet[4, iRow].Value = dr["EDIT_NO"].ToString();
sheet[5, iRow].Value = dr["CREATEDATE_NAME"].ToString();
sheet[6, iRow].Value = dr["EDIT_REASON"].ToString();
sheet[7, iRow].Value = dr["EDIT_FIELD"].ToString();
sheet[8, iRow].Value = dr["EDIT_VALUE_OLD"].ToString();
sheet[9, iRow].Value = dr["EDIT_VALUE_NEW"].ToString();
iRow++;
}
}
private int AddSheetData0(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 40;
sheet.Columns(1).Width = 60;
sheet.Columns(2).Width = 120;
sheet.Columns(3).Width = 45;
sheet.Columns(4).Width = 100;
sheet.Columns(5).Width = 90;
sheet.Columns(6).Width = 45;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 45;
sheet.Columns(9).Width = 45;
sheet.Columns(10).Width = 45;
sheet.Columns(11).Width = 45;
sheet.Columns(12).Width = 45;
sheet.Columns(13).Width = 45;
sheet.Columns(14).Width = 45;
sheet.Columns(15).Width = 60;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "儲位";
sheet[1, iRow].Value = "供應商統編";
sheet[2, iRow].Value = "供應商名稱";
sheet[3, iRow].Value = "品號";
sheet[4, iRow].Value = "品名";
sheet[5, iRow].Value = "條碼";
sheet[6, iRow].Value = "期別";
sheet[7, iRow].Value = "現況迴轉天數";
sheet[8, iRow].Value = "期初數量";
sheet[9, iRow].Value = "進倉數";
sheet[10, iRow].Value = "出倉數";
sheet[11, iRow].Value = "成本";
sheet[12, iRow].Value = "庫存數";
sheet[13, iRow].Value = "成本合計";
sheet[14, iRow].Value = "配本保留";
sheet[15, iRow].Value = "目標安全庫存數";
iRow++;
//Assembly_Parameter();
//DataTable dtData = bco.QueryStockStatement(ParameterList);
//foreach (DataRow dr in dtData.Rows)
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["LOCATE_SECTION"].ToString();
sheet[1, iRow].Value = dr["Vendor"].ToString();
sheet[2, iRow].Value = dr["VENDOR_NAME"].ToString();
sheet[3, iRow].Value = dr["ITEM"].ToString();
sheet[4, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[5, iRow].Value = dr["BARCODE"].ToString();
sheet[6, iRow].Value = dr["PERIOD"].ToString();
sheet[7, iRow].Value = dr["ROTARY_NOW"].ToString();
sheet[8, iRow].Value = dr["INIT_QTY"].ToString();
sheet[9, iRow].Value = dr["IN_QTY"].ToString();
sheet[10, iRow].Value = dr["OUT_QTY"].ToString();
sheet[11, iRow].Value = dr["COST"].ToString();
sheet[12, iRow].Value = dr["ONHD_QTY"].ToString();
sheet[13, iRow].Value = dr["COST_SUM"].ToString();
sheet[14, iRow].Value = dr["RESERVE_QTY"].ToString();
sheet[15, iRow].Value = dr["SAFE_STOCK"].ToString();
iRow++;
}
return iRow;
}
private void AddSheetData2(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 59;
sheet.Columns(1).Width = 51;
sheet.Columns(2).Width = 51;
sheet.Columns(3).Width = 75;
sheet.Columns(4).Width = 69;
sheet.Columns(5).Width = 48;
sheet.Columns(6).Width = 50;
sheet.Columns(7).Width = 71;
sheet.Columns(8).Width = 72;
sheet.Columns(9).Width = 33;
sheet.Columns(10).Width = 57;
sheet.Columns(11).Width = 87;
sheet.Columns(12).Width = 76;
sheet.Columns(13).Width = 57;
sheet.Columns(14).Width = 59;
sheet.Columns(15).Width = 73;
sheet.Columns(16).Width = 54;
sheet.Columns(17).Width = 45;
int iRow = 0;
//即有品項
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 0; //固定左邊欄位
sheet.FreezeTopRows = 1; //固定第一欄
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "異動模式";
sheet[1, iRow].Value = "品號";
sheet[2, iRow].Value = "期別";
sheet[3, iRow].Value = "異動序號";
sheet[4, iRow].Value = "補貼類型";
sheet[5, iRow].Value = "進貨元";
sheet[6, iRow].Value = "退貨元";
sheet[7, iRow].Value = "進貨百分比";
sheet[8, iRow].Value = "退貨百分比";
sheet[9, iRow].Value = "費率";
sheet[10, iRow].Value = "收取方式";
sheet[11, iRow].Value = "收取規則";
sheet[12, iRow].Value = "備註";
sheet[13, iRow].Value = "最低金額";
sheet[14, iRow].Value = "計算店群";
sheet[15, iRow].Value = "補貼原則";
sheet[16, iRow].Value = "月結計算";
sheet[17, iRow].Value = "配送費";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["mode_name"].ToString();
sheet[1, iRow].Value = dr["item"].ToString();
sheet[2, iRow].Value = dr["period"].ToString();
sheet[3, iRow].Value = dr["edit_no"].ToString();
sheet[4, iRow].Value = dr["subsidy_type_name"].ToString();
sheet[5, iRow].Value = dr["accept_amt"].ToString();
sheet[6, iRow].Value = dr["return_amt"].ToString();
sheet[7, iRow].Value = dr["accept_rate"].ToString();
sheet[8, iRow].Value = dr["return_rate"].ToString();
sheet[9, iRow].Value = dr["discount_rate"].ToString();
sheet[10, iRow].Value = dr["pay_type_name"].ToString();
sheet[11, iRow].Value = dr["target_rule_name"].ToString();
sheet[12, iRow].Value = dr["notes"].ToString();
sheet[13, iRow].Value = dr["min_amt"].ToString();
sheet[14, iRow].Value = dr["in_store_flag_name"].ToString();
sheet[15, iRow].Value = dr["period_pay_type_name"].ToString();
sheet[16, iRow].Value = dr["monthcount_yn_name"].ToString();
sheet[17, iRow].Value = dr["distribute_yn_name"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(0).Width = 50;
sheet.Columns(1).Width = 70;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
//sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "狀態";
sheet[1, iRow].Value = "折讓單號";
sheet[2, iRow].Value = "異動折讓日期";
sheet[3, iRow].Value = "結帳統編";
sheet[4, iRow].Value = "統一編號";
sheet[5, iRow].Value = "店號";
sheet[6, iRow].Value = "店名";
sheet[7, iRow].Value = "銷折金額(不含稅)";
sheet[8, iRow].Value = "稅別";
sheet[9, iRow].Value = "營業稅額";
sheet[10, iRow].Value = "銷折合計";
sheet[11, iRow].Value = "帳務人員";
sheet[12, iRow].Value = "申報期別";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["PROOF_FLG_NAME"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["DISC_NO"].ToString();
sheet[2, iRow].Value = Convert.ToDateTime(dtTemp.Rows[i]["CHG_DISC_DATE"].ToString()).ToShortDateString();
sheet[3, iRow].Value = dtTemp.Rows[i]["PAY_RFNO"].ToString();
sheet[4, iRow].Value = dtTemp.Rows[i]["Rfno"].ToString();
sheet[5, iRow].Value = dtTemp.Rows[i]["STORE_ID"].ToString();
sheet[6, iRow].Value = dtTemp.Rows[i]["STORE_NAME"].ToString();
sheet[7, iRow].Value = decimal.Parse(dtTemp.Rows[i]["DISC_UAMT"].ToString());
sheet[8, iRow].Value = dtTemp.Rows[i]["TAX_TYPE_NAME"].ToString();
sheet[9, iRow].Value = decimal.Parse(dtTemp.Rows[i]["DISC_TAX"].ToString());
sheet[10, iRow].Value = decimal.Parse(dtTemp.Rows[i]["DISC_AMT"].ToString());
sheet[11, iRow].Value = dtTemp.Rows[i]["AC_UID"].ToString();
sheet[12, iRow].Value = dtTemp.Rows[i]["PERIOD_MONTH"].ToString();
iRow++;
}
}
private void AddSheetData3(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 59;
sheet.Columns(1).Width = 51;
sheet.Columns(2).Width = 51;
sheet.Columns(3).Width = 77;
sheet.Columns(4).Width = 78;
sheet.Columns(5).Width = 70;
sheet.Columns(6).Width = 106;
sheet.Columns(7).Width = 96;
sheet.Columns(8).Width = 106;
sheet.Columns(9).Width = 96;
int iRow = 0;
//即有品項
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 0; //固定左邊欄位
sheet.FreezeTopRows = 1; //固定第一欄
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "異動模式";
sheet[1, iRow].Value = "品號";
sheet[2, iRow].Value = "期別";
sheet[3, iRow].Value = "補貼類型";
sheet[4, iRow].Value = "店群";
sheet[5, iRow].Value = "門市";
sheet[6, iRow].Value = "建立人員";
sheet[7, iRow].Value = "建立時間";
sheet[8, iRow].Value = "異動人員";
sheet[9, iRow].Value = "異動時間";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["mode_name"].ToString();
sheet[1, iRow].Value = dr["item"].ToString();
sheet[2, iRow].Value = dr["period"].ToString();
sheet[3, iRow].Value = dr["subsidy_type_name"].ToString();
sheet[4, iRow].Value = dr["STORE_GROUP"].ToString();
sheet[5, iRow].Value = dr["STORE"].ToString();
sheet[6, iRow].Value = dr["CREATEUID_NAME"].ToString();
sheet[7, iRow].Value = dr["CREATEDATE"].ToString();
sheet[8, iRow].Value = dr["UPDATEUID_NAME"].ToString();
sheet[9, iRow].Value = dr["UPDATEDATE"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 35;
sheet.Columns(1).Width = 40;
sheet.Columns(2).Width = 40;
sheet.Columns(3).Width = 35;
sheet.Columns(4).Width = 60;
sheet.Columns(5).Width = 60;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "站別";
sheet[1, iRow].Value = "合流站";
sheet[2, iRow].Value = "中置架";
sheet[3, iRow].Value = "儲格";
sheet[4, iRow].Value = "儲格序號";
sheet[5, iRow].Value = "分派序號";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["STATION_NO"].ToString();
sheet[1, iRow].Value = dr["INTERFLOW_LOCATE"].ToString();
sheet[2, iRow].Value = dr["SHELF"].ToString();
sheet[3, iRow].Value = dr["LOCATE_NO"].ToString();
sheet[4, iRow].Value = dr["STATION_ORDER"];
sheet[5, iRow].Value = dr["PICK_ORDER"];
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
//欄位寬度特別設定的
sheet.Columns(0).Width = 30;
sheet.Columns(1).Width = 70;
sheet.Columns(2).Width = 70;
sheet.Columns(3).Width = 70;
sheet.Columns(4).Width = 100;
int iRow = 0;
sheet.Name = strSheetName;
//sheet.FreezeLeftColumns = 3;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "代碼";
sheet[1, iRow].Value = "代碼名稱";
sheet[2, iRow].Value = "代碼說明";
sheet[3, iRow].Value = "明細檔代碼";
sheet[4, iRow].Value = "明細檔代碼說明";
iRow++;
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
sheet[0, iRow].Value = dtTemp.Rows[i]["CODE"].ToString();
sheet[1, iRow].Value = dtTemp.Rows[i]["NAME"].ToString();
sheet[2, iRow].Value = dtTemp.Rows[i]["M_EXPLAIN"].ToString();
sheet[3, iRow].Value = dtTemp.Rows[i]["SCODE"].ToString();
sheet[4, iRow].Value = dtTemp.Rows[i]["D_EXPLAIN"].ToString();
iRow++;
}
}
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 12;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 18;
sheet.Columns(1).Width = 60;
sheet.Columns(2).Width = 60;
sheet.Columns(3).Width = 90;
sheet.Columns(4).Width = 80;
sheet.Columns(5).Width = 60;
sheet.Columns(6).Width = 60;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 100;
sheet.Columns(9).Width = 100;
sheet.Columns(10).Width = 60;
sheet.Columns(11).Width = 100;
sheet.Columns(12).Width = 80;
sheet.Columns(13).Width = 80;
sheet.Columns(14).Width = 80;
sheet.Columns(15).Width = 80;
sheet.Columns(16).Width = 80;
sheet.Columns(17).Width = 80;
sheet.Columns(18).Width = 80;
sheet.Columns(19).Width = 80;
sheet.Columns(20).Width = 80;
sheet.Columns(21).Width = 80;
sheet.Columns(22).Width = 80;
sheet.Columns(23).Width = 80;
sheet.Columns(24).Width = 80;
sheet.Columns(25).Width = 80;
sheet.Columns(26).Width = 80;//20110301Rika 新增
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "序號";
sheet[1, iRow].Value = "沖帳日期";//20110301 Rika修改
sheet[2, iRow].Value = "結帳年月";
sheet[3, iRow].Value = "憑證日期(一代)";
sheet[4, iRow].Value = "種類(一代)";
sheet[5, iRow].Value = "單號(一代)";
sheet[6, iRow].Value = "來源別";
sheet[7, iRow].Value = "日期";
sheet[8, iRow].Value = "銷帳代號";
sheet[9, iRow].Value = "單號(次代)";
sheet[10, iRow].Value = "店號";
sheet[11, iRow].Value = "店名";
sheet[12, iRow].Value = "結帳統編";
sheet[13, iRow].Value = "統一編號";
sheet[14, iRow].Value = "繳款金額";
sheet[15, iRow].Value = "手續費/郵資";
#region 20100609秀玲新增規則
sheet[16, iRow].Value = "尾差";
sheet[17, iRow].Value = "其他差異";
sheet[18, iRow].Value = "匯兌損益";
#endregion
sheet[19, iRow].Value = "沖帳總額";
sheet[20, iRow].Value = "群分類";
sheet[21, iRow].Value = "稅別";
sheet[22, iRow].Value = "備註";
sheet[23, iRow].Value = "帳務人員";//20110301 Rika 新增
sheet[24, iRow].Value = "建立人員";
sheet[25, iRow].Value = "建立日期";
sheet[26, iRow].Value = "利潤中心";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = iRow.ToString();
sheet[1, iRow].Value = dr["WASH_DATE"].ToString();
sheet[2, iRow].Value = dr["CLOSE_MONTH"].ToString();
sheet[3, iRow].Value = "";
sheet[4, iRow].Value = "";
sheet[5, iRow].Value = "";
sheet[6, iRow].Value = dr["SOURCE_TYPE"].ToString();
sheet[7, iRow].Value = dr["SDATE"].ToString();
sheet[8, iRow].Value = dr["PAY_ACCOUNT"].ToString();
sheet[9, iRow].Value = dr["Second_NO"].ToString();
sheet[10, iRow].Value = dr["STORE_ID"].ToString();
sheet[11, iRow].Value = dr["STORE_NAME"].ToString();
sheet[12, iRow].Value = dr["PAY_RFNO"].ToString();
sheet[13, iRow].Value = dr["RFNO"].ToString();
sheet[14, iRow].Value = dr["WASH_AMT"].ToString();
sheet[15, iRow].Value = dr["WASH_CHAGE"].ToString();
#region 20100609秀玲新增規則
sheet[16, iRow].Value = dr["DIFF_AMT"].ToString();
sheet[17, iRow].Value = dr["OTHER_DIFF"].ToString();
sheet[18, iRow].Value = dr["REMIT_DIFF"].ToString();
#endregion
sheet[19, iRow].Value = dr["WASH_AMT_TOT"].ToString();
sheet[20, iRow].Value = dr["ROOT_NO"].ToString();
sheet[21, iRow].Value = dr["TAX_TYPE"].ToString();
sheet[22, iRow].Value = dr["MEMO"].ToString();
sheet[23, iRow].Value = dr["AC_UID"].ToString(); ;//20110301 Rika 新增
sheet[24, iRow].Value = dr["CREATEUID"].ToString();
sheet[25, iRow].Value = dr["CREATEDATE"].ToString();
sheet[26, iRow].Value = dr["PROFIT_NAME"].ToString();
iRow++;
}
}
private int AddSheetData0(ref Worksheet sheet, string strSheetName, DataTable dt)
{
//設定Worksheet各column 寬度
sheet.Font.Size = 10;
sheet.Font.Name = "新細明體";
sheet.Columns(0).Width = 60;
sheet.Columns(1).Width = 120;
sheet.Columns(2).Width = 45;
sheet.Columns(3).Width = 100;
sheet.Columns(4).Width = 90;
sheet.Columns(5).Width = 45;
sheet.Columns(6).Width = 90;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 60;
sheet.Columns(9).Width = 60;
sheet.Columns(10).Width = 60;
sheet.Columns(11).Width = 60;
sheet.Columns(12).Width = 60;
sheet.Columns(13).Width = 60;
sheet.Columns(14).Width = 60;
sheet.Columns(15).Width = 60;
sheet.Columns(16).Width = 60;
sheet.Columns(17).Width = 60;
sheet.Columns(18).Width = 90;
sheet.Columns(19).Width = 60;
int iRow = 0;
sheet.Name = strSheetName;
sheet.FreezeLeftColumns = 0;
sheet.FreezeTopRows = 1;
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet[0, iRow].Value = "供應商統編";
sheet[1, iRow].Value = "供應商名稱";
sheet[2, iRow].Value = "品號";
sheet[3, iRow].Value = "品名";
sheet[4, iRow].Value = "條碼";
sheet[5, iRow].Value = "期別";
sheet[6, iRow].Value = "目標迴轉天數";
sheet[7, iRow].Value = "期初數量";
sheet[8, iRow].Value = "進倉數";
sheet[9, iRow].Value = "出倉數";
sheet[10, iRow].Value = "成本";
sheet[11, iRow].Value = "A儲區";
sheet[12, iRow].Value = "B儲區";
sheet[13, iRow].Value = "C儲區";
sheet[14, iRow].Value = "D儲區";
sheet[15, iRow].Value = "總庫存數";
sheet[16, iRow].Value = "成本合計";
sheet[17, iRow].Value = "配本保留";
sheet[18, iRow].Value = "目標安全庫存數";
sheet[19, iRow].Value = "應廠退數";
iRow++;
foreach (DataRow dr in dt.Rows)
{
sheet[0, iRow].Value = dr["Vendor"].ToString();
sheet[1, iRow].Value = dr["VENDOR_NAME"].ToString();
sheet[2, iRow].Value = dr["ITEM"].ToString();
sheet[3, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[4, iRow].Value = dr["BARCODE"].ToString();
sheet[5, iRow].Value = dr["PERIOD"].ToString();
sheet[6, iRow].Value = dr["ROTARY_TARGET"].ToString();
sheet[7, iRow].Value = dr["INIT_QTY"].ToString();
sheet[8, iRow].Value = dr["IN_QTY"].ToString();
sheet[9, iRow].Value = dr["OUT_QTY"].ToString();
sheet[10, iRow].Value = dr["COST"].ToString();
sheet[11, iRow].Value = dr["A_STOCK"].ToString();
sheet[12, iRow].Value = dr["B_STOCK"].ToString();
sheet[13, iRow].Value = dr["C_STOCK"].ToString();
sheet[14, iRow].Value = dr["D_STOCK"].ToString();
sheet[15, iRow].Value = dr["TOTAL_STOCK"].ToString();
sheet[16, iRow].Value = dr["COST_SUM"].ToString();
sheet[17, iRow].Value = dr["RESERVE_QTY"].ToString();
sheet[18, iRow].Value = dr["SAFE_STOCK"].ToString();
sheet[19, iRow].Value = dr["RETURN_QTY"].ToString();
iRow++;
}
return iRow;
}
