private void LoadFile() { try { //Khai bao bien FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string path = ""; //Xac dinh ten file mau theo loai bao cao path = GetXlsFileDesign(); //Add du lieu tu ds, bien vao Flexcel report if (p_rpType < 30) { flcReport.AddTable(p_ds); // Gan du lieu vao ds } SetValueFlexcel(ref flcReport); // Thiet lap gt cho cac bien object #region Luu file xls len memory stream //FlexCelPreview1.CenteredPreview = true; //_templateStream.Flush(); try { _templateStream = new FileStream(path, FileMode.Open); } catch { } _xlsMemoryStream = new MemoryStream(); flcReport.Run(_templateStream, _xlsMemoryStream);//Chet o day??????????????????????? #endregion #region View file tren mem len FlexCelPreview1 xls = new FlexCel.XlsAdapter.XlsFile(); _xlsMemoryStream.Position = 0; xls.Open(_xlsMemoryStream); // SetValuePageSize(ref flexCelImgExport1); flexCelImgExport1.Workbook = xls; FlexCelPreview1.InvalidatePreview(); #endregion //Hien thi so trag, zoom cua page txtPage.Text = String.Format("{0} / {1}", FlexCelPreview1.StartPage, FlexCelPreview1.TotalPages); //UpdateZoom(); //Gan gia tri stream cho bien toan cuc cua form c_XlsStream = _xlsMemoryStream; _templateStream.Dispose(); } catch (Exception ex) { //Neu loi ko Kiet xuat dc MessageBox.MessageBoxBA msgDialog = new Taxi.MessageBox.MessageBoxBA(); msgDialog.Show(this, "Xảy ra lỗi trong quá trình lấy dữ liệu và tạo báo cáo!", "Thông báo", Taxi.MessageBox.MessageBoxButtonsBA.OK, Taxi.MessageBox.MessageBoxIconBA.Error); //this.Close(); } }
public FileContentResult RacunReport(long racunGlavaId) { DataAccessAdapterBase adapter = Helpers.Helper.GetDataAccessAdapter(); long firmaId = UserEntity.GetFirmaId(adapter, User.Identity.Name); string reportPath = Server.MapPath(string.Format("~/ReportTemplates/{0}/Racun.xls", firmaId)); FlexCel.XlsAdapter.XlsFile xls = new FlexCel.XlsAdapter.XlsFile(); xls.Open(reportPath); List <RacunReport> racunGlavaList = new List <RacunReport>(); racunGlavaList.Add(new RacunReport(adapter, racunGlavaId)); FlexCel.Report.FlexCelReport report = new FlexCel.Report.FlexCelReport(); report.AddTable("Racun", racunGlavaList); report.Run(xls); using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { FlexCel.Render.FlexCelPdfExport pdfExport = new FlexCel.Render.FlexCelPdfExport(xls); pdfExport.BeginExport(ms); pdfExport.ExportAllVisibleSheets(false, ""); pdfExport.EndExport(); //xls.Save(ms); ms.Position = 0; return(File(ms.ToArray(), "application/pdf")); } }
public FileContentResult RacunReportCollection(string jqGridFilters) { DataAccessAdapterBase adapter = Helpers.Helper.GetDataAccessAdapter(); long firmaId = UserEntity.GetFirmaId(adapter, User.Identity.Name); string reportPath = Server.MapPath(string.Format("~/ReportTemplates/{0}/Racun.xls", firmaId)); FlexCel.XlsAdapter.XlsFile xls = new FlexCel.XlsAdapter.XlsFile(); xls.Open(reportPath); short godina = ConfigEntity.GetInstance(adapter, firmaId).AktivnaGodina; RelationPredicateBucket bucket = RacunGlavaPager.CreateBucket(godina, jqGridFilters); bucket.PredicateExpression.Add(RacunGlavaFields.FirmaId == firmaId); IEnumerable <RacunReport> racunReportCollection = CoolJ.DatabaseGeneric.BusinessLogic.RacunReport.GetRacunReportCollection(adapter, bucket, firmaId); FlexCel.Report.FlexCelReport report = new FlexCel.Report.FlexCelReport(); report.AddTable("Racun", racunReportCollection); report.Run(xls); using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { FlexCel.Render.FlexCelPdfExport pdfExport = new FlexCel.Render.FlexCelPdfExport(xls); pdfExport.BeginExport(ms); pdfExport.ExportAllVisibleSheets(false, ""); pdfExport.EndExport(); //xls.Save(ms); ms.Position = 0; return(File(ms.ToArray(), "application/pdf")); } }
void Export() { try { #region Kết xuất FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _path = CommonData.ExcelDesignFilePath; string _fileExcelName = "Contract_Report_Ky.xls"; DataSet ds_all = new DataSet(); DataTable _dt = ConvertData.ConvertToDatatable(c_lst); _dt.Columns.Add("STT"); int index = 1; foreach (DataRow item in _dt.Rows) { item["STT"] = index; index++; } ds_all.Tables.Add(_dt); ds_all.Tables[0].TableName = "Contract"; if (ds_all.Tables.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } if (ds_all.Tables[0].Rows.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } _path += _fileExcelName; string _title_report = "BÁO CÁO KỶ Y"; CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all); CommonFunction.SetValueExportByString(ref flcReport, "title_report", _title_report); System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog(); saveReport.Filter = "Excel files (*.xls)|*.xls"; if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CommonFunction.ExportExcel(flcReport, _path, saveReport); } #endregion } catch (Exception ex) { ErrorLog.log.Error(ex.ToString()); } }
public static void SetValueExportByDataTable(ref FlexCel.Report.FlexCelReport flcReport, DataSet v_ds) { try { flcReport.AddTable(v_ds); } catch { } }
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value) { try { flcReport.SetValue(_ParamName, _value); } catch (Exception ex) { } }
public ActionResult BaoCaoHangTonKho(decimal p_warehouse_id, string p_wareHouse_name) { try { Report_BL _Report_BL = new Report_BL(); string c_err = ""; int is_err = -1; FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _fileExcelName_Src = "/Content/FlexcelDesignFile/BaoCaoTonKho.xls"; // tên mẫu string _fileExcelName_Export = "/Content/FlexcelExportFile/Bao_Cao_Ton_Kho.xls"; // tên file đích //DataSet _ds = _Report_BL.Get_TonKho(p_warehouse_id); List <TonKho_Info> _lst = _Report_BL.Get_TonKho(p_warehouse_id); foreach (TonKho_Info item in _lst) { item.SoNgayTon = Math.Round((decimal)DateTime.Now.Subtract(item.Register_Date.AddDays((double)item.Period)).TotalDays); } DataSet _ds = NaviCommon.ConvertData.ConvertToDataSet <TonKho_Info>(_lst); //someDateTime.Subtract(otherDateTime), if (_ds == null) { return(Json(new { c_err = "Không có dữ liệu báo cáo", is_err = -1 })); } else { if (_ds != null && _ds.Tables.Count > 0 && _ds.Tables[0].Rows.Count > 0) { _ds.Tables[0].TableName = "Data"; // đẩy dataset vào temp CommonFunc.SetValueExportByDataTable(ref flcReport, _ds); CommonFunc.SetValueExportByString(ref flcReport, "TenKho", p_wareHouse_name); is_err = CommonFunc.ExportExcel(flcReport, Server.MapPath(_fileExcelName_Src), Server.MapPath(_fileExcelName_Export), ref c_err); if (is_err == 0)// ko có lỗi { c_err = _fileExcelName_Export; } } else { c_err = "Không có dữ liệu để kết xuất!"; } } return(Json(new { c_err = c_err, is_err = is_err })); } catch (Exception ex) { NaviCommon.Common.log.Error(ex.ToString()); return(Json(new { success = -1 })); } }
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value) { try { flcReport.SetValue(_ParamName, _value); } catch (Exception ex) { CommonData.log.Error(ex.ToString()); } }
public static void SetValueExportByDataTable(ref FlexCel.Report.FlexCelReport flcReport, DataSet v_ds) { try { flcReport.AddTable(v_ds); } catch (Exception ex) { CommonData.log.Error(ex.ToString()); } }
private void SetValueFlexcel(ref FlexCel.Report.FlexCelReport flcReport) { try { Common.SetValueExportByString(ref flcReport, "ngayht", DateTime.Now); Common.SetValueExportByString(ref flcReport, "TuNgay", DateTime.Now); Common.SetValueExportByString(ref flcReport, "DenNgay", DateTime.Now); if (p_rpType == ConstParam.TongHopPhanAnh) { Common.SetValueExportByString(ref flcReport, "KhachHang", p_ds.Tables[0].Rows[_rowIndex][0]); Common.SetValueExportByString(ref flcReport, "SDT", p_ds.Tables[0].Rows[_rowIndex][1]); Common.SetValueExportByString(ref flcReport, "NhanVien", p_ds.Tables[0].Rows[_rowIndex][2]); Common.SetValueExportByString(ref flcReport, "ThoiGianNhan", p_ds.Tables[0].Rows[_rowIndex][3]); Common.SetValueExportByString(ref flcReport, "ThoiGianXuong", p_ds.Tables[0].Rows[_rowIndex][4]); Common.SetValueExportByString(ref flcReport, "LoTrinh", p_ds.Tables[0].Rows[_rowIndex]["Tong"].ToString()); } if (p_rpType == ConstParam.GiaiQuyetPhanAnh) { string strDot = ConstParam.DotLine; Common.SetValueExportByString(ref flcReport, "TenKhachHang", p_ds.Tables[0].Rows[_rowIndex]["TenKH"]); Common.SetValueExportByString(ref flcReport, "LoaiPhanAnh", p_ds.Tables[0].Rows[_rowIndex]["LoaiPA"]); Common.SetValueExportByString(ref flcReport, "SoDienThoai", p_ds.Tables[0].Rows[_rowIndex]["SoDT"]); Common.SetValueExportByString(ref flcReport, "TenDayDu", p_ds.Tables[0].Rows[_rowIndex]["TenDayDu"]); Common.SetValueExportByString(ref flcReport, "ThoiGianPhanAnh", ((DateTime)p_ds.Tables[0].Rows[_rowIndex]["TGPA"]).ToString("HH:MM - dd/MM/yyyy ")); Common.SetValueExportByString(ref flcReport, "ThoiGianPS", ((DateTime)p_ds.Tables[0].Rows[_rowIndex]["TGPS"]).ToString("HH:MM - dd/MM/yyyy ")); Common.SetValueExportByString(ref flcReport, "LoTrinhDi", p_ds.Tables[0].Rows[_rowIndex]["LTTu"]); Common.SetValueExportByString(ref flcReport, "LoTrinhDen", p_ds.Tables[0].Rows[_rowIndex]["LTDen"]); Common.SetValueExportByString(ref flcReport, "TienDH", p_ds.Tables[0].Rows[_rowIndex]["DHT"]); Common.SetValueExportByString(ref flcReport, "DacDiem", p_ds.Tables[0].Rows[_rowIndex]["DacDiem"]); Common.SetValueExportByString(ref flcReport, "DoiTuong", p_ds.Tables[0].Rows[_rowIndex]["DoiTuong"]); Common.SetValueExportByString(ref flcReport, "GiaiQuyet", p_ds.Tables[0].Rows[_rowIndex]["GQ_KQGQ"]); Common.SetValueExportByString(ref flcReport, "GiaiQuyetYKKH", p_ds.Tables[0].Rows[_rowIndex]["GQ_YKKH"]); Common.SetValueExportByString(ref flcReport, "NoiDungKN", p_ds.Tables[0].Rows[_rowIndex]["NoiDung"]); Common.SetValueExportByString(ref flcReport, "DotLine", strDot); } } catch (Exception ex) { //ErrorLog.log.Error(ex.ToString()); } }
public void GerarExcel <T>(IList <T> aLista, string NomeFonte, string aCaminhoTemplate, string acaminhoArquivoSaida) { try { Report = new FlexCel.Report.FlexCelReport(true); ReportFile = new FlexCel.XlsAdapter.XlsFile(true); StreamRelatorio = new MemoryStream(); SetReport(aLista, NomeFonte); this.CreateFile(ReportFile, aCaminhoTemplate); Report.Run(ReportFile); ReportFile.Save(acaminhoArquivoSaida); } catch (Exception e) { throw e; } }
void Export() { try { #region Kết xuất FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _path = CommonData.ExcelDesignFilePath; string _fileExcelName = "Hoa_Don_Ban_Buon.xls"; DataSet ds_all = new DataSet(); DataTable _dt = ConvertData.ConvertToDatatable(c_list_Product_Buy); _dt.Columns.Add("STT"); int index = 1; foreach (DataRow item in _dt.Rows) { item["STT"] = index; index++; } ds_all.Tables.Add(_dt); ds_all.Tables[0].TableName = "BanBuon"; if (ds_all.Tables.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } if (ds_all.Tables[0].Rows.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } _path += _fileExcelName; CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all); CommonFunction.SetValueExportByString(ref flcReport, "SoHoaDon", txtSaleHeader.Text); CommonFunction.SetValueExportByString(ref flcReport, "Customer", txtCustomer_Name.Text); CommonFunction.SetValueExportByString(ref flcReport, "Address", txtAddress.Text); string _tienBangChu = ConvertData.ConvertMoneyToStr(Convert.ToDouble(txtPayPrice.Text)); CommonFunction.SetValueExportByString(ref flcReport, "TienBangChu", _tienBangChu); System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog(); saveReport.Filter = "Excel files (*.xls)|*.xls"; if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CommonFunction.ExportExcel(flcReport, _path, saveReport); } #endregion } catch (Exception ex) { CommonData.log.Error(ex.ToString()); } }
public static void ExportExcel(FlexCel.Report.FlexCelReport flcReport, string pathFile, System.Windows.Forms.SaveFileDialog saveReport) { FileStream _templateStream = null; try { flcReport.DeleteEmptyRanges = false; string _path = ""; string exepath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase).Replace(@"file:\", ""); MemoryStream _xlsMemoryStream = new MemoryStream(); #region Luu file xls len memory stream _path = exepath + "\\" + pathFile.Replace("//", "\\"); bool ckFileIsNotOpen = true; //check xem file co ton tai trong duong dan ko? if (!File.Exists(exepath + "\\" + pathFile.Replace("//", "\\"))) { //bao loi ko ton tai file; //NoteBox.Show("File excel thiết kế không tồn tại trong thư mục Run/report. Kết xuất không thành công.", "Thông báo", NoteBoxLevel.Note); } else { //check file co dang mo hay ko try { var stream = new FileStream(_path, FileMode.Open, FileAccess.Read); stream.Close(); } catch { //NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error); ckFileIsNotOpen = false; } if (ckFileIsNotOpen == true) { _templateStream = new System.IO.FileStream(_path, System.IO.FileMode.Open); flcReport.Run(_templateStream, _xlsMemoryStream); //luu file _xlsMemoryStream.Position = 0; byte[] bytes = new byte[_xlsMemoryStream.Length]; _xlsMemoryStream.Read(bytes, 0, (int)_xlsMemoryStream.Length); try { FileStream OutStream; OutStream = new FileStream(saveReport.FileName, FileMode.Create); OutStream.Write(bytes, 0, bytes.Length); OutStream.Close(); _xlsMemoryStream.Close(); _templateStream.Close(); //Neu chon mo file da luu //MessageBoxResult result = NoteBox.Show("Bạn có muốn mở file vừa chọn kết xuất không?", "Thông báo", NoteBoxLevel.Question); //if (result == MessageBoxResult.Yes) //{ // Process.Start(saveReport.FileName); //} } catch (Exception) { _templateStream.Close(); //NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error); //Mouse.OverrideCursor = null; return; } } } #endregion } catch (Exception ex) { _templateStream.Close(); //NoteBox.Show("Kết xuất file excel không thành công", "Thông báo", NoteBoxLevel.Error); } }
private void GenerateReportButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(); SqlCommand cmd = new SqlCommand(); if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\Output\\")) { Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\Output\\"); } try { conn.ConnectionString = ConnectionStringTextBox.Text; conn.Open(); cmd.Connection = conn; if (UnknownSummaryMRIActualsRadio.Checked) { #region UnknownSummaryMRIActuals cmd.CommandTimeout = 0; cmd.CommandText = "stp_S_UnknownSummaryMRIActuals"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text)); cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text))); //@BudgetYear int, //@BudgetQuater Varchar(2), //@DataPriorToDate DateTime, //@StartPeriod int, //@EndPeriod int DataSet result = GetUnknownSummaryMRIActualsDataSet(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { DataRow newRow = result.Tables[0].NewRow(); //newRow[""] = reader[""]; newRow["SourceCode"] = reader["SourceCode"]; newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"]; newRow["ReferenceCode"] = reader["ReferenceCode"]; newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"]; newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"]; newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"]; newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"]; newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"]; newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"]; newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"]; newRow["InValidOriginatingRegionAndFunctionalDepartment"] = reader["InValidOriginatingRegionAndFunctionalDepartment"]; newRow["InValidActivityTypeAndEntity"] = reader["InValidActivityTypeAndEntity"]; newRow["ResolvedBy"] = reader["ResolvedBy"]; newRow["Gr MajorCategoryName"] = reader["Gr MajorCategoryName"]; newRow["Gr MinorCategoryName"] = reader["Gr MinorCategoryName"]; newRow["Gr AccountSubTypeName"] = reader["Gr AccountSubTypeName"]; newRow["Gr FeeOrExpense"] = reader["Gr FeeOrExpense"]; newRow["Gr ReportingEntityName"] = reader["Gr ReportingEntityName"]; newRow["Gr ActivityTypeName"] = reader["Gr ActivityTypeName"]; newRow["Gr OriginatingRegionName"] = reader["Gr OriginatingRegionName"]; newRow["Gr OriginatingSubRegionName"] = reader["Gr OriginatingSubRegionName"]; newRow["Gr AllocationRegionName"] = reader["Gr AllocationRegionName"]; newRow["Gr AllocationSubRegionName"] = reader["Gr AllocationSubRegionName"]; newRow["Gr OverheadName"] = reader["Gr OverheadName"]; newRow["Gr GlAccountCode"] = reader["Gr GlAccountCode"]; newRow["Gr GlAccountName"] = reader["Gr GlAccountName"]; newRow["Gr FunctionalDepartmentName"] = reader["Gr FunctionalDepartmentName"]; newRow["Period"] = reader["Period"]; newRow["Ref"] = reader["Ref"]; newRow["SiteID"] = reader["SiteID"]; newRow["EntityID"] = reader["EntityID"]; newRow["EntityName"] = reader["EntityName"]; newRow["GlAccountCode"] = reader["GlAccountCode"]; newRow["GlAccountName"] = reader["GlAccountName"]; newRow["Department"] = reader["Department"]; newRow["DepartmentDescription"] = reader["DepartmentDescription"]; newRow["JobCode"] = reader["JobCode"]; newRow["JobCodeDescription"] = reader["JobCodeDescription"]; newRow["Amount"] = reader["Amount"]; newRow["Description"] = reader["Description"]; newRow["EnterDate"] = reader["EnterDate"]; newRow["Reversal"] = reader["Reversal"]; newRow["Status"] = reader["Status"]; newRow["Basis"] = reader["Basis"]; newRow["UserId"] = reader["UserId"]; newRow["CorporateDepartmentCode"] = reader["CorporateDepartmentCode"]; newRow["Source"] = reader["Source"]; result.Tables[0].Rows.Add(newRow); } //Mapp DataSet to Flexcel FlexCel.Report.FlexCelReport unknownSummaryMRIActuals = new FlexCel.Report.FlexCelReport(); unknownSummaryMRIActuals.ClearUserFunctions(); unknownSummaryMRIActuals.AddTable(result); unknownSummaryMRIActuals.SetValue("Date", DateTime.Now); MemoryStream OutStream = new MemoryStream(); try { FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryMRIActualsTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite); try { unknownSummaryMRIActuals.Run(InStream, OutStream); } finally { InStream.Close(); } string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryMRIActuals.xlsx"; FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew); BinaryWriter writeBinay = new BinaryWriter(streamWriter); writeBinay.Write(OutStream.ToArray()); writeBinay.Close(); } finally { OutStream.Close(); } #endregion } else if (UnknownSummaryAllocatedOverheadRadio.Checked) { #region UnknownSummaryAllocatedOverhead cmd.CommandTimeout = 0; cmd.CommandText = "stp_S_UnknownSummaryAllocatedOverhead"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text)); cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text))); cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text))); //@BudgetYear int, //@BudgetQuater Varchar(2), //@DataPriorToDate DateTime, //@StartPeriod int, //@EndPeriod int DataSet result = GetUnknownSummaryAllocatedOverheadDataSet(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { DataRow newRow = result.Tables[0].NewRow(); //newRow[""] = reader[""]; newRow["SourceCode"] = reader["SourceCode"]; newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"]; newRow["ReferenceCode"] = reader["ReferenceCode"]; newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"]; newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"]; newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"]; newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"]; newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"]; newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"]; newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"]; newRow["InValidRegionAndFunctionalDepartment"] = reader["InValidRegionAndFunctionalDepartment"]; newRow["InValidActivityTypeEntity"] = reader["InValidActivityTypeEntity"]; newRow["ResolvedBy"] = reader["ResolvedBy"]; newRow["ExpensePeriod"] = reader["ExpensePeriod"]; newRow["AllocationRegionCode"] = reader["AllocationRegionCode"]; newRow["AllocationRegionName"] = reader["AllocationRegionName"]; newRow["OriginatingRegionCode"] = reader["OriginatingRegionCode"]; newRow["OriginatingRegionSourceCode"] = reader["OriginatingRegionSourceCode"]; newRow["PropertyFundName"] = reader["PropertyFundName"]; newRow["FunctionalDepartmentCode"] = reader["FunctionalDepartmentCode"]; newRow["ActivityTypeCode"] = reader["ActivityTypeCode"]; newRow["ForeignCurrency"] = reader["ForeignCurrency"]; newRow["ForeignActual"] = reader["ForeignActual"]; newRow["GlAccountCode"] = reader["GlAccountCode"]; newRow["EmployeeDisplayName"] = reader["EmployeeDisplayName"]; result.Tables[0].Rows.Add(newRow); } //Mapp DataSet to Flexcel FlexCel.Report.FlexCelReport unknownSummaryAllocatedOverhead = new FlexCel.Report.FlexCelReport(); unknownSummaryAllocatedOverhead.ClearUserFunctions(); unknownSummaryAllocatedOverhead.AddTable(result); unknownSummaryAllocatedOverhead.SetValue("Date", DateTime.Now); MemoryStream OutStream = new MemoryStream(); try { FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryAllocatedOverheadTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite); try { unknownSummaryAllocatedOverhead.Run(InStream, OutStream); } finally { InStream.Close(); } string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryAllocatedOverhead.xlsx"; FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew); BinaryWriter writeBinay = new BinaryWriter(streamWriter); writeBinay.Write(OutStream.ToArray()); writeBinay.Close(); } finally { OutStream.Close(); } #endregion } else { throw new ApplicationException("Invalid Radio Selection"); } MessageBox.Show("Export Complete", "Exporter", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (conn != null) { conn.Dispose(); } } }
public static int ExportExcel(FlexCel.Report.FlexCelReport flcReport, string pathFileSource, string c_fileExport, ref string _err)//, System.Windows.Forms.SaveFileDialog saveReport) { System.IO.FileStream _templateStream = null; try { flcReport.DeleteEmptyRanges = false; string _path_source = ""; //string exepath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase).Replace(@"file:\", ""); MemoryStream _xlsMemoryStream = new MemoryStream(); #region Luu file xls len memory stream _path_source = pathFileSource.Replace("//", "\\"); bool ckFileIsNotOpen = true; //check xem file co ton tai trong duong dan ko? if (!System.IO.File.Exists(_path_source))//if (!System.IO.File.Exists(exepath + "\\" + pathFile.Replace("//", "\\"))) { //bao loi ko ton tai file; _err = "Không tồn tại file mẫu trong thư mục mẫu!"; return(-3); // không có file mẫu } else { //check file co dang mo hay ko try { var stream = new FileStream(_path_source, FileMode.Open, FileAccess.Read); stream.Close(); } catch { //NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error); ckFileIsNotOpen = false; _err = "File đang được mở trong process khác! Không thể tạo báo cáo!"; return(-2); // file đang mo không làm j đc } if (ckFileIsNotOpen == true) { _templateStream = new System.IO.FileStream(_path_source, System.IO.FileMode.Open); flcReport.Run(_templateStream, _xlsMemoryStream); //luu file _xlsMemoryStream.Position = 0; byte[] bytes = new byte[_xlsMemoryStream.Length]; _xlsMemoryStream.Read(bytes, 0, (int)_xlsMemoryStream.Length); try { FileStream OutStream; OutStream = new FileStream(c_fileExport, FileMode.Create);//new FileStream(saveReport.FileName, FileMode.Create); OutStream.Write(bytes, 0, bytes.Length); OutStream.Close(); _xlsMemoryStream.Close(); _templateStream.Close(); return(0); } catch (Exception ex) { NaviCommon.Common.log.Error(ex.ToString()); _templateStream.Close(); _err = "Lỗi tạo file báo cáo !"; return(-1); // lỗi tạo file } } _err = "File đang được mở trong process khác! Không thể tạo báo cáo!"; return(-2); // file đang mo không làm j đc } #endregion } catch (Exception ex) { NaviCommon.Common.log.Error(ex.ToString()); _templateStream.Close(); _err = "Lỗi tạo file báo cáo !"; return(-1); } }
void Export() { try { #region Kết xuất FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _path = CommonData.ExcelDesignFilePath; string _fileExcelName = "Report_Fee.xls"; foreach (Fee_Report_Info item in c_lst) { if (item.Currency == (decimal)Enum_Contract_Currency.VND) { item.PhiMoiGio_VND = item.PhiMoiGio; item.TienChuaVe_VND = item.TienChuaVe; item.TienDaVe_VND = item.TienDaVe; item.PhiMoiGio_USD = 0; item.TienChuaVe_USD = 0; item.TienDaVe_USD = 0; } else { item.PhiMoiGio_USD = item.PhiMoiGio; item.TienChuaVe_USD = item.TienChuaVe; item.TienDaVe_USD = item.TienDaVe; item.PhiMoiGio_VND = 0; item.TienChuaVe_VND = 0; item.TienDaVe_VND = 0; } } DataSet ds_all = new DataSet(); DataTable _dt = ConvertData.ConvertToDatatable(c_lst); _dt.Columns.Add("STT"); int index = 1; foreach (DataRow item in _dt.Rows) { item["STT"] = index; index++; } ds_all.Tables.Add(_dt); ds_all.Tables[0].TableName = "Fee"; if (ds_all.Tables.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } if (ds_all.Tables[0].Rows.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } _path += _fileExcelName; CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all); System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog(); saveReport.Filter = "Excel files (*.xls)|*.xls"; if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CommonFunction.ExportExcel(flcReport, _path, saveReport); } #endregion } catch (Exception ex) { ErrorLog.log.Error(ex.ToString()); } }
void Export_Payment() { try { Fee_Report_Info _Fees_Revenue_Info = (Fee_Report_Info)dgrContract.SelectedItem; if (_Fees_Revenue_Info == null) { return; } Customer_Info _Customer_Info = _Customer_Controller.Customer_GetById(_Fees_Revenue_Info.Object_Id); Estate_Object_Info _Estate_Object_Info = _Estate_Object_Controller.Estate_Object_GetById(_Fees_Revenue_Info.Estate_Id, _Fees_Revenue_Info.Contract_Type); #region Kết xuất FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _path = CommonData.ExcelDesignFilePath; string _fileExcelName = "Payment_Request_VND.xls"; if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD) { _fileExcelName = "Payment_Request_USD.xls"; } _path += _fileExcelName; CommonFunction.SetValueExportByString(ref flcReport, "CurrentDate", DateTime.Now.ToString("dd/MM/yyyy")); CommonFunction.SetValueExportByString(ref flcReport, "Tenat_Name", _Customer_Info.Customer_Name); CommonFunction.SetValueExportByString(ref flcReport, "Address", _Customer_Info.Address); CommonFunction.SetValueExportByString(ref flcReport, "Users", _Fees_Revenue_Info.Users); CommonFunction.SetValueExportByString(ref flcReport, "TaxCode", _Customer_Info.Tax_Code); CommonFunction.SetValueExportByString(ref flcReport, "Estate_Name", _Estate_Object_Info.Estate_Name); CommonFunction.SetValueExportByString(ref flcReport, "Contract_FromDate", _Fees_Revenue_Info.Contract_FromDate.ToString("dd/MM/yyyy")); CommonFunction.SetValueExportByString(ref flcReport, "Contract_ToDate", _Fees_Revenue_Info.Contract_ToDate.ToString("dd/MM/yyyy")); //decimal _VAT_Fee = Math.Round(_Fees_Revenue_Info.Fee_Expected * 10 / 100); //decimal _Fee = _Fees_Revenue_Info.Fee_Expected - _VAT_Fee; //decimal _p = 110 / 100; //decimal _Fee = _Fees_Revenue_Info.Fee_Expected / _p; //decimal _VAT_Fee = Math.Round(_Fee * 10 / 100); decimal _Fee = (_Fees_Revenue_Info.Fee_Expected / 110) * 100; decimal _VAT_Fee = Math.Round(_Fee * 10 / 100); CommonFunction.SetValueExportByString(ref flcReport, "Fee", _Fee.ToString("###,##0")); CommonFunction.SetValueExportByString(ref flcReport, "VAT_Fee", _VAT_Fee.ToString("###,##0")); CommonFunction.SetValueExportByString(ref flcReport, "Total_Fee", _Fees_Revenue_Info.Fee_Expected.ToString("###,##0")); if (_Fees_Revenue_Info.Contract_Type == (decimal)Enum_Contract_Type.Tenant) { #region Kỳ thanh toán string _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy"); if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One) { _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy"); } else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One_Month) { _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddMonths(1).ToString("dd/MM/yyyy"); } else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.Three_Month) { _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddMonths(3).ToString("dd/MM/yyyy"); } else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.Six_Month) { _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddMonths(6).ToString("dd/MM/yyyy"); } else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One_Year) { _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment + " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " + _Fees_Revenue_Info.HanChuyenTien.AddYears(1).ToString("dd/MM/yyyy"); } CommonFunction.SetValueExportByString(ref flcReport, "KyThanhToan", _Kytt); #endregion } else { CommonFunction.SetValueExportByString(ref flcReport, "KyThanhToan", ""); } DateTime _dt_from = DateTime.Now; DateTime _dt_To = _dt_from.AddDays(30); CommonFunction.SetValueExportByString(ref flcReport, "From", _dt_from.ToString("dd/MM/yyyy")); CommonFunction.SetValueExportByString(ref flcReport, "To", _dt_To.ToString("dd/MM/yyyy")); //CommonFunction.SetValueExportByString(ref flcReport, "From", _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy")); //CommonFunction.SetValueExportByString(ref flcReport, "To", _Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy")); if (_Fees_Revenue_Info.Price != 0) { CommonFunction.SetValueExportByString(ref flcReport, "Price", _Fees_Revenue_Info.Price.ToString("###,##0")); } else { CommonFunction.SetValueExportByString(ref flcReport, "Price", ""); } string _tienBangChu = ConvertData.ConvertMoneyToStr(Convert.ToDouble(_Fees_Revenue_Info.Fee_Expected)); if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD) { _tienBangChu = c_Contract_Controller.Convert_Dola(_Fees_Revenue_Info.Fee_Expected); } if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD) { CommonFunction.SetValueExportByString(ref flcReport, "VND", _Fees_Revenue_Info.Fee_Vnd.ToString("###,##0")); } CommonFunction.SetValueExportByString(ref flcReport, "BangChu", _tienBangChu.ToLower()); System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog(); saveReport.Filter = "Excel files (*.xls)|*.xls"; if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CommonFunction.ExportExcel(flcReport, _path, saveReport); } #endregion } catch (Exception ex) { ErrorLog.log.Error(ex.ToString()); } }
void Export() { try { #region Kết xuất FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport(); string _path = CommonData.ExcelDesignFilePath; string _fileExcelName = "Contract_Report.xls"; DataSet ds_all = new DataSet(); DataTable _dt = ConvertData.ConvertToDatatable(c_lst); _dt.Columns.Add("STT"); int index = 1; foreach (DataRow item in _dt.Rows) { item["STT"] = index; index++; } ds_all.Tables.Add(_dt); ds_all.Tables[0].TableName = "Contract"; if (ds_all.Tables.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } if (ds_all.Tables[0].Rows.Count <= 0) { Mouse.OverrideCursor = null; NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note); return; } _path += _fileExcelName; string _title_report = ""; string _status = cboStatus.SelectedValue.ToString(); if (_status == ((decimal)Enum_Contract_Status.Het_Han).ToString()) { _title_report = "BÁO CÁO HỢP ĐỒNG HẾT HẠN"; } else if (_status == ((decimal)Enum_Contract_Status.Gia_Han).ToString()) { _title_report = "BÁO CÁO HỢP ĐỒNG RA HẠN"; } else if (_status == ((decimal)Enum_Contract_Status.Den_Han_Thong_Bao).ToString()) { _title_report = "BÁO CÁO HỢP ĐỒNG ĐẾN HẠN THÔNG BÁO"; } else if (_status == ((decimal)Enum_Contract_Status.Dong_Trc_Thoi_Han).ToString()) { _title_report = "BÁO CÁO HỢP ĐỒNG ĐÓNG TRƯỚC THỜI HẠN"; } else { _title_report = "BÁO CÁO HỢP ĐỒNG"; } CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all); CommonFunction.SetValueExportByString(ref flcReport, "title_report", _title_report); System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog(); saveReport.Filter = "Excel files (*.xls)|*.xls"; if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK) { CommonFunction.ExportExcel(flcReport, _path, saveReport); } #endregion } catch (Exception ex) { ErrorLog.log.Error(ex.ToString()); } }