Exporting to an .XLS file

Business Central/NAV Developers Forum
1

When exporting to a .XLS file when running a report, when the file is opened in Excel it still thinks that it is a .csv file. Is there something else that needs to be done?

2

Hi, How are you exporting in xls format by saving as HTML first and then opening thru’ excel or other methods? Regards

3

When I export the file from Navision I have defined in the code behind the dataport that the file should be a .XLS. However, when I try to open this file in Excel it still thinks it is in a .CSV format.

4

The Suffix .XLS doesn’t enforce a Dataport-File to “be” an Excel-File! The Structure of a file is important! A Dataport exports data in this structure:

<Startdelimiter><Value>Enddelimiter><Separator><Startdelimiter><Value>Enddelimiter><Separator>...

And this is a “classic” .CSV-stucture, which it is interpreted by Excel. To create “real” Excel-Files you have to use the Automation feature of NAVISION. Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP

5

My Advice would be to use Auotomation Server to export the information that you want. Once you start using it I think you will find that there is really no substitute. Here is some code example.

 Name	DataType	Subtype	Length
xlApp	Automation	'Microsoft Excel 9.0 Object Library'.Application	
xlBook	Automation	'Microsoft Excel 9.0 Object Library'.Workbook	
xlSheet	Automation	'Microsoft Excel 9.0 Object Library'.Worksheet	
xlChart	Automation	'Microsoft Excel 9.0 Object Library'.Chart	
xlRange	Automation	'Microsoft Excel 9.0 Object Library'.Range	
Filename	Text		250

// Start Of Excel Program
CREATE(xlApp);
Filename :='S:\Data\BuyersOrder.xls';
xlBook :=xlApp.Workbooks.Add(Filename);
xlSheet :=xlApp.ActiveSheet;
xlSheet.Name := 'Quote #'+"No.";

Counter := '25';
  REPEAT
    // Sales Line 2 From Report #50007
    Core_Deposit := SalesLine."Core Deposit";
    Engine_Deposit := SalesLine."Engine Deposit";
    Returning_Core := SalesLine."Core Deposit";
    SalesLine."Unit Price" := ROUND(SalesLine."Unit Price",1,'=');
    Total_Price := ROUND(Total_Price,1,'=');
    Total_Price := Total_Price + SalesLine."Unit Price";
    Cdes:=SalesLine."Posting Group";
    CPrice:=SalesLine."Unit Price";
    IF (Cdes = 'ENG LYCC') OR (Cdes = 'ENG TELC') THEN BEGIN
       Core_Allowance := CPrice;
       EngModel := SalesLine."No."
    END;
    IF SalesHeader."Tax Liable" = TRUE THEN BEGIN
       Sales_Tax := (Total_Price - Core_Allowance - Engine_Deposit) * 0.0625;
    END;
    xlSheet.Range ('A'+Counter).Value := SalesLine."No.";
    xlSheet.Range ('C'+Counter).Value :=SalesLine.Description;
    xlSheet.Range ('I'+Counter).Value :=SalesLine."Unit Price";
    Counter := INCSTR(Counter);
    SalesLine.NEXT;
  UNTIL SalesLine."Document No." <> "No.";

For Progress = 1 to 100 if Progress = 100 then Progress = 0 Else Next Progress

6

i use followin code: BUF.CreateBook(); BUF.CreateSheet(‘NAME’,‘DESCR’,COMPANYNAME,USERID); BUF.GiveUserControl; where BUF= table 370 if works from 3.00 PS. You have to fill table 370 (Excel buffer) first. But it s not so difficult