internal Worksheet(ExcelXmlWorkbook parent)
{
if (parent == null)
{
throw new ArgumentNullException("parent");
}
ParentBook = parent;
PrintOptions = new PrintOptions();
PrintOptions.Layout = PageLayout.None;
PrintOptions.Orientation = PageOrientation.None;
_Rows = new List <Row>();
_Columns = new List <Column>();
_MergedCells = new List <Range>();
TabColor = -1;
PrintOptions.FitHeight = 1;
PrintOptions.FitWidth = 1;
PrintOptions.Scale = 100;
PrintOptions.ResetMargins();
}
internal Column(Worksheet parent)
{
if (parent == null)
throw new ArgumentNullException("parent");
ParentBook = parent.ParentBook;
}
internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
{
if (!reader.IsEmptyElement)
{
while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "NamedRange")
{
Range range = null;
string name = "";
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
{
name = xa.Value;
}
if (xa.LocalName == "RefersTo" && xa.HasValue)
{
range = new Range(xa.Value);
}
}
NamedRange nr = new NamedRange(range, name, ws);
book.NamedRanges.Add(nr);
}
}
}
}
}
/// <summary>
/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
/// </summary>
/// <param name="importFile">File to import</param>
/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
public static ExcelXmlWorkbook Import(string importFile)
{
if (!File.Exists(importFile))
{
return(null);
}
Stream stream;
try
{
stream = new FileStream(importFile, FileMode.Open, FileAccess.Read);
}
catch (IOException)
{
return(null);
}
catch (SecurityException)
{
return(null);
}
catch (UnauthorizedAccessException)
{
return(null);
}
ExcelXmlWorkbook book = Import(stream);
stream.Close();
stream.Dispose();
return(book);
}
internal Column(Worksheet parent)
{
if (parent == null)
{
throw new ArgumentNullException("parent");
}
ParentBook = parent.ParentBook;
}
internal void Import(XmlReader reader)
{
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
{
Name = xa.Value;
}
if (xa.LocalName == "StyleID" && xa.HasValue)
{
Style = ParentBook.GetStyleByID(xa.Value);
}
}
while (reader.Read() && !(reader.Name == "Worksheet" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "Names":
{
ExcelXmlWorkbook.ImportNamedRanges(reader, GetParentBook(), this);
break;
}
case "Table":
{
ImportTable(reader);
break;
}
case "WorksheetOptions":
{
ImportOptions(reader);
break;
}
}
}
}
}
private void Export_Excel()
{
try
{
string strStartDate = SLP_MDC_START_DATE.StartDate;
string strEndDate = SLP_MDC_START_DATE.EndDate;
string strRoot_S = SLP_ROOT_NO_S.Text;
string strRoot_E = SLP_ROOT_NO_E.Text;
string strPMA_S = SLP_PMA_S.Text;
string strPMA_E = SLP_PMA_E.Text;
string strCATEGORY_S = SLP_CATEGORY_S.Text;
string strCATEGORY_E = SLP_CATEGORY_E.Text;
COMModel.COM14_BCO BCO = new COMModel.COM14_BCO(ConnectionDB);
// 讀取 Excel 資料
ParameterList.Clear();
ParameterList.Add(strStartDate);
ParameterList.Add(strEndDate);
ParameterList.Add(strRoot_S);
ParameterList.Add(strRoot_E);
ParameterList.Add(strPMA_S);
ParameterList.Add(strPMA_E);
ParameterList.Add(strCATEGORY_S);
ParameterList.Add(strCATEGORY_E);
DataTable dt = BCO.QuerySaleRank(ParameterList);
string strSheetName = "商品銷售排行榜";
if (dt.Rows.Count == 0)
{
ErrorMsgLabel.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, strSheetName, dt);
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(strSheetName + ".xls", 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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
private void Export_Excel(ArrayList ParameterList)
{
try
{
BCO.VAM19_BCO BCO = new BCO.VAM19_BCO(ConntionDB);
// 讀取 Excel 資料
DataTable dt;
if (this.rblReportSource.SelectedValue == "1")
{
dt = BCO.QueryExportTrace(ParameterList);
}
else if (this.rblReportSource.SelectedValue == "2")
{
dt = BCO.QueryVamAgmTrace(ParameterList);
}
else
{
dt = BCO.QueryVamSKUTrace(ParameterList);
}
if (dt.Rows.Count == 0)
{
ErrorMsgLabel.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
string s_Name;
if (this.rblReportSource.SelectedValue == "1")
{
s_Name = "補收帳款匯出";
AddSheetData(ref sheet0, s_Name, dt);
}
else if (this.rblReportSource.SelectedValue == "2")
{
s_Name = "各項扣款彙總表(補收)";
AddSheetDataTrace(ref sheet0, s_Name, dt);
}
else
{
s_Name = "商品交易補收彙總表";
AddSheetDataTrace_SKU(ref sheet0, s_Name, dt);
}
//設定輸出檔名
string s_FileName =
s_FileName = HttpUtility.UrlEncode("VAM191_" + s_Name + "_" + ParameterList[0].ToString()
+ "_" + ParameterList[1].ToString() + ".XLS", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
{
if (!reader.IsEmptyElement)
{
while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "NamedRange")
{
Range range = null;
string name = "";
foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
{
if (xa.LocalName == "Name" && xa.HasValue)
name = xa.Value;
if (xa.LocalName == "RefersTo" && xa.HasValue)
range = new Range(xa.Value);
}
NamedRange nr = new NamedRange(range, name, ws);
book.NamedRanges.Add(nr);
}
}
}
}
}
private void Export_Excel(DataTable dtTemp)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "Sheet1", dtTemp);
// 設定檔案路徑
string filePath = Server.MapPath("..\\") + System.Configuration.ConfigurationManager.AppSettings["DownLoadPath"] + "CAA\\";
Environment.CurrentDirectory = filePath + "\\";
// 設定 excel 輸出檔名
string excelFileName = "";
excelFileName = DateTime.Today.Year.ToString("####") + DateTime.Today.Month.ToString("0#") + DateTime.Today.Day.ToString("0#") + "_單品補貼報表.xls";
System.IO.FileStream stream = new FileStream(filePath + "\\" + excelFileName, FileMode.Create);
book.Export(stream);
stream.Close();
// 設定壓縮檔輸出檔名
string zipFileName = filePath + "\\" + DateTime.Today.Year.ToString("####") + DateTime.Today.Month.ToString("0#") + DateTime.Today.Day.ToString("0#") + "_CAA35.zip";
ZipFile zipFile = ZipFile.Create(zipFileName);
// 產生壓縮檔
zipFile.BeginUpdate();
zipFile.Add(excelFileName);
zipFile.CommitUpdate();
zipFile.Close();
// 傳到前端進行檔案下載
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment;filename=" + Path.GetFileName(zipFileName));
Response.ContentType = "application/octet-stream";
Response.WriteFile(zipFileName);
Response.Flush();
Response.Close();
}
/// <summary>
/// 頁籤=[報表] 欄位[報表類型]=彙總，按下Button[匯出]鈕
/// </summary>
private void but_TAB3_Print_Total()
{
try
{
#region 檢查條件
BCO.CAACommon CAAComm = new BCO.CAACommon();
ArrayList arl_Check_Condition = Check_Condition("TAB 報表");
#region 如果檢查有誤，則Return
if (arl_Check_Condition[1].ToString() != string.Empty)
{
#region 錯誤訊息
this.ErrorMsgLabel.Text = arl_Check_Condition[1].ToString();
#endregion
#region Focus欄位
if (arl_Check_Condition[0].ToString() != string.Empty)
{
string s_ScriptManager_Script = CAAComm.ToMakeUp_SetFocus_Script(arl_Check_Condition[0].ToString(), true);
ScriptManager.RegisterStartupScript(this.up_Msg, typeof(UpdatePanel), "CAA181", s_ScriptManager_Script, true);
}
#endregion
return;
}
#endregion
#endregion
#region 傳入參數
ParameterList.Clear();
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_B.Text, "string", false));//[結帳年月]起 20110520修改為起迄
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_E.Text, "string", false));//[結帳年月]迄
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_B.Text, "string", false));//[結帳年月-財會]起 20110520修改為起迄
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_E.Text, "string", false));//[結帳年月-財會]迄
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_B.Text, "string", false));//[利潤中心]起
ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_E.Text, "string", false));//[利潤中心]迄
ParameterList.Add(CAAComm.GetValueSetParameter(Session["UID"].ToString(), "string", false));//V_LOG_UID
#endregion
#region 連接資料庫
DataSet ds_Return = new DataSet();
BCO.MaintainDiscRecord bco = new BCO.MaintainDiscRecord(ConntionDB);
ds_Return = bco.QUERY_CAA18_REPORT_SUMMARY(ParameterList);
#endregion
#region 檢查回傳資料
if (ds_Return.Tables["SUMMARY"].Rows.Count == 0)
{
this.ErrorMsgLabel.Text = "查無資料";
return;
}
#endregion
#region 組合匯出的Excel
#region 設定變數
ExcelXmlWorkbook exl_WorkSheet = new ExcelXmlWorkbook();
Worksheet sheet = exl_WorkSheet[0];
sheet.Name = "折讓入帳彙總表";
#region 設定長和寬
int i_Vertical = 0;//直的有幾個欄位(依據資料庫[利潤中心])
int i_Horizontal = 0;//橫的有幾個欄位(依據資料庫[型式])
int i_Vertical_Fix = 3;//直的固定欄位[型式、稅別、合計]
int i_Horizontal_Fix = 4;//橫的固定欄位[標題抬頭、利潤中心抬頭、合計上方要空一行、合計]
i_Vertical = ds_Return.Tables["PROFIT"].Rows.Count + i_Vertical_Fix;
i_Horizontal = (ds_Return.Tables["DISC_TYPE"].Rows.Count * 3) + i_Horizontal_Fix;
#endregion
#endregion
#region 設定格式
#region 設定欄位寬度
for (int i_Style = 0; i_Style < i_Vertical; i_Style++)
{ sheet.Columns(i_Style).Width = 100; }
#endregion
#region 設定合併儲存格
Range rang_1 = new Range(sheet[0, 0], sheet[i_Vertical - 1, 0]);
rang_1.Merge();
#endregion
#region 設定Style
XmlStyle style_1 = new XmlStyle();
style_1.Alignment.Vertical = VerticalAlignment.Center;
style_1.Border.LineStyle = Borderline.Continuous;
style_1.Border.Color = System.Drawing.Color.Black;
style_1.Border.Sides = BorderSides.All;
style_1.Border.Weight = 1;
style_1.Font.Size = 10;
style_1.Font.Name = "新細明體";
XmlStyle style_2 = new XmlStyle();
style_2.Alignment.Vertical = VerticalAlignment.Center;
style_2.Alignment.Horizontal = HorizontalAlignment.Center;
style_2.Border.LineStyle = Borderline.Continuous;
style_2.Border.Color = System.Drawing.Color.Black;
style_2.Border.Sides = BorderSides.All;
style_2.Border.Weight = 1;
style_2.Font.Size = 10;
style_2.Font.Name = "新細明體";
for (int x = 0; x < i_Vertical; x++)
{
for (int y = 0; y < i_Horizontal; y++)
{
if (y != 0)
{ sheet[x, y].Style = style_1; }
}
}
#endregion
#endregion
#region 組合匯出資料
for (int i = 0; i < i_Horizontal; i++)
{
switch (i)
{
#region 橫的第一行
case 0:
sheet[0, 0].Value = this.slp_3_CLOSE_MONTH_ACCT_B.Text == string.Empty ? "折讓入帳彙總表" : this.slp_3_CLOSE_MONTH_ACCT_B.Text + "-" + this.slp_3_CLOSE_MONTH_ACCT_E.Text + "月折讓入帳彙總表";
sheet[0, 0].Style = style_2;
break;
#endregion
#region 橫的第二行
case 1:
sheet[0, 1].Value = string.Empty;
sheet[1, 1].Value = string.Empty;
for (int i_case_1 = 0; i_case_1 < ds_Return.Tables["PROFIT"].Rows.Count; i_case_1++)
{ sheet[i_case_1 + 2, 1].Value = ds_Return.Tables["PROFIT"].Rows[i_case_1]["PROFIT_NAME"].ToString(); }
sheet[i_Vertical - 1, 1].Value = "合計";
break;
#endregion
#region 其它行
default:
switch (i_Horizontal - i)
{
#region 橫的倒數第二行(合計上面一行要空白)
case 2:
sheet[0, i].Value = string.Empty;
break;
#endregion
#region 橫的最後一行(合計那一行)
case 1:
sheet[0, i].Value = "合計";
for (int i_case_last = 0; i_case_last < ds_Return.Tables["PROFIT"].Rows.Count; i_case_last++)
{ sheet[i_case_last + 2, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_case_last + 2, 0], sheet[i_case_last + 2, i_Horizontal - 2])); }
sheet[i_Vertical - 1, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_Vertical - 1, 0], sheet[i_Vertical - 1, i_Horizontal - 2]));
break;
#endregion
#region 橫的其他行
default:
#region 設定變數
int i_Get_Disc_Type = (i - 2) / 3;
int i_Get_Tax_Type = (i - 2) % 3;
string s_FilterExpression = string.Empty;//篩選條件
string s_DISC_SOURCE = string.Empty;//來源(1=系統、2=人工)
string s_DISC_TYPE = string.Empty;//型式
string s_TAX_TYPE = string.Empty;//稅別
string s_PROFIT_NO = string.Empty;//利潤中心
#endregion
#region 第1欄(型式)
sheet[0, i].Value = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString();
#endregion
#region 第2欄(稅別)
switch (i_Get_Tax_Type)
{
case 0:
sheet[1, i].Value = "應稅";
break;
case 1:
sheet[1, i].Value = "稅額";
break;
case 2:
sheet[1, i].Value = "免稅";
break;
}
#endregion
#region 其他資料欄
for (int i_case_default = 0; i_case_default < ds_Return.Tables["PROFIT"].Rows.Count; i_case_default++)
{
#region 設定[來源][型式][稅別][利潤中心]參數
//[來源][型式]
switch (i_Get_Disc_Type)
{
case 0://第1個一定是系統-一般
s_DISC_SOURCE = "1";//來源(1=系統、2=人工)
s_DISC_TYPE = "1";//型式=1=一般
break;
case 1://第2個一定是人工-一般
s_DISC_SOURCE = "2";//來源(1=系統、2=人工)
s_DISC_TYPE = "1";//型式=1=一般
break;
default:
s_DISC_SOURCE = "2";//來源(1=系統、2=人工)
s_DISC_TYPE = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString();
break;
}
//[稅別]
switch (i_Get_Tax_Type)
{
case 0:
s_TAX_TYPE = "0";//稅別=0=應稅，稅別=1=免稅
break;
case 1:
s_TAX_TYPE = "0";//稅別=0=應稅，稅別=1=免稅
break;
case 2:
s_TAX_TYPE = "1";//稅別=0=應稅，稅別=1=免稅
break;
}
//[利潤中心]
s_PROFIT_NO = ds_Return.Tables["PROFIT"].Rows[i_case_default]["CODE"].ToString();
#endregion
#region 組合查詢條件，查詢資料
s_FilterExpression = "DISC_SOURCE='" + s_DISC_SOURCE + "' and DISC_TYPE='" + s_DISC_TYPE + "' and TAX_TYPE='" + s_TAX_TYPE + "' and PROFIT_NO='" + s_PROFIT_NO + "'";
DataRow[] dr = ds_Return.Tables["SUMMARY"].Select(s_FilterExpression);
#endregion
#region 將資料寫入欄位
switch (dr.Length)
{
case 1:
if (i_Get_Tax_Type != 1)
{ sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_UAMT"].ToString()); }
else
{ sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_TAX"].ToString()); }
break;
default:
sheet[i_case_default + 2, i].Value = 0;
break;
}
#endregion
}
#endregion
#region 合計欄(Excel右邊的[合計]欄)
sheet[i_Vertical - 1, i].Value = FormulaHelper.Formula("SUM", new Range(sheet[0, i], sheet[i_Vertical - 2, i]));
#endregion
break;
#endregion
}
break;
#endregion
}
}
#endregion
#region 匯出Excel
string s_FileName = HttpUtility.UrlEncode("折讓入帳彙總表.XLS", System.Text.Encoding.UTF8);//設定輸出檔名
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
System.IO.MemoryStream stream = new System.IO.MemoryStream();
exl_WorkSheet.Export(stream);
if (stream == null ||
stream.Length == 0)
{ return; }
stream.Flush();
stream.Position = 0;
System.IO.StreamReader sr = new System.IO.StreamReader(stream);
string producedExcel = sr.ReadToEnd();
stream.Close();
Response.Write(producedExcel);
Response.Flush();
Response.End();
#endregion
#endregion
}
catch (Exception ex)
{
WaringLogProcess(ex.Message);
this.ErrorMsgLabel.Text = ex.Message;
}
}
/// <summary>
/// Converts a dataset to a work book
/// </summary>
/// <param name="source">The source dataset to convert to a work book</param>
/// <returns>Returns the <see cref="Yogesh.ExcelXml.ExcelXmlWorkbook"/>
/// for the dataset.</returns>
/// <remarks>All the tables are converted into sheets with sheet names as table + table number,
/// eg. "Table0" "Table1" etc. Supported types which can be successfully
/// converted to cells are the same as described in <see cref="Yogesh.ExcelXml.Cell"/>
/// except <see cref="Yogesh.ExcelXml.Cell"/> and
/// <see cref="Yogesh.ExcelXml.Formula"/></remarks>
public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
{
Worksheet sheet = book[tableNumber];
sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);
int columnCount = source.Tables[tableNumber].Columns.Count;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;
sheet[columnNumber, 0].Font.Bold = true;
}
int rowNumber = 0;
foreach (DataRow row in source.Tables[tableNumber].Rows)
{
rowNumber++;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
string rowType = row[columnNumber].GetType().FullName;
switch (rowType)
{
case "System.DateTime":
{
sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
break;
}
case "System.Boolean":
{
sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
break;
}
case "System.SByte":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64":
case "System.Single":
case "System.Double":
case "System.Decimal":
{
sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber],
CultureInfo.InvariantCulture);
break;
}
case "System.DBNull":
break;
// case "System.String": <-- default covers this...
default:
{
sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
break;
}
}
}
}
}
return(book);
}
/// <summary>
/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
/// </summary>
/// <param name="stream">Stream to import</param>
/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
public static ExcelXmlWorkbook Import(Stream stream)
{
XmlReaderSettings settings = new XmlReaderSettings();
settings.CloseInput = false;
settings.IgnoreComments = true;
settings.IgnoreProcessingInstructions = true;
settings.IgnoreWhitespace = true;
if (!stream.CanRead)
return null;
XmlReader reader = XmlReader.Create(stream, settings);
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
book.Styles.Clear();
int workSheet = 0;
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
// Document Properties
case "DocumentProperties":
{
if (!reader.IsEmptyElement)
book.Properties.Import(reader);
break;
}
case "Styles":
{
if (!reader.IsEmptyElement)
book.ImportStyles(reader);
break;
}
case "Names":
{
ImportNamedRanges(reader, book, null);
break;
}
case "Worksheet":
{
if (!reader.IsEmptyElement)
book[workSheet++].Import(reader);
break;
}
}
}
}
book.ResolveNamedRangeReferences();
book.ResolveCellReferences();
reader.Close();
stream.Close();
stream.Dispose();
return book;
}
private void Export_Excel(DataTable dt1, DataTable dt2,DataTable dt3)
{
try
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
Worksheet sheet1 = book[1];
Worksheet sheet2 = book[2];
AddSheetData1(ref sheet0, "商品期別檔", dt1);
AddSheetData2(ref sheet1, "期別帳務檔", dt2);
AddSheetData3(ref sheet2, "期別帳務店群門市", dt3);
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode("商品期別異動表_" + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + ".XLS", 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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
private void Export_Excel()
{
ErrorMsgLabel.Text = "";
//try
//{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];//頁籤1
Worksheet sheet1 = book[1];//頁籤2
DataTable dtTab = AddSheetData0(ref sheet0, "Table");
if (dtTab.Rows.Count > 0)
{
AddSheetData1(ref sheet1, "Columns", dtTab);
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode("系統檔案使用清單_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", 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 strExcelData = GetStringFromMemoryStream(stream);
stream.Close();
Response.Write(strExcelData);
Response.Flush();
Response.End();
}
else
{
ErrorMsgLabel.Text = "查無資料";
}
//}
//catch (Exception ex)
//{
// this.ErrorMsgLabel.Text = ex.ToString();
//}
//finally { }
}
private void Export_Excel()
{
try
{
ImportStoreOrderItem BCO = new ImportStoreOrderItem(ConntionDB);
// 讀取 Excel 資料
ParameterList.Clear();
ParameterList.Add(Session["UID"].ToString());
DataTable dt = BCO.QueryTemp2(ParameterList);
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];//即有品項
Worksheet sheet1 = book[1];//新增品項
Worksheet sheet2 = book[2];//異動品項
AddSheetData(ref sheet0, "即有品項", dt.Select("CHECK_FLAG='3'"));
AddSheetData(ref sheet1, "新增品項", dt.Select("CHECK_FLAG='1'"));
AddSheetData(ref sheet2, "異動品項", dt.Select("CHECK_FLAG='2'"));
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(txtExportFileName.Text.Trim(), 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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
private void Export_Excel(DataTable dtTemp)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
Worksheet sheet1 = book[1];
Worksheet sheet2 = book[2];
Worksheet sheet3 = book[3];
AddSheetData(ref sheet0, "進貨", DT_Filter(dtTemp, "RTYPE='進貨'"));
AddSheetData(ref sheet1, "退貨", DT_Filter(dtTemp, "RTYPE='退貨'"));
AddSheetData(ref sheet2, "補帳", DT_Filter(dtTemp, "RTYPE='補帳'"));
AddSheetData(ref sheet3, "調撥", DT_Filter(dtTemp, "RTYPE='調撥'"));
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode("銷售明細報表_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xls", 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();
}
private static ExcelXmlWorkbook OrdersArrayToWorkbook(IEnumerable<Order> orders)
{
var book = new ExcelXmlWorkbook { Properties = { Author = "AdvantShop.Net", LastAuthor = "AdvantShop.Net" }, DefaultStyle = new XmlStyle { Font = { Name = "Calibri", Size = 11 } } };
Worksheet sheet = book[0];
sheet.Name = "Orders";
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
//XLS columns definition
sheet.Columns(0).Width = 50;
sheet.Columns(1).Width = 80;
sheet.Columns(2).Width = 100;
sheet.Columns(3).Width = 130;
sheet.Columns(4).Width = 100;
sheet.Columns(5).Width = 100;
sheet.Columns(6).Width = 150;
sheet.Columns(7).Width = 80;
sheet.Columns(8).Width = 80;
sheet.Columns(9).Width = 80;
sheet.Columns(10).Width = 80;
sheet.Columns(11).Width = 300;
sheet.Columns(12).Width = 300;
sheet.Columns(13).Width = 300;
sheet.Columns(14).Width = 300;
sheet.Columns(15).Width = 300;
sheet[0, 0].Value = "OrderID";
sheet[1, 0].Value = "Status";
sheet[2, 0].Value = "OrderDate";
sheet[3, 0].Value = "FIO";
sheet[4, 0].Value = "Customer Email";
sheet[5, 0].Value = "Customer Phone";
sheet[6, 0].Value = "OrderedItems";
sheet[7, 0].Value = "Total";
sheet[8, 0].Value = "Tax";
sheet[9, 0].Value = "Cost";
sheet[10, 0].Value = "Profit";
sheet[11, 0].Value = "Payment";
sheet[12, 0].Value = "Shipping";
sheet[13, 0].Value = "Shipping Address";
sheet[14, 0].Value = "Customer Comment";
sheet[15, 0].Value = "Admin Comment";
var i = 1;
foreach (Order order in orders)
{
if (!CommonStatistic.IsRun) return book;
CommonStatistic.RowPosition++;
//Order to XLS row
sheet[0, i].Value = order.OrderID;
sheet[1, i].Value = order.OrderStatus != null ? order.OrderStatus.StatusName : "Неизвестный";
sheet[2, i].Value = order.OrderDate;
if (order.OrderCustomer != null)
{
sheet[3, i].Value = order.OrderCustomer.LastName + " " + order.OrderCustomer.FirstName;
sheet[4, i].Value = order.OrderCustomer.Email ?? string.Empty;
sheet[5, i].Value = order.OrderCustomer.MobilePhone ?? string.Empty;
}
else
{
sheet[3, i].Value = "Неизвестный";
sheet[4, i].Value = string.Empty;
sheet[5, i].Value = string.Empty;
}
if (order.OrderCurrency != null)
{
sheet[6, i].Value = RenderOrderedItems(order.OrderItems) ?? string.Empty;
sheet[7, i].Value = CatalogService.GetStringPrice(order.Sum, order.OrderCurrency);
sheet[8, i].Value = CatalogService.GetStringPrice(order.TaxCost, order.OrderCurrency);
float totalCost = order.OrderItems.Sum(oi => oi.SupplyPrice * oi.Amount);
sheet[9, i].Value = CatalogService.GetStringPrice(totalCost, order.OrderCurrency);
sheet[10, i].Value = CatalogService.GetStringPrice(order.Sum - order.ShippingCost - order.TaxCost - totalCost, order.OrderCurrency);
sheet[11, i].Value = order.PaymentMethodName;
sheet[12, i].Value = order.ArchivedShippingName + " - " + CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency);
sheet[13, i].Value = order.ShippingContact != null ? order.ShippingContact.Country + ", " + order.ShippingContact.City + ", " + order.ShippingContact.Address : string.Empty;
sheet[14, i].Value = order.CustomerComment ?? string.Empty;
sheet[15, i].Value = order.AdminOrderComment ?? string.Empty;
}
i++;
}
return book;
}
private void buttonSelectFileDirectory_Click(object sender, EventArgs e)
{
try
{
FolderBrowserDialog folderDialog = new FolderBrowserDialog();
folderDialog.Description = "Select Destination Folder for Inventory file";
folderDialog.ShowDialog();
workBook = new ExcelXmlWorkbook();
workBook.Properties.Author = Environment.UserName;
writer = File.OpenWrite(folderDialog.SelectedPath + "\\" + textBoxFileName.Text + ".xml");
sheetCount = 0;
//make this thing and all associated stuff go away
buttonSelectFileDirectory.Enabled = false;
buttonSelectFileDirectory.Visible = false;
textBoxFileName.Enabled = false;
textBoxFileName.Visible = false;
//next phase appear
buttonSelectInventoryDirectory.Enabled = true;
buttonSelectInventoryDirectory.Visible = true;
}
catch(ArgumentNullException ex)
{
System.Windows.Forms.MessageBox.Show(ex.ParamName);
}
catch(IOException ex)
{
System.Windows.Forms.MessageBox.Show("Could not make file-" + ex.Message);
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show("Asking for directory failed" + ex.Message);
}
}
private void Export_Excel()
{
try
{
string strStartDate = SLP_MDC_START_DATE.StartDate;
string strEndDate = SLP_MDC_START_DATE.EndDate;
string strRoot_S = SLP_ROOT_NO_S.Text;
string strRoot_E = SLP_ROOT_NO_E.Text;
COMModel.COM10_BCO BCO = new COMModel.COM10_BCO(ConnectionDB);
// 讀取 Excel 資料
ParameterList.Clear();
ParameterList.Add(strStartDate);
ParameterList.Add(strEndDate);
ParameterList.Add(strRoot_S);
ParameterList.Add(strRoot_E);
//1:各所縣市營業額查詢
//2:營業所及通路門市營業員業績查詢
//3:各所通路單店商品業績查詢
DataTable dt = new DataTable();
string strSheetName = string.Empty;
switch (ddlType.SelectedValue)
{
case "1":
dt = BCO.QueryByZOArea(ParameterList);
strSheetName = "各所縣市營業額查詢";
break;
case "2":
dt = BCO.QueryByZOChanStore(ParameterList);
strSheetName = "COM10_業績查詢";
break;
case "3":
dt = BCO.QueryBookByZOChanStore(ParameterList);
strSheetName = "各所通路單店商品業績查詢";
break;
}
if (dt.Rows.Count == 0)
{
ErrorMsgLabel.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
switch (ddlType.SelectedValue)
{
case "1":
AddSheetData1(ref sheet0, strSheetName, dt);
break;
case "2":
AddSheetData2(ref sheet0, strSheetName, dt);
break;
case "3":
AddSheetData3(ref sheet0, strSheetName, dt);
break;
}
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(strSheetName + ".xls", 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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
private void Export_Excel(DataTable dt)
{
ErrorMsgLabel.Text = "";
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];//頁籤1
AddSheetData0(ref sheet0, "Sheet1", dt);
//設定輸出檔名
string s_FileName = "";
if (rblREPORT.SelectedValue == "1")
s_FileName = HttpUtility.UrlEncode("儲區庫存匯總表_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", System.Text.Encoding.UTF8);
else
s_FileName = HttpUtility.UrlEncode("儲位庫存明細表_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", 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 strExcelData = GetStringFromMemoryStream(stream);
stream.Close();
Response.Write(strExcelData);
Response.Flush();
Response.End();
}
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 { }
}
private void ExportUnusual_Excel()
{
try
{
ParameterList.Clear();
ParameterList.Add(Session["UID"].ToString());
ParameterList.Add("2");
BCO.ProcessVAMMonthClose bco = new BCO.ProcessVAMMonthClose(ConntionDB);
DataTable dt_Return = bco.QueryTmp(ParameterList);
if (dt_Return.Rows.Count == 0)
{
this.ErrorMsgLabel.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "Sheet1", dt_Return);
//設定輸出檔名
string s_FileName =
s_FileName = HttpUtility.UrlEncode("VAM03_供應商合約補收異常報表.XLS", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
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();
}
catch (Exception ex)
{
ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
public void GenerateOutput_Backup(string outputRootDir)
{
#region Input validation
if (string.IsNullOrWhiteSpace(outputRootDir))
throw new ArgumentNullException(nameof(outputRootDir));
if (!Directory.Exists(outputRootDir))
throw new ArgumentException($"Unable to find directory {outputRootDir}.");
#endregion
#region Create output DIR and file
OutputDir = Path.Combine(outputRootDir, Ids.EXCEL_DIR);
if (!Directory.Exists(Directory.CreateDirectory(OutputDir).FullName))
throw new OutputComponentException($"Unable to create output directory {OutputDir}.");
OutputFile = Path.Combine(outputRootDir, Ids.EXCEL_DIR, Ids.EXCEL_FILE);
// File.Create(OutputFile);
// if (!File.Exists(OutputFile))
// throw new OutputComponentException($"Unable to create output file {OutputFile}.");
#endregion
#region Define row 'n' columns values...
int columns = 13;
int firstHeaderRow = 0;
int secondHeaderRow = firstHeaderRow + 1;
int timestampRow = secondHeaderRow + 1;
int tableHeaderRow = 0;
int row = tableHeaderRow + 1;
int freezeRow = tableHeaderRow + 1;
#endregion
#region Create the Excel xml workbook...
// Create a instance...
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
// Many such properties exist. Details can be found in the documentation
// The author of the document
book.Properties.Author = "Code Analyzer";
// This returns the first worksheet.
// Note that we have not declared a instance of a new worksheet
// All the dirty work is done by the library.
Worksheet dashboardSheet = book[0];
Worksheet matchesSheet = book[1];
// Name is the name of the sheet. If not set, the default name
// style is "sheet" + sheet number, like sheet1, sheet2
dashboardSheet.Name = "Dashboard";
matchesSheet.Name = "Matches";
// More on this in documentation
matchesSheet.FreezeTopRows = freezeRow;
// and this too...
dashboardSheet.PrintOptions.Orientation = PageOrientation.Landscape;
dashboardSheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
#endregion
#region Creating the header...
// Text for the header...
dashboardSheet[0, firstHeaderRow].Value = "Octopus | Code Analyzer";
dashboardSheet[0, secondHeaderRow].Value = "The Octopus Code Analyzer - the simple way to keep track of your code base";
// Setting the background color for the header...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[30, firstHeaderRow]).Interior.Color = Color.FromArgb(22, 54, 92);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Interior.Color = Color.FromArgb(22, 54, 92);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Sides = BorderSides.Bottom;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.LineStyle = Borderline.Double;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Color = Color.FromArgb(255, 255, 255);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Weight = 1;
// Setting the foreground color for the header text...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Color = Color.FromArgb(255, 255, 255);
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Color = Color.FromArgb(255, 255, 255);
// Setting the font for the header text...
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Size = 18;
new Range(dashboardSheet[0, firstHeaderRow], dashboardSheet[8, firstHeaderRow]).Font.Bold = true;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Size = 8;
new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Bold = true;
dashboardSheet[0, timestampRow].Value = "Exported on: " + DateTime.Now.ToShortDateString();
dashboardSheet[0, timestampRow].Font.Bold = true;
dashboardSheet[0, timestampRow].Font.Size = 8;
#endregion
#region Create the table header for all the data...
// Setting the width of the columns...
matchesSheet.Columns(0).Width = 100;
matchesSheet.Columns(1).Width = 100;
matchesSheet.Columns(2).Width = 100;
matchesSheet.Columns(3).Width = 100;
matchesSheet.Columns(4).Width = 100;
matchesSheet.Columns(5).Width = 100;
matchesSheet.Columns(6).Width = 100;
matchesSheet.Columns(7).Width = 100;
matchesSheet.Columns(8).Width = 100;
matchesSheet.Columns(9).Width = 100;
matchesSheet.Columns(10).Width = 100;
matchesSheet.Columns(11).Width = 100;
matchesSheet.Columns(12).Width = 100;
matchesSheet.Columns(13).Width = 900;
// Inserting headers and setting them to bold...
matchesSheet[0, tableHeaderRow].Value = "BatchId Id";
matchesSheet[1, tableHeaderRow].Value = "Timestamp";
matchesSheet[2, tableHeaderRow].Value = "Match Id";
matchesSheet[3, tableHeaderRow].Value = "Severity";
matchesSheet[4, tableHeaderRow].Value = "Project";
matchesSheet[5, tableHeaderRow].Value = "Language";
matchesSheet[6, tableHeaderRow].Value = "Category";
matchesSheet[7, tableHeaderRow].Value = "Category description";
matchesSheet[8, tableHeaderRow].Value = "Rule";
matchesSheet[9, tableHeaderRow].Value = "Rule description";
matchesSheet[10, tableHeaderRow].Value = "Rule Expression";
matchesSheet[11, tableHeaderRow].Value = "File name";
matchesSheet[12, tableHeaderRow].Value = "Match on line";
matchesSheet[13, tableHeaderRow].Value = "Code extract";
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Font.Bold = true;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).AutoFilter();
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Sides = BorderSides.Bottom;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.LineStyle = Borderline.Continuous;
new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Weight = 2;
#endregion
#region Insert data...
// Insert data...
IMatchProxy matchProxy = ProxyHome.Instance.RetrieveMatchProxy(OutputKeyKeeper.Instance.AccessKey);
foreach (IMatch match in matchProxy.Matches())
{
if ((row % 2) == 0)
new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(220, 230, 241);
else
new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(197, 217, 241);
matchesSheet[0, row].Value = match.Batch.Id;
matchesSheet[1, row].Value = $"{match.Batch.TimeStamp.ToShortDateString()} - {match.Batch.TimeStamp.ToShortTimeString()}";
matchesSheet[2, row].Value = match.Id;
matchesSheet[3, row].Value = $"{RuleSeverityMapper.Int2RuleSeverity((int)match.Severity)}";
matchesSheet[4, row].Value = match.ProjectDefinitionRef.Name;
matchesSheet[5, row].Value = match.LanguageDeclarationRef.Name;
matchesSheet[6, row].Value = match.CategoryDeclarationRef.Name;
matchesSheet[7, row].Value = match.CategoryDeclarationRef.Description;
matchesSheet[8, row].Value = match.RuleDeclarationRef.Name;
matchesSheet[9, row].Value = match.RuleDeclarationRef.Description;
matchesSheet[10, row].Value = match.RuleDeclarationRef.Expression;
matchesSheet[11, row].Value = match.Filename;
matchesSheet[12, row].Value = match.LineNumber;
matchesSheet[13, row].Value = match.CodeExtract;
row++;
}
#endregion
#region Export the Excel xml workbook...
book.Export(OutputFile);
#endregion
}
static void Main(string[] args)
{
// Create a instance...
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
// Many such properties exist. Details can be found in the documentation
// The author of the document
book.Properties.Author = "Yogesh Jagota";
// This returns the first worksheet.
// Note that we have not declared a instance of a new worksheet
// All the dirty work is done by the library.
Worksheet sheet = book[0];
// Name is the name of the sheet. If not set, the default name
// style is "sheet" + sheet number, like sheet1, sheet2
sheet.Name = "AgewiseOutstanding";
// More on this in documentation
sheet.FreezeTopRows = 3;
// and this too...
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
// This is the actual code which sets out the cell values
// Note again, that we don't declare any instance at all.
// All the work is done by the library.
// Index operator takes first value as column and second as row.
sheet[0, 0].Value = "Outstanding as on " +
DateTime.Now;
sheet[0, 0].Font.Bold = true;
sheet.Columns(0).Width = 180;
sheet.Columns(1).Width = 72;
sheet.Columns(2).Width = 90;
sheet.Columns(3).Width = 51;
sheet.Columns(4).Width = 51;
sheet.Columns(5).Width = 51;
sheet.Columns(6).Width = 51;
sheet.Columns(7).Width = 51;
sheet.Columns(8).Width = 51;
sheet.Columns(9).Width = 72;
sheet[0, 2].Value = "Name of Party";
sheet[1, 2].Value = "RSM";
sheet[2, 2].Value = "ASM";
sheet[3, 2].Value = "0-30";
sheet[4, 2].Value = "31-60";
sheet[5, 2].Value = "61-90";
sheet[6, 2].Value = "91+";
sheet[7, 2].Value = "Total";
new Range(sheet[0, 2], sheet[7, 2]).Font.Bold = true;
sheet[0, 3].Value = "M/s Stupid Paymaster";
sheet[1, 3].Value = "Mr. Nonsense";
sheet[2, 3].Value = "Mr. More Nonsense";
sheet[3, 3].Value = 0;
sheet[4, 3].Value = 5000;
sheet[5, 3].Value = 45000;
sheet[6, 3].Value = 0;
sheet[7, 3].Value = FormulaHelper.Formula("Sum", new Range(sheet[3, 3], sheet[6, 3]));
sheet[0, 4].Value = "M/s Good Paymaster";
sheet[1, 4].Value = "Mr. Good RSM";
sheet[2, 4].Value = "Mr. Good ASM";
sheet[3, 4].Value = 32000;
sheet[4, 4].Value = 0;
sheet[5, 4].Value = 0;
sheet[6, 4].Value = 0;
sheet[7, 4].Value = FormulaHelper.Formula("Sum", new Range(sheet[3, 4], sheet[6, 4]));
// no extension is added if not present
string outputFile = "Outstanding File.xml";
book.Export(outputFile);
}
/// <summary>
/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
/// </summary>
/// <param name="stream">Stream to import</param>
/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
public static ExcelXmlWorkbook Import(Stream stream)
{
XmlReaderSettings settings = new XmlReaderSettings();
settings.CloseInput = false;
settings.IgnoreComments = true;
settings.IgnoreProcessingInstructions = true;
settings.IgnoreWhitespace = true;
if (!stream.CanRead)
{
return(null);
}
XmlReader reader = XmlReader.Create(stream, settings);
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
book.Styles.Clear();
int workSheet = 0;
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
// Document Properties
case "DocumentProperties":
{
if (!reader.IsEmptyElement)
{
book.Properties.Import(reader);
}
break;
}
case "Styles":
{
if (!reader.IsEmptyElement)
{
book.ImportStyles(reader);
}
break;
}
case "Names":
{
ImportNamedRanges(reader, book, null);
break;
}
case "Worksheet":
{
if (!reader.IsEmptyElement)
{
book[workSheet++].Import(reader);
}
break;
}
}
}
}
book.ResolveNamedRangeReferences();
book.ResolveCellReferences();
reader.Close();
stream.Close();
stream.Dispose();
return(book);
}
private void Export_Excel(DataTable dtTemp)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "Sheet1", dtTemp);
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + "_CAA18帳務報表.xls", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
//Response.Clear();
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.Dispose();
Response.Write(producedExcel);
Response.Flush();
Response.End();
}
/// <summary>
/// Converts a dataset to a work book
/// </summary>
/// <param name="source">The source dataset to convert to a work book</param>
/// <returns>Returns the <see cref="Yogesh.ExcelXml.ExcelXmlWorkbook"/>
/// for the dataset.</returns>
/// <remarks>All the tables are converted into sheets with sheet names as table + table number,
/// eg. "Table0" "Table1" etc. Supported types which can be successfully
/// converted to cells are the same as described in <see cref="Yogesh.ExcelXml.Cell"/>
/// except <see cref="Yogesh.ExcelXml.Cell"/> and
/// <see cref="Yogesh.ExcelXml.Formula"/></remarks>
public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
{
Worksheet sheet = book[tableNumber];
sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);
int columnCount = source.Tables[tableNumber].Columns.Count;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;
sheet[columnNumber, 0].Font.Bold = true;
}
int rowNumber = 0;
foreach (DataRow row in source.Tables[tableNumber].Rows)
{
rowNumber++;
for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
{
string rowType = row[columnNumber].GetType().FullName;
switch (rowType)
{
case "System.DateTime":
{
sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
break;
}
case "System.Boolean":
{
sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
break;
}
case "System.SByte":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64":
case "System.Single":
case "System.Double":
case "System.Decimal":
{
sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber],
CultureInfo.InvariantCulture);
break;
}
case "System.DBNull":
break;
// case "System.String": <-- default covers this...
default:
{
sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
break;
}
}
}
}
}
return book;
}
private void Export_Excel(ArrayList ParameterList)
{
try
{
DataTable Dt = new DataTable();
Dt = this.searchData();
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
{
ErrorMsgLabel.Text = "查無資料";
}
else
{
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "不備庫存檔匯出資料", Dt);
//設定輸出檔名
string s_FileName =
s_FileName = HttpUtility.UrlEncode("IVM381不備庫存檔_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".XLS", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
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();
stream.Dispose();
Response.Clear();
}
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
internal string RangeReference(Cell cell)
{
if (CellFrom == null)
{
return(UnresolvedRangeReference);
}
if (CellFrom.ParentRow == null)
{
return("#N/A");
}
if (CellTo != null && CellTo.ParentRow == null)
{
return("#N/A");
}
if (cell == null)
{
throw new ArgumentNullException("cell");
}
string range;
if (Absolute)
{
range = AbsoluteReference();
}
else
{
if (CellTo != null)
{
range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]:R[{2}]C[{3}]",
CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex,
CellFrom.CellIndex - cell.CellIndex,
CellTo.ParentRow.RowIndex - cell.ParentRow.RowIndex,
CellTo.CellIndex - cell.CellIndex);
}
else
{
range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]",
CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex,
CellFrom.CellIndex - cell.CellIndex);
}
}
string sheetReference = "";
if (CellFrom.ParentRow.ParentSheet != cell.ParentRow.ParentSheet)
{
sheetReference = CellFrom.ParentRow.ParentSheet.Name;
ExcelXmlWorkbook workBook = CellFrom.GetParentBook();
if (workBook != cell.GetParentBook())
{
throw new ArgumentException("External workbook references are not supported");
}
}
if (!sheetReference.IsNullOrEmpty())
{
range = "'" + sheetReference + "'!" + range;
}
return(range);
}
private void Export_Excel()
{
try
{
this.ErrorMsgLabel.Text = "";
ParameterList.Clear();
ParameterList.Add(SLP_VENDOR.Text.Trim());
BCO.VAM21_BCO bco = new BCO.VAM21_BCO(ConntionDB);
DataTable dt = bco.QueryData(ParameterList);
if (dt.Rows.Count == 0)
{
this.ErrorMsgLabel.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "Sheet1", dt);
//設定輸出檔名
string s_FileName =
s_FileName = HttpUtility.UrlEncode("折讓單自動排除廠商.XLS", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
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();
}
catch (Exception ex)
{
this.ErrorMsgLabel.Text = ex.ToString();
}
finally { }
}
protected void Btn_ExportSurvey_Click(object sender, EventArgs e)
{
try
{
ErrorMsgLabel.Text = "";
#region 資料檢查
if (SLP_CodeFile1.Text == "" || SLP_CodeFile2.Text == "")
{
ErrorMsgLabel.Text = "營業所不可選全部";
return;
}
if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) != 0)
{
ErrorMsgLabel.Text = "只能選一個營業所";
return;
}
if (dSurveyDate.Text == "")
{
ErrorMsgLabel.Text = "市調日期不可空白";
return;
}
#endregion
DataTable dt = new DataTable();
#region 查詢市調情報資料
//取得查詢參數: 營業所,市調日期
ParameterList.Clear();
ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所
ParameterList.Add(GetValueSetParameter(dSurveyDate.Text, "date")); //市調日期
ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員 2010/09/24 modified
ParameterList.Add(GetValueSetParameter("9999", "int"));
//ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2
ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄)
BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB);
dt = bco.QuerySurveyRec(ParameterList);
#endregion
#region 將上市日期轉為無時間的格式
DataTable Dt = dt.Clone();
if (dt.Rows.Count > 0)
{
Dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String");
foreach (DataRow dr in dt.Rows)
{
DataRow DR = Dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i++)
DR[i] = dr[i];
if (DR["PUBLISH_DATE"] != System.DBNull.Value)
DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString();
Dt.Rows.Add(DR);
}
dt.Dispose();
dt = null;
}
#endregion
if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
{
//若不存在市調情報資料,無法匯出市調表
ErrorMsgLabel.Text = "查無資料";
}
else
{
//若已存在市調情報資料,匯出市調表
#region 匯出市調表
//設定輸出檔名
string s_FileName = "";
s_FileName = HttpUtility.UrlEncode("市調表.xls", System.Text.Encoding.UTF8);
#region 設定Excel Worksheet
//Excel Worksheet initilize
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet = book[0];
sheet.Name = "市調表";
//設定Worksheet橫印
sheet.PrintOptions.Orientation = PageOrientation.Landscape;
sheet.PrintOptions.SetMargins(0.5, 0.7, 0.5, 0.6);
//設定Worksheet各column 寬度
sheet.Columns(0).Width = 55;
sheet.Columns(1).Width = 130;
sheet.Columns(2).Width = 43;
sheet.Columns(3).Width = 58;
sheet.Columns(4).Width = 30;
sheet.Columns(5).Width = 42;
sheet.Columns(6).Width = 30;
sheet.Columns(7).Width = 30;
sheet.Columns(8).Width = 30;
sheet.Columns(9).Width = 30;
sheet.Columns(10).Width = 42;
sheet.Columns(11).Width = 30;
sheet.Columns(12).Width = 30;
sheet.Columns(13).Width = 30;
sheet.Columns(14).Width = 30;
sheet.Columns(15).Width = 42;
sheet.Columns(16).Width = 30;
sheet.Columns(17).Width = 30;
sheet.Columns(18).Width = 30;
sheet.Columns(19).Width = 30;
//設定外框線
sheet.Border.Sides = BorderSides.All;
sheet.Border.LineStyle = Borderline.Continuous;
#endregion
int iRow = 0; //Worksheet 列數
string strSTORE = "";
ArrayList alTITLE = new ArrayList();//營業所,市調人員ArrayList
ArrayList alSTORE = new ArrayList();//店號,店名ArrayList
int iStoreCount = 0; //總店數
//營業所,市調人員ArrayList
foreach (DataRow dr in Dt.Rows)
{
alTITLE.Add(new string[] { dr["EXPLAIN"].ToString(), dr["INVESTIGATOR"].ToString() });
break; //(營業所,市調人員)都相同, 取第一筆即可
}
int iRowStore = 3;//每行店數
int iStoreCol = 5;//每個店有幾個欄位
foreach (string[] ss in alTITLE)
{
//取得店號,店名,市調人員ArrayList
foreach (DataRow dr in Dt.Select("ITEM='" + Dt.Rows[0]["ITEM"].ToString() + "'"))
{
alSTORE.Add(new string[] { dr["STORE"].ToString(), dr["STORE_NAME"].ToString(), dr["INVESTIGATOR"].ToString() });//20121204 顏筱盈
}
//總店數
iStoreCount = alSTORE.Count;
for (int iStoreArea = 0; iStoreArea < Convert.ToInt32(Math.Ceiling(alSTORE.Count / (decimal)iRowStore)); iStoreArea++)
{
#region 設定Header列資訊
ArrayList alQTY = new ArrayList();
sheet[0, iRow].Value = ss[0]; //營業所
//sheet[1, iRow].Value = ss[1]; //市調人員
sheet[1, iRow].Value = ""; //20121204 顏筱盈
sheet[2, iRow].Value = "期別";
sheet[3, iRow].Value = "上市日期";
sheet[4, iRow].Value = "市調";
new Range(sheet[0, iRow + 1], sheet[3, iRow + 1]).Merge();
sheet[4, iRow + 1].Value = "次數";
for (int iStoreNo = (iRowStore * iStoreArea); iStoreNo < (iRowStore * iStoreArea + iRowStore); iStoreNo++)
{
if (iStoreNo >= alSTORE.Count) break;
string[] ss1 = (string[])alSTORE[iStoreNo];
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow].Value = ss1[0];//店號
//sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1];//店名
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1] + " ( " +ss1[2] + " )";//店名,市調人員 20121204 顏筱盈
new Range(sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow], sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow]).Merge();
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow + 1].Value = "發書";
alQTY.Add((iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5);
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow + 1].Value = "補書";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 7, iRow + 1].Value = "退書";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 8, iRow + 1].Value = "庫存";
sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow + 1].Value = "備註";
}
#endregion
iRow = iRow + 2;
int iItemCount = 0; //商品項數
//取得商品相關資料
foreach (DataRow dr in Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea])[0] + "'", "ITEM,ROUTE,STEP"))
{
sheet[0, iRow].Value = dr["ITEM"].ToString(); //品號
sheet[1, iRow].Value = dr["ITEM_NAME"].ToString(); //品名
sheet[2, iRow].Value = dr["PERIOD"].ToString(); //期別
sheet[3, iRow].Value = dr["PUBLISH_DATE"].ToString(); //上市日期
sheet[4, iRow].Value = dr["SURVEY_TIME"].ToString(); //市調次數
int iStore = 0;
foreach (object o in alQTY)
{
DataRow[] drQty = Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea + iStore])[0] + "'" + " AND ITEM='" + dr["ITEM"].ToString() + "'" + " AND PERIOD='" + dr["PERIOD"].ToString() + "'", "ITEM,ROUTE,STEP");
sheet[Convert.ToInt32(o), iRow].Value = drQty[0]["INCOME_QTY"].ToString(); //發書
sheet[Convert.ToInt32(o) + 1, iRow].Value = drQty[0]["REPLINISHMENT_QTY"].ToString(); //補書
sheet[Convert.ToInt32(o) + 2, iRow].Value = drQty[0]["RETURN_QTY"].ToString(); //退書
iStore++;
}
iRow++;
iItemCount++;
}
iRow++;
//商品總項數設定為20,不足則填入空列數
if (iItemCount != 0 & iItemCount < 20)
{
for (int iAdd = iItemCount; iAdd < 20; iAdd++)
{
iRow++;
}
}
}
}
#region 報表最後一頁特別處理
double dStoreCal = (double)(iStoreCount / (decimal)iRowStore);
int iNewBeginRow = Convert.ToInt32(Math.Ceiling(dStoreCal)) * 23;
//報表最後一頁,最後一列,最後一格 給一空白, 這樣最後一頁印出才會完整
if (Math.Ceiling(dStoreCal) % 2 == 1)
{
sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1 + 23].Value = " ";
}
else
{
sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1].Value = " ";
}
#endregion
//設定報表 縮放比例
int iPageCount = Convert.ToInt32(Math.Ceiling(dStoreCal / 2.0));
sheet.PrintOptions.SetFitToPage(1, iPageCount); //Sets excel's fit to page property (int width,int height)
//File download
Response.Charset = "big5";
Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
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)
{
ErrorMsgLabel.Text = ex.Message;
}
}
private void Export_Excel(ArrayList ParameterList)
{
try
{
CAAModel.CAA32_BCO BCO = new CAAModel.CAA32_BCO(ConntionDB);
// 讀取 Excel 資料
DataTable dt = BCO.QueryExportFile(ParameterList);
if (dt.Rows.Count == 0)
{
lblError.Text = "查無資料";
return;
}
ExcelXmlWorkbook book = new ExcelXmlWorkbook();
Worksheet sheet0 = book[0];
AddSheetData(ref sheet0, "前期餘額匯出", dt);
//設定輸出檔名
string s_FileName =
s_FileName = HttpUtility.UrlEncode("CAA321_" + ParameterList[0].ToString() + ParameterList[1].ToString() + ".XLS", System.Text.Encoding.UTF8);
//File download
Response.Charset = "big5";
Response.ContentType = "application/x-excel;charset='utf-8'";
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();
}
catch (Exception ex)
{
this.lblError.Text = ex.ToString();
}
finally { }
}
public static bool exportToExcel_old(DataGridView dgv, string fileName)
{
ExcelXmlWorkbook libro = new ExcelXmlWorkbook();
libro.Properties.Author = "Trust International S.A."; // Tomar el nombre de la empresa de Propertie.
Worksheet hoja = libro[0];
hoja.Name = "Exportacion";
//hoja.FreezeTopRows = 3;
//hoja.DisplayFormat = DisplayFormatType.None;
hoja.PrintOptions.Orientation = PageOrientation.Landscape;
hoja.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
XmlStyle estiloCeldaNombreColumnas = new XmlStyle();
estiloCeldaNombreColumnas.Font.Bold = true;
estiloCeldaNombreColumnas.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
estiloCeldaNombreColumnas.Border.LineStyle = Borderline.Continuous;
estiloCeldaNombreColumnas.Border.Weight = 2;
estiloCeldaNombreColumnas.Border.Sides = BorderSides.All;
// Primero cargo todas las columnas permitiendo hacerle algun formato diferente.
foreach (DataGridViewColumn col in dgv.Columns)
{
hoja[col.Index, 0].Value = col.Name;
hoja[col.Index, 0].Style = estiloCeldaNombreColumnas;
}
XmlStyle estiloCeldaNormal = new XmlStyle();
estiloCeldaNormal.Font.Bold = false;
estiloCeldaNormal.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
estiloCeldaNormal.Border.LineStyle = Borderline.Continuous;
estiloCeldaNormal.Border.Weight = 1;
estiloCeldaNormal.Border.Sides = BorderSides.All;
//estiloCeldaNormal.DisplayFormat = DisplayFormatType.Text;
int fila = 0;
int columna = 0;
double[] maxWithPerColumn;
TimeSpan h;
maxWithPerColumn = new double[dgv.Columns.Count];
DisplayFormatType cellType;
while (fila < dgv.Rows.Count)
{
columna = 0;
while (columna < dgv.Columns.Count)
{
if (dgv[columna, fila].ValueType == typeof(DateTime))
{
cellType = DisplayFormatType.GeneralDate;
if (dgv[columna,fila].Value.ToString() != "")
hoja[columna, fila+1].Value = ((DateTime)dgv[columna,fila].Value).ToShortDateString();
}
else if (dgv[columna, fila].ValueType == typeof(TimeSpan))
{
cellType = DisplayFormatType.Time;
h = (TimeSpan)dgv[columna, fila].Value;
if (dgv[columna, fila].Value.ToString() != "")
hoja[columna, fila + 1].Value = System.Math.Abs(System.Math.Truncate(h.TotalHours)).ToString() + ":" + System.Math.Abs(h.Minutes).ToString();
//((TimeSpan)dgv[columna, fila].Value).ToString();
}
else if (dgv[columna, fila].ValueType == typeof(Boolean))
{
cellType = DisplayFormatType.Custom;
if (dgv[columna, fila].Value.ToString() != "")
hoja[columna, fila+1].Value = ((Boolean)dgv[columna, fila].Value);
}
else if (dgv[columna, fila].ValueType == typeof(int) || dgv[columna, fila].ValueType == typeof(uint) || dgv[columna, fila].ValueType == typeof(byte) || dgv[columna, fila].ValueType == typeof(sbyte))
{
cellType = DisplayFormatType.Standard;
if (dgv[columna, fila].Value.ToString() != "")
hoja[columna, fila + 1].Value = dgv[columna, fila].Value;
}
else
{
// String
String str = dgv[columna, fila].Value.ToString();//.Replace("\n",Environment.NewLine);
cellType = DisplayFormatType.Text;
hoja[columna, fila + 1].Value = str;
}
if (dgv[columna, fila].Value.ToString().Length > maxWithPerColumn[columna])
{
maxWithPerColumn[columna] = (double)dgv[columna, fila].Value.ToString().Length;
}
//hoja[columna, fila].ContentType = ContentType.Formula;
estiloCeldaNormal.DisplayFormat = cellType;
hoja[columna, fila + 1].Style = estiloCeldaNormal;
columna++;
}
fila++;
}
columna = 0;
while (columna < dgv.Columns.Count)
{
hoja.Columns(columna).Width = dgv.Columns[columna].Width;
columna++;
}
// no extension is added if not present
Stream sw = new FileStream(fileName,FileMode.Create);
bool retval;
retval = libro.Export(sw);
sw.Close();
return retval;
}