Worksheet Tables

[jackjindtnt]

Worksheet tables are references to groups of cells. This makes certain operations such as styling the cells in a table easier.

Creating a table from openpyxl import Workbook from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook() ws = wb.active

data = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700], ]

add column headings. NB. these must be strings

ws.append(["Fruit", "2011", "2012", "2013", "2014"]) for row in data: ws.append(row)

tab = Table(displayName="Table1", ref="A1:E5")

Add a default style with striped rows and banded columns

style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) wb.save("table.xlsx") By default tables are created with a header from the first row and filters for all the columns.

Styles are managed using the the TableStyleInfo object. This allows you to stripe rows or columns and apply the different colour schemes.

Important notes Table names must be unique within a workbook and table headers and filter ranges must always contain strings. If this is not the case then Excel may consider the file invalid and remove the table

[jackjindtnt]

You can add data validation to a workbook but currently cannot read existing data validation.

Examples

from openpyxl import Workbook from openpyxl.worksheet.datavalidation import DataValidation

Create the workbook and worksheet we'll be working with

wb = Workbook() ws = wb.active

Create a data-validation object with list validation

dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)

Optionally set a custom error message

dv.error ='Your entry is not in the list' dv.errorTitle = 'Invalid Entry'

Optionally set a custom prompt message

dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection'

Add the data-validation object to the worksheet

ws.add_data_validation(dv)

Create some cells, and add them to the data-validation object

c1 = ws["A1"] c1.value = "Dog" dv.add(c1) c2 = ws["A2"] c2.value = "An invalid value" dv.add(c2)

Or, apply the validation to a range of cells

dv.ranges.append('B1:B1048576')

Write the sheet out. If you now open the sheet in Excel, you'll find that

the cells have data-validation applied.

wb.save("test.xlsx") Other validation examples Any whole number:

dv = DataValidation(type="whole") Any whole number above 100:

dv = DataValidation(type="whole", operator="greaterThan", formula1=100) Any decimal number:

dv = DataValidation(type="decimal") Any decimal number between 0 and 1:

dv = DataValidation(type="decimal", operator="between", formula1=0, formula2=1) Any date:

dv = DataValidation(type="date") or time:

dv = DataValidation(type="time") Any string at most 15 characters:

dv = DataValidation(type="textLength", operator="lessThanOrEqual"), formula1=15) Cell range validation:

from openpyxl.utils import quote_sheetname dv = DataValidation(type="list", formula1="{0}!$B$1:$B$10".format(quote_sheetname(sheetname)) ) Custom rule:

dv = DataValidation(type="custom", formula1"=SOMEFORMULA")