Export to Excel - Data Display

poppins
poppins Member Posts: 647
edited 2012-10-24 in NAV/Navision Classic Client
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???

Answers

  • SPost29
    SPost29 Member Posts: 148
    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
    postsaurav Member Posts: 708
    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.
    Thanks & Regards,
    Saurav Dhyani

    Do you Know this About NAV?


    Connect - Twitter | Facebook | Google + | YouTube

    Follow - Blog | Facebook Page | Google + Page
  • poppins
    poppins Member Posts: 647
    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 :mrgreen:

Categories