private string BuildRep(DataTable dt) { string output_file = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "Rep", DateTime.Now.ToLongDateString() + "_" + DateTime.Now.Ticks.ToString() + ".xlsx"); var ds = new DataSet(); ds.Tables.Add(dt); CalculationlFormulaExcel.CalcSpreadsheetDocument( ReportBuilderXLS.GenerateReport( ds, Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "Template", "HexLog.xlsx")), true, output_file); return(output_file); }
public static void Print2(Dictionary <string, IList> data_for_report, string temlate) { String template_file = Path.Combine(template_path, temlate); if (File.Exists(template_file)) { var file_format = DBHelper.CurrentUser.ReportFormat; String result_file = Path.Combine(rep_path, Path.GetFileNameWithoutExtension(temlate) + "_" + DateTime.Now.Ticks.ToString() + "." + file_format); var report = ReportBuilderXLS.GenerateReport(data_for_report, template_file).ToArray(); // var calc = CalculationlFormulaExcel.CalcSpreadsheetDocument(report, true).ToArray(); if (DBHelper.CurrentUser.InternalEditor != null && DBHelper.CurrentUser.InternalEditor.Value) { if (file_format == "pdf") { new frmPdfView(report).Show(); } else if (file_format == "xlsx") { new frmSpreadsheed(report).Show(); } } else { File.WriteAllBytes(result_file, report); if (File.Exists(result_file)) { Process.Start(result_file); } } } else { MessageBox.Show("Шлях до шаблонів " + template_file + " не знайдено!"); } }
private async Task <Stream> GetNaturalGasBillsInternalAsync(SqlParams @params) { var bills = await _dbConnection.QueryAsync <Bill>(@$ "select company.name as CompanyFullName, company.short_name as CompanyShortName, company.www as CompanySite, company.email as CompanyEmail, company.taxpayer_phone as CompanyPhone, company.state_registry_code as CompanyStateRegistryCode, company.address as CompanyAddress, branch_offices.iban as BranchOfficeIban, branch_offices.bank_full_name as BranchOfficeBankFullName, ap.name as AccountingPointName, concat(address.cityName, ' ', address.streetName, ' ', address.building, ' ', address.apt) as AccountingPointAddress, concat(people.last_name, ' ', people.first_name, ' ', people.patronymic) as OwnerFullName, periods.name as PeriodShortDate, apdh.debt_value::decimal as AccountingPointDebtHistory, payments.sumAmount::decimal as PaymentSumByPeriod, inv.total_units::decimal as InvoiceTotalUnits, inv.total_amount_due::decimal as InvoiceTotalAmountDue, (jsonb_array_elements(inv.usage_t1 -> 'Calculations') -> 'PriceValue')::decimal as TariffRate, ap.debt::decimal as AccountingPointDebt from invoices inv join accounting_points ap on ap.id = inv.accounting_point_id join branch_offices on ap.branch_office_id = branch_offices.id join company on branch_offices.company_id = company.id join periods on branch_offices.current_period_id = periods.id join ( select addresses.id, cities.name as cityName, streets.name as streetName, building, apt from addresses join streets on addresses.street_id = streets.id join cities on streets.city_id = cities.id ) as address on ap.address_id = address.id join people on ap.owner_id = people.id left join accounting_point_debt_history as apdh on ap.id = apdh.accounting_point_id and apdh.period_id=inv.period_id left join LATERAL ( select p.accounting_point_id, sum(p.amount) as sumAmount from payments as p where period_id = inv.period_id and status = 1 and p.accounting_point_id=inv.accounting_point_id group by p.accounting_point_id ) payments on true where ap.branch_office_id = 101 and inv.period_id = case when @periodId is null then inv.period_id else @periodId end and inv.id = case when @id is null then inv.id else @id end", @params); var dict = new Dictionary <string, IList> { { "bill_gas", bills.ToList() } }; var ms = ReportBuilderXLS.GenerateReport(dict, "Templates/bill_gas.xlsx"); //if (@params.PeriodId.HasValue) //{ var counter = 1; using var naturalGasBills = new XLWorkbook(ms); // using var mergedBills = new XLWorkbook(); // mergedBills.AddWorksheet(); // var electricitySpace = 01; foreach (var bill in bills) { //var naturalGasBill = naturalGasBills.Worksheet(1).Range((counter - 1) * 17 + 1, 1, counter * 17, 15); //mergedBills.Worksheet(1).Cell((counter - 1) * 17 + 1, 1).Value = naturalGasBill; //mergedBills.Worksheet(1).LastRowUsed().InsertRowsBelow(1); //var baseUri = _recsBillLocations.Single(l => l.Prefix == bill.AccountingPointName.Substring(0, 2)).BaseUri; //var response = await _httpClient.GetAsync($"{baseUri}rp.name='{HttpUtility.UrlEncode(bill.AccountingPointName)}'?lastOnly=False"); //using var electricityBill = new XLWorkbook(await response.Content.ReadAsStreamAsync()); //var electricity = electricityBill.Range("Bill"); //var spaceIncriment = 24; //if (electricity.LastRow().RowNumber() < 2) //{ // electricity = electricityBill.Range("Billzone"); // spaceIncriment = 31; //} //if (electricity.LastRow().RowNumber() > 10) // naturalGasBills.Worksheet(1).Cell((counter * 19) + electricitySpace, 1).Value = electricity; //else // spaceIncriment = 0; //electricitySpace += spaceIncriment; //naturalGasBills.Worksheet(1).PageSetup.AddHorizontalPageBreak(naturalGasBills.Worksheet(1).LastRowUsed().RowNumber()); var ws = naturalGasBills.Worksheet(1); ws.AddPicture(@"Templates/n_gas.png") .MoveTo(ws.Cell($"A{(counter - 1) * 19 + 7}")) .Scale(0.66); // optional: resize picture if (counter != 0 && counter % 4 == 0) { ws.PageSetup.AddHorizontalPageBreak(counter * 19); } counter++; } // var s = new MemoryStream(); // mergedBills.SaveAs(s); // s.Position = 0; // return s; //} naturalGasBills.SaveAs(ms); ms.Position = 0; return(ms); }