Jump to content

UniGrid DataSet to XLSX export using TMS Flexcel - an example


Recommended Posts

A simple unit using TMS Flexcel to export to XLSX (Excel 2007/2010). VERY fast and very simple. It formats the DATETIME columns as well for easier readability.

 

 

Example on how to call:

procedure TbbbbReports.btnExportToExcelClick(Sender: TObject);
var
  XLSX : TExcelFile;
  Mem : TMemoryStream;
begin
  UniDBGrid1.DataSource := Nil; //<- Stop DBGrid from updating

  XLSX := TXLSFile.Create;

  DataSetToXLS( XLSX, SDQueryReport ); //<- Your dataset goes here. I use SQLDirect

  Mem := TMemoryStream.Create;
  XLSX.Save( Mem, TFileFormats(4) );
  Mem.Position := 0;
  UniSession.SendStream( Mem, TrimSpecialStr(sReportname) + '.xlsx' ); //<- Push to browser
  Mem.Free;
  XLSX.Free;

  UniDBGrid1.DataSource := DataSource1;
end;

 

The unit source code:

unit DataSetExports;

interface

Uses
  Windows, System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter, Data.DB;

procedure DataSetToXLS( XLSX : TExcelFile; DataSet : TDataSet; WorkSheetCount : Integer = 1; ActiveSheet : Integer = 1 );

implementation

procedure DataSetToXLS( XLSX : TExcelFile; DataSet : TDataSet; WorkSheetCount : Integer = 1; ActiveSheet : Integer = 1 );
var
  ioldrecno, icol, irow : integer;
  fmt: TFlxFormat;
  fmtDateTime : Integer;
begin
  //make sure it is XLSX format
  ioldrecno := DataSet.RecNo;

  XLSX.SupportsXlsx := True;
  XLSX.NewFile(WorkSheetCount, TExcelFileFormat(2) );  //2 = v2010, 1 = v2007, 0 = v2003
  XLSX.ActiveSheet := ActiveSheet;
  fmt := XLSX.GetDefaultFormat;
  fmt.Format := 'yyyy-mm-dd HH:MM:SS AM/PM';
  fmtDateTime := XLSX.AddFormat(fmt);

  //Write the headers
  irow := 1;
  for icol := 0 to DataSet.FieldCount - 1 do
  begin
    XLSX.SetCellValue( irow, icol + 1, DataSet.Fields[icol].DisplayName );
  end;

  inc(irow);

  DataSet.First;
  while Not DataSet.EOF do
  begin
    for icol := 0 to DataSet.FieldCount - 1 do
    begin
      case Dataset.Fields[icol].DataType of
        ftUnknown, ftString,
        ftBoolean, ftFloat, ftCurrency, ftBCD,
        ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo,
        ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar, ftWideString,
        ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob,
        ftVariant, ftInterface, ftIDispatch, ftGuid, ftFMTBcd,
        ftFixedWideChar, ftWideMemo, ftOraInterval,
        ftConnection, ftParams, ftStream, ftTimeStampOffset, ftObject:
          begin
            XLSX.SetCellValue( irow, icol + 1, Dataset.Fields[icol].AsString );
          end;
        ftSmallint, ftInteger, ftWord, ftLargeint, ftLongWord, ftShortint, ftByte, ftSingle:
          begin
            XLSX.SetCellValue( irow, icol + 1, Dataset.Fields[icol].AsInteger );
          end;
        ftDate, ftTime, ftDateTime, ftOraTimeStamp, ftTimeStamp:
          begin
            XLSX.SetCellValue( irow, icol + 1, Dataset.Fields[icol].AsDateTime );
            XLSX.SetCellFormat( irow, icol + 1, fmtDateTime );
          end;
        ftExtended:
          begin
            XLSX.SetCellValue( irow, icol + 1, Dataset.Fields[icol].AsFloat );
          end;
      end;
    end;
    inc(irow);
    DataSet.Next;
  end;
  DataSet.RecNo := ioldrecno;
end;


end.
 

 

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...