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