The Wayback Machine - https://web.archive.org/web/20201125092145/https://github.com/SheetJS/sheetjs/issues/2011
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot Read xlsx File Generated from java apache.poi.xssf library #2011

Open
Yocoms opened this issue Jun 18, 2020 · 6 comments
Open

Cannot Read xlsx File Generated from java apache.poi.xssf library #2011

Yocoms opened this issue Jun 18, 2020 · 6 comments

Comments

@Yocoms
Copy link

@Yocoms Yocoms commented Jun 18, 2020

Hi, Thanks for your work, first.

In my project, the business logic is like below.


In Java(Spring) Project : Read an Excel File that is Filled with values and fomulars --> Create a Row and Cell below the last row --> A Client(In my case Chrome Browser) Download the File --> Run Code XLSX.read() --> Run Code XLSX.utils.sheet_to_html() --> Draw a Table from the excel file

The Problems are ...

  1. SheetJS could read the origin values from the excel file, but skipped the values that generated from the java apache poi. Weird thing is that the SheetJS can get the values as data including java generated but the ref range like ("A1:F5") does not include the created area.
    (Here, If I do something(like putting a value, saving file, ...) on the excel file with office-excel program, then it can read all of the values)

  2. The problem above applied to the formula cell as well.
    When I edit a value with java apache poi, SheetJS could not show the calculated value on the table. Yes, it could calculate and show the origin values by the way.

I'm sure someone experiencing same thing like me. And I expect that someone reply for those things.
Thanks for reading.

@SheetJSDev
Copy link
Contributor

@SheetJSDev SheetJSDev commented Jun 18, 2020

Can you share a sample file so we can take a closer look?

@Yocoms
Copy link
Author

@Yocoms Yocoms commented Jun 19, 2020

Sure, below is the source.
Thanks for your comment.

////// 1. A Client request get an excel file

////// 2. Server send response with Java file
// Java file has a process logic like below

    FileInputStream fileFis = new FileInputStream(file);
    XSSFWorkbook fileWb = new XSSFWorkbook(fileFis);
    XSSFSheet fileSh = fileWb.getSheetAt(0);

    // Create Row and Cell, Then Set a Value
    // 5, 2, 4 is just random number for test
    Row fileRow = fileSh.createRow(5);
    Cell fileCell = fileRow.createCell(2, 4);
    fileCell.setCellValue("TEST VALUE");

    fileFis.close();

    // Write the output to a file
    File resultFile = new File("1.xlsx");
    FileOutputStream resultFos = new FileOutputStream(resultFile);
    fileWb.write(resultFos);
    resultFos.close();

    return resultFile;

////// 3. Client get a response from the server and SheetJS works like below

    req.onload = function(evt) {
        var tags = "No Data";
        var data = new Uint8Array(req.response);

        if (data.length > 0) {
            var workbook = XLSX.read(data, {type:"array"});
            var sheetName = workbook.SheetNames[0];
            tags = XLSX.utils.sheet_to_html(workbook.Sheets[sheetName], { header: '' });
        }

        jQuery("#divBodyFileViewer").html(tags);
    }

I would be pleased if I could attach my sample files.
I found a demo page you made ---- https://oss.sheetjs.com/
The problem I mentioned is occurring there as well when I drop the sample files.

Since, I cannot attach the files, I attach images that I tested on your demo page.

======= CASE:: Cannot Read the Cells I Added =======
image

======= CASE:: Can Read the Cells I Added After Open the File with Excel Program and save with doing nothing =======
image

@SheetJSDev
Copy link
Contributor

@SheetJSDev SheetJSDev commented Jun 19, 2020

You should be able to add a file here on github, just click and drag the file into the reply window. If you can't, please email it to hello@sheetjs.com.

In any case, If I had to guess it's a POI issue not updating the worksheet range

@Yocoms
Copy link
Author

@Yocoms Yocoms commented Jun 19, 2020

Wow, I did not know that. What a fool.

The attacked file 1_NoIden.xlsx is the one from POI.
Drag it into the demo page. And then open with excel program. Then save it doing anything.
Re-drag it into the demo page. And then you can see the difference.

1_NoIden.xlsx

@SheetJSDev
Copy link
Contributor

@SheetJSDev SheetJSDev commented Jun 19, 2020

It's not obvious that you can drag-drop files. It's written below the box, but there really should have been an upload link or something above the input box.

That said, this is a mixed issue. To explain, go to https://oss.sheetjs.com/cfb-editor/#/ and drag-drop the file. Then click xl/worksheets/sheet1.xml and click (View as Text). You will see

sheet1 range

That is saying the file range is B1:D9, which is what SheetJS use to determine the sheet range. If you scroll further you'll see references to E8/F8, which are both outside the reported range.

If you have patience, we can open a discussion with the POI project and see if you have to do something different to get the library to update the worksheet range.

As for the other side, the reason we've tried to trust the dimension record is based on the actual worksheet representation. Generating millions of small strings is a massive performance killer in V8 / chrome :(

@SheetJSDev
Copy link
Contributor

@SheetJSDev SheetJSDev commented Jun 19, 2020

@Yocoms we raised POI bug 64536.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
2 participants
You can’t perform that action at this time.