Code snippet for reading and writing excel by delphi

Unit Unit1;

Interface

Uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, {if delphi6.0 add...} Variants {d6 };

Type
TForm1 = class (TForm)
Button1: TButton;
CheckBox1: TCheckBox;
Button2: TButton;
OpenDialog1: TOpenDialog;
Button3: TButton;
Button4: TButton;
Button5: TButton;
Procedure Button1Click (Sender: TObject );
Procedure Button2Click (Sender: TObject );
Procedure Button3Click (Sender: TObject );
Procedure Button4Click (Sender: TObject );
Procedure Button5Click (Sender: TObject );
Private
V: Variant;
Sheet: Variant;
{Private declarations}
Public
{Public declarations}
End;

Var
Form1: TForm1;

Implementation

{$ R *. DFM}

Uses Comobj;

// Open an EXCEL file to create a file
Procedure TForm1.Button1Click (Sender: TObject );
Begin
Try
V: = CreateOleObject ('excel. application ');
V. Visible: = CheckBox1.Checked; // whether to display
V. Workbooks. Add; // create an EXCEL file
V. Workbooks [1]. WorkSheets [1]. Name: = 'delphi demo ';
// Sheet: = v. Workbooks [1]. WorkSheets ['delphi demo']; // equivalent of the following statement
Sheet: = v. Workbooks [1]. WorkSheets [1];
Sheet. Cells [1, 1]: = 'delphi box ';
Sheet. Cells [2, 1]: = 'HTTP: // www.delphibox.com ';
Except
Showmessage ('excel initialization failed, Excel may not be installed, or other errors; please try again. ');
V. DisplayAlerts: = false; // whether the disk is saved
V. Quit; // exit if an error occurs.
Exit;
End;
Application. Restore;
Application. BringToFront;
End;

// Open an existing EXCEL document
Procedure TForm1.Button2Click (Sender: TObject );
Begin
If OpenDialog1.Execute then
Begin
Try
V: = CreateOleObject ('excel. application ');
V. Visible: = CheckBox1.Checked;
V. Workbooks. Open (OpenDialog1.FileName );
// Sheet: = v. Workbooks [1]. WorkSheets [1];
Except
Showmessage ('excel initialization failed, Excel may not be installed, or other errors; please try again. ');
V. DisplayAlerts: = false;
V. Quit;
Exit;
End;
Application. Restore;
Application. BringToFront;
End;
End;

// Close the EXCEL file and exit
Procedure TForm1.Button3Click (Sender: TObject );
Begin
Try
If not varIsEmpty (v) then
Begin
// If you need to determine whether to save the disk before closing, add:
// V. DiaplayAlert: = true; // confirm the disk
// V. DiaplayAlert: = false; // do not save the disk and exit directly
V. WorkBooks [1]. Close (True, 'c:/untitled.xls '); // exit with the file name
V. quit;
End;
Finally
Close;
End;
End;

// Set the EXCEL printing page
Procedure TForm1.Button4Click (Sender: TObject );
Begin
If OpenDialog1.Execute then
Begin
Try
V: = CreateOleObject ('excel. application ');
V. Visible: = CheckBox1.Checked;
V. Workbooks. Open (OpenDialog1.FileName );
Sheet: = v. Workbooks [1]. WorkSheets [1];
Sheet. PageSetup. PrintTitleRows: = '$1: $ 3'; // Header
Sheet. PageSetup. PrintTitleColumns: = '';
Sheet. PageSetup. LeftFooter: = 'note: footer '+ 'total & N page' +' -- page & P '; // footer
Sheet. PageSetup. LeftMargin: = 30; // sets the margin.
Sheet. PageSetup. RightMargin: = 30;
Sheet. PageSetup. TopMargin: = 30;
Sheet. PageSetup. BottomMargin: = 50;
// Sheet. PageSetup. PrintQuality: = 400; // resolution (determined based on the printer)
Sheet. pagesetup. centerhorizontally: = true; // whether to center horizontally
Sheet. pagesetup. centervertically: = true; // whether to center vertically
Sheet. pagesetup. Orientation: = 2; // print horizontally
Sheet. pagesetup. Draft: = false; // non-Draft Mode
// Sheet. pagesetup. firstpagenumber: = xlautomatic;
Sheet. pagesetup. blackandwhite: = true; // blacklist and whitelist
Sheet. pagesetup. Zoom: = 100; // zoom
Sheet. printpreview; // print preview
Except
Showmessage ('excel initialization failed, Excel may not be installed, or other errors; please try again. ');
V. displayalerts: = false;
V. Quit;
Exit;
End;
End;
End;

// Set the Excel document format
Procedure tform1.button5click (Sender: tobject );
VaR
Range: variant;
Begin
If opendialog1.execute then
Begin
Try
V: = createoleobject ('excel. application ');
V. Visible: = checkbox1.checked;
V. workbooks. Open (opendialog1.filename );
Range: = V. workbooks [1]. worksheets [1]. Range ['a2: g2']; // The range ranges from A2 to M2.
Range. merge; // merge cells.
Range. Rows. rowheight: = 50; // you can specify the Row Height.
Range. Borders. linestyle: = 1; // Add a border
Range. Columns [2]. columnwidth: = 12; // you can specify the column width.
Range. formular1c1: = 'merge region ';
Range. horizontalalignment: = 3; // xlcenter (horizontal alignment)
Range. verticalalignment: = 2; // xlcenter (vertical alignment)
Range. characters. Font. Name: = 'body'; // font
Range. characters. Font. fontstyle: = 'bold ';
Range. characters. Font. Size: = 15;
Range. characters. Font. outlinefont: = false; // whether there is an underline
Range. characters. Font. colorindex: = 0; // xlautomatic; // color
Except
Showmessage ('excel initialization failed, Excel may not be installed, or other errors; please try again. ');
V. DisplayAlerts: = false;
V. Quit;
Exit;
End;
End;
End;

End.

 

Procedure TF_Payee.BitBtn4Click (Sender: TObject );
Var
ExcelApp, WorkSheets: Variant;
FileNames: String;
I, J, K: integer;
Begin
If not DM. Query2.Active then begin
MessageBox (Handle, 'You have not performed a query operation on the database, and no records can be exported! ', AppName, MB_ OK + MB_ICONWarning );
Exit;
End;
Try
ExcelApp: = CreateOLEObject ('excel. application ');
Except
MessageBox (Handle, 'the Microsoft Office or Excel component has not been installed in your operating system, and cannot be exported! ', AppName, MB_ICONSTOP + MB_ OK );
Exit;
End;
SaveDiaLog1.InitialDir: = ExtractFilePath (ParamStr (0 ));
If SaveDialog1.Execute then begin
FileNames: = SaveDialog1.FileName;
ExcelApp. WorkBooks. Add;
WorkSheets: = ExcelApp. WorkBooks [1]. WorkSheets [1];
DM. Query2.DisableControls;
DM. Query2.First;
J: = 0;
WorkSheets. Cells []: = F_Main.StatusBar1.Panels [1]. Text + 'basic information table of the Receiving Entity ';
While not DM. Query2.Eof do begin
K: = 0;
For I: = 0 to DM. Query2.FieldCount-1 do
If Dm. Query2.Fields [I]. Visible then begin // export only Visible fields
WorkSheets. cells [2, K + 1]: = DM. query2.Fields. Fields [I]. DisplayLabel;
Worksheets. cells [J + 3, k + 1]: = DM. query2.fields [I]. asstring;
INC (k );
End;
INC (j );
DM. query2.next;
End;
Worksheets. saveas (filenames );
Excelapp. Quit;
Excelapp: = unassigned;
DM. query2.enablecontrols;
MessageBox (self. Handle, 'Data is successfully exported to the Excel file! ', Appname, mb_ OK + mb_iconinformation );
End;
End;