Formatting Errors in Calc

users
#1

LibreOffice Version: 4.1.3.2 Build ID: 410m0(Build:2) on Kubuntu 13.10

Background:

I use KingSoft Office Spreadsheet for Android on my phone because regrettably LibreOffice Calc for Android is not usable. KingSoft uses and saves .xlsx files. From time to time I upload one of these files to my Linux computer to enhance the layout. I use LibreOffice as noted at the top of this post to perform the enhancements.

Problem:

Formulas that evaluate to text strings are being displayed as a formatted zero value.

Examples:

• A formula that evaluates to "14.01.16.xlsx" according to the Function Wizard displays as "December 30, 1899" instead using a date format code. The formula is supposed to display the file name without the path. Since CELL("filename") formats the information differently in Kingsoft Office vs LibreOffice the formula tests the structure of the returned string and extracts the file name accordingly. The LibreOffice part of the formula is:

=MID(CELL("filename"),FIND(C1,CELL("filename"),1)+LEN(C1),FIND("'#", CELL("filename"),1)-( FIND(C1,CELL("filename"),1)+LEN(C1 )))

• A formula that evaluates to "" according to the Function Wizard displays as $0.00 instead using a currency format code. The simple formula is:

=IF(ISNUMBER(F20),IF(F20<C20,C20,""),"")

This is a new phenomenon. To answer the question, "what has changed," the only changes I've made to LibreOffice since these cells displayed correctly to try the 2525 color pallet mentioned in another thread on this list. The changes were in directory:

.config/libreoffice/4/user/config/

• I moved file standard.soc to standard.soc.std and replaced it with a copy of standard.soc--jan-19--10_26---2525-color-blocks.txt following which I restarted LibreOffice.

• After seeing the problem I am reporting I restored file standard.soc from standard.soc.std and again restarted LibreOffice. The problem is still there.

After each restart I took a look at the Background Colors pallet and saw what I expected so I know that each change took effect when tried.

Does anyone know what I should do next to resolve my problem?

#2

James E Lang wrote:

LibreOffice Version: 4.1.3.2 Build ID: 410m0(Build:2) on Kubuntu 13.10

Background:

I use KingSoft Office Spreadsheet for Android on my phone because regrettably LibreOffice Calc for Android is not usable. KingSoft uses and saves .xlsx files. From time to time I upload one of these files to my Linux computer to enhance the layout. I use LibreOffice as noted at the top of this post to perform the enhancements.

Problem:

Formulas that evaluate to text strings are being displayed as a formatted zero value.

Examples:

• A formula that evaluates to "14.01.16.xlsx" according to the Function Wizard displays as "December 30, 1899" instead using a date format code. The formula is supposed to display the file name without the path. Since CELL("filename") formats the information differently in Kingsoft Office vs LibreOffice the formula tests the structure of the returned string and extracts the file name accordingly. The LibreOffice part of the formula is:

=MID(CELL("filename"),FIND(C1,CELL("filename"),1)+LEN(C1),FIND("'#", CELL("filename"),1)-( FIND(C1,CELL("filename"),1)+LEN(C1 )))

• A formula that evaluates to "" according to the Function Wizard displays as $0.00 instead using a currency format code. The simple formula is:

=IF(ISNUMBER(F20),IF(F20<C20,C20,""),"")

This is a new phenomenon. To answer the question, "what has changed," the only changes I've made to LibreOffice since these cells displayed correctly to try the 2525 color pallet mentioned in another thread on this list. The changes were in directory:

.config/libreoffice/4/user/config/

• I moved file standard.soc to standard.soc.std and replaced it with a copy of standard.soc--jan-19--10_26---2525-color-blocks.txt following which I restarted LibreOffice.

• After seeing the problem I am reporting I restored file standard.soc from standard.soc.std and again restarted LibreOffice. The problem is still there.

After each restart I took a look at the Background Colors pallet and saw what I expected so I know that each change took effect when tried.

Does anyone know what I should do next to resolve my problem?

Don't know if either of these will help, but a couple of ideas to try if you haven't already...

1. Compatibility problems with MS Office Open XML (.docx, .xlsx, etc.) files are often reported here. I don't have personal experience of such problems, but the advice usually given is to avoid OOXML files if possible. The format apparently changes slightly with each version of MS Office, and even newer versions of MS Office can display OOXML files from older versions incorrectly - so what hope does anyone else have!

The ideal would be to use Open Document Spreadsheet (.ods) but I'm not sure if Kingsoft supports that. Otherwise, it looks like it can output MS Excel 2003 format (.xls - without the extra x), which is probably more reliable.

2. Check what formatting is applied to the cells when saving the spreadsheet from Kingsoft (before importing into LibreOffice). Maybe the cells are formatted as "General", which Kingsoft may interpret differently from LibreOffice. Try setting the format to "Text" in Kingsoft before transferring the file.

Mark.