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

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.

Best Answers

  • AlexDenAlexDen Posts: 22
    Accepted Answer
    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[email protected] Posts: 23
    Accepted Answer
    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.

Answers

  • krikikriki Posts: 8,665Member, Moderator
    [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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • Slawek_GuzekSlawek_Guzek Posts: 1,648Member
    @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.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • BlackTigerBlackTiger Posts: 1,207Member
    There is no option to delete own post.
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • robertwendler@yahoo.de[email protected] Posts: 23Member
    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?!

  • BlackTigerBlackTiger Posts: 1,207Member
    Comments 325659 and 325660 don't exist anymore.

    Call a CSI squad! :smile:
    "You can’t just ask customers what they want and then try to give that to them.
    By the time you get it built, they’ll want something new.” Steve Jobs
  • AlexDenAlexDen Posts: 22Member
    Accepted Answer
    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[email protected] Posts: 23Member
    Accepted Answer
    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.
  • krikikriki Posts: 8,665Member, Moderator
    @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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • Slawek_GuzekSlawek_Guzek Posts: 1,648Member
    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

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • krikikriki Posts: 8,665Member, Moderator
    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.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
Sign In or Register to comment.