public string exportToWord() { // Step 1. Read data from database if (isStoreNameEmpty()) { return("Store Name is empty"); } if (hasParameterStore) { try { ConnectData cdata = new ConnectData(); // get connectString from Sacombank cdata.ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["gMVVMConnectionString"].ConnectionString; foreach (KeyValuePair <string, string> pair in this.storeParameterValue) { cdata.Paramerters.Add(pair.Key); cdata.ParamertersValue.Add(pair.Value); cdata.ParametersType.Add(SqlDbType.VarChar); } // get data from database to datatable if (!cdata.Read_Store(StoreName, true)) { return("Gọi Store thất bại"); } //bat dau export WordTemplateBase word = new WordTemplateBase() { TemplatePath = this.FilePath }; word.Data = cdata.DataSource; this.packageStream = word.ExcuteMailMergeSimple(); return(null); } catch (Exception ex) { if (ex.Message == "Passed invalid TemplatePath to Excel Template") { return("Chưa có mẫu báo cáo dạng excel."); } return(ex.Message); } } return(null); }
public string exportToExcel() { // Step 1. Read data from database if (isStoreNameEmpty()) { return("Store Name is empty"); } if (isReportNameEmpty()) { return("Excel Name id is empty"); } if (isfilePathTemplateFileEmpty()) { return("Template File Doesn't exists"); } if (hasParameterStore) { try { ConnectData cdata = new ConnectData(); // get connectString from Sacombank cdata.ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["gMVVMConnectionString"].ConnectionString; foreach (KeyValuePair <string, string> pair in this.storeParameterValue) { cdata.Paramerters.Add(pair.Key); cdata.ParamertersValue.Add(pair.Value); cdata.ParametersType.Add(SqlDbType.VarChar); } // get data from database to datatable if (!cdata.Read_Store(StoreName, true)) { return("Gọi Store thất bại"); } //Bat dat export //Khoi tao voi duong dan excel truyen vao ExcelTemplateExportBase excel = new ExcelTemplateExportBase() { TemplatePath = this.FilePath }; //gan du lieu doc tu store do vo excel excel.SmartmarkersObjData = new Dictionary <string, object>(); excel.SmartmarkersObjData.Add("obj", cdata.DataSource.DefaultView); //cac doi so truyen vao foreach (KeyValuePair <string, string> pair in this.ParameterReport) { excel.SmartmarkersObjData.Add(pair.Key, pair.Value); } this.packageStream = excel.ExprortSmartmarkers(); return(null); } catch (Exception ex) { if (ex.Message == "Passed invalid TemplatePath to Excel Template") { return("Chưa có mẫu báo cáo dạng excel."); } return(ex.Message); } } return(null); }
private void ReportMultiesTable() { try { Dictionary <string, Dictionary <string, string> > LstValues = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> parameters = new Dictionary <string, string>(); Dictionary <string, string> ParametersReport = new Dictionary <string, string>(); string key_compare = "pfrp_"; string storeKey = "_stna_"; int _index = 0; string storeName = ""; string _keyParam = ""; foreach (String key in Request.QueryString.AllKeys) { if (key != "ReportName" && key != "StoreName" && key != "IsMuiltiesStore" && key != "HasValue" && key != "TitleName" && key != "IsPrintTemp" && key != "IsExportExcel" && key != "PathExport" && key != "IsExportExcelOld" && key != "IsExportNew" && key != "TypeExport" ) { //Lenght Key > 5 char if (key.Substring(0, key_compare.Length).Equals(key_compare)) { ParametersReport.Add(key.Substring( key_compare.Length , key.Length - key_compare.Length), Request[key]); } else { _index = key.IndexOf(storeKey); storeName = key.Substring(0, _index); _keyParam = key.Substring(_index + 6, key.Length - _index - 6); if (!LstValues.ContainsKey(storeName)) { LstValues.Add(storeName, new Dictionary <string, string>()); } LstValues[storeName].Add(_keyParam, Request[key]); } } } //Goi load report AssetMangement.GenerateData.ConnectData sqlData = new AssetMangement.GenerateData.ConnectData(); rpds_demo = new ReportDataSet(); try { foreach (var store_name in LstValues.Keys) { sqlData.Paramerters = new List <string>(); sqlData.ParametersType = new List <SqlDbType>(); sqlData.ParamertersValue = new List <string>(); if (Request["HasValue"] == "True") { foreach (KeyValuePair <string, string> pair in LstValues[store_name]) { sqlData.Paramerters.Add("@" + pair.Key); sqlData.ParametersType.Add(SqlDbType.VarChar); sqlData.ParamertersValue.Add(pair.Value); } } //add paramerters cho store proc //rpds_demo = new ReportDataSet(); bool isLoad = sqlData.Read_Store(store_name, true); if (isLoad) { try { DataTable dt = sqlData.DataSource; dt.TableName = storeKey + store_name; rpds_demo.Tables.Add(dt); } catch (Exception ex) { Label1.Text = ex.Message; } } else { Label1.Text = "Không kết nối được"; } } } catch (Exception ex) { Label1.Text = "Lỗi data: " + ex.Message; return; } finally { GC.Collect(); } try { string reportPath = Server.MapPath("/Reports/Management/" + Request["ReportName"]); cryRpt = new ReportDocument(); cryRpt.Load(reportPath); cryRpt.SummaryInfo.ReportTitle = Request["TitleName"]; foreach (var keyStoreName in LstValues.Keys) { //reportDocument.OpenSubreport("rptSubReport1.rpt").SetDataSource(dt1); // reportDocument.OpenSubreport("rptSubReport2JNR.rpt").SetDataSource(dt2); cryRpt.Database.Tables[keyStoreName].SetDataSource(rpds_demo.Tables[storeKey + keyStoreName]); } if (ParametersReport.Count > 0) { foreach (KeyValuePair <string, string> pair in ParametersReport) { ParameterDiscreteValue pa = new ParameterDiscreteValue(); pa.Value = pair.Value; ParameterFieldDefinition crParameterFieldDefinition = cryRpt.DataDefinition.ParameterFields[pair.Key]; crParameterFieldDefinition.CurrentValues.Clear(); crParameterFieldDefinition.CurrentValues.Add(pa); crParameterFieldDefinition.ApplyCurrentValues(crParameterFieldDefinition.CurrentValues); } } CrystalReportViewer.ReportSource = cryRpt; } catch (Exception exr) { Label1.Text = "Lỗi parameter Report: " + exr.Message; return; } finally { GC.Collect(); } } catch (Exception ex) { Label1.Text = "Lỗi:" + ex.Message; return; } }
private void load_rpt_FILE(String reportName, String storeName, Dictionary <string, string> paramerter, Dictionary <string, string> parameterReport = null , String hasValue = "false", String title = "Tiêu đề", String isPrintTemp = "False", String isExportExcel = "False") { //ReportDB.exportDataToExcel("ABC", null, "OIUYTR"); //return; AssetMangement.GenerateData.ConnectData sqlData = new AssetMangement.GenerateData.ConnectData(); if (hasValue == "True") { foreach (KeyValuePair <string, string> pair in paramerter) { sqlData.Paramerters.Add("@" + pair.Key); sqlData.ParametersType.Add(SqlDbType.VarChar); sqlData.ParamertersValue.Add(pair.Value); } } // add paramerters cho store proc rpds_demo = new ReportDataSet(); bool isLoad = sqlData.Read_Store(storeName, true); if (isExportExcel == "True" || isExportExcel == "true") { // HyperLink1.NavigateUrl = HttpContext.Current.Request.PhysicalApplicationPath + "ReportPages/"; // return; string tieude = Request["TitleName"]; DataTable dt = new DataTable(); string reportNameWithoutExtension = reportName.Substring(0, reportName.IndexOf('.')); dt = sqlData.DataSource; Dictionary <string, string> listNewColumnName = ReportDB.ReadXML(reportNameWithoutExtension); if (listNewColumnName == null) { Label1.Text = "Lỗi đọc XML"; return; } foreach (KeyValuePair <string, string> pair in listNewColumnName) { // values += "&" + pair.Key + "=" + pair.Value; // check name load from xml if (dt.Columns.Contains(pair.Key) && !dt.Columns.Contains(pair.Value)) { dt.Columns[pair.Key].ColumnName = pair.Value; } } // string path = ReportDB.SaveAs(reportNameWithoutExtension); string path = HttpContext.Current.Request.PhysicalApplicationPath + "ReportPages/" + reportNameWithoutExtension + ".xls"; bool isComplete = ReportDB.exportDataToExcel(tieude, dt, path); Label1.Text = "Xuất file excel cho report " + tieude; if (isComplete) { Label1.Text += " thành công ! ";// + "<a id=\"linkdownload\" href=\"" + path + "\">Tải file</a>"; HyperLink1.NavigateUrl = "../" + reportNameWithoutExtension + ".xls"; HyperLink1.Text = "Tải file"; // System.Diagnostics.Process.Start(path); } else { Label1.Text += " thất bại !"; } // Label1.Text = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + "reportName" + ".xls"; return; } try { if (isLoad) { try { DataTable dt = sqlData.DataSource; if (isPrintTemp.Equals("True")) { dt = getDataPrintTemp(dt, parameterReport.First().Value); } dt.TableName = "Newrpt"; rpds_demo.Tables.Add(dt); } catch (Exception ex) { Label1.Text = ex.Message; } } else { Label1.Text = "Không kết nối được"; } } catch (Exception ex) { Label1.Text = "Lỗi data: " + ex.Message; return; } finally { GC.Collect(); } try { string reportPath = Server.MapPath("/Reports/Management/" + reportName); cryRpt = new ReportDocument(); cryRpt.Load(reportPath); cryRpt.SummaryInfo.ReportTitle = title; cryRpt.SetDataSource(rpds_demo.Tables["Newrpt"]); if (parameterReport.Count > 0) { foreach (KeyValuePair <string, string> pair in parameterReport) { ParameterDiscreteValue pa = new ParameterDiscreteValue(); pa.Value = pair.Value; ParameterFieldDefinition crParameterFieldDefinition = cryRpt.DataDefinition.ParameterFields[pair.Key]; crParameterFieldDefinition.CurrentValues.Clear(); crParameterFieldDefinition.CurrentValues.Add(pa); crParameterFieldDefinition.ApplyCurrentValues(crParameterFieldDefinition.CurrentValues); } } //cryRpt.PrintOptions.PrinterName = "Fax"; //cryRpt.PrintToPrinter(1, false, 0, 0); //CrystalReportViewer.ReportSource = cryRpt; //System.Drawing.Printing.PrintDocument pDoc = new System.Drawing.Printing.PrintDocument(); // CrystalDecisions.Shared.PrintLayoutSettings PrintLayout = new CrystalDecisions.Shared.PrintLayoutSettings(); //System.Drawing.Printing.PrinterSettings printerSettings = new System.Drawing.Printing.PrinterSettings(); // printerSettings.PrinterName = "Default printer"; // don't use this, use the new button //PrintLayout.Scaling = PrintLayoutSettings.PrintScaling.DoNotScale; //System.Drawing.Printing.PageSettings pSettings = new System.Drawing.Printing.PageSettings(printerSettings); //rpt.PrintOptions.DissociatePageSizeAndPrinterPaperSize = false; //cryRpt.PrintOptions.PrinterDuplex = PrinterDuplex.Simplex; //System.Drawing.Printing.PageSettings pageSettings = new System.Drawing.Printing.PageSettings(printerSettings); //if (cryRpt.DefaultPageSettings.PaperSize.Height > cryRpt.DefaultPageSettings.PaperSize.Width) //{ // cryRpt.PrintOptions.DissociatePageSizeAndPrinterPaperSize = true; // rptClientDoc.PrintOutputController.ModifyPaperOrientation(CrPaperOrientationEnum.crPaperOrientationPortrait); //} //else //{ // cryRpt.PrintOptions.DissociatePageSizeAndPrinterPaperSize = true; // //rptClientDoc.PrintOutputController.ModifyPaperOrientation(CrPaperOrientationEnum.crPaperOrientationLandscape); //} //cryRpt.PrintToPrinter(printerSettings, pageSettings, false, PrintLayout); CrystalReportViewer.ReportSource = cryRpt; } catch (Exception exr) { Label1.Text = "Lỗi parameter Report: " + exr.Message; return; } finally { GC.Collect(); } // ReportDB.exportDataToExcel("demo", sqlData.DataSource); }
public string exportToExcel() { // Step 1. Read data from database if (isStoreNameEmpty()) { return("Store Name is empty"); } if (isExcelNameEmpty()) { return("Excel Name id is empty"); } if (isexcelTemplateFileEmpty()) { return("Template File Doesn't exists"); } string xmlError = LoadXMLPath(); if (xmlError != null) { return(xmlError); } if (hasParameterStore) #region "Export" /*{ * * try * { * ConnectData cdata = new ConnectData(); * // get connectString from Sacombank * cdata.ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["gMVVMConnectionString"].ConnectionString; * // add type of Paramerter Store * //foreach (KeyValuePair<string, SqlDbType> pair in this.storeParameterType) * //{ * // cdata.Paramerters.Add(pair.Key); * // cdata.ParametersType.Add(pair.Value); * //} * foreach (KeyValuePair<string, string> pair in this.storeParameterValue) * { * cdata.Paramerters.Add(pair.Key); * cdata.ParamertersValue.Add(pair.Value); * cdata.ParametersType.Add(SqlDbType.VarChar); * } * // get data from database to datatable * cdata.Read_Store(StoreName, true); * // Step 2. Read XML file * // Read ParameterExcel in XML * // load Excel Template file * ExcelPackage pckTemplate = new ExcelPackage(new FileInfo(excelTemplateFile), true); * * var wsTemplate = pckTemplate.Workbook.Worksheets[1]; * XmlNode node; * string Cellname, type, pathXMLCell; * Dictionary<string, string> listFieldData = ReadXML("Template/" + idExcel + "/FieldExcel/StartPoint"); + int row = int.Parse(listFieldData["Row"]), col = int.Parse(listFieldData["Column"]); + string path = "Template/" + idExcel + "/FieldExcel/Fields"; + listFieldData = ReadXML(path); + + // Step 3.1 : set value for cell paramerterExcel + foreach (KeyValuePair<string, string> pair in parameterExcel) + { + Cellname = pair.Key; + pathXMLCell = "Template/" + idExcel + "/ParameterExcel/" + Cellname; + node = xmldoc.SelectSingleNode(pathXMLCell); + type = node.ChildNodes[0].Name; + formatExcel(Cellname, type, ref wsTemplate, pathXMLCell); + wsTemplate.Cells[Cellname].Value = pair.Value; + } + // Step 3.2 : fill data in field excel + // if (cdata.DataSource.Rows.Count > 0) // check if has data or no + // { + // Step 3.2.1 : read row and col started - thieuvq 14/11/2014 + //thieuvq Dictionary<string, string> listFieldData = ReadXML("Template/" + // + idExcel + "/FieldExcel/StartPoint"); + //thieuvq int row = int.Parse(listFieldData["Row"]), col = int.Parse(listFieldData["Column"]); + //thieuvq string path = "Template/" + // + idExcel + "/FieldExcel/Fields"; + //listFieldData = ReadXML(path); + if (listFieldData == null) + return "Error when load XML format datafield "; + int ncol; + foreach (KeyValuePair<string, string> pair in listFieldData) + { + pathXMLCell = "Template/" + idExcel + "/FieldExcel/Fields/" + pair.Key + "/Prior"; + node = xmldoc.SelectSingleNode(pathXMLCell); + // get prior + ncol = int.Parse(node.ChildNodes[0].Value); + //pathXMLCell = "Template/" + // + storeName + "/FieldExcel/Fields/" + pair.Key ; + //type = node.ChildNodes[0].Name; + //formatExcel(); + for (int i = 0; i < cdata.DataSource.Rows.Count; i++) + { + + wsTemplate.Cells[row + i, col + ncol].Value + = cdata.DataSource.Rows[i][pair.Key]; + + pathXMLCell = "Template/" + idExcel + "/FieldExcel/Fields/" + pair.Key; + node = xmldoc.SelectSingleNode(pathXMLCell); + type = node.ChildNodes[1].Name; + formatExcel(row + i, col + ncol, type, ref wsTemplate, pathXMLCell); + + wsTemplate.Cells[row + i, col + ncol].Style.Border.Left.Style = ExcelBorderStyle.Thin; + wsTemplate.Cells[row + i, col + ncol].Style.Border.Top.Style = ExcelBorderStyle.Thin; + wsTemplate.Cells[row + i, col + ncol].Style.Border.Right.Style = ExcelBorderStyle.Thin; + wsTemplate.Cells[row + i, col + ncol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; + + // wsTemplate.Cells[row + i, col + ncol].Style.WrapText = true; + } + + } + // shape.Text = "Sample 3 uses a template that is stored in the application cashe."; + pckTemplate.Save(); + excelPackageStream = pckTemplate.Stream; + + return null; + // } + } + catch (Exception ex) + { + if (ex.Message == "Passed invalid TemplatePath to Excel Template") + return "Chưa có mẫu báo cáo dạng excel."; + return ex.Message; + } + + + }*/ #endregion #region "Export2" { try { ConnectData cdata = new ConnectData(); // get connectString from Sacombank cdata.ConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["gMVVMConnectionString"].ConnectionString; // add type of Paramerter Store //foreach (KeyValuePair<string, SqlDbType> pair in this.storeParameterType) //{ // cdata.Paramerters.Add(pair.Key); // cdata.ParametersType.Add(pair.Value); //} // DateTime start = DateTime.Now; string rootPath = "Template/" + idExcel + "/FieldExcel"; foreach (KeyValuePair <string, string> pair in this.storeParameterValue) { cdata.Paramerters.Add(pair.Key); cdata.ParamertersValue.Add(pair.Value); cdata.ParametersType.Add(SqlDbType.VarChar); } // get data from database to datatable cdata.Read_Store(StoreName, true); // Step 2. Read XML file // Read ParameterExcel in XML // load Excel Template file ExcelPackage pckTemplate = new ExcelPackage(new FileInfo(excelTemplateFile), true); var wsTemplate = pckTemplate.Workbook.Worksheets[1]; XmlNode node; string Cellname, type, pathXMLCell; Dictionary <string, string> listFieldData = ReadXML(rootPath + "/StartPoint"); int row = int.Parse(listFieldData["Row"]), col = int.Parse(listFieldData["Column"]); string path = rootPath + "/Fields"; listFieldData = ReadXML(path); // Step 3.1 : set value for cell paramerterExcel foreach (KeyValuePair <string, string> pair in parameterExcel) { Cellname = pair.Key; pathXMLCell = "Template/" + idExcel + "/ParameterExcel/" + Cellname; node = xmldoc.SelectSingleNode(pathXMLCell); type = node.ChildNodes[0].Name; formatExcel(Cellname, type, ref wsTemplate, pathXMLCell); wsTemplate.Cells[Cellname].Value = pair.Value; } // Step 3.2 : fill data in field excel // if (cdata.DataSource.Rows.Count > 0) // check if has data or no // { // Step 3.2.1 : read row and col started - thieuvq 14/11/2014 //thieuvq Dictionary<string, string> listFieldData = ReadXML("Template/" // + idExcel + "/FieldExcel/StartPoint"); //thieuvq int row = int.Parse(listFieldData["Row"]), col = int.Parse(listFieldData["Column"]); //thieuvq string path = "Template/" // + idExcel + "/FieldExcel/Fields"; //listFieldData = ReadXML(path); if (listFieldData == null) { return("Error when load XML format datafield "); } int ncol; //thieuvq 08062015 Dictionary <string, int> _values = new Dictionary <string, int>(); Dictionary <string, string> _names = new Dictionary <string, string>(); foreach (KeyValuePair <string, string> pair in listFieldData) { pathXMLCell = rootPath + "/Fields/" + pair.Key + "/Prior"; node = xmldoc.SelectSingleNode(pathXMLCell); ncol = int.Parse(node.ChildNodes[0].Value); _values.Add(pair.Key, ncol); pathXMLCell = rootPath + "/Fields/" + pair.Key; node = xmldoc.SelectSingleNode(pathXMLCell); type = node.ChildNodes[1].Name; _names.Add(pair.Key, type); } //pathXMLCell = "Template/" // + storeName + "/FieldExcel/Fields/" + pair.Key ; //type = node.ChildNodes[0].Name; //formatExcel(); int TotalRow = cdata.DataSource.Rows.Count; int TotalCol = listFieldData.Count; for (int i = 0; i < TotalRow; i++) { foreach (KeyValuePair <string, string> pair in listFieldData) { //pathXMLCell = "Template/" // + idExcel + "/FieldExcel/Fields/" + pair.Key + "/Prior"; //node = xmldoc.SelectSingleNode(pathXMLCell); //// get prior //ncol = int.Parse(node.ChildNodes[0].Value); ncol = _values[pair.Key]; wsTemplate.Cells[row + i, col + ncol].Value = cdata.DataSource.Rows[i][pair.Key]; //pathXMLCell = "Template/" //+ idExcel + "/FieldExcel/Fields/" + pair.Key; //node = xmldoc.SelectSingleNode(pathXMLCell); //type = node.ChildNodes[1].Name; // formatExcel(row + i, col + ncol, type, ref wsTemplate, pathXMLCell); } } if (TotalRow > 0) { //formatExcel foreach (KeyValuePair <string, string> pair in listFieldData) { type = _names[pair.Key]; ncol = _values[pair.Key]; Dictionary <string, string> Format = ReadXML(rootPath + "/Fields/" + pair.Key + "/" + type); switch (type) { case "WrapText": switch (Format.Keys.First()) { case "Text": break; case "Date": wsTemplate.Cells[row, col + ncol, row + TotalRow - 1, col + ncol].Style.Numberformat.Format = "dd/mm/yyyy"; break; case "Number": wsTemplate.Cells[row, col + ncol, row + TotalRow - 1, col + ncol].Style.Numberformat.Format = "#,##0"; break; case "Percent": wsTemplate.Cells[row, col + ncol, row + TotalRow - 1, col + ncol].Style.Numberformat.Format = "#,##0"; break; case "FLOAT": wsTemplate.Cells[row, col + ncol, row + TotalRow - 1, col + ncol].Style.Numberformat.Format = "#,##0.00"; break; } wsTemplate.Cells[row, col + ncol, row + TotalRow - 1, col + ncol].Style.WrapText = (Format.Values.First().ToLower().Equals("true")) ? true : false; break; default: break; } } //tao border wsTemplate.Cells[row, col, row + TotalRow - 1, col + TotalCol - 1].Style.Border.Left.Style = ExcelBorderStyle.Thin; wsTemplate.Cells[row, col, row + TotalRow - 1, col + TotalCol - 1].Style.Border.Top.Style = ExcelBorderStyle.Thin; wsTemplate.Cells[row, col, row + TotalRow - 1, col + TotalCol - 1].Style.Border.Right.Style = ExcelBorderStyle.Thin; wsTemplate.Cells[row, col, row + TotalRow - 1, col + TotalCol - 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; wsTemplate.Cells[row, col, row + TotalRow - 1, col + TotalCol - 1].Style.WrapText = true; } // shape.Text = "Sample 3 uses a template that is stored in the application cashe."; pckTemplate.Save(); excelPackageStream = pckTemplate.Stream; //return (start.ToString() + "\n" + DateTime.Now.ToString() + "\n" + (DateTime.Now - start).TotalSeconds.ToString()); return(null); // } } catch (Exception ex) { if (ex.Message == "Passed invalid TemplatePath to Excel Template") { return("Chưa có mẫu báo cáo dạng excel."); } return(ex.Message); } } #endregion return(null); // return "not have data, please check again!"; }