Create, read, write Excel files in Python
GemBox.Spreadsheet is a .NET library for processing Excel files, but since it's also a COM-accessible library, you can use it in Python. To use GemBox.Spreadsheet in Python, you'll need to: This example demonstrates how to create a new Excel file using Python and write spreadsheet data to its cells. It also showcases how to read an Excel file and modify its cells. Not all members of GemBox.Spreadsheet are accessible because of the COM limitations like unsupported static and overload methods. That is why you can use the However, if you need to use many GemBox.Spreadsheet members from Python, a recommended approach is to create a .NET wrapper library instead. Your wrapper library should do all the work within and exposes a minimal set of classes and methods to the unmanaged code. This will enable you to take advantage of GemBox.Spreadsheet's full capabilities, avoid any COM limitations, and improve performance by reducing the number of COM Callable Wrappers created at runtime.System Requirements
:: Add GemBox.Spreadsheet to COM registry for x86 (32-bit) applications.
C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe [path to installed GemBox.Spreadsheet.dll]
:: Add GemBox.Spreadsheet to COM registry for x64 (64-bit) applications.
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe [path to installed GemBox.Spreadsheet.dll]:: Install Python extension for Windows.
pip install pywin32Working with Excel files in Python
import os
import win32com.client as COM
# Create ComHelper object.
comHelper = COM.Dispatch("GemBox.Spreadsheet.ComHelper")
# If using the Professional version, put your serial key below.
comHelper.ComSetLicense("FREE-LIMITED-KEY")
####################
### Create Excel ###
####################
# Create new ExcelFile object.
workbook = COM.Dispatch("GemBox.Spreadsheet.ExcelFile")
# Add new ExcelWorksheet object.
worksheet = workbook.Worksheets.Add("Sheet1")
# Set width and format of column "A".
columnA = comHelper.GetColumn(worksheet, 0)
columnA.Width = 20 * 256
columnA.Style.Font.Weight = 700
# Set values of cells "A1", "A2", "A3" and "A4".
columnA.Cells.Item(0).Value = "John Doe"
columnA.Cells.Item(1).Value = "Bob Garvey"
columnA.Cells.Item(2).Value = "Ben Stilwell"
columnA.Cells.Item(3).Value = "Peter Pan"
# Set values of cells "B1", "B2", "B3" and "B4".
columnB = comHelper.GetColumn(worksheet, 1)
columnB.Cells.Item(0).Value = 1000
columnB.Cells.Item(1).Value = 2000
columnB.Cells.Item(2).Value = 3000
columnB.Cells.Item(3).Value = 4000
# Create new Excel file.
workbook.Save(os.getcwd() + "\\New.xlsx")
##################
### Read Excel ###
##################
# Read existing Excel file.
book = comHelper.Load(os.getcwd() + "\\New.xlsx")
# Get first Excel sheet.
sheet = book.Worksheets.Item(0)
# Get first Excel row.
row1 = comHelper.GetRow(sheet, 0)
# Display values of cells "A1" and "B1".
print("Cell A1:" + str(row1.Cells.Item(0).Value))
print("<br>")
print("Cell B1:" + str(row1.Cells.Item(1).Value))
####################
### Update Excel ###
####################
# Update values of cells "A1" and "B1".
row1.Cells.Item(0).Value = "Jane Doe"
row1.Cells.Item(1).Value = 2000
# Write the updated Excel file.
book.Save(os.getcwd() + "\\Updated.xlsx")
Wrapper Library
ComHelper class which provides alternatives for some members that cannot be called with COM Interop.
