Automate Excel with Python using OpenPyXL | Andrei Dumitrescu | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Automate Excel with Python using OpenPyXL

teacher avatar Andrei Dumitrescu, DevOps Engineer and Professional Trainer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

10 Lessons (52m)
    • 1. Welcome

      1:19
    • 2. Setup the Environment. Installing OpenPyXL

      4:22
    • 3. Excel Basics

      2:29
    • 4. Reading Excel Files

      8:43
    • 5. Reading Data in a Cell Range

      6:18
    • 6. Writing Excel Files

      4:48
    • 7. Creating New Excel Files

      2:54
    • 8. Using Excel Formulas

      4:23
    • 9. Sheets Operations

      6:18
    • 10. Working with Styles

      10:26
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

200

Students

--

Projects

About This Class

009a68fd

In this course you'll get the skills to automate Excel with the Power of Python!

Using OpenPyXL module you will learn how to read and write Excel Files, work with sheets and styles.

Since this is intermediate Python you are required to already master the basics of Python before enrolling into this class. My advice is to first check my other classes on Python published here on SkillShare; they will help you build a strong foundation of Python Programming Language.

In this course we'll get the skills to get ahead!

 Major topics of this Python and Excel course:

  • Setup the Environment. Installing OpenPyXL
  • Excel Basics
  • Reading Excel Documents
  • Reading Data in a Cell Range
  • Writing Excel Files
  • Creating New Excel Files
  • Using Excel Formulas
  • Sheets Operations
  • Working with Styles

and more!

Excel file used in the course: https://drive.google.com/open?id=1NANQ_sVqWK1d-JvE9B3Zd9ZKxpQxAG8K

Meet Your Teacher

Teacher Profile Image

Andrei Dumitrescu

DevOps Engineer and Professional Trainer

Teacher

I've been a Network and Software Engineer for over 15 years, the typical profile of a DevOps Engineer.

I've cofounded Crystal Mind Academy, a Cisco Academy and professional training center in Romania,  that focuses on teaching cutting-edge technologies to students.

I have contributed to education in areas of programming, information security and operating systems. During the last 12 years more than 20,000 thousand students have participated in-person or online  training programs at Crystal Mind Academy. 

I have developed documentation, labs and case studies for many training programs such as Cisco CCNA, CCNA Security, CCNP, Linux Administration, Information Security, Python Programming, Network Automation with Python or Blockchain Programming (Ethereu... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Welcome: hello and welcome toe this class on how to automate Excel files with the power off python. My name is Andrea Dmitry School and I'll be your instructor for this class is well, is lax of other classes here on a skill share. I'll teach you how to use the open P Y Excel model and how to become proficient in Working with Excel spreadsheets in FIFA sees. This is intermediate Typhon. You are required toe already master the basics of python before enrolling into this class. My advice is to first check my other classes on python published here on skill a share. They will help you build a strong foundation off python programming language. You'll get the skills to get ahead. These hens on course goes straight to the point without any destruction and focuses solely on how to effectively use excel in our private applications. If you want to waste a normal time with incomplete scripts or tutorials, copy paste solutions for confusing source code. Then this class is for you. See you in the class 2. Setup the Environment. Installing OpenPyXL: in this section will take a look at how to manage Excel files with Python. Excel is a very popular and powerful spreadsheet application for Windows. Mac really looks. Although Excel is proprietary software from Microsoft, there are free alternatives that run on Windows. Make OS or Lee looks liberal. Fiscal is suction example that works with Excel Excel SX File format. For spreadsheets, you can download the software for free from liberal office door torque. The examples in this section work on both Microsoft Excel version 2007 or later and Khalq Liberal Office Application. The open P y Excel module allows your private programs to read and modify Excel spreadsheet files. This is extremely useful for automating, boring or repetitive tasks off coping certain data from one spreadsheet into another one. Or you might have to look through hundreds off spread shakes off department logics, searching for any that make She's a specific criteria. These are exactly the sort of boring, mindless spreadsheet tasks that can be easily automated with python. The open P y Excel module doesn't belong Toe Python Stamberg library, so we must install it using people in a command prompt or terminal just type people install open P Y Excel. Keep the python package Manager will search for, download and install. Open Pure I Excel for you to import India's in your Python application test. Whether it is installed correctly. Try to import the module in a python shell. Import open P Y. Excel. If there is no error, we assume it has been installed correctly. If you get the name error, such is name open. Pure I excel is not defined. There was a problem with the installation and further trouble Shooting is required. Please keep in mind that many python developers use virtual environments. This is important because it keeps different applications isolated. Each application has access to its own modules, and the four module has been installed globally. Like in our previous example, It doesn't mean it's also available in a specific veto environment, so there could be cases when we stole a package globally. But it's not accessible in pie charm that could use a victory environment for each project . In this case, we must install the module also in the victory environment. If we try to import the open pure I Excel Module and run the script by charm will return a never know module named Open P y Excel. This happens even if ICE told the package globally to install the picket in the vehicle environment used by Pi Charm. We goto file settings Project Interpreter. Here I press on the plus sign, and I'm searching for open P Y Excel. After selecting the module, I'll click on Install Package the same package manager called Peep Downloads and installs the package in the vehicle environment. Okay, it's ready. Pickets open. Pure Excel installed successfully. Now, if I execute the script one more time, I'll get no error. Now that we have everything set up will start working with Excel spreadsheets from our python applications. 3. Excel Basics: before diving in tow. Open pure I Excel Module and how to manage Excel Fights with Python will take a short look at Excel Basics. First, let's go over some definitions. A workbook is another word for your Excel file. A worksheet or a spreadsheet is a collection off cells where you keep in manipulate the data. Each Excel workbook can contain multiple work. Six. They are like documents in which data is lay out in rows and columns like a big table. Whenever you work with Excel, you'll enter information or data into cells. Cells are the basic building blocks off a work ship. Each cell can contain different types off content, including text formulas and functions. A cell is the intersection or faro, and the column columns are identified by letters A, B, C, the E and so on, While arose are identified by numbers 12345 and so on. Each cell has its own a name or sell address based on its column and row in this example the selected cell intersex column C and Row five. So the cell address is C five. The cell address will also appear in the name books note that a cells column and the row headings are highlighted when the cell is selected, you can also select multiple cells at the same time. Ah, group off cells is known as a cell range rather than a single cell address. You will refer toa a cell range, using the cell address off the first and last cells in the cell range separated by a colon , for example, a cell range that includes cell A one, a two, a three, a four and a five will be written is a one colon a Fife. Vicks. Enough for the moment about spreadsheet basics in the next lecture will see how to load a spreadsheet in your private application and how to read the data from that spreadsheet. 4. Reading Excel Files: in this lecture will see how to load a workbook into our python application and how to read the data from with that workbook. I've already created a workbook that you are seeing right now, and we'll use this word book in this section for many testing and for learning how to automate boring and repetitive tasks. This simple workbook is available for you to download is an attachment toe this lecture. It's usually the first step is to import the module import open pure. I excel Once we've imported the open P y Excel module will be able to use the open p y excel dot load workbook function toe open and load a war book into our Brighton application . WB equals open P Y excel dot load workbook. The function takes in the file name and returns award book object. The name off My Excel file is store dot Excel s ex. Remember a letter? The file in this case, stored or X? L SX needs to be in the same directory with the Python script. I'll take negatively. You can use on absolute path. After loading the Excel file, we need to select the spreadsheet we want to work with. You can get the least or fold the sheet names in the workbook by accessing the sheet names . Attribute print wb dot shit names and we can see there are two spreadsheets, products and sales 2018. The workbook object is unutterable object so we can eat rate over it using a for loop for sheet in W B colon print shit. If we want to access the title off the spreadsheet, we use the title attribute. In order to select a spreadsheet from the World Book, we use the name of the World Book and the name or the title off the spreadsheet between square brackets. This is like when getting the value off the corresponding key from a dictionary. So she equals w b the workbook object. And between square break, it's the name off the worship lexei products and I am getting these work shit. You can also read the active member variable off the World Book object toe, get the World books active. Shit! The active shit is the sheet of X on top when you open the Excel file. This is in fact, the first ship off the world Book. So she equals wb dot active. It's the same spreadsheet. All of the operations that follow will work only on this spreadsheet. Now that we've selected a spreadsheet from the war book, we can access a cell object bikes, others. Let's try some examples. Beato underlying cell equals sheet off and the address off the cell in this case, be too Lex. Access another cell. Cito. Underlying cell equals sheet off. See, too. The cell address is case insensitive, so I can use upper or lower case letters. Now to get the value, store it in the cell, we use the value attribute Lex printed the values off these two cells. Beato cell dot value and see to sell dot value. Let's check the cells. This is Beto Mobile phone. And this is Cito. So 15 Lex, execute the script. Okay. Sorry. I've misspelled the spreadsheet name. So here we have products and we see how it pointed out. Mobile phone and 15. These are the values off these two cells. Another way to get the value off a cell in a shit is to use X coordinates in the following way. So sheet don't sell off, and we have here toe arguments. Ro equals four and column equals two. This is the cell, and I want X value, so don't value. And it will return the value off these cells. So for column, too, the season is a martyr, Butch. Her row and column are attributes off any cell object so we can get the coordinates or address off any sell by using these attributes. Lexie, the coordinates off, see to sell. And these are the coordinates off the cell. Another useful attributes off a cell object is data type, which returns the pipe of data stored in that cell. Let's see what type of data is stored in some cells. In this example, I want to see the type of data in a five and be five. This is a five and this is be five, and it returned on n and on s and comes from numeric and s from strength. So this is a numeric value, and this is a string value. And if we want to see the encoding scheme who use the encoding attribute and the encoding is beautifui eight, then we have the parent off a cell, which is the spreadsheet that contains the cell. Lexi. What spreadsheet contains the cell with the address? D four. So print sheet off before the parent and this is the spread ship its name. He's products. If we want to see all attributes and methods off a cell object, we use the built in dear function and basin. The cell is an argument. So dear Off Beato Cell, I'll point out all our three books and medics off a cell object. 5. Reading Data in a Cell Range: in this lecture, I'll show you how to read the data in a cell rings we can eat, rate over a cell range and read the data saved in those cells. In this example, my cell rings is defined between B two and C 11. This is Beato and see 11 sell underlying range. I create a new variable equals sheet off and the range address. So be to Colon Sea 11 and I'll eat a rate over the rink for product. Call my price in cell range. Brent and I am using a Neff string literal So product colon and product between a pair of curly braces in price. Colon in the price variable between a pair off curry braces off course. If I want to see only the values start in these cells. I use the value our tribute off the sale object, price dot value and product daughter value. Her cell has also other attributes, and we see the data in this cell rings in the last example. Off this lecture, I'll show you how to display all the content from a spreadsheet for this purpose. There are three other cells attributes dimensions. Max zero and Max column. Forest Lexie The ship dimensions for that I use the dimensions are three bit, and the sheet dimensions is a one colon e 11. The dimensions are tribute. It turns a cell range. Defined is the upper left in the bottom, right part off the sheet of it. Contains data, and you can see this is a one e 11. There are also the Mexico and Max column are three books. Note that the Max column return is an integer rather than the letter that appears in Excel . So instead off E, eat this place. Five. 12345 Now let's iterated and display all the content from the spreadsheet. For a B, see the end e in sheet off sheet daughter dimensions. Print a daughter failure coma be dot value, si dot value the dollar value and e dot value. In fact, I am printing the value or full the cells in the spreadsheet, and these are the values In the last column named Total Amount. There is a formula that calculates the total amount is total. UNIX Times unit Price and python displayed the formula. You can switch between extracting the formula and ex result by using the data only equals two argument when opening the World Book. So he had at the beginning I used another argument. Data on Lee equals through and I'll executor script again. We can see how it displayed the result off that formula instead off the formula. Another way to eat right and display all the content from a sheet is to use the roast. Our tribute off the sheet object 40 in sheet Daughter Rose for sale in a row. A row has many cells, print sell dot value and I don't want a bex elation after each line. I want a big slash end, so a new line after each zero, and when executing the script, I'll get the content off the spread ship. If I want the white space after each value, I can add to the white space here in the last example off this lecture, I'll show how to read Teacher off the sheet is a topple for or Ohene she dot values colon print grow and we see the content off the sheet is topples ik. Zero is infected, topple, and each cell is an element off the topple. In this lecture, we've seen how to read Excel Fights. See you in the next lecture where I'll show you how to write Excel fights. Thank you. 6. Writing Excel Files: in the last lecture, we've seen how to read data from Excel Files. Open P Y. Excel module also provides ways off writing data, meaning that your price on application can create and edit spreadsheet files is we've done in the previous lecture. When we read the data from Excel Files, the first step, when writing a workbook is to import the module, load the war book in tow, a war book object and get a reference to the spreadsheet you want to modify. In this example, Isla modify the active shit, which is the first sheet off the World Book writing values Toe cells is much like writing values. Toe kiss in a dictionary. If you have the cells, coordinate is a string. You can use it just like a dictionary key on the worksheet object to specify which sells to right toe. So in order to modify the content off a cell, we use the address off the cell equals and the new value. In this example, Isla modify the unit price off a mobile phone from 300 to 400. The address off the cell is the to the Vietnamese sheet off the two equals 400. Any time you modify the World Book, object or Ex Sheet and sells, the spreadsheet file will not be saved until you call the safe or polka method. That means wb dot safe in the name off the fire store dot x l SX. Off course. Here I can save is another fight. I'd recommend you to close the file before saving it in order to avoid permissions problems . I am executing the script and I'll open the Excel file again, and we see the new value, which is 400 in the D to sell. If you want to add a neuro at the end of the sheet, you can use the A pent method. This method takes in a topple that contains the information I create a topple new product equals. This is the I. D. The name of the product is tablet, the number off UNIX 12 the unit price 600 and the total amount 12 times 600. And I am calling the upend method off any sheet object. I am executing the script, and I'll open the Excel file one more time, and we see the new product that has been appended at the end of the file in the last example off this lecture, I'll erase the data from the Total Amount column. Now, for my Python script, I want to calculate and update the total amount for each product is total UNIX times unit price for these, I need the data in the C and D columns toe update the E column. In order to do this, I will reiterate over C two e 12 celery. So for C d comma e in shit off Cito Colon E 12 this is the cell rings. I am iterating over and I write, he don't value equals c dot value times the daughter value. Okay, now I'll save and close the fight. Lex executed the script, the script executed without error and the legs open the Excel file again, and we can see how it updated the file with the correct values 7. Creating New Excel Files: in the last lectures, we've worked with existing Excel files. Now I'll show you how to create New Excel files. New war books with Python First is usually we import the open pure I Excel Module. Then instead off loading on existing war book. Using the load workbook function, we just create a war book object. Then we get the reference toe, the active shit, which will be the only sheet off the World Book. Now we'll start writing toe our new shit. There is a dictionary called Sales. The keys are the years in the values are the sails off a company in these years. Let's right the headers off the shit sheet off. A one equals here and sheet off B one equals sales. What I'll do next. Street rate over the dictionary and upend the keys and the values. Toe the sheet for Kcom. Avi in sales dot items colon and the block off Cody's sheet dot upend. I am using the upend method that takes in a couple and EDS. The Topple at the end Off the shit in the top. Elise K. Kumar v it to the end. I am saving the war book here I am using a relative path and the new war book will be saved in the current working directory. Off course. You can use an absolute path if you like. The war book object, not safe. And the name off the file. Lexei sales dot x l s x lex Execute the script and we see how a new file called sales dot Excel s ex appeared in the current directory. Lex opened the file and check X content. Perfect. Everything is just is expected. 8. Using Excel Formulas: formula is an expression which calculates the value off a cell. Functions are pretty, find formulas and are already available in Excel. Formulas, which begin with unequal sign, can configure cells toe contain values calculated from other cells. In this lecture will add formulas. Toe sells off Our Simple Excel file from python formulas are aided just like any normal value. For example, let's head on Excel formula that calculates the total amount for each product is total UNIX Times unit price. In the last lecture with calculated these values from Python and updated the spreadsheet accordingly. Now I want to use Excel formulas and not toe calculate it in python forest legs ahead of formula for a single cell and edit directly in Excel in E to sell. I write the equal sign Cito multiplied by D to and Excel automatically calculated the value and put it in the cell. Now let's it the same formula for a second cell. But from our Python script, not in Excel, I'll read a formula for E three. First, I am closing the fight here. I write sheet off you three, he quotes, and the formula will be aided is a stink the equal Sign C three times the three. Now I am executing the script off course. First, I need a reference to the active shit, so sheet equals wb dot active, and I am executive the script again. There is no error, so let's check the Excel file and we can see how it has aided in the E three. Sell the equal sign C three, Star D three and Excel automatically calculated the value and put it in the cell if we know how to wear the formula for a single cell. Legs modified. The script toe ended the same formula for all the cells. Off column E We start iterating over a cell rings and the next Cito E 12 and we modify the value off the sell by writing in a string. The vet contains the Excel formula for C commodity commodity in sheet off and now the cell range. See to Colon E 12. This is the cell range, and I'm going to use a Neff string literal because inside the string I need the actual value off si dot coordinate and the d dot coordinate. So the coordinate our tribute off any cell object these are in fact, the coordinates off the cells from column C Andy. He don't value equals and the F string literal the equal sign. See, don't coordinate times the dot coordinate. So the actual coordinates Hofer C nd vex All I can execute to the script. There is no error. Let's check the Excel file and we see how it ended the formula in each cell off column e. 9. Sheets Operations: in this lecture. Let's take a look at what properties and operations are available for working with work sheikhs first after importing the module and opening the workbook. Lex print all sheikhs in the World Book. There are two sheikhs in the workbook, so let's get a reference to the first shit. Shit equals WB off in the title Off the Shit. In this case products, we can see all methods and are three books off a sheet object by calling the deal function and best in a sheet object. Dear off shit and we see there are a lot off methods and are three books. Now let's see the title off the sheet by using the title attribute, and it's Thai Police Brothers. If we want to modify the title off the sheet, we use the same title. Attribute. Here I write sheet dot title equals in the new title Lexei Products for Sale, and the new title is Products for Save. Now let's get the basic parameters off a shit, but before of it, I must change the title off the sheet, using its new value. There is no sheet named products but products for sale and I write print she dot sheet underline format. Another attribute that returns other parameters is sheet Underline properties, and there are other parameters. Now let's see how toe ed a new sheet toe the war book. There is a method called Create Underscore Shit It takes in the name or the title off the new sheet is argument W B. This is the world book object dot Create shit and the name off the new shit Turnover. I'll executor script by default. The new shit will be the last sheet off the World Book if we want to insert the new shit on a specific position we use on optional second argument, which is the position, and it starts from zero. So I let another sheet called turnover one on position. Zeer. This will be the first sheet off the war book and don't move or Juan his own position. Zero or the first position. Now let's take a look at how to remove a sheet from the World Book. First we get the reference to the shit we want to remove. Let's remove the turnover. One sheet so she won equals WB off Turn over one. After that, we call the remove method and best in the sheet reference we've just obtained earlier. W bid that remove and the sheet reference sheets one. And I'll execute the script. Don't forget to close the word book before modifying and saving it. If not, you'll get a permission denied air. Let's check if the sheet called turnover one has been removed and there is no sheet called turnover. One. In the last example, I'll show you how to copy a sheet in the same war. Book varies a method called copy underline. Work it for this purpose. The method returns a reference to the new shit. I create a sheet reference. This will be the shit that will be cope. It likes a source equals WB off starting over and the now destination equals WB. That copy work shit and the argument is the source. It will create a copy off the source worksheet and return it as destination. So if I want, I can print destination. That title I'll executor script and you can see the name off the new sheet. Turnover. Copy. Let's check it also in the Excel file, and there is a new sheet called turnover copy 10. Working with Styles: in this lecture will discuss about applying different styles toe the cells inside our spreadsheets. Styles are used to change the look off your data while displayed on screen. Sitting cell styles is done by creating new style objects and by assigning them toe properties off a cell. There are several style objects, such as front, better and feel border and alignment. Now let's go toe coding and see how we can modify the style off cells in order to access all names from open P y excel dot styles directly. We also import everything from open pure excel dot styles. This is done for us to access classes and methods directly after importing the module, loading the Excel file and getting a reference to a shit off the World Book. Let's get a reference to the cell we want to modify My underline cell equals sheet off, be four, and we can see that it contains those market which text before starting modifying the styles off this cell. Let's take a look at what classes and attributes are available in open pure I excel dot styles module. So dear. Off open P y excel dot styles. Don't forget to close the Excel file before executing the script, otherwise will get some permissions. Eriks. As we see there, are many classes like alignment, border collar or front. All of these classes can be used to change the styles off ourselves to change a style property off a cell. First, you have to create a new style, object with the desired settings and then assign the new style object toe the cell. First, let's change the front. We'll use the Homa size 16. The color will be read in the text will be made bold and italic, and I am creating a front object like this front equals front off. Name equals in the name of the fund. Tahoma size equals 16 color equals collars dot read Here. I can also use a hex on decimal code for the color bolt equals true italic equals through we can have True or false in the strike equals false perfect. After creating the new front, object will apply toe the front property off the cell. My cell dot front equals front the new front object, and we've just changed the phone for the text within ourselves. Lex Executor script an tastic and received a text in red. The front name is the Homa in the size is 16. The text is also bold and italic. The next thing will due east weather pattern feel a big round color to ourselves. In order to do that, I'm going to create a better feel object called Phil better and feel off and the arguments are feel. Type equals solid Fiji collar from foreground color that is also BG color from background. Color equals colors that yellow better feel is a class inside the open P y Excel styles Module. After creating the pattern, feel object. We must apply it toe the feel property off the cell. My cell dot feel equals feel okay, lets tested and everything looks OK. There is a yellow background. Next will modify the border for a Bordeaux facil. We can change both the border style, which refers to how thick a border is if it's a double border or Athene border in the border by the collar. First, we'll create two styles for the border using the side class and after of it will apply the new border styles to our cell using the border class. The first variable is double border green equals side off border style bubble. This is a double border in color equals colors dot green and the second style for the border. Lex 18 board. A red border style is thin and color is red. This time I use a hex on the Cemal coat for the red color. I just want to show you that you can use some pretty find colors or hicks are decimal coats . Now I'll apply the new border styles to ourselves myself dot Border equals border. And here we have four arguments left, right top and bottom left equals double border green, right equals themed border Red stop equals double border green and bottom equals seen border red Lex tested and we can see the borders off the cell finally will set the alignment off the text inside ourselves. In order to do that, we use the alignment class off open pure. I excelled art styles. Module alignment equals a line. Month off likes a horizontal equals right? I can have right or left and very critical equals center. And I'll apply the alignment toe the cell. My cell dot alignment equals alignment. Lex tested and we see the alignment we are expecting. The last thing I'll show you in this lecture is how to copy styles from a cell to another cell. To do that, we import another module called Copy Infect. I'll import only the copy function off the copy module from copy import copy. It is used for generic, shallow or deep coping operations. Now I'll get a reference to one yourself, and I want to use the same styles with defined before for the other cell accepting the color, which should be green. So Anu underlying cell equals sheet off. Be then and the new front equals copy myself dot front. I am creating a copy off the front object off myself. Now I'm going to change the color off New fund. New front dot color equals colors dot green, and I am applying the new front object toe their cell. New cell dot fund equals new fund Lex. Best it, and we see that the text in Beaten uses the same fund, the Homa and size 16 Onley. The color is different. There are also other style properties that can be changed, but I think I've shown you the most used ones anyway. You may find a link to the official documentation off. Open pure I excel dot styles module a text toe this lecture. Thank you.