delphi 自带的空间有些弱.我一直的做法是用一个第三方控件 叫TXLSReadWriteII 思路就是:读excel的函数-->二维数组-->写excel的函数。 下面是代码。 ----------------------------------- unit OpExcell;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, XLSReadWriteII2, QFileCtrls, OleCtrls, DB, ADODB, CellFormats2, XLSFonts2, BIFFRecsII2, Rows2;type TStringArray = array of array of string; TOpExcell = class(TObject) public function ReadXLSII(var Content: TStringArray; fileName: string): BOOL; //读excel表格 function WriteXLSII(var Content: TStringArray; fileName: string): BOOL; //写excel表格 end; implementation function TOpExcell.ReadXLSII(var Content: TStringArray; fileName: string): BOOL; var iR, iC, i, j: Integer; XLS: TXLSReadWriteII2;begin Result := True; XLS := TXLSReadWriteII2.Create(nil); XLS.fileName := fileName; XLS.Read; //得到行列 iR := XLS.Sheets[0].LastRow; iC := XLS.Sheets[0].LastCol; try SetLength(Content, iR + 1, iC + 1); for i := 0 to iR do begin for j := 0 to iC do begin Content[i, j] := XLS.Sheets[0].AsString[j, i]; end; end; except Result := False; end; if Assigned(XLS) then begin XLS.Destroy; end; end;function TOpExcell.WriteXLSII(var Content: TStringArray; fileName: string): BOOL; var iR, iC, i, j: Integer; XLS: TXLSReadWriteII2;begin XLS := TXLSReadWriteII2.Create(nil); XLS.fileName := fileName; try try iR := Length(Content); iC := Length(Content[0]); for i := 0 to iR - 1 do begin for j := 0 to iC - 1 do begin XLS.Sheets[0].AsString[j, i] := Content[i, j]; end; end; XLS.Write; Result := True; except Result := False; end; finally XLS.Destroy; end end; end.
1.導入: procedure TRES_DCC_ECRN_F.cxButton1Click(Sender: TObject); var ExcelApp,WorkBook:Olevariant; ExcelSheetCount,i,k:Integer; begin inherited; if RzButtonEdit1.Text <>'' then begin if (ExtractFileExt(RzButtonEdit1.Text)<>'.xls') then begin MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0); RzButtonEdit1.Clear; end else begin try Application.ProcessMessages; ExcelApp:=CreateOleObject('Excel.Application'); WorkBook:=ExcelApp.WorkBooks.Open(RzButtonEdit1.Text); ExcelApp.Visible:=False; ExcelSheetCount:=WorkBook.WorkSheets.Count; for i:=1 to ExcelSheetCount do begin Screen.Cursor:=crSQLWait; WorkBook.WorkSheets[i].Activate; a:=ExcelApp.Cells[4,2].Value; b:=ExcelApp.Cells[8,2].Value; c:=ExcelApp.Cells[14,2].Value; d:=ExcelApp.Cells[21,2].Value; e:=ExcelApp.Cells[22,2].Value; f:=ExcelApp.Cells[23,2].Value; if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then begin MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0); InsertErrorLOG; Screen.Cursor:=crDefault; Exit; end; with adoq_ecrn do begin Close; SQL.Text:='select * from RES_ECR_TYPE where ECRNO='''+ a+''''; Open; if RecordCount=1 then begin MessageDlg('請檢查:導入檔案時編號"'+ a+'"的記錄重復!',mtWarning,[mbOK],0); InsertRepeatLOG; Screen.Cursor:=crDefault; Exit; end; end; with adoq_ecrn do begin Close; SQL.Clear; SQL.Add('insert into RES_ECR_TYPE(ECRNO,DESOFCHANGE,RELEASEDATE,RESPEOPLE,ISSUEDEPT,MODEL) values(:a,:b,:c,:d,:e,:f)'); Parameters.ParamByName('a').Value:=a; Parameters.ParamByName('b').Value:=b; Parameters.ParamByName('c').Value:=Trim(Copy(c,1,19)); Parameters.ParamByName('d').Value:=d; Parameters.ParamByName('e').Value:=e; Parameters.ParamByName('f').Value:=f; ExecSQL; end; InsertECRLOG; ProgressBar1.Min:=0; ProgressBar1.Max:=ExcelSheetCount; for k:=33 to WorkBook.WorkSheets[i].usedrange.rows.count do begin g:=ExcelApp.Cells[K,1].Value; q:=ExcelApp.Cells[k,2].Value; w:=ExcelApp.Cells[k,3].Value; v:=ExcelApp.Cells[k,4].Value; r:=ExcelApp.Cells[k,5].Value; t:=ExcelApp.Cells[k,6].Value; y:=ExcelApp.Cells[k,7].Value; u:=ExcelApp.Cells[k,8].Value; o:=ExcelApp.Cells[k,9].Value; if (q<>'')and(w<>'') then with adoq_item do begin Close; SQL.Clear; SQL.Add('insert into RES_ECR_ITEM(GROUPID,ECRNO,ITEMNO,ITEMDESPTION,FZRELATION,CHANGETYPE,BEFORECHANGE,AFTERCHANGE,PROPOSE,REMARK) values(:g,:p,:q,:w,:v,:r,:t,:y,:u,:o)'); Parameters.ParamByName('g').Value:=g; Parameters.ParamByName('p').Value:=a; Parameters.ParamByName('q').Value:=q; Parameters.ParamByName('w').Value:=w; Parameters.ParamByName('v').Value:=v; Parameters.ParamByName('r').Value:=r; Parameters.ParamByName('t').Value:=t; Parameters.ParamByName('y').Value:=y; Parameters.ParamByName('u').Value:=u; Parameters.ParamByName('o').Value:=o; ExecSQL; ProgressBar1.Position:=ProgressBar1.Position+1; end; end; Screen.Cursor:=crDefault; Application.ProcessMessages; ProgressBar1.Position:=0; RefreshECRN; RefreshGroupItem; SendToEmail; Zt:=1; end; finally ExcelApp.ActiveWorkBook.Saved:=True; WorkBook.Close; ExcelApp.Quit; end; end; end else begin MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0); Exit; end; end;
2.導出:(如果需要導出指定的格式需要用Variant\ole的方式導出,如果直接導出,可用以下代碼); uses cxGridExportLink; procedure TRes_Hr_BkSh_F.btnSB_PrintClick(Sender: TObject); begin inherited; if qry_approvebk.IsEmpty then begin MessageBox(Handle,'數據不能為空!!!','數據導出...',MB_OK+ MB_ICONWARNING); Exit; end else begin if dlgSave1.Execute then begin ExportGridToExcel(dlgSave1.FileName,cxGrid1,True,True,False,'XLS'); MessageBox(Handle,'數據成功導出!!!','數據導出...',MB_OK+ MB_ICONWARNING); end; end; end;
那你這表格控件中取值吧???參考一下以下的單元格取值代碼,或許對你有幫助:procedure TRES_HR_CQAUDIT_F.cxGrid1DBTableView1CellClick( Sender: TcxCustomGridTableView; ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton; AShift: TShiftState; var AHandled: Boolean); var xx,yy,zz,jj,ff,aa:Variant; begin inherited; xx:=ACellViewInfo.Value; yy:=cxGrid1DBTableView1.Controller.FocusedColumn.VisibleCaption; zz:=cxGrid1DBTableView1.Controller.FocusedRow.Values[0]; jj:=RightStr(yy,2); ff:=cxGrid1DBTableView1.Controller.FocusedColumnIndex; aa:=FormatDateTime('yyyy-mm',cxDateEdit1.Date)+'-'+jj; if StrToInt(Trim(ff))=0 then begin MessageDlg('提示:不要選擇“部門名稱”的列標題,請點擊選擇擇人數!',mtWarning,[mbOK],0); Exit; end else begin qry_psjbdetail.Close; qry_psjbdetail.SQL.Clear; qry_psjbdetail.SQL.Add( ' select '''+ FormatDateTime('mm',cxDateEdit1.Date)+''' as YF,c.COLRQ,''>''+'''+ Trim(dxSpinEdit1.Text)+'''+''Hr'' as TJ,c.CB,c.BM,c.KB,c.XB,c.COLAID,c.Full_Name,c.COLJBT from ' ); qry_psjbdetail.SQL.Add( ' (select a.COLRQ,b.CB,b.BM,b.KB,b.XB,a.COLAID,b.Full_Name,a.COLJBT from HR_EMPLOYEE_DAY a ' ); qry_psjbdetail.SQL.Add( ' left join Res_Hrd_Temp b on a.COLAID=b.Account_Id ' ); qry_psjbdetail.SQL.Add( ' where a.COLRQ='''+ Trim(aa)+''' and a.COLJBT>'''+ Trim(dxSpinEdit1.Text)+'''and b.BM='''+ Trim(zz)+''' ) c ' ); qry_psjbdetail.Open; end; end;
認真學習: for i:=1 to ExcelSheetCount do begin Screen.Cursor:=crSQLWait; WorkBook.WorkSheets[i].Activate; a:=ExcelApp.Cells[4,2].Value;//這裡就是取的excel指定的單元格數據 b:=ExcelApp.Cells[8,2].Value; c:=ExcelApp.Cells[14,2].Value; d:=ExcelApp.Cells[21,2].Value; e:=ExcelApp.Cells[22,2].Value; f:=ExcelApp.Cells[23,2].Value; if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then begin MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0); InsertErrorLOG; Screen.Cursor:=crDefault; Exit; end; ..........
叫TXLSReadWriteII
思路就是:读excel的函数-->二维数组-->写excel的函数。
下面是代码。
-----------------------------------
unit OpExcell;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, XLSReadWriteII2, QFileCtrls, OleCtrls, DB, ADODB, CellFormats2,
XLSFonts2, BIFFRecsII2, Rows2;type TStringArray = array of array of string; TOpExcell = class(TObject)
public function ReadXLSII(var Content: TStringArray; fileName: string): BOOL; //读excel表格
function WriteXLSII(var Content: TStringArray; fileName: string): BOOL; //写excel表格
end;
implementation
function TOpExcell.ReadXLSII(var Content: TStringArray; fileName: string): BOOL;
var
iR, iC, i, j: Integer;
XLS: TXLSReadWriteII2;begin Result := True; XLS := TXLSReadWriteII2.Create(nil);
XLS.fileName := fileName;
XLS.Read; //得到行列
iR := XLS.Sheets[0].LastRow;
iC := XLS.Sheets[0].LastCol; try SetLength(Content, iR + 1, iC + 1);
for i := 0 to iR do
begin
for j := 0 to iC do
begin
Content[i, j] := XLS.Sheets[0].AsString[j, i];
end;
end; except Result := False;
end; if Assigned(XLS) then
begin
XLS.Destroy;
end;
end;function TOpExcell.WriteXLSII(var Content: TStringArray; fileName: string): BOOL;
var
iR, iC, i, j: Integer;
XLS: TXLSReadWriteII2;begin XLS := TXLSReadWriteII2.Create(nil);
XLS.fileName := fileName; try
try iR := Length(Content);
iC := Length(Content[0]); for i := 0 to iR - 1 do
begin
for j := 0 to iC - 1 do
begin
XLS.Sheets[0].AsString[j, i] := Content[i, j];
end;
end; XLS.Write;
Result := True; except
Result := False;
end; finally
XLS.Destroy;
end
end;
end.
procedure TRES_DCC_ECRN_F.cxButton1Click(Sender: TObject);
var
ExcelApp,WorkBook:Olevariant;
ExcelSheetCount,i,k:Integer;
begin
inherited;
if RzButtonEdit1.Text <>'' then
begin
if (ExtractFileExt(RzButtonEdit1.Text)<>'.xls') then
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
RzButtonEdit1.Clear;
end
else
begin
try
Application.ProcessMessages;
ExcelApp:=CreateOleObject('Excel.Application');
WorkBook:=ExcelApp.WorkBooks.Open(RzButtonEdit1.Text);
ExcelApp.Visible:=False;
ExcelSheetCount:=WorkBook.WorkSheets.Count;
for i:=1 to ExcelSheetCount do
begin
Screen.Cursor:=crSQLWait;
WorkBook.WorkSheets[i].Activate;
a:=ExcelApp.Cells[4,2].Value;
b:=ExcelApp.Cells[8,2].Value;
c:=ExcelApp.Cells[14,2].Value;
d:=ExcelApp.Cells[21,2].Value;
e:=ExcelApp.Cells[22,2].Value;
f:=ExcelApp.Cells[23,2].Value;
if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then
begin
MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0);
InsertErrorLOG;
Screen.Cursor:=crDefault;
Exit;
end; with adoq_ecrn do
begin
Close;
SQL.Text:='select * from RES_ECR_TYPE where ECRNO='''+ a+'''';
Open;
if RecordCount=1 then
begin
MessageDlg('請檢查:導入檔案時編號"'+ a+'"的記錄重復!',mtWarning,[mbOK],0);
InsertRepeatLOG;
Screen.Cursor:=crDefault;
Exit;
end;
end; with adoq_ecrn do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_TYPE(ECRNO,DESOFCHANGE,RELEASEDATE,RESPEOPLE,ISSUEDEPT,MODEL) values(:a,:b,:c,:d,:e,:f)');
Parameters.ParamByName('a').Value:=a;
Parameters.ParamByName('b').Value:=b;
Parameters.ParamByName('c').Value:=Trim(Copy(c,1,19));
Parameters.ParamByName('d').Value:=d;
Parameters.ParamByName('e').Value:=e;
Parameters.ParamByName('f').Value:=f;
ExecSQL;
end;
InsertECRLOG; ProgressBar1.Min:=0;
ProgressBar1.Max:=ExcelSheetCount;
for k:=33 to WorkBook.WorkSheets[i].usedrange.rows.count do
begin
g:=ExcelApp.Cells[K,1].Value;
q:=ExcelApp.Cells[k,2].Value;
w:=ExcelApp.Cells[k,3].Value;
v:=ExcelApp.Cells[k,4].Value;
r:=ExcelApp.Cells[k,5].Value;
t:=ExcelApp.Cells[k,6].Value;
y:=ExcelApp.Cells[k,7].Value;
u:=ExcelApp.Cells[k,8].Value;
o:=ExcelApp.Cells[k,9].Value;
if (q<>'')and(w<>'') then
with adoq_item do
begin
Close;
SQL.Clear;
SQL.Add('insert into RES_ECR_ITEM(GROUPID,ECRNO,ITEMNO,ITEMDESPTION,FZRELATION,CHANGETYPE,BEFORECHANGE,AFTERCHANGE,PROPOSE,REMARK) values(:g,:p,:q,:w,:v,:r,:t,:y,:u,:o)');
Parameters.ParamByName('g').Value:=g;
Parameters.ParamByName('p').Value:=a;
Parameters.ParamByName('q').Value:=q;
Parameters.ParamByName('w').Value:=w;
Parameters.ParamByName('v').Value:=v;
Parameters.ParamByName('r').Value:=r;
Parameters.ParamByName('t').Value:=t;
Parameters.ParamByName('y').Value:=y;
Parameters.ParamByName('u').Value:=u;
Parameters.ParamByName('o').Value:=o;
ExecSQL;
ProgressBar1.Position:=ProgressBar1.Position+1;
end;
end;
Screen.Cursor:=crDefault;
Application.ProcessMessages;
ProgressBar1.Position:=0;
RefreshECRN;
RefreshGroupItem;
SendToEmail;
Zt:=1;
end;
finally
ExcelApp.ActiveWorkBook.Saved:=True;
WorkBook.Close;
ExcelApp.Quit;
end;
end;
end
else
begin
MessageDlg('請選擇要導入的Excel檔案!',mtWarning,[mbOK],0);
Exit;
end;
end;
uses cxGridExportLink;
procedure TRes_Hr_BkSh_F.btnSB_PrintClick(Sender: TObject);
begin
inherited;
if qry_approvebk.IsEmpty then
begin
MessageBox(Handle,'數據不能為空!!!','數據導出...',MB_OK+ MB_ICONWARNING);
Exit;
end else
begin
if dlgSave1.Execute then
begin
ExportGridToExcel(dlgSave1.FileName,cxGrid1,True,True,False,'XLS');
MessageBox(Handle,'數據成功導出!!!','數據導出...',MB_OK+ MB_ICONWARNING);
end;
end;
end;
那你這表格控件中取值吧???參考一下以下的單元格取值代碼,或許對你有幫助:procedure TRES_HR_CQAUDIT_F.cxGrid1DBTableView1CellClick(
Sender: TcxCustomGridTableView;
ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
AShift: TShiftState; var AHandled: Boolean);
var
xx,yy,zz,jj,ff,aa:Variant;
begin
inherited;
xx:=ACellViewInfo.Value;
yy:=cxGrid1DBTableView1.Controller.FocusedColumn.VisibleCaption;
zz:=cxGrid1DBTableView1.Controller.FocusedRow.Values[0];
jj:=RightStr(yy,2);
ff:=cxGrid1DBTableView1.Controller.FocusedColumnIndex;
aa:=FormatDateTime('yyyy-mm',cxDateEdit1.Date)+'-'+jj;
if StrToInt(Trim(ff))=0 then
begin
MessageDlg('提示:不要選擇“部門名稱”的列標題,請點擊選擇擇人數!',mtWarning,[mbOK],0);
Exit;
end else
begin
qry_psjbdetail.Close;
qry_psjbdetail.SQL.Clear;
qry_psjbdetail.SQL.Add( ' select '''+ FormatDateTime('mm',cxDateEdit1.Date)+''' as YF,c.COLRQ,''>''+'''+ Trim(dxSpinEdit1.Text)+'''+''Hr'' as TJ,c.CB,c.BM,c.KB,c.XB,c.COLAID,c.Full_Name,c.COLJBT from ' );
qry_psjbdetail.SQL.Add( ' (select a.COLRQ,b.CB,b.BM,b.KB,b.XB,a.COLAID,b.Full_Name,a.COLJBT from HR_EMPLOYEE_DAY a ' );
qry_psjbdetail.SQL.Add( ' left join Res_Hrd_Temp b on a.COLAID=b.Account_Id ' );
qry_psjbdetail.SQL.Add( ' where a.COLRQ='''+ Trim(aa)+''' and a.COLJBT>'''+ Trim(dxSpinEdit1.Text)+'''and b.BM='''+ Trim(zz)+''' ) c ' );
qry_psjbdetail.Open;
end;
end;
其实大大的代码......我是没怎么看懂的,,,如果我要excel里面某一行某一列的数据,不用全部读取的,那该怎么样.导出的话,可不可以说详细点呢谢谢大大,如果有教学视频更好
begin
Screen.Cursor:=crSQLWait;
WorkBook.WorkSheets[i].Activate;
a:=ExcelApp.Cells[4,2].Value;//這裡就是取的excel指定的單元格數據
b:=ExcelApp.Cells[8,2].Value;
c:=ExcelApp.Cells[14,2].Value;
d:=ExcelApp.Cells[21,2].Value;
e:=ExcelApp.Cells[22,2].Value;
f:=ExcelApp.Cells[23,2].Value;
if ((Copy(Trim(a),1,3)<>'ECR') and (b<>'')) and ((Copy(Trim(a),1,3)<>'DCR') and (b<>'')) then
begin
MessageDlg('檔案格式錯誤!',mtWarning,[mbOK],0);
InsertErrorLOG;
Screen.Cursor:=crDefault;
Exit;
end;
..........
这些类似Cells的属性,其实都是微软的接口对象,
频繁调用,其效率可想而知。