Python openpyxl

openpyxl allows you to read/write to xlsx files. Charts/Images can be written, but they are lost on open/close if they are not rewritten.

Example

import openpyxl

wbook  = openpyxl.Workbook()
wsheet = wbook.active
for i in xrange(10):							## row
	wsheet.append([ 'A','B','C' ])		## column-values in row

wbook.save( filename='/tmp/myfile.xlsx')

Syntax

Workbook

import openpyxl

wbook  = openpyxl.load_workbook( filename = filepath )	## Open Existing Excel File
wbook  = openpyxl.Workbook()										## Create New Excel File
wbook.save( filename = filepath )

Worksheet

import openpyxl

wsheet = wbook.active												         ## Select Worksheet
wsheet.title = 'first worksheet'									         ## Worksheet Title
wsheet.column_dimensions['B'].width = len( wsheet['B3'].value )	## Resize Column

wsheet.dimensions																	## A2:D2	Better Dimensions
wsheet.max_row, wsheet.max_column											## last row/column
wsheet.cell(1,1).value															## You can also work with coordinates
openpyxl.cell.get_column_letter( 4 )										## Column_letter from number

Cells

Values

import openpyxl

wsheet.append(['firstrow'  , 'a' , 'b'])						## 'append' adds to the end of the worksheet
wsheet.append(['secondrow' , 'a' , 'b'])

wsheet['B2'] = 'Write B2 Column'									## Write Specific cell
wsheet['C2'] = datetime.datetime.now()							## Native Datetime
wsheet['D2'] = '=SUM(1,1)'											## Excel Formulas
wsheet['B2'].value													## Value of cell
var = wsheet['B2']                                       ## Copy Cell

Fonts/Colours/Styles/Alignment

import openpyxl

align_left = openpyxl.styles.Alignment(
		horizontal='left'
)
font = openpyxl.styles.Font(
		italic = True,
		bold   = True,
		color  = openpyxl.styles.colors.RED,
	)

wsheet['A1'].font = font	
wsheet['A1'].alignment = align_left

Borders

import openpyxl

border_line = openpyxl.styles.Side( border_style='thin', color='FF000000' )

border = openpyxl.styles.Border(
						left   = border_line,
						right  = border_line,
						bottom = border_line,
						top    = border_line,
					)

wsheet['A1'].border = border

CellTypes

wsheet['A1'].number_format = '0.00'

Charts

This isn't entirely complete. I don't think charts will be very useful to me.

import openpyxl

valuesA = openpyxl.chart.Reference( wsheet, last_row+1,1, last_row+11,1)
seriesA = openpyxl.chart.Series( valuesA, title='Data1' )

valuesB = openpyxl.chart.Reference( wsheet, last_row+1,2, last_row+11,2)
seriesB = openpyxl.chart.Series( valuesB, title='Data2' )

chart  = openpyxl.chart.BarChart()
chart.append( seriesA )
chart.append( seriesB )
wsheet.add_chart( chart )