unit uContasolNativeXlsx;

interface

uses
  System.SysUtils, System.Classes, System.IOUtils, System.Zip, System.Math,
  System.Generics.Collections;

type
  TXlsxCellType = (xctText, xctInteger, xctDecimal, xctDate);

  TXlsxCell = record
    CellType: TXlsxCellType;
    TextVal: string;
    IntVal: Int64;
    FloatVal: Double;
  end;

  TNativeXlsxTable = class
  private
    FTableName: string;
    FHeaders: TArray<string>;
    FRows: TList<TArray<TXlsxCell>>;
    function ColIndexToLetter(ACol: Integer): string;
    function EscapeXml(const S: string): string;
    function FloatToXmlStr(const Value: Double): string;
    function BuildSheetXml: string;
  public
    constructor Create(const ATableName: string; const AHeaders: TArray<string>);
    destructor Destroy; override;
    procedure AddRow(const ACells: array of TXlsxCell);
    function SaveToFile(const AFilePath: string): Boolean;
  end;

function XText(const AVal: string): TXlsxCell;
function XInt(AVal: Int64): TXlsxCell;
function XFloat(AVal: Double): TXlsxCell;
function XDate(const AVal: string): TXlsxCell;

implementation

function XText(const AVal: string): TXlsxCell;
begin
  Result.CellType := xctText;
  Result.TextVal := AVal;
  Result.IntVal := 0;
  Result.FloatVal := 0.0;
end;

function XInt(AVal: Int64): TXlsxCell;
begin
  Result.CellType := xctInteger;
  Result.TextVal := '';
  Result.IntVal := AVal;
  Result.FloatVal := 0.0;
end;

function XFloat(AVal: Double): TXlsxCell;
begin
  Result.CellType := xctDecimal;
  Result.TextVal := '';
  Result.IntVal := 0;
  Result.FloatVal := AVal;
end;

function XDate(const AVal: string): TXlsxCell;
begin
  Result.CellType := xctDate;
  Result.TextVal := AVal;
  Result.IntVal := 0;
  Result.FloatVal := 0.0;
end;

{ TNativeXlsxTable }

constructor TNativeXlsxTable.Create(const ATableName: string; const AHeaders: TArray<string>);
begin
  inherited Create;
  FTableName := ATableName;
  FHeaders := AHeaders;
  FRows := TList<TArray<TXlsxCell>>.Create;
end;

destructor TNativeXlsxTable.Destroy;
begin
  FRows.Free;
  inherited Destroy;
end;

procedure TNativeXlsxTable.AddRow(const ACells: array of TXlsxCell);
var
  LRow: TArray<TXlsxCell>;
  I: Integer;
begin
  SetLength(LRow, Length(ACells));
  for I := 0 to High(ACells) do
    LRow[I] := ACells[I];
  FRows.Add(LRow);
end;

function TNativeXlsxTable.ColIndexToLetter(ACol: Integer): string;
var
  LRem: Integer;
begin
  Result := '';
  while ACol > 0 do
  begin
    LRem := (ACol - 1) mod 26;
    Result := Chr(65 + LRem) + Result;
    ACol := (ACol - 1) div 26;
  end;
end;

function TNativeXlsxTable.EscapeXml(const S: string): string;
begin
  Result := S;
  Result := StringReplace(Result, '&', '&amp;', [rfReplaceAll]);
  Result := StringReplace(Result, '<', '&lt;', [rfReplaceAll]);
  Result := StringReplace(Result, '>', '&gt;', [rfReplaceAll]);
  Result := StringReplace(Result, '"', '&quot;', [rfReplaceAll]);
  Result := StringReplace(Result, '''', '&apos;', [rfReplaceAll]);
end;

function TNativeXlsxTable.FloatToXmlStr(const Value: Double): string;
var
  FS: TFormatSettings;
begin
  FS := TFormatSettings.Create('en-US');
  FS.DecimalSeparator := '.';
  Result := FormatFloat('0.00', Value, FS);
end;

function TNativeXlsxTable.BuildSheetXml: string;
var
  SB: TStringBuilder;
  R, C: Integer;
  LRowIdx: Integer;
  LColRef: string;
  LCell: TXlsxCell;
begin
  SB := TStringBuilder.Create(1024 * 64);
  try
    SB.Append('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10);
    SB.Append('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'#10);
    SB.Append('<sheetData>'#10);

    // Fila 1: Cabeceras
    SB.Append('  <row r="1">'#10);
    for C := 0 to High(FHeaders) do
    begin
      LColRef := ColIndexToLetter(C + 1) + '1';
      SB.AppendFormat('    <c r="%s" t="inlineStr" s="1"><is><t>%s</t></is></c>'#10,
        [LColRef, EscapeXml(FHeaders[C])]);
    end;
    SB.Append('  </row>'#10);

    // Filas de datos
    for R := 0 to FRows.Count - 1 do
    begin
      LRowIdx := R + 2;
      SB.AppendFormat('  <row r="%d">'#10, [LRowIdx]);
      for C := 0 to High(FRows[R]) do
      begin
        LColRef := ColIndexToLetter(C + 1) + IntToStr(LRowIdx);
        LCell := FRows[R][C];
        case LCell.CellType of
          xctText, xctDate:
            SB.AppendFormat('    <c r="%s" t="inlineStr"><is><t>%s</t></is></c>'#10,
              [LColRef, EscapeXml(LCell.TextVal)]);
          xctInteger:
            SB.AppendFormat('    <c r="%s"><v>%d</v></c>'#10,
              [LColRef, LCell.IntVal]);
          xctDecimal:
            SB.AppendFormat('    <c r="%s" s="2"><v>%s</v></c>'#10,
              [LColRef, FloatToXmlStr(LCell.FloatVal)]);
        end;
      end;
      SB.Append('  </row>'#10);
    end;

    SB.Append('</sheetData>'#10);
    SB.Append('</worksheet>');
    Result := SB.ToString;
  finally
    SB.Free;
  end;
end;

function TNativeXlsxTable.SaveToFile(const AFilePath: string): Boolean;
var
  LTempDir: string;
  LGuidStr: string;
  LContentTypes, LRels, LWbRels, LWorkbook, LStyles, LSheetXml: string;
  LFileXls: string;
begin
  LGuidStr := TGUID.NewGuid.ToString.Replace('{', '').Replace('}', '').Replace('-', '');
  LTempDir := IncludeTrailingPathDelimiter(TPath.GetTempPath) + 'xlsx_gen_' + LGuidStr;

  try
    TDirectory.CreateDirectory(LTempDir);
    TDirectory.CreateDirectory(LTempDir + '\_rels');
    TDirectory.CreateDirectory(LTempDir + '\xl');
    TDirectory.CreateDirectory(LTempDir + '\xl\_rels');
    TDirectory.CreateDirectory(LTempDir + '\xl\worksheets');

    // 1. [Content_Types].xml
    LContentTypes :=
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10 +
      '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'#10 +
      '  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'#10 +
      '  <Default Extension="xml" ContentType="application/xml"/>'#10 +
      '  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'#10 +
      '  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'#10 +
      '  <Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'#10 +
      '  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'#10 +
      '</Types>';
    TFile.WriteAllText(LTempDir + '\[Content_Types].xml', LContentTypes, TEncoding.UTF8);

    // 2. _rels/.rels
    LRels :=
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10 +
      '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'#10 +
      '  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'#10 +
      '</Relationships>';
    TFile.WriteAllText(LTempDir + '\_rels\.rels', LRels, TEncoding.UTF8);

    // 3. xl/_rels/workbook.xml.rels
    LWbRels :=
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10 +
      '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'#10 +
      '  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'#10 +
      '  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>'#10 +
      '  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'#10 +
      '</Relationships>';
    TFile.WriteAllText(LTempDir + '\xl\_rels\workbook.xml.rels', LWbRels, TEncoding.UTF8);

    // 4. xl/workbook.xml (Incluye Hoja1 y la pestaña con el nombre de la tabla)
    LWorkbook := Format(
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10 +
      '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'#10 +
      '  <sheets>'#10 +
      '    <sheet name="Hoja1" sheetId="1" r:id="rId1"/>'#10 +
      '    <sheet name="%s" sheetId="2" r:id="rId2"/>'#10 +
      '  </sheets>'#10 +
      '</workbook>', [EscapeXml(FTableName)]);
    TFile.WriteAllText(LTempDir + '\xl\workbook.xml', LWorkbook, TEncoding.UTF8);

    // 5. xl/styles.xml
    LStyles :=
      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'#10 +
      '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'#10 +
      '  <numFmts count="1">'#10 +
      '    <numFmt numFmtId="164" formatCode="#,##0.00"/>'#10 +
      '  </numFmts>'#10 +
      '  <fonts count="2">'#10 +
      '    <font><name val="Segoe UI"/><sz val="10"/></font>'#10 +
      '    <font><b/><name val="Segoe UI"/><sz val="10"/></font>'#10 +
      '  </fonts>'#10 +
      '  <fills count="2">'#10 +
      '    <fill><patternFill patternType="none"/></fill>'#10 +
      '    <fill><patternFill patternType="gray125"/></fill>'#10 +
      '  </fills>'#10 +
      '  <borders count="1">'#10 +
      '    <border><left/><right/><top/><bottom/><diagonal/></border>'#10 +
      '  </borders>'#10 +
      '  <cellStyleXfs count="1">'#10 +
      '    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>'#10 +
      '  </cellStyleXfs>'#10 +
      '  <cellXfs count="3">'#10 +
      '    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>'#10 +
      '    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="1"/>'#10 +
      '    <xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyNumberFormat="1"/>'#10 +
      '  </cellXfs>'#10 +
      '  <cellStyles count="1">'#10 +
      '    <cellStyle name="Normal" xfId="0" builtinId="0"/>'#10 +
      '  </cellStyles>'#10 +
      '</styleSheet>';
    TFile.WriteAllText(LTempDir + '\xl\styles.xml', LStyles, TEncoding.UTF8);

    // 6. xl/worksheets/sheet1.xml y sheet2.xml
    LSheetXml := BuildSheetXml;
    TFile.WriteAllText(LTempDir + '\xl\worksheets\sheet1.xml', LSheetXml, TEncoding.UTF8);
    TFile.WriteAllText(LTempDir + '\xl\worksheets\sheet2.xml', LSheetXml, TEncoding.UTF8);

    // 7. Empaquetar a .xlsx final
    if FileExists(AFilePath) then
      TFile.Delete(AFilePath);

    TZipFile.ZipDirectoryContents(AFilePath, LTempDir);

    // 8. Crear también copia .xls por compatibilidad
    LFileXls := ChangeFileExt(AFilePath, '.xls');
    if FileExists(LFileXls) then
      TFile.Delete(LFileXls);
    TFile.Copy(AFilePath, LFileXls, True);

    Result := FileExists(AFilePath);
  finally
    try
      if TDirectory.Exists(LTempDir) then
        TDirectory.Delete(LTempDir, True);
    except
      // Silenciar limpieza de temporales
    end;
  end;
end;

end.
