1. Products
  2.   Spreadsheet
  3.   Python
  4.   Openpyxl
 
  

Open Source Python API for Spreadsheets

Python library to Read & Write Excel 2010 xlsx/xlsm files.

Openpyxl is an open source python API for reading & writing Microsoft Excel 2010 (XLSX/XLSM/XLTX/XLTM) file formats. The API allows the Python developer to read existing Excel files, create a new workbook, use number formats, use formulas, merge & un-merge cells, insert images and fold columns. Furthermore, the API allows you to manipulate your workbook in memory, and there is no need to create a file on the file system.

The API is rich with features for manipulating Microsoft Excel file format programmatically. You can add charts, add/load & save comments, work with styles, tables and more.

Previous Next

Getting Started with Openpyxl

The recommended way to install openpyxl is via PIP. Please use the following command to install Openpyxl.

Install Openpyxl via PIP Command

pip install openpyxl

Manipulating Excel Workbooks via Free Python API

Openpyxl API allows reading & writing Microsoft Excel 2010 file formats using Python. The developers can easily open existing workbook using load_workbook() method and create a new workbook using workbook() method. You can use numbers formats, use formulas, merge & unmerged cells. insert images and more.

Add Comments to Excel Cell via Python API

from openpyxl import Workbook
from openpyxl.comments import Comment
wb = Workbook()
ws = wb.active
comment = ws["A1"].comment
comment = Comment('This is the comment text', 'Comment Author')
comment.text
    'This is the comment text'
comment.author
    'Comment Author'

Add Charts is Excel using Free Python API

The Open Source spreadsheet library Openpyxl allows adding a wide range of charts in your excel files programmatically. You can add 2D Area Charts, 3D Area Charts, Vertical/Horizontal & Stacked Bar Charts, 3D Bar Charts, Bubble Charts, Line Charts, 3D Line Charts, Scatter Charts, Pie Charts, Projected Pie Charts, 3D Pie Charts, Doughnut Charts, Radar Charts, Stock Charts, and Surface Charts.

Creating Charts is Excel via Python API

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
for i in range(10):
     ws.append([i])

from openpyxl.chart import BarChart, Reference, Series
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E15")
wb.save("SampleChart.xlsx")

Style Excel Spreadsheets using Python

Styles are very important part of a the way content are going to be appear on the screen and can be used to change the look of your data. The open source Openpyxl library allows developers the capability to style Excel Spreadsheets programmatically. Using the API, you can set font, font size, color, underlining, set borders, align cells, and more. The library has provided various functions for working with styles, such as Creating a Named Style, Copying styles, apply Cell Styles, apply Named Styles, Styling Merged Cells, Edit Page Setup and many more.

Apply Styles to Worksheet Cell via Python API

from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl import Workbook
wb = Workbook()
ws = wb.active

a1 = ws['A1']
d4 = ws['D4']
ft = Font(color="FF0000")
a1.font = ft
d4.font = ft

a1.font.italic = True # is not allowed # doctest: +SKIP

# If you want to change the color of a Font, you need to reassign it::

a1.font = Font(color="FF0000", italic=True) # the change only affects A1
 English