{Conversion of the data set to an Excel format When you do a project, in many cases, the customer needs to save the data to us in the database, such as financial needs of this year's financial situation, general manager needs An electronic report for this year's sales situation. Our program can display these data with DBGRID, or print out with QuickReport, but these data cannot be copied by customers, there is no independent electronic form, so they cannot be used independently. To put the data in the database, in Delphi is the data set, convert to an independent electronic form, we can have several options: Use ClientDataSet to save the data set as a CDS file or XML file; The two file formats of the former need special programs to view, although XML is a universal data exchange format, but general customers who need to directly use data are not applicable. So we need to convert the dataset to the customer's file format that can easily use. The Excel in Microsoft's Office suite is more familiar, so we choose to convert the data set to an Excel format, and then let customers browse and process the data using Excel's powerful features. For example, customers use this data to completely customize their own reports, such as joining rich layout formats and customized titles, add data column view, and so on. At the same time, customers can copy them in their own office such as Word. Ok, let's start design. First determine the target requirements: input: TDATASET or the TTABLE, TQuery, TclientDataSet, TadodataSet ...) Enter: EXCEL format file (* .xls) feature: You can control whether Excel is visible, so that users can see it immediately The data in Excel can control which fields in the TDataSet do not output into the excel to include the key to the field title, how to generate an Excel format file and how to write data? Excel can be used as an OLE server to output some properties, methods, and events to the outside. Delphi can utilize these features to implement integration with Excel. Microsoft's Excel object model includes 128 different objects, from rectangular, text boxes, etc. Simple objects to perspective tables, charts, and more complex objects. Below we briefly introduce one of these most important and use the most used objects. These can find a complete document in Excel's VBA 1. Application object Application object is in the top floor of the Excel object hierarchy, indicating the operating environment of Excel. 2. Workbook object The Workbook object is directly under the next layer of the Application object, indicating an Excel works thin file. 3. Worksheet object Worksheet objects are included in the Workbook object, indicating an Excel worksheet. 4. The Range object Range object is included in the Worksheet object, indicating one or more cells in the Excel worksheet. The data in a cell can also be referenced in Delphi in Delphi in Delphi, can be used in Delphi, using the Olevariant variable. In order to control which fields can be output to Excel, we use a property tag that uses the field, if it is less than or equal to a certain value we specified, then output to Excel, otherwise it will not be output. If your DataSet does not add a permanent field object, then all the TAG values of all fields are 0.
If you add a permanent field object, you can control the Tag value of each field and the displaylabel property of the output title. Specific function design: 1, DataSetToExcelsheet: Convert the data set to the Sheet object, no user interface. 2, DataSetToExcel: Call DatasettoExcelsheet, convert the dataset to the Excel file, with user interface, such as the bomb dialog, error message, show Excel et al. The program is compiled under Delphi4, 5 and has been used in multiple items. Also integrated in a small component TDBNAVIGATEBUTTON written in the author: DatasettoExcel (Table1, 0, False, 'Hello1.xls'); DataSetToExcel (Table1, 2, True);} code:
{------------------------------------- ---------------------------------------------- unit: ExcelTools author: Bear function: save the data set, such as TTable, TQuery, TClientDataSet as the Excel file that contains the title, can only be a part of this field is derived by setting the dataSet either export a value greater than a certain value Tag field to deal with the principle of : Calling Microsoft Excel OLE object call mode: Function DatasetToExcel (DataSet: tdataset; FieldTagmax: Integer; Visible: Boolean; Excelfilename: String = '): boolean; ------------------------------------------------------------------------------------------------------ -------------------------------------------------- --------------------------------} Unit ExcelTools; Interface Uses Classes, Comctrls, Stdctrls, Windows, Dialogs, Controls, Sysutils, DB, Forms, dbclient, Comobj; // Import Data Sets into the core function of Excelsheet Function DatasetToExcelsheet (DataSet: TDataSet; FieldTagmax: Integer; // Field TAG value is not exported to Excel Sheet: Olevariant : Boolean; // The function used to use the DataSetToExcelsheet inside, add the UI interface and error handling Function DataSetToExcel (DataSet: TDataSet; // To convert the data set fieldtagmax: integer; // field TAG value is greater than this value, no export to Excel visible: Boolean; // whether to let Excel do the conversion work is visible ExcelFileName: String = '' // Excel file name, * xls):. Boolean; implementation function DataSetToExcelSheet (DataSet: TDataSet; FieldTagMax: Integer; Sheet: OleVariant : Boolean; Var Row, Col, FieldIndex: Integer; BK: TBOOKMARK; Begin Result: = false; {If the data set is not open, then exit} if not dataset.active kilns; {note down the data set.
Frozen display control display} BK: = Dataset.getBookmark; DataSet.disableControls; {Conversion: By loop, first conversion the title, then conversion table content} sheet.activate; try // column headline Row: = 1; col: = 1 For FieldIndex: = 0 to DataSet.fieldcount-1 Do Begin if DataSet.fields [FieldIndex] .tag <= fieldtagmax the beginning: = dimels (row, col): = dataset.fields [FieldIndex] .displaylabel; inc (Colog ); End; end; // table content dataset.first; while not dataset.eof do begin row: = row 1; col: = 1; for FieldIndex: = 0 to dataset.fieldcount-1 do begin if DataSet.fields [FieldIndex] .tag <= FIELDTAGMAX THEN BEGIN Sheet.cells (Row, Col): = Dataset.fields [FieldIndex] .sstring; inc; {End; End; Finally, return to the original location of the data set, restore the display control synchronization display} Finally Dataset.gotobookmark (BK); Dataset.enableControls; End; End; Function DataSettoExcel (DataSet: TDataSet; Fi eldTagMax: Integer; Visible: Boolean; ExcelFileName: String = ''): Boolean; var ExcelObj, WorkBook, Sheet: OleVariant; OldCursor: TCursor; SaveDialog: TSaveDialog; begin Result: = False; {If the data set has not yet opened, Exit} if not dataset.active kilns; {Save the current mouse cursor, then turn the mouse cursor to the wait cursor, indicating the following operations may require a point time} OldCursor: = Screen.Cursor; Screen.cursor: = CRHOURGLASS; {Prepare the desired Excel object if it fails, pop-up} TRY Excelobj: = CreateoleObject ('Excel.Sheet'); Excelobj.Application.visible: = Visible;
