internal Column(Worksheet parent) { if (parent == null) throw new ArgumentNullException("parent"); ParentBook = parent.ParentBook; }
private static Cell GetCell(Worksheet ws, Cell cell, bool absolute, int row, int col) { if (absolute) { col = cell.CellIndex + col; row = cell.ParentRow.RowIndex + row; } return ws[col, row]; }
private void SetSheetNameByIndex(Worksheet ws, int index) { index++; string name = "Sheet" + index.ToString(CultureInfo.InvariantCulture); while (GetSheetIDByName(name) != -1) name = "Sheet" + (++index).ToString(CultureInfo.InvariantCulture); ws.Name = name; }
internal NamedRange(Range range, string name, Worksheet ws) { if (range == null) throw new ArgumentNullException("range"); if (name.IsNullOrEmpty()) throw new ArgumentNullException("name"); Worksheet = ws; Range = range; Name = name; }
private void buttonNameWorksheet_Click(object sender, EventArgs e) { sheet = workBook[sheetCount]; sheet.Name = textBoxWorkSheetName.Text; sheet.Font.Name = "Calibri"; sheet.Font.Size = 11; //make this thing and all associated stuff go away textBoxWorkSheetName.Enabled = false; textBoxWorkSheetName.Visible = false; buttonNameWorksheet.Enabled = false; buttonNameWorksheet.Visible = false; //next phase appear buttonStartInventory.Enabled = true; buttonStartInventory.Visible = true; }
internal void ExportNamedRanges(XmlWriter writer, Worksheet ws) { bool started = false; if (ws != null && ws.PrintOptions.PrintTitles) { started = true; writer.WriteStartElement("Names"); writer.WriteStartElement("NamedRange"); writer.WriteAttributeString("ss", "Name", null, "Print_Titles"); writer.WriteAttributeString("ss", "RefersTo", null, ws.PrintOptions.GetPrintTitleRange(ws.Name)); writer.WriteEndElement(); } foreach (NamedRange nr in NamedRanges) { if (nr.Worksheet == ws) { if (!started) { started = true; writer.WriteStartElement("Names"); } writer.WriteStartElement("NamedRange"); writer.WriteAttributeString("ss", "Name", null, nr.Name); writer.WriteAttributeString("ss", "RefersTo", null, nr.Range.NamedRangeReference(true)); if (nr.Name == "_FilterDatabase") writer.WriteAttributeString("ss", "Hidden", null, "1"); writer.WriteEndElement(); } } if (started) writer.WriteEndElement(); }
internal void RemoveNamedRange(string name, Worksheet ws) { NamedRanges.RemoveAll(nr => nr.Name == name && nr.Worksheet == ws); }
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(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 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 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 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; }
internal void AddNamedRange(Range range, string name, Worksheet ws) { if (range.FirstCell() != null && range.FirstCell().GetParentBook() != this) throw new InvalidOperationException("Named range parent book should be same"); NamedRange namedRange = NamedRanges.Find(nr => nr.Name == name && nr.Worksheet == ws); if (namedRange == null) { namedRange = NamedRanges.Find(nr => nr.Range.Match(range)); if (namedRange == null) { if (name == "_FilterDatabase") { NamedRanges.Insert(0, new NamedRange(range, name, ws)); } else { NamedRanges.Add(new NamedRange(range, name, ws)); } } else { namedRange.Name = name; } } else { namedRange.Range = range; } }
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 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 AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp) { //設定Worksheet各column 寬度 sheet.Font.Size = 12; sheet.Font.Name = "新細明體"; //欄位寬度特別設定的 //sheet.Columns(0).Width = 74; int iRow = 0; sheet.Name = strSheetName; //sheet.PrintOptions.Orientation = PageOrientation.Landscape; sheet.PrintOptions.Orientation = PageOrientation.Portrait; 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 = ""; Range rng = new Range(sheet[1, iRow], sheet[2, iRow]); rng.Merge(); rng = new Range(sheet[3, iRow], sheet[4, iRow]); rng.Merge(); rng = new Range(sheet[5, iRow], sheet[8, iRow]); rng.Merge(); sheet[0, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[1, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[2, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[3, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[4, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[5, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[6, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[7, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[8, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[0, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[1, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[2, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[3, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[4, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[5, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[6, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[7, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[8, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; iRow++; 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[0, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[1, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[2, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[3, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[4, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[5, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[6, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[7, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[8, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; sheet[0, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[1, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[2, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[3, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[4, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[5, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[6, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[7, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[8, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; iRow++; for (int i = 0; i < dtTemp.Rows.Count; i++) { sheet[0, iRow].Value = dtTemp.Rows[i]["ROUTE"].ToString(); sheet[1, iRow].Value = dtTemp.Rows[i]["QTY_00"].ToString(); sheet[2, iRow].Value = dtTemp.Rows[i]["QTY_01"].ToString(); sheet[3, iRow].Value = dtTemp.Rows[i]["QTY_10"].ToString(); sheet[4, iRow].Value = dtTemp.Rows[i]["QTY_11"].ToString(); sheet[5, iRow].Value = dtTemp.Rows[i]["SUM_QTY_0"].ToString(); sheet[6, iRow].Value = dtTemp.Rows[i]["SUM_QTY_1"].ToString(); sheet[7, iRow].Value = dtTemp.Rows[i]["TOTAL_QTY"].ToString(); sheet[8, iRow].Value = ""; if (sheet[0, iRow].Value == "小計" || sheet[0, iRow].Value == "總計") { sheet[0, iRow].Font.Bold = true; sheet[1, iRow].Font.Bold = true; sheet[2, iRow].Font.Bold = true; sheet[3, iRow].Font.Bold = true; sheet[4, iRow].Font.Bold = true; sheet[5, iRow].Font.Bold = true; sheet[6, iRow].Font.Bold = true; sheet[7, iRow].Font.Bold = true; sheet[8, iRow].Font.Bold = true; } sheet[0, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[1, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[2, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[3, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[4, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[5, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[6, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[7, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[8, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All; sheet[1, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[2, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[3, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[4, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[5, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[6, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[7, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; sheet[8, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right; iRow++; } iRow++; 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 = ""; }
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 = "審核"; iRow++; for (int i = 0; i < dtTemp.Rows.Count; i++) { sheet[0, iRow].Value = dtTemp.Rows[i]["GROUP_NO"].ToString(); sheet[1, iRow].Value = dtTemp.Rows[i]["NAME"].ToString(); sheet[2, iRow].Value = dtTemp.Rows[i]["DISCOUNT_KIND_NAME"].ToString(); sheet[3, iRow].Value = dtTemp.Rows[i]["ROOT_NO_NAME"].ToString(); sheet[4, iRow].Value = dtTemp.Rows[i]["PMA_NAME"].ToString(); sheet[5, iRow].Value = dtTemp.Rows[i]["CATEGORY_NAME"].ToString(); sheet[6, iRow].Value = dtTemp.Rows[i]["ITEM"].ToString(); sheet[7, iRow].Value = dtTemp.Rows[i]["ITEM_NAME"].ToString(); sheet[8, iRow].Value = dtTemp.Rows[i]["PERIOD"].ToString(); sheet[9, iRow].Value = dtTemp.Rows[i]["PERIOD_END"].ToString(); if (dtTemp.Rows[i]["PRICE_DISCOUNT"].ToString() == string.Empty) { sheet[10, iRow].Value = ""; } else { sheet[10, iRow].Value = decimal.Parse(dtTemp.Rows[i]["PRICE_DISCOUNT"].ToString()); } if (dtTemp.Rows[i]["FIX_PRICE_DISCOUNT"].ToString() == string.Empty) { sheet[11, iRow].Value = ""; } else { sheet[11, iRow].Value = decimal.Parse(dtTemp.Rows[i]["FIX_PRICE_DISCOUNT"].ToString()); } sheet[12, iRow].Value = dtTemp.Rows[i]["EFF_START"].ToString(); sheet[13, iRow].Value = dtTemp.Rows[i]["EFF_END"].ToString(); sheet[14, iRow].Value = dtTemp.Rows[i]["CONFIRM_CODE"].ToString(); //setPercentFormat(sheet[9, iRow]); iRow++; } }
internal string GetAutoFilterRange(Worksheet ws) { NamedRange namedRange = NamedRanges.Find( nr => nr.Name == "_FilterDatabase" && nr.Worksheet == ws); if (namedRange == null) return ""; return namedRange.Range.NamedRangeReference(false); }
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++; } }
/// <summary> /// Returns the sheet at a given position /// </summary> /// <param name="index">Index of the <see cref="Worksheet"/> starting from 0</param> /// <returns><see cref="Worksheet"/> reference to the requested sheet</returns> public Worksheet this[int index] { get { if (index < 0) throw new ArgumentOutOfRangeException("index"); if ((index + 1) > _Worksheets.Count) for (int i = _Worksheets.Count; i <= index; i++) { Worksheet ws = new Worksheet(this); SetSheetNameByIndex(ws, i); _Worksheets.Add(ws); } return _Worksheets[index]; } }
private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp) { //設定Worksheet各column 寬度 sheet.Font.Size = 10; sheet.Font.Name = "新細明體"; //欄位寬度特別設定的 //sheet.Columns(0).Width = 30; 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]["PROFIT_NO"].ToString(); sheet[1, iRow].Value = dtTemp.Rows[i]["W_AMT"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["W_AMT"].ToString()); sheet[2, iRow].Value = dtTemp.Rows[i]["Z_AMT"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT"].ToString()); sheet[3, iRow].Value = dtTemp.Rows[i]["Z_AMT_1"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_1"].ToString()); sheet[4, iRow].Value = dtTemp.Rows[i]["Z_AMT_2"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_2"].ToString()); sheet[5, iRow].Value = dtTemp.Rows[i]["Z_AMT_3"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_3"].ToString()); sheet[6, iRow].Value = dtTemp.Rows[i]["PSS_AMT"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["PSS_AMT"].ToString()); sheet[7, iRow].Value = dtTemp.Rows[i]["Z_AMT_4"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_4"].ToString()); sheet[8, iRow].Value = dtTemp.Rows[i]["Z_AMT_5"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_5"].ToString()); sheet[9, iRow].Value = dtTemp.Rows[i]["Z_AMT_6"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_6"].ToString()); sheet[10, iRow].Value = dtTemp.Rows[i]["OTHER_AMT"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["OTHER_AMT"].ToString()); sheet[11, iRow].Value = dtTemp.Rows[i]["Z_AMT_7"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["Z_AMT_7"].ToString()); //sheet[12, iRow].Value = dtTemp.Rows[i]["AMT"].ToString() == string.Empty ? 0 : decimal.Parse(dtTemp.Rows[i]["AMT"].ToString()); iRow++; } }
/// <summary> /// Delete a sheet by instance /// </summary> /// <param name="ws">Instance of sheet to delete</param> public void DeleteSheet(Worksheet ws) { if (ws != null) DeleteSheet(_Worksheets.FindIndex(s => s == ws)); }
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++; } }
/// <summary> /// Insert a new sheet before another sheet /// </summary> /// <param name="ws">Instance of sheet before which new sheet will be added</param> /// <returns>New worksheet instance</returns> /// <remarks>If sheet is not found, the new sheet is added to the end of all sheets</remarks> public Worksheet InsertBefore(Worksheet ws) { return InsertBefore(_Worksheets.FindIndex(s => s == ws)); }
internal static void ParsePrintHeaders(Worksheet ws, string range) { string[] printOptions = range.Split(new [] { ',' }); foreach (string po in printOptions) { Match match = PrintRowRegex.Match(po); if (match.Success) { int start; if (int.TryParse(match.Groups["RowStart"].Value, NumberStyles.Integer, CultureInfo.InvariantCulture, out start)) { int end = 0; if (!(match.Groups["RowEnd"].Success && int.TryParse(match.Groups["RowEnd"].Value, NumberStyles.Integer, CultureInfo.InvariantCulture, out end))) { end = start; } ws.PrintOptions.SetTitleRows(start, end); } } else { match = PrintColumnsRegex.Match(po); if (match.Success) { int start; if (int.TryParse(match.Groups["ColStart"].Value, NumberStyles.Integer, CultureInfo.InvariantCulture, out start)) { int end = 0; if (!(match.Groups["ColEnd"].Success && int.TryParse(match.Groups["ColEnd"].Value, NumberStyles.Integer, CultureInfo.InvariantCulture, out end))) { end = start; } ws.PrintOptions.SetTitleColumns(start, end); } } } } }
/// <summary> /// Insert a new sheet after another sheet /// </summary> /// <param name="index">Index of sheet after which new sheet will be added</param> /// <returns>New worksheet instance</returns> /// <remarks>If index is not in bounds, the new sheet is added to the end of all sheets</remarks> public Worksheet InsertAfter(int index) { if (index < 0) return Add(); if (index >= _Worksheets.Count) return this[index]; Worksheet newSheet = new Worksheet(this); SetSheetNameByIndex(newSheet, index + 1); _Worksheets.Insert(index + 1, newSheet); return newSheet; }
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++; } }
/// <summary> /// Insert a new sheet after another sheet /// </summary> /// <param name="ws">Instance of sheet after which new sheet will be added</param> /// <returns>New worksheet instance</returns> /// <remarks>If sheet is not found, the new sheet is added to the end of all sheets</remarks> public Worksheet InsertAfter(Worksheet ws) { return InsertAfter(_Worksheets.FindIndex(s => s == ws)); }