Save data in the TDataSet into an Excel file

/ ************************************************** *************************** * Function Name: ExportDataToExcel * Enter parameters: ado // Pointer of TadoQuery * dia // Pointer Of TsaveDialog * Grid // Pointer of TdbGrid * k // y Coordiate of Cells * Str // Title of Excel Table * Output Parameters: None * Return Value: Void * Description: ************ *********************************************************** ***************** / VOID EXPORTDATATOEXCEL (TadoQuery * ADO, TSAVEDIALOG * DIA, TDBGRID * GRID, INT K, CHAR * STR) {Variant EX, Newxls, Cellms; Ansistring SfileName ; Int Row, I, J = 1;

IF (DIA-> Execute ()) {try {ex= creteoleObject ("excel.application"); // Start Excel Ex. Deals false; // Make Excel NEWXLS = EX.OLEPROPERTYGET ("Workbooks")). OLEFUNCTION ("add"); // Add a work thin cellms = newxls.olePropertyget ("ActiveSheet"); // Create workspace}

Catch (...) {ShowMessage ("Unable to start Excel"); Return;}

ADO-> ACTIVE = true; ado-> first ();

Row = 1; // On the first line Show title //ex.olepropertyget ("cells ", 1, 6) .olePropertySet (" Value ",); ex.olepropertyget (" cells ", 1, k) .olePropertySet "Value", str); cellms = ex.exec ("" Range ") << (" a " INTOSTR (ROW) ": a " INTOSTR (ROW)));

For (i = 0; i fieldcount; i ) {// write field names to work-thin second lines Cellms.olePropertyget ("cells", 2, i 1) .olePropertySet ("Value", (WideString) Grid-> Column-> items [i] -> title-> caption.c_str ()); //cellms.olepropertyget ("cells ", 2, i 1) .olepropertyset (" Value ", (WideString ADO-> Fields-> Fields [i] -> FieldName);} While (! ADO-> EOF) // Write records in the database to Excel {J = J 1;

For (i = 0; i fieldcount; i ) {Cellms.olePropertyget ("cells", J 1, i 1) .olepropertyset ("Value", (WideString) ado-> fields-> fields [ I] -> asstring);} ado-> next ();

sfilename = dia-> filename; newxls.oleprocedure ("saveas", sfilename.c_str ()); // Save an Excel file

EX.OLEFUNCTION ("quit"); // Exit Excel EX = Unassigned; newxls = unassigned; cellms = unassigned;}} // **** Get the Excel file name to be saved ***** // function GetsaveDFileName : string; var sd1: tsavedialog; begin sd1: = tsavedialog.create (nil); sd1.filter: = '* .csv | * .csv'; sd1.defaultext: = '* .csv'; Result: = ' If sd1.execute1 begin if fileexists (sd1.filename) The beginiff (Messagedlg ('file' sd1.filename 'already exists, is it to overwrite?', MTConfirmation, [mbyes, mbno], 0) = mryes) The deletefile (sd1.filename) else exit; end; result: = sd1.filename; end;

// **** DBGRID data output to the Excel file ******** // Procedure DBGRIDTOEXCEL (DBGRID: TDBGRID; filename: string); var CSV, DBROW: TSTRINGLIST; I, J: Integer; DS : Tdataset; FieldName: string; begin if filename = '' Then exit; csv: = tstringlist.create; try dbrow: = tstringlist.create; try {Add title} for i: = 0 to dbgrid.columns.count-1 do Begin dbrow.add (dbgrid.columns [i] .title.caption); end; csv.add (dbrow.commatext); {Add Content} DS: = dbgrid.datasource.DataSet; if Ds.active The Begin Ds.disableControls Try J: = DS.RECNO; DS.First; while not ds.eof do beg, dbrow.clear; for i: = 0 to dbgrid.columns.count-1 do begin fieldname: = dbgrid.columns [i] .fieldname WITH DS.FIELDBYNAME (FIELDNAME) Do Begin Case DataType of fTString: dbrow.add (asstring); ftdatetime: dbrow.Add (Format DateTime (DATETIME, AsDateTime)); ftCurrency, ftBCD: DBRow.Add (CurrToStr (AsCurrency)); ftFloat: DBRow.Add (FloatToStr (AsFloat)); ftInteger: DBRow.Add (IntToStr (AsInteger)); end; end; End; csv.add (dbrow.commatext); ds.next; end; ds.recno: = j; finally ds.enablecontrols; end;

Csv.savetofile (filename); Finally DBROW.FREE; End; end;