Manipulating an Excel file with Python
Tags: python, excel, automation, windows
Published: 2020-05-15 21:04:01

It's one of those things that sound easy but hardly are. There are a lot of libraries and solutions out there that enable you to manipulate Excel files via code. Some of them are expensive, other are open source.

A lot of languages have a library to help you manipulate Excel files. An obvious language to choose is C#. It has a lot of options, some open source, some paying. Instead of using C#, I opted for Python. Unlike C#, Python is very light weight to start with and does not need a big IDE like Visual Studio to create a small application.

In Python there are multiple options to use. [Pandas](https://pandas.pydata.org/) and [xlsxwriter](https://xlsxwriter.readthedocs.io/) can both write to an Excel file but they both have their limitations as well. Xlsxwriter can only create new Excel files and Pandas does not have the functionality to export a sheet of the Excel file to PDF. So I opted in using the [Excel COM Object](https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model) that ships with Excel.

This comes with some prerequisites, the code will only work on Windows with Excel installed. There is a package to use COM objects called `pywin32`. It's a bit confusing, there are actually two packages, `pywin32` and `pypiwin32`. But [`pypiwin32` is abandoned an the `pywin32` package is the way to go](https://stackoverflow.com/questions/55918311/what-is-the-difference-between-pywin32-and-pypiwin32). It's installed via `pip`:

```bash
pip install pywin32
```

After installing the package, the code is fairly easy. First let's create an application, this is the main object which allows to manipulate the Excel file.

```python
from win32com import client

excelApp = client.Dispatch("Excel.Application")
```

Next let's load an excel file to manipulate.

```python
books = excelApp.Workbooks.Open(r"C:\\folder\\test.xlsx")
```

# Writing a value to Excel

Now everything is in place to read or write to the Excel file, to discover the possibilities you can always check the [COM object documentation](<https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview?redirectedfrom=MSDN](https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview?redirectedfrom=MSDN)>). Let's write a value to the Excel file and save it.

```python
from win32com import client

excelApp = client.Dispatch("Excel.Application")
book = excelApp.Workbooks.open(r"C:\\folder\\test.xlsx")

workSheet = book.Worksheets('Sheet1')
workSheet.Cells(1, 1).Value = "test"

book.Save()
book.Close()
```

This code will write the value `test` to the cell A1 in the Excel file. Notice that the `Cells` property has a 1-based index for both the row and the column.

# Generating a PDF from the Excel file

There are not a lot of libraries out there that foresee in this functionality. Using Python and the Excel COM object is one of the easiest ways to generate a PDF from an Excel file. I've used this on big Excel files and achieved very good results with it.

```python
from win32com import client

excelApp = client.Dispatch("Excel.Application")
book = excelApp.Workbooks.open(r"C:\\folder\\test.xlsx")

sheet = book.Worksheets('Sheet1')
sheet.ExportAsFixedFormat(0, r"C:\\folder\\test.pdf")

book.Close()
```

`ExportAsFixedFormat` is the method that does all the work. The first parameter is the type of format and is based on the [`XlFixedFormatType` enum](https://docs.microsoft.com/en-us/office/vba/api/excel.xlfixedformattype). The PDF will be created on the destination that is specified as second parameter.

# Performance

When the use case gets more complex you often bump into performance problems. Here are a few tips that can help you. The first and most obvious tip is to get rid of all the unused functions in your Excel file, make your Excel file as simple as possible and make sure it has no unused formulas.

## Disable display alerts

To avoid Excel being blocked by an alert that could be shown to the user we can disable display alerts. If the Excel manipulation happens in the background the alerts won't be visible anyway.

```python
import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.DisplayAlerts = false
```

## XLSB file type

Saving the Excel as the XSLB binary file type instead of the XLSX xml file type will give a small performance boost and make the file smaller.

[<img alt="Save As XSLB" src="https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/save_as_xslb.png" width="500"/>](https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/save_as_xslb.png)

## Compress images

To make the Excel file as small as possible, the images that it contains can be compressed. Go to `File > Save As` and open the `Compress Pictures` option via `Tools`. Choosing a higher compression will result in a smaller file.

[<img alt="Save As Compress Pictures Dialog" src="https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/save_as_compress_pictures.png" width="500"/>](https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_1.png)

[<img alt="Save As Compress Pictures Dialog" src="https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/save_as_compress_pictures_dialog.png" width="250"/>](https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_1.png)

## Reading and writing values using `Range`

Instead of using the `Cells` property to write values one by one, it's also possible to write an entire range to the Excel file. This will be more performant than using `Cells`. The value that is set to the Range is always an array of arrays. Let's start with a simple case

```python
workSheet.Range('A1:A5').Value = [ [ 1 ], [ 2 ], [ 3 ], [ 4 ], [ 5 ] ]
```

This will write values to the range `A1:A5`.

[<img alt="Write To Excel Using Range" src="https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_1.png" width="600"/>](https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_1.png)

To write to multiple columns a value can be added to the arrays for each column.

```python
workSheet.Range('A1:C5').Value = [ [ 1, 10, 100 ], [ 2, 20, 200 ], [ 3, 30, 300 ], [ 4, 40, 400 ], [ 5, 50, 500 ] ]
```

The result:

[<img alt="Write To Excel Using Range" src="https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_2.png" width="600"/>](https://eysermans.com/images/articles/manipulating-an-excel-file-with-python/range_test_2.png)

## Disable automatic calculation

If the Excel file you are using has a lot of formulas this might slow down the manipulation considerably. Because the formulas need to be recalculated every time a value changes in the Excel. This automatic recalculating can be disabled. This can be handy if you have a lot of values to fill out and if you are only interested in the end result. First you disable automatic recalculation, you fill out all the values and then you reenable automatic recalculation. This way the recalculation will only happen once even if a lot of values are filled out.

This code will set the calculation to manual, the possible values of the `Calculation` property can be found in the [XlCalculation enumeration](https://docs.microsoft.com/en-us/office/vba/api/excel.xlcalculation).

```python
import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.Calculation = -4135
```

To reenable the calculation set the `Calculation` property to automatic:

```python
excelApp.Calculation = -4105
```

## Turn off screen updating

Turning off screen updating can also speed up the Excel manipulation.

```python
import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.ScreenUpdating = false
```

# Call rejected by callee

This error can pop up sometimes, it usually happens when Excel is being called consecutive times and it is not fast enough to respond to the calls. Then it will return the error `Call rejected by callee`. The solution is to retry the call whenever this error occurs, most of the time Excel will have finished with the current call and it will be able to handle the next one.

To easily implement this I use a `ComWrapper` class, the inspiration I got for this class comes from several [Stackoverflow](https://stackoverflow.com/questions/11249154/call-rejected-by-callee) [questions](https://stackoverflow.com/questions/54449520/call-was-rejected-by-callee-in-win32com-if-a-dialog-box-is-open-or-excel-is-othe/55892457#55892457).

```python
import time

from pywintypes import com_error

class ComWrapper:

    @staticmethod
    def wrap(func, *func_args, **kwargs):
        try:
            return func(*func_args, **kwargs)
        except com_error as e:

            if e.strerror == 'Call was rejected by callee.':
                print('com_error retrying ', e)
                time.sleep(0.5)
                self.wrap(func, *func_args, **kwargs)

            raise
```

This class will wrap an existing method and when the pywin `com_error` with the message `Call was rejected by callee.` occurs it will wait for half a second and retry the same function with the same arguments.

If we want to wrap writing a value to a cell, this is how it is done:

```python
def writeValue(workSheet, rowNumber, columnNumber, value):
    workSheet.Cells(rowNumber, columnNumber).Value = value

ComWrapper.wrap(writeValue, workSheet, 1, 1, "test")
```

First the property to set a certain value is wrapped in the `writeValue` method. This way the method can be passed to the `ComWrapper`. The first argument for the wrapper class is the method itself. Note that we are not calling that method, we pass it to the wrapper which will call it. Then all arguments that are normally passed to the method itself are passed to wrapper as well. The wrapper will retry the method call as long as the `Call was rejected by callee.` error is thrown by Excel.