How to convert pivot table to HTML

Question

I have an Excel in xlsx format. It has two sheets, TransactionDetails and Pivot. Pivot sheet contains pivot tables generated from data in TransactionDetails sheet. How can I convert the pivot sheet into HTML?

I have tried using Apache POI and POI is unable to read cells from pivot tables (the pivot table was generated using Apache POI).

Answer 1

I was able to find a solution to the above problem. Since the pivot tables were generated by Apache POI, the excel needed to be opened first and saved for the excel to load the pivot tables from the definitions. I my case, I save also saved the excel as an xls file (Pandas had issues with reading pivot table filters generated by 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

Thereafter , I used xlrd to convert my Pivot table to 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)