RDB2/OExcelWriter.pas - master - YakuninAV/Convertors

RDB2/OExcelWriter.pas
274 строки9 KB
YakuninAV
begin with gitflick
31 июл 2026, 15:21
31 июл 2026, 15:211dc0fc6
unit OExcelWriter;

{$I mormot.defines.inc}

interface

uses
  System.Sysutils,
  Vcl.Graphics,
  mormot.core.base, mormot.core.variants, mormot.core.json, mormot.core.text, mormot.core.data,
  mormot.core.os, mormot.core.unicode,
  DBDIntfs, DBDExcelInfs, DBDExcelOleClasses;

type

  TObjEstimate2Excel = class
  private
    FData: Variant;
    pOE: PDocVariantData;
    pMain: PDocVariantData;
    pVariables: PDocVariantData;
    pSignatures: PDocVariantData;
    pUserData: PDocVariantData;
    Excl: IDBDExcelV2;
    FNotifier: IDBDNotifierV1;
    TotalsNames: TRawUtf8DynArray;
    ItemsNames: TRawUtf8DynArray;
    LevelsCount: Integer;
    DataRow: Integer;
    ExcelRow: Integer;
  protected
    function FillItemRow(const RowData: PDocVariantData): Boolean;
    function FillSection(const SecData: PDocVariantData): Boolean;
    function FillWS(const WSData: PDocVariantData): Boolean;
    function WSEstimate(const WSData: PDocVariantData): Boolean;
    function WSSignatures(const WSData: PDocVariantData): Boolean;
    function WSVariables(const WSData: PDocVariantData): Boolean;
    function WSUserData(const WSData: PDocVariantData): Boolean;
  public
    constructor Create(Notifier: IDBDNotifierV1);
    destructor Destroy; override;
    function ToExcel(Data: Variant; const ExcelName: TFileName): Boolean;
  end;

implementation

{ TObjEstimate2Excel }

constructor TObjEstimate2Excel.Create(Notifier: IDBDNotifierV1);
begin
  inherited Create;
  FNotifier:= Notifier;
end;

destructor TObjEstimate2Excel.Destroy;
begin
  Excl := nil;
  inherited;
end;

function TObjEstimate2Excel.FillItemRow(const RowData: PDocVariantData): Boolean;
var i: Integer; pCol: PDocVariantData; v,t: Variant; r: RawUTF8; s: string; d: Double; pv,pd:PVariant;
begin
{$IFDEF DEBUG}
  Assert(RowData<>nil, 'RowData не может быть nil');
{$ENDIF}
  i:=0; Result := True;
  try
    for pv in RowData^.Items do begin
      if (TVarData(pv^).VType=DocVariantVType)then begin
        pCol:=_Safe(pv^);
        if pCol^.GetAsPVariant('Value',pd) then begin
          if VariantToDouble(pd^, d) then begin
            Excl.Writer.WriteDouble(ExcelRow,i+1, d);
          end else begin
            s := VariantToString(pd^);
            Excl.Writer.WriteString(ExcelRow,i+1, s);
          end;
          if pCol^.GetAsRawUtf8('Formula',r) and (r<>'') and (r[1]='=') then begin
            Excl.Writer.WriteComment(ExcelRow, i+1,Utf8ToString(r));
          end;
        end;
      end else if VariantToDouble(pv^, d) then begin
        Excl.Writer.WriteDouble(ExcelRow,i+1, d);
      end else begin
        s := VariantToString(pv^);
        Excl.Writer.WriteString(ExcelRow,i+1, s);
      end;
      Inc(i);
    end;
  except
    on E: Exception do begin
      FNotifier.Log(E);
      Result:=False;
    end;
  end;
  Inc(ExcelRow);
end;

function TObjEstimate2Excel.FillSection(const SecData: PDocVariantData): Boolean;
var i: Integer; pRows, pRow: PDocVariantData; s: string;
begin
{$IFDEF DEBUG}
  Assert(SecData<>nil, 'SecData не может быть nil');
{$ENDIF}
  i:=0; Result := True;
  s := SecData^.S['NAME'];
  Excl.Writer.WriteString(ExcelRow, 1, s);
  Excl.RangeColor(ExcelRow,0, ColorToRGB(clAqua));
  Inc(ExcelRow);
  // Данные раздела
  if SecData^.GetAsDocVariant('Items_0', pRows) and (pRows^.Kind = dvArray) then begin
    for i := 0 to pRows^.Count-1 do begin
      pRow:=pRows^._[i];
      Result := pRow^.Kind=dvArray;
      if not Result then Continue;
      Result := FillItemRow(pRow) and Result;
    end;
  end;
  // Итоги раздела
  if SecData^.GetAsDocVariant('Items_1', pRows) and (pRows^.Kind = dvArray) then begin
    for i := 0 to pRows^.Count-1 do begin
      pRow:=pRows^._[i];
      Result := pRow^.Kind=dvArray;
      if not Result then Continue;
      Result := FillItemRow(pRow) and Result;
    end;
  end;
  FNotifier.Log(dmkIteration,0,s);
end;

function TObjEstimate2Excel.FillWS(const WSData: PDocVariantData): Boolean;
var i,j: Integer; pFields, pLevel, pCol, pRows,pRow, pSecs,pSec: PDocVariantData; s: string;
begin
{$IFDEF DEBUG}
  Assert(WSData<>nil, 'WSData не может быть nil');
  Assert(WSData^.Kind=dvObject, 'WSData должен быть объектом');
{$ENDIF}
  ExcelRow:=1;
  if Assigned(FNotifier) then FNotifier.Log(dmkInformation, Excl.CurrWorkSheetIdx,
                                            '===== Начали формирование листа: ' + Excl.CurrWSName);
  Result := WSData^.GetAsDocVariant('Fields', pFields) and (pFields^.Kind=dvArray);
  if not Result then Exit;
  LevelsCount:=pFields^.Count;
  // названия полей
  for i := 0 to LevelsCount-1 do begin
    pLevel:=pFields^._[i];
    for j := 0 to pLevel^.Count-1 do begin
      pCol := pLevel^._[j];
      s:=pCol^.S['nm'];
      Excl.Writer.WriteString(ExcelRow,j+1, s);
    end;
    Excl.RangeColor(ExcelRow,0, ColorToRGB(clLtGray));
    Inc(ExcelRow);
  end;
  Inc(ExcelRow);
  // Данные вне разделов
  if WSData^.GetAsDocVariant('Items_0', pRows) and (pRows^.Kind = dvArray) then begin
    for i := 0 to pRows^.Count-1 do begin
      pRow:=pRows^._[i];
      Result := pRow^.Kind=dvArray;
      if not Result then Continue;
      Result := FillItemRow(pRow) and Result;
    end;
  end;
  //Смета
  if WSData^.GetAsDocVariant('Section',pSecs) and (pSecs^.Kind = dvArray) then begin
    for i := 0 to pSecs^.Count-1 do begin
      pSec:=pSecs._[i];
      Result := pSec^.Kind=dvObject;
      if not Result then Continue;
      Result := FillSection(pSec) and Result;
    end;
  end;
  // Итоги сметы
  if WSData^.GetAsDocVariant('Items_1', pRows) and (pRows^.Kind = dvArray) then begin
    for i := 0 to pRows^.Count-1 do begin
      pRow:=pRows^._[i];
      Result := pRow^.Kind=dvArray;
      if not Result then Continue;
      Result := FillItemRow(pRow) and Result;
    end;
  end;
  s := '===== Формирование листа ' + Excl.CurrWSName;
  if Result then s := s + ' успешно завершено'
            else s := s + ' завершено с ошибками';
  if Assigned(FNotifier) then FNotifier.Log(dmkInformation, Excl.CurrWorkSheetIdx, s);
end;

function TObjEstimate2Excel.ToExcel(Data: Variant; const ExcelName: TFileName): Boolean;
var p: PDocVariantData; r: RawUtf8; valid: Boolean;  i: Integer;
begin
  p := _Safe(FData);
  if p^.Count>0 then p^.Clear;
  p^.InitCopy(Data,[]);
  Result := p^.GetAsRawUtf8('rdr',r) and (r='TRDBReaderBase')
        and p^.GetAsDocVariant('cont', pOE) and (pOE^.Kind=dvObject)
        and pOE^.GetAsDocVariant('CONSOLMAIN', pMain) and (pMain^.Kind=dvObject)
        ;
  if Result then begin
    Excl := TDBDExcelOle.CreateNewFile(ExcelName, [owbVisibleAlwais, owbCloseOnExit], FNotifier);
    Result := WSEstimate(pMain)
          and pOE^.GetAsDocVariant('VARIABLES',pVariables) and (pVariables^.Kind=dvObject)
          and WSVariables(pVariables)
          and pOE^.GetAsDocVariant('FUNCTIONERS',pSignatures) and (pSignatures^.Kind=dvObject)
          and WSSignatures(pSignatures)
          and pOE^.GetAsDocVariant('USER_TABLE',pUserData) and (pUserData^.Kind=dvObject)
          and WSUserData(pUserData)
    ;
    Excl.Close;
  end;
end;

function TObjEstimate2Excel.WSEstimate(const WSData: PDocVariantData): Boolean;
var i: Integer;
const WSName: string = 'Смета';
begin
  if Excl.CountWS=0 then begin
    i := Excl.AddWorkSheet(WSName);
    Result := i>0;
  end else begin
    i := Excl.CurrWorkSheetIdx;
    Excl.CurrWSName := WSName;
    Result := (i=1);
  end;
  Result := Result and (Excl.OpenWorkSheet(WSName)=i) and FillWS(WSData);
end;

function TObjEstimate2Excel.WSSignatures(const WSData: PDocVariantData): Boolean;
var i: Integer;
const WSName: string = 'Подписанты';
begin
  if Excl.CountWS=2 then begin
    i := Excl.AddWorkSheet(WSName);
    Result := i>0;
  end else begin
    Result := Excl.NextWorkSheet;
    i := Excl.CurrWorkSheetIdx;
    Excl.CurrWSName := WSName;
  end;
  Result := Result and (Excl.OpenWorkSheet(WSName)=i) and FillWS(WSData);
end;

function TObjEstimate2Excel.WSUserData(const WSData: PDocVariantData): Boolean;
var i: Integer;
const WSName: string = 'Реквизиты';
begin
  if Excl.CountWS=3 then begin
    i := Excl.AddWorkSheet(WSName);
    Result := i>0;
  end else begin
    Result := Excl.NextWorkSheet;
    i := Excl.CurrWorkSheetIdx;
    Excl.CurrWSName := WSName;
  end;
  Result := Result and (Excl.OpenWorkSheet(WSName)=i) and FillWS(WSData);
end;

function TObjEstimate2Excel.WSVariables(const WSData: PDocVariantData): Boolean;
var i: Integer;
const WSName: string = 'Переменные';
begin
  if Excl.CountWS=1 then begin
    i := Excl.AddWorkSheet(WSName);
    Result := i>0;
  end else begin
    Result := Excl.NextWorkSheet;
    i := Excl.CurrWorkSheetIdx;
    Excl.CurrWSName:=WSName;
  end;
  Result := Result and (Excl.OpenWorkSheet(WSName)=i) and FillWS(WSData);
end;

end.