如何将EXCEL在数据库SQL Server中导入导出？

[IAMUN]

现在有一个EXCEL表格，怎么导入到数据库？
将一个数据库怎么导出为EXCEL表格？BDE连接吗？
我实验了几次，BDE连接也不行！
Windows Server 2003下,Delphi7!

[fhuibo]

excel表导入数据库代码：
uses ：comobj，db；
Procedure TForm1.Excel_2_db(str :string);
var eclApp,WorkBook :variant ;
xlsFileName :string;
a_FiledCount:integer; //数据库表中的列数
b_filedCount:integer; //excel 文件中的 列数
b_row :integer; // excel 文件的行熟
i,j :integer;
a_flag :boolean;
begin
Form1.OpenDialog1.Title :='Excel文件 导入到数据库'+str+'表';
Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
if (Form1.OpenDialog1.Execute )
then xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName)
else exit;

try
eclApp := CreateOleObject('Excel.Application');
WorkBook :=CreateOleObject('Excel.Sheet');
except
showmessage('您系统未安装MS-EXCEL');
exit;
end;
try
workBook :=eclApp.WorkBooks.add ;
eclApp.workBooks.open(Form1.OpenDialog1.FileName );
except
on EOleException do
begin
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end;
end;
eclApp.visible :=false;
try //try ..finally
try //try ..except
With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
a_FiledCount :=FieldCount;

end;

b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数
b_row :=eclApp.activesheet.UsedRange.rows.count; //返回excel 表中的行数

if (a_FiledCount <>b_FiledCount) //当数据 表和导入的excel表中的列数不一样，说明导入的excel文件不是正确的
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
exit;
end
else begin //列数正确，但是还要继续判断每列的字段名是否一致


for i :=1 to b_filedCount do
begin
//showmessage(eclApp.activesheet.cells.item[1,i].value);
//showmessage(DataMod.ADO_basic.Fields.Fields[i-1].FieldName );

if eclApp.activesheet.cells.item[2,i].value<>Data_Mod.DataModule1.kcinfo_Tab.Fields[i-1].FieldName //判断字段名是否相等
//if eclApp.activesheet.cells.item[1,i].value<>DataMod.ADO_basic.Fields.Fields[i-1].FieldName //判断中文title.caption 是否相等
then begin
showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
exit;
end;
end; //for i:=.....
end; //end with else

for i :=3 to b_row do //行
begin
a_flag :=Data_Mod.DataModule1.kcinfo_Tab.Locate(eclApp.activesheet.cells.item[2,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]);
if (a_flag =true)
then begin
showmessage('该记录已经存在');
Data_Mod.DataModule1.kcinfo_Tab.Next ;
continue;
end;

With Data_Mod.DataModule1.kcinfo_Tab do
begin
close ;
TableName :=str;
active :=true;
Append;
end;
For j :=1 to b_filedCount do //列
begin //开始导入数据库
//showmessage(eclApp.activesheet.cells.item[1,j]);//.Value);
////showmessage(eclApp.activesheet.cells.item[i,j].value);
//showmessage(eclApp.activesheet.cells[i,j].value);

Data_Mod.DataModule1.kcinfo_Tab.FieldByName(eclApp.activesheet.cells.item[2,j]).Value :=eclApp.activesheet.cells[i,j].value;

end ; //end with For j :=1 to b_filedCount do
Data_Mod.DataModule1.kcinfo_Tab.Post ;
Data_Mod.DataModule1.kcinfo_Tab.Refresh ;
end;
showmessage('导入数据成功');
except
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end; //end try except
finally //操作错误，退出
WorkBook.close;
eclApp.quit;
eclApp:=Unassigned;
Data_Mod.DataModule1.kcinfo_Tab.Close ;
end;
end;

[fhuibo]

导出为excel表：
procedure TForm3.Excel4Click(Sender: TObject); //将联合查询的结构转为excel表
var xlsFilename :string;
eclApp,WorkBook :variant ;
a_filedNo,i,j :integer;
begin
a_filedNo :=Form3.DBGrid4.FieldCount ;
xlsFileName :='关于学生成绩基本信息.xls';

try
eclApp :=CreateOleObject('Excel.Application');
WorkBook :=CreateOleObject('Excel.Sheet');
except
showmessage('您的系统没有安装MS EXCEL');
exit;
end;

try
WorkBook :=eclApp.workBooks.add ;
for i :=1 to a_FiledNo do //转化字段名；
begin
//eclApp.cells(1,i) :=Form3.DBGrid4.Columns[i-1].Title.caption ;
eclApp.cells(1,i) :=Form3.DBGrid4.Fields[i-1].FieldName ;
end;

Form3.DBGrid4.DataSource.DataSet.First ;
for i :=1 to Form3.a_recno do //Form3.a_recno

begin
for j :=1 to a_filedNo do //转化一个记录
begin
eclApp.cells(i+1,j) :=Form3.DbGrid4.Fields[j-1].Value ;
end;
Form3.DBGrid4.DataSource.DataSet.Next ;
end;
try
WorkBook.saveas(ExtractFilePath(Application.ExeName )+xlsFileName);
WorkBook.close;
showmessage('保存EXECL文件成功，路径为：'+ExtractFilePath(Application.ExeName )+xlsFileName);
except
showmessage('保存文件出错');
end;


except
showmessage('不能正确操作EXECL文件，可能该文件已经被其他程序占用或系统错误');
WorkBook.close;
eclApp.quit;
eclApp :=Unassigned;
end;
end;

[IAMUN]

大侠！你们都太牛了！佩服的五体投地！

[IAMUN]

over

[zwjason]

good

[mashuo]

给你个例子
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False')...sheet1$

如果要在程序中执行的话Excel联接ADO串
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\a.xls;Extended
Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False
Extended Properties参数属性这样写 Excel 5.0;HDR=YES;IMEX=1
IMEX=1就是指混合型转换为文本

[michaelpeng7799]

你用的是SQLSERVER么？SQLSERVER里有一个DTS可以将数据库转为其它格式导出，
在SQLSERVER盘上还有相关例子，不过用的是VB，其实中调用DTS那个组件来实现的。
在delphi的import type libaray里能找到microsoft dts.....什么的。

[michaelpeng7799]

我以前的一个文章，不过是VB的，如果你用的是access，可以参考的
mashuo(mashuo) 的方法和我的那个文章

[michaelpeng7799]

对不起忘粘地指了，http://www.csdn.net/Develop/read_article.asp?id=4540

[IAMUN]

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False')...sheet1$
虽然执行成功，但是没有看到结果啊？

[mashuo]

你是在查询分析器里执行的吗？

[dancedog]

不要使用ole,我把我以前写的一些给你,你可以先用adotable打开,再一对一的添加到连接到sql server的adotable.



经过摸索,我终于找到了方便快捷的通过jet把excel数据导出方法,我把我的一些经验写出来,希望对后人有帮助.

假设有一个excel表d:\在职职工库.xls,簿中有一个表在职职工,其中有如下数据

2002年5月职工花名册

序号 姓名 性别 民族 工资

1 张三 男 汉 800.00

2 李四 男 回 1200.00

3 张芸 女 汉 852.00

4 何天荣 男 满 962.00



现要把其中的职工数据导出到d:\zghmc.dbf.



首先用excel打开d:\在职职工库.xls,选择包含职工数据的区域,不包括标题"2002年5月职工花名册",执行"插入->名称->定义",输入zgk,为所选的数据定义一个名称,关闭excel.



在project中加入一个ADOConnection1、一个ADOQuery1，添加以下代码：

ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\在职职工库.xls;Extended Properties=excel 8.0;Persist Security Info=False';

ADOQuery1.Connection:= ADOConnection1;

ADOQuery1.SQL.Clear;

ADOQuery1.SQL.Add('select * into zghmc in "d:\" "dBASE 5.0;" from zgk' );

ADOQuery1.ExecSQL;

ADOQuery1.Close;



然后编译运行，就把excel中的职工数据导出到d:\zghmc.dbf中了。

如果要在程序中显示excel中的数据,添加一个ADOTable1,设置Connection为ADOConnection1就可以了.

[IAMUN]

up

[IORILI]

procedure Tjiance.ToolButton1Click(Sender: TObject);
var
myworksheet1:TExcelWorksheet;
MyExcel:TExcelApplication;
MyWorkbook:TExcelWorkbook;
cell,cur_row:integer;
begin
if datamodule2.SaveDialog1.Execute then
begin
//连接excel
MyExcel:=TExcelApplication.Create(Application);
MyWorkbook:=TExcelWorkbook.Create(Application);
myworksheet1:=TExcelWorksheet.Create(Application);
myexcel.connect;
myexcel.workbooks.Add(EmptyParam, 0);
myworkbook.ConnectTo(ExcelApplication1.Workbooks[1]);
myWorksheet1.ConnectTo(myWorkbook.Worksheets[1] as _worksheet);
myworksheet1.name:='基本信息';
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type where oid='''+trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.Open;
//写机型信息
myworksheet1.cells.item[1, 1]:='名称';
myworksheet1.cells.item[1, 2]:=datamodule2.publicq.FieldByName('name').AsString;
myworksheet1.cells.item[2, 1]:='专用号';
myworksheet1.cells.item[2, 2].NumberFormatLocal:='@';
myworksheet1.cells.item[2, 2]:=datamodule2.publicq.FieldByName('oid').AsString;
myworksheet1.cells.item[3, 1]:='型号经理';
myworksheet1.cells.item[3, 2]:= datamodule2.publicq.FieldByName('type_mgr').AsString;
myworksheet1.cells.item[4, 1]:='编制';
myworksheet1.cells.item[4, 2]:=datamodule2.publicq.FieldByName('bianzhi').AsString;
myworksheet1.range[myworksheet1.cells.Item[1, 1],myworksheet1.cells.Item[4,1]].Font.ColorIndex := 5;

myworksheet1.cells.item[6, 1]:='母本厂家';
myworksheet1.cells.item[6, 2]:='国别';
myworksheet1.cells.item[6, 3]:='机型';
myworksheet1.cells.item[6, 4]:='借鉴点';
myworksheet1.cells.item[6, 5]:='差异点';
myworksheet1.cells.item[6, 6]:='母本价格';
myworksheet1.cells.item[6, 7]:='市场占有率';
myworksheet1.range[myworksheet1.cells.Item[6, 1],myworksheet1.cells.Item[6,7]].Font.ColorIndex := 5;

For cell:=3 To 9 do
myworksheet1.cells.item[7,cell-2]:=datamodule2.publicq.Fields[cell].AsString;
myworksheet1.cells.item[9, 1]:='价格';
myworksheet1.cells.item[9, 2]:='卖点';
myworksheet1.cells.item[9, 3]:='竞争对手分析';
myworksheet1.cells.item[9, 4]:='图片';
myworksheet1.cells.item[9, 5]:='目标销量';
myworksheet1.range[myworksheet1.cells.Item[9, 1],myworksheet1.cells.Item[9,5]].Font.ColorIndex := 5;

For cell:=10 To 14 do
myworksheet1.cells.item[10,cell-9]:=datamodule2.publicq.Fields[cell].AsString;
// '写案例信息
myworksheet1.cells.item[12, 1]:='案例日期';
myworksheet1.cells.item[12, 2]:='案例经过';
myworksheet1.cells.item[12, 3]:='紧急措施';
myworksheet1.cells.item[12, 4]:='过渡措施';
myworksheet1.cells.item[12, 5]:='根治措施';
myworksheet1.range[myworksheet1.cells.Item[12, 1],myworksheet1.cells.Item[12,5]].Font.ColorIndex := 5;

For cell:= 16 To 20 do
myworksheet1.cells.item[13,cell-15]:=datamodule2.publicq.Fields[cell].AsString;
// '写成本信息
myworksheet1.cells.item[15, 1]:='目标成本价';
myworksheet1.cells.item[15, 2]:= datamodule2.publicq.Fields[15].AsString;
//myworksheet1.cells.item[15, 1).Font.ColorIndex = 5
myworksheet1.cells.item[16, 1]:='竞争对手名称';
myworksheet1.cells.item[16, 2]:='竞争对手类似产品';
myworksheet1.cells.item[16, 3]:='竞争对手产品供价';
myworksheet1.cells.item[16, 4]:='竞争对手产品售价';
myworksheet1.cells.item[16, 5]:='竞争对手产品销量';
myworksheet1.range[myworksheet1.cells.Item[16, 1],myworksheet1.cells.Item[16,5]].Font.ColorIndex := 5;

datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type_enemy where type_oid='''+trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.open;
cur_row := 17;
While Not datamodule2.publicq.EOF do
begin
For cell := 1 To 5 do
myworksheet1.cells.item[cur_row, cell]:=datamodule2.publicq.Fields[cell].AsString;
datamodule2.publicq.Next;
cur_row := cur_row + 1;end;
//写专利信息
cur_row := cur_row + 1;
myworksheet1.cells.item[cur_row, 1]:='专利种类';
myworksheet1.cells.item[cur_row, 2]:='专利描述';
myworksheet1.cells.item[cur_row, 3]:='专利号';
myworksheet1.cells.item[cur_row, 4]:='时间';
myworksheet1.cells.item[cur_row, 5]:='专利人';
myworksheet1.cells.item[cur_row, 6]:='有效期';
myworksheet1.range[myworksheet1.cells.Item[cur_row, 1],myworksheet1.cells.Item[cur_row,6]].Font.ColorIndex := 5;

cur_row := cur_row + 1;
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type_patent where type_oid='''+Trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.open;
While Not datamodule2.publicq.Eof do
begin
For cell:= 1 To 6 do
myworksheet1.cells.item[cur_row,cell]:=datamodule2.publicq.fields[cell].asstring;
datamodule2.publicq.Next;
end;

[IORILI]

procedure Tjiance.ToolButton1Click(Sender: TObject);
var
myworksheet1:TExcelWorksheet;
MyExcel:TExcelApplication;
MyWorkbook:TExcelWorkbook;
cell,cur_row:integer;
begin
if datamodule2.SaveDialog1.Execute then
begin
//连接excel
MyExcel:=TExcelApplication.Create(Application);
MyWorkbook:=TExcelWorkbook.Create(Application);
myworksheet1:=TExcelWorksheet.Create(Application);
myexcel.connect;
myexcel.workbooks.Add(EmptyParam, 0);
myworkbook.ConnectTo(ExcelApplication1.Workbooks[1]);
myWorksheet1.ConnectTo(myWorkbook.Worksheets[1] as _worksheet);
myworksheet1.name:='基本信息';
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type where oid='''+trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.Open;
//写机型信息
myworksheet1.cells.item[1, 1]:='名称';
myworksheet1.cells.item[1, 2]:=datamodule2.publicq.FieldByName('name').AsString;
myworksheet1.cells.item[2, 1]:='专用号';
myworksheet1.cells.item[2, 2].NumberFormatLocal:='@';
myworksheet1.cells.item[2, 2]:=datamodule2.publicq.FieldByName('oid').AsString;
myworksheet1.cells.item[3, 1]:='型号经理';
myworksheet1.cells.item[3, 2]:= datamodule2.publicq.FieldByName('type_mgr').AsString;
myworksheet1.cells.item[4, 1]:='编制';
myworksheet1.cells.item[4, 2]:=datamodule2.publicq.FieldByName('bianzhi').AsString;
myworksheet1.range[myworksheet1.cells.Item[1, 1],myworksheet1.cells.Item[4,1]].Font.ColorIndex := 5;

myworksheet1.cells.item[6, 1]:='母本厂家';
myworksheet1.cells.item[6, 2]:='国别';
myworksheet1.cells.item[6, 3]:='机型';
myworksheet1.cells.item[6, 4]:='借鉴点';
myworksheet1.cells.item[6, 5]:='差异点';
myworksheet1.cells.item[6, 6]:='母本价格';
myworksheet1.cells.item[6, 7]:='市场占有率';
myworksheet1.range[myworksheet1.cells.Item[6, 1],myworksheet1.cells.Item[6,7]].Font.ColorIndex := 5;

For cell:=3 To 9 do
myworksheet1.cells.item[7,cell-2]:=datamodule2.publicq.Fields[cell].AsString;
myworksheet1.cells.item[9, 1]:='价格';
myworksheet1.cells.item[9, 2]:='卖点';
myworksheet1.cells.item[9, 3]:='竞争对手分析';
myworksheet1.cells.item[9, 4]:='图片';
myworksheet1.cells.item[9, 5]:='目标销量';
myworksheet1.range[myworksheet1.cells.Item[9, 1],myworksheet1.cells.Item[9,5]].Font.ColorIndex := 5;

For cell:=10 To 14 do
myworksheet1.cells.item[10,cell-9]:=datamodule2.publicq.Fields[cell].AsString;
// '写案例信息
myworksheet1.cells.item[12, 1]:='案例日期';
myworksheet1.cells.item[12, 2]:='案例经过';
myworksheet1.cells.item[12, 3]:='紧急措施';
myworksheet1.cells.item[12, 4]:='过渡措施';
myworksheet1.cells.item[12, 5]:='根治措施';
myworksheet1.range[myworksheet1.cells.Item[12, 1],myworksheet1.cells.Item[12,5]].Font.ColorIndex := 5;

For cell:= 16 To 20 do
myworksheet1.cells.item[13,cell-15]:=datamodule2.publicq.Fields[cell].AsString;
// '写成本信息
myworksheet1.cells.item[15, 1]:='目标成本价';
myworksheet1.cells.item[15, 2]:= datamodule2.publicq.Fields[15].AsString;
//myworksheet1.cells.item[15, 1).Font.ColorIndex = 5
myworksheet1.cells.item[16, 1]:='竞争对手名称';
myworksheet1.cells.item[16, 2]:='竞争对手类似产品';
myworksheet1.cells.item[16, 3]:='竞争对手产品供价';
myworksheet1.cells.item[16, 4]:='竞争对手产品售价';
myworksheet1.cells.item[16, 5]:='竞争对手产品销量';
myworksheet1.range[myworksheet1.cells.Item[16, 1],myworksheet1.cells.Item[16,5]].Font.ColorIndex := 5;

datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type_enemy where type_oid='''+trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.open;
cur_row := 17;
While Not datamodule2.publicq.EOF do
begin
For cell := 1 To 5 do
myworksheet1.cells.item[cur_row, cell]:=datamodule2.publicq.Fields[cell].AsString;
datamodule2.publicq.Next;
cur_row := cur_row + 1;end;
//写专利信息
cur_row := cur_row + 1;
myworksheet1.cells.item[cur_row, 1]:='专利种类';
myworksheet1.cells.item[cur_row, 2]:='专利描述';
myworksheet1.cells.item[cur_row, 3]:='专利号';
myworksheet1.cells.item[cur_row, 4]:='时间';
myworksheet1.cells.item[cur_row, 5]:='专利人';
myworksheet1.cells.item[cur_row, 6]:='有效期';
myworksheet1.range[myworksheet1.cells.Item[cur_row, 1],myworksheet1.cells.Item[cur_row,6]].Font.ColorIndex := 5;

cur_row := cur_row + 1;
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type_patent where type_oid='''+Trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.open;
While Not datamodule2.publicq.Eof do
begin
For cell:= 1 To 6 do
myworksheet1.cells.item[cur_row,cell]:=datamodule2.publicq.fields[cell].asstring;
datamodule2.publicq.Next;
end;
myWorksheet1.ConnectTo(myWorkbook.Worksheets[2] as _worksheet);

[IORILI]

myworksheet1.Name := '机型BOM';
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('execute create_bom '''+Trim(dbedit1.Text)+''''+',0');
datamodule2.publicq.ExecSQL;
datamodule2.publicq.Close;
datamodule2.publicquery.SQL.Clear;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select ceng,parent_oid,com_oid,name,mater,size,per_use,base_unit,order_no,try_factory,try_price from temp_bom');
datamodule2.publicq.Active:=true;
datamodule2.publicq.open;
myworksheet1.cells.item[1, 1]:='层';
myworksheet1.cells.item[1, 2]:='装配关系';
myworksheet1.cells.item[1, 3]:='专用号';
myworksheet1.cells.item[1, 4]:='名称';
myworksheet1.cells.item[1, 5]:='材料';
myworksheet1.cells.item[1, 6]:='规格';
myworksheet1.cells.item[1, 7]:='单台用量';
myworksheet1.cells.item[1, 8]:='单位';
myworksheet1.cells.item[1, 9]:='工序';
myworksheet1.cells.item[1, 10]:='试制时厂家';
myworksheet1.cells.item[1, 11]:='试制时价格';
myworksheet1.range[myworksheet1.cells.Item[1, 1],myworksheet1.cells.Item[1,11]].Font.ColorIndex := 5;

myworksheet1.cells.item[2, 2].NumberFormatLocal:='@';
myworksheet1.cells.item[3,3].NumberFormatLocal :='@';
cur_row := 2;
While Not datamodule2.publicq.EOF do
begin
For cell:= 0 To 10 do
myworksheet1.cells.item[cur_row,cell+ 1]:=datamodule2.publicq.Fields[cell].AsString;
datamodule2.publicq.next;
cur_row := cur_row + 1;
end;
//'写试制信息
cur_row := cur_row + 1;
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type where oid='''+trim(dbedit1.Text)+'''');
datamodule2.publicq.Active:=true;
datamodule2.publicq.Open;
myworksheet1.cells.item[cur_row, 1] :='试制日期';
myworksheet1.cells.item[cur_row, 2] :='试制人';
myworksheet1.cells.item[cur_row, 3] :='试制中出现的问题';
myworksheet1.cells.item[cur_row, 4] :='解决措施';
myworksheet1.range[myworksheet1.cells.Item[cur_row, 1],myworksheet1.cells.Item[cur_row,4]].Font.ColorIndex := 5;

cur_row := cur_row + 1;
For cell:= 25 To 28 do
myworksheet1.cells.item[cur_row, cell- 24]:=datamodule2.publicq.Fields[cell].AsString;
//'写认证信息
myWorksheet1.ConnectTo(myWorkbook.Worksheets[3] as _worksheet);
myworksheet1.Name :='认证信息';
datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select * from type_rz where type_oid='''+trim(dbedit1.text)+'''');
datamodule2.publicq.active:=true;
datamodule2.publicq.open;
// .Columns(1).NumberFormatLocal = "@"
cur_row := 1;
While Not datamodule2.publicq.Eof do
begin
myworksheet1.cells.item[cur_row, 1]:='认证种类';
myworksheet1.cells.item[cur_row, 2]:='期限';
myworksheet1.cells.item[cur_row, 3]:='完成日期';
myworksheet1.cells.item[cur_row, 4]:='印刷品标志';
myworksheet1.range[myworksheet1.cells.Item[cur_row, 1],myworksheet1.cells.Item[cur_row,4]].Font.ColorIndex := 5;

cur_row := cur_row + 1;
For cell:= 1 To 4 do
myworksheet1.cells.item[cur_row,cell]:=datamodule2.publicq.Fields[cell].AsString;
datamodule2.publicq.Next;
cur_row := cur_row + 1;
end;
myworksheet1.cells.item[cur_row, 1] :='部件专用号';
myworksheet1.cells.item[cur_row, 2] :='认证时厂家';
myworksheet1.cells.item[cur_row, 3] :='认证时价格';
myworksheet1.range[myworksheet1.cells.Item[cur_row, 1],myworksheet1.cells.Item[cur_row,3]].Font.ColorIndex := 5;

datamodule2.publicq.Close;
datamodule2.publicq.SQL.Clear;
datamodule2.publicq.SQL.Add('select com_oid,com_factory,com_price from type_rz_com where type_oid='''+trim(dbedit1.text)+'''');
datamodule2.publicq.active:=true;
datamodule2.publicq.open;
cur_row := cur_row + 1;
While Not datamodule2.publicq.EOF do
begin
For cell:= 0 To 2 do
myworksheet1.cells.item[cur_row,cell+ 1]:=datamodule2.publicq.Fields[cell].AsString;
datamodule2.publicq.Next;
cur_row := cur_row + 1;
end;
// cur_row := cur_row + 1;
myworkbook.Close;
myexcel.Quit;
myworksheet1.SaveAs(datamodule2.SaveDialog1.FileName+'.xls');
end;
end;

end.

生成3个worksheet 给sheet的名字负值，填充 绝对原创

[IAMUN]

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source= "C:\temp\b.xls ";Extended Properties= "Excel 8.0;HDR=Yes; ";Persist Security Info=False')...sheet1$
出错！
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。
[OLE/DB provider returned message: 找不到可安装的 ISAM。]

[IAMUN]

IORILI(虫子) ,完整的代码（project）能否发给我[e-mail]?
万分感谢？

[IAMUN]

ADO连接EXCEL数据库，ADOConnection String为：
Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=C:\book1.xls;Mode=Read;Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

但是问题是返回的record count返回不了！

[IAMUN]

测试用edit1.Text:=inttostr(adotable1.RecordCount);出错