fpspreadsheet large file

[Hansvb]

I try to write a lot of records to an xlsx file.

Nothing happens. I get no errors the program starts en de pc is working hard. But there is nothing written to an excel file.
What's wrong?

Code: [Select]

procedure TMain_frm.Button2Click(Sender: TObject);
var
  workbook: TsWorkbook;
  worksheet: TsWorksheet;
  headerTemplate: PCell;
  t: TTime;
  i,j : Integer;

begin
  try
    workbook := TsWorkbook.Create;
    try
      worksheet := workbook.AddWorksheet('Sheet1');
      worksheet.WriteFontStyle(0, 1, [fssBold]);

      workbook.Options := [boVirtualMode, boBufStream];

      workbook.VirtualColCount := DBGrid1.DataSource.DataSet.FieldCount;  // Define number of columns - we want a column for each field
      workbook.VirtualRowCount := DBGrid1.DataSource.DataSet.RecordCount;  // Define number of rows - we want every record, plus 1 row for the title row

    //headers
        for i := 0 to DBGrid1.DataSource.DataSet.Fields.Count - 1 do
      worksheet.WriteUTF8Text(0, i, DBGrid1.DataSource.DataSet.Fields[i].FieldName);

    // Write all cells to the worksheet
    DBGrid1.DataSource.DataSet.First;
    j := 0;
    while not DBGrid1.DataSource.DataSet.EOF do
    begin
      for i := 0 to DBGrid1.DataSource.DataSet.Fields.Count - 1 do
        worksheet.WriteUTF8Text(j + 1, i, DBGrid1.DataSource.DataSet.Fields[i].AsString);

      DBGrid1.DataSource.DataSet.Next;
      Inc(j);
    end;
//
      workbook.OnWriteCellData := @WriteCellDataHandler;

      t := Now;
      workbook.WriteToFile('test_virtual_01.xlsx', sfOOXML, true);

      t := Now - t;

    finally
      workbook.Free;
    end;

  finally
    //
  end;
end;


[rvk]

When using boVirtualMode you shouldn't write directly to the worksheet (with WriteUTF8Text) but you need to implement the OnWriteCellData method to do the actual writing.

Looking at you code you're doing things double. With the "// Write all cells to the worksheet" you're writing directly to the worksheet (which you shouldn't do in boVirtualMode) and right below it you set the OnWriteCellData. You didn't show that code but with boVirtualMode you should remove the loops with WriteUTF8Text ("//headers" and "// Write all cells to the worksheet") and do all the work in OnWriteCellData.

You can look at the wiki here how you should implement boVirtualMode with the OnWriteCellData.

(Note to wp: It might be an idea to build in some checks when WriteUTF8Text is used there is an error generated not to use that method)

[wp]

There is a worked-out example in the folder examples\db_import_export of how to write a database to a spreadsheet file. In addition you can have a look at the example in the wiki http://wiki.lazarus.freepascal.org/FPSpreadsheet#Converting_a_large_database_table_to_a_spreadsheet_using_virtual_mode.

Your problem is that you do envoke virtual mode (which is a must for large files), but do not provide the required infrastructure correctly: You have to specify the number of fields and datasets to be written (VirtualColCount, VirtualRowCount), and you must provide a handler for OnWriteCellData in which you loop through all records of the dataset and write the data to the spreadsheet. Doing this in the main program is not working in virtual mode.

[rvk]

... but do not provide the required infrastructure correctly: You have to specify the number of fields and datasets to be written (VirtualColCount, VirtualRowCount), and you must provide a handler for OnWriteCellData in which you loop through all records of the dataset and write the data to the spreadsheet.

He did set VirtualColCount and VirtualRowCount and even provided an OnWriteCellData (of which he didn't show the code) but he also did two loops with WriteUTF8Text which should only be used without boVirtualMode. Hence my idea to build in a check to generate an error when using WriteUTF8Text in boVirtualMode.

Does first writing to the worksheet with WriteUTF8Text really interfere with the later use of OnWriteCellData? I don't think so, it's just data collected in memory which isn't used at all. So there must be something wrong in the OnWriteCellData he provided. Maybe that's not returning the data at all. Maybe because of the .next in the main-routine the dataset is already at EOF when it comes to the OnWriteCellData. Either way... removing the two loop from the main-routine should be the first thing to do.

[Hansvb]

OnWriteCellData (of which he didn't show the code)

Because i have no code for the OnWriteCellData. i will look at the wiki again...

[Hansvb]

i missed this in the wiki : What is left is to write the event handler for OnWriteCellData.
I wil  try now

[rvk]

i missed this in the wiki : What is left is to write the event handler for OnWriteCellData.
I wil  try now

And remove the two loops from the main-routine 

I wonder how you did "workbook.OnWriteCellData := @WriteCellDataHandler;" without a routine called WriteCellDataHandler?

[howardpc]

Probably Ctrl-Shift-C, when Lazarus writes the empty handler and updates the class declaration accordingly?

[Hansvb]

My answer was to quick. The example code stood in
WriteCellDataHandler

[wp]

Hence my idea to build in a check to generate an error when using WriteUTF8Text in boVirtualMode

My gut feeling tells me that this would be a bad idea because this method is called also in virtualMode, it just does not write to a cell in the spreadsheet, but to an isolated one on the stack. But I think a better place would be in the writer class: if a cell is written to the stream in virtual mode and if there is on event handler the writer must report an error.

[Hansvb]

Now i get a file (6 MB), But Excel doesn't open it.

Code: [Select]

procedure TMain_frm.Button2Click(Sender: TObject);
var
  workbook: TsWorkbook;
  worksheet: TsWorksheet;
  headerTemplate: PCell;
  t: TTime;
  i,j : Integer;
begin
  try
    workbook := TsWorkbook.Create;
    try
      worksheet := workbook.AddWorksheet('Sheet1');
      worksheet.WriteFontStyle(0, 1, [fssBold]);

      workbook.Options := [boVirtualMode, boBufStream];

      workbook.VirtualColCount := DBGrid1.DataSource.DataSet.FieldCount;  // Define number of columns - we want a column for each field
      workbook.VirtualRowCount := DBGrid1.DataSource.DataSet.RecordCount;  // Define number of rows - we want every record, plus 1 row for the title row

    //headers
        for i := 0 to DBGrid1.DataSource.DataSet.Fields.Count - 1 do
      worksheet.WriteUTF8Text(0, i, DBGrid1.DataSource.DataSet.Fields[i].FieldName);

    // Write all cells to the worksheet
{    DBGrid1.DataSource.DataSet.First;
    j := 0;
    while not DBGrid1.DataSource.DataSet.EOF do
    begin
      for i := 0 to DBGrid1.DataSource.DataSet.Fields.Count - 1 do
        worksheet.WriteUTF8Text(j + 1, i, DBGrid1.DataSource.DataSet.Fields[i].AsString);

      DBGrid1.DataSource.DataSet.Next;
      Inc(j);
    end;  }

      workbook.OnWriteCellData := @WriteCellDataHandler;
      t := Now;
      workbook.WriteToFile('test_virtual_01.xlsx', sfOOXML, true);
      t := Now - t;
    finally
      workbook.Free;
    end;
  finally
    //
  end;
end; 


Code: [Select]

procedure TMain_frm.WriteCellDataHandler(Sender: TObject; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
var
  s: String;
begin
  if ARow = 0 then begin
    AValue :=  DBGrid1.DataSource.DataSet.Fields[ACol].FieldName;   //MyDatabase.Fields[ACol].FieldName;
    DBGrid1.DataSource.DataSet.First;
  end else begin
    AValue := DBGrid1.DataSource.DataSet.Fields[ACol].AsVariant;
    if ACol = DBGrid1.DataSource.DataSet.FieldCount-1 then DBGrid1.DataSource.DataSet.Next;
  end;
end;


[rvk]

My answer was to quick. The example code stood in
WriteCellDataHandler

In that case my hunch was right. In your main-routine you have a loop though the dataset (after .First). So when it comes time for fpspreadsheet to call your WriteCellDataHandler the dataset was already to its end (i.e. EOF) and there was nothing to write to the file anymore. So if you remove the two loops, as i suggested, you code should work fine.

[rvk]

Now i get a file (6 MB), But Excel doesn't open it.

You can also remove the header-loop.

[Hansvb]

That makes no difference

[rvk]

That makes no difference

I can't find anything wrong with your code (other than those loops).

Could you try setting VirtualRowCount to a smaller count (like 5 or 10) to see if the generated file is correct. When it is readable you could increment this by, let's say 50% of you original count and adding 50% of the remainder each time the file is correct. This way you can narrow down the record which is causing the problem.

If even with 5 records your file is corrupt you could try posting it here so we can take a look at that small file why it can't be read by Excel.