The openpyxl module allows you to manipulate Excel sheets within Python. This is also includes file creation.



In [2]:

    
import openpyxl

The Workbook() function creates an excel workbook.



In [4]:

    
wb = openpyxl.Workbook()
wb









    Out[4]:





<openpyxl.workbook.workbook.Workbook at 0x10ff1ef98>

These workbooks are created with a default sheet, called 'Sheet'.



In [25]:

    
wb.get_sheet_names()









    Out[25]:





['Sheet', 'Sheet2', 'Sheet3']



In [7]:

    
sheet = wb.get_sheet_by_name('Sheet')

All the methods covered in Lesson 42 still apply, except all the cells are empty.



In [10]:

    
sheet['A1'].value



In [11]:

    
sheet['A1'].value == None









    Out[11]:





True

We can now write values to these cells.



In [13]:

    
sheet['A1'].value = 42
sheet['A1'].value = 'Hello'

However, this spreadsheet only exists in the memory; it must be saved to be accessible in storage.



In [18]:

    
import os

# Navigate to the folder you want to save to, and confirm your path
os.chdir('files')
os.getcwd()









    Out[18]:





'/Users/vivek.menon/Dropbox/learn/practice/python/AutomateTheBoringStuffWithPython/files'

We can now use the .save() method on the Workbook object and provide a filename. Here, the filename is 'example2.xlsx'



In [20]:

    
wb.save('example2.xlsx')

It is recommended to save filenames with unique identifiers, so that they don't overwite existing files (example3, example4, etc.)

We can create new sheets with the .create_sheet() method.



In [30]:

    
sheet2 = wb.create_sheet()



In [37]:

    
wb.get_sheet_names()









    Out[37]:





['Sheet', 'Sheet2', 'Sheet3', 'Sheet21', 'My New Sheet Name']

Sheet names can be changed by editing the .title value of a sheet object, or by passing that string to the .create_sheet() method.



In [34]:

    
sheet2.title = 'My New Sheet Name'
# wb.create_sheet('My New Sheet Name')

The OpenPyXl module (openpyxl) can also create Excel spreadsheet files (.xlsx).
openpyxl.Workbook() creates a workbook object in memory.
You can save the workbook to a location using the .save() method on the workbook with a string.
You can create new sheets in the workbook using the .create_sheet() function and a string.
You can also edit this title later by editing the .title parameter of a sheet object.
You can edit any value in a cell by refering it with square brackets, like changing a list or dictionary.