get many _x000d if cell contain \n

[Jack-Myth]

I'm trying to use OpenXLSX to read XLSX. It's a great library but I find something strange. If a cell's value contains multiple lines, the "\n" will be read as x000d.
"abc
abc" will become "abc_x000d__000d..abc"
any idea?

[troldal]

Hi @Jack-Myth

I'm afraid I can't recreate your issue. Can you show the code you are using as well an Excel spreadsheet? Also, can you Tell what system you are using (Operating system, version etc.)?

I made a small test myself; here are my results:

I made a spreadsheet called "TestBook.xlsx", with one sheet called "Sheet1" with the following contents:

Then I ran the following code:

#include <iostream>
#include <iomanip>
#include <OpenXLSX/OpenXLSX.h>

using namespace std;
using namespace OpenXLSX;

int main() {

    XLDocument doc;
    doc.OpenDocument("./TestBook.xlsx");
    auto wks = doc.Workbook().Worksheet("Sheet1");

    cout << "In TestBook:" << endl;
    cout << wks.Cell("A1").Value().Get<std::string>() << endl;
    cout << wks.Cell("A2").Value().Get<std::string>() << endl;
    cout << wks.Cell("A3").Value().Get<std::string>() << endl;

    wks.Cell("B1").Value() = "B1: Line1";
    wks.Cell("B2").Value() = "B2: Line1\n B2: Line 2";
    wks.Cell("B3").Value() = "B1: Line1\n B2: Line 2\n B2: Line 3";

    doc.SaveDocument();
    doc.CloseDocument();

    return 0;
}

The resulting console output:

...and the "TestBook.xlsx" after running the code:

(Note that when you write multiple lines in an Excel cell, the cell is automatically word wrapped; the same does not happen in OpenXLSX. Therefore, the image above shows the cell contents of the B-column as one long line)

[Jack-Myth]

Thanks for your reply.
I think it maybe not caused by the '\n', but in some situation it will do return many x000d.
Actually I can give you an xlsx file, you may find something in it.
test.xlsx
It's a part of a huge xlsx.
And also I write a test program, here is the code:

#include <stdio.h>
#include "OpenXLSX/OpenXLSX.h"
#pragma comment(lib,"OpenXLSX.lib")

int main(int argc, char* argv[])
{
	if (argc == 0)
		return -1;
	OpenXLSX::XLDocument doc;
	doc.OpenDocument(argv[1]);
	auto TargetWorkSheet = doc.Workbook().Worksheet(doc.Workbook().WorksheetNames()[0]);
	for (unsigned int i = 1; i <= TargetWorkSheet.ColumnCount(); i++)
	{
		printf("%s\n", TargetWorkSheet.Row(1).Cell(i).Value().AsString().c_str());
	}
}

It's very simple and just print the first row, but with it you can see the error:

And, by the way, I find the xlsx saved by OpenXLSX can't be read by a python library named "xlrd", But it can be read by "openpyxl". so now I use openpyxl load the xlsx and resave it so the xlrd can read the file, but openpyxl is very slow , it caused many time to resave the xlsx. so do you mind spend a little bit time on this problem?

[troldal]

I looked into this, and it turns out that x000D is the UTF-8 code for "\r" (carriage return), not "\n". The difference is that "\n" jumps to the beginning of the next line, whereas "\r" jumps to the beginning of the current line.

The problem actually lies in the spreadsheet you use, not in OpenXLSX, because Excel actually stores the "\r" character as a string with the UTF-8 code (i.e. "_x000D_"), not the "\r" character itself. So it is difficult to come up with good solution to the problem. Indeed, other Excel libraries have had the same problem (for example: jmcnamara/libxlsxwriter#189).

Also, when I open the test.xlsx file in excel, it behaves strangely. When I select the cell, the contents is reformatted, as if the "\r" characters are replaced with "\n".

Solution

If you are not able to fix the spreadsheet itself (maybe you imported data from a text file?), then you can do something like the following:

#include <iostream>
#include <regex>
#include <OpenXLSX/OpenXLSX.h>

using namespace std;
using namespace OpenXLSX;

int main() {

    XLDocument doc;
    doc.OpenDocument("./test.xlsx");
    auto sht = doc.Workbook().Worksheet("Sheet1");
    auto text = std::regex_replace(sht.Cell("A1").Value().AsString(), regex("_x000D_"), "\n");

    cout << text << endl;

    return 0;
}

This will take the contents of a cell and replace all instances of the substring "_x000D_" with "\n". You can also replace it with "\r", but that is almost certainly not what you want to do (actually, Python/xlrd does that, resulting in only the last line of the cell contents being printed; this makes it very difficult to understand what is going on).

Regarding your question about opening .xlsx files made with OpenXLSX with Python/xlrd, I have fixed that now.

Please let me know if everything works out for you.

[Jack-Myth]

Sorry for my late reply, I have some personal problem last week and can't test it in time.
Thanks for your work, I have test it just now and find the xlsx can be read by xlrd, it help me saved a lot of time.
But,There still have a issue, I have fix it by myself but I think it better to let you know.
This issue seems not due to the OpenXLSX, but the other library named Zippy.
The issue is: When I trying to save the document,It bring the temp file to the target folder,but doesn't change it's name.So it cause this situation after save:

the target file "test.xlsx" doesn't have anything, It's an empty xlsx. and the temp file "***.tmp" is the actual xlsx.
After read the source code I find it cause by the strange behavior of these two code:

code is at Zippy/ZipArchive.h :442
I replace these two code to:

It load the tmp file and write it back to the target file,and it works fine.
I don't know why the std::rename broken but it do have some problems.

[troldal]

Sorry for the delay. The last few months have been really busy!

I am unable ro reproduce the error you mention. I have tested it with Visual Studio 2019 Community Edition, using both the 32- and 64-bit compiler, with no problems.

According to these references, the std::remove and std::rename functions are available in Visual Studio:
https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/rename-wrename?view=vs-2019
https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/remove-wremove?view=vs-2019

I will update the library to check the return value of std::remove and std::rename. If an error code is returned, the library will throw an exception.

[MarvinXu]

@Jack-Myth I came across the same issue. I've read your test.xlsx using pandas, and I can see 'x00D' symbols. Then I opened your file in Excel and click "Save as", the 'x00D' symbol became '\r'...