
{******************************************}
{                                          }
{                FastCube 2                }
{        Open Document Format export       }
{                                          }
{         Copyright (c) 1998-2016          }
{          by Alexander Fediachov,         }
{             Fast Reports Inc.            }
{                                          }
{******************************************}

//VCL uses section
{$IFNDEF FMX}
unit fcxExportXLSX;
{$I fcx.inc}

interface

uses
  SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, ExtCtrls, ComCtrls,
  ImgList,
  {$IFDEF DELPHI_6UP}
  Variants,
  {$ENDIF}
  fcxXML, fcxZip, fcxCustomExport, fcxCustomSliceGridExport, fcxFormats, fcxCube, fcxSlice,
  fcxUtils, fcxGraphicUtils, fcxRes, fcxOfficeOpen,
  fcxCustomGrid, fcxSliceGrid, fcxStyles, fcxExportDialog, fcxTypes;
{$ELSE FMX}
{$INCLUDE fcx.inc}
interface

uses
  // RTL
  System.SysUtils, System.Classes, System.UITypes, System.Variants,
  // FMX
  FMX.StdCtrls, FMX.Dialogs,
  {$IFDEF Delphi_19UP}
  FMX.Graphics,
  {$ENDIF}
  // FC FMX
  FMX.fcxRes, FMX.fcxUtils, FMX.fcxXML, FMX.fcxFormats, FMX.fcxZip,
  FMX.fcxCube, FMX.fcxSlice, FMX.fcxCustomExport, FMX.fcxCustomSliceGridExport, FMX.fcxStyles,
  FMX.fcxGraphicUtils, FMX.fcxExportDialog, FMX.fcxOfficeOpen, FMX.fcxTypes;
{$ENDIF}

{$IFNDEF FMX}
{$IFNDEF FPC}
{$DEFINE HAS_METAFILE}
{$ENDIF}
{$ENDIF}

type
  TfcxXLSXExportDialog = class(TfcxExportDialog)
  private
    OpenCB: TCheckBox;
    cbRepeatValues: TCheckBox;
  public
    constructor Create(AOwner: TComponent); override;
  end;

  TfcxXLSXStyleRec = record
    ID: Integer;
    Alignment: TAlignment;
    Format: TfcxFormat;
  end;
  TfcxXLSXStyleArray = array of TfcxXLSXStyleRec;

(*
{$IFDEF DELPHI16}
[ComponentPlatformsAttribute(pidWin32 or pidWin64)]
{$ENDIF}
*)
  TfcxXLSXExport = class(TfcxCustomSliceGridExportFilter)
  private
    FOpenAfterExport: Boolean;
    FDocFolder: string;
    FContentTypes: TStream; // [Content_Types].xml
    FRels: TStream; // _rels/.rels
    FStyles: TStream; // xl/styles.xml
    FWorkbook: TStream; // xl/workbook.xml
    FSharedStrings: TStream; // sharedStrings.xml
    FWorkbookRels: TStream; // xl/_rels/workbook.xml.rels
    FFonts: TStrings; // <fonts> section in xl/styles.xml
    FFills: TStrings; // <fills> section in xl/styles.xml
    FBorders: TStrings; // <borders> section in xl/styles.xml
    FCellStyleXfs: TStrings; // <cellStyleXfs> section in xl/styles.xml
    FCellXfs: TStrings; // <cellXfs> section in xl/styles.xml
    FColors: TList; // <colors> section in xl/styles.xml
    FNumFmts: TStrings; // <numFmts> section in xl/styles.xml
    FStrings: TStrings; // <sst> section in xl/sharedStrings
    FStringsCount: Integer; // count of strings in the workbook

    FRows: TStrings; // <sheetData> section in xl/worksheets/sheetXXX.xml
    FMerges: TStrings; // <mergeCells> section in xl/worksheets/sheetXXX.xml
    StylesMap: array of TfcxXLSXStyleArray;

//    FLastPage: TfcxMap;
    FExportPageBreaks: Boolean;
    FRepeatValues: Boolean;
    FCreator: String;
    FSavePictures: Boolean;
{$IFNDEF FMX}
    FYAxisImageList: TCustomImageList;
    FDrawings: TStrings; // <xdr:twoCellAnchor> sections in drawings/drawingXXX.xml
    p_size: string;
{$ENDIF}
    colcount, rowcount: integer;
    FSaveYAxisPictures: Boolean;
    function AddString(s: string): Integer;
    function AddColor(c: TColor): Integer;
    procedure AddColors(const c: array of TColor);
    procedure AddSheet(AIndex: integer);
//    procedure ExportFormats(FNumFmts: TStrings);
    procedure UpdateStyles;
    procedure ExportPage(Stream: TStream);

    function GetStyleIndex(AStyle: TfcxCustomThemeStyle; AAlignment: TAlignment; ADisplayFormat: TfcxFormat): integer;

    function XAxisDrawProc(Sender: TfcxAxisContainer; const ARec: TfcxSliceDrawHeader): Boolean;
    function YAxisDrawProc(Sender: TfcxAxisContainer; const ARec: TfcxSliceDrawHeader): Boolean;
    function GetEmptyCell(col, row, style: integer): string;
    function GetStringCell(col, row: integer; value: string;
      style: integer): string;
    function A1(x, y: Integer): string;
    function GetDateCell(col, row: integer; value: TDateTime;
      style: integer): string;
    function GetNumCell(col, row: integer; value: Variant;
      style: integer): string;
  public
    constructor Create(AOwner: TComponent); override;
    class function GetDescription: string; override;
    function ShowModal: TModalResult; override;
    function Start: Boolean; override;
    procedure Run; override;
    procedure Finish; override;
  published
    property ExportPageBreaks: Boolean read FExportPageBreaks write FExportPageBreaks default True;
    property OpenAfterExport: Boolean read FOpenAfterExport write FOpenAfterExport default False;
    property OverwritePrompt;
    property RepeatValues: Boolean read FRepeatValues write FRepeatValues default False;
    property Creator: String read FCreator write FCreator;
    property SaveYAxisPictures: Boolean read FSaveYAxisPictures write FSaveYAxisPictures default False;
  end;

implementation

const
  StylesRid = 'rId1000_'; // xl/styles.xml
  ThemeRid = 'rId2000_'; // xl/theme1.xml
  SharedStringsRid = 'rId3000_'; // xl/sharedStrings.xml
  WorkbookRid = 'rId4000_'; // xl/workbook.xml
  CoreRid = 'rId5000_'; // docProps/core.xml
  ColWidthFactor = 0.111317;
  RowHeightFactor = 0.754967;
  MarginFactor = 1 / 25.4;

var
  SetFormat : TFormatSettings;

{ TfcxXLSXExportDialog }

constructor TfcxXLSXExportDialog.Create(AOwner: TComponent);
var
  Page: TComponent;
begin
  inherited;

  Page := AddPage(fcxResources.Get('sExport'));
  OpenCB := AddCheckBox(Page, fcxGet(8706));
  cbRepeatValues := AddCheckBox(Page, fcxResources.Get('sRepeatAxesValues'));
end;

{ TfcxXLSXExport }

function TfcxXLSXExport.AddColor(c: TColor): Integer;
var
  i, j, k: Integer;
begin
  if c = 0 then c := $FFFFFF;
  c := RGBSwap(c);
  j := -1;
  k := 1000000;
  for i := 0 to FColors.Count - 1 do
    if Distance(Integer(FColors[i]), c) < k then
    begin
      k := Distance(Integer(FColors[i]), c);
      j := i;
    end;

  if (k = 0) or (FColors.Count = 56) then
  begin
    Result := j;
    Exit;
  end;

  Result := FColors.Add(Pointer(c));
end;

procedure TfcxXLSXExport.AddColors(const c: array of TColor);
var
  i: Integer;
begin
  for i := Low(c) to High(c) do
    AddColor(c[i]);
end;

procedure TfcxXLSXExport.AddSheet(AIndex: integer);
var
  ss: TStream; // xl/worksheets/sheetXXX.xml
{$IFDEF HAS_METAFILE}
  dg: TStream; // xl/drawings/drawingXXX.xml
  BMap: TBitmap;
  Emf: TMetafile;
  MetaCanvas: TMetafileCanvas;
{$ENDIF}
  ss_temp: TStream;

  function ColWidth(i: Integer): Double;
  begin
    Result := 30;
//      Result := ColWidthFactor * (GetXPosById(i + 1) - GetXPosById(i));
  end;

  function RowHeight(i: Integer): Double;
  begin
    Result := 10;
//      Result := RowHeightFactor * (GetYPosById(i + 1) - GetYPosById(i));
  end;

  procedure ExportCubeRows();
  var
    Columns: TfcxCubeDataColumns;
    i, j, row: integer;
    Value: Variant;
    rowstr: string;
  begin
    Columns := DoGetCubeCols;

//    rowstr := Format('<row r="%d" ht="%f" customHeight="1">',
//      [1, RowHeight(0)], SetFormat);
    rowstr := Format('<row r="%d">',
      [1], SetFormat);
    for j := 0 to colcount - 1 do
    begin
      rowstr := rowstr + GetStringCell(j, 0, Columns[j].Field.CubeFieldDisplayLabel, GetStyleIndex(Styles[gsHeaderCells], taCenter, nil));
    end;
    rowstr := rowstr + '</row>';
    WriteStr(ss, rowstr);
    for i := 0 to rowcount - 1 do
    begin
//      rowstr := Format('<row r="%d" ht="%f" customHeight="1">',
//        [i+2, RowHeight(i+1)], SetFormat);
      rowstr := Format('<row r="%d">',
        [i+2], SetFormat);
      for j := 0 to colcount - 1 do
      begin
        row := DoGetCubeRowIndex(i);
        Value := Cube.SourceHolder.UniqueValueAsVariant[row, Columns[j].Field];
        if Value = null then
        begin
          rowstr := rowstr + GetStringCell(j, i+1, Cube.SourceHolder.UniqueValueCaption[row, Columns[j].Field], GetStyleIndex(Styles[gsDataCells], taLeftJustify, Columns[j].Field.DisplayFormat));
        end
        else
        if Columns[j].Field.DateType in [odt_Month, odt_DayOfWeek] then
        begin
          rowstr := rowstr + GetStringCell(j, i+1, Cube.SourceHolder.UniqueValueCaption[row, Columns[j].Field], GetStyleIndex(Styles[gsDataCells], taLeftJustify, Columns[j].Field.DisplayFormat));
        end
        else
        if (VarType(Value) = varDate) then
        begin
          rowstr := rowstr + GetDateCell(j, i+1, VarToDateTime(Value), GetStyleIndex(Styles[gsDataCells], taRightJustify, Columns[j].Field.DisplayFormat));
        end
        else
        if VarIsNumeric(Value) then
        begin
          rowstr := rowstr + GetNumCell(j, i+1, Value, GetStyleIndex(Styles[gsDataCells], taRightJustify, Columns[j].Field.DisplayFormat));
        end
        else
        begin
          rowstr := rowstr + GetStringCell(j, i+1, Value, GetStyleIndex(Styles[gsDataCells], taLeftJustify, Columns[j].Field.DisplayFormat));
        end;
      end;
      rowstr := rowstr + '</row>';
      WriteStr(ss, rowstr);
    end;

  end;

  procedure ExportSliceRows();
  var
    MaxCol, MaxRow: Integer;
    Style1, Style2: Integer;
    rowstr: string;
    i, j, Col, Row, RowIndex: integer;
    Measure: TfcxMeasureField;
    MeasureCell: TfcxMeasureCell;
    format1: TfcxFormat;
    varvalue: variant;
    xlc, ylc: integer;
  begin
    // add header
    Style1 := GetStyleIndex(Styles[gsHeaderArea], taCenter, nil);
    Style2 := GetStyleIndex(Styles[gsActiveDimension], taLeftJustify, nil);

    // row title
    xlc := Slice.XAxisContainer.VisibleLevelCount;
    if xlc = 0 then
      xlc := 1;
    ylc := Slice.YAxisContainer.VisibleLevelCount;
    if ylc = 0 then
      ylc := 1;
    MaxCol := Slice.YAxisContainer.VisibleLevelCount - 1;
    if MaxCol < 0 then
      MaxCol := 0;
    MaxRow := Slice.XAxisContainer.VisibleLevelCount - 1;
    if MaxRow < 0 then
      MaxRow := 0;
    for i := 0 to MaxRow do
    begin
      FRows.Add('');
      rowstr := '';
      for j := 0 to MaxCol do
      begin
        rowstr := rowstr + GetEmptyCell(j, i, Style1);
{
        Format('<c r="%s" s="%d">',
          [A1(j, i), GetStyleIndex(Styles[gsHeaderCells], taCenter, nil)], SetFormat);
        rowstr := rowstr + '</c>';
}
      end;
      FRows[i] := FRows[i] + rowstr;
    end;

    FRows.Add('');
    rowstr := '';
    for j := 0 to Slice.YAxisContainer.RealLevelCount - 1 do
    begin
      if (Slice.YAxisContainer.LevelInfo[j].IsVisible) then
      begin
        if Slice.YAxisContainer.LevelInfo[j].IsMeasure then
          rowstr := rowstr + GetStringCell(j, MaxRow+1, Slice.MeasuresContainer.Caption, GetStyleIndex(Styles[gsMeasure], taLeftJustify, nil))
        else
          rowstr := rowstr + GetStringCell(j, MaxRow+1, Slice.YAxisContainer.LevelInfo[j].RegionField.Caption, Style2);
      end;
    end;
    FRows[MaxRow+1] := FRows[MaxRow+1] + rowstr;

    // merge
    if (MaxCol > 0) or (MaxRow > 0) then
    begin
      rowstr := Format('<mergeCell ref="%s"/>',
        [A1(0, 0)+':'+A1(MaxCol, MaxRow)], SetFormat);
      FMerges.Add(rowstr);
    end;

    // column title
    rowstr := '';
    for j := 0 to Slice.XAxisContainer.RealLevelCount - 1 do
      if (Slice.XAxisContainer.LevelInfo[j].IsVisible) then
      begin
        if Slice.XAxisContainer.LevelInfo[j].IsMeasure then
          rowstr := rowstr + GetStringCell(j + ylc, 0, Slice.MeasuresContainer.Caption, GetStyleIndex(Styles[gsMeasure], taLeftJustify, nil))
        else
          rowstr := rowstr + GetStringCell(j + ylc, 0, Slice.XAxisContainer.LevelInfo[j].RegionField.Caption, Style2);
      end;
    FRows[0] := FRows[0] + rowstr;

    // column header
    Slice.XAxisContainer.TraverseAxis(0, Slice.XAxisContainer.VisibleLevelCount - 1, 0, XAxisDrawProc);

    // row header
    Slice.YAxisContainer.TraverseAxis(0, Slice.YAxisContainer.VisibleLevelCount - 1, 0, YAxisDrawProc);

    for i := 0 to xlc do
      WriteStr(ss, Format('<row r="%d">', [i + 1], SetFormat) + FRows[i] + '</row>');

    // data
    for Row := 0 to Slice.RowCount - 1 do
    begin
      RowIndex := Row + xlc + 1;

      WriteStr(ss, Format('<row r="%d">', [RowIndex + 1], SetFormat));
      if (FRows.Count > RowIndex) then
        rowstr := FRows[RowIndex]
      else
        rowstr := '';

      for Col := 0 to Slice.ColCount - 1 do
      begin
        Slice.GetMeasureCell(Col, Row, Measurecell);
        Measure := Slice.MeasuresContainer.Measures[MeasureCell.MeasureIndex];
        if (MeasureCell.MeasureIndex >= 0) and ((Measure.DisplayAs in (da_Percents + da_Ranks)))then
        begin
          varvalue := MeasureCell.StrValue;
          format1 := nil;
        end
        else
        begin
          varvalue := MeasureCell.Value;
          if varvalue = Null then varvalue := Unassigned;
          if (MeasureCell.MeasureIndex >= 0) then
            format1 := Measure.DisplayFormat
          else
            format1 := nil;
        end;
        if MeasureCell.IsTotal or MeasureCell.IsGrandTotal then
          Style1 := GetStyleIndex(Styles[gsDataCellsTotals], MeasureCell.Alignment, format1)
        else
          Style1 := GetStyleIndex(Styles[gsDataCells], MeasureCell.Alignment, format1);
        if (MeasureCell.MeasureIndex >= 0) and (Measure.DisplayAs in da_Percents) then
          rowstr := rowstr + GetStringCell(Col + ylc, RowIndex, varvalue, Style1)
        else
        if (MeasureCell.MeasureIndex >= 0) and (Measure.DisplayAs in da_Ranks) then
          rowstr := rowstr + GetNumCell(Col + ylc, RowIndex, varvalue, Style1)
        else
        begin
          if (TVarData(varvalue).VType) <= 1 then
            rowstr := rowstr + GetEmptyCell(Col + ylc, RowIndex, Style1)
          else
          if (TVarData(varvalue).VType = varDate) then
            rowstr := rowstr + GetDateCell(Col + ylc, RowIndex, VarToDateTime(varvalue), Style1)
          else
          if VarIsNumeric(varvalue) then
            rowstr := rowstr + GetNumCell(Col + ylc, RowIndex, varvalue, Style1)
          else
            rowstr := rowstr + GetStringCell(Col + ylc, RowIndex, varvalue, Style1);
        end;
      end;
      // close row
      WriteStr(ss, rowstr + '</row>');
      DoProgress(Row);
    end;
    FRows.Clear;
  end;
var
  i: integer;
  s: String;

begin
  if Assigned(Slice) then
  begin
    colcount := Slice.YAxisContainer.VisibleLevelCount + Slice.ColCount;
    if Slice.YAxisContainer.VisibleLevelCount = 0 then
      inc(colcount);
    rowcount := Slice.XAxisContainer.VisibleLevelCount + 1 + Slice.RowCount;
    if Slice.XAxisContainer.VisibleLevelCount = 0 then
      inc(rowcount);
  end
  else
  begin
    colcount := DoGetCubeCols.VisibleCount;
    rowcount := DoGetCubeRowCount;
  end;

  WriteStr(FContentTypes, '<Override PartName="/xl/worksheets/sheet' + IntToStr(AIndex) +
    '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml' +
    '.worksheet+xml"/>');

  WriteStr(FWorkbook, Format('<sheet name="Page %d" sheetId="%d" r:id="rId%d"/>',
    [AIndex, AIndex, AIndex], SetFormat));

  WriteStr(FWorkbookRels, Format('<Relationship Id="rId%d" Type="http://schemas.' +
    'openxmlformats.org/officeDocument/2006/relationships/worksheet" ' +
    'Target="worksheets/sheet%d.xml"/>', [AIndex, AIndex], SetFormat));

  ss := TFileStream.Create(FDocFolder + 'xl/worksheets/sheet' + IntToStr(AIndex) +
    '.xml', fmCreate);

  with TfcxWriter.Create(ss) do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<worksheet xmlns="http://schemas.openxmlformats.org/',
      'spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/',
      'officeDocument/2006/relationships">',
      Format('<dimension ref="%s:%s"/>',
        [A1(0, 0), A1(colcount, rowcount)]),
      '<sheetViews><sheetView showGridLines="1"',
      ' workbookViewId="0"/></sheetViews>']);
    Free;
  end;

  if FSavePictures then
  begin
{$IFDEF HAS_METAFILE}
    { xl/drawings/drawingXXX.xml }
    dg := TFileStream.Create(FDocFolder + 'xl/drawings/drawing' + IntToStr(AIndex) +
      '.xml', fmCreate);
    p_size := '<xdr:ext cx="' + inttostr(FYAxisImageList.Width * 9525) + '" cy="' + inttostr(FYAxisImageList.Height * 9525) + '"/>';
    with TfcxWriter.Create(dg) do
    begin
      Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        '<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml',
        '/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/',
        'drawingml/2006/main">']);
      Free;
    end;

      for i := 0 to FYAxisImageList.Count - 1 do
      begin
{
    with TfcxWriter.Create(dg) do
    begin
          Write(['<xdr:oneCellAnchor><xdr:from><xdr:col>', IntToStr(1+1),
            '</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>',
            IntToStr(i+1),
            '</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>',
            p_size,
            '<xdr:pic><xdr:nvPicPr><xdr:cNvPr id="1025"',
            ' name="Picture 1"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"',
            ' noChangeArrowheads="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill>',
            '<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/',
            'relationships" r:embed="rId', IntToStr(i + 1), '"/><a:srcRect/><a:stretch>',
            '<a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr bwMode="auto"><a:xfrm>',
            '<a:off x="0" y="0"/><a:ext cx="9525" cy="9525"/></a:xfrm><a:prstGeom',
            ' prst="rect"><a:avLst/></a:prstGeom><a:noFill/></xdr:spPr></xdr:pic>',
            '<xdr:clientData/></xdr:oneCellAnchor>']);
      Free;
    end;
}
      end;

{$ENDIF}
  end;

  { columns widths }

{
  WriteStr(ss, '<cols>');

  for i := 0 to colcount - 1 do
    WriteStr(ss, Format('<col min="%d" max="%d" width="%f" customWidth="1"/>',
      [i + 1, i + 1, ColWidth(i)], SetFormat));

  WriteStr(ss, '</cols>');
}
  { cell styles }

  SetLength(StylesMap, 0);

  { First default border }
  FBorders.Add('<border><left/><right/><top/><bottom/></border>');
//...

  { cells }

  WriteStr(ss, '<sheetData>');
  if Assigned(Slice) then
    ExportSliceRows()
  else
    ExportCubeRows();
  WriteStr(ss, '</sheetData>');
  { merged cells }
  if FMerges.Count > 0 then
  begin
    WriteStr(ss, '<mergeCells>');
    WriteStr(ss, FMerges.Text);
    WriteStr(ss, '</mergeCells>');
  end;
  { pictures }

  if FSavePictures then
  begin
{$IFDEF HAS_METAFILE}
//    FYAxisImageList.Count;
    WriteStr(FContentTypes, Format('<Override PartName="/xl/drawings/drawing%d.xml"' +
      ' ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>',
      [AIndex], SetFormat));

    { xl/worksheets/_rels/sheetXXX.xml.rels }
    ss_temp := TFileStream.Create(FDocFolder + 'xl/worksheets/_rels/sheet' + IntToStr(AIndex) +
      '.xml.rels', fmCreate);

    with TfcxWriter.Create(ss_temp) do
    begin
      Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        '<Relationships xmlns="http://schemas.openxmlformats.org/',
        'package/2006/relationships">',
        Format('<Relationship Id="rId1" Type="http://schemas.' +
          'openxmlformats.org/officeDocument/2006/relationships/drawing"' +
          ' Target="../drawings/drawing%d.xml"/>', [AIndex], SetFormat),
        '</Relationships>']);

      Free;
    end;
    ss_temp.Free;

    { xl/drawings/_rels/drawingXXX.xml.rels }
    ss_temp := TFileStream.Create(FDocFolder + 'xl/drawings/_rels/drawing' + IntToStr(AIndex) +
      '.xml.rels', fmCreate);
    with TfcxWriter.Create(ss_temp) do
    begin
      Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        '<Relationships xmlns="http://schemas.openxmlformats.org/',
        'package/2006/relationships">']);
      BMap := TBitmap.Create;
      try
        Emf := TMetafile.Create;
        Emf.Height := FYAxisImageList.Height;
        Emf.Width := FYAxisImageList.Width;
        try
            for i := 0 to FYAxisImageList.Count - 1 do
            begin
              // The extension must be "Emf", regardless what the actual format is.
              s := Format('image-s%d-p%d%s', [AIndex, i + 1, '.Emf'], SetFormat);

              Write(Format('<Relationship Id="rId%d" Type="http://schemas.' +
                'openxmlformats.org/officeDocument/2006/relationships/image"' +
                ' Target="../media/%s"/>', [i + 1, s], SetFormat));
              FYAxisImageList.GetBitmap(i, BMap);
              MetaCanvas := TMetafileCanvas.Create(Emf, 0);
              try
                MetaCanvas.Draw(0, 0, BMap);
              finally
                MetaCanvas.Free;
              end;
              Emf.SaveToFile(FDocFolder + 'xl/media/' + s);
            end;
            Write('</Relationships>');
        finally
          Emf.Free;
        end;
      finally
        BMap.Free;
      end;
      Free;
    end;
    ss_temp.Free;

{$ENDIF}
  end;

  if FSavePictures then
  begin
{$IFDEF HAS_METAFILE}
    WriteStr(ss, '<drawing r:id="rId1"/>');
    if FDrawings.Count > 0 then
    begin
      WriteStr(dg, FDrawings.Text);
    end;
    WriteStr(dg, '</xdr:wsDr>');
    dg.Free;
{$ENDIF}
  end;

  { ending }

  WriteStr(ss, '</worksheet>');

  { free resources }

  ss.Free;
end;

function TfcxXLSXExport.AddString(s: string): Integer;
var i: integer;
    s1: string;
begin
    s1 := '';
    for i := 1 to length(s) do
    {$IFDEF Delphi12}
      if not CharInSet(s[i], [#0..#9, #11, #12, #14..#31]) then
    {$ELSE}
      if not (s[i] in [#0..#9, #11, #12, #14..#31]) then
    {$ENDIF}
        s1 := s1 + s[i];
    Result := FStrings.Add(Escape(s1));
end;

constructor TfcxXLSXExport.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  FilterDesc := fcxResources.Get('XLSXExportFilter');
  DefaultExt := '.xlsx';
  FCreator := 'FastReport';
  FSaveYAxisPictures := False;
{$IFDEF HAS_METAFILE}
  FYAxisImageList := nil;
{$ENDIF}
end;

procedure TfcxXLSXExport.ExportPage(Stream: TStream);
begin
  UpdateStyles;
  AddSheet(1);
end;

procedure TfcxXLSXExport.Finish;
var
  i: Integer;
  Zip: TfcxZipArchive;
  f: TStream;
begin
  WriteStr(FContentTypes, '</Types>');
  WriteStr(FWorkbook, '</sheets><calcPr calcId="124519"/></workbook>');

  { xl/styles.xml }

  with TfcxWriter.Create(FStyles) do
  begin
    Write(['<styleSheet xmlns="http://schemas.openxmlformats.org/',
      'spreadsheetml/2006/main">']);

    Write('numFmts', FNumFmts);
    Write('fonts', FFonts);
    Write('fills', FFills);
    Write('borders', FBorders);
    Write('cellStyleXfs', FCellStyleXfs);
    Write('cellXfs', FCellXfs);

    Write(['<cellStyles count="1"><cellStyle name="Normal" xfId="0"',
      ' builtinId="0"/></cellStyles><dxfs count="0"/>',
      '<tableStyles count="0" defaultTableStyle="TableStyleMedium9"',
      ' defaultPivotStyle="PivotStyleLight16"/>',
      '<colors><indexedColors>']);

    for i := 0 to FColors.Count - 1 do
      Write('<rgbColor rgb="%x"/>', [Integer(FColors[i])]);

    Write('</indexedColors></colors></styleSheet>');
    Free;
  end;

  { xl/sharedStrings.xml }

  with TfcxWriter.Create(FSharedStrings) do
  begin
    Write('<sst xmlns="http://schemas.openxmlformats.org/' +
      'spreadsheetml/2006/main" count="%d" uniqueCount="%d">',
      [FStringsCount, FStrings.Count]);

    for i := 0 to FStrings.Count - 1 do
    begin
      Write('<si><t xml:space="preserve">');
      // note: in unicode delphi12+ Utf8Encode has no effect: when converted to widestring it does utf8decode automatically
      Write(Copy(FStrings[i], 1, length(FStrings[i]) {- 2 ???}), {$IFDEF Delphi12}True{$ELSE}False{$ENDIF});
      Write('</t></si>');
    end;

    Write('</sst>');
    Free;
  end;

  { xl/_rels/workbook.xml.rels }

  WriteStr(FWorkbookRels, '</Relationships>');

  { free resources }

  FFonts.Free;
  FFills.Free;
  FBorders.Free;
  FCellXfs.Free;
  FCellStyleXfs.Free;
  FColors.Free;
  FNumFmts.Free;
  FStrings.Free;
  
  FRows.Free;
  FMerges.Free;
  FDrawings.Free;

  { close files }

  FContentTypes.Free;
  FRels.Free;
  FStyles.Free;
  FSharedStrings.Free;
  FWorkbookRels.Free;
  FWorkbook.Free;

  { compress data }

  if Assigned(Stream) then
    f := Stream
  else
    try
      f := TFileStream.Create(FileName, fmCreate);
    except
      f := nil;
    end;

  if Assigned(f) then
  begin
    Zip := TfcxZipArchive.Create;
    try
      Zip.RootFolder := AnsiString(FDocFolder);
      Zip.AddDir(AnsiString(FDocFolder));
      Zip.SaveToStream(f);
    finally
      Zip.Free;
    end;
  end;

  if not Assigned(Stream) then
    f.Free;

//  DeleteFolder(FDocFolder);

  if OpenAfterExport and not Assigned(Stream) then
    OpenDocument(FileName);
end;

class function TfcxXLSXExport.GetDescription: string;
begin
  Result := fcxResources.Get('XLSXExport');
end;

function TfcxXLSXExport.GetStyleIndex(AStyle: TfcxCustomThemeStyle;
  AAlignment: TAlignment; ADisplayFormat: TfcxFormat): integer;
  
    function ClearFormat(fmt: string): string;
    var i: integer;
    begin
      Result := '';
      for i := 1 to length(fmt) do
        {$IFDEF Delphi12}
        if CharInSet(fmt[i], ['#', '0', ' ', ',', '.']) then
        {$ELSE}
        if fmt[i] in ['#', '0', ' ', ',', '.'] then
        {$ENDIF}
          Result := Result + fmt[i];
    end;

    function ConvertFormat(fmt: TfcxFormat): string;
    var
      err,
      p : integer;
      s: string;
    begin
      result := '';
      if fmt.TypeFormat.Kind = fkText then
        exit;
      s := '';
      if length(fmt.TypeFormat.FormatStr)>0 then
      begin
        p := pos('.', fmt.TypeFormat.FormatStr);
        if p > 0 then
        begin
          s := Copy(fmt.TypeFormat.FormatStr, p+1, length(fmt.TypeFormat.FormatStr)-p-1);
          val(s, p ,err);
          SetLength(s, p);
          if p>0 then
          begin
  {$IFDEF Delphi12}
            s := StringOfChar(Char('0'), p);
  {$ELSE}
            FillChar(s[1], p, '0');
  {$ENDIF}
            s := '.' + s;
          end;
        end;

        p := pos('%', fmt.TypeFormat.FormatStr);
        if p > 0 then
          begin
            case fmt.TypeFormat.FormatStr[length(fmt.TypeFormat.FormatStr)] of
              'n': result := '#,##0' + s;
              'f': result := '0' + s;
              'g': result := '';//s; // '#,##; -#,##';
              'm': result := '#,##0.00';
            end;
          end
        else
          begin
            if (pos('E', fmt.TypeFormat.FormatStr) <> 0) or (pos('e', fmt.TypeFormat.FormatStr) <> 0) then
              result := fmt.TypeFormat.FormatStr
            else
              if (pos('#', fmt.TypeFormat.FormatStr) <> 0) or (pos('0', fmt.TypeFormat.FormatStr) <> 0) then
                result := ClearFormat(fmt.TypeFormat.FormatStr)
              else
                result := '#,##0.00';
          end;
      end;
    end;

    function GetFormatCode(f: TfcxFormat{; idx: Integer}) : Integer;
    var
      res_count: Integer;
      res, format_string: string;
    begin
      Result := 0;
      res_count := 166;

      case f.TypeFormat.Kind of
        fkText:
          Result:= 49;
        fkDateTime:
          Result:= 14;
        fkNumeric:
          begin
            format_string := ConvertFormat(f);
            if format_string = '' then
              exit;
            res := Format('<numFmt numFmtId="%d" formatCode="%s" />',
      {$IFDEF Delphi12}
              [res_count + FNumFmts.Count,format_string],SetFormat);
      {$ELSE}
              [res_count + FNumFmts.Count,UTF8Encode(format_string)],SetFormat);
      {$ENDIF}
              FNumFmts.Add(res);
            Result := FNumFmts.Add(res) + res_count;
          end;
      end;
    end;

    function FS(const s: string; b: Boolean): string;
    begin
      if b then
        Result := s
      else
        Result := '';
    end;

{$IFNDEF FMX}
    function ColorText(col: TColor) : String;
    var
      R0,R1,G0,G1,B0,B1: Byte;
    begin
      R1 := GetRValue(col) and $f;
      R0 := (GetRValue(col) shr 4) and $f;
      G1 := GetGValue(col) and $f;;
      G0 := (GetGValue(col) shr 4) and $f;
      B1 := GetBValue(col) and $f;;
      B0 := (GetBValue(col) shr 4) and $f;
      Result := Format('FF%x%x%x%x%x%x', [R0,R1,G0,G1,B0,B1] );
    end;
{$ELSE FMX}
    function ColorText(col: TAlphaColor) : String;
    var
      acr: TAlphaColorRec;
      R0,R1,G0,G1,B0,B1: Byte;
    begin
      acr.Color := col;
      R1 := acr.R and $f;
      R0 := (acr.R shr 4) and $f;
      G1 := acr.G and $f;;
      G0 := (acr.G shr 4) and $f;
      B1 := acr.B and $f;;
      B0 := (acr.B shr 4) and $f;
      Result := Format('FF%x%x%x%x%x%x', [R0,R1,G0,G1,B0,B1] );
    end;
{$ENDIF}

    function XLHAlign(a: TAlignment): string;
    begin
      case a of
        taLeftJustify: Result := 'left';
        taRightJustify: Result := 'right';
        taCenter: Result := 'center';
        else Result := 'left';
      end;
    end;

    function BoolToInt(b: Boolean): Integer;
    begin
      if b then
        Result := 1
      else
        Result := 0;
    end;

  var
    styleindex: integer;
    I, f, j, k, l: integer;
  begin

    if Length(StylesMap) <= AStyle.Index then
      SetLength(StylesMap, AStyle.Index + 1)
    else
    begin
      for I := 0 to High(StylesMap[AStyle.Index]) do
        if (StylesMap[AStyle.Index][I].Alignment = AAlignment) and
           ((StylesMap[AStyle.Index][I].Format = ADisplayFormat) or
            (Assigned(StylesMap[AStyle.Index][I].Format) and
            (StylesMap[AStyle.Index][I].Format.Equal(ADisplayFormat)))) then
        begin
          Result := StylesMap[AStyle.Index][I].ID;
          Exit;
        end;
    end;
    styleindex := Length(StylesMap[AStyle.Index]);
    SetLength(StylesMap[AStyle.Index], styleindex + 1);
    if Assigned(ADisplayFormat) then
      f := GetFormatCode( ADisplayFormat{, styleindex})
    else
      f := 0;
    j := FFonts.Add(Format('<font>' + FS('<b/>', {$IFDEF FMX}TFontStyle.{$ENDIF}fsBold in AStyle.Font.Style) +
      FS('<i/>', {$IFDEF FMX}TFontStyle.{$ENDIF}fsItalic in AStyle.Font.Style) + FS('<u/>', {$IFDEF FMX}TFontStyle.{$ENDIF}fsUnderline in AStyle.Font.Style) +
      FS('<strike/>', {$IFDEF FMX}TFontStyle.{$ENDIF}fsStrikeOut in AStyle.Font.Style) +
      '<sz val="%' + {$IFDEF FMX}'g'{$ELSE}'d'{$ENDIF} + '"/><color rgb="'+ColorText(AStyle.TextColor)+'"/>',
      [AStyle.Font.Size], SetFormat)+
      '<name val="' + {$IFDEF FMX}AStyle.Font.Family{$ELSE}AStyle.Font.Name{$ENDIF} + '"/>' +
      {$IFNDEF FMX}Format('<charset val="%d"/>', [AStyle.Font.Charset], SetFormat) + {$ENDIF}
      '</font>');

    k := FBorders.Add('<border>' +
      '<left style="thin"><color rgb="' + ColorText({$IFDEF FMX}TAlphaColors.Black{$ELSE}clBlack{$ENDIF}) + '"/></left>' +
      '<right style="thin"><color rgb="' + ColorText({$IFDEF FMX}TAlphaColors.Black{$ELSE}clBlack{$ENDIF}) + '"/></right>' +
      '<top style="thin"><color rgb="' + ColorText({$IFDEF FMX}TAlphaColors.Black{$ELSE}clBlack{$ENDIF}) + '"/></top>' +
      '<bottom style="thin"><color rgb="' + ColorText({$IFDEF FMX}TAlphaColors.Black{$ELSE}clBlack{$ENDIF}) + '"/></bottom>' +
      '</border>');

    if AStyle.FillColor = {$IFDEF FMX}TAlphaColors.Null{$ELSE}clNone{$ENDIF} then
      l := FFills.Add(Format('<fill><patternFill patternType="%s">' +
      '<fgColor indexed="%d"/></patternFill></fill>', ['none',
      AddColor(AStyle.FillColor)] ))
    else
      l := FFills.Add(Format('<fill><patternFill patternType="%s">' +
      '<fgColor indexed="%d"/></patternFill></fill>', ['solid',
      AddColor(AStyle.FillColor)] ));

    StylesMap[AStyle.Index][styleindex].Id := FCellXfs.Add(Format('<xf numFmtId="%d" fontId="%d" fillId="%d"' +
      ' borderId="%d" xfId="0" applyNumberFormat="0" applyFont="1" applyFill="1"' +
      ' applyBorder="1" applyAlignment="1" applyProtection="1">' +
      '<alignment horizontal="%s" vertical="%s" wrapText="%d"' +
      ' readingOrder="1"/></xf>', [f, j, l, k, XLHAlign(AAlignment),
      'center', BoolToInt(True)], SetFormat));
    StylesMap[AStyle.Index][styleindex].Alignment := AAlignment;
    StylesMap[AStyle.Index][styleindex].Format := ADisplayFormat;
    result := StylesMap[AStyle.Index][styleindex].Id;
end;

procedure TfcxXLSXExport.Run;
var
  Exp: TStream;
begin  
  Exp := Stream;
  try
    if not Assigned(Stream) then
      Exp := TFileStream.Create(FileName, fmCreate);
    ExportPage(Exp);
  finally
    if not Assigned(Stream) then
      Exp.Free;
  end;
end;

function TfcxXLSXExport.ShowModal: TModalResult;
begin
  Result := mrOk; // Default
  if Assigned(Stream) then Exit;

  with TfcxXLSXExportDialog.Create(nil) do
  begin
    Caption := fcxResources.Get('XLSXExport');
    PrepareSaveDialog(SaveDialog);
    OpenCB.Checked := OpenAfterExport;
    cbRepeatValues.Checked := RepeatValues;

    Result := ShowModal;
    if Result = mrOk then
    begin
      OpenAfterExport := OpenCB.Checked;
      RepeatValues := cbRepeatValues.Checked;
      if SaveDialog.Execute then
        FileName := SaveDialog.FileName
      else
        Result := mrCancel;
    end;
    Free;
  end;
end;

function TfcxXLSXExport.Start: Boolean;
begin
  Result := False; // Default
  if (FileName = '') and not Assigned(Stream) then
    Exit;
  if (ExtractFilePath(FileName) = '') and (DefaultPath <> '') then
    FileName := DefaultPath + '\' + FileName;

  Result := True;

  { additional data }

  FFonts := TfcxStrList.Create;
  FFills := TfcxStrList.Create;
  FBorders := TfcxStrList.Create;
  FCellStyleXfs := TfcxStrList.Create;
  FNumFmts := TfcxStrList.Create;
  FStrings := TfcxStrList.Create;
  FStringsCount := 0;
  FCellXfs := TfcxStrList.Create;
  FColors := TList.Create;
  FRows := TStringList.Create;
  FMerges := TStringList.Create;
  FDrawings := TStringList.Create;
  FSavePictures := FSaveYAxisPictures and Assigned(SliceGrid);
  if FSavePictures then
  begin
{$IFNDEF FMX}
    FYAxisImageList := SliceGrid.YAxisZone.Images;
    FSavePictures := Assigned(SliceGrid.OnGetAxisCellImageIndex)
      and Assigned(FYAxisImageList) and (FYAxisImageList.Count > 0);
{$ENDIF}
  end;

  { file structure }

  FDocFolder := GetTempFile;
  DeleteFile(FDocFolder);
  FDocFolder := FDocFolder + '\';
  MkDir(FDocFolder);
  MkDir(FDocFolder + 'xl');
  MkDir(FDocFolder + 'xl/_rels');
  MkDir(FDocFolder + '_rels');
  MkDir(FDocFolder + 'xl/worksheets');
  MkDir(FDocFolder + 'xl/worksheets/_rels   ');
  MkDir(FDocFolder + 'xl/drawings');
  MkDir(FDocFolder + 'xl/drawings/_rels');
  MkDir(FDocFolder + 'xl/media');
  MkDir(FDocFolder + 'docProps');

  { [Content_Types].xml }

  FContentTypes := TFileStream.Create(FDocFolder + '[Content_Types].xml', fmCreate);
  with TfcxWriter.Create(FContentTypes) do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">',
      '<Default Extension="xml" ContentType="application/xml"/>',
      '<Default Extension="rels" ContentType=',
      '"application/vnd.openxmlformats-package.relationships+xml"/>',
      '<Default Extension="emf" ContentType="image/x-emf"/>',
      '<Override PartName="/xl/styles.xml" ContentType=',
      '"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
      '<Override PartName="/xl/workbook.xml" ContentType=',
      '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
      '<Override PartName="/xl/sharedStrings.xml" ContentType=',
      '"application/vnd.openxmlformats-officedocument.spreadsheetml',
      '.sharedStrings+xml"/>',
      '<Override PartName="/docProps/core.xml" ContentType="application/vnd.',
      'openxmlformats-package.core-properties+xml"/>']);

    Free;
  end;

  { _rels/.rels }

  FRels := TFileStream.Create(FDocFolder + '_rels/.rels', fmCreate);
  with TfcxWriter.Create(FRels) do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<Relationships xmlns="http://schemas.openxmlformats.org/',
      'package/2006/relationships">',
      '<Relationship Id="', WorkbookRid, '" Type="http://schemas.openxmlformats.',
      'org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>',
      '<Relationship Id="', CoreRid, '" Type="http://schemas.openxmlformats.org/package/',
      '2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>',
      '</Relationships>']);

    Free;
  end;

  { docProps/core.xml }

  with TfcxFileWriter.Create(FDocFolder + 'docProps/core.xml') do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/',
      'metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/"',
      ' xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/',
      'dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">',
      '<dc:creator>' + string(UTF8Encode(Creator)) + '</dc:creator>','</cp:coreProperties>']);

    Free;
  end;

  { xl/workbook.xml }

  FWorkbook := TFileStream.Create(FDocFolder + 'xl/workbook.xml', fmCreate);
  with TfcxWriter.Create(FWorkbook) do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<workbook xmlns="http://schemas.openxmlformats.org/',
      'spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/',
      'officeDocument/2006/relationships">',
      '<fileVersion appName="xl" lastEdited="4" lowestEdited="4"',
      ' rupBuild="4505"/>',
      '<workbookPr defaultThemeVersion="124226"/>',
      '<bookViews><workbookView xWindow="0" yWindow="0"',
      ' windowWidth="15480" windowHeight="8190" tabRatio="400" firstSheet="0"',
      ' activeTab="0"/></bookViews>',
      '<sheets>']);

    Free;
  end;

  { xl/styles.xml }

  FStyles := TFileStream.Create(FDocFolder + 'xl/styles.xml', fmCreate);
  WriteStr(FStyles, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');

  { xl/sharedStrings.xml }

  FSharedStrings := TFileStream.Create(FDocFolder + 'xl/sharedStrings.xml', fmCreate);
  WriteStr(FSharedStrings, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');

  { xl/_rels/workbook.xml.rels }

  FWorkbookRels := TFileStream.Create(FDocFolder + 'xl/_rels/workbook.xml.rels', fmCreate);
  with TfcxWriter.Create(FWorkbookRels) do
  begin
    Write(['<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
      '<Relationships xmlns="http://schemas.openxmlformats.org/',
      'package/2006/relationships">',
      '<Relationship Id="', StylesRid,
      '" Type="http://schemas.openxmlformats.org/officeDocument/2006/',
      'relationships/styles" Target="styles.xml"/>',
      '<Relationship Id="', SharedStringsRid,
      '" Type="http://schemas.openxmlformats.org/officeDocument/2006/',
      'relationships/sharedStrings" Target="sharedStrings.xml"/>']);

    Free;
  end;

  // do body export

end;

procedure TfcxXLSXExport.UpdateStyles;
begin
  { <numFmts> section in xl/styles.xml }

//  FNumFmts.Add('<numFmt numFmtId="5" formatCode=' +
//    '"#,##0&quot;R.&quot;;\-#,##0&quot;R.&quot;"/>');

// will add formats during export    
//  ExportFormats( FNumFmts );

  { <cellStyleXfs> section in xl/styles.xml }

  FCellStyleXfs.Add('<xf numFmtId="0" fontId="0" fillId="0" borderId="0">' +
    '<alignment horizontal="left" vertical="top" wrapText="1"/></xf>');

  { <cellXfs> section in xl/styles.xml }

  FCellXfs.Add('<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">' +
    '<alignment horizontal="left" vertical="top" wrapText="1"/></xf>');

  { <colors> section in xl/styles.xml }
  AddColors([$00000000, $00FFFFFF, $00FF0000, $0000FF00, $000000FF,
    $00FFFF00, $00FF00FF, $0000FFFF]);

  { <fills> section in xl/styles.xml }

  FFills.Add('<fill><patternFill patternType="none"/></fill>');
  FFills.Add('<fill><patternFill patternType="gray125"/></fill>');

  { <fonts> section in xl/styles.xml }

  FFonts.Add('<font><sz val="12"/><color rgb="FF000000"/><name val="Arial"/></font>');
end;

function TfcxXLSXExport.GetStringCell(col, row: integer; value: string; style: integer): string;
var
  k: integer;
begin
  k := AddString(String(Utf8Encode(value)));
  Result := '<c r="' + A1(col, row) + '" s="' + IntToStr(style) + '" t="s"><v>' +
            IntToStr(k) + '</v></c>';
end;

function TfcxXLSXExport.GetDateCell(col, row: integer; value: TDateTime; style: integer): string;
begin
  Result := '<c r="' + A1(col, row) + '" s="' + IntToStr(style) + '"><v>' +
            Format('%g', [VarToDateTime(Value)]) + '</v></c>';
end;

function TfcxXLSXExport.GetNumCell(col, row: integer; value: Variant; style: integer): string;
begin
  Result := '<c r="' + A1(col, row) + '" s="' + IntToStr(style) + '"><v>' +
            StringReplace(value, ',', '.', []) + '</v></c>';
end;

function TfcxXLSXExport.GetEmptyCell(col, row: integer; style: integer): string;
begin
  Result := '<c r="' + A1(col, row) + '" s="' + IntToStr(style) + '"></c>';
end;

function TfcxXLSXExport.A1(x, y: Integer): string;
begin
  Result := '';
  repeat
    Result := Chr(65 + x mod 26) + Result;
    x := x div 26;
  until x <= 25;
  if (x > 0) then
    Result := Chr(64 + x) + Result;

  Result := Result + IntToStr(y + 1);
end;

function TfcxXLSXExport.XAxisDrawProc(Sender: TfcxAxisContainer;
  const ARec: TfcxSliceDrawHeader): Boolean;
var
  Row, RowIndex, Col: integer;
  StyleIndex: Integer;
  rowstr: string;
  ylc: integer;
begin
  ylc := Slice.YAxisContainer.VisibleLevelCount;
  if ylc = 0 then
    ylc := 1;
  StyleIndex := GetStyleIndex(Styles[gsHeaderCells], ARec.Alignment, nil);
  if RepeatValues then
  begin
    RowIndex := ARec.TreeRect.Level + 1;
    rowstr := '';
    for Col := 0 to ARec.TreeRect.SizeCell - 1 do
      rowstr := rowstr + GetStringCell(ARec.TreeRect.Cell + ylc + Col, RowIndex, ARec.Text, StyleIndex);
    FRows[RowIndex] := FRows[RowIndex] + rowstr;
    for Row := 1 to ARec.TreeRect.SizeLevel - 1 do
    begin
      rowstr := '';
      for Col := 0 to ARec.TreeRect.SizeCell - 1 do
        rowstr := rowstr + GetEmptyCell(ARec.TreeRect.Cell + ylc + Col, RowIndex + Row, StyleIndex);
      FRows[RowIndex + Row] := FRows[RowIndex + Row] + rowstr;
    end;
    if ARec.TreeRect.SizeLevel > 1 then
      for Col := 0 to ARec.TreeRect.SizeCell - 1 do
      begin
        rowstr := Format('<mergeCell ref="%s"/>',
          [A1(ARec.TreeRect.Cell + ylc + Col, RowIndex)+':'+A1(ARec.TreeRect.Cell + ylc + Col, RowIndex + ARec.TreeRect.SizeLevel - 1)], SetFormat);
        FMerges.Add(rowstr);
      end;
  end
  else
  begin
    RowIndex := ARec.TreeRect.Level + 1;
    rowstr := '';
    rowstr := rowstr + GetStringCell(ARec.TreeRect.Cell + ylc, RowIndex, ARec.Text, StyleIndex);
    for Col := 1 to ARec.TreeRect.SizeCell - 1 do
      rowstr := rowstr + GetEmptyCell(ARec.TreeRect.Cell + ylc + Col, RowIndex, StyleIndex);
    FRows[RowIndex] := FRows[RowIndex] + rowstr;
    for Row := 1 to ARec.TreeRect.SizeLevel - 1 do
    begin
      rowstr := '';
      for Col := 0 to ARec.TreeRect.SizeCell - 1 do
        rowstr := rowstr + GetEmptyCell(ARec.TreeRect.Cell + ylc + Col, RowIndex + Row, StyleIndex);
      FRows[RowIndex + Row] := FRows[RowIndex + Row] + rowstr;
    end;
    if (ARec.TreeRect.SizeLevel > 1) or (ARec.TreeRect.SizeCell > 1) then
    begin
      rowstr := Format('<mergeCell ref="%s"/>',
        [A1(ARec.TreeRect.Cell + ylc, RowIndex)+':'+A1(ARec.TreeRect.Cell + ylc + ARec.TreeRect.SizeCell - 1, RowIndex + ARec.TreeRect.SizeLevel - 1)], SetFormat);
      FMerges.Add(rowstr);
    end;
  end;
  Result := False;
end;

function TfcxXLSXExport.YAxisDrawProc(Sender: TfcxAxisContainer;
  const ARec: TfcxSliceDrawHeader): Boolean;
var
  Row, RowAdd, i, ImageIndex: integer;
  StyleIndex: Integer;
  rowstr: string;
  CellInfo: TfcxCellInfo;
begin
  RowAdd := Slice.XAxisContainer.VisibleLevelCount + 1;
  if Slice.XAxisContainer.VisibleLevelCount = 0 then
    inc(RowAdd);
  StyleIndex := GetStyleIndex(Styles[gsHeaderCells], ARec.Alignment, nil);
  if FRows.Count <= (RowAdd + ARec.TreeRect.Cell + ARec.TreeRect.SizeCell - 1) then
    for i := FRows.Count to (RowAdd + ARec.TreeRect.Cell + ARec.TreeRect.SizeCell - 1) do
      FRows.Add('');

// picture
  if FSavePictures then
  begin
{$IFNDEF FMX}
    ImageIndex := -1;
    CellInfo := TfcxCellInfo.Create(nil, ARec);
    SliceGrid.OnGetAxisCellImageIndex(SliceGrid.YAxisZone, CellInfo, ImageIndex);
    CellInfo.Free;
    if ImageIndex >= 0 then
      FDrawings.Add(
           '<xdr:oneCellAnchor><xdr:from><xdr:col>' + IntToStr(ARec.TreeRect.Level) +
            '</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>' +
            IntToStr(RowAdd + ARec.TreeRect.Cell) +
            '</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>' +
            p_size +
            '<xdr:pic><xdr:nvPicPr><xdr:cNvPr id="1025"' +
            ' name="Picture 1"/><xdr:cNvPicPr><a:picLocks noChangeAspect="1"' +
            ' noChangeArrowheads="1"/></xdr:cNvPicPr></xdr:nvPicPr><xdr:blipFill>' +
            '<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/' +
            'relationships" r:embed="rId' +
            IntToStr(ImageIndex + 1) +
            '"/><a:srcRect/><a:stretch>' +
            '<a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr bwMode="auto"><a:xfrm>' +
            '<a:off x="0" y="0"/><a:ext cx="9525" cy="9525"/></a:xfrm><a:prstGeom' +
            ' prst="rect"><a:avLst/></a:prstGeom><a:noFill/></xdr:spPr></xdr:pic>' +
            '<xdr:clientData/></xdr:oneCellAnchor>');
{$ENDIF}
  end;

  if RepeatValues then
  begin
    for Row := 0 to ARec.TreeRect.SizeCell - 1 do
    begin
      rowstr := GetStringCell(ARec.TreeRect.Level, RowAdd + ARec.TreeRect.Cell + Row, ARec.Text, StyleIndex);
      for i := ARec.TreeRect.Level + 1 to ARec.TreeRect.Level + ARec.TreeRect.SizeLevel - 1 do
        rowstr := rowstr + GetEmptyCell(i, RowAdd + ARec.TreeRect.Cell + Row, StyleIndex);
      FRows[RowAdd + ARec.TreeRect.Cell + Row] := rowstr + FRows[RowAdd + ARec.TreeRect.Cell + Row];
      if ARec.TreeRect.SizeLevel > 1 then
      begin
        rowstr := Format('<mergeCell ref="%s"/>',
          [A1(ARec.TreeRect.Level,  RowAdd + ARec.TreeRect.Cell + Row)+':'+A1(ARec.TreeRect.Level + ARec.TreeRect.SizeLevel - 1,  RowAdd + ARec.TreeRect.Cell + Row)], SetFormat);
        FMerges.Add(rowstr);
      end;
    end;
  end
  else
  begin
    rowstr := GetStringCell(ARec.TreeRect.Level, RowAdd + ARec.TreeRect.Cell, ARec.Text, StyleIndex);
    for i := 1 to ARec.TreeRect.SizeLevel - 1 do
      rowstr := rowstr + GetEmptyCell(ARec.TreeRect.Level + i, RowAdd + ARec.TreeRect.Cell, StyleIndex);
    FRows[RowAdd + ARec.TreeRect.Cell] := rowstr + FRows[RowAdd + ARec.TreeRect.Cell];
    for Row := 1 to ARec.TreeRect.SizeCell - 1 do
    begin
      rowstr := '';
      for i := 0 to ARec.TreeRect.SizeLevel - 1 do
        rowstr := rowstr + GetEmptyCell(ARec.TreeRect.Level + i, RowAdd + ARec.TreeRect.Cell + Row, StyleIndex);
      FRows[RowAdd + ARec.TreeRect.Cell + Row] := rowstr + FRows[RowAdd + ARec.TreeRect.Cell + Row];
    end;
    if (ARec.TreeRect.SizeLevel > 1) or (ARec.TreeRect.SizeCell > 1) then
    begin
      rowstr := Format('<mergeCell ref="%s"/>',
        [A1(ARec.TreeRect.Level, RowAdd + ARec.TreeRect.Cell)+':'+A1(ARec.TreeRect.Level + ARec.TreeRect.SizeLevel - 1, RowAdd + ARec.TreeRect.Cell + ARec.TreeRect.SizeCell - 1)], SetFormat);
      FMerges.Add(rowstr);
    end;
  end;
  Result := False;
end;

initialization

SetFormat.DecimalSeparator := '.';

end.
