want to print report in excel format via navision

shweta

Hi Everyone
I want to print report in Excel format where columns in excel can change dynamically. what is the process of this

sendoh

use excel buffer table or you can create your own by using automation, do the search, there's a lot of thread here regarding your concern.

shweta

sendoh wrote:

use excel buffer table or you can create your own by using automation, do the search, there's a lot of thread here regarding your concern.

How i can use excel buffer table what is the procedure for it.Please Help

sendoh

here's the link http://www.mibuso.com/forum/viewtopic.php?t=12014

rajpatelbca

with the using of excel buffer table
create an object of excel buffer table type record. and use these table's function to export data into excel but,

you can use excel automation object to export report in excel.

Create following automation object for example create blank report.



enter following dataitem.
DataItem Name
Purchase Header <Purchase Header>
Purchase Line <Purchase Line>


Name DataType Subtype Length
xlapp Automation Unknown Automation Server.Application
xlwbook Automation Unknown Automation Server.Workbook
xlsheet Automation Unknown Automation Server.Worksheet
xlrange Automation Unknown Automation Server.Range
ctr Integer
Descr Text 150
ctr1 Integer
sn Integer
testFilter Text 100


enter code in onprereport trigger.


sn:=0;
testFilter := "Purchase Header".GETFILTERS;

CREATE(xlapp);
xlwbook :=xlapp.Workbooks.Add;
xlsheet :=xlwbook.Worksheets.Add;
xlsheet.Name:='TEST Report';
xlapp.Visible := TRUE;

xlsheet.Range('A1').Value := 'Vendor Name';
xlsheet.Range('A2').Value := 'SNo';
xlsheet.Range('B2').Value := 'Item No';
xlsheet.Range('C2').Value := 'description';
xlsheet.Range('D2').Value := 'Quantity';
xlsheet.Range('E2').Value := 'Qty to Receive';
//xlsheet.Range('F1').Value := 'Quality';
//xlsheet.Range('G1').Value := 'Cut';
//xlsheet.Range('H1').Value := 'Size';
//xlsheet.Range('I1').Value := 'Unit Cost';
//xlsheet.Range('J1').Value := 'Qty pcs';
//xlsheet.Range('K1').Value := 'Location';

xlsheet.Range('A1:H1').Font.Bold := TRUE;
xlsheet.Range('D1:DD1').NumberFormat :=0.00;
xlsheet.Range('E1:EE1').NumberFormat :=0.00;




write following code in purchase header presection


ctr :=ctr+1;
//xlsheet.Range('A2'+FORMAT(ctr+1)).Value :="Buy-from Vendor No.";
xlsheet.Range('C1').Value :="Buy-from Vendor No.";
//xlsheet.Range('B'+FORMAT(ctr1+1)).Value := "No.";
//xlsheet.Range('C'+FORMAT(ctr1+1)).Value := Description;
//xlsheet.Range('D'+FORMAT(ctr1+1)).Value := FORMAT(Quantity);
//xlsheet.Range('E'+FORMAT(ctr1+1)).Value :=FORMAT("Qty. to Receive");


write follwing code in purchase line presection


sn:=sn+1;
//

---

excel printing

---

ctr :=ctr+1;
xlsheet.Range('A'+FORMAT(ctr+1)).Value :=sn;
xlsheet.Range('B'+FORMAT(ctr+1)).Value := "No.";
xlsheet.Range('C'+FORMAT(ctr+1)).Value := Description;
xlsheet.Range('D'+FORMAT(ctr+1)).Value := FORMAT(Quantity);
xlsheet.Range('E'+FORMAT(ctr+1)).Value :=FORMAT("Qty. to Receive");

and chenge the report property processing only to yes

then run the report u will find that data will be exported in excel.


i hope it will be help full.

bappaditya44

create a function and assign RowNo ,ColumnNo and Cell Value asText into Excel Buffer table using Validate function.

fun(RNo,CNo,Value)

ExcelBuffer.validate(RowNo,RNo);
ExcelBuffer.validate(ColumnNo,CNo);
ExcelBuffer.validate(CellValueAsText,Value);
ExcelBuffer.insert;

Take one global variable Rowno , increament it whenever a new record is generated in the report then call that function in this way
fun(Rowno,1,<val>);
.....

in OnPostReport write
ExcelBuffer.createbook
ExcelBuffer.createsheet
ExcelBuffer.giveusercontrol