// Willis
public ActionResult ExportExcel()
{
List <Item> DownloadableData = _itemDAO.GetDownloadableData();
ExcelReport excelReport = new ExcelReport();
byte[] ExcelData = excelReport.GenerateExcelReport(DownloadableData);
return(File(ExcelData, "application/xlsx", "Ordered Data.xlsx"));
}
public IActionResult Relatorio(MovimentacoesRelatorioModel model)
{
if (ModelState.IsValid)
{
var consulta = new MovimentacoesConsultaModel
{
DataInicio = model.DataInicio,
DataTermino = model.DataTermino
};
//consultando as movimentações
var dados = movimentacaoFinanceiraApplicationService.Consultar(consulta);
try
{
//verificar o tipo do relatorio
switch (model.TipoRelatorio)
{
case "EXCEL":
var relatorioExcel = ExcelReport.GenerateReport
(DateTime.Parse(model.DataInicio), DateTime.Parse(model.DataTermino), dados);
//download do arquivo
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Headers.Add("content-disposition", "attachment; filename=relatorio.xlsx");
Response.Body.WriteAsync(relatorioExcel, 0, relatorioExcel.Length);
Response.Body.Flush();
Response.StatusCode = StatusCodes.Status200OK;
break;
case "PDF":
var relatorioPDF = PDFReport.GenerateReport
(DateTime.Parse(model.DataInicio), DateTime.Parse(model.DataTermino), dados);
//download do arquivo
Response.Clear();
Response.ContentType = "application/pdf";
Response.Headers.Add("content-disposition", "attachment; filename=relatorio.pdf");
Response.Body.WriteAsync(relatorioPDF, 0, relatorioPDF.Length);
Response.Body.Flush();
Response.StatusCode = StatusCodes.Status200OK;
break;
}
}
catch (Exception e)
{
TempData["MensagemErro"] = e.Message;
}
}
return(View());
}
public ExcelReport GetReport()
{
var report = new ExcelReport();
report.SetReportType(_reportType);
report.SetReportHeader(_header);
report.SetReportContent(_content);
report.SetReportFooter(_footer);
return(report);
}
public void CreateReport(string filePath = null, string fileName = null)
{
IList <DriverInfo> sqliteDriversData = SQLiteWorker.GetDriverRouteData();
IList <VehicleInformation> mysqlVehiclesData = EntryPoint.GetAllVehicleInformation();
var averageSalaryReport = ExcelReport.GenerateAverageSalaryReport(sqliteDriversData);
var averageTravelledDistanceByVehicle = ExcelReport.GenerateAverageTravelledDistanceByVehicle(sqliteDriversData, mysqlVehiclesData);
var excelGenerator = new ExcelGenerator(filePath, fileName);
excelGenerator.Generate(averageSalaryReport, averageTravelledDistanceByVehicle);
}
/*
* below to BE removed!
* private async Task<ExcelReport<JobSummaryReportCriteria>> GetJobSummaryReportCriteria(bool isCurrentUserAdmin)
* {
* var vm = new JobSummaryReportCriteria();
* vm.PeriodSettings = GetDefaultPeriodSettings();
* vm.AvailableJobs = (await jobService.GetAsync()).OrderBy(x => x.FullJobCodeWithName).ToList();
* var rpt = new ExcelReport<JobSummaryReportCriteria>(JobSummaryReportCriteria.PROJECT_STATUS_REPORT_NAME, vm, isCurrentUserAdmin);
* return rpt;
* }
*/
private async Task <ExcelReport <QuickJobTimeReportCriteria> > GetJobDetailreportAsync()
{
var vm = new QuickJobTimeReportCriteria();
vm.PeriodSettings = GetDefaultPeriodSettings();
vm.AvailableJobs = (await jobService.GetAsync()).OrderBy(x => x.FullJobCodeWithName).ToList();
var rpt = new ExcelReport <QuickJobTimeReportCriteria>(QuickJobTimeReportCriteria.QUICK_JOB_TIME_REPORT_NAME, vm, true);
return(rpt);
}
private ExcelReport <PayPeriodReportCriteria> GetPayPeriodReportViewModel(bool isCurrentUserAdmin)
{
var vm2 = new PayPeriodReportCriteria();
vm2.PayPeriodEnd = WeekDTO.CreateWithWeekContaining(DateTime.Now).WeekEnd;
vm2.PayPeriodList = PayPeriodRepository.GetPPRange(rangeSize: 25);
var rpt = new ExcelReport <PayPeriodReportCriteria>(PayPeriodReportCriteria.PAY_PERIOD_REPORT_NAME, vm2, isCurrentUserAdmin);
return(rpt);
}
protected void generateAuditReports(DataSet dsData, string[] sheetnames, string[] titles, string fileName, string[][] cols, string[][] colsFormat)
{
DataSet dsFinal = new DataSet();
dsFinal.Clear();
dsData.Tables[0].TableName = "audTable";
dsFinal.Tables.Add(dsData.Tables[0].Copy());
dsFinal.Tables[0].TableName = "audTableF";
dsData.Clear();
ExcelReport.ExcelXMLRpt(dsFinal, fileName, sheetnames, titles, cols, colsFormat);
}
static void WriteExcelReport(FileInfo file, IEnumerable <Record> records, IEnumerable <Workout> workouts, Settings settings, ISettingsStore settingsStore, DateTimeZone zone)
{
using var excelFile = new ExcelPackage();
var customSheets = LoadCustom(settingsStore).Match(
Some: c => c,
None: Enumerable.Empty <ExcelWorksheet>());
ExcelReport.BuildReport(records.ToList(), workouts.ToList(), excelFile.Workbook, settings, zone, customSheets);
excelFile.SaveAs(file);
}
/// <summary>
/// 测试 读写.
/// </summary>
public void TestReadWrite()
{
Console.WriteLine("测试 Excel 读写 开始！");
string fileName =
System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase
+ @"ExcelFiles\测试读写.xls";
ExcelReport service = new ExcelReport();
// 打开 Excel.
service.OpenExcel();
// 打开 Excel 文件.
service.OpenExcelFile(fileName);
// 选择工作表.
service.SelectSheet("测试读写");
for (int i = 2; i <= 5; i++)
{
// 获取数据.
Console.WriteLine("{0}.{1}.{2}.{3}",
service.GetValue(i, 1),
service.GetValue(i, 2),
service.GetValue(i, 3),
service.GetValue(i, 4));
// 设置公式.
service.SetFormulaR1C1(i, 5, "=IF(测试读写!R" + i + "C4>=18,\"18+\",\"未成年\")");
// 设置数值.
service.SetValue(i, 6, "Test" + i);
}
// 保存 Excel 文件.
service.SaveExcelFile();
// 关闭 Excel.
service.CloseExcel();
Console.WriteLine("测试 Excel 读写 结束！");
}
public HttpResponseMessage ExportExcel(string eventID)
{
try
{
ExcelReport excelReport = new ExcelReport();
string url = excelReport.GetReportDataByEventId(eventID);
return(Request.CreateResponse(HttpStatusCode.OK, url));
}
catch (Exception ex)
{
return(Request.CreateResponse(HttpStatusCode.InternalServerError, ex));
}
}
public Result <MimeMessage> Process(MimeMessage originalEmail, IEnumerable <Tuple <string, byte[]> > attachments)
{
var theExportZip = GetExportZip(originalEmail, attachments);
var attachment = ExcelReport.CreateReport(theExportZip, _settings, _customSheets);
var attachmentName = $"export.{originalEmail.Date.Date:yyyy-MM-dd}.xlsx";
var reply = MailUtility.ConstructReply(originalEmail, new MailboxAddress(_from), builder =>
{
builder.TextBody = @"Hey there, I saw your health data... good work!";
builder.Attachments.Add(attachmentName, attachment);
});
return(Result.Success(reply));
}
private void ArrayDataToGraphData(double[,] value, Word.Chart oChart, string[] rowHeader = null, string[] colHeader = null, bool firstColumnAsXAxis = true)
{
string data;
string rc;
string rHeader;
string cHeader;
Excel.Worksheet series = ((Excel.Workbook)oChart.ChartData.Workbook).Worksheets[1];
Excel.ListObject list = series.ListObjects[1];
string colIdx = firstColumnAsXAxis ? "B" : "A";
list.Resize(series.Cells.get_Range(colIdx + "1", ExcelReport.IntToLetter(value.GetLength(1) + 1) + (value.GetLength(0) + 1).ToString()));
for (int i = 0; i < value.GetLength(0); i++)
{
rHeader = "A" + (i + 2).ToString();
if (rowHeader != null)
{
series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = rowHeader[i];
}
else
{
series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = (i).ToString();
}
for (int j = 0; j < value.GetLength(1); j++)
{
data = value[i, j].ToString();
rc = ExcelReport.IntToLetter(j + 2) + (i + 2).ToString();
series.Cells.get_Range(rc, _missing).FormulaR1C1 = data;
cHeader = ExcelReport.IntToLetter(j + 2) + "1";
if (i == 0)
{
if (colHeader != null)
{
series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = colHeader[j];
}
else
{
series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = "Column" + (j).ToString();
}
}
}
}
series.Activate();
((Excel.Workbook)oChart.ChartData.Workbook).Close();
Thread.Sleep(5);
}
protected void btn_genRpt_Click(object sender, EventArgs e)
{
string yrmo = ddlYrmo.SelectedItem.Text;
DataSet ds = new DataSet();
DataSet dsFinal = new DataSet();
dsFinal.Clear();
string[][] cols = { new string[] { "Reconciliation Type", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance" },
new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"},
new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"},
new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "EBA Count", "EBA Amount", "Anthem Count", "Anthem Amount", "EBA Count Variance", "EBA vs Anthem % Count Variance", "Threshold", "Threshold Level"} };
string[][] colsFormat = { new string[] { "string", "number", "decimal", "number", "decimal", "number" },
new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"},
new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"},
new string[] { "string", "string", "string", "number", "decimal", "number", "decimal", "number", "decimal", "decimal", "string"} };
string[] sheetnames = { "Summary", "Active", "Retiree", "Cobra" };
string[] titles = { "Domestic Billing Reconciliation Summary for YRMO - " + yrmo, "Active Billing Reconciliation for YRMO - " + yrmo, "Retiree Billing Reconciliation for YRMO - " + yrmo, "Cobra Billing Reconciliation for YRMO - " + yrmo };
try
{
ds = ReconDAL.GetDomSummary(yrmo);
ds.Tables[0].TableName = "sumTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[0].TableName = "sumTableF";
ds.Clear();
ds = ReconDAL.GetDomReconData(yrmo, "ACT");
ds.Tables[0].TableName = "actTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[1].TableName = "actTableF";
ds.Clear();
ds = ReconDAL.GetDomReconData(yrmo, "RET");
ds.Tables[0].TableName = "retTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[2].TableName = "retTableF";
ds.Clear();
ds = ReconDAL.GetDomReconData(yrmo, "COB");
ds.Tables[0].TableName = "cobTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[3].TableName = "cobTableF";
ds.Clear();
ExcelReport.ExcelXMLRpt(dsFinal, "DomesticRecon_" + yrmo, sheetnames, titles, cols, colsFormat);
}
catch (Exception ex)
{
MultiView1.SetActiveView(view_main);
lbl_error.Text = "Error in generating excel report" + ex.Message;
}
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
//string pFromBarcode = dtpFromDate.Value.ToString("yyMMdd") + txtFromBarcode.Text.Trim().PadLeft(4, '0') +
// ".NB";
//string pToDate = dtpFromDate.Value.ToString("yyMMdd") + txtToBarcode.Text.Trim().PadLeft(4, '0') +
// ".NB";
int testTypeID = Utility.Int32Dbnull(Utilities.GetPropertyValue(cboTestType, "SelectedValue"), -1);
var ds =
SPs.GtvtBaoCaoLuuNoBarCodeThanhNhan(dtpFromDate.Value.Date.ToString("yyyy/MM/dd"),
dtpToDate.Value.Date.ToString("yyyy/MM/dd"),
testTypeID).
GetDataSet();
if (Utility.Int32Dbnull(cboBarcodeType.SelectedIndex) == 0)
{
dtRawResult = ds.Tables[2];
}
else if (Utility.Int32Dbnull(cboBarcodeType.SelectedIndex) == 1)
{
dtRawResult = ds.Tables[1];
}
else if (Utility.Int32Dbnull(cboBarcodeType.SelectedIndex) == 2)
{
dtRawResult = ds.Tables[0];
}
var dataTable = new DataTable();
dataTable = Utilities.GetInversedDataTable(dtRawResult, "Para_Name", "Barcode", "Test_Result", "", false);
if (dataTable.Rows.Count <= 0)
{
Utility.ShowMsg("Không tìm thấy kết quả !");
return;
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
GridEXColumn grdCol = new GridEXColumn(dataTable.Columns[i].ColumnName);
grdAllPatientInfoAndResult.RootTable.Columns.Add(grdCol);
//grdAllPatientInfoAndResult.RootTable.Columns[grdCol.Key].AggregateFunction = AggregateFunction.Count;
}
// grdAllPatientInfoAndResult.RootTable.Columns["Patient_ID"].Visible = false;
//SetPropertiesCol("Barcode", "Barcode");
// SetPropertiesCol("Test_Date", "Ngày Xét Nghiệm");
// SetPropertiesCol("TestType_Name", "Loại XN");
grdAllPatientInfoAndResult.DataSource = dataTable;
grdAllPatientInfoAndResult.AutoSizeColumns();
ExcelReport.ExportGridEx(grdAllPatientInfoAndResult);
}
public int isGovActivity(string organizations, string strDate, string endDate, int repType, string membNo, string lang, string fileName)
{ // get all learner activity for CCOHS within selected organizations
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["v8server"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "dbo.spXisGovActivity";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@organizations", organizations));
cmd.Parameters.Add(new SqlParameter("@strDate", strDate));
cmd.Parameters.Add(new SqlParameter("@endDate", endDate));
cmd.Parameters.Add(new SqlParameter("@repType", repType));
Int32 rowCount = 0;
using (SqlDataReader drd = cmd.ExecuteReader())
// count all records
{
while (drd.Read())
{
rowCount = drd.GetInt32(0);
}
// get all records
if (drd.NextResult())
{
while (drd.Read())
{
// generate an excel or csv file
if (rowCount <= 5000)
{
// pass the drd to the excel writer and return count in JSON
ExcelReport report = null;
report = new ExcelReport(membNo, lang, fileName);
rowCount = report.isGovActivityReport(drd);
}
else
{
CsvReport csvReport = new CsvReport();
rowCount = csvReport.generate(membNo, lang, fileName, drd);
}
}
}
}
con.Close();
return rowCount;
}
protected void printReport(string _yrmo)
{
DataSet ds = new DataSet();
DataSet dsFinal = new DataSet();
dsFinal.Clear();
ClaimsRecon repObj = new ClaimsRecon();
string[][] cols = { new string[] { "YRMO", "Aging Report", "Claim ID", "Amount", "Source", "Notes", "User", "Date" },
new string[] { "YRMO", "Aging Report", "Claim ID", "Amount", "Source", "Notes", "User", "Date" },
new string[] { "YRMO", "Aging Report", "Claim ID", "Amount", "Source", "Notes", "User", "Date" },
new string[] { "YRMO", "Aging Report", "Claim ID", "Amount", "Source", "Notes", "User", "Date" } };
string[][] colsFormat = { new string[] { "string", "string", "string", "decimal", "string", "string", "string", "string" },
new string[] { "string", "string", "string", "decimal", "string", "string", "string", "string" },
new string[] { "string", "string", "string", "decimal", "string", "string", "string", "string" },
new string[] { "string", "string", "string", "decimal", "string", "string", "string", "string" } };
string[] sheetnames = { "MismatchAmounts_CF_Adjustments", "UnmatchedAnthem_CF_Adjustments", "UnmatchedBOA_CF_Adjustments", "Duplicate_CF_Adjustments" };
string[] titles = { "Mismatch Amount - CarryForward Adjustments for YRMO - " + _yrmo, "Unmatched Checks from Anthem - CarryForward Adjustments for YRMO - " + _yrmo, "Unmatched Checks from BOA - CarryForward Adjustments for YRMO - " + _yrmo, "Duplicate Check CarryForwards Adjustments for YRMO - " + _yrmo };
try
{
ds = repObj.generateAuditAdjReport(_yrmo, "MismatchAmounts");
ds.Tables[0].TableName = "amtMisTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[0].TableName = "amtMisTableF";
ds.Clear();
ds = repObj.generateAuditAdjReport(_yrmo, "AnthemMismatch");
ds.Tables[0].TableName = "anthMisTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[1].TableName = "anthMisTableF";
ds.Clear();
ds = repObj.generateAuditAdjReport(_yrmo, "BOAMismatch");
ds.Tables[0].TableName = "boaMisTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[1].TableName = "boaMisTableF";
ds.Clear();
ds = repObj.generateAuditAdjReport(_yrmo, "DuplicateChecks");
ds.Tables[0].TableName = "dupsTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[1].TableName = "dupsTableF";
ds.Clear();
ExcelReport.ExcelXMLRpt(dsFinal, "CA_CF_Recon_Adjustments_" + _yrmo, sheetnames, titles, cols, colsFormat);
}
finally
{
}
}
public virtual void exportToExcel(Stream os, bool pageBroken, int dispRatio)
{
ExcelReport report = null;
if (!pageBroken)
{
report = new ExcelReport(Cells, 999999999, 999999999);
}
else
{
report = new ExcelReport(Cells);
}
report.DispRatio = dispRatio;
report.out_Renamed(os);
os.Flush();
}
public void TestManyData()
{
var excelReport = new ExcelReport("Test many data", "Unknown", "sheet 1");
excelReport.AddEmptyRows(5);
excelReport.AddLogo(0, 0);
for (int rowIndex = 1; rowIndex < 30000; rowIndex++)
{
for (int columnIndex = 1; columnIndex < 20; columnIndex++)
{
excelReport.AddCell(rowIndex * columnIndex, columnIndex, rowIndex, TextStyle.NormalText);
}
}
excelReport.SaveToExcelFile(@"c:\\TestManyData.xls");
}
private void ImportFromMySqlAndSqliteToExcel(object sender, EventArgs e)
{
try
{
var db = new TravelAgencyDbContext();
var reader = new ReadFromSQLite();
var excelReportsToAdd = new ExcelReport(reader);
var reports = excelReportsToAdd.GenerateReports(db);
var excelWriter = new WritteDataToExcel();
excelWriter.WritteDestinationInExcel(reports);
MessageBox.Show("Excel report was succesfully generated with the data from MySQL and SQLite1");
}
catch (Exception)
{
MessageBox.Show("Excel report couldn't be generated!");
}
}
public ActionResult CreateExcel(DevOpsReportParams @params)
{
try
{
var config = GetConfig();
var report = new DevOpsReport(config, GetCustomFieldsFile());
report.ExecMainReport(@params);
var excel = new ExcelReport();
var criteria = new Dictionary <string, string>
{
{ "Период", $"{@params.Start:dd.MM.yyyy} - {(@params.End ?? DateTime.Now.Date):dd.MM.yyyy}" },
{ "Сотрудники", string.Join(", ", config.Employees.Where(x => @params.Employees.Contains(x.Name)).Select(x => x.NameShort).OrderBy(x => x)) },
};
excel.AddList(report.ActivityReport, criteria);
excel.AddList(report.TaskReport, criteria);
excel.AddList(report.WorkingTimeReport, criteria);
excel.AddList(report.WorkingTimeDiffReport, criteria);
var bytes = excel.GetXlsFile();
var key = $"Execl{Guid.NewGuid().ToString()}";
TempData[key] = new TmpFile
{
Content = bytes,
FileName = "Аналитический отчет.xlsx"
};
return(new JsonNetResult
{
Data = new
{
key = key
}
});
}
catch (Exception e)
{
return(new ExceptionResult(e));
}
}
private void AddSalesToReport(OleDbDataReader reader, ExcelReport report)
{
using (reader)
{
while (reader.Read())
{
string productName = reader[0].ToString();
if (productName != "Total sum")
{
int quantity = int.Parse(reader[1].ToString());
decimal unitPrice = decimal.Parse(reader[2].ToString());
decimal sum = quantity * unitPrice;
var sale = new Sale(productName, quantity, unitPrice, sum);
report.AddSale(sale);
}
}
}
}
protected void download_Click1(object sender, EventArgs e)
{
List <TimeSheetTicket> list = new List <TimeSheetTicket>();
int invoiceID = int.Parse(hidtsID.Value);
InvoiceEntity invoice = iapp.GetInvoice(invoiceID);
if (invoice.ProposalId > 0)
{
list = tsApp.GetTimesheetByProposalId(invoice.ProposalId);
}
else
{
list = tsApp.GetTimesheet(invoiceID);
}
ExcelReport report = new ExcelReport();
report.ExportInvoice(list);
}
public void TestManyDataWithBorder()
{
var excelReport = new ExcelReport("Test many data with border", "Unknown", "sheet 1");
excelReport.AddEmptyRows(5);
excelReport.AddLogo(0, 0);
for (int rowIndex = 1; rowIndex < 30000; rowIndex++)
{
var row = excelReport.GetNextRow();
for (int columnIndex = 1; columnIndex < 20; columnIndex++)
{
excelReport.AddCell(rowIndex * columnIndex, columnIndex, row, TextStyle.NormalText, HSSFColor.WHITE.index, CellBorderType.THIN, HSSFColor.BLACK.index);
}
}
excelReport.SaveToExcelFile(@"c:\\TestManyDataWithBorder.xls");
}
public int learnerReport2(string custId, string membNo, string lang, string fileName)
{ // get all learners for this account : custId
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["v8server"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "dbo.spXlearnerReport2";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@custId", custId));
SqlDataReader drd = cmd.ExecuteReader();
// pass the drd to the excel writer and return count in JSON
ExcelReport report = null;
report = new ExcelReport(membNo, lang, fileName);
int rowCount = report.learnerReport2(drd);
con.Close();
return rowCount;
}
protected void iBtnDownload_Click(object sender, EventArgs e)
{
SearchTimeSheetsRequest request = new SearchTimeSheetsRequest(SearchType.QueryReport,
false, OrderBy, OrderDirection);
request.Keywords = txtKeyword.Text;
request.CurrentPage = CurrentPageIndex;
request.PageCount = ReportPage.PageSize;
request.ProjectID = int.Parse(ddlProject.SelectedValue);
request.TicketID = int.Parse(ddlTickets.SelectedValue);
request.UserID = UserInfo.ID;
request.StartDate = StartDate;
request.EndDate = EndDate;
SearchTimeSheetsResponse response = tsApp.QueryTimesheet(request);
ExcelReport report = new ExcelReport();
report.Generate(response.TimeSheetsList, null, UserInfo, ddlProject.SelectedItem.Text, DateTime.Now);
}
private void cmdExportExcel_Click(object sender, EventArgs e)
{
var ds =
SPs.SpBaoCaoXoaKetQua(dtpFromDate.Value.ToString("yyyy/MM/dd"),
dtpToDate.Value.ToString("yyyy/MM/dd"),
Utility.Int32Dbnull(cboTestType.SelectedValue, -1),
Utility.sDbnull(cboUserName.SelectedValue, "")).GetDataSet();
var dataTable = new DataTable();
//Tổng hợp
if (cboReportType.SelectedIndex == 1)
{
dt = ds.Tables[0];
//dataTable = Utilities.GetInversedDataTable(dt, "Para_Name", "Barcode", "Test_Result", "", false);
}
//Chi tiết
else
{
dt = ds.Tables[1];
dataTable = lablinkhelper.Utilities.GetInversedDataTable(dt, "TestType_Name", "Barcode", "Delete_Date", "", false);
}
if (dataTable.Rows.Count <= 0)
{
Utility.ShowMsg("Không có dữ liệu để báo cáo");
return;
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
GridEXColumn grdCol = new GridEXColumn(dataTable.Columns[i].ColumnName);
grdAllPatientInfoAndResult.RootTable.Columns.Add(grdCol);
//grdAllPatientInfoAndResult.RootTable.Columns[grdCol.Key].AggregateFunction = AggregateFunction.Count;
}
// grdAllPatientInfoAndResult.RootTable.Columns["Patient_ID"].Visible = false;
//SetPropertiesCol("Barcode", "Barcode");
// SetPropertiesCol("Test_Date", "Ngày Xét Nghiệm");
// SetPropertiesCol("TestType_Name", "Loại XN");
grdAllPatientInfoAndResult.DataSource = dataTable;
grdAllPatientInfoAndResult.AutoSizeColumns();
ExcelReport.ExportGridEx(grdAllPatientInfoAndResult);
}
public void TestCellAdd2()
{
var excelReport = new ExcelReport("Test many data", "Unknown", "sheet 1");
excelReport.AddEmptyRows(5);
excelReport.AddLogo(0, 0);
for (int rowIndex = 1; rowIndex < 30000; rowIndex++)
{
//var row = excelReport.GetNextRow();
for (int columnIndex = 1; columnIndex < 20; columnIndex++)
{
//excelReport.AddCell(rowIndex * columnIndex, columnIndex, row, TextStyle.NormalText);
excelReport.AddCell("test " + columnIndex + rowIndex, columnIndex, rowIndex, TextStyle.NormalText, HSSFColor.WHITE.index, CellBorderType.NONE, HSSFColor.WHITE.index);
}
}
excelReport.SaveToExcelFile(@"c:\\TestCellAdd2.xls");
}
public void SetDataOnPechInExcelTest()
{
OptZRM opz = new OptZRM();
opz.nachDataPech = Convert.ToDateTime("01.12.2014");
opz.nomerPech = 1;
string path =
@"C:\Users\Александр\Documents\учебка\диплом\web\calcOptimalZRM\calcOptimalZRM\Content\Оптимальная доменная шихта 2010_.xlt";
opz.SetDataOnPechInExcel(path);
Thread.Sleep(5000);
ExcelReport exp = new ExcelReport(path, true);
exp.ChangeWorkSheet("Ввод составов (база)");
string g = exp.GetValue("B7");
double c = Convert.ToDouble(g);
Assert.AreEqual(12.80, c, 1);
}
/// <summary>
/// 测试 复制与隐藏.
/// </summary>
public void TestCopyAndHide()
{
Console.WriteLine("测试 Excel 复制与隐藏 开始！");
string fileName =
System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase
+ @"ExcelFiles\测试复制与隐藏.xls";
ExcelReport service = new ExcelReport();
// 打开 Excel.
service.OpenExcel();
// 打开 Excel 文件.
service.OpenExcelFile(fileName);
// 复制
service.SimpleCopy(
"测试复制与隐藏",
"复制后",
"A3");
// 隐藏
service.HideSheet("测试复制与隐藏");
// 保存 Excel 文件.
service.SaveExcelFile();
// 关闭 Excel.
service.CloseExcel();
Console.WriteLine("测试 Excel 复制与隐藏 结束！");
}
protected void btnCobDet_OnClick(object sender, EventArgs e)
{
string yrmo = ddlYrmo.SelectedItem.Text;
DataSet ds = new DataSet();
DataSet dsFinal = new DataSet();
dsFinal.Clear();
string[][] cols = { new string[] { "YRMO", "Anthem Group Suffix", "Anthem Covg. Code", "Subscriber Id", "Subscriber Name", "EBA Count", "Anthem Count" }, new string[] { "Plan", "Plan Desc", "Subscriber ID", "Subscriber Name", "Division", "Coverage EffDt", "Coverage Level", "Coverage Period", "Comments", "Total Premium", "Total Carrier" }, new string[] { "Plan Code", "Subscriber ID", "Subscriber Name", "Coverage Code", "Coverage Period", "Premium" }, new string[] { "Plan", "Plan Desc", "Subscriber ID", "Subscriber Name", "Division", "Coverage EffDt", "Coverage Level", "Coverage Period", "Comments", "Total Premium", "Total Carrier" } };
string[][] colsFormat = { new string[] { "string", "string", "string", "string", "string", "number", "number" }, new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "decimal", "decimal" }, new string[] { "string", "string", "string", "string", "string", "decimal" }, new string[] { "string", "string", "string", "string", "string", "string", "string", "string", "string", "decimal", "decimal" } };
string[] sheetnames = { "Cobra_Recon_Details", "Unmatched_ADP_Cobra", "Unmatched_Anthem_Cobra", "ADP_Cobra" };
string[] titles = { "Cobra Reconciliation Detailed Report for YRMO - " + yrmo, "Subscribers in ADP Cobra Report not in Anthem Bill for YRMO - " + yrmo, "Subscribers in Anthem Bill not in ADP Cobra Report for YRMO - " + yrmo, "ADP Cobra Subscribers Details for YRMO - " + yrmo };
try
{
ds = DetailReports.getCobraDetails(yrmo);
ds.Tables[0].TableName = "cobDetTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[0].TableName = "cobTableF";
ds.Clear();
ds = DetailReports.getADPUnmatched(yrmo);
ds.Tables[0].TableName = "adpTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[1].TableName = "adpTableF";
ds.Clear();
ds = DetailReports.getAnthCobUnmatched(yrmo);
ds.Tables[0].TableName = "anthTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[2].TableName = "anthTableF";
ds.Clear();
ds = DetailReports.getCobraDetailsReport(yrmo);
ds.Tables[0].TableName = "cobDetTable";
dsFinal.Tables.Add(ds.Tables[0].Copy());
dsFinal.Tables[0].TableName = "cobTableF";
ds.Clear();
ExcelReport.ExcelXMLRpt(dsFinal, "CobraReconDetails_" + yrmo, sheetnames, titles, cols, colsFormat);
}
catch (Exception ex)
{
MultiView1.SetActiveView(view_main);
lbl_error.Text = "Error in generating excel report" + ex.Message;
}
}
public ActionResult ExportDemo()
{
// 1.获取数据源
DataSet dataSource = new DataSet();
DataTable dt = new DataTable("数据表");
dt.Columns.Add("姓名");
DataRow dr = dt.NewRow();
dr["姓名"] = "胡明明";
dt.Rows.Add(dr);
dataSource.Tables.Add(dt);
// 2.生成报表
string templatePath = SysContext.Config.ReportTemplateDirectory_Physical + "Excel报表模版Demo.xlsx";
ExcelReport excelReport = new ExcelReport(templatePath);
excelReport.CreateExcelFile(dataSource);
// 3.展现报表
string reportName = "Excel普通报表 Demo";
return View("~/Views/Shared/Controls/ExcelReportView.cshtml", new ExcelDisplayViewModel
{
Title = reportName,
FilePhysicalPath = excelReport.FilePhysicalPath,
BodyEndJavascript = "alert('" + reportName + "');",
BodyStartHtml = string.Format("<a href=\"{0}\" target=\"_blank\">开始下载</a>",
SysContext.CommonService.GetDownloadLink(excelReport.FilePhysicalPath, reportName)),
});
}
