Current location - Health Preservation Learning Network - Healthy weight loss - The source code of delphi+excel got a high score.
The source code of delphi+excel got a high score.
/1/25944.htmlWord and Excel are the most commonly used in daily office, and Excel is particularly convenient when dealing with data reports (operation formulas, print formats, etc.). If we import data into Excel, we can quickly edit our useful data (such as leaders' reports, etc.). ) It depends on our skillful operation of Excel. By controlling Excel, we can automatically compile the format and format we want. /(A) the use of dynamic creative methods

First, use ComObj to create an Excel object:

var ExcelApp:Variant;

Excel app:= create ole object(' Excel。 Application');

1) displays the current window:

ExcelApp。 Visible: = true;

2) change the Excel title bar:

ExcelApp。 Title: =' Application calls Microsoft Excel ';;

3) Add a new workbook:

ExcelApp。 WorkBooks . Add

4) Open an existing workbook:

ExcelApp。 WorkBooks . Open(' C:\ Excel \ demo . xls ')。

5) Set the second worksheet as active sheet:

ExcelApp。 Worksheet. Value: =' first row, fourth column';

7) Take the first column as an example to set the width of the specified column (unit: number of characters):

ExcelApp。 active sheet . columns . clear contents;

13) Set the font property of the first line:

Excelapp。 Activesheet.rows.value: =' first row, fourth column';

9) Take the first column as an example to set the width of the specified column (unit: number of characters):

ExcelApplication 1。 active sheet . columns . clear contents;

15) Set the font property of the first line:

Excel application1.activesheet.rows [1]. Font.name: =' official script';

ExcelApplication 1。 active sheet . Rows[ 1]. font . color:= cl blue;

ExcelApplication 1。 active sheet . Rows[ 1]. font . bold:= True;

ExcelApplication 1。 active sheet . Rows[ 1]. font . underline:= True;

16) for page setup:

A. Title:

Excel application1.activesheet.pagesetup.centerheader: =' report demonstration';

B. Footer:

Excel application1.activesheet.pagesetup.centerfooter: =' The first &; Page p';

C. 2 cm from header to top:

ExcelApplication 1。 active sheet . page setup . header margin:= 2/0.035;

D. The distance from the footer to the bottom is 3cm:

ExcelApplication 1。 active sheet . page setup . header margin:= 3/0.035;

E. the upper margin is 2 cm:

ExcelApplication 1。 active sheet . page setup . top margin:= 2/0.035;

F the distance between the bottom edges is 2cm:

ExcelApplication 1。 active sheet . page setup . bottom margin:= 2/0.035;

G. the distance on the left side is 2cm:

ExcelApplication 1。 active sheet . page setup . left margin:= 2/0.035;

H. 2 cm from the right:

ExcelApplication 1。 active sheet . page setup . right margin:= 2/0.035;

I. the page is horizontally centered:

ExcelApplication 1。 active sheet . page setup . center horizontally:= 2/0.035;

J. vertically centered page:

ExcelApplication 1。 active sheet . page setup . center vertically:= 2/0.035;

K. print cell network lines:

ExcelApplication 1。 active sheet . page setup . print gridlines:= True;

17) copy operation:

A. copy the entire worksheet:

ExcelApplication 1。 active sheet . used . range . copy;

B. copy the designated area:

ExcelApplication 1。 active sheet . Range[' a 1:E2 ']。 Copy;

C. paste from A 1 position:

excel application 1 . active sheet . range .[' a 1 ']。 PasteSpecial

D. Paste from the end of the file:

ExcelApplication 1。 active sheet . range . paste special;

18) Insert a row or column:

a.ExcelApplication 1。 ActiveSheet.Rows[2]。 Insert;

b.ExcelApplication 1。 ActiveSheet.Columns[ 1]。 Insert;

19) Delete a row or column:

a.ExcelApplication 1。 ActiveSheet.Rows[2]。 Delete;

b.ExcelApplication 1。 ActiveSheet.Columns[ 1]。 Delete;

20) print preview worksheet:

ExcelApplication 1。 ActiveSheet . PrintPreview

2 1) Print out the worksheet:

ExcelApplication 1。 ActiveSheet . PrintOut

22) Save the worksheet:

If it is not ExcelApplication 1. ActiveWorkBook. Then save it.

ExcelApplication 1。 ActiveSheet . PrintPreview

23) Save the worksheet as:

ExcelApplication 1。 Save as ('c: \ Excel \ demo1.xls');

24) Give up saving:

ExcelApplication 1。 active workbook . Saved:= True;

25) Close the workbook:

ExcelApplication 1。 Workbook. Close;

26) exit Excel:

ExcelApplication 1。 Quit;

ExcelApplication 1。 Disconnect;

(3) Control Exce2D drawing with Delphi.

Put ExcelApplication, ExcelWorkbook and ExcelWorksheet into the form respectively.

var asheet 1,achart,range:variant;

1) is selected as the first worksheet in the first workbook.

ashet 1:= excelapplication 1。 Workbook [1]. Worksheet [1];

2) Add a 2D chart

achart:= asheet 1 . chart objects . add( 100, 100,200,200);

3) Choose the form of two-dimensional graphics.

achart . chart . charttype:= 4;

4) Assign values to two-dimensional graphics.

series:= achart . chart . series collection;

Range: =sheet 1! r2c 3:r3c 9;

series.add(range,true);

5) Add the title of the 2D drawing.

Ahart. chart . HasTitle:= True;

Ahart. Chart.charttitle.characters.text: =' Excluding 2D charts'

6) Change the font size of 2D drawing titles.

Ahart. chart . chart title . font . size:= 6;

7) Subscript two-dimensional graph.

Ahart. Charts. Axis (xlCategory, xlPrimary). HasTitle:= True;

Ahart. Charts. Axis (XL category, XL main). Axis title. Characters.text: =' subscript description';

8) Add a left label to the 2D drawing.

Ahart. Axis of the chart (xlValue, xlPrimary). HasTitle:= True;

Ahart. Charts. Axis (xlvalue, xlprimary). Axis title. Characters.text: =' Description of the left label';

9) Add a right label to the 2D drawing.

Ahart. Axis of the chart (xlValue, xlSecondary). HasTitle:= True;

Ahart. Charts. Axis (xlvalue, xlsecondary). Axis title. Characters.text: =' Description of the right tag';

10) to change the size of the display area of two-dimensional graphics.

Ahart. chart . plot area . left:= 5;

Ahart. chart . plot area . width:= 223;

Ahart. chart . plot area . height:= 108;

1 1) marks the coordinate axis of two-dimensional graphics.

achart . chart . series collection[ 1]。 Name: =' Axis Description';

(3) Database

1. stored procedure

First execute the following stored procedure.

EXEC sp_addlinkedserver

External resources',

Jet 4.0 ',

Microsoft. Jet.OLEDB.4.0 ',

' c:\ documents and settings \ administrator \ desktop \ xinxi 3 . xls ',

Empty,

' Excel 5.0 '

EXEC sp _ addlinkedsrvlogin excel source,FALSE,NULL,NULL

Then execute the following stored procedure.

Execsp _ tables _ exexexcelsource//shows several tables.

At this time, the names of several tables will be returned (the number is consistent with the number of tables in excel).

Then you use the following sentence:

Select * from excel source ... sheet1USD.

You can find the data in excel, and then you can pour it in.

Remember to execute the following two stored procedures after import:

-EXEC sp _ droplinkedsrvlogin excel source,NULL

- EXEC sp_dropserver ExcelSource

All the above operations can be tested in the query analyzer first, and then you can write stored procedures to implement it.