Importing excel file using xmlport in navision

azimkhan96

Hi,

Can anyone tell me, is it possible to import an xls file using xmlport in navision or not.
If yes tell please give way or any article which i can refer for this.

Thanks

kine

XLS is not XML. XLSX includes some XML, but it is zipped. And th complexity of the XML is beyond the limits of the XMLPort.

garak

azimkhan96 wrote:

Hi,
... is it possible to import an xls file using xmlport ...

A XLS (Excel Spreadsheet) is not XML (Extensible Markup Language) File.
So you can import a xls file on different ways. As csv with a dataport or for example like the function "Import from Excel" in form 113.

Is your xls file a XLSX file as kine ask?

Regards

azimkhan96

Thanks for replying.


As mentioned by you, i have changed the excel file extension to xlsx but still error persists at the time of importing.
The error is "The XML Parser setup fails Error = c00cee2d Code meaning = Unknown error 0xC00CEE2D:"

I have also checked the form 113 Budget. The Budget form have function which in turn call the code to write the data from the excel file into navision table. If this is way to import/export from excel file, then what is the use of xmlport with respect to excel file import/export.

DenSter

XMLPorts are used to import XML documents, not Excel files.

bhalpin

If you are trying to read an Excel file (xls) then I would you look at Table 370 - Excel Buffer. It has most of the functionality needed to read a spreadsheet. (In NAV 4.x it would die on formula cells, but I've got a fix for that if you need it.)

If you define ExcelBuf as Table 370, you simply:

ExcelBuf.OpenBook(FileName,SheetName);
ExcelBuf.ReadSheet;

That loads all the data into table 97 (it uses one record for each cell in the spreadsheet.) Then you work through that table doing whatever you want to with the values.

Try the load then look at what's in 97 to get yourself oriented. Then look at the code in that table to see wehat else is on the menu.

Good luck

Bob

vincentc

Hey bhalpin,

I use this functionality too... works great. How did you get past the issue with formula cells?

bhalpin

Here's what I changed:

Table 370 - Excel Buffer - Ver NAVW14.00 - ReadSheet()
-----------------------------------------------------------------------
Window.OPEN(
  Text007 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

DELETEALL;
XlRange := XlWrkSht.Range(GetExcelReference(5)).SpecialCells(11);
Maxi := XlRange.Row;
Maxj := XlRange.Column;
i := 1;
REPEAT
  j := 1;
  VALIDATE("Row No.",i);
  REPEAT
    VALIDATE("Column No.",j);
// ---------------------------------------
//  Handle formula cells
//  "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
    "Cell Value as Text" := '';
    IF FORMAT(XlWrkSht.Range(xlColID + xlRowID).HasFormula) = 'No' THEN
      "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');
// ---------------------------------------
    IF "Cell Value as Text" <> '' THEN BEGIN
      INSERT;
    END;
    j := j + 1;
  UNTIL j > Maxj;
  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
UNTIL i > Maxi;
XlWrkBk.Close(FALSE);
XlApp.Quit;
CLEAR(XlApp);
Window.CLOSE;