If you draw cell borders then you can't format cell style in excel, bug?

[SunyD]

Hello,
if you draw border an any side of cell, then you can't open format cell dialog in excel more.
I tested it with Excel Xp and Excel 2012.

You can test it with spready application or with the example from below.

How to test:
1.) Open test2.xls which created by the example program below in Excel.
2.) select one of the cells from first or second row (below or under the cell with line)
3.) now you can't open Cellformat dialog in excel.

Is this bug or my mistake?

Code: Pascal  [Select][+][-]

1. program excelexport1;
2. uses sysutils, laz_fpspreadsheet,fpspreadsheet, fpsTypes;
3. var  aWbk: TsWorkbook;
4.      aWst: TsWorksheet;
5.      r,c: integer;
6.      s:string;
7. begin
8.   aWbk := TsWorkbook.Create;
9.  try
10.   aWst := aWbk.AddWorksheet('Tabelle1');
11.   for r:=0 to 4 do begin
12.     for c:=0 to 3 do begin
13.       aWst.WriteText(r,c,Format('%d:%d',[r,c]));
14.       if r=1 then aWst.WriteBorders(r,c,[cbNorth]);
15.     end;
16.   end;
17.   aWbk.WriteToFile(ExtractFilePath(ParamStr(0))+'test2.xls', sfExcel8, true);
18.  finally
19.   aWbk.Free;
20.  end;
21. end.
22.  

[wp]

These nasty BIFF palettes...

Analyzing this bug I found out that Excel seems to consider the first 8 colors as being read-only and duplicates them into the next 8 palette entries as editable colors. The black of your cell borders was written as palette index 0, i.e. Excel refuses to edit this color - the drastic way of silently not showing the entire cell format dialog at all seems to be a bug of Excel...

Writing the black border color as the editable palette index 8 (instead of the readonly 0) solved your problem. However, this resulted in some fails of the standard unit tests which took me some time to fix.

[SunyD]

Thanks,
yes it must be Excel bug. Libre Office opened the dialog.
Excel is weird.
Now I changed the border color to the value of $01010101 and Excel opens the dialog but show only this color for fill and border color.
Even if you create a spreesheet without borders it shows no color in color dialogs.
And I draw in another code borderline like in the code from posting 1 and in some exports it show the format dialog and in somer others it doesn`t show it. From same export code 

Which format is easier, better or most completed sfExcel8 or sfOpenDocument?

[wp]

Did you already try the new revision? As I wrote in the previous post the old fpspreadsheet had written the border color as palette index 0 which is read-only for Excel. The new version duplicates the 8 built-in colors to be modifiable, and assigns the border color to palette index 8 which is black as well but can be modified. Excel opens the cell format dialog on my system now.

Look at http://wiki.lazarus.freepascal.org/FPSpreadsheet#Progress_by_supported_cell_content to learn about the "completeness" of the various file formats. I am using quotes here because there are still many more spreadsheet topics which are not implemented at all.

Since your issue is related to color palettes I would avoid the old .xls BIFF formats (sfExcel8, sfExcel5, and of course sfExcel2) since this is the usual way these formats handle color. sfOOXML (.xlsx) and sfOpenDocument (.ods) treat color directly as RGB integers (like TColor).

[SunyD]

I  tought most common version is sfExcel8. To many programs can handle it without errors, Libre Office, FreeOffice ...  Even with I use with Libre Office, FreeOffice sfExcel8 format as default open save format. Maybe I"m doing something wrong
I'm trying the new version now.
Thanks.

[SunyD]

Now I used svn-version from lccr, it works, big thanks.
There are colors on color palatte now. Before your modifications there was no color except the one or two that is used in spreadsheet and have same color value as excel excepts.
I put screenshot from color palette, one is from fpspreadsheet export and the another one with all colors standard excelxp palette.

[wp]

Yes, sfExcel8 is about the same level of completeness as xlsx or ods. It just carries some relics of ancient time, such as the color palettes. As practical consequence is that you can use only - I think - 64 colors in total. Another limitation is the reduced count of columns (256). Normally these limitations are not relevant (and fpspreadsheet has issues with super-large files anyway...), therefore sfExcel8 is as good as the others in practice. It has the advantage that there may still be programs around which cannot read the new xml-based formats.

[wp]

I put screenshot from color palette, one is from fpspreadsheet export and the another one with all colors standard excelxp palette.

My Excel (2007) does not show the same dialog...

Is this screenshot from the new version? If yes, this would mean that I'll have to write the entire Excel palette to file. ATM I am only writing the 8 built-in "EGA colors" plus the user-defined rgb values used in the workbook.

[SunyD]

Yes the screenshot is from new version, checked out today at ~ 10:20 am.
The Excel 2011 has also other color palette.

[wp]

Please try the new r5863. It fills empty palette entries by the default colors of the file format, i.e. there should not be any more empty color boxes in  Excel's color dialog (as mentioned above, I cannot test this myself...)

[SunyD]

I did it, R5864. You are great man, big thanks.
Looks good most colors are there only some are missing.
White areas are missing colors, except the one I marked.

[wp]

8 colors missing... Sounds like the duplicated builtin-color are kicking out something?

Could you post the files for which you showed the complete (reply #5) and the incomplete color palette (reply #10)? Since the forum software probably will not accept .xls, pack the two files into a zip file.

[SunyD]

Here is it.

[SunyD]

I found out Image adding and saving doesn't works for sfExcel8 format. I made this:

Code: Pascal  [Select][+][-]

1.   aWst.WriteImage(1, 1, aDir+'logo.bmp'); //logo.png, tried both format
2.   aWbk.WriteToFile(aDir+'test2.xls', sfExcel8, true);  //  image not saved
3.   //aWbk.WriteToFile(aDir+'test4.xlsx', sfOOXML, true);  //  image saved, it is ok
4.  

I let run the save code from fpspreadsheet.pas in debugger step for step. It passed without error, found  image ... But if you open the produced excel file in Excel/FreeOffice/LibreOffice there was no image.

[wp]

I found out Image adding and saving doesn't works for sfExcel8 format.

I know, it is documented in the wiki (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Progress_by_supported_cell_content). The problem is in the additional non-BIFF records recquired. The best image support is for ods where you can read and write images.