Issue with DefaultRowHeight

Hello,

program Demo;


{$APPTYPE CONSOLE}


uses
  System.SysUtils, Winapi.Windows, Winapi.ShellAPI, Vcl.FlexCel.Core, FlexCel.XlsAdapter;


const FILE_NAME = 'FlexCel.xlsx';


procedure SetDefaultFont(const AXls: TXlsFile);
var
  oFormat: TFlxFormat;
begin
  oFormat := AXls.GetDefaultFormat;
  oFormat.Font.Size20 := 10 * 20;
  AXls.SetFormat(AXls.DefaultFormatId, oFormat);
end;


var
  oXls: TXlsFile;
  I: Integer;
begin
  try
    oXls := TXlsFile.Create(True);
    try
      oXls.NewFile(1, TExcelFileFormat.v2019);


//      SetDefaultFont(oXls);


      oXls.DefaultRowHeight := 500;
      oXls.DefaultRowHeightAutomatic := False;
      for I := 1 to 50 do
        oXls.SetCellValue(I, 1, 'abc');
      oXls.Save(FILE_NAME);
      ShellExecute(0, nil,FILE_NAME, nil, nil, SW_SHOWNORMAL);
    finally
      oXls.Free;
    end;
  except
    on E: Exception do Writeln(E.ClassName, ': ', E.Message);
  end;
end.
If you run abowe program everything works ok. But if you set DefaultFont (please uncomment SetDefaultFont(oXls) line) first 20 rows has DefaultRowHeight while rest rows has Excel default row height. Is procedure SetDefaultFont() wrong? How could I fix this issue?

TIA and best regards,
Branko

Hi,

Thanks for reporting this. It actually looks like a bug in Excel (introduced in Excel 2010: Excel up to 2007 will open the file fine). If the font is not the standard font, and the global row height is not standard either, and you don't save the row height for every row, Excel (2010 or newer) seems to apply the not-standard row height only to the rows that roughly fit on a screen.

We will workaround this, but I need to think a little about it, since this is a very core thing and we risk breaking other stuff. The simplest workaround (what Excel does) is to set all row heights to not automatic, but this is not the same as what the code abobe should be doing.

While we fix this, a simple workaround on your side would be to set all row heights manually before saving:



      for I := 1 to 50 do
        oXls.SetCellValue(I, 1, 'abc');


      for I := 1 to oXls.RowCount do
         oXls.SetAutoRowHeight(I, false);
      oXls.Save(FILE_NAME);


This looks like what we will have to do anyway internally before saving, because Excel is not doing what it should do. As said, we will have soon a new version fixing this (once we evaluate all possible ways to workaround the bug and the impact it would have). But in the meantime, the code above should work.
The other soultion is of course if possible, not to change the normal font. 

Thanks for answer. This workaround only works if all rows has default height. But if you change some row height this workaround doesn't works. For example:

      for I := 1 to 50 do
        oXls.SetCellValue(I, 1, 'abc');
      oXls.SetRowHeight(3, 800);


      for I := 1 to oXls.RowCount do
         oXls.SetAutoRowHeight(I, false);
The only reliable solution is to explicitly set the row height for each row.
      for I := 1 to 50 do
      begin
        if I = 3 then
          oXls.SetRowHeight(I, 800)
        else  
          oXls.SetRowHeight(I, oXls.DefaultRowHeight);
        oXls.SetCellValue(I, 1, 'abc');
      end;
The only sense of DefaultRowHeight is that have rows after RowCount DefaultRowHeight

I don't know if you noticed - you can't change the default font name. Please try the following (font name is "Calibri" not "Arial" - Excel 2016):
procedure SetDefaultFont(const AXls: TXlsFile);
var
  oFormat: TFlxFormat;
begin
  oFormat := AXls.GetDefaultFormat;
  oFormat.Font.Name := 'Arial';
  oFormat.Font.Size20 := 16 * 20;
  AXls.SetFormat(AXls.DefaultFormatId, oFormat);
end;

The default Font.Name doesn't work in Excel 2007 neither.

Hi,

I am not sure why you say the workaround wouldn't work if you set heights manually. Have you run the code you posted? It seems to work fine here.

Actually the line:

oXls.SetRowHeight(3, 800);


Will internally call the equivalent of oXls.SetAutoRowhHeight(3, false), because when you set a row height to a manual value, it is not automatic anymore. When you later do:



for I := 1 to oXls.RowCount do
         oXls.SetAutoRowHeight(I, false);


It will set the rowheight of row 3 to be manual, which already was, so it won't change anything.

The only drawback of this solution is the opposite: that it will set rows which were to autofit to have a manually set size. If you don't want some rows to have fixed size, you will have to skip them when setting them to autorowheight=false.

The code:

oXls.SetRowHeight(I, oXls.DefaultRowHeight);


Is not different from 

oXls.SetAutoRowHeight(I, false);


And yes, the main sense of having a defaultrowheight is so rows which have no data display at a given size. (they might be after row count, or they might be in between. For example if you don't set a value in rows 3 and leave the row empty: In that case the row will have the default row size)

I think we have figured out how to workaround the issue you see here, but I think I might need to explain in a little more detail how row heights work in Excel.

1. First of all, with all the standard data, a row height in Excel is automatic, to all the cells. If you increase the font in a cell, the row will be autofitted bigger.

2. If you set a default row height of say 500, then all empty rows will be 500. Now, when you enter data in a previously empty row, that row won't be autofitted. Excel will create a row record with no autofit and the default size, and store that. FlexCel is currently creating a row record with autofit and the default size, and this is causing the problem, because when the row is autofitted, it won't be the default size anymore.

To be more clear, in the example you sent, the bug in Excel is in the first 20 rows, not in the others. The first 20 rows should be autofitted and not default size.
We are looking into making FlexCel create a row record that is not autofitted when you call xls.SetCellValue(some empty row, column). This way, it would work as you expect it.

But a simple workaround as said is to just set the row to be not autofit. You could do it when you create the row, or in a loop at the end as in the example I sent.

To change the font name, you either need to change the spreadsheet's theme font to be "Arial", or detach the font from the scheme, by doing:



  oFormat := AXls.GetDefaultFormat;
  oFormat.Font.Size20 := 10 * 20;
  oformat.Font.Name := 'Arial';
  oformat.Font.Scheme := TFontScheme.None;
  AXls.SetFormat(AXls.DefaultFormatId, oFormat);




Please read http://www.tmssoftware.biz/flexcel/doc/vcl/tips/changing-the-font-name.html for more information.

Hi,

Sorry, my mistake, it works. When testing, I overlooked the added code that affected the result.

Just to let you know FlexCel 7.1.1 was just released and it includes a fix for the row height issue which doesn't require to use the workaround anymore