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; }