说明:
程序中使用OLE来操作EXCEL。需要在uses中写上ComObj.
使用属性来获取行数和列数:
使用代码1提供的代码获取的excel中的行数和列数,不包括开头的空行和空列。
1
TemperMaxRows :
=
ExcelApp.worksheets[
1
].Usedrange.Rows.count;
{
代码1
}
2 TemperMaxcolumns: = ExcelApp.worksheets[ 1 ].Usedrange.columns.count;
2 TemperMaxcolumns: = ExcelApp.worksheets[ 1 ].Usedrange.columns.count;
包含开头空行和空列的行数和列数:
如果想获得最大行数(列数),需要在程序中去判断。
判断方法:
1、先假设TemperMaxRows为最大行,然后去判断每一列的元素是否都为非空,如果有一列全部为空,则列数加1,最终得到实际的最大列数
2、根据计算到的实际最大列数,然后去判断每一行的元素是否都为非空,如果有一行全部为空,则行数加1,最终得到实际的最大行数。
代码示例:
1
unit
Unit1;
2
3 interface
4
5 uses
6 Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
7 Dialogs, Grids, Menus,ComObj;
8
9 type
10 TForm1 = class (TForm)
11 strngrd1: TStringGrid;
12 mm1: TMainMenu;
13 File1: TMenuItem;
14 Open1: TMenuItem;
15 dlgOpen1: TOpenDialog;
16 ExcelFile1: TMenuItem;
17 Open2: TMenuItem;
18 procedure Open2Click(Sender: TObject);
19 procedure FormClose(Sender: TObject; var Action: TCloseAction);
20 private
21 { Private declarations }
22 public
23 { Public declarations }
24 end ;
25
26 var
27 Form1: TForm1;
28 ExcelApp : Variant;
29 FileDirectory: string ;
30
31 implementation
32
33 { $R *.dfm }
34
35 procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
36 begin
37 ExcelApp.WorkBooks.Close;
38 ExcelApp.quit;
39 end ;
40
41
42 procedure TForm1.Open2Click(Sender: TObject);
43 var
44 i,j:Integer;
45 tempermaxRow,maxRow:Integer;
46 tempermaxcolumn,maxcolumn : Integer;
47 flag:Boolean;
48 begin
49 dlgOpen1.Execute;
50 FileDirectory : = dlgOpen1.FileName; { 获取文件的路径名 }
51 Text : = FileDirectory;
52 ExcelApp : = CreateOleObject( ' Excel.Application ' );
53 { ExcelApp.Visible := True; }
54 ExcelApp.workbooks.Open( ' E:\我的文档\Delphi程序\Delphi操作excel\book1.xls ' );
55
56 for I : = 01 to 20 do { 读取excel中的数据 }
57 strngrd1.Cells[ 1 ,i] : = ExcelApp.cells[i, 2 ]; { excel单元格不是从[0,0]开始的 }
58
59 { 获取数据中的使用的行数和列数,如果开头的行(列)内容全为空,则不计算到总数内。 }
60 tempermaxRow : = ExcelApp.worksheets[ 1 ].Usedrange.Rows.count;
61 tempermaxcolumn : = ExcelApp.worksheets[ 1 ].Usedrange.columns.count;
62 ShowMessage(Format( ' 程序中获取的行数:%d,程序中获取的列数%d ' ,[tempermaxRow,tempermaxcolumn]));
63 Maxcolumn : = tempermaxcolumn;
64 maxRow : = tempermaxRow;
65 flag : = false;
66 for i : = 1 to tempermaxcolumn do
67 begin
68 for j : = 1 to maxRow do
69 begin
70 if ExcelApp.cells[j,i].value <> '' then
71 begin
72 flag : = True;
73 Continue;
74 end ;
75 end ;
76 if (flag = False) then
77 Maxcolumn : = Maxcolumn + 1 ;
78 end ;
79
80
81 flag : = false;
82 for i : = 1 to tempermaxRow do
83 begin
84 for j : = 1 to maxcolumn do
85 begin
86 if ExcelApp.cells[i,j].value <> '' then
87 begin
88 flag : = True;
89 Continue;
90 end ;
91 end ;
92 if (flag = False) then
93 maxRow : = maxRow + 1 ;
94 end ;
95
96 { 将空行也计算到总数内 }
97 ShowMessage(Format( ' 实际行数:%d,实际列数%d ' ,[maxRow,Maxcolumn]));
98 end ;
99
100 end .
101
2
3 interface
4
5 uses
6 Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
7 Dialogs, Grids, Menus,ComObj;
8
9 type
10 TForm1 = class (TForm)
11 strngrd1: TStringGrid;
12 mm1: TMainMenu;
13 File1: TMenuItem;
14 Open1: TMenuItem;
15 dlgOpen1: TOpenDialog;
16 ExcelFile1: TMenuItem;
17 Open2: TMenuItem;
18 procedure Open2Click(Sender: TObject);
19 procedure FormClose(Sender: TObject; var Action: TCloseAction);
20 private
21 { Private declarations }
22 public
23 { Public declarations }
24 end ;
25
26 var
27 Form1: TForm1;
28 ExcelApp : Variant;
29 FileDirectory: string ;
30
31 implementation
32
33 { $R *.dfm }
34
35 procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
36 begin
37 ExcelApp.WorkBooks.Close;
38 ExcelApp.quit;
39 end ;
40
41
42 procedure TForm1.Open2Click(Sender: TObject);
43 var
44 i,j:Integer;
45 tempermaxRow,maxRow:Integer;
46 tempermaxcolumn,maxcolumn : Integer;
47 flag:Boolean;
48 begin
49 dlgOpen1.Execute;
50 FileDirectory : = dlgOpen1.FileName; { 获取文件的路径名 }
51 Text : = FileDirectory;
52 ExcelApp : = CreateOleObject( ' Excel.Application ' );
53 { ExcelApp.Visible := True; }
54 ExcelApp.workbooks.Open( ' E:\我的文档\Delphi程序\Delphi操作excel\book1.xls ' );
55
56 for I : = 01 to 20 do { 读取excel中的数据 }
57 strngrd1.Cells[ 1 ,i] : = ExcelApp.cells[i, 2 ]; { excel单元格不是从[0,0]开始的 }
58
59 { 获取数据中的使用的行数和列数,如果开头的行(列)内容全为空,则不计算到总数内。 }
60 tempermaxRow : = ExcelApp.worksheets[ 1 ].Usedrange.Rows.count;
61 tempermaxcolumn : = ExcelApp.worksheets[ 1 ].Usedrange.columns.count;
62 ShowMessage(Format( ' 程序中获取的行数:%d,程序中获取的列数%d ' ,[tempermaxRow,tempermaxcolumn]));
63 Maxcolumn : = tempermaxcolumn;
64 maxRow : = tempermaxRow;
65 flag : = false;
66 for i : = 1 to tempermaxcolumn do
67 begin
68 for j : = 1 to maxRow do
69 begin
70 if ExcelApp.cells[j,i].value <> '' then
71 begin
72 flag : = True;
73 Continue;
74 end ;
75 end ;
76 if (flag = False) then
77 Maxcolumn : = Maxcolumn + 1 ;
78 end ;
79
80
81 flag : = false;
82 for i : = 1 to tempermaxRow do
83 begin
84 for j : = 1 to maxcolumn do
85 begin
86 if ExcelApp.cells[i,j].value <> '' then
87 begin
88 flag : = True;
89 Continue;
90 end ;
91 end ;
92 if (flag = False) then
93 maxRow : = maxRow + 1 ;
94 end ;
95
96 { 将空行也计算到总数内 }
97 ShowMessage(Format( ' 实际行数:%d,实际列数%d ' ,[maxRow,Maxcolumn]));
98 end ;
99
100 end .
101
后续文章: