delphi 利用NativeExcel 实现 导入（返回clientdataset） 导出

1、先要安装控件\NativeExcel310（自行下载）

2、添加单元引用

   uses nexcel;

3、转换方法

function ExcelToDataSet(fileNmae:string):TClientDataSet;

var

od: TOpenDialog;

wb: IXLSWorkbook;

ws: IXLSWorksheet;

irow,J: integer;

v: Variant;

ClientDataSet1: TClientDataSet;

MaxCell: Integer;

begin

  wb := TXLSWorkbook.Create;

  wb.Open(fileNmae);

  ws := wb.WorkSheets[1];            // sheet1

  if ws.UsedRange.Rows.Count = 0 then

  Exit;

  if ClientDataSet1 <> nil then

    ClientDataSet1.Free;

  ClientDataSet1:= TClientDataSet.Create(nil);

  MaxCell:= 0;

  for irow := 1 to ws.UsedRange.Cells.Count - 1 do // 遍历标题

  begin

    v := ws.UsedRange.Cells[1, irow].Value;

    if VarIsNull(v) then

       Break;

    if not VarIsNull(v) then

     begin

        MaxCell:= MaxCell + 1;

        ClientDataSet1.FieldDefs.Add(v,ftstring,90 );

     end;

  end;

  ClientDataSet1.CreateDataSet; //建立

  ClientDataSet1.Active;

  ClientDataSet1.Edit;

  for J := 2 to ws.UsedRange.Rows.Count   do

  begin

//      for irow := 1 to ws.UsedRange.Cells.Count - 1 do // 遍历标题

    for irow := 1 to MaxCell -1 do // 遍历标题

    begin

      v := VarToStr(ws.UsedRange.Cells[J, irow].Value);

//        if not VarIsNull(v) then

      begin

        //根据列名Post数据

         ClientDataSet1.FieldByName(ws.UsedRange.Cells[1, irow].Value).AsString:= v;

      end;

    end;

      ClientDataSet1.Append;

  end;

  Result := ClientDataSet1;

  wb.Close;

end;

4、导出

procedure TForm2.Button3Click(Sender: TObject);

var

xls: TDataset2Excel;

sd: TSaveDialog;

begin

  //export

  if ClientDataSet1 = nil then

    Exit;

  sd := TSaveDialog.Create(nil);

  sd.Filter := 'xls|*.xls';

  sd.FileName := '1.xls';

  if sd.Execute then

  begin

    xls := TDataset2Excel.Create(nil);

    xls.Dataset := ClientDataSet1;

    xls.WorksheetName := '1';

    xls.SaveDatasetAs(sd.FileName);

    xls.Workbook := nil;

    xls.Free;

  end;

  sd.Free;

end;