Delphi Excel operation, write a function for converting adodataset to excel as an example of later learning

To use this function, use excel2010 first.

 

// Dataset export the excel2010 format
// Filename = Name of the Excel file to be exported, without the path and suffix; titleline1 = first header of Excel after export, titleline2 = Second header of Excel; cellsnames = Name of the field in the Excel table;
// Isopen = whether procedure publish (dset: TADODataSet; filename, titleline1, titleline2: string; cellsnames: tstringlist; isopen: Boolean = false); var excelapp: texcelapplication; excelworkbook: texcelworkbook; excelworksheet: texcelworksheet; range: olevariant; I, recno: integer; tmppath: string; begin try excelapp: = texcelapplication. create (NiL); excelworkbook: = texcelworkbook. create (NiL); excelworksheet: = texcelworksheet. create (NiL); cmdt msgraise ('The Office 2007 or a later version is not installed, please install it and try again '); end; // create an Excel app. connect; excelapp. workbooks. add (null, 0); excelworkbook. connectto (excelapp. workbooks [1]); excelworksheet. connectto (excelworkbook. worksheets [1] As _ worksheet); // create an Excel header range: = excelapp. range [excelworksheet. cells. item [1, 1], excelworksheet. cells. item [2, cellsnames. count]; range. mergecells: = true; range. font. size: = 18; range. font. bold: = true; range. horizontalalignment: = 3; // center horizontally. If xlcenter is used, the "class range" error range is reported. verticalalignment: = 2; // center vertically. If xlcenter is used, the "class range" error range is reported. rowheight: = 40; range. cells []: = titleline1; // the first row of the header range: = excelapp. range [excelworksheet. cells. item [3, 1], excelworksheet. cells. item [3, cellsnames. count]; range. mergecells: = true; // merge range. font. size: = 10; range. font. bold: = true; range. horizontalalignment: = 4; // The same as the preceding error range. verticalalignment: = 2; // The same as the preceding error range. rowheight: = 20; range. cells []: = titleline2; // the second row of the header for I: = 1 to cellsnames. count do begin range. cells [2, I]: = cellsnames. names [I-1]; // must be consistent with the fields sequence of dset. columns [I]. columnwidth: = cellsnames. values [cellsnames. names [I-1]; // The cell width in an Excel file, in characters. end; recno: = dset. recno; dset. disablecontrols; dset. first; while not dset. EOF do begin for I: = 1 to cellsnames. count do range. cells [dset. recno + 2, I]: = vartostr (dset. fields [I-1]. value); dset. next; end; dset. recno: = recno; dset. enablecontrols; tmppath: = sysgetpath (hinstance) + 'xls \ '; // sysgetpath = custom function, get the current path if not directoryexists (tmppath) Then forcedirectories (tmppath); excelworksheet. saveas (tmppath + filename + '.xlsx'); excelworksheet. disconnect; excelworkbook. disconnect; excelapp. quit; // This sentence is important. In many examples, if this sentence is not used, you can still find that the Excel process has not closed the excelapp. disconnect; freeandnil (excelworksheet); freeandnil (excelworkbook); freeandnil (excelapp); If isopen then partition (tmppath + filename + '.xlsx', '','', 0); end;