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