Write CSV
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Hansvb · 2015-04-10 20:27

With the next procedure i write xlsx format. This works great

Code: [Select]procedure TExport_To_File.Export_To_xlsx(Adbgrid:
TDBGrid);
  var
    workbook  : TsWorkbook;
    Worksheet : TsWorksheet;
    j, nRecordteller        : Integer;
  begin
    screen.Cursor:=crHourglass;

    if Adbgrid.DataSource.DataSet.IsEmpty = True then
      begin
        MessageDlg('De DbGrid is leeg. Export is niet mogelijk.',
mtWarning,[mbOk],0);
      end
    else
      begin  //Export starten
        nRecordteller :=  Adbgrid.DataSource.DataSet.RecordCount;
          try
            workbook := TsWorkbook.Create;
            try
              worksheet := workbook.AddWorksheet('Sheet1');
              worksheet.WriteFontStyle(0, 1, [fssBold]);
              workbook.Options := [boVirtualMode, boBufStream];
              workbook.VirtualColCount :=
Adbgrid.DataSource.DataSet.FieldCount;
              workbook.VirtualRowCount := nRecordteller +1;

              Frm_Main.ProgressBar1.Min:=0;
              Frm_Main.ProgressBar1.Max:=nRecordteller;
              Frm_Main.ProgressBar1.Position:=0;
              Frm_Main.ProgressBar1.Visible:=True;

              if Frm_Main.Progressbar1.Max <
Frm_Main.Progressbar1.Width then 
                step_size := 1
              else
                step_size := Frm_Main.ProgressBar1.Max div
Frm_Main.Progressbar1.Width; 
              counter := 0;

              workbook.OnWriteCellData := @WriteCellDataHandler;
              try
                Adbgrid.DataSource.DataSet.DisableControls;
                //workbook.WriteToFile(Frm_Main.SaveDialog_FileName +
'.xlsx' , sfOOXML, true);
                workbook.WriteToFile(Frm_Main.SaveDialog_FileName,
sfOOXML, true);
                Frm_Main.StatusbarText_0 := 'Aantal geëxporteerde
records: ' + IntToStr(nRecordteller);
              finally
                Adbgrid.DataSource.DataSet.First;  //back to the first
record in the dataset
                Adbgrid.DataSource.DataSet.EnableControls;
              end;
            finally
              workbook.Free;
            end;
          finally
            Frm_Main.ProgressBar1.Visible:=False;

            screen.Cursor:=crDefault;
          end;
      end;
end;  


When i want to write to CSV i thougt i could try the same. But next
procedure only makes an empty file.
Code: [Select]procedure TExport_To_File.Export_2_CSV(Adbgrid:
TDBGrid);
var
  workbook  : TsWorkbook;
  Worksheet : TsWorksheet;
  j, nRecordteller        : Integer;
begin
  screen.Cursor:=crHourglass;

  if Adbgrid.DataSource.DataSet.IsEmpty = True then
    begin
      MessageDlg('De DbGrid is leeg. Export is niet mogelijk.',
mtWarning,[mbOk],0);
    end
  else
    begin  //Export starten
      nRecordteller :=  Adbgrid.DataSource.DataSet.RecordCount;
        try
          workbook := TsWorkbook.Create;
          try
            worksheet := workbook.AddWorksheet('Sheet1');
            workbook.Options := [boVirtualMode, boBufStream];
            workbook.VirtualColCount :=
Adbgrid.DataSource.DataSet.FieldCount;
            workbook.VirtualRowCount := nRecordteller +1;

            Frm_Main.ProgressBar1.Min:=0;
            Frm_Main.ProgressBar1.Max:=nRecordteller;
            Frm_Main.ProgressBar1.Position:=0;
            Frm_Main.ProgressBar1.Visible:=True;

            if Frm_Main.Progressbar1.Max < Frm_Main.Progressbar1.Width
then
              step_size := 1
            else
              step_size := Frm_Main.ProgressBar1.Max div
Frm_Main.Progressbar1.Width;
            counter := 0;

            workbook.OnWriteCellData := @WriteCellDataHandler;
            try
              Adbgrid.DataSource.DataSet.DisableControls;
              workbook.WriteToFile(Frm_Main.SaveDialog_FileName +
STR_COMMA_SEPARATED_EXTENSION, sfCSV, true);   //<---???
              Frm_Main.StatusbarText_0 := 'Aantal geëxporteerde
records: ' + IntToStr(nRecordteller);
            finally
              Adbgrid.DataSource.DataSet.First;
              Adbgrid.DataSource.DataSet.EnableControls;
            end;
          finally
            workbook.Free;
          end;
        finally
          Frm_Main.ProgressBar1.Visible:=False;

          screen.Cursor:=crDefault;
        end;
    end;

end;

----------------------------------------------------------------------
wp · 2015-04-10 20:55

OK, I see...

VirtualMode is not implemented for CSV because I did not think that
somebody would want to write huge csv files which are not compressed
(unlike xlsx or ods). Huge files - that's what VirtualMode is for...

Did you try with "regular" mode, like in http://wiki.lazarus.freepasca
l.org/FPSpreadsheet#Converting_a_database_to_a_spreadsheet? Or you
could also try the export component
(http://wiki.lazarus.freepascal.org/FPSpreadsheet#Dataset_export)
which hides many details of fpspreadsheet.

BTW, the memory consumption of spreadsheets cells is much smaller now
(in the trunk version), therefore, virtualMode is not necessary any
more in many cases.

----------------------------------------------------------------------
Hansvb · 2015-04-11 08:42

I will try you're options.

yesterday i exported 750000 rows to xlsx with the virtual mode. I
worked great.