Leaderboard

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.