如果你是大牛,请帮我指出错误(我知道代码有问题)。如果你是小菜,这些代码可供你参考。如果你介于两者之间,请不要喷我。
在网上找了7、8种方法。拿到我的环境下发现总是有很多问题导致调试不通过。自己写了一下,请帮忙找错,并指出解决方法。
下面是我的代码:编译环境 delphi 2007 +win7
var
realfile1: string; //excel模板文件所在位置
sstrs: array of string;
I, j, m, n: integer; //
begin
ProgressBar1.Visible := true;
ProgressBar1.Max := form1.Width;
ProgressBar1.Min := 0;
if SaveDialog1.Execute() then
begin
realfile1 := SaveDialog1.FileName;
end;
try
ExcelApplication1.Workbooks.Open(realfile1, 0, true, null, null, null, null, null, null, null, null, null, null, 0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
setlength(sstrs, ExcelWorksheet1.UsedRange[1].Columns.Count);
for j := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
sstrs[j] := (ExcelWorkbook1.Worksheets.Item[1] as _WorkSheet).Cells.Item[1, j + 1];
end;
ProgressBar1.Step := round(Form1.Width div (ExcelWorksheet1.UsedRange[1].Rows.Count - 1));
ProgressBar1.Left := 0;
ProgressBar1.Position := 0;
table1.Open;
if (ExcelWorksheet1.UsedRange[1].Columns.Count <> table1.fieldcount) then
begin
showmessage('源、目标表的字段数不一致');
exit;
end;
if (ExcelWorksheet1.UsedRange[1].Columns.Count = table1.fieldcount) then
begin
for i := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
if sstrs[i] <> table1.fields[i].fieldname then
begin
showmessage('源、目标表的字段名不一致');
exit;
end;
end;
end; for I := 2 to ExcelWorksheet1.UsedRange[1].Rows.Count do
begin
for j := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
sstrs[j] := (ExcelWorkbook1.Worksheets.Item[1] as _WorkSheet).Cells.Item[i, j + 1];
end;
try
begintrans();
with Query1 do
begin
n := 1;
close;
sql.Clear;
SQL.ADD('select * from sven where ID = ' + sstrs[0] + ' ');
open;
if fieldbyname('ID').asstring = '' then
begin
close;
sql.Clear;
SQL.ADD('INSERT INTO sven VALUES(' + sstrs[0] + ' ');
while (n < table1.fieldcount) do
begin
sql.add(' ,' + sstrs[n] + ' ');
inc(n);
end;
sql.add(' ) ');
execsql;
close;
end;
if fieldbyname('ID').asstring <> '' then
begin
m := 2;
close;
sql.Clear;
SQL.ADD('update sven set ' + table1.fields[1].fieldname + ' = ' + sstrs[1] + ' ');
while (m < table1.fieldcount) do
begin
sql.Add(' ,' + table1.fields[m].fieldname + ' =' + sstrs[m] + ' ');
inc(m);
end;
SQL.ADD('where ' + table1.fields[0].fieldname + ' = ' + sstrs[0] + ' ');
inputbox('', '', sql.text);
execsql;
close;
end;
end;
committrans();
except
rollback();
end;
ProgressBar1.Position := (i - 1) * ProgressBar1.Step;
end;
ProgressBar1.Position := form1.Width;
except
on e: exception do
begin
showmessage('打开excel文件出错。' + e.Message);
exit;
end;
end;
with query1 do
begin
close;
sql.Clear;
sql.Add('select * from sven order by ID asc');
open;
end;
end;
在网上找了7、8种方法。拿到我的环境下发现总是有很多问题导致调试不通过。自己写了一下,请帮忙找错,并指出解决方法。
下面是我的代码:编译环境 delphi 2007 +win7
var
realfile1: string; //excel模板文件所在位置
sstrs: array of string;
I, j, m, n: integer; //
begin
ProgressBar1.Visible := true;
ProgressBar1.Max := form1.Width;
ProgressBar1.Min := 0;
if SaveDialog1.Execute() then
begin
realfile1 := SaveDialog1.FileName;
end;
try
ExcelApplication1.Workbooks.Open(realfile1, 0, true, null, null, null, null, null, null, null, null, null, null, 0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
setlength(sstrs, ExcelWorksheet1.UsedRange[1].Columns.Count);
for j := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
sstrs[j] := (ExcelWorkbook1.Worksheets.Item[1] as _WorkSheet).Cells.Item[1, j + 1];
end;
ProgressBar1.Step := round(Form1.Width div (ExcelWorksheet1.UsedRange[1].Rows.Count - 1));
ProgressBar1.Left := 0;
ProgressBar1.Position := 0;
table1.Open;
if (ExcelWorksheet1.UsedRange[1].Columns.Count <> table1.fieldcount) then
begin
showmessage('源、目标表的字段数不一致');
exit;
end;
if (ExcelWorksheet1.UsedRange[1].Columns.Count = table1.fieldcount) then
begin
for i := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
if sstrs[i] <> table1.fields[i].fieldname then
begin
showmessage('源、目标表的字段名不一致');
exit;
end;
end;
end; for I := 2 to ExcelWorksheet1.UsedRange[1].Rows.Count do
begin
for j := 0 to ExcelWorksheet1.UsedRange[1].Columns.Count - 1 do
begin
sstrs[j] := (ExcelWorkbook1.Worksheets.Item[1] as _WorkSheet).Cells.Item[i, j + 1];
end;
try
begintrans();
with Query1 do
begin
n := 1;
close;
sql.Clear;
SQL.ADD('select * from sven where ID = ' + sstrs[0] + ' ');
open;
if fieldbyname('ID').asstring = '' then
begin
close;
sql.Clear;
SQL.ADD('INSERT INTO sven VALUES(' + sstrs[0] + ' ');
while (n < table1.fieldcount) do
begin
sql.add(' ,' + sstrs[n] + ' ');
inc(n);
end;
sql.add(' ) ');
execsql;
close;
end;
if fieldbyname('ID').asstring <> '' then
begin
m := 2;
close;
sql.Clear;
SQL.ADD('update sven set ' + table1.fields[1].fieldname + ' = ' + sstrs[1] + ' ');
while (m < table1.fieldcount) do
begin
sql.Add(' ,' + table1.fields[m].fieldname + ' =' + sstrs[m] + ' ');
inc(m);
end;
SQL.ADD('where ' + table1.fields[0].fieldname + ' = ' + sstrs[0] + ' ');
inputbox('', '', sql.text);
execsql;
close;
end;
end;
committrans();
except
rollback();
end;
ProgressBar1.Position := (i - 1) * ProgressBar1.Step;
end;
ProgressBar1.Position := form1.Width;
except
on e: exception do
begin
showmessage('打开excel文件出错。' + e.Message);
exit;
end;
end;
with query1 do
begin
close;
sql.Clear;
sql.Add('select * from sven order by ID asc');
open;
end;
end;
解决方案 »
- form1>FormCreate事件问题
- 隆重庆祝本人排名进入10000名。散分200
- TMediaPlayer的问题,用程序控制录音播放,但控件的PLAY没有变成不可使能,暂停还有停止也没有变成可使能
- listview中如何调整图标间的距离和如何固定在一行中输出指定数目项
- dspack2.3.1播放ASF文件出错!
- 怎么在word文档中插入几行字啊
- 如何创建ini文件???
- 我自己继承于label写了一个控件,我想任何属性改变就出发一个事件,如何写?
- 请教如何定义一棵树的结构?或者构造的算法?
- 用TBatchMove进行表的复制出现的问题。。在线等待
- Delphi record结构体 数据较多时出现数据紊乱和内存报错
- TStringList最后一个回车怎么去掉获取不要
with adoquery1 do
close;
sql.Clear;
ParamCheck := false;
sql.text:='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
//strPath:Excel的全路径
execsql;
end;