Python Excel

Home   openpyxl Tutorial   Contact us

Deleting a sheet in Openpyxl:


In Openpyxl if you delete a sheet, you have to be a little careful as it is not recoverable. Hence you should know what are you deleting and why? Its always better to know about  different sheets present in a workbook, and then delete one or more worksheets. The process however is very simple. When you have to delete a sheet in Openpyxl simply follow these steps.

  1. Load workbook in to memory.
  2. See the sheets in workbook.
  3. Assign a reference to sheet which you want to delete.
  4. Delete  the specific sheet.
  5. Save workbook. 
  6. Test if sheet is deleted.

If you want to Read, Write and Manipulate(Copy, cut, paste, delete or search for an item etc) Excel files in Python with simple and practical examples I will suggest you to see this simple and to the point Python Excel Openpyxl Course with examples about how to deal with MS Excel files in Python. This video course teaches efficiently how to manipulate excel files and automate tasks. Moreover, you will also get free help in writing Python code for your Excel tasks.

Everything you do in Microsoft Excel, can be automated with Python. So why not use the power of Python and make your life easy. You can make intelligent and thinking Excel sheets, bringing the power of logic and thinking of Python to Excel which is usually static, hence bringing flexibility in Excel and a number of opportunities.


Example of Openpyxl delete sheet:


We create a new xlsx file and name as "testdel.xlsx" in our python directory. After that start writing the following code in Python shell.


Python Excel Training

>>> import openpyxl

>>> workbook=openpyxl.load_workbook('testdel.xlsx')

>>> workbook.get_sheet_names()

['Sheet1', 'Sheet2', 'Sheet3']

>>> std=workbook.get_sheet_by_name('Sheet2')

>>> workbook.remove_sheet(std)

>>> workbook.get_sheet_names()

['Sheet1', 'Sheet3']

>>> workbook.save('testdel.xlsx')

>>> 




Explanation of How you delete a sheet:



First go to your python folder and create a new MS Excel file there. Name it as 'testdel.xlsx' the file will have three sheets by default. Please note that the file will be empty and hence it will not delete any of your important Excel data. When you practice with this test file, you can proceed to deleting a sheet from your actual Excel Workbook.



>>> import openpyxl

If you want to work with openpyxl, you have to import it. And before importing you have to install it.


>>> workbook=openpyxl.load_workbook('testdel.xlsx')


Next step is that you load the particular Excel file or workbook in to memory from which you want to delete a sheet. In this statement a file 'testdel.xlsx' is loaded in to memory and we have created a reference workbook to that file. We can access anything of this file with this reference.
if everything goes fine. you will get a prompt ready for writing further code.


>>> workbook.get_sheet_names()


Its better to see the sheet names in our workbook. Python provides us the number of sheets with their names without any objection as


['Sheet1', 'Sheet2', 'Sheet3']

Up to here, we have loaded an Excel file in memory and checked its sheets. Now we refer to the particular sheet we want to delete in this Excel Workbook. In order to do that we will have to create a reference to that specific sheet. Lets suppose you want to delete 'Sheet2'. You create a reference object to that sheet.


>>> std=workbook.get_sheet_by_name('Sheet2')


std is the reference you have created to the sheet which needs to be deleted. Sheet is deleted with this reference here, not with its name.


>>> workbook.remove_sheet(std)



This line of code deletes the sheet pointed to by the reference std. In simple words Sheet2 is deleted.

>>> workbook.get_sheet_names()

['Sheet1', 'Sheet3']

If you check sheets in workbook again you will see that there are only two sheets now. Sheet2 is deleted. All the work is done in memory, you have to save the file so that change is permanent. The following code serves the purpose of "Save As". You can write a different file name so that you have  original file and the one with changes. 

>>> workbook.save('testdel.xlsx')



Do you have to automate excel tasks, or you want python to read Microsoft Excel, csv files, search, match sort data and write in a new Excel file you may please click here to contact us.

We also provide 1 to 1 live online Training at Skype for Python.


For Suggestions or questions please Contact us