Format entire sheet

FLX TMS FlexCel for VCL & FMX
1
Hi Adrian

If I select an entire row or column I can use SetRowFormat/SetColFormat but there doesn't seem to be a method for the entire worksheet. I checked the source of AddFormat but of course that applies to the workbook. I'm currently just setting each column format individually which leads to a small delay when opening the workbook (also in Excel).

It would be nice to have a way to set the entire worksheet if that's possible, although it's not a show-stopper since the opening delay is small.

Cheers, Bob
2

Hi,

Sadly there is no attribute in the file format for the whole sheet. So you need indeed to set all the columns, and that's what Excel does internally if you select the full sheet and set a format. You can verify it easily with APIMate: Set the full format to something in Excel, then open the file in APIMate

For example I set all the cells to red, and I got this in APIMate:





 xls.SetColWidth(1, 16384, 2321);  //(8.32 + 0.75) * 256


  ColFmt := xls.GetFormat(xls.GetColFormat(1));
  ColFmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
  ColFmt.FillPattern.FgColor := TUIColor.FromArgb($FF, $00, $00);
  ColFmt.FillPattern.BgColor := TExcelColor.Automatic;
  xls.SetColFormat(1, 16384, xls.AddFormat(ColFmt));

Now, note that while this code sets all columns, it sets them in a single call, which is faster than setting them one by one.
Sadly we can't do much better than that. It is weird that Excel doesn't have an entry for "Format of the full sheet" but that's the way it is.

If you want to apply the same format to all the sheets in the workbook, not to a single sheet, you might change the normal font. For example the code:


 //Styles.
  StyleFmt := xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0));
  StyleFmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
  StyleFmt.FillPattern.FgColor := TUIColor.FromArgb($FF, $00, $00);
  StyleFmt.FillPattern.BgColor := TExcelColor.Automatic;
  xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt);

Will change all the sheets in all the file to be red.
3
Thanks Adrian - that's very useful to know.

Cheers, Bob