Does anyone know how to work around the OpenOffice Calc problem, is it right to process new rows in cells?
I have a python script that dynamically generates an excel book using openpyxl via pandas.
The script works fine, but when I look at cells in OpenOffice containing newlines, all values are duplicated several times. If I open the same file using Microsoft Excel Viewer, everything will be displayed correctly, and if I use a character other than a new line (for example, a comma, #, etc.), It will be displayed as in both.
I have a workaround to go to excel and replace the random character with a macro, but I would like to avoid this if possible, since the process really needs to be fully automated. also because the file will be processed by another internal tool, I need these cells to be processed with a new line, and I cannot change the character.
I also tried using chr (10) and / or chr (13), but in the first case it just gets replaced in the output with "\ n" anyway, as expected.
The code I use is similar to:
test_list = []
for x in range(1,18):
test_list.append([
"value1",
"\n".join(['element1', 'element2', 'element3']),
"value3"
])
data_df = pd.DataFrame(test_list)
fn = r'/path/to/excel/file.xlsx'
writer = pd.ExcelWriter(fn, engine='xlsxwriter')
data_df.to_excel(writer, sheet_name='Data', index=False, header=0)
workbook = writer.book
worksheet = writer.sheets['Data']
worksheet.set_column('A:ZZ',50,
workbook.add_format({'text_wrap': True}))
writer.save()
What happens to the Element data is that it shows in an OpenOffice Calc cell something like:
Oddly enough, the last item looks right
The same data as a list or through DataFrame.head () looks accurate:
pprint(test_list)
[['value1', 'element1\nelement2\nelement3', 'value3'],
['value1', 'element1\nelement2\nelement3', 'value3'],
['value1', 'element1\nelement2\nelement3', 'value3'],
['value1', 'element1\nelement2\nelement3', 'value3'],
['value1', 'element1\nelement2\nelement3', 'value3'],
['value1', 'element1\nelement2\nelement3', 'value3'],
...
['value1', 'element1\nelement2\nelement3', 'value3']]
data_df.head(18):
0 1 2
0 value1 element1\nelement2\nelement3 value3
1 value1 element1\nelement2\nelement3 value3
2 value1 element1\nelement2\nelement3 value3
...
15 value1 element1\nelement2\nelement3 value3
16 value1 element1\nelement2\nelement3 value3
, openpyxl OpenOffice.