请问如何将EXCEL中的数据导入到MDB数据库中??或者是用DELPHI怎样看到EXCEL中的数据并对它们进行操作??
最好可以给我一个例程!!!
万分感谢!!
my email:[email protected]
最好可以给我一个例程!!!
万分感谢!!
my email:[email protected]
解决方案 »
- (新手问题)显示像素矩阵
- 为何我增加完一条纪录或者重新刷新页面输入法又自动变为智能ABC了??
- 幫華為做外包,面試有那些需要注意的地方,謝謝,請教
- 用所有的分数和大家来探讨这个问题
- StringGrid问题,速速来拿分!!
- 怎样用将文本文件读到DbGrid中显示?
- 报表字体显示比较乱,怎么解决呀??
- 如何在记录文件中删除一条记录??
- TO Musicwind 你到底是不是程序员????????
- 哪里有MP3和RM播放的组件?(要可以编译进可执行文件脱离DELPHI运行的)
- 呜呜。。救命啊。。请问怎么在报表的预览页面加个鼠标双击事件,或按钮时间呢?
- 贫农.中农.富农都关注一下:关于《windows网络编程》v2 怎么学?
procedure TBaseInputFrm.SpeedButton1Click(Sender: TObject);
var
chexingshu:integer;
Excelx1:string;
Excelx2:string;
xl,Cell1: olevariant;
Sum:integer;
begin
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.Caption := '报表打印';
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets[1].Activate;
ExcelApp.Visible := True;
ExcelApp.ActiveWindow.Zoom := 75;
chexingshu:=23;
excelx1:=excelop(chexingshu)+'1';
excelx2:='a1';
ExcelApp.Range[excelx2, excelx1].Merge(xl);
ExcelApp.Cells[1,1].Value :='商户基本信息报表';
ExcelApp.cells.Item[1, 1].font.size := 20;
ExcelApp.cells.Item[1, 1].RowHeight := 25;
ExcelApp.cells.Item[1 , 1].Font.Bold := True;
ExcelApp.cells.Item[1, 1].HorizontalAlignment := xlCenter;
ExcelApp.cells.Item[1, 1].font.Name := '隶书';
ExcelApp.cells.Item[2, 1].HorizontalAlignment := xlCenter;
ExcelApp.Range['a2', 'a3'].Merge(xl);
ExcelApp.Cells[2,1].Value :='录入日期';
ExcelApp.cells.Item[2 , 1].Font.Bold := True;
ExcelApp.Range['a2', 'a3'].ColumnWidth := 20;
ExcelApp.cells.Item[2, 1].RowHeight := 20;
ExcelApp.Range['b2', 'b3'].Merge(xl);
ExcelApp.Cells[2,2].Value := '商铺号';
ExcelApp.cells.Item[2 , 2].Font.Bold := True;
ExcelApp.Range['c2', 'c3'].Merge(xl);
ExcelApp.cells.Item[2, 2].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,3].Value := '租房面积';
ExcelApp.cells.Item[2 , 3].Font.Bold := True;
ExcelApp.Range['d2', 'd3'].Merge(xl);
ExcelApp.cells.Item[2, 3].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,4].Value := '合同号';
ExcelApp.cells.Item[2 , 4].Font.Bold := True;
ExcelApp.Range['e2', 'e3'].Merge(xl);
ExcelApp.Range['d2', 'd2'].ColumnWidth := 15;
ExcelApp.cells.Item[2, 4].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,5].Value := '公司名称';
ExcelApp.cells.Item[2 , 5].Font.Bold := True;
ExcelApp.Range['f2', 'f3'].Merge(xl);
ExcelApp.cells.Item[2, 5].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,6].Value := '经营项目';
ExcelApp.cells.Item[2 , 6].Font.Bold := True;
ExcelApp.Range['g2', 'g3'].Merge(xl);
ExcelApp.cells.Item[2, 6].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,7].Value := '代理品牌';
ExcelApp.cells.Item[2 , 7].Font.Bold := True;
ExcelApp.Range['h2', 'h3'].Merge(xl);
ExcelApp.cells.Item[2, 7].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,8].Value := '公司规模';
ExcelApp.cells.Item[2 , 8].Font.Bold := True;
ExcelApp.Range['i2', 'i3'].Merge(xl);
ExcelApp.cells.Item[2, 8].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,9].Value := '员工人数';
ExcelApp.cells.Item[2 , 9].Font.Bold := True;
ExcelApp.Range['j2', 'j3'].Merge(xl);
ExcelApp.cells.Item[2, 9].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,10].Value := '公司性质';
ExcelApp.cells.Item[2 , 10].Font.Bold := True;
ExcelApp.Range['k2', 'k3'].Merge(xl);
ExcelApp.cells.Item[2, 10].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,11].Value := '法人代表';
ExcelApp.cells.Item[2 , 11].Font.Bold := True;
ExcelApp.Range['l2', 'l3'].Merge(xl);
ExcelApp.cells.Item[2, 11].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,12].Value := '常务负责人';
ExcelApp.cells.Item[2 , 12].Font.Bold := True;
ExcelApp.Range['m2', 'm3'].Merge(xl);
ExcelApp.cells.Item[2, 12].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,13].Value := '座机';
ExcelApp.cells.Item[2 , 13].Font.Bold := True;
ExcelApp.Range['n2', 'n3'].Merge(xl);
ExcelApp.cells.Item[2, 13].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,14].Value := '手机';
ExcelApp.cells.Item[2 , 14].Font.Bold := True;
ExcelApp.Range['o2', 'o3'].Merge(xl);
ExcelApp.cells.Item[2, 14].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,15].Value := '开始日期';
ExcelApp.cells.Item[2 , 15].Font.Bold := True;
ExcelApp.Range['p2', 'p3'].Merge(xl);
ExcelApp.cells.Item[2, 15].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,16].Value := '结束日期';
ExcelApp.cells.Item[2 , 16].Font.Bold := True;
ExcelApp.Range['q2', 'q3'].Merge(xl);
ExcelApp.cells.Item[2, 16].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,17].Value := '租期';
ExcelApp.cells.Item[2 , 17].Font.Bold := True;
ExcelApp.Range['r2', 'r3'].Merge(xl);
ExcelApp.cells.Item[2, 17].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,18].Value := '租金';
ExcelApp.cells.Item[2 , 18].Font.Bold := True;
ExcelApp.Range['s2', 's3'].Merge(xl);
ExcelApp.cells.Item[2, 18].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,19].Value := '合计';
ExcelApp.cells.Item[2 , 19].Font.Bold := True;
ExcelApp.Range['t2', 't3'].Merge(xl);
ExcelApp.cells.Item[2, 19].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,20].Value := '租金情况';
ExcelApp.cells.Item[2 , 20].Font.Bold := True;
ExcelApp.Range['u2', 'u3'].Merge(xl);
ExcelApp.cells.Item[2, 20].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,21].Value := '营业执照编号';
ExcelApp.cells.Item[2 , 21].Font.Bold := True;
ExcelApp.Range['v2', 'v3'].Merge(xl);
ExcelApp.cells.Item[2, 21].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,22].Value := '国税证编号';
ExcelApp.cells.Item[2 , 22].Font.Bold := True;
ExcelApp.Range['w2', 'w3'].Merge(xl);
ExcelApp.cells.Item[2, 22].HorizontalAlignment := xlCenter;
ExcelApp.Cells[2,23].Value := '地税证编号';
ExcelApp.cells.Item[2 , 23].Font.Bold := True;
ExcelApp.Range['x2', 'x3'].Merge(xl);
ExcelApp.cells.Item[2, 23].HorizontalAlignment := xlCenter;
Sum:=4;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from BaseMsg');
ADOQuery1.Open;
begin
ExcelApp.Cells[sum,1].Value :=ADOQuery1.FieldByName('InputDate').AsString;
ExcelApp.cells.Item[sum,1].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,2].Value :=ADOQuery1.FieldByName('BusID').AsString;
ExcelApp.cells.Item[sum,2].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,3].Value :=ADOQuery1.FieldByName('HouseEar').AsString+'平方米';
ExcelApp.cells.Item[sum,3].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,4].Value :=ADOQuery1.FieldByName('HTID').AsString;
ExcelApp.cells.Item[sum,4].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,5].Value :=ADOQuery1.FieldByName('Gsmc').AsString;
ExcelApp.cells.Item[sum,5].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,6].Value :=ADOQuery1.FieldByName('Jyxm').AsString;
ExcelApp.cells.Item[sum,6].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,7].Value :=ADOQuery1.FieldByName('DLPP').AsString;
ExcelApp.cells.Item[sum,7].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,8].Value :=ADOQuery1.FieldByName('GSGM').AsString;
ExcelApp.cells.Item[sum,8].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,9].Value :=ADOQuery1.FieldByName('RGRS').AsString;
ExcelApp.cells.Item[sum,9].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,10].Value :=ADOQuery1.FieldByName('GSXZ').AsString;
ExcelApp.cells.Item[sum,10].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,11].Value :=ADOQuery1.FieldByName('FRDB').AsString;
ExcelApp.cells.Item[sum,11].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,12].Value :=ADOQuery1.FieldByName('CWFZR').AsString;
ExcelApp.cells.Item[sum,12].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,13].Value :=ADOQuery1.FieldByName('Telphone').AsString;
ExcelApp.cells.Item[sum,13].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,14].Value :=ADOQuery1.FieldByName('TelphoneID').AsString;
ExcelApp.cells.Item[sum,14].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,15].Value :=ADOQuery1.FieldByName('StartDate').AsString;
ExcelApp.cells.Item[sum,15].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,16].Value :=ADOQuery1.FieldByName('EndDate').AsString;
ExcelApp.cells.Item[sum,16].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,17].Value :=ADOQuery1.FieldByName('ZQ').AsString;
ExcelApp.cells.Item[sum,17].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,18].Value :=ADOQuery1.FieldByName('ZJ').AsString+'元/月/平方米';
ExcelApp.cells.Item[sum,18].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,19].Value :=ADOQuery1.FieldByName('MoneySum').AsString+'元';
ExcelApp.cells.Item[sum,19].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,20].Value :=ADOQuery1.FieldByName('ZJQK').AsString;
ExcelApp.cells.Item[sum,20].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,21].Value :=ADOQuery1.FieldByName('YYID').AsString;
ExcelApp.cells.Item[sum,21].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,22].Value :=ADOQuery1.FieldByName('GSID').AsString;
ExcelApp.cells.Item[sum,22].HorizontalAlignment := xlCenter;
ExcelApp.Cells[sum,23].Value :=ADOQuery1.FieldByName('DSID').AsString;
ExcelApp.cells.Item[sum,23].HorizontalAlignment := xlCenter;
sum:=sum+1;
ADOQuery1.next;
end;end;
connstr:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';
connstr:=constr+excelfilename+';Extended Properties=Excel 8.0;Persist Security Info=False';
ADODataSet1.connectionstring:=conNstr; 连接字符串ADODataset1.Commandtype:=cmdtableDirect;
ADODataset1.commandtext:=sheet1$; 注意这个地方!
ADODataset1.Active:=TRUE;
谢谢各位的解答,等我回家就结贴!!
你安SQL Server了吗?那里就带这个工具呀,打开以后一目又了然的,你一看就会,very easy!
{Private declarations}
v :Variant;
Sheet:Variant;
在单击事件中加入如下代码:
procedure TForm1.Button1Click(Sender:TObject);
begin//打开Excel新建文件
try
v := CreateOleObject('Excel.Application');
v.Visible:=CheckBox1.Checked;//是否显示Excel
v.WorkBooks.Add;//新建Excel文件
v.WorkBooks[1].WorkSheets[1].Name:='操作Excel';
//第一页标题
v.WorkBooks[1].WorkSheets[2].Name:='编程乐园';
v.WorkBooks[1].WorkSheets[3].Name:='都来看呀';//
v.WorkBooks[1].WorkSheets['Delphi演示'];'等效下面的语句
Sheet:=v.WorkBooks[1].WorkBooks[1];
Sheet.Cells[1,1]:='好看';//单元格内容
Sheet.Cells[1,2]:='确实';
Sheet.Cells[1,2]:='我喜欢';
except//错误处理
ShowMessage('初始化Excel失败,可能没有装Excel或者其他错误,请重起再试'); v.DisplayAlerts:=false;//是否提示存盘
v.Quit;//退出Excel
exit;
end;
Application.Restore;
Application.BringToFront;end;
其中,ComObj中包含了用来检索OLE的对象和向它传发命令的例程,第一行代码启动Excel,但这时是不可见的,只有在后台运行,所以第二行代码根据CheckBox1.Checked来确定是否显示Excel运行界面。如果需要打开本身存在的Excel文件,则用v.WorkBooks.Open(OpenDialog1.FileName);代替v.WorkBooks.Add;就可以了。 Sheet对象是很多页面的集合,其他的WorkBooks是工作簿的集合,WorkBooks对象是工作表的集合,Charts对象是图表的集合。
//Excel文档格式设置
procedrue TForm1.Button5Click(Sender:TObject);
var
Range:Variant;
begin
if OpenDialog1.Execute then begin
try
v:=CreateOleObject('Excel.Application');
v.Visible:=CheckBox1.Checked;
v.WorkBooks.OpenDialog1.FileName);
Range :=v.WorkBooks[1].WorkBooks[1].Range['A2:G2'];//单元格从A2到G2 Range.Merge;//合并单元格
Range.Rows.RowHeight :=50;//设置行高;
Range.Borders.LineStyle:=1;//加边框
Range.Columns[2].ColumnWidth:=12;//设置列宽
Range.FormulaR1C1:='合并区';
Range.HorizontalAlignment:=3;//xlCenter(水平对齐方式) Range.VerticalAlignment:=2;//xlCenter(垂直对齐方式) Range.Characters.Font.Name:='宋体';//字体 Range.Characters.Font.Size:=15;
Range.Characters.Font.OutlineFont:=False;//是否有下划线 Range.Characters.Font.ColorIndex:=0;//xlAutomatic;颜色
except//错误处理
ShowMessage('初始化Excel失败,可能没有装Excel或者其他错误,请重起再试'); v.DisplayAlerts:=false;//是否提示存盘
v.Quit;//退出Excel
end;
end; //EXCEL打印页面设置及打印预览
procedure TForm1.Button4Click(Sender:TObject);
begin
if OpenDialog1.Execute then
begin
try
v:=CreateOleObject('Excel.Application');
v.Visible:=CheckBox1.Checked;
v.WorkBooks.OpenDialog1.FileName);
Sheet:=v.WorkBooks[1].WorkBooks[1]; Sheet.PageSetup.PrintTitleRows:='$1:$3';//页眉 Sheet.PageSetup.PrintTitleColumns:='';
Sheet.PageSetup.LeftFooter:='注:页脚'+'总共&N页'+'——第&P页';//页脚 Sheet.PageSetup.LeftMargin:=30;//设置边距 Sheet.PageSetup.RightMargin:=30;
Sheet.PageSetup.TopMargin:=30;
Sheet.PageSetup.BottomMargin:=50;//
Sheet.PageSetup.PrintQuality:= 400;//分辨率(根据打印机确定) Sheet.PageSetup.CenterHorizontally:=true;//是否水平居中 Sheet.PageSetup.CenterVertical:=true;//是否垂直居中 Sheet.PageSetup.Orientation:=2;//横向打印
Sheet.PageSetup.Draft:=false;//非草稿模式// Sheet.PageSetup.FirstPageNumber:=xlAutomatic; Sheet.PageSetup.BlackAndWhite:=true;//黑白稿
Sheet.PageSetup..Zoom:=100;//缩放
Sheet.PrintPreView;//打印预览
except//错误处理
ShowMessage('初始化Excel失败,可能没有装Excel或者其他错误,请重起再试'); v.DisplayAlerts:=false;//是否提示存盘
v.Quit;//退出Excel
end;
end;
end; 最后就是要关闭Excel了。
在创建了Excel Application对象后,调用它的Quit方法完成关闭事件。//关闭EXCEL并退出procedure TForm1.Button3Click(Sender:TObject);
begin
try
if not varIsEmpty(v) then
begin//如果需要在关闭前确定是否存盘,加入://
v.DiaplayAlert:=true;//确定存盘//
v.DialayAlert:=False;//不存盘,直接退出
v.WorkBooks[1].Close(True,'C:\untitled.xls');// 取文件名退出
v.Quit;
end;
finally
Close;
end
end;