Excel導出問題

Question

現在要從數據庫中到出數據到Excel,其中有CP_NO欄位有型如(1234 123456 00)或(1234 1234)的數據要求導出后將其中的空格去掉并且如果后面兩位是零的也必須去掉﹐沒有0的則不用。該如何實現啊??

Answer 1

从dbgrid倒成excel 得代码：你可以拿来修改一下，增加“去掉末两位的零”的功能，看看能不能达到你的要求procedure Tfmcx2.DbgridSaveToExcel(dbgrid:TDBgrid);
var
    XlAPP:Variant;
    Sheet1:Variant;
    i,j:integer;
    curRow:integer;begin
    if dbgrid.DataSource.DataSet.RecordCount<1 then exit;
    //?建excel?象
    try
        XlApp:=createoleobject('Excel.Application');
        XLApp.Visible:=false;
        XLApp.Workbooks.Add(xlWBatWorkSheet);
        Sheet1 := XLApp.Workbooks[1].WorkSheets['sheet1'];       //   Sheet1.Columns[1].NumberFormat:='@'; //////////設計某列為文本類型// XLApp.Workbooks.Options.CheckSpellingAsYouType:= False;
       // XLApp.Workbooks.Options.CheckGrammarAsYouType:= False;
    except
    on e:exception do
    begin       showmessage('excel程序出錯，無法完成此功能!');
        exit;
        end;
    end;    curRow:=0;
    for j:=0 to dbgrid.FieldCount-1 do
    begin        sheet1.cells[1,curRow+1]:=dbgrid.Columns[j].Title.Caption;
        inc(curRow);
    end;
    //?理??    dbgrid.DataSource.DataSet.First;
    i:=2;
    while not dbgrid.DataSource.DataSet.Eof do
    begin
       //?理一行
       curRow:=0;
       for j:=0 to dbgrid.Columns.Count-1 do
       begin                          Sheet1.cells[i,curRow+1]:=Trim(dbgrid.Fields[j].DisplayText) ;           inc(curRow);       end;
       i:=i+1;
       dbgrid.DataSource.DataSet.Next;
    end;
    XLApp.Visible:=true;
end;

Answer 2

幫我看看代碼哪里出錯了?﹕(
procedure TForm1.Button1Click(Sender: TObject);
var
  i,row,column:Integer;
begin
  try
    try
      ExcelApplication1.Connect;
    except
      ExcelApplication1.Disconnect;
      ShowMessage('確認你是否安裝了Excel!');
    end;
    ExcelApplication1.Visible[0]:=True;
    ExcelApplication1.Caption:='Excel Application1';
    ExcelApplication1.Workbooks.Add(null,0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
    MSQuery1.Open;
    row:=1;
    column:=1;
    for i:=0 to MSQuery1.FieldCount-1 do
    begin
      ExcelWorksheet1.Cells.Item[row,column]:=MSQuery1.Fields[i].DisplayLabel;
      Inc(column);
    end;    MSQuery1.First;
    row:=1;
    for i:=0 to MSQuery1.RecordCount-1 do
    begin
      if MSQuery1.Fields[i].DisplayLabel='cp_name' then
      begin
        ExcelWorksheet1.Cells.Item[row,1]:=MSQuery1.fieldbyname('cp_name').AsString;
        //showmessage(ExcelWorksheet1.Cells.Item[row,1]);
        ExcelWorksheet1.Cells.Item[row,2]:=MSQuery1.fieldbyname('cp_no').AsString;
        //showmessage(ExcelWorksheet1.Cells.Item[row,2]);
        Inc(row);
      end else
      if MSQuery1.Fields[i].DisplayLabel='cp_no' then
      begin
        case Length(MSQuery1.fieldbyname('cp_no').AsString) of
        12:begin
            ExcelWorksheet1.Cells.Item[row,2]:=Copy(Trim(Msquery1.fieldbyname'cp_no').AsString),1,8);
            Inc(row);
          end;
       9: begin
            ExcelWorksheet1.Cells.Item[row,2]:=trim(MSQuery1.fieldbyname('cp_no').AsString);
            Inc(row);
          end;
       end;
     end;
     msquery1.next; 
  end;
  finally
    ExcelApplication1.Quit;
  end;
end;

Answer 3

幫我看看代碼哪里出錯了?﹕(應該是這樣的
procedure TForm1.Button1Click(Sender: TObject);
var
  i,row,column:Integer;
begin
  try
    try
      ExcelApplication1.Connect;
    except
      ExcelApplication1.Disconnect;
      ShowMessage('確認你是否安裝了Excel!');
    end;
    ExcelApplication1.Visible[0]:=True;
    ExcelApplication1.Caption:='Excel Application1';
    ExcelApplication1.Workbooks.Add(null,0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
    MSQuery1.Open;
    row:=1;
    column:=1;
    for i:=0 to MSQuery1.FieldCount-1 do
    begin
      ExcelWorksheet1.Cells.Item[row,column]:=MSQuery1.Fields[i].DisplayLabel;
      Inc(column);
    end;    MSQuery1.First;
    row:=1;
    for i:=0 to MSQuery1.RecordCount-1 do
    begin
      if MSQuery1.Fields[i].DisplayLabel='cp_name' then
      begin
        ExcelWorksheet1.Cells.Item[row,1]:=MSQuery1.fieldbyname('cp_name').AsString;
        //showmessage(ExcelWorksheet1.Cells.Item[row,1]);
        Inc(row);
      end else
      if MSQuery1.Fields[i].DisplayLabel='cp_no' then
      begin
        case Length(MSQuery1.fieldbyname('cp_no').AsString) of
        12:begin
            ExcelWorksheet1.Cells.Item[row,2]:=Copy(Trim(Msquery1.fieldbyname'cp_no').AsString),1,8);
            Inc(row);
          end;
       9: begin
            ExcelWorksheet1.Cells.Item[row,2]:=trim(MSQuery1.fieldbyname('cp_no').AsString);
            Inc(row);
          end;
       end;
     end;
     msquery1.next; 
  end;
  finally
    ExcelApplication1.Quit;
  end;
end;