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"));
}
}
/// <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());
}
}
}
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;
}
}
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);
}
}
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);
}
}
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();
}
}
}