Save xlsx file from gsheets alters file

[nicosafull]

Hello,

I am experiencing an issue with the xlnt library when trying to read spreadsheets that have been saved from Google Sheets. When I open a spreadsheet with MS Excel and save it as an xlsx file, the xlnt library is able to parse and save the file correctly. However, if I open the same file with Google Sheets and save it again (keeping the xlsx format), the xlnt library no longer "understands" the empty/null cells.

Here is a code snippet that demonstrates the issue:

wb.load(DatFileNames.As_Ansi());

if (!wb.contains("Functions"))
    return FALSE;

//-- Find the sheet.
auto ws = wb.sheet_by_title("Functions");

//-- Get the number of records excluding header.
auto rows = ws.rows();
m_NumFunctions = rows.length() - 1;

In this case, m_NumFunctions returns 53 when the file is saved with MS Excel, which is the correct number of rows in the spreadsheet. However, when the file is saved with Google Sheets then m_NumFunctions returns 999.

When I iterate through the rows, I encounter another issue. Here is the code snippet:

for (size_t i = 0; i < m_NumFunctions; i++)
{
    size_t rowIndex = i + 2; // 2 as offset because we must consider Excel is not zero based and then because we want to skip first row as it's the header
    CommandID = rows.cell(L_String("A%d", rowIndex).As_Ansi()).value<int>();
}

In this case, CommandID on the actual last row is 53, which is correct. However, on the following iteration (that only happens when file is saved on Google Sheets as on MS Excel it just finishes because it read the legth properly) CommandID returns "0" (zero), even though there is no actual zero in the spreadsheet. I have seen other users struggling with similar issues when using Google Sheets, as it seems to be defaulting a zero on cells that it cannot properly set.

I am not sure how to make the xlnt library "aware" that "0" is not a legitimate value but an actual end-of-line or terminator. I would appreciate any help or suggestions on how to resolve this issue.

Thank you.

[m7913d]

As discussed in issue https://github.com/tfussell/xlnt/issues/748, this repo has been unmaintained for several years. In order to continue this great project started by tfussell, we have created a new repo to support further development by the community. Feel free to participate in this community effort by submitting issues and PR to this new community-driven repo.

[nicosafull]

As discussed in issue #748, this repo has been unmaintained for several years. In order to continue this great project started by tfussell, we have created a new repo to support further development by the community. Feel free to participate in this community effort by submitting issues and PR to this new community-driven repo.

thank you very much