我找到了解决上述问题的方法。由于数据透视表是由Apache POI生成的，因此需要首先打开并保存excel，以便excel从定义中加载数据透视表。在我的情况下，我保存也保存为xls文件的Excel（Pandas在读取POI生成的数据透视表过滤器时有问题）。

@echo off
set "wpsPath=C:\Users\CONCERTO_L221\AppData\Local\Kingsoft\WPS Office\ksolaunch.exe"  
set "filePath=D:\Automation_Workspace\ACS_Data_Sync\src\test\testdata\New_ACS MONITORING_5 PM - 6 PM_Pivot.xlsx" 
set "macroName=SaveAsXLS"  
"%wpsPath%" "%filePath%"
timeout /t 5  
echo.Set objExcel = CreateObject("Excel.Application") > SaveAsXLS.vbs
echo.Set objWorkbook = objExcel.Workbooks.Open("%filePath%") >> SaveAsXLS.vbs
echo.objWorkbook.SaveAs "%~dp0file.xls", -4143 >> SaveAsXLS.vbs
echo.objWorkbook.Close False >> SaveAsXLS.vbs
echo.objExcel.Quit >> SaveAsXLS.vbs
wscript.exe SaveAsXLS.vbs
timeout /t 2  
del SaveAsXLS.vbs
taskkill /f /im wps.exe  
exit

此后，我使用xlrd将数据透视表转换为html。

import os
import sys
import xlrd
import argparse

def excel_to_html(input_file, sheet_name, output_file):
    # Load the workbook and select the sheet
    wb = xlrd.open_workbook(input_file)
    sheet = wb.sheet_by_name(sheet_name)

    # Define cell border style
    cell_style = "border: 1px solid black;"

    def create_table(col_start, col_end):
        table_rows = []
        for row_num in range(sheet.nrows):
            html_row = ["<tr>"]
            for col_num in range(col_start, col_end + 1):
                cell_value = sheet.cell_value(row_num, col_num)
                if isinstance(cell_value, (int, float)) and cell_value.is_integer():
                    cell_value = int(cell_value)
                cell_value = "" if cell_value is None else str(cell_value)

                # Apply border conditionally
                style = ""
                if not (col_num in (5, 6) or row_num in (0, 1)):
                    if not (col_num in (7, 8) and row_num not in (2, 3, 4)):
                        style = cell_style

                # Modify borders for rows 3-5 and columns 1-5
                if row_num in (2, 3, 4) and col_num < 4:
                    style = "border-top: 1px solid black; border-bottom: 1px solid black;"

                # Apply background color conditionally
                if row_num in (2, 3, 4) and col_num < 5 or row_num == sheet.nrows - 1 and col_num < 5:
                    style += " background-color: lightSkyBlue;"

                html_row.append(f'<td style="{style}">{cell_value}</td>')
            html_row.append("</tr>")
            table_rows.append("".join(html_row))
        return table_rows

    # Create the two tables
    table1 = create_table(7, 8)
    table2 = create_table(0, 4)

    # Combine the tables with two blank rows
    combined_rows = table1 + ["<tr><td></td></tr>"] * 2 + table2

    # Wrap the combined rows in a table tag
    html = f'<table style="border-collapse: collapse; border-spacing: 0;">{"".join(combined_rows)}</table>'

    # Write to output file
    with open(output_file, "w") as f:
        f.write(html)

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Convert Excel sheet to HTML")
    parser.add_argument("input_file", help="Path to the input Excel file")
    args = parser.parse_args()

    input_file = args.input_file
    sheet_name = "pivot"
    output_folder = os.path.dirname(input_file)
    output_file = os.path.join(output_folder, "pivot.html")

    excel_to_html(input_file, sheet_name, output_file)