Export to Excel - Data Display

poppins

Hi everyone,

I am trying to export some data to excel...
I want the data displayed this way:

No. 5
First Name John



I've written a function MakeExcelDataHeader() that generates the data header in the Excel file and called it in the OnPreReport() trigger:

ExcelBuf.AddColumn(Employee.FIELDCAPTION("No."),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Employee.FIELDCAPTION("First Name"),FALSE,'',TRUE,FALSE,TRUE,'');

where ExcelBuf is a record of type Excel Buffer...

I've also written a function MakeExcelDataBody() that generates the data body in the Excel file and called it in the Employee - OnAfterGetRecord() trigger:

ExcelBuf.AddColumn(Employee."No.",FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Employee."First Name",FALSE,'',TRUE,FALSE,TRUE,'');

The problem is that I get the data displayed this way:
No.
First Name
5
John

What shall I do to get the data displayed the way I want???

SPost29

Your code is not quite right.

you print a col1 row1
then
you print col1 row2

then you print col1 row 3
then you print col1 row4

your call to NewRow tells Excel to
CurrentRow := CurrentRow + 1;
CurrentCol := 0;

That sets the currentcol back to 0

you want to be on col2

I hope this helps

postsaurav

HI poppins,

The code is correct but you don't need MakeExcelDataHeader() Function.

As per your requirement you just require the MakeExcelDataBody() and the code will be -

ExcelBuf.AddColumn(Employee.FIELDCAPTION("No."),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(Employee."No.",FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Employee.FIELDCAPTION("First Name"),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(Employee."First Name",FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;

The output will be -

No. 5
First Name John

No. 10
First Name Saurav


Let me know if you have any issues.

poppins

Thank you guys

SPost29 wrote:

Your code is not quite right.

you print a col1 row1
then
you print col1 row2

then you print col1 row 3
then you print col1 row4

your call to NewRow tells Excel to
CurrentRow := CurrentRow + 1;
CurrentCol := 0;

That sets the currentcol back to 0

you want to be on col2

I hope this helps

Yes...I was wondering if I can make it go back to the first line of the Excel file to write the values corresponding to each column of the header...

postsaurav wrote:

HI poppins,

The code is correct but you don't need MakeExcelDataHeader() Function.

As per your requirement you just require the MakeExcelDataBody() and the code will be -

ExcelBuf.AddColumn(Employee.FIELDCAPTION("No."),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(Employee."No.",FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Employee.FIELDCAPTION("First Name"),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(Employee."First Name",FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.NewRow;

The output will be -

No. 5
First Name John

No. 10
First Name Saurav


Let me know if you have any issues.

You're right, postsaurav...Infact, I was advised to look into standard report 6 (Trail Balance), and that's why I separated the code into two functions