How do I save Excel Sheet as HTML in Python?

Question

I'm working with this library XlsxWriter.

I've opened a workbook and written some stuff in it (considering the official example) -

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

I've gone through the docs rigorously but can't seem to find the save as functionality.

Is there a way (any way) to save the workbook as a HTML file?

If it isn't possible from python code, can I somehow write VBA code and call that code from python?

Answer 1

You can use win32com.client to call a VBA macro. Assuming your file is named Bar...

VBA:

Sub SaveHTML()
ThisWorkbook.SaveAs Filename:="C:\Foo\Bar.htm", FileFormat:=xlHtml
End Sub

Python:

from win32com.client import Dispatch

xl = Dispatch('Excel.Application')
xl.Workbooks.Open('C:\Foo\Bar.xlsx')
#xl.Visible = True -- optional
xl.Application.Run("SaveHTML")
xl.Workbooks.Close

Modify as necessary.

EDIT: I forgot to add, using win32com to close Excel is an absolute pain. The workbooks will close, but the application itself will linger (check Task Manager). Please refer to this SO post on a workaround for this.

Answer 2

You can save to HTML from Python directly then attach to email:

from win32com.client.gencache import EnsureDispatch
from win32com.client import constants

yourExcelFile = ...
newFileName = ...

xl = EnsureDispatch('Excel.Application')
wb = xl.Workbooks.Open(yourExcelFile)
wb.SaveAs(newFileName, constants.xlHtml)
xl.Workbooks.Close()
xl.Quit()
del xl