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;