Excel Sheet Save as HTML File

 var XLApp, Sheet, Data, vWorkBook: OleVariant;
  vProcedureData:OleVariant;
  i, iTitleLine, iSheetIndex, iRowCount, iColCount:Integer;
  sSampleFile, sFileAlias, sFileName, sBeginCell, sEndCell, sFormulaBeginCell, sFormulaEndCell:String;
  sRange, sSheetName:String;
begin
  Result := '';
  vProcedureData := UnitLib.fn_Get_Data_Array_From_Attach_Procedure;
  iTitleLine := DM1.adoAutoMail.FieldByName('TitleLine').AsInteger;
  sSampleFile := DM1.adoAutoMail.FieldByName('SampleFile').AsString;
  sFileAlias := UnitLib.fn_Get_AutoMail_FieldValue('FileAlias');
  iSheetIndex := DM1.adoAutoMail.FieldByName('SheetIndex').AsInteger;
  //
  XLApp := CreateOleObject('Excel.Application');
  try
    XLApp.Visible := False;
    XLApp.DisplayAlerts := False; //關掉警告訊息
    XLApp.WorkBooks.Open(sSampleFile);
    vWorkBook := XLApp.Workbooks[1];
    Sheet := vWorkBook.Worksheets[iSheetIndex];
    //
    sSheetName := Sheet.Name;
    iRowCount := DM1.adoAttach_Procedure.RecordCount;
    iColCount := DM1.adoAttach_Procedure.FieldCount;
    sBeginCell := RefToCell(iTitleLine+1, 1);
    sEndCell := RefToCell(iTitleLine+iRowCount+1, iColCount);
    Sheet.Range[sBeginCell, sEndCell].Value := vProcedureData;
    //SubTotal Row
    for i := 0 to iColCount-1 do
    begin
      if fn_IsNumericField(DM1.adoAttach_Procedure.Fields[i]) then
      begin
        sBeginCell := RefToCell(iTitleLine+iRowCount+1, i+1);
        sEndCell := RefToCell(iTitleLine+iRowCount+1, i+1);
        sFormulaBeginCell := RefToCell(iTitleLine+1, i+1);
        sFormulaEndCell := RefToCell(iTitleLine+iRowCount, i+1);
        Sheet.Range[sBeginCell, sEndCell].Formula := '=SUM('+sFormulaBeginCell+':'+sFormulaEndCell+')';
      end;
    end;
    //Save as Xlsx
    sFileName := ChangeFileExt(sFileAlias, '.xlsx'); //xlOpenXMLWorkbook(51) Xlsx Format / xlHtml
    vWorkBook.SaveAs(sFileName);
    Result := sFileName;

    //Save Sheet to Html
    sFileName := ChangeFileExt(sFileAlias, '.html');
    sBeginCell := RefToCell(1, 1);
    sEndCell := RefToCell(iTitleLine+iRowCount+1, iColCount);
    sRange := sBeginCell+':'+sEndCell;
    vWorkBook.PublishObjects.Add(xlSourceRange, sFileName, sSheetName, sRange, xlHtmlStatic).Publish(True);
  finally
    if not VarIsEmpty(XLApp) then
    begin
      vWorkBook.Close;
      XLApp.Quit;
      XLAPP := Unassigned;
      Sheet := Unassigned;
      Application.ProcessMessages;
    end;
  end;
end;