Loading an Excel Workbook In Python and Creating/Removing Sheets

Python 3: Automating Your Job Tasks Superhero Level: Automate Excel Tasks with Python 3
5 minutes
Share the link to this page
You need to have access to the item to view this lesson.
One-time Fee
List Price:  $139.99
You save:  $40
List Price:  €128.67
You save:  €36.76
List Price:  £110.19
You save:  £31.48
List Price:  CA$190.60
You save:  CA$54.46
List Price:  A$208.90
You save:  A$59.69
List Price:  S$188.42
You save:  S$53.84
List Price:  HK$1,092.29
You save:  HK$312.10
CHF 90.89
List Price:  CHF 127.25
You save:  CHF 36.36
NOK kr1,073.95
List Price:  NOK kr1,503.58
You save:  NOK kr429.62
DKK kr686.39
List Price:  DKK kr960.97
You save:  DKK kr274.58
List Price:  NZ$228.20
You save:  NZ$65.20
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  ৳16,391.62
You save:  ৳4,683.65
List Price:  ₹11,661.24
You save:  ₹3,332.02
List Price:  RM656.20
You save:  RM187.50
List Price:  ₦205,785.30
You save:  ₦58,800
List Price:  ₨38,789.74
You save:  ₨11,083.57
List Price:  ฿5,074.35
You save:  ฿1,449.91
List Price:  ₺4,513.75
You save:  ₺1,289.73
List Price:  B$714.59
You save:  B$204.18
List Price:  R2,530.23
You save:  R722.97
List Price:  Лв251.81
You save:  Лв71.95
List Price:  ₩189,601.05
You save:  ₩54,175.60
List Price:  ₪518.60
You save:  ₪148.18
List Price:  ₱8,079.59
You save:  ₱2,308.62
List Price:  ¥21,792.94
You save:  ¥6,227
List Price:  MX$2,325.03
You save:  MX$664.34
List Price:  QR507.87
You save:  QR145.11
List Price:  P1,896.68
You save:  P541.94
List Price:  KSh18,338.69
You save:  KSh5,240
List Price:  E£6,566.93
You save:  E£1,876.40
List Price:  ብር8,018.62
You save:  ብር2,291.20
List Price:  Kz118,629.90
You save:  Kz33,896.68
List Price:  CLP$125,220.73
You save:  CLP$35,779.90
List Price:  CN¥1,011.17
You save:  CN¥288.92
List Price:  RD$8,157.46
You save:  RD$2,330.87
List Price:  DA18,810.98
You save:  DA5,374.95
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,087.56
You save:  Q310.75
List Price:  GY$29,285.87
You save:  GY$8,367.99
ISK kr13,827.61
List Price:  ISK kr19,359.21
You save:  ISK kr5,531.60
List Price:  DH1,386.51
You save:  DH396.17
List Price:  L2,464.07
You save:  L704.07
List Price:  ден7,916.55
You save:  ден2,262.03
List Price:  MOP$1,124.95
You save:  MOP$321.43
List Price:  N$2,547.77
You save:  N$727.98
List Price:  C$5,144.63
You save:  C$1,470
List Price:  रु18,661.67
You save:  रु5,332.28
List Price:  S/520.05
You save:  S/148.59
List Price:  K543.23
You save:  K155.22
List Price:  SAR524.96
You save:  SAR150
List Price:  ZK3,554.51
You save:  ZK1,015.64
List Price:  L640.62
You save:  L183.04
List Price:  Kč3,180.29
You save:  Kč908.72
List Price:  Ft49,855.26
You save:  Ft14,245.38
SEK kr1,071.59
List Price:  SEK kr1,500.27
You save:  SEK kr428.67
List Price:  ARS$124,136.13
You save:  ARS$35,470
List Price:  Bs967.19
You save:  Bs276.36
List Price:  COP$532,965.46
You save:  COP$152,286.72
List Price:  ₡71,639.71
You save:  ₡20,469.95
List Price:  L3,448
You save:  L985.21
List Price:  ₲1,048,812.96
You save:  ₲299,682.25
List Price:  $U5,395.05
You save:  $U1,541.55
List Price:  zł548.83
You save:  zł156.82
Already have an account? Log In


Okay, first of all, let's have a brief look over our employees dot XLS x Excel workbook. First of all, notice that we have three sheets right here. So we have employee data, salaries and skills, each sheet having a column header and 10 rows representing our employees. The first sheet inside the workbook contains general information about our employees, like the first name, last name, department, phone number and address. The second worksheet contains the salary of each employee. And finally, the third worksheet contains the most important skills that each of these employees brings to the company.

Okay, one more thing to mention here. I'm going to close this file when we are going to work on it from within the Python interpreter to avoid any permission related issues. Don't worry however, whenever we want to read something from within the file or whenever we are Going to make changes to the file from within the Python interpreter, we are going to open the file once again to verify our changes. So let me close the file for now. And let me open up the Python interpreter. We have already imported the open pi Excel module.

So first of all, in order to be able to work with our workbook, we should first load it into the interpreter. To do that, I'm going to create this object right here workbook equals the name of the module open pi Excel dot. Now the name of the method for loading the workbook. So load workbook, and in between parentheses, and also in between double quotes, you should enter the full path to your file. So in my case, that would be D, colon, backslash employees, dot XLS x. Okay, after hitting Enter, the workbook has been loaded, and now we can work with it and also read various information about it.

First of all, let's get some basic properties of the workbook. By using the properties attributes, so workbook dot properties, returns some basic information about the workbook, like for example, the date and time of the last change, and also the user that made the last change to the file. We can also get the name of the sheets inside the workbook by using workbook dot sheet names. And indeed, we have our three sheets from within the workbook, employee data, salaries and skills as members of a list. Now this is very useful because we can use this list in our code if we are building an application. For now, let's just focus on other attributes that may prove to be useful when working with workbooks.

Okay, so we got the sheet names. Now let's get the active sheet in the workbook, which is usually the first sheet in the workbook. So workbook dot active. And as I said, we have the first sheet in the workbook which is employee data being returned. Now let's see how to read Friends are shipped by its name using Python. So that would be sheet equals workbook.

And in between square brackets, and also in between quotes, we enter the name of the sheet that we want to reference. In our case, let's say employee data. Now we can use this object right here in order to perform other operations, as we're going to see later in this lecture. For now, let's see how to create a new sheet in the workbook. And for that, we are going to use workbook dot the name of the method create underscore sheet, and in between its parentheses and also in between quotes, you should enter the name of the new sheet, let's call it test sheet. And in order for our change to be applied, we also have to save the workbook.

So we have workbook dot save and in between the parentheses of safe and also in between single or double quotes. We enter again the full path to the file. So let me enter it once again. Employees dot XLS x. Okay, at this point, the workbook has been saved. Now let's open the XLS x file and check if indeed our new sheet has been created.

And as you can see, we have test sheet right here as the new sheet of this workbook. Great. Now let me close the file. And let me show you how to remove a certain sheet from the workbook. For example, let's say we have sheet equals workbook of that sheet. So we are referencing this newly created sheet at this point, and now we have two options.

In order to remove this sheet, we can either use workbook dot remove of sheet, and let's hit Enter. And the second option would have been to use Dell workbook. And in between square brackets, also in between quotes the name of the sheet we want to remove. So that would be that sheet in our case, and that would accomplish the same task. As you can see, if we use The second option after using the first one, Python already notifies us about the fact that the worksheet does not exist. So at this point, if we save our workbook once again, and then we open the file, we can see that the test sheet sheet has been removed.

Okay, I'm going to close the file once again. And that's it for now. I'll see you in the next lecture where we are going to start working with particular sheets inside our workbook. So I'll see you soon.

Sign Up


Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.