Excel Import XLSX (read only lines in filter) - NAV 2013 R2

robertwendler@yahoo.de

Hi guys,

every week we get a big excel file. We import all values into NAV with excel buffer and process the data. Unfortunately the excel file has a filter.

For example: The excel file has 1000 lines and 200 are in the current filter. We want to import only the 200 lines in the filter.

We get the file with the filter and the sender will not change the excel file.

Is there any chance to automate this process? At the moment we copy the filtered lines to a new sheet and import this sheet.

AlexDen

Hi,

You can get the list of hidden rows by the following way:
Add the function below to Excel Buffer table:

PROCEDURE GetHiddenRowsList(VAR RowsList : TEMPORARY Record Integer);
----------
  XMLDOMManagement : Codeunit XML DOM Management;
  XmlWrkShtDoc : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
  XmlNodeList : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
  XmlNode : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
  XmlNamespaceManager : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager";
  RowNo : Integer;
----------
  RowsList.RESET;
  RowsList.DELETEALL;

  XmlWrkShtDoc := XmlWrkShtDoc.XmlDocument;
  XmlWrkShtDoc.LoadXml(XlWrkShtReader.Worksheet.OuterXml);

  XMLDOMManagement.AddNamespaces(XmlNamespaceManager,XmlWrkShtDoc);

  XmlNodeList := XmlWrkShtDoc.SelectNodes('/x:worksheet/x:sheetData/x:row[@hidden=1]',XmlNamespaceManager);

  IF ISNULL(XmlNodeList) THEN
    EXIT;

  FOREACH XmlNode IN XmlNodeList DO BEGIN
    EVALUATE(RowNo, XmlNode.GetAttribute('r'));
    RowsList.Number := RowNo;
    RowsList.INSERT;
  END;

And then you can check whether the row hidden or not and use only data from visible rows:

LOCAL PROCEDURE OpenXlAndGetHiddenRows();
----------
  xlBuf : TEMPORARY Record Excel Buffer;
  HiddenRowsList : TEMPORARY Record Integer;
  FileManagement : Codeunit File Management;
  ClientFileName : Text;
  ServerFileName : Text;
  SheetName : Text;
----------
  xlBuf.RESET;
  xlBuf.DELETEALL;

  ClientFileName := FileManagement.OpenFileDialog('','',FileManagement.GetToFilterText('','*.xlsx'));
  IF ClientFileName = '' THEN
    EXIT;

  ServerFileName := FileManagement.UploadFileSilent(ClientFileName);

  SheetName := xlBuf.SelectSheetsName(ServerFileName);
  xlBuf.OpenBook(ServerFileName,SheetName);
  xlBuf.GetHiddenRowsList(HiddenRowsList);
  xlBuf.ReadSheet;

  IF xlBuf.FINDLAST THEN BEGIN
    MESSAGE('%1 of %2 rows are visible', xlBuf."Row No."- HiddenRowsList.COUNT, xlBuf."Row No.");
    IF HiddenRowsList.GET(xlBuf."Row No.") THEN
      MESSAGE('The last row is hidden')
    ELSE
      MESSAGE('The last row is visible');
  END;

This is solution for Nav 2017, but I think can be used in Nav 2013.

robertwendler@yahoo.de

Just to let you guys know: FOREACH is not supported in NAV 2015 or older.

Other Solution:

Name	DataType	Subtype	Length
Enumerator_l	DotNet	System.Collections.Generic.List`1+Enumerator.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	

Enumerator_l := XmlNodeList_l.GetEnumerator;
WHILE Enumerator_l.MoveNext DO BEGIN
  XmlNode_l := Enumerator_l.Current;
  EVALUATE(RowNo_l, XmlNode_l.GetAttribute('r'));
  RowsListTmp_v.Number := RowNo_l;
  RowsListTmp_v.INSERT;
END;

Also you have to downgrade the function AddNamespaces in the XMLDOMManagement.

robertwendler@yahoo.de

This looks nice. I will try this. Thanks!

robertwendler@yahoo.de

Why was the post with the solution deleted?

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

Probably the poster deleted it for some reason. I've seen also people deleting their questions after it has been answered. Still don't know why they do it.

Slawek_Guzek

@kriki There is no option on Mibuso to delete own comment, or is there? I wanted to delete my comment once, and could not find any way to do so.

robertwendler@yahoo.de

mibuso.com https://forum.mibuso.com/
AlexDen answered your question: Excel Import XLSX (read only lines in filter) - NAV 2013 R2
Check it out: https://forum.mibuso.com/discussion/comment/325659#Comment_325659

I have seen the complete solution to my problem and then the post was deleted?!

AlexDen

Hi,

You can get the list of hidden rows by the following way:
Add the function below to Excel Buffer table:

PROCEDURE GetHiddenRowsList(VAR RowsList : TEMPORARY Record Integer);
----------
  XMLDOMManagement : Codeunit XML DOM Management;
  XmlWrkShtDoc : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
  XmlNodeList : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
  XmlNode : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
  XmlNamespaceManager : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager";
  RowNo : Integer;
----------
  RowsList.RESET;
  RowsList.DELETEALL;

  XmlWrkShtDoc := XmlWrkShtDoc.XmlDocument;
  XmlWrkShtDoc.LoadXml(XlWrkShtReader.Worksheet.OuterXml);

  XMLDOMManagement.AddNamespaces(XmlNamespaceManager,XmlWrkShtDoc);

  XmlNodeList := XmlWrkShtDoc.SelectNodes('/x:worksheet/x:sheetData/x:row[@hidden=1]',XmlNamespaceManager);

  IF ISNULL(XmlNodeList) THEN
    EXIT;

  FOREACH XmlNode IN XmlNodeList DO BEGIN
    EVALUATE(RowNo, XmlNode.GetAttribute('r'));
    RowsList.Number := RowNo;
    RowsList.INSERT;
  END;

And then you can check whether the row hidden or not and use only data from visible rows:

LOCAL PROCEDURE OpenXlAndGetHiddenRows();
----------
  xlBuf : TEMPORARY Record Excel Buffer;
  HiddenRowsList : TEMPORARY Record Integer;
  FileManagement : Codeunit File Management;
  ClientFileName : Text;
  ServerFileName : Text;
  SheetName : Text;
----------
  xlBuf.RESET;
  xlBuf.DELETEALL;

  ClientFileName := FileManagement.OpenFileDialog('','',FileManagement.GetToFilterText('','*.xlsx'));
  IF ClientFileName = '' THEN
    EXIT;

  ServerFileName := FileManagement.UploadFileSilent(ClientFileName);

  SheetName := xlBuf.SelectSheetsName(ServerFileName);
  xlBuf.OpenBook(ServerFileName,SheetName);
  xlBuf.GetHiddenRowsList(HiddenRowsList);
  xlBuf.ReadSheet;

  IF xlBuf.FINDLAST THEN BEGIN
    MESSAGE('%1 of %2 rows are visible', xlBuf."Row No."- HiddenRowsList.COUNT, xlBuf."Row No.");
    IF HiddenRowsList.GET(xlBuf."Row No.") THEN
      MESSAGE('The last row is hidden')
    ELSE
      MESSAGE('The last row is visible');
  END;

This is solution for Nav 2017, but I think can be used in Nav 2013.

robertwendler@yahoo.de

Just to let you guys know: FOREACH is not supported in NAV 2015 or older.

Other Solution:

Name	DataType	Subtype	Length
Enumerator_l	DotNet	System.Collections.Generic.List`1+Enumerator.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	

Enumerator_l := XmlNodeList_l.GetEnumerator;
WHILE Enumerator_l.MoveNext DO BEGIN
  XmlNode_l := Enumerator_l.Current;
  EVALUATE(RowNo_l, XmlNode_l.GetAttribute('r'));
  RowsListTmp_v.Number := RowNo_l;
  RowsListTmp_v.INSERT;
END;

Also you have to downgrade the function AddNamespaces in the XMLDOMManagement.

kriki

Slawek_Guzek wrote: »

@kriki There is no option on Mibuso to delete own comment, or is there? I wanted to delete my comment once, and could not find any way to do so.

As far as I know, the only way the user can do it, is just to edit the post and remove the text in it. But the post itself remains.
The only ones that can delete posts or topics are administrators and moderators.

Slawek_Guzek

kriki wrote: »

As far as I know, the only way the user can do it, is just to edit the post and remove the text in it. But the post itself remains.
The only ones that can delete posts or topics are administrators and moderators.

Exactly. And the comment 325659 was not just cleared by the user, it has been physically deleted. So either someone (a forum Administrator, you?) removed it (by mistake?) or it has been deleted by some forum code bug

kriki

I only delete posts when it is spam. When a topic is a spam topic and someone replies to it, I delete the whole topic. That is the only moment that a non-spam post is deleted.
I am not aware of a forum code bug that does that.