如何读写并操作Excel文件，得到所需Excel格式？
我有一个Excel文件，共17列，其中各列的单元格中都可能有如“ACTCW CWACT CLIP”的内容。我想通过Delphi控制该Excel表，将所有形如上述单元格的内容改成“ACTCW&CWACT&CLIP”。
注：上述单元格中的内容为举例，内容不一定相同，相同的只是格式。
谢谢！
[解决办法]
这玩意遍历单元格，然后处理，想弄成什么样都可以啊。
通过Cells.Item[X,Y]去访问
[解决办法]
exclplctn_test: TExcelApplication;
exclwrksht1: TExcelWorksheet;
exclwrkbk1: TExcelWorkbook;
var
vrow, vcol: Integer;
i, j: Integer;
f: string;
vvalue: string;
begin
if OpenDialog1.Execute then
f := OpenDialog1.FileName
else
f := ' ';
if f = ' ' then
Exit;
try
exclplctn_test.Connect;
exclplctn_test.Visible[0] := False;
exclplctn_test.Workbooks.Open(f, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0);
exclwrkbk1.ConnectTo(exclplctn_test.Workbooks[1]);
exclwrksht1.ConnectTo(exclwrkbk1.Worksheets[1] as _Worksheet);
vrow := exclwrksht1.UsedRange[1].Rows.count;
vcol := exclwrksht1.UsedRange[1].Columns.Count;
for i := 2 to vrow do
for j := 1 to vcol do
begin
vvalue := exclwrksht1.Cells.Item[i, j];
if (vvalue = 'ACTCW ') or (vvalue = 'CWACT ') or (vvalue = 'CLIP ') then
exclwrksht1.Cells.Item[i, j] := 'ACTCW&CWACT&CLIP ';
Continue;
end;
except
Application.MessageBox( '读取Excel文件失败 ', '提示 ', 64);
exclwrksht1.Disconnect;
exclwrkbk1.Disconnect;
exclplctn_test.Workbooks[1].Close(True, 0, 0, 0);
exclplctn_test.Disconnect;
exit;
end;
exclwrksht1.Disconnect;
exclwrkbk1.Disconnect;
exclplctn_test.Workbooks[1].Close(True, 0, 0, 0);
exclplctn_test.Disconnect;
end;
[解决办法]
这是别人给我的
具体组件代码如下：
unit PrintSQL_Data;
interface
uses
SysUtils, Classes,Messages, Variants, Graphics, Controls, Forms,
Dialogs,windows,ADODB,ComObj,Excel2000,RzPrgres;
type
TPrintSQL_Data = class(TComponent)
private
{ Private declarations }
FDataSet:TADODataSet;
FActive:Boolean;
FSQLString:string;
FExcelTitle:TCaption;
FPrinting:Boolean;
FExcelFileName:string;
procedure SetSQLString(Value:string);
procedure SetExcelTitle(Value:TCaption);
procedure SetExcelFileName(Value:string);
procedure SetActive(Value:Boolean);
procedure SetPrinting(Value:Boolean);
procedure SetDataSet(Value:TADODataSet);
//打印
Function GetExcelCoulmnCaption(num:Cardinal):string;
protected
{ Protected declarations }
public
{ Public declarations }
Constructor Create(AOwner:TComponent); Override;
Destructor Destroy; override;
procedure PrintSqlDataToExcel; overload;
procedure PrintSqlDataToExcel(PB:TRzProgressBar); overload;
published
{ Published declarations }
property SQLString:string
read FSQLString
write SetSQLString;
property DataSet:TADODataSet
read FDataSet
write SetDataSet;
property ExcelTitle:TCaption
read FExcelTitle
write SetExcelTitle;
property ExcelFileName:String
read FExcelFileName
write SetExcelFileName;
property Active:boolean
read FActive
write SetActive;
property Printing:boolean
read FPrinting
write SetPrinting;
end;
procedure Register;
implementation
procedure Register;
begin
RegisterComponents( 'rocxu ', [TPrintSQL_Data]);
end;
{ TPrintSQL_Data }
constructor TPrintSQL_Data.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
end;
destructor TPrintSQL_Data.Destroy;
begin
inherited;
end;
function TPrintSQL_Data.GetExcelCoulmnCaption(num: Cardinal): string;
var
mod_num,div_num:Cardinal;
begin
if num=0 then exit;
if (num mod 26=0) then mod_num:=26
else mod_num:=num mod 26;
div_num:=num div 26;
if mod_num=26 then DEC(div_num);
if div_num=0 then
Result:=Chr(64+mod_num)
else Result:=Chr(64+div_num)+Chr(64+mod_num);
end;
procedure TPrintSQL_Data.PrintSqlDataToExcel;
var
I:integer;
Range,ExcelApp,V:variant;
begin
Try
ExcelApp:=CreateOleObject( 'Excel.application ');
Except
MessageDlg( '没有安装Office 办公软件Excel! ',mtinformation,[MBOK],0);
exit;
End;
try
ExcelApp.WorkBooks.add(Null);
V:=ExcelApp.WorkBooks[1].WorkSheets[1];
//*开始设计标题*/
Range:=V.Range[ 'A1 ',GetExcelCoulmnCaption(DataSet.Fields.Count)+ '1 '];
Range.MergeCells:=true;
Range.RowHeight:=24;
Range.HoriZontalAlignMent:=xlCenter;
Range.VerticalAlignMent:=xlCenter;
Range.Font.Name:= '新宋体 ';
Range.Font.size:=16;
Range.Font.FontStyle:= '加粗 ';
Range.Value:=FExcelTitle;
Range.Borders.LineStyle:=xlContinuous; //边框
Range.Borders.Weight:=xlThin;
Range.Borders.ColorIndex:=xlAutomatic;
//显示标题
For i:=0 To DataSet.Fields.Count-1 Do
begin
Range:=V.Range[GetExcelCoulmnCaption(I+1)+ '2 ',GetExcelCoulmnCaption(I+1)+ '2 '];
Range.RowHeight:=24;
Range.HoriZontalAlignMent:=xlCenter;
Range.VerticalAlignMent:=xlCenter;
Range.Font.Name:= '新宋体 ';
Range.Font.size:=9;
Range.Font.FontStyle:= '加粗 ';
Range.Columns.AutoFit;
Range.Value:=DataSet.Fields[I].FieldName;
Range.Borders.LineStyle:=xlContinuous; //边框
Range.Borders.Weight:=xlThin;
Range.Borders.ColorIndex:=xlAutomatic;
end;
//显示内容
//set
Range:=V.Range[ 'A3 ',GetExcelCoulmnCaption(DataSet.FieldCount)+IntToStr(DataSet.recordcount+2)];
Range.NumberFormatLocal:= '@ ';
Range.RowHeight:=20;
Range.HoriZontalAlignMent:=xlCenter;
Range.VerticalAlignMent:=xlCenter;
Range.Borders.LineStyle:=xlContinuous; //边框
Range.Borders.Weight:=xlThin;
Range.Borders.ColorIndex:=xlAutomatic;
Range.Font.Name:= '新宋体 ';
Range.Font.size:=9;
Range.Columns.AutoFit;
DataSet.First;
While (Not DataSet.Eof) do
begin
For i:=0 To DataSet.Fields.Count-1 Do
begin
Range:=V.Range[GetExcelCoulmnCaption(I+1)+IntToStr(DataSet.RecNo+2),GetExcelCoulmnCaption(I+1)+IntToStr(DataSet.RecNo+2)];
if DataSet.Fields[I].IsNull then
Range.Value:= ' '
else
Range.Value:=DataSet.Fields[I].AsString;
Range.Borders.LineStyle:=xlContinuous; //边框
Range.Borders.Weight:=xlThin;
Range.Borders.ColorIndex:=xlAutomatic;
end;
DataSet.next;
end;
//显示Excel文档界面
ExcelApp.visible:=true;
V.Activate;
finally
//释放接口对象
ExcelApp:=unassigned;
V:= unassigned;
Range:=unassigned;
end;
end;