unit QExport4Xlsx;

{$I QExport4VerCtrl.inc}

interface

uses
  {$IFDEF VCL16}
    System.Classes,
    Vcl.Graphics,
    Vcl.ExtCtrls,
    System.WideStrings,
  {$ELSE}
    Classes,
    Graphics,
    ExtCtrls,
    {$IFDEF VCL10}
      WideStrings,
    {$ELSE}
      QExport4EmsWideStrings,
    {$ENDIF}
  {$ENDIF}
  QExport4IniFiles, QExport4, QExport4BaseArchiveClass, QExport4Types,
    QXMLWriter, QExport4EmsWideStrUtils, QExport4MSOfficeCommon;

type
  TXlsxBorder = class(TPersistent)
  private
    FStyle: TXlsxBorderStyle;
    FColor: TColor;
  public
    constructor Create;
    procedure Assign(Source: TPersistent); override;
    procedure Default;
  published
    property Style: TXlsxBorderStyle read FStyle
      write FStyle default xbsThin;
    property Color: TColor read FColor write FColor
      default clBlack;
  end;

  TXlsxCellStyle = class;

  TXlsxStyleItem = class(TCollectionItem)
  private
    FOptions: TXlsxCellStyle;
    procedure SetOptions(const Value: TXlsxCellStyle);
  published
    constructor Create(Collection: TCollection); override;
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;
    property Options: TXlsxCellStyle read FOptions
      write SetOptions;
  end;

  TXlsxStyleList = class(TCollection)
  private
    FHolder: TPersistent;
  protected
    FCellXfsCount: Integer;
    FNumFmtsCount: Integer;
    FFontsCount: Integer;
    FFillsCount: Integer;
    FBordersCount: Integer;
    function GetItem(Index: Integer): TXlsxStyleItem;
    procedure SetItem(Index: Integer; Value: TXlsxStyleItem);
    function GetOwner: TPersistent; override;
  public
    constructor Create(Holder: TPersistent);
    function Add: TXlsxStyleItem;
    procedure AddList(Source: TPersistent);
    function CountByColName(AColName: string): Integer;
    function FindByColName(AColName: string): TXlsxStyleItem;
    function StyleIdByColName(AColName: string): Integer;
    function GetStyleByStyleID(const StyleID: Integer): TXlsxStyleItem;
    function GetStyleIDByStyle(const Style: TXlsxCellStyle): Integer;
    property Items[Index: Integer]: TXlsxStyleItem read GetItem
      write SetItem; default;
  end;

  // dee
  TXlsxStyleType = (xlsxstDefault, xlsxstHeader, xslxstCaption, xlsxstData, xlsxstFooter,
    xlsxstInteger, xlsxstFloat, xlsxstDate, xlsxstTime, xlsxstDateTime, xlsxstCurrency,
    xlsxstStripCol, xlsxstStripRow);

  TXlsxCellStyle = class(TPersistent)
  private
    FFont: TFont;
    FBorder: TXlsxBorder;
    FBackgroundColor: TColor;
    FAlignment: TMSCellAlignment;
    FVerticalAligment: TMSCellVerticalAligment;
    FWrapText: boolean;
    FNumericFormat: string;
    FStyleID: Integer; // for StyleList only
    FColName: string;
    FNumFmtId: Integer;
    FFontId: Integer;
    FFillId: Integer;
    FBorderId: Integer;
    FUseBackground: Boolean;
    FUseBorder: Boolean;
    function GetIsDefault: Boolean;
    procedure SetDefault(const Value: Boolean);
    procedure SetFont(const Value: TFont);
    procedure SetBorder(const Value: TXlsxBorder);
    procedure SetNumericFormat(const Value: string);
  public
    constructor Create;
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;

    procedure LoadFromIni(IniFile: TQIniFile; const Section: WideString);
    procedure SaveToIni(IniFile: TQIniFile; const Section: WideString);
    procedure Default; virtual;
    property IsDefault: Boolean read GetIsDefault write SetDefault;

    // TODO to published
    property NumericFormat: string read FNumericFormat write SetNumericFormat;
    property StyleID: Integer read FStyleID write FStyleID;
    //\

  published
    property Font: TFont read FFont write SetFont;

    property BackgroundColor: TColor read FBackgroundColor
      write FBackgroundColor default clWhite;
    property UseBackground: boolean read FUseBackground
      write FUseBackground default False;

    property Alignment: TMSCellAlignment read FAlignment
      write FAlignment default caLeft;
    property VerticalAligment: TMSCellVerticalAligment read FVerticalAligment
      write FVerticalAligment default cvaBottom;
    property WrapText: boolean read FWrapText
      write FWrapText default False;

    property UseBorder: boolean read FUseBorder
      write FUseBorder default False;
    property Border: TXlsxBorder read FBorder write SetBorder;
  end;

  TQExport4XlsxOptions = class(TPersistent)
  private
    FHolder: TPersistent;
    FHeaderStyle: TXlsxCellStyle;
    FFooterStyle: TXlsxCellStyle;
    FCaptionRowStyle: TXlsxCellStyle;
    FDataStyle: TXlsxCellStyle;
    FStripStyle: TMSStripStyleType;
    FStripStylesList: TXlsxStyleList;
    FAutoCalcColWidth: Boolean;
    procedure SetHeaderStyle(const Value: TXlsxCellStyle);
    procedure SetCaptionStyle(const Value: TXlsxCellStyle);
    procedure SetDataStyle(const Value: TXlsxCellStyle);
    procedure SetStripStyles(const Value: TXlsxStyleList);
    procedure SetFooterStyle(const Value: TXlsxCellStyle);
  protected
     function GetOwner: TPersistent; override;
  public
    constructor Create(Holder: TPersistent);
    destructor Destroy; override;
    procedure Assign(Source: TPersistent); override;
  published
    property AutoCalcColWidth: Boolean read FAutoCalcColWidth
      write FAutoCalcColWidth default False;
    property HeaderStyle: TXlsxCellStyle read FHeaderStyle
      write SetHeaderStyle;
    property CaptionRowStyle: TXlsxCellStyle read FCaptionRowStyle
      write SetCaptionStyle;
    property DataStyle: TXlsxCellStyle read FDataStyle
      write SetDataStyle;
    property FooterStyle: TXlsxCellStyle read FFooterStyle
      write SetFooterStyle;
    property StripStyleType: TMSStripStyleType read FStripStyle
      write FStripStyle default ssNone;
    property StripStylesList: TXlsxStyleList read FStripStylesList
      write SetStripStyles;
  end;

  TXlsxFileMaker = class
  private
    FTempSharedListFileName: String;
    FSharedListWriter: TQXMLWriter;
    FSharedListStream: TStream;
    FSharedListIndex: Integer;
    FExportStream: TStream;
    FExportWriter: TQXMLWriter;
    FXlsxOptions: TQExport4XlsxOptions;
    FWorkbookFormats: TQExportFormats;
    FStyleList,
    FCustomStyleList: TXlsxStyleList;
    FSizeHeader: Int64;

    FZipper: TBaseArchiveClass;
    FTempDir: WideString;
    FSheetName: WideString;
    FExportFileName: WideString;
    FRightToLeftExcelMode: Boolean;
    function GetSharedString(const Value: WideString; CellType: TQExportColType):
        Integer;
    procedure SetSheetName(const Value: WideString);
    procedure PrepareStyleList;
    procedure CreateStyles;
    procedure CreateSharedList;
  protected
    property CustomStyleList: TXlsxStyleList read FCustomStyleList;
  public
    constructor Create(const ExportFile: WideString;
      ExportOptions: TQExport4XlsxOptions; WorkbookFormats: TQExportFormats);
    destructor Destroy; override;

    procedure CreateDirsStructure;
    function IsExistDirsStructure: Boolean;
    procedure FillCommonData;
    procedure CompressFile;
    procedure AddCell(Col, Row, ColCount: Integer; Value: WideString;
      CellType: TQExportColType; CellStyle: Integer = -1);
    procedure PrepareExport(const UseAutoCalColWidth: Boolean);
    procedure FinishExport;
    procedure WriteColsWidth(const ColsWidth: TQExportColumns);

    property SheetName: WideString read FSheetName write SetSheetName;
    property ExportFileName: WideString read FExportFileName;
    property RightToLeftExcelMode: Boolean read FRightToLeftExcelMode write FRightToLeftExcelMode;
  end;

  TGetDataParamsEvent = procedure(Sender: TObject; const Col, Row: integer;
    var Style: TXlsxCellStyle; var UseStyle: Boolean) of object;

  TQExport4Xlsx = class(TQExport4FormatText)
  private
    FCounter: Integer;
    FDataRowCounter: Integer;
    FXlsxFile: TXlsxFileMaker;
    FXlsxOptions: TQExport4XlsxOptions;
    FSheetName: WideString;
    FAutoTruncateValue: Boolean;
    FOnGetDataParams: TGetDataParamsEvent;
    FRightToLeftExcelMode: Boolean;
    procedure SetXlsxOptions(const Value: TQExport4XlsxOptions);
    procedure SetSheetName(const Value: WideString);
  private
    procedure WriteHeader;
    procedure WriteFooter;
  protected
    procedure BeginExport; override;
    procedure BeforeExport; override;
    procedure AfterExport; override;
    procedure EndExport; override;

    procedure WriteCaptionRow; override;
    procedure WriteDataRow; override;
    procedure CalcColsWidth(const Value: string;
      const XlsxStyleId, ColIndex: Integer);
    procedure SaveProperties(IniFile: TQIniFile); override;
    procedure LoadProperties(IniFile: TQIniFile); override;
    procedure CheckFormats; override;    
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure Execute; override;
    property RightToLeftExcelMode: Boolean read FRightToLeftExcelMode write FRightToLeftExcelMode
      default False;
  published
    property AllowCaptions;
    property AutoTruncateValue: Boolean read FAutoTruncateValue write FAutoTruncateValue default False;
    property Captions;
    property Formats;
    property UserFormats;
    property AutoCalcColWidth;
    property ColumnsWidth;
    property SheetName: WideString read FSheetName write SetSheetName;
    property XlsxOptions: TQExport4XlsxOptions read FXlsxOptions write SetXlsxOptions;
    property OnGetDataParams: TGetDataParamsEvent read FOnGetDataParams write FOnGetDataParams;
  end;

procedure DrawXlsxCell(PaintBox: TPaintBox; CellStyle: TXlsxCellStyle);

implementation

uses
  {$IFDEF VCL16}
    System.SysUtils,
    System.StrUtils,
    {$WARNINGS OFF}
      Vcl.FileCtrl,
    {$WARNINGS ON}
    System.Variants,
    {$IFDEF VCL21}
    System.NetEncoding,
    {$ELSE}
    Soap.EncdDecd,
    {$ENDIF}
    Winapi.Windows,
    Data.DB,
  {$ELSE}
    SysUtils,
    DB,
    {$IFDEF VCL6}
      EncdDecd,
      Variants,
      StrUtils,
    {$ENDIF}
    {$IFDEF VER130}
      FileCtrl,
    {$ENDIF}
    Windows,
  {$ENDIF}
  {$IFDEF VCL17}
    System.UITypes,
  {$ENDIF}
  QExport4Common;

const
  xlsxDefaultStylesCount = 5; // 1 "default" + 4 "Header, CaptionRow, Data, Footer"
  xlsxStyle_Default = 0;
  xlsxStyle_Header = 1;
  xlsxStyle_Footer = 2;
  xlsxStyle_Caption = 3;
  xlsxStyle_Data = 4;
  xlsxCustomNumericFormat = 164; // first custom format
  xlsxColWidthZeroStr   = '0';
  xlsxColWidthZero      = 0;

var
  xlsxDefaultFont: TFont;

  function Font2String(AFont: TFont): string;
  begin
    Result := AFont.Name
      + '|' + IntToStr(AFont.Size)
      + '|' + IntToStr(AFont.Charset)
      + '|' + ColorToHex(AFont.Color);
    if fsBold in AFont.Style then Result := Result + '|' + 'b';
    if fsItalic in AFont.Style then Result := Result + '|' + 'i';
    if fsUnderline in AFont.Style then Result := Result + '|' + 'u';
  end;
  function Border2String(ABorder: TXlsxBorder): string;
  begin
    Result := IntToStr( Integer(ABorder.Style))
      +'|'+ ColorToHex(ABorder.Color);
  end;
  function CellXfs2String(AStyle: TXlsxCellStyle; AFull: Boolean = true): string;
  begin
    Result := IntToStr(AStyle.FNumFmtId)
    + '|' + IntToStr(AStyle.FFontId)
    + '|' + IntToStr(AStyle.FFillId)
    + '|' + IntToStr(AStyle.FBorderId);
    if AStyle.Alignment <> caLeft then Result := Result + '|' + 'h';
    if AStyle.VerticalAligment <> cvaBottom then Result := Result + '|' + 'v';
    if AStyle.WrapText then Result := Result + '|' + 'w';
    if AFull then
      Result := Result + '=' + IntToStr(AStyle.FStyleID);
  end;

  function Col2Letter(AColumn: Integer): string;
  const
    LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  var
    thirdLetter, secondLetter, firstLetter, col: Integer;
  begin
    col := AColumn - 1;
    Result := EmptyStr;

    thirdLetter := (col + 26 * (col div 702)) div 702; // [A] A A
    secondLetter := col div 26 - 26 * thirdLetter;     // A [A] A
    firstLetter := col mod 26;                         // A A [A]
    if thirdLetter > 0 then Result := Result + Copy(LETTERS, thirdLetter, 1);
    if secondLetter > 0 then Result := Result + Copy(LETTERS, secondLetter, 1);
    Result := Result + Copy(LETTERS, firstLetter + 1, 1);
  end;

procedure DrawXlsxCell(PaintBox: TPaintBox; CellStyle: TXlsxCellStyle);

  procedure FillBackground;
  begin
    if CellStyle.UseBackground then
      PaintBox.Canvas.Brush.Color := CellStyle.BackgroundColor
    else
      PaintBox.Canvas.Brush.Color := clWhite;
    PaintBox.Canvas.FillRect(Rect(0, 0, PaintBox.Width, PaintBox.Height));
  end;

const
  OutText = 'Aa Zz';
var
  X, Y: integer;
begin
  if not Assigned(CellStyle) then Exit;
  
  FillBackground;
  with PaintBox.Canvas.Font do
  begin
    Name := CellStyle.Font.Name;
    Size := CellStyle.Font.Size;
    Charset := CellStyle.Font.Charset;
    Color := CellStyle.Font.Color;
    Style := CellStyle.Font.Style;
  end;

  case CellStyle.Alignment of
    caLeft: X := 5;
    caCenter: X := (PaintBox.Width - PaintBox.Canvas.TextWidth(OutText)) div 2;
    caRight: X := PaintBox.Width - 5 - PaintBox.Canvas.TextWidth(OutText);
    else
      X := 0;
  end;

  case CellStyle.VerticalAligment of
    cvaTop: Y := 5;
    cvaMiddle: Y := (PaintBox.Height - PaintBox.Canvas.TextHeight(OutText)) div 2;
    cvaBottom: Y := PaintBox.Height - 5 - PaintBox.Canvas.TextHeight(OutText);
    else
      Y := 0;
  end;
  PaintBox.Canvas.TextOut(X, Y, OutText);
end;

{ TXlsxBorder }

constructor TXlsxBorder.Create;
begin
  Default;
end;

procedure TXlsxBorder.Assign(Source: TPersistent);
begin
  if Source is TXlsxBorder then
  begin
    Style := (Source as TXlsxBorder).Style;
    Color := (Source as TXlsxBorder).Color;
  end;
end;

procedure TXlsxBorder.Default;
begin
  FStyle := xbsThin;
  FColor := clBlack;
end;

{ TXlsxStyleItem }

procedure TXlsxStyleItem.SetOptions(const Value: TXlsxCellStyle);
begin
  FOptions.Assign(Value);
end;

constructor TXlsxStyleItem.Create(Collection: TCollection);
begin
  inherited Create(Collection);
  FOptions := TXlsxCellStyle.Create;
end;

destructor TXlsxStyleItem.Destroy;
begin
  FOptions.Free;
  inherited Destroy;
end;

procedure TXlsxStyleItem.Assign(Source: TPersistent);
begin
  FOptions.Assign(TXlsxStyleItem(Source).Options);
end;

{ TXlsxStyleList }

constructor TXlsxStyleList.Create(Holder: TPersistent);
begin
  FHolder := Holder;
  inherited Create(TXlsxStyleItem);
  FNumFmtsCount := 0;
  FFontsCount := 0;
  FFillsCount := 0;
  FBordersCount := 0;
end;

function TXlsxStyleList.GetItem(Index: Integer): TXlsxStyleItem;
begin
  Result := TXlsxStyleItem(inherited Items[Index]);
end;

function TXlsxStyleList.GetOwner: TPersistent;
begin
  Result := FHolder;
end;

procedure TXlsxStyleList.SetItem(Index: Integer;
  Value: TXlsxStyleItem);
begin
  inherited Items[Index] := Value;
end;

function TXlsxStyleList.Add: TXlsxStyleItem;
begin
  Result := TXlsxStyleItem(inherited Add)
end;

function TXlsxStyleList.CountByColName(AColName: string): Integer;
var
  I: Integer;
begin
  Result := 0;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName,AColName) = 0 then
      Inc(Result);
end;

function TXlsxStyleList.FindByColName(AColName: string): TXlsxStyleItem;
var
  I: Integer;
begin
  Result := nil;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName,AColName) = 0 then
    begin
      Result := Items[i];
      Exit;
    end;
end;

function TXlsxStyleList.StyleIdByColName(AColName: string): Integer;
var
  I: Integer;
begin
  Result := -1;
  for I := 0 to Count - 1 do
    if QECompareStr(Items[I].Options.FColName, AColName) = 0 then
    begin
      Result := Items[I].Options.StyleID;
      Exit;
    end;
end;

function TXlsxStyleList.GetStyleByStyleID(const StyleID: Integer): TXlsxStyleItem;
var
  i: Integer;
begin
  Result := nil;
  for i := 0 to Count - 1 do
    if StyleID = Items[i].Options.StyleID then
    begin
      Result := Items[i];
      Break;
    end;  
end;  

function TXlsxStyleList.GetStyleIDByStyle(const Style: TXlsxCellStyle): Integer;
var
  I: Integer;
  StyleName,
  PresentStyleName: qeString;
begin
  Result := -1;
  for i := 0 to Count - 1 do
  begin
    StyleName := CellXfs2String(Style, False) + '|' +
      Border2String(Style.Border) + '|' +
      Font2String(Style.Font) + '|' +
      ColorToHex(Style.BackgroundColor)
      {$IFDEF VCL6}
       + '|' +
      BoolToStr(Style.UseBackground)
      {$ENDIF};

    PresentStyleName := CellXfs2String(Items[I].Options, False) + '|' +
      Border2String(Items[I].Options.Border) + '|' +
      Font2String(Items[I].Options.Font) + '|' +
      ColorToHex(Items[I].Options.BackgroundColor)
      {$IFDEF VCL6}
       + '|' +
      BoolToStr(Style.UseBackground)
      {$ENDIF};
    if PresentStyleName = StyleName then
    begin
      Result := Items[I].Options.StyleID;
      Break;
    end;
  end;
end;

procedure TXlsxStyleList.AddList(Source: TPersistent);
var
  I: Integer;
begin
  if Source is TCollection then
  begin
    BeginUpdate;
    try
      for I := 0 to TCollection(Source).Count - 1 do
        Add.Assign(TCollection(Source).Items[I]);
    finally
      EndUpdate;
    end;
    Exit;
  end;
end;

{ TXlsxCellStyle }

function TXlsxCellStyle.GetIsDefault: Boolean;
begin
  Result := (FFont.Name = xlsxDefaultFont.Name)
    and (FFont.Size = xlsxDefaultFont.Size)
    and (FFont.Style = xlsxDefaultFont.Style)
    and (FFont.Color = xlsxDefaultFont.Color)
    and (FBackgroundColor = clWhite)
    and (FUseBackground = False)
    and (FAlignment = caLeft)
    and (FVerticalAligment = cvaBottom)
    and (not FWrapText)
    and (FNumericFormat = '');
end;

procedure TXlsxCellStyle.SetDefault(const Value: Boolean);
begin
  if Value then
    Default;
end;

procedure TXlsxCellStyle.SetFont(const Value: TFont);
begin
  FFont.Assign(Value);
end;

procedure TXlsxCellStyle.SetNumericFormat(const Value: string);
begin
  FNumericFormat := Value;
end;

procedure TXlsxCellStyle.SetBorder(const Value: TXlsxBorder);
begin
  FBorder.Assign(Value);
end;

constructor TXlsxCellStyle.Create;
begin
  FFont := TFont.Create;
  FBorder := TXlsxBorder.Create;
  Default;
end;

destructor TXlsxCellStyle.Destroy;
begin
  FBorder.Free;
  FFont.Free;
  inherited;
end;

procedure TXlsxCellStyle.Assign(Source: TPersistent);
begin
  if Source is TXlsxCellStyle then
  begin
    Font := TXlsxCellStyle(Source).Font;
    UseBackground := TXlsxCellStyle(Source).UseBackground;
    BackgroundColor := TXlsxCellStyle(Source).BackgroundColor;
    Alignment := TXlsxCellStyle(Source).Alignment;
    VerticalAligment := TXlsxCellStyle(Source).VerticalAligment;
    WrapText := TXlsxCellStyle(Source).WrapText;
    UseBorder := TXlsxCellStyle(Source).UseBorder;
    Border.Assign(TXlsxCellStyle(Source).Border);
    NumericFormat := TXlsxCellStyle(Source).NumericFormat;

    FStyleID := TXlsxCellStyle(Source).FStyleID;
    FNumFmtId := TXlsxCellStyle(Source).FNumFmtId;
    FFontId := TXlsxCellStyle(Source).FFontId;
    FFillId := TXlsxCellStyle(Source).FFillId;
    FBorderId := TXlsxCellStyle(Source).FBorderId;
  end;
end;

procedure TXlsxCellStyle.LoadFromIni(IniFile: TQIniFile;
  const Section: WideString);
begin
  with IniFile do
  begin
    Font.Name := ReadString(Section, S_XLSX_FontName, xlsxDefaultFont.Name);
    Font.Size := ReadInteger(Section, S_XLSX_FontSize, xlsxDefaultFont.Size);
    Font.Color := ReadInteger(Section, S_XLSX_FontColor, xlsxDefaultFont.Color);
    if ReadBool(Section, S_XLSX_FontBold, False) then
      Font.Style := Font.Style + [fsBold]
    else
      Font.Style := Font.Style - [fsBold];
    if ReadBool(Section, S_XLSX_FontItalic, False) then
      Font.Style := Font.Style + [fsItalic]
    else
      Font.Style := Font.Style - [fsItalic];
    if ReadBool(Section, S_XLSX_FontUnderline, False) then
      Font.Style := Font.Style + [fsUnderline]
    else
      Font.Style := Font.Style - [fsUnderline];
    UseBackground := ReadBool(Section, S_XLSX_UseBackground, False);
    BackgroundColor := ReadInteger(Section, S_XLSX_BackgroundColor, clBlack);
    Alignment := TMSCellAlignment(ReadInteger(Section, S_XLSX_HorAlignment, 0));
    VerticalAligment := TMSCellVerticalAligment(ReadInteger(Section, S_XLSX_VertAlignment, 0));
    UseBorder := ReadBool(Section, S_XLSX_UseBorder, False);
    Border.Style := TXlsxBorderStyle(ReadInteger(Section, S_XLSX_BorderStyle, 0));
    Border.Color := ReadInteger(Section, S_XLSX_BorderColor, clBlack);
    NumericFormat := ReadString(Section, S_XLSX_NumericFormat, '');
  end;
end;

procedure TXlsxCellStyle.SaveToIni(IniFile: TQIniFile;
  const Section: WideString);
begin
  with IniFile do
  begin
    WriteString(Section, S_XLSX_FontName, Font.Name);
    WriteInteger(Section, S_XLSX_FontSize, Font.Size);
    WriteInteger(Section, S_XLSX_FontColor, Font.Color);
    if fsBold in Font.Style then
      WriteBool(Section, S_XLSX_FontBold, true)
    else
      WriteBool(Section, S_XLSX_FontBold, false);
    if fsItalic in Font.Style then
      WriteBool(Section, S_XLSX_FontItalic, true)
    else
      WriteBool(Section, S_XLSX_FontItalic, false);
    if fsUnderline in Font.Style then
      WriteBool(Section, S_XLSX_FontUnderline, true)
    else
      WriteBool(Section, S_XLSX_FontUnderline, false);
    WriteBool(Section, S_XLSX_UseBackground, UseBackground);
    WriteInteger(Section, S_XLSX_BackgroundColor, BackgroundColor);
    WriteInteger(Section, S_XLSX_HorAlignment, Integer(Alignment));
    WriteInteger(Section, S_XLSX_VertAlignment, Integer(VerticalAligment));
    WriteBool(Section, S_XLSX_UseBorder, UseBorder);
    WriteInteger(Section, S_XLSX_BorderStyle, Integer(Border.Style));
    WriteInteger(Section, S_XLSX_BorderColor, Border.Color);
    WriteString(Section, S_XLSX_NumericFormat, NumericFormat);
  end;
end;

procedure TXlsxCellStyle.Default;
begin
  FStyleID := -1;  // for StyleList only
  FNumFmtId := -1;
  FFontId := -1;
  FFillId := -1;
  FBorderId := -1;

  FFont.Name := xlsxDefaultFont.Name;
  FFont.Size := xlsxDefaultFont.Size;
  FFont.Style := xlsxDefaultFont.Style;
  FFont.Color := xlsxDefaultFont.Color;
  FBackgroundColor := clWhite;
  FUseBackground := False;
  FAlignment := caLeft;
  FVerticalAligment := cvaBottom;
  FWrapText := False;
  FUseBorder := False;
  FBorder.Default;
  FNumericFormat := '';
end;

{ TQExport4XlsxOptions }

procedure TQExport4XlsxOptions.SetHeaderStyle(const Value: TXlsxCellStyle);
begin
  FHeaderStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetCaptionStyle(
  const Value: TXlsxCellStyle);
begin
  FCaptionRowStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetDataStyle(const Value: TXlsxCellStyle);
begin
  FDataStyle.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetStripStyles(
  const Value: TXlsxStyleList);
begin
  FStripStylesList.Assign(Value);
end;

procedure TQExport4XlsxOptions.SetFooterStyle(const Value: TXlsxCellStyle);
begin
  FFooterStyle.Assign(Value);
end;


constructor TQExport4XlsxOptions.Create(Holder: TPersistent);
begin
  FHolder := Holder;
  inherited Create;
  FHeaderStyle := TXlsxCellStyle.Create;
  FCaptionRowStyle := TXlsxCellStyle.Create;
  FDataStyle := TXlsxCellStyle.Create;
  FFooterStyle := TXlsxCellStyle.Create;

  FStripStyle := ssNone;
  FStripStylesList := TXlsxStyleList.Create(Self);

  FAutoCalcColWidth := False;
end;

destructor TQExport4XlsxOptions.Destroy;
begin
  FHolder := nil;
  FHeaderStyle.Free;
  FCaptionRowStyle.Free;
  FDataStyle.Free;
  FStripStylesList.Free;
  FFooterStyle.Free;
  inherited;
end;

function TQExport4XlsxOptions.GetOwner: TPersistent;
begin
  Result := FHolder;
end;

procedure TQExport4XlsxOptions.Assign(Source: TPersistent);
begin
  if Source is TQExport4XlsxOptions then
  begin
    HeaderStyle := TQExport4XlsxOptions(Source).HeaderStyle;
    CaptionRowStyle := TQExport4XlsxOptions(Source).CaptionRowStyle;
    DataStyle := TQExport4XlsxOptions(Source).DataStyle;
    FooterStyle := TQExport4XlsxOptions(Source).FooterStyle;
    StripStyleType := TQExport4XlsxOptions(Source).StripStyleType;
    StripStylesList := TQExport4XlsxOptions(Source).StripStylesList;
  end;
end;

{ TXlsxMaker }

constructor TXlsxFileMaker.Create(const ExportFile: WideString;
  ExportOptions: TQExport4XlsxOptions; WorkbookFormats: TQExportFormats);
begin
  FExportFileName := ExportFile;
  FTempDir := GetTempDir;
  FSheetName := 'sheet1';
  FXlsxOptions := ExportOptions;
  FWorkbookFormats := WorkbookFormats;
  FStyleList := TXlsxStyleList.Create(nil);
  FCustomStyleList := TXlsxStyleList.Create(nil);
  FSharedListIndex := -1;
end;

destructor TXlsxFileMaker.Destroy;
begin
  FStyleList.Free;
  FCustomStyleList.Free;
  inherited;
end;

function TXlsxFileMaker.GetSharedString(const Value: WideString; CellType:
    TQExportColType): Integer;
begin
  FSharedListWriter.BeginNode('si');
  if CellType = ectUnknown then
    FSharedListWriter.CreateFullNode('t', ['dt:dt', 'xml:space'], ['bin.base64', 'preserve'], Value)
  else
    FSharedListWriter.CreateFullNode('t', ['xml:space'], ['preserve'], Value);
  FSharedListWriter.EndNode;
  FSharedListIndex := FSharedListIndex + 1;
  Result := FSharedListIndex;
end;

procedure TXlsxFileMaker.SetSheetName(const Value: WideString);
begin
  if Value <> '' then
    FSheetName := Value;
end;

procedure TXlsxFileMaker.CreateDirsStructure;
begin
  ForceDirectories(FTempDir);
  ForceDirectories(FTempDir + '\xl');
  ForceDirectories(FTempDir + '\xl\_rels');
  ForceDirectories(FTempDir + '\xl\theme');
  ForceDirectories(FTempDir + '\xl\worksheets');
  ForceDirectories(FTempDir + '\docProps');
  ForceDirectories(FTempDir + '\_rels');
end;

function TXlsxFileMaker.IsExistDirsStructure: Boolean;
begin
  Result := DirectoryExists(FTempDir)
    and DirectoryExists(FTempDir + '\xl')
    and DirectoryExists(FTempDir + '\xl\_rels')
    and DirectoryExists(FTempDir + '\xl\theme')
    and DirectoryExists(FTempDir + '\xl\worksheets')
    and DirectoryExists(FTempDir + '\docProps')
    and DirectoryExists(FTempDir + '\_rels');
end;

procedure TXlsxFileMaker.FillCommonData;
var
  Stream: TStream;
begin
  //FTempDir + '\_rels\.rels'
  Stream := TFileStream.Create(FTempDir + '\_rels\.rels', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Relationships', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/relationships']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId3', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties', 'docProps/app.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId2', 'http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties', 'docProps/core.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId1', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument', 'xl/workbook.xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\docProps\app.xml'
  Stream := TFileStream.Create(FTempDir + '\docProps\app.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Properties', ['xmlns', 'xmlns:vt'], ['http://schemas.openxmlformats.org/officeDocument/2006/extended-properties', 'http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes']);
        CreateFullNode('Application', 'Microsoft Excel');
        CreateFullNode('DocSecurity', '0');
        CreateFullNode('ScaleCrop', 'false');
        BeginNode('HeadingPairs');
        BeginNode('vt:vector', ['size', 'baseType'], ['2', 'variant']);
        BeginNode('vt:variant');
        CreateFullNode('vt:lpstr', 'Worksheets');
        EndNode();
        BeginNode('vt:variant');
        CreateFullNode('vt:i4', '1');
        EndNode();
        EndNode();
        EndNode();
        BeginNode('TitlesOfParts');
        BeginNode('vt:vector', ['size', 'baseType'], ['1', 'lpstr']);
        CreateFullNode('vt:lpstr', FSheetName);
        EndNode();
        EndNode();
        CreateFullNode('Company');
        CreateFullNode('LinksUpToDate', 'false');
        CreateFullNode('SharedDoc', 'false');
        CreateFullNode('HyperlinksChanged', 'false');
        CreateFullNode('AppVersion', '12.0000');
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\docProps\core.xml'
  Stream := TFileStream.Create(FTempDir + '\docProps\core.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('cp:coreProperties', ['xmlns:cp', 'xmlns:dc', 'xmlns:dcterms', 'xmlns:dcmitype', 'xmlns:xsi'],
          ['http://schemas.openxmlformats.org/package/2006/metadata/core-properties', 'http://purl.org/dc/elements/1.1/',
          'http://purl.org/dc/terms/', 'http://purl.org/dc/dcmitype/', 'http://www.w3.org/2001/XMLSchema-instance']);
        CreateFullNode('dc:creator', 'User');
        CreateFullNode('cp:lastModifiedBy', 'User');
        CreateFullNode('dcterms:created', ['xsi:type'], ['dcterms:W3CDTF'], '2006-10-02T04:59:59Z');
        CreateFullNode('dcterms:modified', ['xsi:type'], ['dcterms:W3CDTF'], '2006-10-02T05:00:35Z');
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\xl\_rels\workbook.xml.rels'
  Stream := TFileStream.Create(FTempDir + '\xl\_rels\workbook.xml.rels', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Relationships', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/relationships']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId3', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles', 'styles.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId1', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet', 'worksheets/' + FSheetName + '.xml']);
        CreateFullNode('Relationship', ['Id', 'Type', 'Target'], ['rId4', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings', 'sharedStrings.xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\xl\worksheets\' + FSheetName + '.xml'
  //DO it into BeginExport procedure
  
  //FTempDir + '\xl\styles.xml'

//  if Assigned(FXlsxOptions) then // dee
//    CreateStyles;
   PrepareStyleList;
  //FTempDir + '\xl\workbook.xml'
  Stream := TFileStream.Create(FTempDir + '\xl\workbook.xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('workbook', ['xmlns', 'xmlns:r'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships']);
        CreateFullNode('fileVersion ', ['lastEdited', 'lowestEdited', 'rupBuild'], ['4', '4', '4017']);
        CreateFullNode('workbookPr', ['defaultThemeVersion'], ['123820']);
        BeginNode('bookViews');
        CreateFullNode('workbookView', ['xWindow', 'yWindow', 'windowWidth', 'windowHeight'], ['120', '105', '14175', '7365']);
        EndNode();
        BeginNode('sheets');
        CreateFullNode('sheet', ['name', 'sheetId', 'r:id'], [FSheetName, '1', 'rId1']);
        EndNode();
        CreateFullNode('calcPr', ['calcId'], ['122211']);
        CreateFullNode('webPublishing', ['codePage'], ['1251']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;

  //FTempDir + '\[Content_Types].xml'
  Stream := TFileStream.Create(FTempDir + '\[Content_Types].xml', fmCreate);
  try
    with TQXMLWriter.Create(Stream) do
      try
        CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
        BeginNode('Types', ['xmlns'], ['http://schemas.openxmlformats.org/package/2006/content-types']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/theme/theme1.xml', 'application/vnd.openxmlformats-officedocument.theme+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/styles.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml']);
        CreateFullNode('Default', ['Extension', 'ContentType'], ['rels', 'application/vnd.openxmlformats-package.relationships+xml']);
        CreateFullNode('Default', ['Extension', 'ContentType'], ['xml', 'application/xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/workbook.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/docProps/app.xml', 'application/vnd.openxmlformats-officedocument.extended-properties+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/worksheets/' + FSheetName + '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/xl/sharedStrings.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml']);
        CreateFullNode('Override', ['PartName', 'ContentType'], ['/docProps/core.xml', 'application/vnd.openxmlformats-package.core-properties+xml']);
        EndNode();
      finally
        Free;
      end;
  finally
    Stream.Free;
  end;
end;

procedure TXlsxFileMaker.CompressFile;
begin
  FZipper := TBaseArchiveClass.Create;
  try
    FZipper.TempDir := FTempDir;
    FZipper.FileName := ExportFileName;
    FZipper.Compress;
  finally
    FZipper.Free;
  end;
end;

procedure TXlsxFileMaker.AddCell(Col, Row, ColCount: Integer; Value: WideString;
  CellType: TQExportColType; CellStyle: Integer = -1);
begin
  with FExportWriter do
  begin
    if Col = 0 then
      BeginNode('row', ['r', 'spans'], [IntToStr(Row), '1:'+IntToStr(ColCount)]);

    if (CellType in [ectString, ectBoolean, ectUnknown]) or (Value = 'null') then
    begin
      if CellStyle > -1 then
        BeginNode('c', ['r', 's', 't'], [Col2Letter(Col + 1) + IntToStr(Row), IntToStr(CellStyle), 's'])
      else
        BeginNode('c', ['r', 't'], [Col2Letter(Col + 1) + IntToStr(Row), 's']);

      if Value <> '' then
      begin
        if CellType <> ectUnknown then
          CreateFullNode('v', IntToStr(GetSharedString(ReplaceSymbols(Value, True), CellType)))
        else
          CreateFullNode('v', IntToStr(GetSharedString(Value, CellType)))
      end
      else
        CreateFullNode('v');

    end
    else
    begin
      if CellStyle > -1 then
        BeginNode('c', ['r', 's'], [Col2Letter(Col + 1) + IntToStr(Row), IntToStr(CellStyle)])
      else
        BeginNode('c', ['r'], [Col2Letter(Col + 1) + IntToStr(Row)]);

      if Value <> '' then
      begin
        if CellType in [ectFloat, ectNumeric, ectCurrency, ectDateTime, ectDate, ectTime] then
          Value := CorrectExcelDecimalSepInValue(Value);
        CreateFullNode('v', Value)
      end
      else
        CreateFullNode('v');

    end;
    EndNode(); // c

    if Col = (ColCount-1) then
      EndNode(); // row
  end;
end;

procedure TXlsxFileMaker.PrepareExport(const UseAutoCalColWidth: Boolean);
var
  RightToLeftFlag: Char;
begin
  //FTempDir + '\xl\worksheets\' + FSheetName + '.xml'
  FExportStream := TFileStream.Create(FTempDir + '\xl\worksheets\' +
    FSheetName + '.xml', fmCreate);

  if UseAutoCalColWidth then
  begin
    FExportStream.Free;

    FExportStream := TFileStream.Create(FTempDir + '\xl\worksheets\' +
      FSheetName + '.tmp', fmCreate)
  end;

  FTempSharedListFileName := FTempDir + '\xl\sharedStrings.tmp';
  FSharedListStream := TFileStream.Create(FTempSharedListFileName, fmCreate);
  FSharedListWriter := TQXMLWriter.Create(FSharedListStream);

  FExportWriter := TQXMLWriter.Create(FExportStream);
  FExportWriter.CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
  FExportWriter.BeginNode('worksheet', ['xmlns', 'xmlns:r'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships']);
  FExportWriter.CreateFullNode('dimension', ['ref'], ['A1']);
  FExportWriter.BeginNode('sheetViews');
  if RightToLeftExcelMode then
    RightToLeftFlag := '1'
  else
    RightToLeftFlag := '0';
  FExportWriter.BeginNode('sheetView', ['rightToLeft','tabSelected',
    'workbookViewId'], [RightToLeftFlag, '1', '0']);
  FExportWriter.CreateFullNode('selection');
  FExportWriter.EndNode();
  FExportWriter.EndNode();
  FExportWriter.CreateFullNode('sheetFormatPr', ['defaultRowHeight'], ['15']);

  FSizeHeader := FExportStream.Size;

  FExportWriter.BeginNode('sheetData');
end;

procedure TXlsxFileMaker.FinishExport;
begin
  CreateStyles;

  CreateSharedList;

  //FTempDir + '\xl\worksheets\' + FSheetName +'.xml'
  with FExportWriter do
  begin
    EndNode(); //sheetData
    CreateFullNode('printOptions');
    CreateFullNode('pageMargins', ['left', 'right', 'top', 'bottom', 'header', 'footer'], ['0.7', '0.7', '0.75', '0.75', '0.3', '0.3']);
    CreateFullNode('headerFooter');
    EndNode(); // worksheet
  end;

  FExportWriter.Free;
  FExportStream.Free;
  if Assigned(FSharedListWriter) then
    FSharedListWriter.Free;
  if Assigned(FSharedListStream) then
  begin
    FSharedListStream.Free;
    DeleteFile(PChar(FTempSharedListFileName));
  end;
end;

procedure TXlsxFileMaker.WriteColsWidth(const ColsWidth: TQExportColumns);
//use only with AutoCalcColWidth = True
  var
    i: Integer;
begin
  FExportWriter.Stream := TFileStream.Create(FTempDir + '\xl\worksheets\' +
    FSheetName + '.xml', fmCreate or fmOpenWrite);
  try
    FExportStream.Position := 0;
    FExportWriter.Stream.CopyFrom(FExportStream, FSizeHeader);

    FExportWriter.BeginNode('cols');

    for i := 0 to ColsWidth.Count - 1 do
      FExportWriter.CreateFullNode('col', ['min', 'max', 'width'],
        [IntToStr(i + 1), IntToStr(i + 1), IntToStr(ColsWidth[i].Width)]);

    FExportWriter.EndNode;

    FExportWriter.Stream.CopyFrom(FExportStream, FExportStream.Size - FSizeHeader);
  finally
    FExportStream.Free;
    {$IFDEF VCL16}System.SysUtils{$ELSE}SysUtils{$ENDIF}.DeleteFile(FTempDir + '\xl\worksheets\' + FSheetName + '.tmp');
    FExportStream := FExportWriter.Stream;
  end;
end;

procedure TXlsxFileMaker.PrepareStyleList;

  procedure GetColumnFormat(ACol: TQExportColumn;
    var AFormat: string; var AFmtID: Integer);
  begin
    AFormat := ACol.Format;
    // dee: need to verify a match, the format of an Excel column
    //
//      case ACol.ColType of
//        ectInteger, ectBigint:
//          if QECompareStr(AFormat, FWorkbookFormats.IntegerFormat) = 0 then
//            AFmtID := 1;
//        ectFloat:
//          if QECompareStr(AFormat, FWorkbookFormats.FloatFormat) = 0 then
//            AFmtID := 2;
//        ectDate:
//          if QECompareStr(AFormat, FWorkbookFormats.DateFormat) = 0 then
//            AFmtID := 14;
//        ectTime:
//          if QECompareStr(AFormat, FWorkbookFormats.TimeFormat) = 0 then
//            AFmtID := 21;
//        ectDateTime:
//          if QECompareStr(AFormat, FWorkbookFormats.DateTimeFormat) = 0 then
//            AFmtID := 22;
//        ectCurrency:
//          if QECompareStr(AFormat, FWorkbookFormats.CurrencyFormat) = 0 then
//              AFmtID := 44;
//      end;
  end;

  function NumFmts2String(AStyle: TXlsxCellStyle; AFull: Boolean = true): string;
  begin
    Result := AStyle.NumericFormat;
    if AFull then Result := Result + '=' + IntToStr(AStyle.FNumFmtId);
  end;

  procedure AddItem(AStyle:TXlsxCellStyle; AColumn:TQExportColumn);
  var
    Item: TXlsxStyleItem;
  begin
    Item := FStyleList.Add;
    Item.Options.Assign(AStyle);

    case AColumn.ColType of
      ectFloat,
      ectCurrency,
      ectNumeric,
      ectInteger,
      ectBigint:
        Item.Options.FNumericFormat := AColumn.Format;
      ectDate,
      ectTime,
      ectDateTime:
        Item.Options.FNumericFormat := AColumn.Format;
    else
      Item.Options.FNumericFormat := AColumn.Format;
    end;
    Item.Options.FColName := AColumn.Name;
  end;

var
  I, J: Integer;
  StrList: TStrings;
begin
  FStyleList.Clear;
  // Default 0
  FStyleList.Add.Options.Font := xlsxDefaultFont;
  // Header 1, Footer 2, CaptionRow 3, DataRow 4
  FStyleList.Add.Options.Assign(FXlsxOptions.HeaderStyle);
  FStyleList.Add.Options.Assign(FXlsxOptions.FooterStyle);
  FStyleList.Add.Options.Assign(FXlsxOptions.CaptionRowStyle);
  FStyleList.Add.Options.Assign(FXlsxOptions.DataStyle);

  if (FXlsxOptions.StripStylesList.Count > 0)
    and (FXlsxOptions.StripStyleType <> ssNone) then
        if FXlsxOptions.StripStyleType = ssColumn then
          for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
          begin
            AddItem(
              FXlsxOptions.StripStylesList[i mod FXlsxOptions.StripStylesList.Count].Options,
              TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
            );
          end
        else
          for J := 0 to FXlsxOptions.StripStylesList.Count - 1 do
            for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
              begin
                AddItem(
                  FXlsxOptions.StripStylesList[J].Options,
                  TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
                );
              end
  else
    for I := 0 to TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns.Count - 1 do
      if not (TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i].ColType
        in [ectString, ectBoolean, ectUnknown]) then
      begin
          AddItem(
            FXlsxOptions.FDataStyle,
            TQExport4Xlsx(FXlsxOptions.FHolder).ExportRow.Columns[i]
          );
      end;

  //this cycle must be stay after append DataStyle
  for I := 0 to CustomStyleList.Count - 1 do
  begin
    FStyleList.Add.Options.Assign(CustomStyleList[I].Options);
    Inc(FStyleList.FCellXfsCount)
  end;

  StrList := TStringList.Create;
  try
    // Fonts
    FStyleList.FFontsCount := 0;
    for I := 0 to FStyleList.Count - 1 do
    begin
      j := StrList.IndexOf( Font2String(FStyleList[i].Options.Font) );
      if j > -1 then
        FStyleList[i].Options.FFontId := j
      else
      begin
        FStyleList[i].Options.FFontId := FStyleList.FFontsCount;
        StrList.Add( Font2String(FStyleList[i].Options.Font ));
        Inc(FStyleList.FFontsCount);
      end;
    end;
    // Fills
    StrList.Clear;
    FStyleList.FFillsCount := 1; // 2 default value 'none' and 'gray125'
    for I := 0 to FStyleList.Count - 1 do
    begin
      j := StrList.IndexOf( ColorToHex(FStyleList[i].Options.BackgroundColor ));
      if j > -1 then
      begin
        FStyleList[i].Options.FFillId := j;
        if j > 0 then
          Inc( FStyleList[i].Options.FFillId);
      end
      else
      begin
        FStyleList[i].Options.FFillId := FStyleList.FFillsCount;
        StrList.Add( ColorToHex(FStyleList[i].Options.BackgroundColor ));
        Inc(FStyleList.FFillsCount);
      end;
    end;
    // Borders
    StrList.Clear;
    FStyleList.FBordersCount := 0;
    for I := 0 to FStyleList.Count - 1 do
    begin
      j := StrList.IndexOf( Border2String(FStyleList[i].Options.Border ));
      if j > -1 then
        FStyleList[i].Options.FBorderId := j
      else
      begin
        FStyleList[i].Options.FBorderId := FStyleList.FBordersCount;
        StrList.Add( Border2String(FStyleList[i].Options.Border ));
        Inc(FStyleList.FBordersCount);
      end;
    end;
    // numFmts
    StrList.Clear;
    FStyleList.FNumFmtsCount := 0;
    for I := 0 to FStyleList.Count - 1 do
    begin
      if (FStyleList[i].Options.NumericFormat <> '') or (FStyleList[i].Options.FColName <> '')  then
      begin
        if (FStyleList[i].Options.FNumFmtId = -1) then
        begin
          j := StrList.IndexOfName( NumFmts2String(FStyleList[i].Options, false ));
          if j > -1 then
          begin
            FStyleList[i].Options.FNumFmtId :=
              StrToInt( StrList.Values[ NumFmts2String(FStyleList[i].Options,false )] );
          end
          else
          begin
            FStyleList[i].Options.FNumFmtId := FStyleList.FNumFmtsCount + xlsxCustomNumericFormat;
            Inc(FStyleList.FNumFmtsCount);
            StrList.Add( NumFmts2String(FStyleList[i].Options ));
          end;
        end;
      end else
        FStyleList[i].Options.FNumFmtId := 0;
    end;

    // Styles
    StrList.Clear;
    FStyleList[xlsxStyle_Default].Options.FStyleID := xlsxStyle_Default;
    StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Default].Options ));
    FStyleList[xlsxStyle_Header].Options.FStyleID := xlsxStyle_Header;
    StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Header].Options ));
    FStyleList[xlsxStyle_Footer].Options.FStyleID := xlsxStyle_Footer;
    StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Footer].Options ));
    FStyleList[xlsxStyle_Caption].Options.FStyleID := xlsxStyle_Caption;
    StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Caption].Options ));
    FStyleList[xlsxStyle_Data].Options.FStyleID := xlsxStyle_Data;
    StrList.Add( CellXfs2String( FStyleList[xlsxStyle_Data].Options ));
    FStyleList.FCellXfsCount := xlsxDefaultStylesCount;
    for I := xlsxDefaultStylesCount to FStyleList.Count - 1 do
    begin
      j := StrList.IndexOfName( CellXfs2String( FStyleList[I].Options, False ));
      if j > -1 then
        FStyleList[i].Options.FStyleID := j
      else
      begin
        FStyleList[i].Options.FStyleID := FStyleList.FCellXfsCount;
        StrList.Add( CellXfs2String( FStyleList[i].Options ));
        Inc(FStyleList.FCellXfsCount);
      end;
    end;

  finally
    StrList.Free;
  end;

end;

procedure TXlsxFileMaker.CreateSharedList;
var
  Stream: TStream;
  XMLWriter: TQXMLWriter;
begin
  Stream := TFileStream.Create(FTempDir + '\xl\sharedStrings.xml', fmCreate);

  try
    XMLWriter := TQXMLWriter.Create(Stream);
    try
      XMLWriter.CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
      XMLWriter.BeginNode('sst', ['xmlns', 'xmlns:dt', 'count', 'uniqueCount'],
        ['http://schemas.openxmlformats.org/spreadsheetml/2006/main',
        'uuid:C2F41010-65B3-11d1-A29F-00AA00C14882',
        IntToStr(FSharedListIndex + 1), IntToStr(FSharedListIndex + 1)]);
      XMLWriter.Write('>');
      XMLWriter.NodeStack.EmptyElementTag := False;
      FSharedListStream.Position := 0;
      Stream.CopyFrom(FSharedListStream, FSharedListStream.Size);

      XMLWriter.EndNode;
    finally
      XMLWriter.Free;
    end;
  finally
    Stream.Free;
  end;
end;

procedure TXlsxFileMaker.CreateStyles;
var
  Stream: TStream;
  Writer: TQXMLWriter;
  i,k: Integer;

  procedure SaveFill(UseDefault: Boolean; Color: TColor = clWhite);
  begin
    if UseDefault then
    begin
      Writer.BeginNode('fill');
      Writer.CreateFullNode('patternFill', ['patternType'], ['none']);
      Writer.EndNode();
    end else begin
      Writer.BeginNode('fill');
      Writer.BeginNode('patternFill', ['patternType'], ['solid']);
      Writer.CreateFullNode('fgColor', ['rgb'], [ColorToHex(Color)]);
      Writer.EndNode();
      Writer.EndNode();
    end;
  end;

  procedure CreateBorder(Default: Boolean; Style: TXlsxBorderStyle = xbsThin; Color: TColor = clBlack);
  const
    Borders: array[1..5] of WideString =
      ('left', 'right', 'top', 'bottom', 'diagonal');
  var
    i: Integer;
  begin
    if Default then
    begin
      Writer.BeginNode('border');
      for i := 1 to 5 do
        Writer.CreateFullNode(Borders[i]);
      Writer.EndNode();
    end else
    begin
      Writer.BeginNode('border');
      for i := 1 to 4 do
      begin
        Writer.BeginNode(Borders[i]);
        Writer.AddAttr('style', MSBorderStyleToStr(Style));
        if Color <> clBlack then
          Writer.CreateFullNode('color', ['rgb'], [ColorToHex(Color)])
        else
          Writer.CreateFullNode('color', ['indexed'], ['64']);
        Writer.EndNode();
      end;
      Writer.CreateFullNode(Borders[5]);
      Writer.EndNode();
    end;
  end;

  procedure SaveFont(AFont: TFont);
  begin
    Writer.BeginNode('font');
    if fsBold in AFont.Style then Writer.CreateFullNode('b');
    if fsItalic in AFont.Style then Writer.CreateFullNode('i');
    if fsUnderline in AFont.Style then Writer.CreateFullNode('u');
    Writer.CreateFullNode('sz', ['val'], [IntToStr(AFont.Size)]);
    Writer.CreateFullNode('color', ['rgb'], [ColorToHex(AFont.Color)]);
    Writer.CreateFullNode('name', ['val'], [AFont.Name]);
    Writer.CreateFullNode('charset', ['val'], [IntToStr(AFont.Charset)]);
    Writer.CreateFullNode('family', ['val'], ['2']);
    //pav
    //Writer.CreateFullNode('scheme', ['val'], ['minor']);
    //\pav fix CR30598
    Writer.EndNode();
  end;

  procedure SaveXF( Style: TXlsxCellStyle ); 
  begin
    Writer.BeginNode('xf');
    Writer.AddAttr('numFmtId', IntToStr(Style.FNumFmtId));
    Writer.AddAttr('fontId', IntToStr(Style.FFontId));
    Writer.AddAttr('fillId', IntToStr(Style.FFillId));
    Writer.AddAttr('borderId', IntToStr(Style.FBorderId));
    Writer.AddAttr('xfId','0');
    if Style.FNumFmtId > 0 then
      Writer.AddAttr('applyNumberFormat', '1');
    Writer.AddAttr('applyFont', '1');
    Writer.AddAttr('applyFill', '1');
    Writer.AddAttr('applyBorder', '1');
    if (Style.Alignment <> caLeft)
      or (Style.VerticalAligment <> cvaBottom)
      or Style.WrapText
    then
    begin
      Writer.AddAttr('applyAlignment','1');
      Writer.BeginNode('alignment');
      if Style.Alignment <> caLeft then
        Writer.AddAttr('horizontal',MSCellAlignmentToStr(Style.Alignment));
      if Style.VerticalAligment <> cvaBottom then
        Writer.AddAttr('vertical',MSCellAlignmentToStr(Style.VerticalAligment));
      if Style.WrapText then
         Writer.AddAttr('wrapText','1');
      Writer.EndNode();
    end;
    Writer.EndNode(False);
  end;

begin
  PrepareStyleList;

  Stream := TFileStream.Create(FTempDir + '\xl\styles.xml', fmCreate);
  try
    Writer := TQXMLWriter.Create(Stream);
    try
      Writer.CreateProcessingInstruction('1.0', 'UTF-8', 'yes');
      Writer.BeginNode('styleSheet',['xmlns'], ['http://schemas.openxmlformats.org/spreadsheetml/2006/main']);

      if FStyleList.FNumFmtsCount > 0 then
      begin
        Writer.BeginNode('numFmts');
        Writer.AddAttr('count', IntToStr( FStyleList.FNumFmtsCount ));
        k := xlsxCustomNumericFormat - 1;
        for I := 0 to FStyleList.Count - 1 do
        begin
          if FStyleList[i].Options.FNumFmtId > k then
          begin
            Writer.CreateFullNode('numFmt', ['numFmtId','formatCode'],
              [IntToStr(FStyleList[i].Options.FNumFmtId),
              FStyleList[i].Options.NumericFormat]);
            k := FStyleList[i].Options.FNumFmtId;
          end;
        end;
        Writer.EndNode(); // numFmts
      end;

      Writer.BeginNode('fonts');
      Writer.AddAttr('count', IntToStr( FStyleList.FFontsCount ));
      Writer.BeginNode('font'); //Default
      Writer.CreateFullNode('sz', ['val'], ['11']);
      Writer.CreateFullNode('color', ['theme'], ['1']);
      Writer.CreateFullNode('name', ['val'], ['Calibri']);
      Writer.CreateFullNode('family', ['val'], ['2']);
      Writer.CreateFullNode('charset', ['val'], ['1']); // 1 = DEFAULT_CHARSET  //???
      Writer.CreateFullNode('scheme', ['val'], ['minor']);
      Writer.EndNode(); // font
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FFontId > k then
        begin
          SaveFont(FStyleList[i].Options.Font);
          k := FStyleList[i].Options.FFontId;
        end;
      end;
      Writer.EndNode(); // fonts

      Writer.BeginNode('fills');
      Writer.AddAttr('count', IntToStr( FStyleList.FFillsCount ));
      SaveFill(True); // default
      Writer.BeginNode('fill');
      Writer.CreateFullNode('patternFill', ['patternType'], ['gray125']);
      Writer.EndNode(); // fill
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FFillId > k then
        begin
          SaveFill(not FStyleList[i].Options.UseBackground,
            FStyleList[i].Options.BackgroundColor);
          k := FStyleList[i].Options.FFillId;
        end;
      end;
      Writer.EndNode(); // fills

      Writer.BeginNode('borders');
      Writer.AddAttr('count', IntToStr( FStyleList.FBordersCount ));
      CreateBorder(True); // default
      k := 0;
      for I := 1 to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FBorderId > k then
        begin
          CreateBorder(not FStyleList[i].Options.UseBorder,
            FStyleList[i].Options.Border.Style, FStyleList[i].Options.Border.Color);
          k := FStyleList[i].Options.FBorderId;
        end;
      end;
      Writer.EndNode(); // borders

      Writer.BeginNode('cellStyleXfs', ['count'], ['1']);
      Writer.CreateFullNode('xf', ['numFmtId', 'fontId', 'fillId', 'borderId'], ['0', '0', '0', '0']);
      Writer.EndNode(); // cellStyleXfs

      Writer.BeginNode('cellXfs');
      Writer.AddAttr('count', IntToStr( FStyleList.FCellXfsCount ));
      // default - 0
      Writer.CreateFullNode('xf', ['numFmtId', 'fontId', 'fillId', 'borderId', 'xfId'], ['0', '0', '0', '0', '0']);
      SaveXF(FStyleList[xlsxStyle_Header].Options);
      SaveXF(FStyleList[xlsxStyle_Footer].Options);
      SaveXF(FStyleList[xlsxStyle_Caption].Options);
      SaveXF(FStyleList[xlsxStyle_Data].Options);
      k := 0;
      for I := xlsxDefaultStylesCount to FStyleList.Count - 1 do
      begin
        if FStyleList[i].Options.FStyleId > k then
        begin
          SaveXF(FStyleList[i].Options);
          k := FStyleList[i].Options.FStyleId;
        end;
      end;
      Writer.EndNode(); // cellXfs

      Writer.BeginNode('cellStyles', ['count'], ['1']);
      Writer.CreateFullNode('cellStyle', ['name', 'xfId', 'builtinId'], ['Normal', '0', '0']);
      Writer.EndNode();

      Writer.CreateFullNode('dxfs', ['count'], ['0']);
      Writer.CreateFullNode('tableStyles', ['count', 'defaultTableStyle', 'defaultPivotStyle'], ['0', 'TableStyleMedium9', 'PivotStyleLight16']);
      Writer.CreateFullNode('colors');

      Writer.EndNode(); // sheetStyle
    finally
      Writer.Free;
    end;
  finally
    Stream.Free;
  end;
end;

{ TQExport4Xlsx }

procedure TQExport4Xlsx.SetXlsxOptions(const Value: TQExport4XlsxOptions);
begin
  FXlsxOptions.Assign(Value);
end;

procedure TQExport4Xlsx.SaveProperties(IniFile: TQIniFile);
begin
  inherited;

  IniFile.WriteBool(S_XLSX, S_AutoTruncateValue, AutoTruncateValue);
  IniFile.WriteBool(S_XLSX, S_AutoCalcColWidth, AutoCalcColWidth);
  IniFile.WriteBool(S_XLSX, S_RightToLeft, FRightToLeftExcelMode);
end;

procedure TQExport4Xlsx.SetSheetName(const Value: WideString);
begin
  if Value <> '' then
    FSheetName := Value;
end;

procedure TQExport4Xlsx.WriteHeader;
var
  i: Integer;
begin
  for i := 0 to Header.Count - 1 do
  begin
    Inc(FCounter);
    FXlsxFile.AddCell(0, FCounter, 1, Header[i], ectString, xlsxStyle_Header);

    if AutoCalcColWidth then
      CalcColsWidth(Header[i], xlsxStyle_Header, 0);
  end;
end;

procedure TQExport4Xlsx.WriteFooter;
var
  i: Integer;
begin
  for i := 0 to Footer.Count - 1 do
  begin
    Inc(FCounter);
    FXlsxFile.AddCell(0, FCounter, 1, Footer[i], ectString, xlsxStyle_Footer);
    if AutoCalcColWidth then
      CalcColsWidth(Footer[i], xlsxStyle_Footer, 0);
  end;
end;

procedure TQExport4Xlsx.BeginExport;
  var
    i: Integer;
begin
  inherited;
  FCounter := 0;
  FDataRowCounter := 0;

  if AutoCalcColWidth then
    for I := 0 to Columns.Count - 1 do
      if (ColumnsWidth.IndexOfName(Columns[I].Name) < 0)  then
        Columns[I].Width := xlsxColWidthZero;

  FXlsxFile := TXlsxFileMaker.Create(FileName, XlsxOptions, Formats);
  FXlsxFile.SheetName := SheetName;
  FXlsxFile.RightToLeftExcelMode := FRightToLeftExcelMode;
  FXlsxFile.CreateDirsStructure;
  if FXlsxFile.IsExistDirsStructure then
  begin
    FXlsxFile.FillCommonData;
    FXlsxFile.PrepareExport(AutoCalcColWidth);
    WriteHeader;
  end;
end;

procedure TQExport4Xlsx.BeforeExport;
begin
  inherited;

end;

procedure TQExport4Xlsx.AfterExport;
begin

  inherited;
end;

procedure TQExport4Xlsx.EndExport;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    WriteFooter;

    if AutoCalcColWidth then
      FXlsxFile.WriteColsWidth(Columns);

    FXlsxFile.FinishExport;
    FXlsxFile.CompressFile;
  end;
  FXlsxFile.Free;
  inherited;
end;

procedure TQExport4Xlsx.WriteCaptionRow;
var
  i: Integer;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    Inc(FCounter);
    for i := 0 to Columns.Count - 1 do
    begin
      FXlsxFile.AddCell(i, FCounter, Columns.Count,
        GetColCaption(i), ectString, xlsxStyle_Caption);
      if AutoCalcColWidth then
        CalcColsWidth(GetColCaption(i), xlsxStyle_Caption, i);
    end;
  end;
end;

procedure TQExport4Xlsx.WriteDataRow;

  function GetValue(Col: TQExportCol): QEString;
  var
    dbl: Double;
    dt: TDateTime;
    d: Integer;
    bd: Int64;
  begin
    if ((Col.Value = EmptyStr) or (Col.Value = 'null'))
      and (VarIsEmpty(Col.Data) or VarIsNull(Col.Data))
    then
      Result := Formats.NullString
    else
      case Col.Row.Columns[Col.ColumnIndex].ColType of
        ectInteger: begin
          try d := Col.Data; except d := 0; end;
          Result := IntToStr(d);
        end;
        ectBigint: begin
        {$IFDEF VCL6}
          try bd := Col.Data; except bd := 0; end;
          Result := IntToStr(bd);
        {$ELSE}
          Result := Col.Value;
        {$ENDIF}
        end;
        ectFloat,
        ectNumeric,
        ectCurrency: begin
          try dbl := Col.Data; except dbl := 0; end;
          Result := FloatToStr(dbl);
        end;
        ectDate,
        ectTime,
        ectDateTime: begin
          try dt := Col.Data; except dt := 0; end;
          Result := FloatToStr(dt);
        end;
        ectString,
        ectBoolean:
          Result := Col.Value;
      else
      {$IFDEF VCL21}
        Result := TNetEncoding.Base64.Encode(Col.Value);
      {$ELSE}
      {$IFDEF VCL6}
        Result := {$IFDEF VCL16}Soap.{$ENDIF}EncdDecd.EncodeString(Col.Value) //encode to base64
      {$ELSE}
        Result := Col.Value;
      {$ENDIF}
      {$ENDIF}
      end;
  end;

var
  i, n: Integer;
  UseStyle: Boolean;
  Value: qeString;
  StyleOptions: TXlsxCellStyle;
  WorkStyle: TXlsxStyleItem;
begin
  if FXlsxFile.IsExistDirsStructure then
  begin
    Inc(FCounter);
    for i := 0 to ExportRow.Count - 1 do
    begin
      Value := GetValue(ExportRow[i]);
      if Length(Value) > MAX_DATA_LENGTH then
        if FAutoTruncateValue then
          Value := Copy(Value, 1, MAX_DATA_LENGTH)
        else
          raise Exception.Create(sStringTooLarge);
      n := xlsxStyle_Data;
      if (FXlsxFile.FStyleList.Count > xlsxDefaultStylesCount) then
        if (XlsxOptions.StripStylesList.Count > 0) and (XlsxOptions.StripStyleType <> ssNone) then
          if (XlsxOptions.StripStyleType = ssRow) then
          begin
            n := (FDataRowCounter mod XlsxOptions.StripStylesList.Count)
              * ExportRow.Count + I + xlsxDefaultStylesCount;
            n := FXlsxFile.FStyleList[n].Options.StyleId;
          end
          else
            n := FXlsxFile.FStyleList.StyleIdByColName(ExportRow.Columns[i].Name)
        else
        if not (ExportRow.Columns[i].ColType in [ectString, ectBoolean, ectUnknown]) then
          n := FXlsxFile.FStyleList.StyleIdByColName(ExportRow.Columns[i].Name);

      if Assigned(OnGetDataParams) then
      begin
        StyleOptions := TXlsxCellStyle.Create;
        try
          UseStyle := False;

          WorkStyle := FXlsxFile.FStyleList.GetStyleByStyleID(N);
          if Assigned(WorkStyle) then
            StyleOptions.Assign(WorkStyle.Options);

          OnGetDataParams(Self, i + 1, FCounter, StyleOptions, UseStyle);
          if UseStyle then
          begin
            StyleOptions.StyleID := -1;
            N := FXlsxFile.FStyleList.GetStyleIDByStyle(StyleOptions);
            if N < 0 then
            begin
              N := FXlsxFile.CustomStyleList.GetStyleIDByStyle(StyleOptions);
              if N < 0 then
              begin
                N := FXlsxFile.FStyleList.Count +
              FXlsxFile.CustomStyleList.Count;//next style after DataStyle

                StyleOptions.StyleID := N;
            FXlsxFile.CustomStyleList.Add.Options.Assign(StyleOptions);
              end
              else
                StyleOptions.StyleID := N;
            end
            else
              StyleOptions.StyleID := N;
          end;
        finally
          StyleOptions.Free;
        end;
      end;

      FXlsxFile.AddCell(i, FCounter, ExportRow.Count, Value,
        ExportRow.Columns[i].ColType, n );

      if AutoCalcColWidth then
        CalcColsWidth(ExportRow[i].Value, n, i);
    end;
    Inc(FDataRowCounter);
  end;
end;

procedure TQExport4Xlsx.CalcColsWidth(const Value: string;
  const XlsxStyleId, ColIndex: Integer);

  procedure SetDefaultFont(const aFont: TFont);
  begin
    aFont.Style := xlsxDefaultFont.Style;
    aFont.Color := xlsxDefaultFont.Color;
    aFont.CharSet := xlsxDefaultFont.Charset;
    aFont.Name := xlsxDefaultFont.Name;
    aFont.Size := xlsxDefaultFont.Size;
  end;

  const
    Padding = 5;

  var
    DC: HDC;
    Canvas: TCanvas;
    ValueLength: Integer;
    ValueLengthDiv: Double;

    XlsxStyleItem: TXlsxStyleItem;
begin
  if (ColumnsWidth.IndexOfName(Columns[ColIndex].Name) >= 0) then
    Exit;

  DC := CreateCompatibleDC(0);
  try
  Canvas := TCanvas.Create;
    try
      Canvas.Handle := DC;

      XlsxStyleItem := FXlsxFile.FStyleList.GetStyleByStyleID(XlsxStyleId);

      if Assigned(XlsxStyleItem) then
      begin
        Canvas.Font.Assign(XlsxStyleItem.Options.Font);
        Canvas.Font.Size := Canvas.Font.Size;
      end
      else
        SetDefaultFont(Canvas.Font);

      ValueLengthDiv :=  Canvas.TextWidth(Value) + Padding;
      SetDefaultFont(Canvas.Font);
      ValueLengthDiv := ValueLengthDiv /
        Canvas.TextWidth('0');

      ValueLength := Trunc(ValueLengthDiv);

      if Frac(ValueLengthDiv) > 0 then
        ValueLength := ValueLength + 1;

      if ValueLength > Integer(Columns[ColIndex].Width) then
        Columns[ColIndex].Width := ValueLength;
    finally
      Canvas.Free;
    end;
  finally
    DeleteDC(DC);
  end;
end;

procedure TQExport4Xlsx.CheckFormats;
var
  SysDecSep, SysThoSep: Char;
begin
  inherited;
  SysDecSep := {$IFDEF VCL16}System.SysUtils{$ELSE}SysUtils{$ENDIF}.
               {$IFDEF VCL17}FormatSettings.{$ENDIF}DecimalSeparator;
  SysThoSep := {$IFDEF VCL16}System.SysUtils{$ELSE}SysUtils{$ENDIF}.
               {$IFDEF VCL17}FormatSettings.{$ENDIF}ThousandSeparator;
  Assert(Formats.DecimalSeparator = SysDecSep,
    'Setting DecimalSeparator will not have an effect on XLSx export type');
  Assert(Formats.ThousandSeparator = SysThoSep,
    'Setting ThousandSeparator will not have an effect on XLSx export type');
end;

constructor TQExport4Xlsx.Create(AOwner: TComponent);
begin
  inherited;
  FSheetName := 'sheet1';
  FAutoTruncateValue := False;
  FRightToLeftExcelMode := False;
  FXlsxOptions := TQExport4XlsxOptions.Create(Self);
end;

destructor TQExport4Xlsx.Destroy;
begin
//  if Assigned(FXlsxOptions) then // dee
//  begin
    FXlsxOptions.Free;
    FXlsxOptions := nil;
//  end;

  inherited;
end;

procedure TQExport4Xlsx.Execute;
begin
  BeforeExecute;
  try
    DoExport;
    ShowResult;
  finally
    AfterExecute
  end;
end;

procedure TQExport4Xlsx.LoadProperties(IniFile: TQIniFile);
begin
  inherited;

  AutoTruncateValue := IniFile.ReadBool(S_XLSX, S_AutoTruncateValue,
    AutoTruncateValue);
  AutoCalcColWidth := IniFile.ReadBool(S_XLSX, S_AutoCalcColWidth,
    AutoCalcColWidth);
  FRightToLeftExcelMode := IniFile.ReadBool(S_XLSX, S_RightToLeft, FRightToLeftExcelMode);
end;

initialization
  xlsxDefaultFont := TFont.Create();
  xlsxDefaultFont.Name := 'Calibri';
  xlsxDefaultFont.Size := 11;
  xlsxDefaultFont.Style := [];
  xlsxDefaultFont.Color := clBlack;

finalization
  xlsxDefaultFont.Free();

end.
