stiaan Posted June 2, 2014 Share Posted June 2, 2014 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;interfaceUses Windows, System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter, Data.DB;procedure DataSetToXLS( XLSX : TExcelFile; DataSet : TDataSet; WorkSheetCount : Integer = 1; ActiveSheet : Integer = 1 );implementationprocedure 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. 1 Link to comment Share on other sites More sharing options...
billyChou Posted June 3, 2014 Share Posted June 3, 2014 Thanks Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now