File save error

I am working with a very simple test app I have built in XE7. I am filling cells in a new sheet from a dataset. First I fill a row with header captions, then fill subsequent rows with data. Finally, I call:

    XLS.Save(FileName);

I get exception $C0000005. I have traced into code and found that in _UXlsAdapterFile.XlsFile, in the TXlsFile.Recalc routine, it fails in this code:

          if forced or PendingRecalc then
          begin
            FWorkbook.CleanFlags;
            FWorkbook.Recalc(Self, nil);  <--- failse on this call
          end;

If I do not write any rows of data, but do write the headers, it does not fail. If need be, I can send a project which is relatively small. But I really am challenged to understand how I can get through the writing of a row of data -- I reduced it to a single row for testing -- without an exception, but it throws the exception in the call to save.

I am writing only three kinds of data to the cells: Integer, Float, or String.

What can I possibly do in those operations which would result in a memory error affecting the save?

Thanks,

Bill Meyer

Additional data: If I write only to column 1 in the data rows, then save works fine. I can write to column 1 from any of the dataset columns, and it works. But if I change the code to write to column 2, then save fails. 

Hi,


If you can send me the project to adrian@tmssoftware.com I will take a look. From what you say it would be failing in the recalculating step, but it is hard to diagnose because a memory corruption error might be coming from somewhere else. So I think the simplest if you have a reproducible project will be that I take a look at that.

Adrian, I will do so. It is not huge, but not a tiny project, either. But has nothing in it that isn't related to making FlexCel work for us.

Hi,

I think that the memory corruption is happening in this code in your FillCellUpdate:


  if Length(FColWidths) < FMaxCol then
    SetLength(FColWidths, FMaxCol);


 if AccumWidth then
    FColWidths[ACol] := Max(FColWidths[ACol], XLS.GetStringFromCell(ARow, ACol).Length);


Remember that FColWidths is a dynamic array, and it goes from 0 to length -1. You should change it to be:
[CODE]
  if Length(FColWidths) < FMaxCol + 1 then
    SetLength(FColWidths, FMaxCol + 1);
[/CODE]
Once I fixed this, the app worked well. While the bug was manifesting in FlexCel, it was caused by the memory corruption here.

Now, if I might, some unsolicited advise :)

1) I think that since turbo pascal 6 times, the overhead in having {$R+} doesn't compensate the danger of unchecked array access. I myself keep R+ in all FlexCel code except in very specific places where we do performance critical code or base2 math.
I know this was a "play" project, but that is even more reason to have R+ and FastMM in full debug mode. An R+ at the top of the unit would catch this error instantly. (and that's the way I figured it out actually: first thing I did was to set range check on for the project and use FastMM in full debug mode)

2)You can set the format and the value of a cell in the same pass. It doesn't do a big difference really, but it should be a hair faster. Instead of 
 XLS.SetCellValue(ARow, ACol, AValue);
  SetValueFormatted(Fmt, ARow, ACol, AFormat, Align, Style);

You could do something like
  UpdateFormat(Fmt, Format, Style);
  XLS.SetCellValue(ARow, ACol, AValue, xls.AddFormat(fmt));

where UpdateFormat would be something like:
 Fmt.Format := AFormat;
  Fmt.HAlignment := arrAligns[Align];
  Fmt.Font.Style := GetFlexStyles(Style);

As said, it is a very small optimization, but it can remove some cycles if you have millions of rows to export.

Thanks! I have put {R+} into the unit. I normally do have it, and I'm not sure how it was missed here.


I appreciate the suggestion on setting the value and format in a single call. I generally do not have millions of rows to export, but I do have some exports to produce which are sufficiently complex that little optimizations are worth implementing. Although the project itself is for exploring, the base form unit is evolving into an abstraction which will become the foundation for dozens of exports.

At the moment I am riding two horses. Our old export modules in D2007 use a component for export where rows and columns are zero-based. All new work will be in XE7 with FlexCel, therefore one-based. It adds to the challenge, as ingrained practices from the old modules keep appearing in my code, unless I am very careful to remember at all times in which environment I am working.

I do deeply appreciate the excellent support you provide, including the unsolicited comments. ;)