read xlsx file

[kuku Forever]

HI

I need to perform several steps

1. Read the contents of an XLSX file.

2. Update a line in the XLSX file.

 

I am currently able to read a CSV file but not an XLSX file

By the following command

function readFile(path) {
    var file = new FileReader(path);
    try {
        file.open();
        var content = file.readAll();
        System.log("File Content: " + content);
    } catch (e) {
        System.error("An error occurred while reading the file: " + e);
    } finally {
        file.close(); // Ensure the file is closed
    }
}


readFile(filePathReport);

Just for starters
What is the correct way to read an XLSX file? 
THX

[Stefan Schnell]

Hello kuku,

XLSX files are ZIP container. If you want to read an Excel XLSX file it is necessary to unzip it and to extract the worksheet as XML file. This XML file contains the data of the worksheet, e.g.

<c r="A1" s="0" t="n"><v>1</v></c>
<c r="B1" s="0" t="n"><v>1</v></c>
<c r="C1" s="0" t="n"><f aca="false">B1+A1</f><v>2</v></c>

In this case cell A1 contains the value 1, cell B1 contains the value 1 and cell C1 contains a formula with the addition of B1 and A1 and the value 2.

To read the the XML content of a worksheet you can use the following approach:

var ZipReader = function(file) {
  if (
    typeof file !== "undefined" && file !== null && String(file).trim() !== ""
  ) {
    this.filename = file;
    this.contentItems = [];
    try {
      if (java.io.File(this.filename).exists) {
        var zipInput = java.util.zip.ZipInputStream(
          java.io.BufferedInputStream(
            java.io.FileInputStream(this.filename)
          )
        );
        var entry = zipInput.getNextEntry();
        while(entry !== null) {
          this.contentItems.push({
            "entryName": entry.getName(),
            "content": zipInput.readAllBytes(),
            "encoding": null
          });
          zipInput.closeEntry();
          entry = zipInput.getNextEntry();
        }
        zipInput.close();
      }
    } catch (exception) {
      throw new Error(exception);
    }
  }
};

ZipReader.prototype = {
  readZip : function(entryName) {
    var retValue = "";
    this.contentItems.forEach( function(item) {
      if (entryName === String(item.entryName)) {
        for (i = 0; i < item.content.length; i++) {
          retValue += String.fromCharCode(item.content[i]);
        }
      }
    });
    return retValue;
  }
};

function Main() {
  var zipReader = new ZipReader(System.getTempDirectory() + "/test.xlsx");
  var data = zipReader.readZip("xl/worksheets/sheet1.xml");
  System.log(data);
}

Main();

The code contains a ZIP reader class, equivalent to the standard ZipWriter class.

For this example an Excel file with the name test.xlsx was uploaded into the temporary directory.
With the ZIP reader class the Excel file is read and Worksheet 1 is extracted, from the path xl/worksheets/sheet1.xml.

Here an example from the hands-on lab:

To use this class it is necessary to set in the control center the system property com.vmware.scripting.javascript.allow-native-object to true.

So you can extract the content you want to change. With the XML plug-in you can modify the XML and with the ZipWriter class you can write it back as Excel XLSX.

Best regards
Stefan

------------------------------
More interesting information at https://blog.stschnell.de
------------------------------

[kuku Forever]

Thanks for sharing Stefan Schnell !

 

I get the same result like you do.

but I your data value is a number, If I have a text I cannot read clearly 

 

I believe there is a solution to the issue,

 

I believe there is a solution to the issue,

 

But since I am going to work with a large Excel file with many sheets ...

So I will have to call them by name each time to perform a read and then a write ...

And from what I have seen I can only refer to sheet . sheet2. sheet3 etc...and not by the name of the sheet .... Seems a bit complicated.

 

Question,

Is it worth installing the application

npm install xlsx on the VRO?

 

Sorry for the digging :)

Thank you

[Stefan Schnell]

Hello kuku,

yep, the XML structure of XLSX is relatively complicated. For example, a string is saved in a separate file and then referenced. This makes handling and therewith programming more complex. In my opinion the use of a library to use Excel files is worthwhile in any case. Using their consolidated functions to access the data in the Excel file will be very helpful. Your question can be answered with a clear yes, SheetJS looks very interesting.

Best regards
Stefan

------------------------------
More interesting information at https://blog.stschnell.de
------------------------------