nativeexcel导入导出

nativeexcel导入导出

// cxg 2025-5-11 需要nativeexcel控件

unit core.ImpExp;

interface

uses
  SysUtils, db, Classes, Forms, Math, Variants, Dialogs,
  Dataset2Excel, nexcel;

procedure Import(ADataset: TDataSet; AMap: TStringList);
procedure ExportTo(ADataset: TDataSet);

implementation

procedure Import(ADataset: TDataSet; AMap: TStringList);
// AMap： excel的每一列都映射一个fieldname  键值对：1=field1
var
  LWorkSheet: IXLSWorksheet;
  LWorkBook: IXLSWorkBook;
  LExecutedCount, LExecuteCount, LRow, LCol: Integer;
begin
  var
    LOpenDialog: TOpenDialog := TOpenDialog.Create(nil);
  try
    LOpenDialog.Filter := 'Excel files(*.xlsx;*.xls)|*.xlsx;*.xls';
    if not LOpenDialog.Execute then
      Exit;
    LWorkBook := TXLSWorkbook.Create;
    LWorkBook.Open(LOpenDialog.FileName);
  finally
    LOpenDialog.Free;
  end;
  LWorkSheet := LWorkBook.WorkSheets[1];
  if LWorkSheet.UsedRange.Rows.Count = 0 then
    Exit; // 空表
  const
    LEachTime = 1000; // 每次导入1000
    // 执行次数=exce的行数 / 每次导入数量
  LExecuteCount := Ceil(LWorkSheet.UsedRange.Rows.Count / LEachTime);
    // 已执行次数
  LExecutedCount := 0;
  while LExecutedCount < LExecuteCount do // 依次导入
  begin
    var
      LCurrent: Integer;
    if LExecutedCount = 0 then
      LCurrent := LExecutedCount * LEachTime + 2
    else
      LCurrent := LExecutedCount * LEachTime;
    for LRow := LCurrent to (LExecutedCount + 1) * LEachTime do
    // 遍历excel的行
    begin
      for LCol := 0 to AMap.Count - 1 do // 遍历excel的列
      begin
        var
          LVariant: Variant := LWorkSheet.UsedRange.Cells
            [LRow, AMap.Names[LCol].ToInteger].Value; // 单元格的值
          // 跳过 null值，因为null值转为其它数据类型会异常
        if VarIsNull(LVariant) then
          Continue;
        ADataSet.Append;
        ADataSet.FieldByName(AMap.ValueFromIndex[LCol]).Text := LVariant;
        ADataSet.Post;
      end;
    end;
    Inc(LExecutedCount); // 累加已执行次数
  end;
end;

procedure ExportTo(ADataset: TDataSet);
var
  LDataset2Excel: TDataset2Excel;
  LSaveDialog: TSaveDialog;
begin
  if ADataset = nil then
    Exit;
  LSaveDialog := TSaveDialog.Create(nil);
  try
    LSaveDialog.FileName := '1.xlsx';
    LSaveDialog.Filter := 'Excel files(*.xlsx;*.xls)|*.xlsx;*.xls' +
      '|Html files (*.html;*.htm)|*.html;*.htm)' + '|Rtf files(*.rtf)|*.rtf';
    if not LSaveDialog.Execute then
      Exit;
    LDataset2Excel := TDataset2Excel.Create(nil);
    try
      LDataset2Excel.DataSet := ADataset;
      LDataset2Excel.WorksheetName := '1';
      LDataset2Excel.SaveDatasetAs(LSaveDialog.FileName);
      LDataset2Excel.Workbook := nil;
    finally
      LDataset2Excel.Free;
    end;
  finally
    LSaveDialog.Free;
  end;
end;

end.