Python openpyxl
From wikinotes
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 numberCells
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 CellFonts/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
Bordersimport 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
CellTypeswsheet['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 )