export to excel

stan · February 8, 2007, 12:55am

hai there…
is it possible if we want to export the data to excel?
like we create report than preview so the data will appear in the excel worksheet
how to make it?
thanx

Paul_Croucher · February 8, 2007, 1:08am

Hi, if its simply data from a table that you want to export to Excel then why not create a DataPort. You can secify which fields you want and the order you want them in. Once the Dataport has run you can open the file in Excel…Paul [:D]

stan · February 8, 2007, 1:43am

hehe ok… yes you right
but something i forgot to tell… (sorry :P)
what i want is not to export like the ordinary export, but create a excel report
so after we give the parameter like for example : salesperson code : AA
and then automatically preview in excel only for salesperson code : AA

Mbad · February 8, 2007, 6:28am

You can get a pretty good idea of what to do if you read the code that is used in Analisys by dimensions, export to excell. Here things are exported using filters/pivots etc.

colingbradley · February 8, 2007, 8:30am

However, this may be too complex.

I have created many reports that output to Excel without trying to add formatting or pivot tables.

It is really simple.
Contact me by email and I will send you an example.

Rgds,
Colin

TonyH · February 8, 2007, 8:09pm

Take a look at table 370, and then how to implement it can be seen on report 29, 81, 82, 7113, 7132 and 11605.

/TH

stan · February 9, 2007, 1:08am

i could export
but only if type data = text
if decimal it will be appear error message

this is my code

Documentation()

Item - OnPreDataItem()
Window.OPEN(
‘Says :’ + Text001 +
‘@1@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
TotalRecNo := Item.COUNTAPPROX;
RecNo :=0;

TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);
EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
EnterCell(3, 1, t00, TRUE, TRUE, FALSE);
EnterCell(3, 2, t01, TRUE, TRUE, FALSE);
EnterCell(3, 3, t02, TRUE,TRUE,FALSE);
EnterCell(3, 4, t03, TRUE,TRUE,FALSE);
EnterCell(3, 5, t04, TRUE,TRUE,FALSE);
EnterCell(3, 6, t05, TRUE,TRUE,FALSE);
EnterCell(3, 7, t06, TRUE,TRUE,FALSE);
EnterCell(3, 8, t07, TRUE,TRUE,FALSE);
EnterCell(3, 9, t08, TRUE,TRUE,FALSE);
EnterCell(3, 10, t09, TRUE,TRUE,FALSE);
EnterCell(3, 11, t10, TRUE,TRUE,FALSE);
Row := 3;

Item - OnAfterGetRecord()
Row := Row + 1;

EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);
EnterCell(Row, 4, “Unit Cost”, FALSE, FALSE, FALSE); —> this is the problem, because unit cost is decimal data type
EnterFilterInCell(GETFILTER(“Unit Cost”),FIELDCAPTION(“Unit Cost”));

RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

Item - OnPostDataItem()
Window.CLOSE;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;

EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;

Anna_Perotti · February 9, 2007, 8:12am

EnterCell(Row, 4, FORMAT(“Unit Cost”), FALSE, FALSE, FALSE) [:)]

TonyH · February 9, 2007, 1:44pm

Use explicity casting as adviced by Anna above. Note that there are only a very few data types that implicitly cast in Navision.

Search in the help for the Format command, there are some cool things you can do with it by using parameters in Carrots < >

stan · February 11, 2007, 6:16pm

EnterCell(Row, 4, FORMAT(“Unit Cost”), FALSE, FALSE, FALSE)

Still the same
there’s an error message said
You have Specified an unknown variable
Unit Cost
Define the variable under 'Global C/AL sysmbols

stan · February 12, 2007, 1:27am

by the way if we want to export from navision direct to excel, isi possible using dataport?
because while i’m using dataport, the data is mergin in 1 field altogether…
i must export to txt first and then import using excel, the txt file i have export first from navision

stan · February 12, 2007, 1:30am

form?report?
ID number please?

Jwilder · February 12, 2007, 4:00am

You can use a dataport to export to Excel (sort of).

Make sure the Dataport is tab delimited.
Make sure the filename ends in .txt.
Run the dataport
Browse to the file, right click, Open With, Micorsoft Office Excel

By doing it this way Excel skips the whole import wizard and opens the file directly!

(The default in Excel is to import based on tab delimted so if you haven’t changed this then it will work. If it doesn’t work then email at jwilder@stonewallkithcen.com and I will tell you how to fix it)

Mbad · February 12, 2007, 4:17am

Easiest would prolly be report 7113.

Advanced one is CU 424

TonyH · February 12, 2007, 6:12pm

Stan,

the field “Unit Cost” is in the item table as per standard. Has someone renamed it?

Have you tried hitting F5 and then going to the “Item” in the first column, “Fieldname” in the second column, and then finding “Unit Cost” in the third column?

If you export to text, copy and paste the object in here, or PM me I will take a look.

Regards

/TH

stan · February 12, 2007, 6:59pm

this the code :

Item - OnAfterGetRecord()
Row := Row + 1;
EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);
EnterCell(Row, 4, FORMAT(“Unit Cost”),TRUE,TRUE,TRUE);
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

the bold one is the problem
I’m sure the table name in database is not renamed
because the problem is if I use the data type decimal like : Unit Cost, Unit Price, Standard Cost, Last Direct Cost, etc
nevertheless if I use the data type varchar like No., Description, Vendor No. etc, it can be done successfully

by the way I export it to excel, not to txt

TonyH · February 12, 2007, 7:04pm

Hey Stan,

Can you export the object to text. Like you export to Fob but to Text instead, its in the dropdown “Save as Type”. Object Designer, highlight object., file, export (heres where you save as text)

Open it in a text editor and then copy and paste it into a message here. That way I can get the same exact object as you have.

/TH

stan · February 12, 2007, 7:26pm

OBJECT Report 50021 Export Item By Location
{
OBJECT-PROPERTIES
{
Date=13/02/03;
Time=10:02:14;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table27;
OnPreDataItem=BEGIN
Window.OPEN(
‘Stanley Bilang :’ + Text001 +
‘@1@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
TotalRecNo := Item.COUNTAPPROX;
RecNo :=0;

TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);
EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
EnterCell(3, 1, t00, TRUE, TRUE, FALSE);
EnterCell(3, 2, t01, TRUE, TRUE, FALSE);
EnterCell(3, 3, t02, TRUE,TRUE,FALSE);
EnterCell(3, 4, t03, TRUE,TRUE,FALSE);
EnterCell(3, 5, t04, TRUE,TRUE,FALSE);
EnterCell(3, 6, t05, TRUE,TRUE,FALSE);
EnterCell(3, 7, t06, TRUE,TRUE,FALSE);
EnterCell(3, 8, t07, TRUE,TRUE,FALSE);
EnterCell(3, 9, t08, TRUE,TRUE,FALSE);
EnterCell(3, 10, t09, TRUE,TRUE,FALSE);
EnterCell(3, 11, t10, TRUE,TRUE,FALSE);
Row := 3;
END;

OnAfterGetRecord=BEGIN
Row := Row + 1;

EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);

RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
END;

OnPostDataItem=BEGIN
Window.CLOSE;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;
END;

}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
{ PROPERTIES
{
DataItemTable=Table32;
DataItemTableView=SORTING(Item No.,Entry Type,Variant Code,Drop Shipment,Location Code,Posting Date)
WHERE(Location Code=CONST(GD));
DataItemVarName=GD;
DataItemLinkReference=Item;
DataItemLink=Item No.=FIELD(No.),
Posting Date=FIELD(Date Filter);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
TempExcelBuffer@1000000004 : TEMPORARY Record 370;
Window@1000000003 : Dialog;
Row@1000000002 : Integer;
TotalRecNo@1000000001 : Integer;
RecNo@1000000000 : Integer;
Text000@1000000009 : TextConst ‘ENU=Daftar Item BY Location’;
Text001@1000000008 : TextConst ‘ENU=Analisa Data…’;
t00@1000000007 : TextConst ‘ENU=NKS Lama’;
t01@1000000006 : TextConst ‘ENU=NKS’;
t02@1000000005 : TextConst ‘ENU=Description’;
t03@1000000010 : TextConst ‘ENU=Harga’;
t04@1000000011 : TextConst ‘ENU=GD’;
t05@1000000012 : TextConst ‘ENU=DO’;
t06@1000000013 : TextConst ‘ENU=SEWA’;
t07@1000000014 : TextConst ‘ENU=EXT’;
t08@1000000015 : TextConst ‘ENU=BPDI’;
t09@1000000016 : TextConst ‘ENU=DO PENDING’;
t10@1000000017 : TextConst ‘ENU=TOTAL’;

PROCEDURE EnterCell@1000000000(RowNo@1000000005 : Integer;ColumnNo@1000000004 : Integer;CellValue@1000000003 : Text[250];Bold@1000000002 : Boolean;Italic@1000000001 : Boolean;UnderLine@1000000000 : Boolean);
BEGIN
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;
END;

BEGIN
END.
}
}

thanks a lot Tony!

TonyH · February 12, 2007, 8:22pm

I’m just heading out climbing, so I will get back to this tomorrow.

/TH

stan · February 12, 2007, 11:12pm

ok then…thanx a lot!

Topic		Replies	Views
Reports to Excel Developers Forum	6	3941	March 19, 2008
Export to Excel - Data Display Developers Forum	1	1341	October 24, 2012
Export Report Data to Excel Developers Forum	8	3959	May 25, 2012
excel report User Forum	11	3866	February 18, 2013
Export to Excel Developer Forum	4	5771	October 19, 2011

export to excel

Related Topics