Created attachment 84100 [details]
Spreadsheet with border issue
Spreadsheets with Single line borders saved using previous version of Calc when opened with new version shows dashes instead of single line.
Further when printout is takes these dashes are not printed for all rows
Sample spreadsheet is enclosed.
I confirm the bug under Win7 64bit.
If I open the test file with 4.0.4 final, border is single line.
If I open it with 4.1.0 final, the border appears dashed.
setting status to NEW, adding regression keyword and Calc developer to CC list.
changing summary notes.
some questions for you:
1- which LibO version did you use to create the test file?
2- which exact Windows version do you run?
Created attachment 84525 [details]
Sample file created using LibreOffice Calc 4.04
Created attachment 84526 [details]
Image of Borders show for file made using LibreOffice Calc 4.04
Created attachment 84527 [details]
Sample file created using LibreOffice Calc 4.1
Created attachment 84528 [details]
Images of Borders of file created using LibreOffice 4.1 and opened by other programs
Created attachment 84529 [details]
Sample file created using OpenOffice 4.0
Created attachment 84530 [details]
Images of Borders of file created using OpenOffice 4.0 and opened by other programs
Created attachment 84531 [details]
Sample file created using Microsoft Office 2010
Created attachment 84532 [details]
Images of Borders of file created using Microsoft Office 2010 and opened by other programs
I have tried on Win7 Pro (32Bit) and Win8 Pro (32Bit).
The main issue is that thin line cell borders (0.05 thick - the default border) made using OpenOffice Calc 4.0 and LibreOffice Calc 4.04 and saved as .xls file when opened with LibreOffice Calc 4.1 shows as dashes and does not print properly.
I have made a spread sheet with three cells having borders (0.05, 0.50 and 1.0 thick) using LibreOffice Calc 4.04, LibreOffice Calc 4.1, OpenOffice Calc 4.0 and Microsoft Office 2010 and saved the sheets as .xls file.
The above 4 .xls files were then opened with the 4 programs and how the three cells are being shown are given in the 4 xls and 4 jpg files attached
shrug.. I know there were plenty, but I'm not aware of changes, are you, Kohei, Markus?
(In reply to comment #12)
> Or Noel?
are most likely related to this.
so in comment #1 the problem is that the imported lines are now dashed ( as they are in Excel )? so, better import here should explain the differences. About the printing not happening for all rows I couldn't even begin to comment on that ( I have absolutely no clue about printing ) it is imho a separate issue entirely
I can only assume the new widths for imported excel border styles associated with http://cgit.freedesktop.org/libreoffice/core/commit/?id=a6833704bcfe6b5b5b6eb020eb79aebf0dc6cd4f
are correct ( they look better to me at least ), but... regardless xls and xlsx import should be aligned, there is no point in having a single Excel document ( albeit saved in different formats, e.g. xls/xlsx ) that displays differently in Libreoffice.
To me I don't see a problem ( besides the separate printing issue )
*** Bug 68172 has been marked as a duplicate of this bug. ***
Created attachment 84651 [details]
Cell border problem of LibreOffice Calc 4.1
The problem is not that single line borders appears as dashed lines on the screen.
An spreadsheet made with the previous version of Libreoffice Calc (4.04) or OpenOffice 4.0 having cells with the default single line borders (0.05 pt thick) when saved as .xls file and opened with the new version of LibreOffice Calc (4.1), the single line borders shows as dashes on the screen. If you open the cell property of a cell having border you can find that "line style" combo box is blank! ie the 0.05 pt style single line (the default single line) of previous version of Calc (4.04) is not recognised properly by the new version.
Further, on printing the spreadsheet using the new 4.1 version, the dashes are not printed on all cells (most of the cells appear without any borders). This is a big nuisance, since all old spreadsheets have to be edited and the borders have to be changed when you open with the new version.
See the attached image of the borders printed.
(In reply to comment #15)
> Created attachment 84651 [details]
> Cell border problem of LibreOffice Calc 4.1
> The problem is not that single line borders appears as dashed lines on the
> An spreadsheet made with the previous version of Libreoffice Calc (4.04) or
> OpenOffice 4.0 having cells with the default single line borders (0.05 pt
> thick) when saved as .xls file and opened with the new version of
> LibreOffice Calc (4.1), the single line borders shows as dashes on the
Sorry, I don't get it, if I export a cell 0.05 pt single line border from Libreoffice 4.0 or 4.1 and open that file in Excel XP, 2003, 2007 or 2010 I see a hairline border ( hairline = some dot/dash type borderline, more dot than dash in this case ).
If I open that same file again
i) in LibreOffice 4.0 I get a single line ( because the import doesn't handle the hairline )
ii) in 4.1 you see a hairline ( well maybe more dash than dot but better than a single line )
Regardless of 4.1 or 4.0 the SAME Microsoft border type is exported, how it is displayed is different
> If you open the cell property of a cell having border you can find
> that "line style" combo box is blank! ie the 0.05 pt style single line (the
> default single line) of previous version of Calc (4.04) is not recognised
> properly by the new version.
so, indeed it is blank, I don't know why ( perhaps that line style isn't supported natively in odf ? ) certainly that line style is not supported in the dialog. But..... so what ? the import is trying it's best to show you what the document would look like in excel, that imho is the correct thing to do
> Further, on printing the spreadsheet using the new 4.1 version, the dashes
> are not printed on all cells (most of the cells appear without any borders).
> This is a big nuisance, since all old spreadsheets have to be edited and the
> borders have to be changed when you open with the new version.
like I said, I have no idea about printing but that is a separate issue and deserves a separate bug.
Created attachment 84676 [details]
Sheet made with Calc 4.04
To replicate the problem:
Make a spreadsheet with the previous version of Libreoffice Calc (4.04) with some cells with the default single line borders (0.05 pt thick) and save the file as .xls
Now open this .xls with the new version of LibreOffice Calc (4.1), the single line borders shows as dashes on the screen. If you open the cell property of a cell having border you can find that "line style" combo box is blank! ie the 0.05 pt style single line of previous version of Calc (4.04) is not recognised properly by the new version.
A sample sheet made with Calc 4.04 is enclosed. Opening it with Calc 4.1, you can find the above problem. You can find
1. 0.05 border has changed to dashes which is not shown in Print Priview or printed
2. 0.50 border has changed to 0.05 thick border
3. 1.00 border has changed to 1.5 thick border
(In reply to comment #17)
> Created attachment 84676 [details]
please don't attach endless copies of the same document to the bug
> Now open this .xls with the new version of LibreOffice Calc (4.1), the
> single line borders shows as dashes on the screen.
and this is the closest to what you would see if you opened the document in Excel ( where is the problem ? )
> If you open the cell
> property of a cell having border you can find that "line style" combo box is
> blank! ie the 0.05 pt style single line of previous version of Calc (4.04)
> is not recognised properly by the new version.
it has already being explained to you that libreoffice does understand the line style ( the dialog does not )
> A sample sheet made with Calc 4.04 is enclosed. Opening it with Calc 4.1,
> you can find the above problem.
it is not (imho) a problem
> You can find
> 1. 0.05 border has changed to dashes which is not shown in Print Priview or
the dashes are IMHO not a problem, the printing issue is a separate thing ( already suggested to you to open a new issue for that )
> 2. 0.50 border has changed to 0.05 thick border
> 3. 1.00 border has changed to 1.5 thick border
So what ? you expect that you save borders of a certain thickness from a native Calc format to an Excel format and you think the border thickness will survive the roundtrip? Excel doesn't store border line thickness in it's format, Excel only stores different border styles, the thickness of the border lines that Calc uses to display different Excel borders has been changed ( this have been explained to you ) to display those borders more like Excel does.
Yes Noel, you are right when you say that a 0.05 thick lines made using Calc 4.04, saved as .xls and opened with Calc 4.1 appears as dashes which is more similar to the way it is shown (as dots) when opened with Excel.
While printing the above file (or during print preview) Calc 4.1 does not print most of these borders, whereas Excel prints as very thin line (just like in Calc 4.04 and what you intended). As you said this may be a separate issue. Should I create a separate thread?
However if you make a 0.05 thick lines using Calc 4.1, save it as .xls and open it again with Calc 4.1 the border shows as thin lines (0.05 thick) and prints as very thin lines. However opening this file (made with Calc 4.1) using Excel, the border will show as a normal single line and not as dots as it should have show and prints as a thicker line
Further Dotted, Normal and Thick lines made with Excel imported to Calc 4.04 showed as 0.05, 0.2 and 0.60 thick lines and when imported to Calc 4.1 showed as dashes (does not print), 0.05 and 1.0 thick lines.
*** Bug 69257 has been marked as a duplicate of this bug. ***
I reviewed little bit the last comments and my 2cents:
First of all, I see a problem, that single lined-borders are now shown as dotted. I don't expect them, cause IFFF I really would have liked them this style, I would have marked it previously that WAY!
Second I would interpret the import as somehow incomplete resp. the border-style not properly internally translated. If you open the format-cell => border, then there is the style not selected in the Combobox. I would say from my experience that the style which is set for THIS combobox is due to out of the selection range, i.e. not a valid selection. Therefore I doubt that the borders are translated (internally) properly.
Third I guess that there was always a bug in saving the borders properly. BUT I guess additionally that there was a specific style set, which can hardly be set manually.
Fourth I see it as an Import problem, which has problems with the proper translation. The export might work now the way as Excel work, but anyway as an user I am not really interested what will happen now with the save.
Last but not least I have a major problem with this new feature, cause I have several spreadsheets formatted with borders. And when there are not even with printing available I see this issue as a major blocking point for upgrading. Especially I use LibreOffice only and the XLS-mode is the compatibility mode for all users.
Problem of Cell border of spreadsheets created with previous version of Calc (4.0) not showing/printing properly in 4.1 verion of Calc has not been resolved in the 18.104.22.168 version (Tested in Windows 8.1 and 7)
*** Bug 67932 has been marked as a duplicate of this bug. ***
Is it possbile to have some kind of workaround? e.g. Run some specific macro (within LibreOffice) and the border-lines are set properly?
*** Bug 66988 has been marked as a duplicate of this bug. ***
I think I know why. This issue is must because of the patch in Bug 56960: FILEOPEN particular .xlsx shows cell border lines with wrong FORMATTING (too thick).
Before LibreOffice 4.1, LO "shows cell border lines with wrong FORMATTING". This was a bug, and was fixed in 4.1. See comments 19 and 30 in 56960.
Thus, xls and xlsx files exported with LO version before 4.1 was wrong, and after 4.1 is right, as compared with how it shows in MSO.
So, as 56960 was a bug, 68148 should not be a bug.
The default border in Calc 4.1 is of 0.05 thick whereas the default border in MS Office 2010 is a much thicker line when you print.
The equivalent line thickness when you print are as given below:
Calc 4.1 default border (0.05 thick) -> MS Office Dotted Line border
Excel 2010 default border (Normal Single line border) -> Calc 4.1 about 0.60 thick line
When exporting to .xls file
Calc 4.1 Exports 0.05 and 0.60 thick borders as Normal Excel border.
Whereas Calc 4.0 used to export 0.05 border as dotted line and 0.60 thick border as Normal border in Excel
While viewing on the screen the way Calc 4.1 exports is OK, however while printing the Calc 4.0 results were more like Excel.
In case of import of .xls files
Calc 4.1 does not recognise the MS Office Dotted Line border format. (The line style in the property box is blank and the line is shown as dashed line on the screen and does not print) - This is a big problem since Calc 4.0 used to export its default border (0.05 thick) to MS Office Dotted line and such borders of all the existing files made with Calc 4.0 are shown as dashes and not printed now.
Calc 4.1 imports Excel 2010 default border (Normal Single line border) as 0.05 thick border. While viewing of the screen this is OK, however while printing the line thickness is much less when compared to Excel.
Calc 4.0 used to import MS Office Dotted Line border as 0.05 thick line and Excel 2010 default border (Normal Single line border) as 0.20 thick border. So, the printing was much similar to the result obtained in Excel.
Hope some changes are made to the next upgrade since lot of files saved using previous versions of Calc cannot be printed unless the cell borders are edited and changed.
I took a brief look in the patches of Bug 56960 and see that some of the internal defintions of a border were removed resp. added/changed in the code. Therefore I am not really surprised to have new side-effects.
I see some image-samples from Ashok and wonder now if there is/was a complete test-matrix available. e.g.
Verfiying solid, dashed, thik-lines:
save in LO3.6/Win ==> open in 4.1 Win/Linux/...
save in LO4.0/Win ==> open in 4.1 Win/Linux/...
save in LO4.0/Linux ==> open in 4.1 Win/Linux/...
etc.etc. Since I see here several combinations I wonder if this would make sense to have one. Otherwise the same problem will reappear due to sideeffects. I mean the images are a good starting point, but for me it is highly unclear what is an error and what not. e.g.
exported thickness of 1.0 in LO4.1 ==> looks different in 4.0 ==> error or not?
exported thickness of 1.0 in LO4.1 ==> looks different in Office 2007/2010 ==> error or not?
I would guess that due of a missing test-matrix neither the developers nor testers have a real clue what needs to be done to finally get rid of this issue. Test-cases by cherry picking is option but have a high risk ending up as zombies.
Created attachment 89749 [details]
xls file showing how cell borders are exported and inported in Calc 4.0 and 4.1
The enclosed spreadsheet contains details of
1. How cell borders of Microsoft Office Excel 2010 look when they are printed and how they look when the file is saved as .xls and opened using LibreOffice Calc 4.0 and Calc 4.1.
2. How cell borders of Calc 4.0 and 4.1 look when they are printed and how they look when the file is exported as .xls and opened using Microsoft Office 2010.
Note : In the enclosed sheet the images of the printout are shown enlarged so that the difference can be noticed.
Created attachment 89767 [details]
How cell borders change when exporting from Calc 4.0 to 4.1 and Excel
The uploaded xls file shows how cell borders are exported and imported in Calc 4.0 and 4.1 (ie make a spread sheet using one program, save as ".xls" and open using another program)
The enclosed spreadsheet contains details of how cell borders look / print when exported from:
1. Microsoft Office Excel 2010 to LibreOffice Calc 4.1 / Calc 4.0.
2. LibreOffice Calc 4.1 to Microsoft Office Excel 2010 / LibreOffice Calc 4.0.
3. LibreOffice Calc 4.0 to Microsoft Office Excel 2010 / LibreOffice Calc 4.1.
Note : In the enclosed sheet the images of the printout are shown enlarged so that the difference can be noticed.
Forgot to add that I have tested only on Win platform (Win XP, 7 and 8.1). Since I do not have access to Linux or Mac machines, I could not check if the problem is there only on Win platforms
(In reply to comment #28)
> I would guess that due of a missing test-matrix neither the developers nor
> testers have a real clue what needs to be done to finally get rid of this
> issue. Test-cases by cherry picking is option but have a high risk ending up
> as zombies.
Even more: from what I understood from a discussion on the dev list, is that there are so many corner cases, due to different mechanisms and backwards compatibillity, that solving all is a sort of... let me say really hard ;)
Of course any dev is free to say that I'm wrong here ;)
All the more reason - for when you have the change - to work with Styles ..
For printout compatibility import of "xls" by LO Calc should be as follows
1. Dotted Line -> 0.05 thick Single Line
2. Dashed Line -> 1.00 thick Dashed Line
3. Longer Dashed Line -> Could not replicate on Calc
4. Normal Line (Default in Excel) -> 1.0 thick Single Line
5. Thick Line -> 2.0 thick Single Line
6. Thicker Line -> 3.0 thick Single Line
7. Double Line -> 3.0 thick Double Line
The above data has been compiled based on how Calc 4.0 and Calc 4.1 prints it border now.
As Noel had pointed out Excel format has only line styles and there is no option to change thickness except through the line style.
So while exporting to "xls", single line of thickness between .20 and 1.5 may have to be saved as Normal Line, between 1.5 and 2.5 may have to be saved as Thick Line and between 2.5 and 3.5 may have to be saved as still Thicker Line.
Further line thickness of Double line and Dashed Line available in Calc will have not effect when exported to "xls"
Printing problems may root in the fact that 0.05pt is very thin. 1pt is 1/72 inch, so 0.05pt is 1/1440 inch, it is hardly printable even on a laser printer, not to mention that screen resolution is 75-100 DPI only. Where did this default come from?
Created attachment 90135 [details]
PDF showing how Borders are printed in Calc 4.1
The line thickness when applying borders using the border button on the toolbar in Calc is 0.05pt. So I suppose that this is the default thickness. Further printout with this thickness is very much visible. The PDF shows how borders are printed (Only borders which are imported from .xls are shown. Calc supports other border thickness and patterns and they can be saved in .ods format)
Even if 0.05pt would calculate to 1/1440 pt for printing I have problems to understand the impact. Reg. Wikipedia 1200 dpi-printers are commonly available since 2008, so this would lead somehow to at least one continuous dotted line. Yeah, perhaps 0,06 pt would be more appropriate.
But I do NOT understand why there is so heavy focusing on printing, cause the lines are also not visible on screen. And even they would be visible on a printout I still would treat this bug as a blocking point for an upgrade to 4.1 due to lack of visibility during work. Since the lines were visible prior to 4.1 I would expect to have them back despite slick talk and arguments why they should disappear.
all of our .xls files (saved with LO 4.0.4) have dotted lines on screen and won't print most lines with LO 22.214.171.124.
As a workaround our colleagues are now saving those .xls as .ods files and everything is fine.
This is why we are afraid of upgrading LO versions, those side effects with old .xls are hard to understand for our users.
For me this is a excel compatibility problem and currently a point against LO.
The "Main trouble" is:
Different behaviour of same document in different versions of LO.
(About my own documents:) Documents were created by "MS Excell" and used for last 3 years. They stay ".xls" because they SHOULD be compatible with "MS Excell". Because I should be able to present "compatible" documents for "MS 2007 Excell" (I hate this buggy instrument, that why I use LO).
on LO versions 4.0.X I got "The same result" as in "MS Excell".
If I "fix" borders thikness in LO versions 4.1.X should I get normal result in
If not... You should not recommend to upgrade to less compatible LO version.
People, please stop adding useless comments, such as anger, venting, crying etc. The more you do it, the more difficult it becomes for anyone to pick up and work on. And THAT IS VERY IMPORTANT!!!
Could someone please summarize this bug in a concise manner?
Sorry, but the last comment I don't understand. The files provided by Ashok make quite well clear the problem of this bug. I would appreciate if you follow your own words, i.e. make clear what questions you have to the files provided by Ashok, make clear what kind of information you are missing and please, stop reinventing the wheel from the very beginning. The mentioned filed gives a perfect overview. And please stop as well from whining around comments you dislike - sitting in the glasshouse, throwing pebbles?
BTW: The bug is a showstopper - at least for my case, cause as already mentioned, it prevents an upgrade from 4.0.x to 4.1. And as matter of fact, if this cannot be fixed, then I have to thing of alternatives....
So, what is unclear with the file?
Look at the summary:
" single line cell borders turn dashed when opened with different LibO release "
then comment #15
" The problem is not that single line borders appears as dashed lines on the screen. "
and then there seems also saving as xls is involved
" ... when saved as .xls "
Well, I may expect that there are quite some more remarks spread over the app. 30 comments that might give relevant information, or maybe make the pciture less clear, or belong to another issue...
IMO the request from Kohei is approrpiate and useful:
one comment giving a proper and comprehensive picture and
with (reference to) one or two attachments,
preferably with an updated summary line and there a pointer to comment #x)
would be helpful indeed.
And yes, it could be a lot of work to check all comments and test all mentioned situations.
The first attachment from Ashok makes it clear, what the bug is about. So, what is unclear about that summary of the file? Did you ever open it?
It already fulfills your first two of your requests.
Perhaps the subject-adaption is still missing, but all your requests are already fulfilled. And to be honest, I have no real clue, what an appropriate subject for this bug would be. Something like ? Cell-borders shown differently in different L.O version ? Inconsistent border handling over versions? But both excludes the different behavior to MS - which I cannot evaluate if THAT cross-compatibility should be covered here as well. I would guess that was not the reason why this ticket was opened, but I think it is reasonable if somebody fixes this issue, it should cover MS as well.
Anyway, I come back to my previous statement:
So, what is unclear with the file?
(In reply to comment #42)
> The first attachment from Ashok makes it clear, what the bug is about.
So then why do we have appr. 30 extra comments with opinions, information, Q&A :)
I mean: knowing 'what it is about', is not the same as being able to give a valid judgement in detail and desired solution.
Reading through the contributions (again) I can only point to what I wrote in comment #32 . And sometimes improvements in one direction, alas cause problems in another one (and I do not judge which is more or less important)
(This is an automated message.)
Setting priority to highest as this is a 4.1 MAB. This is part of an effort to make the importance of MAB reflected in priority too.
Issue is there in 126.96.36.199 release also
(Single line Cell Borders created using Calc 4.0 and saved as .xls when opened with Calc 4.1 or 4.2 shown as dashes and is not printed)
The cell border issues when importing xls made with earlier versions of Calc have been resolved in 4.2.1 RC1 version.
Hairline border (0.05pt thick) made with Calc 4.0 and earlier versions shows as 0.05 thick when opened with 4.2.1 RC1 versions and prints ok. (When opened with version 4.1 and 4.2 it showed as dashes and was not printed!)
The Hairline border (0.05pt thick) made with Calc 4.1.0 (to Calc 4.2.0) when opened with 4.2.1 RC1 shows as 0.75 thick, but this is OK and not an issue at all
Thanks Kohei Yoshida for your wonderful work.
More details on this can been seen on his blog post:
according to previous comment let's mark this as RESOLVED FIXED
well done Kohei
Any New Border added on cells getting lost on saving as xls file.
Version with problem is 4.2.1 rc1. Used OS windows XP sp3
Existing border preserves saved using old versions & copy-paste also preserves on save. But If add a new border, on save it lost & reopen shows no border.
You need to file a new bug. Let's put this one to rest.
Created attachment 94253 [details]
document before save
Created attachment 94254 [details]
document on save
Created attachment 94255 [details]
document on save
Created attachment 94256 [details]
document after reopening
(In reply to comment #53)
> Created attachment 94256 [details]
> document after reopening
As Kohei already mentioned, you need to open a new bug report for your problem. Please respect that we use one bug report for exactly one problem. Even if your problem might look related it is still another problem.
(In reply to comment #54)
> (In reply to comment #53)
> > Created attachment 94256 [details]
> > document after reopening
> As Kohei already mentioned, you need to open a new bug report for your
> problem. Please respect that we use one bug report for exactly one problem.
> Even if your problem might look related it is still another problem.
Reg. new Bug:
IFFF I talk terms of ITIL then I am curious where is the ticket related to the problem?
To my understanding the commits of Kohei fix the bug with cells don't have border when importing from 4.0 to e.g. 4.1. Which is great. According to James there is a new (???) issue arising with the new fix (Q: was is introduced or already there?)
My point here is the danger of an ongoing series all for fixes related to border-issues. Each reported bug might be fixed but nevertheless the main problem (Save Borders and Import) will never (???) be fixed - simply due to missing overview... Therefore you have in ITIL-terms something called 'Problem-ticket' with different variations of a problem.
Please don't get me wrong, I agree to the fact one ticket for one bug and fixing an issue can only be done on base of a regular ticket. I highly appreciate the work done by Kohei. I just wonder if the current approach of individual tickets might get the proper results (e.g. loss of priority although all aim the same target).