Cannot format Ranges

[obeltrami]

Hi,

Code such as the one below

Code: Select all

          xls.Sheets[0].Range.Items[0,0,7,0].FontStyle := [xfsBold];
          xls.Sheets[0].Range.Items[0,0,7,0].FillPatternForeColor := XColorToRGB(xcGray25);

which worked fine with XLSReadWrite 4, now crashes systematically with v.5 (access violation or Index 27 out of bounds)

Did the syntax change between v.4 and v.5 ?

Very best regards,

Olivier Beltrami

[larsa]

Hello

Use CmdFormat instead of Range. Please study the FormatCells sample.

[obeltrami]

Thank you Lars, this has helped in the reports that I have converted.

However, I am still getting a lot of oddities in the 60+ reports that I migrated from v4 to v5; text set in cells not appearing, first default sheet being buggy and needing to add a new sheet and delete the default sheet, autosize failing randomly for row heights. All in all out of the 60+ reports that I have painstakingly put together over the years, not one comes out intact after migrating to v5.

Are there any other breaking changes such as CmdFormat/Range that I should know about ?

Also, I create all the components on the fly (xls := TXLSReadWriteII5.Create(nil);), should I initialize something when moving to v5 ?

[larsa]

Hello

Sorry if the upgrade has caused you problems, but the full implementation of the Excel 2007/XLSX file format forced us to do a lot of changes to the cell formatting. If you have any issues that you can reproduce, please let me know and I will try to help you.

[obeltrami]

Thank you for your prompt reply.

Over the weekend I have continued replacing ranges with CmdFormat, and things are looking better. The sample project was a nice place to start.

But now, after spending a few days in the conversion, I have some conceptual questions (BTW: I am always using xcfmMerge):

• Is there a preferred order to the attributes added to CmdFormat ? For example first font, then shade, ...
• Should I add text before using CmdFormat ?
• If I am dealing with a group of merged cells, should I apply CmdFormat to all of them, or just the top-left cell ?
• Is there a minimum set of attributes that I must set in ? Or can I just start a BeginEdit and just add font color and apply ? The reason I ask is that sometime, it seems I need to specify also the font name and size otherwise the cell is formatted with unwanted new fonts.
• I am having a lot of problems with AutoHeightRows() for merged cells that contain wrapped text. In the same document, on some tabs it works and on some other tabs it does not. Is there a way for me to query a given cell and ask on how many lines the text has run ? Then I could simply re-size the row.

Very best regards,

Olivier

[larsa]

Hello

>>Is there a preferred order to the attributes added to CmdFormat ? For example first font, then shade, ...
No, that shall not matter.
>> Should I add text before using CmdFormat ?
Doesn't matter. CmdFormat will add a blank cell if there is no cell.
>> If I am dealing with a group of merged cells, should I apply CmdFormat to all of them, or just the top-left cell ?
All merged cells shall be in the area.
>> Is there a minimum set of attributes that I must set in ? Or can I just start a BeginEdit and just add font color and apply ? The reason I ask is that sometime, it seems I need to specify also the font name and size otherwise the cell is formatted with unwanted new fonts.
If no font name is given, the default font is used. At least that is the intention.
>> I am having a lot of problems with AutoHeightRows() for merged cells that contain wrapped text. In the same document, on some tabs it works and on some other tabs it does not. Is there a way for me to query a given cell and ask on how many lines the text has run ? Then I could simply re-size the row.
AutoRowHeight don't works with wrapped text. You can't get the line count of wrapped text as that is not calculated. This is the reason why AutoRowHeight don't works with wrapped text.

[obeltrami]

AutoRowHeight don't works with wrapped text. You can't get the line count of wrapped text as that is not calculated. This is the reason why AutoRowHeight don't works with wrapped text.

I have found a way to calculate the number of lines, and Height, using TCanvas and DrawText, based on the font and width of the columns.

However, when I try to set the row heights accordingly:

Code: Select all

XLS.Sheets[iSheet].Rows[iRow].PixelHeight := heightLines;

Then it does not seem to work. If I open the resulting file in OpenOffice the row Height is a bit more than the normal height, but nowhere near what I specified. If I open the file in excel, then the row height is simply un-changed. weird.