Python has multiple 3rd party libraries for reading and writing Microsoft Excel spreadsheet files, including .xls and .xlsx.
For working with .xls files, there isxlrd for reading andxlwt for writing.
For working with .xlsx files, there isxlrd for reading,openpyxl for reading and writing, andXlsxWriter andPyExcelerate for writing.
To interact with the Excel application and create Python-based add-ins:xlwings,xlOil,PyXLL (commercial).
Supports reading .xls Excel files. Support for .xlsx files was removed in xlrd version 2.0.0 from Dec 2020 due to security concerns, but is still available in xlrd version 1.2.0 from Dec 2018. License: BSD.
Example:
importxlrdworkbook=xlrd.open_workbook("MySpreadsheet.xls")#for sheet in workbook.sheets(): # Loads all the sheets, unlike workbook.sheet_names()forsheetNameinworkbook.sheet_names():# Sheet iteration by nameprint("Sheet name:",sheetName)sheet=workbook.sheet_by_name(sheetName)forrownoinrange(sheet.nrows):forcolnoinrange(sheet.ncols):cell=sheet.cell(rowno,colno)print(str(cell.value))# Output as a stringifcell.ctype==xlrd.XL_CELL_DATE:dateTuple=xlrd.xldate_as_tuple(cell.value,workbook.datemode)print(dateTuple)# E.g. (2017, 1, 1, 0, 0, 0)mydate=xlrd.xldate.xldate_as_datetime(cell.value,workbook.datemode)print(mydate)# In xlrd 0.9.3print()forsheetnoinrange(workbook.nsheets):# Sheet iteration by indexsheet=workbook.sheet_by_index(sheetno)print("Sheet name:",sheet.name)fornotekeyinsheet.cell_note_map:# In xlrd 0.7.2print("Note AKA comment text:",sheet.cell_note_map[notekey].text)print(xlrd.formula.colname(1))# Column name such as A or AD, here 'B'
Links:
Supports writing .xls files. License: BSD.
Links:
Supports reading and writing .xlsx Excel files. Does not support .xls files. License: MIT.
Reading a workbook:
fromopenpyxlimportload_workbookworkbook=load_workbook("MyNewWorkbook.xlsx")forworksheetinworkbook.worksheets:print("==%s=="%worksheet.title)forrowinworksheet:# For each cell in each rowforcellinrow:print(cell.row,cell.column,cell.value)# E.g. 1 A Valueforcellinworksheet["A"]:# For each cell in column Aprint(cell.value)print(worksheet["A1"].value)# A single cellprint(worksheet.cell(column=1,row=1).value)# A1 value as well
Creating a new workbook:
fromopenpyxlimportWorkbookworkbook=Workbook()worksheet=workbook.worksheets[0]worksheet['A1']='String value'worksheet['A2']=42# Numerical valueworksheet.cell(row=3,column=1).value="New A3 Value"workbook.save("MyNewWorkbook.xlsx")# Overrides if it exists
Changing an existing workbook:
fromopenpyxlimportload_workbookworkbook_name='MyWorkbook.xlsx'workbook=load_workbook(workbook_name)worksheet=workbook.worksheets[0]worksheet['A1']="String value"workbook.save(workbook_name)
Links:
Supports writing of .xlsx files. License: BSD.
Links:
Supports writing .xlsx files. License: BSD.
Links:
Supports various operations and queries on .xls files; depends on xlrd and xlwt. License: MIT.
Links:
Supports creation of Python-based Excel add-ins. Requires Python 3.6 or later; requires Excel 2010 or later installed. Supports: global and local scope worksheet functions, ribbon customisation, custom task panes, RTD/async functions,numpy,matplotlib,pandas,jupyter. Low overhead function calls due to use of the Excel's C-API and embedded in-process Python
Examples:
Create a function to add one day to a date:
importdatetimeasdt@xloil.funcdefpyTestDate(x:dt.datetime)->dt.datetime: returnx+dt.timedelta(days=1)
Create a function which give a live ticking clock in an cell (uses RTD):
@xloil.funcasyncdefpyTestAsyncGen(secs):whileTrue:awaitasyncio.sleep(secs)yielddatetime.datetime.now()
Links:
Supports access to Windows applications via Windows Component Object Model (COM). Thus, on Windows, if Excel is installed, PyWin32 lets you call it from Python and let it do various things. You can install PyWin32 by downloading a .exe installer from SourceForge, where it is currently hosted.
Links: