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 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"));
}
}
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 static void SetValueExportByDataTable(ref FlexCel.Report.FlexCelReport flcReport, DataSet v_ds)
{
try
{
flcReport.AddTable(v_ds);
}
catch
{
}
}
private void SetReport <T>(IList <T> aLista, string NomeFonte)
{
Report.SetValue("Empresa", "LCorp S.A.");
Report.SetValue("PaginaXdeY", string.Empty);
Report.SetValue("DataSistema", DateTime.Now.ToShortDateString());
Report.AddTable(NomeFonte, aLista);
//Adiciona a função de BuscarContexto, para que possam ser traduzido os termos
Report.SetUserFunction("BuscarContexto", new BuscarContexto());
}
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());
}
}
/// <summary>
/// This is the method that will be called by the ASP.NET front end. It returns an array of bytes
/// with the report data, so the ASP.NET application can stream it to the client.
/// </summary>
/// <returns>The generated file as a byte array.</returns>
public byte[] WebRun(Stream DataStream, string FileName)
{
XlsReport.SetValue("Date", DateTime.Now);
XlsReport.SetValue("FileName", FileName);
DataSet Data = new DataSet();
DataTable ValidateResult = Data.Tables.Add("ValidateResult");
ValidateResult.Columns.Add("FileName", typeof(string));
ValidateResult.Columns.Add("CellRef", typeof(string));
ValidateResult.Columns.Add("ErrorType", typeof(string));
ValidateResult.Columns.Add("FunctionName", typeof(string));
DataTable CompareResult = Data.Tables.Add("CompareResult");
CompareResult.Columns.Add("SheetName", typeof(string));
CompareResult.Columns.Add("CellRef", typeof(string));
CompareResult.Columns.Add("CalcResult", typeof(string));
CompareResult.Columns.Add("XlsResult", typeof(string));
CompareResult.Columns.Add("Diff", typeof(string));
CompareResult.Columns.Add("FormulaText", typeof(string));
XlsReport.AddTable(Data);
XlsFile xls1 = new XlsFile();
XlsFile xls2 = new XlsFile();
xls1.Open(DataStream);
DataStream.Position = 0;
xls2.Open(DataStream);
CompareXls(xls1, xls2, CompareResult);
ValidateXls(xls1, ValidateResult);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
using (MemoryStream OutStream = new MemoryStream())
{
using (FileStream InStream = new FileStream(DataPath + "ValidateReport.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XlsReport.Run(InStream, OutStream);
return(OutStream.ToArray());
}
}
}
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();
}
}
}