Top 20 library for excel spreadsheet for django
Excel spreadsheets are one of those things you might have to deal with at some point. Either it’s because your boss loves them or because marketing needs them, you might have to learn how to work with spreadsheets, and that’s when knowing Show
Spreadsheets are a very intuitive and user-friendly way to manipulate large datasets without any prior technical background. That’s why they’re still so commonly used today. In this article, you’ll learn how to use openpyxl to:
This article is written for intermediate developers who have a pretty good knowledge of Python data structures, such as dicts and lists, but also feel comfortable around OOP and more intermediate level topics. Before You BeginIf you ever get asked to extract some data from a database or log file into an Excel spreadsheet, or if you often have to convert an Excel spreadsheet into some more usable programmatic form, then this tutorial is perfect for you. Let’s jump into the Practical Use CasesFirst things first, when would you need to use a package like Importing New Products Into a DatabaseYou are responsible for tech in an online store company, and your boss doesn’t want to pay for a cool and expensive CMS system. Every time they want to add new products to the online store, they come to you with an Excel spreadsheet with a few hundred rows and, for each of them, you have the product name, description, price, and so forth. Now, to import the data, you’ll have to iterate over each spreadsheet row and add each product to the online store. Exporting Database Data Into a SpreadsheetSay you have a Database table where you record all your users’ information, including name, phone number, email address, and so forth. Now, the Marketing team wants to contact all users to give them some discounted offer or promotion. However, they don’t have access to the Database, or they don’t know how to use SQL to extract that information easily. What can you do to help? Well, you can make a quick script using That’s gonna earn you an extra slice of cake at your company’s next birthday party! Appending Information to an Existing SpreadsheetYou may also have to open a spreadsheet, read the information in it and, according to some business logic, append more data to it. For example, using the online store scenario again, say you get an Excel spreadsheet with a list of users and you need to append to each row the total amount they’ve spent in your store. This data is in the Database and, in order to do this, you have to read the spreadsheet, iterate through each row, fetch the total amount spent from the Database and then write back to the spreadsheet. Not a problem for Learning Some Basic Excel TerminologyHere’s a quick list of basic terms you’ll see when you’re working with Excel spreadsheets: Term Explanation Spreadsheet or Workbook A Spreadsheet is the main file you are creating or working with. Worksheet or Sheet A Sheet is used to split different kinds of content within the same spreadsheet. A Spreadsheet can have one or more Sheets. Column A Column is a vertical line, and it’s represented by an uppercase letter: A. Row A Row is a horizontal line, and it’s represented by a number: 1. Cell A Cell is a combination of Column and Row, represented by both an uppercase letter and a number: A1. Getting Started With openpyxlNow that you’re aware of the benefits of a tool like After you install the package, you should be able to create a super simple spreadsheet with the following code: The code above should create a file called Woohoo, your first spreadsheet created! Reading Excel Spreadsheets With openpyxlLet’s start with the most essential thing one can do with a spreadsheet: read it. You’ll go from a straightforward approach to reading a spreadsheet to more complex examples where you read the data and convert it into more useful Python structures. Dataset for This TutorialBefore you dive deep into some code examples, you should download this sample dataset and store it somewhere as This is one of the datasets you’ll be using throughout this tutorial, and it’s a spreadsheet with a sample of real data from Amazon’s online product reviews. This dataset is only a tiny fraction of what Amazon provides, but for testing purposes, it’s more than enough. A Simple Approach to Reading an Excel SpreadsheetFinally, let’s start reading some spreadsheets! To begin with, open our sample spreadsheet: In the code above, you first open the spreadsheet Now, after opening a spreadsheet, you can easily retrieve data from it like this: To return the actual value of a cell, you need to do `openpyxl`2. Otherwise, you’ll get the main `openpyxl`3 object. You can also use the method `openpyxl`4 to retrieve a cell using index notation. Remember to add `openpyxl`2 to get the actual value and not a `openpyxl`3 object: You can see that the results returned are the same, no matter which way you decide to go with. However, in this tutorial, you’ll be mostly using the first approach: `openpyxl`7. The above shows you the quickest way to open a spreadsheet. However, you can pass additional parameters to change the way a spreadsheet is loaded. Additional Reading OptionsThere are a few arguments you can pass to
Importing Data From a SpreadsheetNow that you’ve learned the basics about loading a spreadsheet, it’s about time you get to the fun part: the iteration and actual usage of the values within the spreadsheet. This section is where you’ll learn all the different ways you can iterate through the data, but also how to convert that data into something usable and, more importantly, how to do it in a Pythonic way. Iterating Through the DataThere are a few different ways you can iterate through the data depending on your needs. You can slice the data with a combination of columns and rows: You can get ranges of rows or columns: You’ll notice that all of the above examples return a `openpyxl`9. If you want to refresh your memory on how to handle `openpyxl`0 in Python, check out the article on . There are also multiple ways of using normal Python generators to go through the data. The main methods you can use to achieve this are:
Both methods can receive the following arguments:
These arguments are used to set boundaries for the iteration: You’ll notice that in the first example, when iterating through the rows using `openpyxl`1, you get one `openpyxl`9 element per row selected. While when using `openpyxl`2 and iterating through columns, you’ll get one `openpyxl`9 per column instead. One additional argument you can pass to both methods is the Boolean `openpyxl`1. When it’s set to `openpyxl`2, the values of the cell are returned, instead of the `openpyxl`3 object: If you want to iterate through the whole dataset, then you can also use the attributes `openpyxl`4 or `openpyxl`5 directly, which are shortcuts to using `openpyxl`1 and `openpyxl`2 without any arguments: These shortcuts are very useful when you’re iterating through the whole dataset. Manipulate Data Using Python’s Default Data StructuresNow that you know the basics of iterating through the data in a workbook, let’s look at smart ways of converting that data into Python structures. As you saw earlier, the result from all iterations comes in the form of `openpyxl`0. However, since a `openpyxl`9 is nothing more than a `openpyxl`0 that’s immutable, you can easily access its data and transform it into other structures. For example, say you want to extract product information from the A straightforward way to do this is to iterate over all the rows, pick the columns you know are related to product information, and then store that in a dictionary. Let’s code this out! First of all, have a look at the headers and see what information you care most about: This code returns a list of all the column names you have in the spreadsheet. To start, grab the columns with names:
Lucky for you, the columns you need are all next to each other so you can use the `openpyxl`6 and `openpyxl`7 to easily get the data you want: Nice! Now that you know how to get all the important product information you need, let’s put that data into a dictionary: The code above returns a JSON similar to this: Here you can see that the output is trimmed to 2 products only, but if you run the script as it is, then you should get 98 products. Convert Data Into Python ClassesTo finalize the reading section of this tutorial, let’s dive into Python classes and see how you could improve on the example above and better structure the data. For this, you’ll be using the new Python Data Classes that are available from Python 3.7. If you’re using an older version of Python, then you can use the default instead. So, first things first, let’s look at the data you have and decide what you want to store and how you want to store it. As you saw right at the start, this data comes from Amazon, and it’s a list of product reviews. You can check the list of all the columns and their meaning on Amazon. There are two significant elements you can extract from the data available:
A Product has:
The Review has a few more fields:
You can ignore a few of the review fields to make things a bit simpler. So, a straightforward implementation of these two classes could be written in a separate file `openpyxl`8: After defining your data classes, you need to convert the data from the spreadsheet into these new structures. Before doing the conversion, it’s worth looking at our header again and creating a mapping between columns and the fields you need: Let’s create a file `openpyxl`9 where you have a list of all the field names and their column location (zero-indexed) on the spreadsheet: You don’t necessarily have to do the mapping above. It’s more for readability when parsing the row data, so you don’t end up with a lot of magic numbers lying around. Finally, let’s look at the code needed to parse the spreadsheet data into a list of product and review objects: After you run the code above, you should get some output like this: That’s it! Now you should have the data in a very simple and digestible class format, and you can start thinking of storing this in a Database or any other type of data storage you like. Using this kind of OOP strategy to parse spreadsheets makes handling the data much simpler later on. Appending New DataBefore you start creating very complex spreadsheets, have a quick look at an example of how to append data to an existing spreadsheet. Go back to the first example spreadsheet you created ( Et voilà, if you open the new `openpyxl`1 spreadsheet, you’ll see the following change: Notice the additional writing ;) on cell `openpyxl`2. Writing Excel Spreadsheets With openpyxlThere are a lot of different things you can write to a spreadsheet, from simple text or number values to complex formulas, charts, or even images. Let’s start creating some spreadsheets! Creating a Simple SpreadsheetPreviously, you saw a very quick example of how to write “Hello world!” into a spreadsheet, so you can start with that: The highlighted lines in the code above are the most important ones for writing. In the code, you can see that:
Even though these lines above can be straightforward, it’s still good to know them well for when things get a bit more complicated. One thing you can do to help with coming code examples is add the following method to your Python file or console: It makes it easier to print all of your spreadsheet values by just calling `openpyxl`3. Basic Spreadsheet OperationsBefore you get into the more advanced topics, it’s good for you to know how to manage the most simple elements of a spreadsheet. Adding and Updating Cell ValuesYou already learned how to add values to a spreadsheet like this: There’s another way you can do this, by first selecting a cell and then changing its value: The new value is only stored into the spreadsheet once you call `openpyxl`4. The As you can see, when trying to add a value to cell `openpyxl`6, you end up with a tuple with 10 rows, just so you can have that test value. Managing Rows and ColumnsOne of the most common things you have to do when manipulating spreadsheets is adding or removing rows and columns. The
Every single one of those methods can receive two arguments:
Using our basic The only thing you need to remember is that when inserting new data (rows or columns), the insertion happens before the `hello_world.xlsx`2 parameter. So, if you do `hello_world.xlsx`6, it inserts a new row before the existing first row. It’s the same for columns: when you call `hello_world.xlsx`7, it inserts a new column right before the already existing second column (`hello_world.xlsx`8). However, when deleting rows or columns, `hello_world.xlsx`9 deletes data starting from the index passed as an argument. For example, when doing `sample.xlsx`0 it deletes row `sample.xlsx`1, and when doing `sample.xlsx`2 it deletes the third column (`sample.xlsx`3). Managing SheetsSheet management is also one of those things you might need to know, even though it might be something that you don’t use that often. If you look back at the code examples from this tutorial, you’ll notice the following recurring piece of code: This is the way to select the default sheet from a spreadsheet. However, if you’re opening a spreadsheet with multiple sheets, then you can always select a specific one like this: You can also change a sheet title very easily: If you want to create or delete sheets, then you can also do that with `sample.xlsx`4 and `sample.xlsx`5: One other thing you can do is make duplicates of a sheet using `sample.xlsx`6: If you open your spreadsheet after saving the above code, you’ll notice that the sheet Products Copy is a duplicate of the sheet Products. Freezing Rows and ColumnsSomething that you might want to do when working with big spreadsheets is to freeze a few rows or columns, so they remain visible when you scroll right or down. Freezing data allows you to keep an eye on important rows or columns, regardless of where you scroll in the spreadsheet. Again, If you open the `sample.xlsx`0 spreadsheet in your favorite spreadsheet editor, you’ll notice that row `sample.xlsx`1 and columns `sample.xlsx`2 and `hello_world.xlsx`8 are frozen and are always visible no matter where you navigate within the spreadsheet. This feature is handy, for example, to keep headers within sight, so you always know what each column represents. Here’s how it looks in the editor: Notice how you’re at the end of the spreadsheet, and yet, you can see both row `sample.xlsx`1 and columns `sample.xlsx`2 and `hello_world.xlsx`8. Adding FiltersYou can use At first, this might seem like a pretty useless feature, but when you’re programmatically creating a spreadsheet that is going to be sent and used by somebody else, it’s still nice to at least create the filters and allow people to use it afterward. The code below is an example of how you would add some filters to our existing You should now see the filters created when opening the spreadsheet in your editor: You don’t have to use `sample.xlsx`9 if you know precisely which part of the spreadsheet you want to apply filters to. Adding FormulasFormulas (or formulae) are one of the most powerful features of spreadsheets. They gives you the power to apply specific mathematical equations to a range of cells. Using formulas with You can see the list of formulas supported by Let’s add some formulas to our Starting with something easy, let’s check the average star rating for the 99 reviews within the spreadsheet: If you open the spreadsheet now and go to cell `load_workbook()`3, you should see that its value is: 4.18181818181818. Have a look in the editor: You can use the same methodology to add any formulas to your spreadsheet. For example, let’s count the number of reviews that had helpful votes: You should get the number `load_workbook()`4 on your `load_workbook()`5 spreadsheet cell like so: You’ll have to make sure that the strings within a formula are always in double quotes, so you either have to use single quotes around the formula like in the example above or you’ll have to escape the double quotes inside the formula: `load_workbook()`6. There are a ton of other formulas you can add to your spreadsheet using the same procedure you tried above. Give it a go yourself! Adding StylesEven though styling a spreadsheet might not be something you would do every day, it’s still good to know how to do it. Using You can also choose to either apply a style directly to a cell or create a template and reuse it to apply styles to multiple cells. Let’s start by having a look at simple cell styling, using our If you open your spreadsheet now, you should see quite a few different styles on the first 5 cells of column `sample.xlsx`2: There you go. You got:
You can also combine styles by simply adding them to the cell at the same time: Have a look at cell `openpyxl`01 here: When you want to apply multiple styles to one or several cells, you can use a `openpyxl`02 class instead, which is like a style template that you can use over and over again. Have a look at the example below: If you open the spreadsheet now, you should see that its first row is bold, the text is aligned to the center, and there’s a small bottom border! Have a look below: As you saw above, there are many options when it comes to styling, and it depends on the use case, so feel free to check Conditional FormattingThis feature is one of my personal favorites when it comes to adding styles to a spreadsheet. It’s a much more powerful approach to styling because it dynamically applies styles according to how the data in the spreadsheet changes. In a nutshell, conditional formatting allows you to specify a list of styles to apply to a cell (or cell range) according to specific conditions. For example, a widespread use case is to have a balance sheet where all the negative totals are in red, and the positive ones are in green. This formatting makes it much more efficient to spot good vs bad periods. Without further ado, let’s pick our favorite spreadsheet— You can start by adding a simple one that adds a red background to all reviews with less than 3 stars: Now you’ll see all the reviews with a star rating below 3 marked with a red background: Code-wise, the only things that are new here are the objects `openpyxl`05 and `openpyxl`06:
Using a `openpyxl`06 object, you can create numerous conditional formatting scenarios. However, for simplicity sake, the
The ColorScale gives you the ability to create color gradients: Now you should see a color gradient on column `openpyxl`15, from red to green, according to the star rating: You can also add a third color and make two gradients instead: This time, you’ll notice that star ratings between 1 and 3 have a gradient from red to yellow, and star ratings between 3 and 5 have a gradient from yellow to green: The IconSet allows you to add an icon to the cell according to its value: You’ll see a colored arrow next to the star rating. This arrow is red and points down when the value of the cell is 1 and, as the rating gets better, the arrow starts pointing up and becomes green: The Finally, the DataBar allows you to create progress bars: You’ll now see a green progress bar that gets fuller the closer the star rating is to the number 5: As you can see, there are a lot of cool things you can do with conditional formatting. Here, you saw only a few examples of what you can achieve with it, but check the Adding ImagesEven though images are not something that you’ll often see in a spreadsheet, it’s quite cool to be able to add them. Maybe you can use it for branding purposes or to make spreadsheets more personal. To be able to load images to a spreadsheet using Apart from that, you’ll also need an image. For this example, you can grab the Real Python logo below and convert it from `openpyxl`20 to `openpyxl`21 using an online converter such as cloudconvert.com, save the final file as `openpyxl`22, and copy it to the root folder where you’re running your examples: Afterward, this is the code you need to import that image into the `openpyxl`23 spreadsheet: You have an image on your spreadsheet! Here it is: The image’s left top corner is on the cell you chose, in this case, `openpyxl`24. Adding Pretty ChartsAnother powerful thing you can do with spreadsheets is create an incredible variety of charts. Charts are a great way to visualize and understand loads of data quickly. There are a lot of different chart types: bar chart, pie chart, line chart, and so on. Here, you’ll see only a couple of examples of charts because the theory behind it is the same for every single chart type: For any chart you want to build, you’ll need to define the chart type: `openpyxl`26, `openpyxl`27, and so forth, plus the data to be used for the chart, which is called `openpyxl`28. Before you can build your chart, you need to define what data you want to see represented in it. Sometimes, you can use the dataset as is, but other times you need to massage the data a bit to get additional information. Let’s start by building a new workbook with some sample data: Now you’re going to start by creating a bar chart that displays the total number of sales per product: There you have it. Below, you can see a very straightforward bar chart showing the difference between online product sales online and in-store product sales: Like with images, the top left corner of the chart is on the cell you added the chart to. In your case, it was on cell `openpyxl`29. Try creating a line chart instead, changing the data a bit: With the above code, you’ll be able to generate some random data regarding the sales of 3 different products across a whole year. Once that’s done, you can very easily create a line chart with the following code: Here’s the outcome of the above piece of code: One thing to keep in mind here is the fact that you’re using `openpyxl`30 when adding the data. This argument makes the chart plot row by row instead of column by column. In your sample data, you see that each product has a row with 12 values (1 column per month). That’s why you use `openpyxl`31. If you don’t pass that argument, by default, the chart tries to plot by column, and you’ll get a month-by-month comparison of sales. Another difference that has to do with the above argument change is the fact that our `openpyxl`28 now starts from the first column, `openpyxl`33, instead of the second one. This change is needed because the chart now expects the first column to have the titles. There are a couple of other things you can also change regarding the style of the chart. For example, you can add specific categories to the chart: Add this piece of code before saving the workbook, and you should see the month names appearing instead of numbers: Code-wise, this is a minimal change. But in terms of the readability of the spreadsheet, this makes it much easier for someone to open the spreadsheet and understand the chart straight away. Another thing you can do to improve the chart readability is to add an axis. You can do it using the attributes `openpyxl`34 and `openpyxl`35: This will generate a spreadsheet like the below one: As you can see, small changes like the above make reading your chart a much easier and quicker task. There is also a way to style your chart by using Excel’s default `openpyxl`36 property. In this case, you have to choose a number between 1 and 48. Depending on your choice, the colors of your chart change as well: With the style selected above, all lines have some shade of orange: There is no clear documentation on what each style number looks like, but this spreadsheet has a few examples of the styles available. Here’s the full code used to generate the line chart with categories, axis titles, and style: There are a lot more chart types and customization you can apply, so be sure to check out the package documentation on this if you need some specific formatting. Convert Python Classes to Excel SpreadsheetYou already saw how to convert an Excel spreadsheet’s data into Python classes, but now let’s do the opposite. Let’s imagine you have a database and are using some Object-Relational Mapping (ORM) to map DB objects into Python classes. Now, you want to export those same objects into a spreadsheet. Let’s assume the following data classes to represent the data coming from your database regarding product sales: Now, let’s generate some random data, assuming the above classes are stored in a `openpyxl`37 file: By running this piece of code, you should get 5 products with 5 months of sales with a random quantity of sales for each month. Now, to convert this into a spreadsheet, you need to iterate over the data and append it to the spreadsheet: That’s it. That should allow you to create a spreadsheet with some data coming from your database. However, why not use some of that cool knowledge you gained recently to add a chart as well to display that data more visually? All right, then you could probably do something like this: Now we’re talking! Here’s a spreadsheet generated from database objects and with a chart and everything: That’s a great way for you to wrap up your new knowledge of charts! Bonus: Working With PandasEven though you can use Pandas to handle Excel files, there are few things that you either can’t accomplish with Pandas or that you’d be better off just using For example, some of the advantages of using But guess what, you don’t have to worry about picking. In fact, First things first, remember to install the `openpyxl`42 package: Then, let’s create a sample DataFrame: Now that you have some data, you can use `openpyxl`43 to convert it from a DataFrame into a worksheet: You should see a spreadsheet that looks like this: If you want to add the , you can change `openpyxl`44, and it adds each row’s index into your spreadsheet. On the other hand, if you want to convert a spreadsheet into a DataFrame, you can also do it in a very straightforward way like so: Alternatively, if you want to add the correct headers and use the review ID as the index, for example, then you can also do it like this instead: Using indexes and columns allows you to access data from your DataFrame easily: There you go, whether you want to use ConclusionPhew, after that long read, you now know how to work with spreadsheets in Python! You can rely on
There are a few other things you can do with Feel free to leave any comments below if you have any questions, or if there’s any section you’d love to hear more about. Should you use Python or Django to create a spreadsheet?When a client needs to manipulate data, it’s easiest to give them an Excel spreadsheet. Creating a spreadsheet is easy with Python, and making it available for web download is just as easy with the Django web framework. After a brief tangent into evolutionary history, you’ll find out how to do both. What's a good xlsx spreadsheet renderer for Django REST framework?GitHub - wharton/drf-excel: An XLSX spreadsheet renderer for Django REST Framework. FlipperPA Cleanup comments and spacing. Cleanup comments and spacing. Initial commit. Update supported versions. drf-excel provides an Excel spreadsheet (xlsx) renderer for Django REST Framework. Does Django model's data convert to XLSX Excel format?As business requirements evolve, certain features are expected to evolve as well. That was the case for me a few days ago when I had to implement a feature where Django model's data are converted to .xlsx excel format and sent, as an attachment, to a provided email address literally. What is Djangodjango-excel is based on pyexcel and makes it easy to consume/produce information stored in excel files over HTTP protocol as well as on file system. This library can turn the excel data into a list of lists, a list of records (dictionaries), dictionaries of lists. And vice versa. Which Python library is best for Excel?Best Open-Source Python Libraries for Excel. nb2xls. ... . Koala. ... . pyxlsb. ... . XlsxPandasFormatter. ... . xls2slsx. ... . vb2py. ... . hotxlfp. 2 A Python Excel Formula Parser similar to the javascript handsontable formulaparser.. xltable. xltable is an API for writing tabular data and charts to Excel.. What libraries read Excel files in Python?There are multiple approaches to reading and writing Excel files in Python. Some popular libraries include pandas, xlrd, xlwt, openpyxl, and xlsxwriter. Which library is used to handle Excel?Openpyxl is a Python library that allows users to read Excel files and write to them. This framework can help you write functions, format spreadsheets, create reports, and build charts directly in Python without even having to open an Excel application. Which Python library is for manipulating Excel files?5 Libraries to Make Working with Excel in Python Easier. openpyxl. The first Python package for Excel which we'll discuss is openpyxl. ... . XlsxWriter. The next Python package for working with Excel is XlsxWriter, which works with . ... . 3 and 4. pyxlsb and pyxlsb2. ... . pylightxl.. |