如何读写并操作Excel文件，得到所需Excel格式？

Question

我有一个Excel文件，共17列，其中各列的单元格中都可能有如“ACTCW    CWACT    CLIP”的内容。我想通过Delphi控制该Excel表，将所有形如上述单元格的内容改成“ACTCW&CWACT&CLIP”。注：上述单元格中的内容为举例，内容不一定相同，相同的只是格式。谢谢！

Answer 1

这玩意遍历单元格，然后处理，想弄成什么样都可以啊。
通过Cells.Item[X,Y]去访问

Answer 2

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;

Answer 3

这是别人给我的
具体组件代码如下：   
  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;