You’re offline. This is a read only version of the page.
82
Hello,
I am exporting an Excel to make a price comparison of certain offers and I have encountered two problems:
1) Creating the Excel from the beginning using the Excel Buffer table, it is not possible to format the cells (except bold, italic and underlined).
2) Seeing that problem 1 seems complex, I have created a template on the server that has a default layout associated with it. Well, I use the Excel Buffer table to fill in the data but the format of the cells that are written is lost... and that's where I need help. Do you know how I can solve this problem? I show you two images of a small simplified test so you can see what happens as well as the associated code:
Share
Reportcodeunit 50013 "Files Mgt2"
{
trigger OnRun()
begin
end;
procedure ExportQuotesToExcel(var Quote: Record "Purchase Header")
var
TempExcelBuf: Record "Excel Buffer" temporary;
begin
InitExcel(TempExcelBuf);
FillExcelBuffer(TempExcelBuf, Quote);
TempExcelBuf.CloseBook();
TempExcelBuf.SetFriendlyFilename('test');
TempExcelBuf.OpenExcel();
end;
local procedure InitExcel(var TempExcelBuf: Record "Excel Buffer" temporary)
var
InStr: InStream;
Files: File;
begin
Files.Open('C:\Users\Public\Comparador ofertas compra.xlsx');
Files.CreateInStream(InStr);
TempExcelBuf.UpdateBookStream(InStr, 'Precios', false);
end;
local procedure FillExcelBuffer(var TempExcelBuf: Record "Excel Buffer" temporary; var Quote: Record "Purchase Header")
var
TempExcelBufSheet: Record "Excel Buffer" temporary;
begin
TempExcelBufSheet.EnterCell(TempExcelBufSheet, 2, 1, 'test', true, false, false);
TempExcelBuf.WriteAllToCurrentSheet(TempExcelBufSheet);
end;
}
The version of BC we use is 16.1 (we know that there are new Excel features currently available, but we are not migrating for now).
Thank you so much.
Thank you very much for answering Steven but I will answer the same as Inge, our version is 16.1 and in that version the features that you expose in the links are not available. Actually using Excel Buffer is not complicated, what is incredible is that it only allows you to format cells in bold, italics and underlined. And the other option that I explained at the beginning of the post does not work either because in addition to not being able to format with Excel Buffer, the format of the cells that are written from a template hosted on the server is changed.
It does not seem that this has a solution without migrating from version...
Thank you very much for answering Inge. In the version we are using (16.1) the options described in the link you have provided me do not appear. For example, I can't choose the "Send to > Microsoft Excel Document (data only)" option in the task 1 steps and I can't "Set Format Options to Excel" during the task 2 steps either.
I don't recommend to use the Excel Buffer, it seems way to complex to achieve a simple Excel layout.
Instead create a report, with a normal dataset and then add an Excel layout to it.
Here are some examples/tutorials:
Why cant you create a report that you define a Excel layout for?
Then the report gives you all the raw data in Excel you can do the formatting and upload that layout so it can be reused when you run the same report again?
Hi, thanks for answering, but the code that appears on that page is related to problem number 1 that I put in the post, which can only be put in bold, italics and underlined. If the cell could be colored, it would be worth it as a solution, but I think it is not possible. As a result I am looking for the solution to alternative number 2, which would be to use a design established in a template, but the problem is that the format of the cells is lost when writing over them.
Hi,
Refer the below link for information
https://erpconsultors.com/export-to-excel-report-in-business-central/
Regards
Amit Sharma
linkedin.com/in/amit-sharma-94542440/
Press Yes if info is useful
Thank you very much for your interest! Unfortunately, my problem has not been resolved, since the report you indicate also deletes the design that I have set (it loses the background color, which is what interests me). You can see it in the image:
Actually the report ends up using the same code as the EnterCell function of the Excel Buffer table. I hope someone can help me because the problem does not look good...
Hi,
You can refer the standard report to get help, and check how this report is exporting data into excel.
report 29 "Export Acc. Sched. to Excel"
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (