How to convert xls to xlsx with Python Azure Webjob

Question

I need to be able to convert xls into xlsx Files with Python and run the script as an Azure Webjob. I'm able to do the job on my local machine with the following code:

import win32com.client as win32
import os

def xls_2_xlsx(xls_path, xlsx_path):
    # Create temp xlsx-File
    if os.path.exists(xlsx_path): os.remove(xlsx_path)

    excel = win32.DispatchEx("Excel.Application")
    excel.Visible = 0
    wb = excel.Workbooks.Open(xls_path)

    wb.SaveAs(xlsx_path, FileFormat = 51)    #FileFormat = 51 is for .xlsx extension
    wb.Close()

When running the code as an Azure Webjob i get the following error:

pywintypes.com_error: (-2147221005, 'Invalid class string', None, None)

Probably due to the fact that there is (obviously) no Excel installed on the Azure Webjob machine.

I tried other approaches to convert the files as described here: how to convert xls to xlsx

Unfortunately some of the xls-files have cell-values which start with a "+"-sign an get interpreted as a formula, which results in an error. When converting the xls-file cell by cell, the actual value of those cells get lost.

Any help to achieve this task with python as an Azure WebJob would be very appreciated.

score 0 · Accepted Answer · answered Mar 07 '19 at 09:07

On Azure WebApps for Windows, due to there is a secure environment called Azure Web App Sandbox which will restricts many operations include Win32k.sys (User32/GDI32) Restrictions. So you can not call any COM components via win32com that cause your issue.

In pure Python, there are many soultions to convert xls to xlsx, such as the common two as below.

Install pyexcel via pip install pyexcel pyexcel-xls pyexcel-xlsx to do it.

import pyexcel as p

p.save_book_as(file_name='<your input file>.xls', dest_file_name='<your output file>.xlsx')

Install pandas via pip install pandas to do it.

import pandas as pd

dataFrame = pd.read_excel('<your input file>.xls')
dataFrame.to_excel('<your output file>.xlsx', index=False)

Note: the two solutions above just can convert these xls files of common data types which like string, number, not rich content (chart or image, etc.)

If you want to convert a xls file with rich content or without any format changes via win32com with Excel.Application, you have to use Azure Windows VM to run your script and integrate with your WebApp.

Perfect - your first solution seems to work like a charm! Thanks a lot. — LordRA, Mar 12 '19 at 13:44

How to convert xls to xlsx with Python Azure Webjob

1 Answers1