/// <summary>
/// Constructs a formula and adds another formula as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="parameter">Another formula to add to this formula's parameter list</param>
public static Formula Formula(string function, Formula parameter)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(parameter).EndGroup();
return formula;
}
/// <summary>
/// Constructs a formula and adds a range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <example><code>Formula formula = new Formula("sum", new Range(cell1, cell2));</code></example>
public static Formula Formula(string function, Range range)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range).EndGroup();
return formula;
}
/// <summary>
/// Constructs a formula without any parameters
/// </summary>
/// <param name="function">Function name</param>
/// <example><code>Formula formula = new Formula("sum");</code></example>
public static Formula Formula(string function)
{
Formula formula = new Formula();
formula.Add(function).EmptyGroup();
return formula;
}
/// <summary>
/// Constructs a formula and adds another formula as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="parameter">Another formula to add to this formula's parameter list</param>
public static Formula Formula(string function, Formula parameter)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(parameter).EndGroup();
return(formula);
}
/// <summary>
/// Constructs a formula and adds a range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <example><code>Formula formula = new Formula("sum", new Range(cell1, cell2));</code></example>
public static Formula Formula(string function, Range range)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range).EndGroup();
return(formula);
}
/// <summary>
/// Constructs a formula without any parameters
/// </summary>
/// <param name="function">Function name</param>
/// <example><code>Formula formula = new Formula("sum");</code></example>
public static Formula Formula(string function)
{
Formula formula = new Formula();
formula.Add(function).EmptyGroup();
return(formula);
}
/// <summary>
/// Constructs a formula and adds a filtered range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <param name="cellCompare">A custom defined to compare the values of the range</param>
/// <remarks>
/// Custom delegates can filter all cells and auto add them to the parameter list of a formula
/// by passing a System.Predicate>Cell<, i.e. a
/// delegate which accepts Cell as its value and returns bool
/// to both Formula constructor or Add. All the values accessors (i.e. Value, NumericValue etc.)
/// and cell style can be checked.
/// </remarks>
/// <example>
/// Lets assume column 1,2,3,6 and 7 are bold...
/// <code>
/// XmlStyle style = new XmlStyle();
/// style.Font.Bold = true;
///
/// // VS2008 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.Style == style);
///
/// // or VS2005 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// delegate (Cell cell) { return cell.Style == style; } );
/// </code>
/// In the first example of style, the value of the cell will be =SUM(A4:C4, F4:G4).
/// <para><b>Continuous ranges matching to true will be joined as one parameter, i.e. A4:C4
/// and not as seperate parameters, i.e. A4,B4,C4</b></para>
///
/// Using value accessors...
/// <code>
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.NumericValue > 10000 & cell.NumericValue <= 50000);
/// </code>
/// </example>
public static Formula Formula(string function, Range range, Predicate <Cell> cellCompare)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range, cellCompare).EndGroup();
return(formula);
}
private static void ParseFormula(Cell cell, Formula formula, string formulaText)
{
Match match;
ParseArgumentType pat = GetArgumentType(formulaText, out match);
switch (pat)
{
case ParseArgumentType.Function:
{
string function = match.Groups["FunctionName"].Value;
Formula subFormula = new Formula();
subFormula.Add(function).StartGroup();
string[] parameters = match.Groups["Parameters"].Value.Split(new [] { ',' });
foreach (string parameter in parameters)
{
ParseFormula(cell, subFormula, parameter);
}
subFormula.EndGroup();
formula.Add(subFormula);
break;
}
case ParseArgumentType.Range:
case ParseArgumentType.AbsoluteRange:
{
Range range = new Range(formulaText);
formula.Add(range);
break;
}
case ParseArgumentType.None:
{
formula.Add(formulaText);
break;
}
}
}
private static void ParseFormula(Cell cell, Formula formula, string formulaText)
{
Match match;
ParseArgumentType pat = GetArgumentType(formulaText, out match);
switch (pat)
{
case ParseArgumentType.Function:
{
string function = match.Groups["FunctionName"].Value;
Formula subFormula = new Formula();
subFormula.Add(function).StartGroup();
string[] parameters = match.Groups["Parameters"].Value.Split(new [] { ',' });
foreach (string parameter in parameters)
ParseFormula(cell, subFormula, parameter);
subFormula.EndGroup();
formula.Add(subFormula);
break;
}
case ParseArgumentType.Range:
case ParseArgumentType.AbsoluteRange:
{
Range range = new Range(formulaText);
formula.Add(range);
break;
}
case ParseArgumentType.None:
{
formula.Add(formulaText);
break;
}
}
}
protected void Btn_Export_Click(object sender, EventArgs e)
{
try
{
#region 匯出條件變數
string strItem = this.SLP_SKU1.Text;
string strPeriod = this.SLP_ItemPeriod1.Text;
string strSLP_StoreChain_S = SLP_StoreChain_S.Text;
string strSLP_StoreChain_E = SLP_StoreChain_E.Text;
string strSLP_CodeFile97_S = SLP_CodeFile97_S.Text;
string strSLP_CodeFile97_E = SLP_CodeFile97_E.Text;
string strSLP_CodeFile91_S = SLP_CodeFile91_S.Text;
string strSLP_CodeFile91_E = SLP_CodeFile91_E.Text;
string strSLP_CodeFile18_S = SLP_CodeFile18_S.Text;
string strSLP_CodeFile18_E = SLP_CodeFile18_E.Text;
string strSLP_CodeFile3_S = SLP_CodeFile3_S.Text;
string strSLP_CodeFile3_E = SLP_CodeFile3_E.Text;
#endregion
#region check是否存在OnPack贈品主檔資料
DataTable dtGift = new DataTable();
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(strItem, "string"));
ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
ParameterList.Add(GetValueSetParameter("-1", "int"));
BCO.MaintainGift bco = new MaintainGift(ConnectionDB);
dtGift = bco.QueryGift(ParameterList);
if (dtGift == null || (dtGift != null && dtGift.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "無贈品主檔資料";
return;
}
#endregion
#region check是否存在通路門市贈品資料
DataTable dtStoreGift = new DataTable();
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(strItem, "string"));
ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_E, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_S, "string"));
ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_E, "string"));
ParameterList.Add(SLP_SHELVE_CM3.Text); //專屬架
BCO.MaintainStoreGift bcoStoreGift = new MaintainStoreGift(ConnectionDB);
dtStoreGift = bcoStoreGift.ExportStoreGift(ParameterList);
if (dtStoreGift == null || (dtStoreGift != null && dtStoreGift.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "無通路門市贈品資料";
return;
}
#endregion
#region 匯出通路門市贈品資料
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
Worksheet sheet1 = book[1];
//設定Worksheet各column 寬度
sheet.Columns(3).Width = 70;
sheet.Columns(5).Width = 110;
sheet.Columns(6).Width = 45;
sheet.Columns(7).Width = 60;
sheet.Columns(8).Width = 120;
sheet1.Columns(0).Width = 30;
sheet1.Columns(2).Width = 110;
sheet1.Columns(3).Width = 45;
sheet1.Columns(4).Width = 60;
sheet1.Columns(5).Width = 120;
//sheet1.Columns(6).Hidden = true; //品號品名怕被修改到,所以產生兩個隱藏欄位
//sheet1.Columns(7).Hidden = true;
int iRow = 0;
//贈品主檔
sheet1.Name = "贈品主檔";
sheet1.PrintOptions.Orientation = PageOrientation.Landscape;
sheet1.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
iRow = 0;
sheet1[0, iRow].Value = "序號";
sheet1[1, iRow].Value = "商品品號";
sheet1[2, iRow].Value = "商品名稱";
sheet1[3, iRow].Value = "期別";
sheet1[4, iRow].Value = "贈品名稱";
sheet1[5, iRow].Value = "虛擬品號";
//sheet1[6, iRow].Value = "贈品名稱";
//sheet1[7, iRow].Value = "虛擬品號";
iRow++;
DataRow[] drs = dtGift.Select("1=1", "VIRTUAL_NAME");
foreach (DataRow dr in drs)
{
if (dr["id"].ToString() != "0")
{
sheet1[0, iRow].Value = iRow.ToString();
sheet1[1, iRow].Value = dr["ITEM"].ToString();
sheet1[2, iRow].Value = dr["ITEM_NAME"].ToString();
sheet1[3, iRow].Value = dr["PERIOD"].ToString();
sheet1[4, iRow].Value = dr["VIRTUAL_NAME"].ToString();
sheet1[5, iRow].Value = dr["VIRTUAL_CODE"].ToString();
//sheet1[6, iRow].Value = dr["VIRTUAL_NAME"].ToString();
//sheet1[7, iRow].Value = dr["VIRTUAL_CODE"].ToString();
iRow++;
}
}
//多通路門市贈品設定
iRow = 0;
sheet.Name = "多通路門市贈品設定";
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
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 = "贈品名稱";
iRow++;
foreach (DataRow dr in dtStoreGift.Rows)
{
sheet[0, iRow].Value = dr["CHAN_NO"].ToString();
sheet[1, iRow].Value = dr["CHAN_NAME"].ToString();
sheet[2, iRow].Value = dr["STORE"].ToString();
sheet[3, iRow].Value = dr["STORE_NAME"].ToString();
sheet[4, iRow].Value = dr["ITEM"].ToString();
sheet[5, iRow].Value = dr["ITEM_NAME"].ToString();
sheet[6, iRow].Value = dr["PERIOD"].ToString();
Formula f = new Formula().Add("VLOOKUP").StartGroup();
f.Add(new Range(sheet[8, iRow])).Operator(',');
//f.Add(new Range(sheet1[5, 1], sheet1[5, dtGift.Rows.Count])).Operator(',');
//f.Add(new Range(sheet1[4, 1], sheet1[4, dtGift.Rows.Count])).EndGroup();
f.Add(new Range(sheet1[5, 1], sheet1[4, dtGift.Rows.Count])).Operator(',');
f.Add("2").Operator(',');
f.Add("false").EndGroup();
sheet[7, iRow].Value = f;
//sheet[7, x].Value = dr["VIRTUAL_CODE"].ToString();
sheet[8, iRow].Value = dr["VIRTUAL_NAME"].ToString();
iRow++;
}
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(string.Format("MKT14_通路贈品門市設定_{0}_{1}.xls", strItem, strPeriod), System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
System.IO.MemoryStream stream = new System.IO.MemoryStream();
book.Export(stream);
string producedExcel = GetStringFromMemoryStream(stream);
stream.Close();
Response.Write(producedExcel);
Response.Flush();
Response.End();
#endregion
}
catch (Exception ex)
{ this.ErrorMsgLabel.Text = ex.ToString(); }
finally { }
}
/// <summary>
/// Constructs a formula and adds a filtered range as the first parameter
/// </summary>
/// <param name="function">Function name</param>
/// <param name="range">Range to add as parameter</param>
/// <param name="cellCompare">A custom defined to compare the values of the range</param>
/// <remarks>
/// Custom delegates can filter all cells and auto add them to the parameter list of a formula
/// by passing a System.Predicate>Cell<, i.e. a
/// delegate which accepts Cell as its value and returns bool
/// to both Formula constructor or Add. All the values accessors (i.e. Value, NumericValue etc.)
/// and cell style can be checked.
/// </remarks>
/// <example>
/// Lets assume column 1,2,3,6 and 7 are bold...
/// <code>
/// XmlStyle style = new XmlStyle();
/// style.Font.Bold = true;
///
/// // VS2008 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.Style == style);
///
/// // or VS2005 style
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// delegate (Cell cell) { return cell.Style == style; } );
/// </code>
/// In the first example of style, the value of the cell will be =SUM(A4:C4, F4:G4).
/// <para><b>Continuous ranges matching to true will be joined as one parameter, i.e. A4:C4
/// and not as seperate parameters, i.e. A4,B4,C4</b></para>
///
/// Using value accessors...
/// <code>
/// sheet[7, 3].Value = new Formula("sum", new Range(sheet[0, 3], sheet[6, 3]),
/// cell => cell.NumericValue > 10000 & cell.NumericValue <= 50000);
/// </code>
/// </example>
public static Formula Formula(string function, Range range, Predicate<Cell> cellCompare)
{
Formula formula = new Formula();
formula.Add(function).StartGroup().Add(range, cellCompare).EndGroup();
return formula;
}
