To VBA and beyond - building a RESTful backend using plain Microsoft Excel macros

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

This article consists of three main parts, an introduction to webserver internals, getting Excel to answer http requests and adding some special sauce to make it a RESTful backend. If you can't wait to read through the source, you can check out the repository webxcel, which I will refer to throughout this article.

Webservers

When using express or ASP.NET, we usually think about http routes and request bodies, but we never really care about how requests are handled inside the framework. Deep down, every http request consists of a TCP connection, where the client and server exchange various messages and eventually close the connection. A very basic request might look like this:

POST /api/cities HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows; U; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727)
Content-Type: application/json
Content-Length: 29

{
"name": "Springfield"
}

This post request sends a JSON payload to /api/cities, and we'd usually expect the server to create a city named "Springfield". If our backend is a simple express server, it might look something like this:

const express = require("express"),
app = express();

function handler(req, res) {
// create city

res.status(201)
.send("woo hoo!"); //
}

app.post("/api/cities", handler);
app.listen(8080);

Before executing handler, express parses the request, extracts the request method and requested resource to determine what action to perform. It also parses the request headers and - depending on the Content-Type - the request body, too. This looks somewhat similar to the following piece of pseudo-js:

// ...

const requestText = socket.readToEnd(),
request = parseRequest(requestText),
contentType = request.headers.get("Content-Type");

if (canParseRequestBody(contentType)) {
request.body = parseRequestBody(body, contentType);
}

const handler = registeredHandlers.findHandler(request.method, request.url),
response = new HttpResponse();;

if (handler) {
handler(request, response);
}

sendResponse(response);

// ...

The express example above also showed adding arbitary text to the response using res.send() and setting the response's status using res.status(). As you can see in the pseudo-js, after calling the request handling function, the underlaying framework will convert the response object to an HTTP response similar to the following and send it back to the client:

HTTP/1.1 201 Created
X-Powered-By: Express
Content-Length: 8

woo hoo!

In our client, we'd then evaluate the status code, headers and response text after parsing everything again.

Hello, this is Excel

Now, how do we make Excel answer our requests? Microsoft Office comes with a really cool toolbelt called macros, which people now hate because of ransomware. You could argue that macros are obsolete by now and coding them in Visual Basic isn't cool when you could use any modern programming language instead, but the concept behind them is pretty neat in fact.

VBA macros

Macros were originally conceived to save the user from repeating the same task over and over again. For this, users could record macros, which would then repeat what they did earlier. Internally the macro host would create a "script" of what's happening and interpret it later. These scripts happen to be generated in Visual Basic, or - to be more specific - Visual Basic for Applications (VBA). To interact with the application, macro hosts "inject" functionality into the VBA interpreter, like the Range function in Excel, which can be used to access a collection of cells. As VBA is extremely easy to learn if you don't have a programming background, users quickly adopted and combinded injected functions and classes to Subs and Functions to e.g. automatically generate cell values based on more complex calculations.

Crafting Excel files for version control

A major problem when developing macros is version control. Office files are usually zip containers, so adding them in binary would prevent any sane way of diffing. Extracting and re-zipping the project would be the way to go, if macros weren't binary encoded in a separate container using a special format called OLE. There are some OLE macro extractors like decalage2's oletools out there, but strangely I didn't find any library to create these containers the easy way.

Instead of reading the specification and creating our own library, we may try something else first: we can control Excel (or any other Office application) using .NET and let Excel do all the hard work for us. Using this approach, we need our macros' code in plain-text files, and an importer, which starts Excel and imports our macros. We'll be using PowerShell for this, which comes with full access to the .NET framework and because we don't have to compile these scripts.

In PowerShell, we can create an Excel instance, which we can then use to create workbooks and import our macros. You can take a look at the build script build.ps1 in the repository.

Escaping interop hell - using Windows Sockets in VBA

After we got our version control problems out of our way, we can get straight to the core of creating our server. As mentioned earlier, building a webserver requires handling TCP connections. The bad news is, VBA doesn't come with a TCP implementation by default and I can't really think of a reason why it should. But don't worry, Microsoft thought of somebody needing questionable features, so they baked C interop into VBA as well.

Everybody who's done C interop from a high-level language like C# knows the pain of AccessViolationException when incorrectly marshalling parameters. In VBA it's basically the same, except that both the debugger and the IDE aren't really meant to develop interop-heavy applications, and thus debugging isn't as easy as you might be used to.

The "easiest" way of getting a TCP server running using only interop and no external libraries (like a C# library which implements the HTTP server already - that'd be too easy for us), is to use Windows Sockets (winsocks). If you haven't used winsocks yet, this is what it basically looks like in C++:

// we can skip all variable declarations as they're not that important here

// setup winsocks
WSAStartup(mode, &wsa);

// create a server socket, this is similar to bsd sockets
server = socket(AF_INET, SOCK_STREAM, 0);

// bind the server socket to an address and port, which it'll listen to later
addr.sin_port = htons(8080);
result = bind(server, &addr, sizeof(sockaddr_in));
// usually we'd check the result and handle errors, but that's not important here

// start listening on the server socket and allow queuing up backlog clients
result = listen(server, backlog);
// check result, see above

// get the first client socket in the backlog queue
client = accept(server, &clientAddr, sizeof(sockaddr));

// at this point, the connection is active and we can send/receive data
send(client, message, messageLength, flags);

// cleanup after we're done
closesocket(client);
closesocket(server);
WSACleanup();

The first good news is: we can translate this straight to VBA by importing all required methods in a module and simply call all of them in the right order to get a TCP server up and running.

Since we have a working TCP connection by now, we can continue our server development by parsing incoming HTTP requests. As shown above, requests consist of a protocol line, the headers and a request body. Parsing the protocol line is probably the easiest, we can split it into three parts: the request method, the resource and http version.

Before actually splitting the line, we should make sure we're dealing with an http request. To do so, we can use VBA's text comparison feature Like, which checks if some text matches a very simple pattern, similar to regular expressions. By evaluating

' prevent comparison errors if clients send lower case requests
Dim upperLine As String
upperLine = UCase(line)

' this is somewhat similar to
' /.* HTTP/1.1/.test(upperLine)
' in js
If Not upperLine Like "* HTTP/1.1" Then
' we're concentrating on http 1, since version 2 is a bit more complex to implement
Err.Raise StatusCode.ErrorHttpRequestInvalidFormat
End If

' now we know the request is an http request and can continue parsing it

we can make sure to only process http requests and extract the request method and resource. Splitting the headers is a piece of cake as well, we just need to use Split(line, ":", 2) on each header line, where 2 represents the maximum count of parts the split function should return, and we're set. To keep it simple, we're not going to parse the request body for now. Since we want our server to return a very simple response, we're just going to echo the request.

Similar to express, we'll handle requests using response objects. Our response class contains headers, a status and a body. Using this class, we can create a simple echo server by reading all incoming text, parsing the request and sending our response:

Dim server As TcpServer
Set server = New TcpServer

' listen for incoming connections on port 8080
server.BindTo 8080

' accept an incoming connection ...
Dim client As TcpClient
Set client = server.AcceptTcpClient()

' ... and receive the request text
Dim requestText As String
requestText = client.ReceiveString()

Dim request As HttpRequest
Set request = New HttpRequest

request.Parse requestText

Dim response As HttpResponse
Set response = New HttpResponse

' send "200 OK" and the body
response.StatusCode = 200
response.Body = "Called " & request.Url & " with this body:" & vbCrLf & vbCrLf & request.Body

Dim responseText As String
responseText = response.ToString()

' actually send the response back to the client
client.SendString responseText

' and do some cleanup
client.Dispose
server.Dispose

Webservers like nginx and apache can be configured to send the server version, so we're going to do the same with Excel. In our response class, we're using a ToString method to convert our object to a string containing all response information. When examining our server with the axios node.js module, we'll receive a very satisfying response:

> const axios = require("axios");
> axios.post("http://localhost:8080", "it works").then(response => {
... console.log(response.status, response.statusText);
... console.log(response.headers);
... console.log(response.data);
... });

// outputs
200 'Nobody Needs This Anyway'

{ 'content-length': '39',
connection: 'close',
server: 'Microsoft Excel/16.0' }

Called /hello with this body:

it works

As you can see, we're also adding a header Connection: close to our response, but most servers usually send Connection: keep-alive. This is due to the http specification, which allows reusing the current socket for future requests, and webservers use this to gain some extra performance. Since our webserver isn't going to be as fast as any other server anyway, we might as well skip this and close the sockets, which is easier than keeping connections open, too.

It's blocking the gui, how do I stop it now?

We got an echo server working, great! But it only works for one request and we'll have to restart the macro everytime a client requests something, so let's put it in a loop and we're good to go.

Well, not exactly. If we execute a while (true) { } style loop in a macro, we'll see a lot of white and a "Microsoft Excel (not responding)" kind of titlebar. This is due to how Excel handles macro execution. As you might guess, macros are executed on the main thread, so whatever we'll do, our server will prevent us from accessing Excel or even stopping the macro.

In our macro, however, we can do as much as we want, e.g. implement a kill-switch. Our kill-switch will be a file, which we'll create when the server starts and which the server will monitor. If the file gets deleted, the server stops, easy as that.

But we're not done here just yet. Calling accept to get a client socket also blocks the macro execution until a client connects to our server. Searching for "winsocks accept timeout" takes us onto another C++ adventure: porting the FD_SET and FD_ZERO macros to VBA to use the select method, which in turn gives us the count of available client sockets.

After we successfully ported these C++ macros to VBA, we can pass a timeval object to select and check if there's a client before blocking with accept. Adding this to our server, we're finally able to do as many requests as we wish, plus we can stop the server using our kill-switch. Awesome!

Update 2017-10-09: As Michiel van der Blonk pointed out, calling DoEventsfrom VBA will pause the macro execution until Excel finished processing its event queue. Adding this to our server loop allows us to access Excel while the server is running.

Creating a modular server architecture

If we want to get on the same level as express or any real-world webserver, we must to be able to configure http routes. VBA doesn't contain any form of inline functions, but it does contain basic inheritance. We can use this feature to create an abstract IWebController base class, which we then subclass for our specific controllers.

Besides actually handling requests, each controller should also contain a method like MatchesUrl, which the server can use to find the appropriate controller for a request. Encapsulating this in a WebControllerCollection and adding such a collection to our server, we're now able to add any business logic to our server (like a FileSystemWebController to serve static files).

Getting some REST

The title of this article promised a RESTful backend in Excel, and to this point we only got a basic http server. Since Excel is basically a set of tables, we might use this to our advance and read/modify the table's data using an IWebController subclass.

CREATE TABLE

When we create a table in any real-world relational database, we're using something like this:

CREATE TABLE cities (
id INT PRIMARY KEY,
name VARCHAR(200),
fk_states VARCHAR(200)
);

In Excel, we can use worksheets as tables and the current workbook as our database. But how do we create columns? We can't change the column headings from "A", "B", "C", ... to anything else, so our best bet is to use the first line for our columns. Defining the primary key in a column needs to be easy as well. The easiest way to show that something is important, is to make it red or bold. Excel supports many different red tones, so marking our primary keys bold is probably the best idea:

Reading all entities from a table is easy as well, we just need to iterate over all rows until the primary key column is empty. Inserting works the same way, except we're looking for the first empty primary key cell to insert our record.

For any create or update REST action we'll need to parse the request body. Since most frontend frameworks use JSON exclusively, we'll need a new JsonParser, which emits JsonObjects and JsonArrays. To keep it simple, we're using a hand-written recursive-descent top-down parser, which counts braces/brackets and then recursively calls the appropriate parse method.

Now that we have tables with primary keys and a JSON parser, we can go ahead and create REST endpoints in a WorkbookWebController. To not iterate over all our tables on every request, we can add a route prefix like /workbook (e.g. /workbook/cities). In the ProcessRequest method of our controller, we can then analyze which sheet was requested and which REST method we should perform. This yields a basic REST backend, which can return all entries in a table, and return, update or delete a single entity.

Let's call it WRM

Doing basic REST stuff is not good enough though: real web frameworks like ASP.NET map relationships of entities. If we'd have above schema in a database used by an ASP.NET app, the underlying persistence framework would resolve all foreign keys (e.g. fk_states) and map these to their actual entities. We can create something similar in Excel, using not an object relationship mapper, but rather a worksheet relationship mapper (WRM).

In our WRM, we can read all table entries, but before returning the data to the client or inserting it to our tables, we're iterating over all columns and try to resolve each column starting with "fk_". Everytime we find such a column, we'll get the matching entity of the foreign table and use it instead of the raw value. Once everything is resolved, we might get something like this when accessing /workbook/cities from the above schema:

[
{
"id": "1",
"city": "Seattle",
"states": {
"short_name": "WA",
"full_name": "Washington"
}
},
{
"id": "2",
"city": "Springfield",
"states": null
}
]
Putting it all together

We can now combine the contents of this article and build highly complex database schemas in Excel, which we can access using REST methods. As quickly noted before, our Excel server also supports serving static files, so it makes it an ideal platform to prototype our future web applications - at least if you're prototyping on Windows (maybe macOS support will come one day).

To showcase webxcel's ease-of-use, the repository contains a React todo app with an Excel backend in the example folder.


By :  Michael Neu


A Guide to Excel Spreadsheets in Python With Openpyxl

A Guide to Excel Spreadsheets in Python With Openpyxl

In this article, you’ll learn how to use openpyxl to: Manipulate Excel spreadsheets with confidence, Extract information from spreadsheets, Create simple or more complex spreadsheets, including adding styles, charts, and so on

Originally published by Pedro Pregueiro at https://realpython.com

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 openpyxl comes in handy!

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:

  • Manipulate Excel spreadsheets with confidence
  • Extract information from spreadsheets
  • Create simple or more complex spreadsheets, including adding styles, charts, and so on

Table of Contents

  • Before You Begin
  • Practical Use Cases
  • Learning Some Basic Excel Terminology
  • Getting Started With openpyxl
  • Reading Excel Spreadsheets With openpyxl
  • Dataset for This Tutorial
  • A Simple Approach to Reading an Excel Spreadsheet
  • Importing Data From a Spreadsheet
  • Appending New Data
  • Writing Excel Spreadsheets With openpyxl
  • Creating a Simple Spreadsheet
  • Basic Spreadsheet Operations
  • Adding Formulas
  • Adding Styles
  • Conditional Formatting
  • Adding Images
  • Adding Pretty Charts
  • Convert Python Classes to Excel Spreadsheet
  • Bonus: Working With Pandas
  • Conclusion

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 Begin

If 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 openpyxl caravan!

Practical Use Cases

First things first, when would you need to use a package like openpyxl in a real-world scenario? You’ll see a few examples below, but really, there are hundreds of possible scenarios where this knowledge could come in handy.

Importing New Products Into a Database

You 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 Spreadsheet

Say 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 openpyxl that iterates over every single User record and puts all the essential information into an Excel spreadsheet.

That’s gonna earn you an extra slice of cake at your company’s next birthday party!

Appending Information to an Existing Spreadsheet

You 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 openpyxl!

Learning Some Basic Excel Terminology

Here’s a quick list of basic terms you’ll see when you’re working with Excel spreadsheets:

Getting Started With openpyxl

Now that you’re aware of the benefits of a tool like openpyxl, let’s get down to it and start by installing the package. For this tutorial, you should use Python 3.7 and openpyxl 2.6.2. To install the package, you can do the following:

$ pip install openpyxl

After you install the package, you should be able to create a super simple spreadsheet with the following code:

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "hello"
sheet["B1"] = "world!"

workbook.save(filename="hello_world.xlsx")

The code above should create a file called hello_world.xlsx in the folder you are using to run the code. If you open that file with Excel you should see something like this:

Woohoo, your first spreadsheet created!

Reading Excel Spreadsheets With openpyxl

Let’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 Tutorial

Before you dive deep into some code examples, you should download this sample dataset and store it somewhere as sample.xlsx:

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 Spreadsheet

Finally, let’s start reading some spreadsheets! To begin with, open our sample spreadsheet:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="sample.xlsx")
>>> workbook.sheetnames
['Sheet 1']

>>> sheet = workbook.active
>>> sheet
<Worksheet "Sheet 1">

>>> sheet.title
'Sheet 1'

In the code above, you first open the spreadsheet sample.xlsx using load_workbook(), and then you can use workbook.sheetnames to see all the sheets you have available to work with. After that, workbook.active selects the first available sheet and, in this case, you can see that it selects Sheet 1 automatically. Using these methods is the default way of opening a spreadsheet, and you’ll see it many times during this tutorial.

Now, after opening a spreadsheet, you can easily retrieve data from it like this:

>>> sheet["A1"]
<Cell 'Sheet 1'.A1>

>>> sheet["A1"].value
'marketplace'

>>> sheet["F10"].value
"G-Shock Men's Grey Sport Watch"

To return the actual value of a cell, you need to do .value. Otherwise, you’ll get the main Cell object. You can also use the method .cell() to retrieve a cell using index notation. Remember to add .value to get the actual value and not a Cell object:

>>> sheet.cell(row=10, column=6)
<Cell 'Sheet 1'.F10>

>>> sheet.cell(row=10, column=6).value
"G-Shock Men's Grey Sport Watch"

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: ["A1"].

Note: Even though in Python you’re used to a zero-indexed notation, with spreadsheets you’ll always use a one-indexed notation where the first row or column always has index 1.

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 Options

There are a few arguments you can pass to load_workbook() that change the way a spreadsheet is loaded. The most important ones are the following two Booleans:

  1. read_only loads a spreadsheet in read-only mode allowing you to open very large Excel files.
  2. data_only ignores loading formulas and instead loads only the resulting values.

Importing Data From a Spreadsheet

Now 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 Data

There 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:

>>> sheet["A1:C2"]
((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>),
(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>))

You can get ranges of rows or columns:

>>> # Get all cells from column A
>>> sheet["A"]
(<Cell 'Sheet 1'.A1>,
<Cell 'Sheet 1'.A2>,
...
<Cell 'Sheet 1'.A99>,
<Cell 'Sheet 1'.A100>)

>>> # Get all cells for a range of columns
>>> sheet["A:B"]
((<Cell 'Sheet 1'.A1>,
<Cell 'Sheet 1'.A2>,
...
<Cell 'Sheet 1'.A99>,
<Cell 'Sheet 1'.A100>),
(<Cell 'Sheet 1'.B1>,
<Cell 'Sheet 1'.B2>,
...
<Cell 'Sheet 1'.B99>,
<Cell 'Sheet 1'.B100>))

>>> # Get all cells from row 5
>>> sheet[5]
(<Cell 'Sheet 1'.A5>,
<Cell 'Sheet 1'.B5>,
...
<Cell 'Sheet 1'.N5>,
<Cell 'Sheet 1'.O5>)

>>> # Get all cells for a range of rows
>>> sheet[5:6]
((<Cell 'Sheet 1'.A5>,
<Cell 'Sheet 1'.B5>,
...
<Cell 'Sheet 1'.N5>,
<Cell 'Sheet 1'.O5>),
(<Cell 'Sheet 1'.A6>,
<Cell 'Sheet 1'.B6>,
...
<Cell 'Sheet 1'.N6>,
<Cell 'Sheet 1'.O6>))

You’ll notice that all of the above examples return a tuple. If you want to refresh your memory on how to handle tuples in Python

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:

  • .iter_rows()
  • .iter_cols()

Both methods can receive the following arguments:

  • min_row
  • max_row
  • min_col
  • max_col

These arguments are used to set boundaries for the iteration:

>>> for row in sheet.iter_rows(min_row=1,
... max_row=2,
... min_col=1,
... max_col=3):
... print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>)
(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)

>>> for column in sheet.iter_cols(min_row=1,
... max_row=2,
... min_col=1,
... max_col=3):
... print(column)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>)
(<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>)
(<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>)

You’ll notice that in the first example, when iterating through the rows using .iter_rows(), you get one tuple element per row selected. While when using .iter_cols() and iterating through columns, you’ll get one tuple per column instead.

One additional argument you can pass to both methods is the Boolean values_only. When it’s set to True, the values of the cell are returned, instead of the Cell object:

>>> for value in sheet.iter_rows(min_row=1,
... max_row=2,
... min_col=1,
... max_col=3,
... values_only=True):
... print(value)
('marketplace', 'customer_id', 'review_id')
('US', 3653882, 'R3O9SGZBVQBV76')

If you want to iterate through the whole dataset, then you can also use the attributes .rows or .columns directly, which are shortcuts to using .iter_rows() and .iter_cols() without any arguments:

>>> for row in sheet.rows:
... print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>
...
<Cell 'Sheet 1'.M100>, <Cell 'Sheet 1'.N100>, <Cell 'Sheet 1'.O100>)

These shortcuts are very useful when you’re iterating through the whole dataset.

Manipulate Data Using Python’s Default Data Structures

Now 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 tuples. However, since a tuple is nothing more than an immutable list, you can easily access its data and transform it into other structures.

For example, say you want to extract product information from the sample.xlsx spreadsheet and into a dictionary where each key is a product ID.

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:

>>> for value in sheet.iter_rows(min_row=1,
... max_row=1,
... values_only=True):
... print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

This code returns a list of all the column names you have in the spreadsheet. To start, grab the columns with names:

  • product_id
  • product_parent
  • product_title
  • product_category

Lucky for you, the columns you need are all next to each other so you can use the min_column and max_column to easily get the data you want:

>>> for value in sheet.iter_rows(min_row=2,
... min_col=4,
... max_col=7,
... values_only=True):
... print(value)
('B00FALQ1ZC', 937001370, 'Invicta Women's 15150 "Angel" 18k Yellow...)
('B00D3RGO20', 484010722, "Kenneth Cole New York Women's KC4944...)
...

Nice! Now that you know how to get all the important product information you need, let’s put that data into a dictionary:

import json
from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

products = {}

Using the values_only because you want to return the cells' values

for row in sheet.iter_rows(min_row=2,
min_col=4,
max_col=7,
values_only=True):
product_id = row[0]
product = {
"parent": row[1],
"title": row[2],
"category": row[3]
}
products[product_id] = product

Using json here to be able to format the output for displaying later

print(json.dumps(products))

The code above returns a JSON similar to this:

{
"B00FALQ1ZC": {
"parent": 937001370,
"title": "Invicta Women's 15150 ...",
"category": "Watches"
},
"B00D3RGO20": {
"parent": 484010722,
"title": "Kenneth Cole New York ...",
"category": "Watches"
}
}

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 Classes

To 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 Classes 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:

  1. Products
  2. Reviews

A Product has:

  • ID
  • Title
  • Parent
  • Category

The Review has a few more fields:

  • ID
  • Customer ID
  • Stars
  • Headline
  • Body
  • Date

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 classes.py:

import datetime
from dataclasses import dataclass

@dataclass
class Product:
id: str
parent: str
title: str
category: str

@dataclass
class Review:
id: str
customer_id: str
stars: int
headline: str
body: str
date: datetime.datetime

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:

>>> for value in sheet.iter_rows(min_row=1,
... max_row=1,
... values_only=True):
... print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

>>> # Or an alternative
>>> for cell in sheet[1]:
... print(cell.value)
marketplace
customer_id
review_id
product_id
product_parent
...

Let’s create a file mapping.py where you have a list of all the field names and their column location (zero-indexed) on the spreadsheet:

# Product fields
PRODUCT_ID = 3
PRODUCT_PARENT = 4
PRODUCT_TITLE = 5
PRODUCT_CATEGORY = 6

Review fields

REVIEW_ID = 2
REVIEW_CUSTOMER = 1
REVIEW_STARS = 7
REVIEW_HEADLINE = 12
REVIEW_BODY = 13
REVIEW_DATE = 14

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:

from datetime import datetime
from openpyxl import load_workbook
from classes import Product, Review
from mapping import PRODUCT_ID, PRODUCT_PARENT, PRODUCT_TITLE,
PRODUCT_CATEGORY, REVIEW_DATE, REVIEW_ID, REVIEW_CUSTOMER,
REVIEW_STARS, REVIEW_HEADLINE, REVIEW_BODY

Using the read_only method since you're not gonna be editing the spreadsheet

workbook = load_workbook(filename="sample.xlsx", read_only=True)
sheet = workbook.active

products = []
reviews = []

Using the values_only because you just want to return the cell value

for row in sheet.iter_rows(min_row=2, values_only=True):
product = Product(id=row[PRODUCT_ID],
parent=row[PRODUCT_PARENT],
title=row[PRODUCT_TITLE],
category=row[PRODUCT_CATEGORY])
products.append(product)

# You need to parse the date from the spreadsheet into a datetime format
spread_date = row[REVIEW_DATE]
parsed_date = datetime.strptime(spread_date, "%Y-%m-%d")

review = Review(id=row[REVIEW_ID],
                customer_id=row[REVIEW_CUSTOMER],
                stars=row[REVIEW_STARS],
                headline=row[REVIEW_HEADLINE],
                body=row[REVIEW_BODY],
                date=parsed_date)
reviews.append(review)

print(products[0])
print(reviews[0])

After you run the code above, you should get some output like this:

Product(id='B00FALQ1ZC', parent=937001370, ...)
Review(id='R3O9SGZBVQBV76', customer_id=3653882, ...)

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 Data

Before 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 (hello_world.xlsx) and try opening it and appending some data to it, like this:

from openpyxl import load_workbook

Start by opening the spreadsheet and selecting the main sheet

workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active

Write what you want into a specific cell

sheet["C1"] = "writing ;)"

Save the spreadsheet

workbook.save(filename="hello_world_append.xlsx"

Et voilà, if you open the new hello_world_append.xlsx spreadsheet, you’ll see the following change:

Notice the additional writing ;) on cell C1.

Writing Excel Spreadsheets With openpyxl

There 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 Spreadsheet

Previously, you saw a very quick example of how to write “Hello world!” into a spreadsheet, so you can start with that:

from openpyxl import Workbook
filename = "hello_world.xlsx"
workbook = Workbook()
sheet = workbook.active
sheet["A1"] = "hello"
sheet["B1"] = "world!"
workbook.save(filename=filename)

The highlighted lines in the code above are the most important ones for writing. In the code, you can see that:

  • Line 5 shows you how to create a new empty workbook.
  • Lines 8 and 9 show you how to add data to specific cells.
  • Line 11 shows you how to save the spreadsheet when you’re done.

Even though these lines above can be straightforward, it’s still good to know them well for when things get a bit more complicated.

Note: You’ll be using the hello_world.xlsx spreadsheet for some of the upcoming examples, so keep it handy.

One thing you can do to help with coming code examples is add the following method to your Python file or console:

>>> def print_rows():
... for row in sheet.iter_rows(values_only=True):
... print(row)

It makes it easier to print all of your spreadsheet values by just calling print_rows().

Basic Spreadsheet Operations

Before 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 Values

You already learned how to add values to a spreadsheet like this:

>>> sheet["A1"] = "value"

There’s another way you can do this, by first selecting a cell and then changing its value:

>>> cell = sheet["A1"]
>>> cell
<Cell 'Sheet'.A1>

>>> cell.value
'hello'

>>> cell.value = "hey"
>>> cell.value
'hey'

The new value is only stored into the spreadsheet once you call workbook.save().

The openpyxl creates a cell when adding a value, if that cell didn’t exist before:

>>> # Before, our spreadsheet has only 1 row
>>> print_rows()
('hello', 'world!')

>>> # Try adding a value to row 10
>>> sheet["B10"] = "test"
>>> print_rows()
('hello', 'world!')
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, None)
(None, 'test')

As you can see, when trying to add a value to cell B10, you end up with a tuple with 10 rows, just so you can have that test value.

Managing Rows and Columns

One of the most common things you have to do when manipulating spreadsheets is adding or removing rows and columns. The openpyxl package allows you to do that in a very straightforward way by using the methods:

  • .insert_rows()
  • .delete_rows()
  • .insert_cols()
  • .delete_cols()

Every single one of those methods can receive two arguments:

  1. idx
  2. amount

Using our basic hello_world.xlsx example again, let’s see how these methods work:

>>> print_rows()
('hello', 'world!')

>>> # Insert a column before the existing column 1 ("A")
>>> sheet.insert_cols(idx=1)
>>> print_rows()
(None, 'hello', 'world!')

>>> # Insert 5 columns between column 2 ("B") and 3 ("C")
>>> sheet.insert_cols(idx=3, amount=5)
>>> print_rows()
(None, 'hello', None, None, None, None, None, 'world!')

>>> # Delete the created columns
>>> sheet.delete_cols(idx=3, amount=5)
>>> sheet.delete_cols(idx=1)
>>> print_rows()
('hello', 'world!')

>>> # Insert a new row in the beginning
>>> sheet.insert_rows(idx=1)
>>> print_rows()
(None, None)
('hello', 'world!')

>>> # Insert 3 new rows in the beginning
>>> sheet.insert_rows(idx=1, amount=3)
>>> print_rows()
(None, None)
(None, None)
(None, None)
(None, None)
('hello', 'world!')

>>> # Delete the first 4 rows
>>> sheet.delete_rows(idx=1, amount=4)
>>> print_rows()
('hello', 'world!')

The only thing you need to remember is that when inserting new data (rows or columns), the insertion happens before the idx parameter.

So, if you do insert_rows(1), it inserts a new row before the existing first row.

It’s the same for columns: when you call insert_cols(2), it inserts a new column right before the already existing second column (B).

However, when deleting rows or columns, .delete_... deletes data starting from the index passed as an argument.

For example, when doing delete_rows(2) it deletes row 2, and when doing delete_cols(3) it deletes the third column (C).

Managing Sheets

Sheet 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:

sheet = workbook.active

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:

>>> # Let's say you have two sheets: "Products" and "Company Sales"
>>> workbook.sheetnames
['Products', 'Company Sales']

>>> # You can select a sheet using its title
>>> products_sheet = workbook["Products"]
>>> sales_sheet = workbook["Company Sales"]

You can also change a sheet title very easily:

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> products_sheet.title = "New Products"

>>> workbook.sheetnames
['New Products', 'Company Sales']

If you want to create or delete sheets, then you can also do that with .create_sheet() and .remove():

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> operations_sheet = workbook.create_sheet("Operations")
>>> workbook.sheetnames
['Products', 'Company Sales', 'Operations']

>>> # You can also define the position to create the sheet at
>>> hr_sheet = workbook.create_sheet("HR", 0)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales', 'Operations']

>>> # To remove them, just pass the sheet as an argument to the .remove()
>>> workbook.remove(operations_sheet)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales']

>>> workbook.remove(hr_sheet)
>>> workbook.sheetnames
['Products', 'Company Sales']

One other thing you can do is make duplicates of a sheet using copy_worksheet():

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> products_sheet = workbook["Products"]
>>> workbook.copy_worksheet(products_sheet)
<Worksheet "Products Copy">

>>> workbook.sheetnames
['Products', 'Company Sales', 'Products Copy']

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 Columns

Something 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, openpyxl also has a way to accomplish this by using the worksheet freeze_panes attribute. For this example, go back to our sample.xlsx spreadsheet and try doing the following:

>>> workbook = load_workbook(filename="sample.xlsx")
>>> sheet = workbook.active
>>> sheet.freeze_panes = "C2"
>>> workbook.save("sample_frozen.xlsx")

If you open the sample_frozen.xlsx spreadsheet in your favorite spreadsheet editor, you’ll notice that row 1 and columns A and B 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 1 and columns A and B.

Adding Filters

You can use openpyxl to add filters and sorts to your spreadsheet. However, when you open the spreadsheet, the data won’t be rearranged according to these sorts and filters.

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 sample.xlsx spreadsheet:

>>> # Check the used spreadsheet space using the attribute "dimensions"
>>> sheet.dimensions
'A1:O100'

>>> sheet.auto_filter.ref = "A1:O100"
>>> workbook.save(filename="sample_with_filters.xlsx")

You should now see the filters created when opening the spreadsheet in your editor:

You don’t have to use sheet.dimensions if you know precisely which part of the spreadsheet you want to apply filters to.

Adding Formulas

Formulas (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 openpyxl is as simple as editing the value of a cell.

You can see the list of formulas supported by openpyxl:

>>> from openpyxl.utils import FORMULAE
>>> FORMULAE
frozenset({'ABS',
'ACCRINT',
'ACCRINTM',
'ACOS',
'ACOSH',
'AMORDEGRC',
'AMORLINC',
'AND',
...
'YEARFRAC',
'YIELD',
'YIELDDISC',
'YIELDMAT',
'ZTEST'})

Let’s add some formulas to our sample.xlsx spreadsheet.

Starting with something easy, let’s check the average star rating for the 99 reviews within the spreadsheet:

>>> # Star rating is column "H"
>>> sheet["P2"] = "=AVERAGE(H2:H100)"
>>> workbook.save(filename="sample_formulas.xlsx")

If you open the spreadsheet now and go to cell P2, 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:

>>> # The helpful votes are counted on column "I"
>>> sheet["P3"] = '=COUNTIF(I2:I100, ">0")'
>>> workbook.save(filename="sample_formulas.xlsx")

You should get the number 21 on your P3 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: "=COUNTIF(I2:I100, ">0")".

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 Styles

Even though styling a spreadsheet might not be something you would do every day, it’s still good to know how to do it.

Using openpyxl, you can apply multiple styling options to your spreadsheet, including fonts, borders, colors, and so on. Have a look at the openpyxl documentation to learn more.

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 sample.xlsx again as the base spreadsheet:

>>> # Import necessary style classes
>>> from openpyxl.styles import Font, Color, Alignment, Border, Side, colors

>>> # Create a few styles
>>> bold_font = Font(bold=True)
>>> big_red_text = Font(color=colors.RED, size=20)
>>> center_aligned_text = Alignment(horizontal="center")
>>> double_border_side = Side(border_style="double")
>>> square_border = Border(top=double_border_side,
... right=double_border_side,
... bottom=double_border_side,
... left=double_border_side)

>>> # Style some cells!
>>> sheet["A2"].font = bold_font
>>> sheet["A3"].font = big_red_text
>>> sheet["A4"].alignment = center_aligned_text
>>> sheet["A5"].border = square_border
>>> workbook.save(filename="sample_styles.xlsx")

If you open your spreadsheet now, you should see quite a few different styles on the first 5 cells of column A:

There you go. You got:

  • A2 with the text in bold
  • A3 with the text in red and bigger font size
  • A4 with the text centered
  • A5 with a square border around the text

Note: For the colors, you can also use HEX codes instead by doing Font(color="C70E0F").

You can also combine styles by simply adding them to the cell at the same time:

>>> # Reusing the same styles from the example above
>>> sheet["A6"].alignment = center_aligned_text
>>> sheet["A6"].font = big_red_text
>>> sheet["A6"].border = square_border
>>> workbook.save(filename="sample_styles.xlsx")

Have a look at cell A6 here:

When you want to apply multiple styles to one or several cells, you can use a NamedStyle class instead, which is like a style template that you can use over and over again. Have a look at the example below:

>>> from openpyxl.styles import NamedStyle

>>> # Let's create a style template for the header row
>>> header = NamedStyle(name="header")
>>> header.font = Font(bold=True)
>>> header.border = Border(bottom=Side(border_style="thin"))
>>> header.alignment = Alignment(horizontal="center", vertical="center")

>>> # Now let's apply this to all first row (header) cells
>>> header_row = sheet[1]
>>> for cell in header_row:
... cell.style = header

>>> workbook.save(filename="sample_styles.xlsx")

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 openpyxl documentation and see what other things you can do.

Conditional Formatting

This 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—sample.xlsx—and add some conditional formatting.

You can start by adding a simple one that adds a red background to all reviews with less than 3 stars:

>>> from openpyxl.styles import PatternFill, colors
>>> from openpyxl.styles.differential import DifferentialStyle
>>> from openpyxl.formatting.rule import Rule

>>> red_background = PatternFill(bgColor=colors.RED)
>>> diff_style = DifferentialStyle(fill=red_background)
>>> rule = Rule(type="expression", dxf=diff_style)
>>> rule.formula = ["$H1<3"]
>>> sheet.conditional_formatting.add("A1:O100", rule)
>>> workbook.save("sample_conditional_formatting.xlsx")

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 DifferentialStyle and Rule:

  • DifferentialStyle is quite similar to NamedStyle, which you already saw above, and it’s used to aggregate multiple styles such as fonts, borders, alignment, and so forth.
  • Rule is responsible for selecting the cells and applying the styles if the cells match the rule’s logic.

Using a Rule object, you can create numerous conditional formatting scenarios.

However, for simplicity sake, the openpyxl package offers 3 built-in formats that make it easier to create a few common conditional formatting patterns. These built-ins are:

  • ColorScale
  • IconSet
  • DataBar

The ColorScale gives you the ability to create color gradients:

>>> from openpyxl.formatting.rule import ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="min",
... start_color=colors.RED,
... end_type="max",
... end_color=colors.GREEN)

>>> # Again, let's add this gradient to the star ratings, column "H"
>>> sheet.conditional_formatting.add("H2:H100", color_scale_rule)
>>> workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")

Now you should see a color gradient on column H, from red to green, according to the star rating:

You can also add a third color and make two gradients instead:

>>> from openpyxl.formatting.rule import ColorScaleRule
>>> color_scale_rule = ColorScaleRule(start_type="num",
... start_value=1,
... start_color=colors.RED,
... mid_type="num",
... mid_value=3,
... mid_color=colors.YELLOW,
... end_type="num",
... end_value=5,
... end_color=colors.GREEN)

>>> # Again, let's add this gradient to the star ratings, column "H"
>>> sheet.conditional_formatting.add("H2:H100", color_scale_rule)
>>> workbook.save(filename="sample_conditional_formatting_color_scale_3.xlsx")

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:

>>> from openpyxl.formatting.rule import IconSetRule

>>> icon_set_rule = IconSetRule("5Arrows", "num", [1, 2, 3, 4, 5])
>>> sheet.conditional_formatting.add("H2:H100", icon_set_rule)
>>> workbook.save("sample_conditional_formatting_icon_set.xlsx")

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 openpyxl package has a full list of other icons you can use, besides the arrow.

Finally, the DataBar allows you to create progress bars:

>>> from openpyxl.formatting.rule import DataBarRule

>>> data_bar_rule = DataBarRule(start_type="num",
... start_value=1,
... end_type="num",
... end_value="5",
... color=colors.GREEN)
>>> sheet.conditional_formatting.add("H2:H100", data_bar_rule)
>>> workbook.save("sample_conditional_formatting_data_bar.xlsx")

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 openpyxl documentation to see a bunch of other options.

Adding Images

Even 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 openpyxl, you’ll have to install Pillow:

$ pip install Pillow

Apart from that, you’ll also need an image. For this example, you can grab the Real Python logo below and convert it from .webp to .png using an online converter such as cloudconvert.com, save the final file as logo.png, 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 hello_word.xlsx spreadsheet:

from openpyxl import load_workbook
from openpyxl.drawing.image import Image

Let's use the hello_world spreadsheet since it has less data

workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active

logo = Image("logo.png")

A bit of resizing to not fill the whole spreadsheet with the logo

logo.height = 150
logo.width = 150

sheet.add_image(logo, "A3")
workbook.save(filename="hello_world_logo.xlsx")

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, A3.

Adding Pretty Charts

Another 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. openpyxl has support for a lot of them.

Here, you’ll see only a couple of examples of charts because the theory behind it is the same for every single chart type:

Note: A few of the chart types that openpyxl currently doesn’t have support for are Funnel, Gantt, Pareto, Treemap, Waterfall, Map, and Sunburst.

For any chart you want to build, you’ll need to define the chart type: BarChart, LineChart, and so forth, plus the data to be used for the chart, which is called Reference.

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:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
workbook = Workbook()
sheet = workbook.active

Let's create some sample sales data

rows = [
["Product", "Online", "Store"],
[1, 30, 45],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
sheet.append(row)

Now you’re going to start by creating a bar chart that displays the total number of sales per product:

chart = BarChart()
data = Reference(worksheet=sheet,
min_row=1,
max_row=8,
min_col=2,
max_col=3)
chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, "E2")
workbook.save("chart.xlsx")

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 E2.

Note: Depending on whether you’re using Microsoft Excel or an open-source alternative (LibreOffice or OpenOffice), the chart might look slightly different.

Try creating a line chart instead, changing the data a bit:

import random
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
workbook = Workbook()
sheet = workbook.active

Let's create some sample sales data

rows = [
["", "January", "February", "March", "April",
"May", "June", "July", "August", "September",
"October", "November", "December"],
[1, ],
[2, ],
[3, ],
]
for row in rows:
sheet.append(row)
for row in sheet.iter_rows(min_row=2,
max_row=4,
min_col=2,
max_col=13):
for cell in row:
cell.value = random.randrange(5, 100)

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:

chart = LineChart()
data = Reference(worksheet=sheet,
min_row=2,
max_row=4,
min_col=1,
max_col=13)
chart.add_data(data, from_rows=True, titles_from_data=True)
sheet.add_chart(chart, "C6")
workbook.save("line_chart.xlsx")

Here’s the outcome of the above piece of code:

One thing to keep in mind here is the fact that you’re using from_rows=True 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 from_rows. 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 Reference now starts from the first column, min_col=1, 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:

cats = Reference(worksheet=sheet,
min_row=1,
max_row=1,
min_col=2,
max_col=13)
chart.set_categories(cats)

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 x_axis and y_axis:

chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"

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 ChartStyle 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:

# You can play with this by choosing any number between 1 and 48
chart.style = 24

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.

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 Spreadsheet

You 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:

from dataclasses import dataclass
from typing import List

@dataclass
class Sale:
id: str
quantity: int

@dataclass
class Product:
id: str
name: str
sales: List[Sale]

Now, let’s generate some random data, assuming the above classes are stored in a db_classes.py file:

import random

Ignore these for now. You'll use them in a sec ;)

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from db_classes import Product, Sale
products = []

Let's create 5 products

for idx in range(1, 6):
sales = []
# Create 5 months of sales
for _ in range(5):
sale = Sale(quantity=random.randrange(5, 100))
sales.append(sale)
product = Product(id=str(idx),
name="Product %s" % idx,
sales=sales)
products.append(product)

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:

workbook = Workbook()
sheet = workbook.active

Append column names first

sheet.append(["Product ID", "Product Name", "Month 1",
"Month 2", "Month 3", "Month 4", "Month 5"])

Append the data

for product in products:
data = [product.id, product.name]
for sale in product.sales:
data.append(sale.quantity)
sheet.append(data)

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:

chart = LineChart()
data = Reference(worksheet=sheet,
min_row=2,
max_row=6,
min_col=2,
max_col=7)
chart.add_data(data, titles_from_data=True, from_rows=True)
sheet.add_chart(chart, "B8")
cats = Reference(worksheet=sheet,
min_row=1,
max_row=1,
min_col=3,
max_col=7)
chart.set_categories(cats)
chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"
workbook.save(filename="oop_sample.xlsx")

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 Pandas

Even 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 openpyxl directly.

For example, some of the advantages of using openpyxl are the ability to easily customize your spreadsheet with styles, conditional formatting, and such.

But guess what, you don’t have to worry about picking. In fact, openpyxl has support for both converting data from a Pandas DataFrame into a workbook or the opposite, converting an openpyxl workbook into a Pandas DataFrame.

Note: If you’re new to Pandas, check our course on Pandas DataFrames beforehand.

First things first, remember to install the pandas package:

$ pip install pandas

Then, let’s create a sample DataFrame:

import pandas as pd
data = {
"Product Name": ["Product 1", "Product 2"],
"Sales Month 1": [10, 20],
"Sales Month 2": [5, 35],
}
df = pd.DataFrame(data)

Now that you have some data, you can use .dataframe_to_rows() to convert it from a DataFrame into a worksheet:

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
workbook = Workbook()
sheet = workbook.active
for row in dataframe_to_rows(df, index=False, header=True):
sheet.append(row)
workbook.save("pandas.xlsx")

You should see a spreadsheet that looks like this:

If you want to add the DataFrame’s index, you can change index=True, 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:

import pandas as pd
from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

values = sheet.values
df = pd.DataFrame(values)

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:

import pandas as pd
from openpyxl import load_workbook
from mapping import REVIEW_ID

workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active

data = sheet.values

Set the first row as the columns for the DataFrame

cols = next(data)
data = list(data)

Set the field "review_id" as the indexes for each row

idx = [row[REVIEW_ID] for row in data]

df = pd.DataFrame(data, index=idx, columns=cols)

Using indexes and columns allows you to access data from your DataFrame easily:

>>> df.columns
Index(['marketplace', 'customer_id', 'review_id', 'product_id',
'product_parent', 'product_title', 'product_category', 'star_rating',
'helpful_votes', 'total_votes', 'vine', 'verified_purchase',
'review_headline', 'review_body', 'review_date'],
dtype='object')

>>> # Get first 10 reviews' star rating
>>> df["star_rating"][:10]
R3O9SGZBVQBV76 5
RKH8BNC3L5DLF 5
R2HLE8WKZSU3NL 2
R31U3UH5AZ42LL 5
R2SV659OUJ945Y 4
RA51CP8TR5A2L 5
RB2Q7DLDN6TH6 5
R2RHFJV0UYBK3Y 1
R2Z6JOQ94LFHEP 5
RX27XIIWY5JPB 4
Name: star_rating, dtype: int64

>>> # Grab review with id "R2EQL1V1L6E0C9", using the index
>>> df.loc["R2EQL1V1L6E0C9"]
marketplace US
customer_id 15305006
review_id R2EQL1V1L6E0C9
product_id B004LURNO6
product_parent 892860326
review_headline Five Stars
review_body Love it
review_date 2015-08-31
Name: R2EQL1V1L6E0C9, dtype: object

There you go, whether you want to use openpyxl to prettify your Pandas dataset or use Pandas to do some hardcore algebra, you now know how to switch between both packages.

Conclusion

Phew, after that long read, you now know how to work with spreadsheets in Python! You can rely on openpyxl, your trustworthy companion, to:

  • Extract valuable information from spreadsheets in a Pythonic manner
  • Create your own spreadsheets, no matter the complexity level
  • Add cool features such as conditional formatting or charts to your spreadsheets

There are a few other things you can do with openpyxl that might not have been covered in this tutorial, but you can always check the package’s official documentation website to learn more about it. You can even venture into checking its source code and improving the package further.

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.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Python

Complete Python Bootcamp: Go from zero to hero in Python 3

Machine Learning A-Z™: Hands-On Python & R In Data Science

Python and Django Full Stack Web Developer Bootcamp

Complete Python Masterclass

Python Tutorial - Python GUI Programming - Python GUI Examples (Tkinter Tutorial)

Computer Vision Using OpenCV

OpenCV Python Tutorial - Computer Vision With OpenCV In Python

Python Tutorial: Image processing with Python (Using OpenCV)

A guide to Face Detection in Python

Excel vs SQL: A Conceptual Comparison

Excel vs SQL: A Conceptual Comparison

An experienced Excel-user’s perspective of SQL and why it’s worth learning.

Introduction

I have been involved in the data analytics realm for about 3 years. I’ve worked in the field for over 2 years as a healthcare analyst, and I recently finished my MBA with a focus in data science.

During my masters I was particularly interested in predictive modeling techniques using python (and I still am). However, from a basic organization /analysis / reporting perspective, I am hands-down most comfortable using Excel. Spending 60–80% of the day staring into an Excel spreadsheet is something not foreign to me. The tabs, ribbons, groupings, and tools nested within Excel’s GUI are the instruments to my orchestra. They make the sounds, but I conduct and transpose the melody and harmony into reports, deliverables, and analyses. Throughout consulting projects, personal budgeting, and side hustles, Excel has always been my go-to tool of choice.

I have known about SQL and its basic concepts for a long time. However, it wasn’t until relatively recently that I decided to buckle down and learn it for professional reasons. From an Excel user’s perspective, SQL has its ups and downs. In this article, I hope to convey the nature and use of SQL by comparing it to Excel.

What are they?

Excel is a program. SQL is a language. That is a very important piece of information to digest. Excel can only be used after clicking the green icon and running the program. SQL, on the other hand, can be used to interact and communicate with database programs. A few of the most popular:

The way I learned SQL is through Google’s Big Query. It’s an entertaining way to learn / use SQL by analyzing huge databases on Google cloud available for free.

Where is the data?

Excel

Excel is the quintessential spreadsheet tool. You have your data saved in a file on your computer and its typically organized in tabs, columns and rows. The excel file is local to your computer. You are directly interacting with it. No middleman. No administrator. Sure, it’s possible to use API’s to bring data from another location; however, the data is ultimately yours to do with what you want. This makes tracking changes difficult if several people are collaborating with Excel workbooks… It’s indeed possible to track changes, but it’s not very convenient.

SQL

SQL is a language that interacts with databases. It stands for Structured Query Language. Your data is one step further away in this case. You write and send queries in SQL to the database which receives these queries and then gives you what you request or makes changes. The data is stored in a database and organized by tables.The beauty of querying is it’s more collaborative and traceable. These queries can be traced back to see who made what changes to which table. Users can also save and share useful queries with other for future or collaborative purposes.

Example of a query that filters the “austin_bikeshare” table on Big Query based on “bikeid” and then orders the selection by “duration_minutes”.

SELECT 

   duration_minutes

FROM

  bigquery-public-data.austin_bikeshare.bikeshare_trips

Where

   bikeid = "446"

ORDER BY

   duration_minutes desc

Once you know how the syntax works, manipulating data can be much faster using SQL than with Excel. Another great aspect is that the syntax is similar to English which makes it arguably the easiest computer language to learn.

What they are best used for?

Excel

  • Smaller data sets: under 1 million rows, even north of 100,000 it will likely slow down your computer.
  • Manually entering data
  • More flexible structure: any cell can be of any data type, regardless of what column it’s in.
  • Outputting graphs and visualizations
  • Built-in spell check and other useful functions
  • Working independently on a project

SQL

  • Larger datasets: depending on the software and database, this can be very very large. Doesn’t slow down like Excel does.
  • Organization /Structure: SQL tables are more strict about consistent data types and restricts users if they try to enter the wrong type.
  • Collaborative work
  • Prepping data for further analysis in another software
  • Consistent reports or calculations: as mentioned earlier, you can save and share queries.
  • More secure, as changes are always traceable and auditable.
Conclusion

When I first learned about JOIN clauses in SQL, my initial visceral reaction was to label it as insignificant because I already knew how to use Vlookups in Excel. I kept this attitude for a little while afterwards, but as I kept going throughout the lessons the reality of the situation started to emerge. As I learned how easy and useful JOIN clauses were, I remembered all the times Vlookups took forever to execute over large quantities of rows. I remembered how they make the file size exponentially bigger if you don’t paste values after you run the calculation. I also remembered how limiting it is by bringing only 1 value at a time… Similar lessons were experienced as I compared SQL to Excel throughout my learning.

In conclusion, both tools have their place when it comes to data analytics. Both serve their unique purpose, and knowing both is beneficial for anyone who uses data regularly. From my experience and research on the topic, however, SQL is a more in demand and useful skill to have as a data analyst. Excel is great for small business owners, consultants and students. SQL is better for analysts and data scientists.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

The Complete Oracle SQL Certification Course

An Introduction to Queries in MySQL

How To Troubleshoot MySQL Queries?

SQL with MySQL - Complete Tutorial for Beginners

How to import CSV file using MySQL?

Export or Import of CSV or Excel file in Laravel 5.8 with MySQL


HTML 5 Tutorial: How to create a table using HTML tags and Stylesheet (CSS)

HTML 5 Tutorial: How to create a table using HTML tags and Stylesheet (CSS)

In this article, we want to show you how to create a table using HTML tags and Stylesheet (CSS). HTML table may vary depends on data and style requirements. Sometimes, in the real application, we use an HTML table as a layout of the Email template in HTML format.

In this article, we want to show you how to create a table using HTML tags and Stylesheet (CSS). HTML table may vary depends on data and style requirements. Sometimes, in the real application, we use an HTML table as a layout of the Email template in HTML format.

HTML Table uses to represent tabular data like in the Excel Application and arrange the layout of the Web View.

There are some common HTML tags that use by HTML table:

Before start practicing HTML 5 table, make sure all

tag put inside complete and tag.

<!DOCTYPE html>
<html>
 <head>
  <title>Basic HTML Table</title>
 </head>
 <body>
  <table></table>
 </body>
</html>

Basic HTML Table

Here is an example of a basic HTML table or common use of the above HTML tags to define or create a table.

<table>
 <tr>
  <th>No.</th>
  <th>Full Name</th>
  <th>Position</th>
  <th>Salary</th>
 </tr>
 <tr>
  <td>1</td>
  <td>Bill Gates</td>
  <td>Founder Microsoft</td>
  <td><table>
 <tr>
  <th>No.</th>
  <th>Full Name</th>
  <th>Position</th>
  <th>Salary</th>
 </tr>
 <tr>
  <td>1</td>
  <td>Bill Gates</td>
  <td>Founder Microsoft</td>
  <td>$1000</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Steve Jobs</td>
  <td>Founder Apple</td>
  <td>$1200</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Larry Page</td>
  <td>Founder Google</td>
  <td>$1100</td>
 </tr>
 <tr>
  <td>4</td>
  <td>Mark Zuckeberg</td>
  <td>Founder Facebook</td>
  <td>$1300</td>
 </tr>
</table>
000</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Steve Jobs</td>
  <td>Founder Apple</td>
  <td><table>
 <tr>
  <th>No.</th>
  <th>Full Name</th>
  <th>Position</th>
  <th>Salary</th>
 </tr>
 <tr>
  <td>1</td>
  <td>Bill Gates</td>
  <td>Founder Microsoft</td>
  <td>$1000</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Steve Jobs</td>
  <td>Founder Apple</td>
  <td>$1200</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Larry Page</td>
  <td>Founder Google</td>
  <td>$1100</td>
 </tr>
 <tr>
  <td>4</td>
  <td>Mark Zuckeberg</td>
  <td>Founder Facebook</td>
  <td>$1300</td>
 </tr>
</table>
200</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Larry Page</td>
  <td>Founder Google</td>
  <td><table>
 <tr>
  <th>No.</th>
  <th>Full Name</th>
  <th>Position</th>
  <th>Salary</th>
 </tr>
 <tr>
  <td>1</td>
  <td>Bill Gates</td>
  <td>Founder Microsoft</td>
  <td>$1000</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Steve Jobs</td>
  <td>Founder Apple</td>
  <td>$1200</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Larry Page</td>
  <td>Founder Google</td>
  <td>$1100</td>
 </tr>
 <tr>
  <td>4</td>
  <td>Mark Zuckeberg</td>
  <td>Founder Facebook</td>
  <td>$1300</td>
 </tr>
</table>
100</td>
 </tr>
 <tr>
  <td>4</td>
  <td>Mark Zuckeberg</td>
  <td>Founder Facebook</td>
  <td><table>
 <tr>
  <th>No.</th>
  <th>Full Name</th>
  <th>Position</th>
  <th>Salary</th>
 </tr>
 <tr>
  <td>1</td>
  <td>Bill Gates</td>
  <td>Founder Microsoft</td>
  <td>$1000</td>
 </tr>
 <tr>
  <td>2</td>
  <td>Steve Jobs</td>
  <td>Founder Apple</td>
  <td>$1200</td>
 </tr>
 <tr>
  <td>3</td>
  <td>Larry Page</td>
  <td>Founder Google</td>
  <td>$1100</td>
 </tr>
 <tr>
  <td>4</td>
  <td>Mark Zuckeberg</td>
  <td>Founder Facebook</td>
  <td>$1300</td>
 </tr>
</table>
300</td>
 </tr>
</table>

Output:

As a default, HTML 5 table not defined with border, you should add the border manually in each table cells.

HTML Table with Border

To add a basic border to HTML 5 table, simply add this style attribute in

tag.

<table style="border: solid 1px #aaa999;">

Output:

As you can see, Table Border only draw lines to the table only and cells left borderless. To make border for all cells, add style attribute to all and all .

<table style="border: solid 1px #aaa999;">
 <tr>
  <th style="border: solid 1px #aaa999;">No.</th>
  <th style="border: solid 1px #aaa999;">Full Name</th>
  <th style="border: solid 1px #aaa999;">Position</th>
  <th style="border: solid 1px #aaa999;">Salary</th>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">1</td>
  <td style="border: solid 1px #aaa999;">Bill Gates</td>
  <td style="border: solid 1px #aaa999;">Founder Microsoft</td>
  <td style="border: solid 1px #aaa999;"><table style="border: solid 1px #aaa999;">
 <tr>
  <th style="border: solid 1px #aaa999;">No.</th>
  <th style="border: solid 1px #aaa999;">Full Name</th>
  <th style="border: solid 1px #aaa999;">Position</th>
  <th style="border: solid 1px #aaa999;">Salary</th>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">1</td>
  <td style="border: solid 1px #aaa999;">Bill Gates</td>
  <td style="border: solid 1px #aaa999;">Founder Microsoft</td>
  <td style="border: solid 1px #aaa999;">$1000</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">2</td>
  <td style="border: solid 1px #aaa999;">Steve Jobs</td>
  <td style="border: solid 1px #aaa999;">Founder Apple</td>
  <td style="border: solid 1px #aaa999;">$1200</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">3</td>
  <td style="border: solid 1px #aaa999;">Larry Page</td>
  <td style="border: solid 1px #aaa999;">Founder Google</td>
  <td style="border: solid 1px #aaa999;">$1100</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">4</td>
  <td style="border: solid 1px #aaa999;">Mark Zuckeberg</td>
  <td style="border: solid 1px #aaa999;">Founder Facebook</td>
  <td style="border: solid 1px #aaa999;">$1300</td>
 </tr>
</table>
000</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">2</td>
  <td style="border: solid 1px #aaa999;">Steve Jobs</td>
  <td style="border: solid 1px #aaa999;">Founder Apple</td>
  <td style="border: solid 1px #aaa999;"><table style="border: solid 1px #aaa999;">
 <tr>
  <th style="border: solid 1px #aaa999;">No.</th>
  <th style="border: solid 1px #aaa999;">Full Name</th>
  <th style="border: solid 1px #aaa999;">Position</th>
  <th style="border: solid 1px #aaa999;">Salary</th>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">1</td>
  <td style="border: solid 1px #aaa999;">Bill Gates</td>
  <td style="border: solid 1px #aaa999;">Founder Microsoft</td>
  <td style="border: solid 1px #aaa999;">$1000</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">2</td>
  <td style="border: solid 1px #aaa999;">Steve Jobs</td>
  <td style="border: solid 1px #aaa999;">Founder Apple</td>
  <td style="border: solid 1px #aaa999;">$1200</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">3</td>
  <td style="border: solid 1px #aaa999;">Larry Page</td>
  <td style="border: solid 1px #aaa999;">Founder Google</td>
  <td style="border: solid 1px #aaa999;">$1100</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">4</td>
  <td style="border: solid 1px #aaa999;">Mark Zuckeberg</td>
  <td style="border: solid 1px #aaa999;">Founder Facebook</td>
  <td style="border: solid 1px #aaa999;">$1300</td>
 </tr>
</table>
200</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">3</td>
  <td style="border: solid 1px #aaa999;">Larry Page</td>
  <td style="border: solid 1px #aaa999;">Founder Google</td>
  <td style="border: solid 1px #aaa999;"><table style="border: solid 1px #aaa999;">
 <tr>
  <th style="border: solid 1px #aaa999;">No.</th>
  <th style="border: solid 1px #aaa999;">Full Name</th>
  <th style="border: solid 1px #aaa999;">Position</th>
  <th style="border: solid 1px #aaa999;">Salary</th>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">1</td>
  <td style="border: solid 1px #aaa999;">Bill Gates</td>
  <td style="border: solid 1px #aaa999;">Founder Microsoft</td>
  <td style="border: solid 1px #aaa999;">$1000</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">2</td>
  <td style="border: solid 1px #aaa999;">Steve Jobs</td>
  <td style="border: solid 1px #aaa999;">Founder Apple</td>
  <td style="border: solid 1px #aaa999;">$1200</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">3</td>
  <td style="border: solid 1px #aaa999;">Larry Page</td>
  <td style="border: solid 1px #aaa999;">Founder Google</td>
  <td style="border: solid 1px #aaa999;">$1100</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">4</td>
  <td style="border: solid 1px #aaa999;">Mark Zuckeberg</td>
  <td style="border: solid 1px #aaa999;">Founder Facebook</td>
  <td style="border: solid 1px #aaa999;">$1300</td>
 </tr>
</table>
100</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">4</td>
  <td style="border: solid 1px #aaa999;">Mark Zuckeberg</td>
  <td style="border: solid 1px #aaa999;">Founder Facebook</td>
  <td style="border: solid 1px #aaa999;"><table style="border: solid 1px #aaa999;">
 <tr>
  <th style="border: solid 1px #aaa999;">No.</th>
  <th style="border: solid 1px #aaa999;">Full Name</th>
  <th style="border: solid 1px #aaa999;">Position</th>
  <th style="border: solid 1px #aaa999;">Salary</th>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">1</td>
  <td style="border: solid 1px #aaa999;">Bill Gates</td>
  <td style="border: solid 1px #aaa999;">Founder Microsoft</td>
  <td style="border: solid 1px #aaa999;">$1000</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">2</td>
  <td style="border: solid 1px #aaa999;">Steve Jobs</td>
  <td style="border: solid 1px #aaa999;">Founder Apple</td>
  <td style="border: solid 1px #aaa999;">$1200</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">3</td>
  <td style="border: solid 1px #aaa999;">Larry Page</td>
  <td style="border: solid 1px #aaa999;">Founder Google</td>
  <td style="border: solid 1px #aaa999;">$1100</td>
 </tr>
 <tr>
  <td style="border: solid 1px #aaa999;">4</td>
  <td style="border: solid 1px #aaa999;">Mark Zuckeberg</td>
  <td style="border: solid 1px #aaa999;">Founder Facebook</td>
  <td style="border: solid 1px #aaa999;">$1300</td>
 </tr>
</table>
300</td>
 </tr>
</table>

If you want a simple coding without writing a style for each cells, use

Accessing Google Spreadsheet Data using Python

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

As you all are familiar with importing, exporting and manipulating comma separate files (CSV) using Python, Hereby in this article I’m going to show you the step by step guide to access Google Spreadsheets on the cloud using Python.

As the very first thing, go to Google API Manager by simply googling it and go to https://console.developers.google.com/

https://console.developers.google.com/

To kick things off first create a new project.

Figure 1.0: Creating a new Project

I’ll name my project as ‘Telemedicine’ since we will be working with a spreadsheet which includes all the tweets related to Telemedicine hashtags which I extracted earlier (Click here to see how I extracted tweets using hashtags). Define a suitable project name according to your dataset on the cloud then click CREATE to initiate the project. (You don’t have to worry about the Location* below the project name)

Figure 1.0: Define a suitable project name according to your dataset

OKAY. The first part is done, Now go to API Library and search for Google Drive.

Figure 2.0: Search for Google Drive in API Library

Then add Google Drive API to our project which will allow us to access spreadsheet inside of Google Sheets for our account.

Figure 2.1: Click on Enable

Once that’s added, we need to create some credentials to access the API so click on Add Credentials on the next screen you see after enabling the API.

Figure 3.0: Click on Create Credentials to initialize credentials to access the API

Since we’ll be accessing the API using a web server, We’ll add the Web Serveroption on this page and give access to Application Data and tell them that you’re not running your application on either GCE or GAE by selecting the option ‘No, I’m not using them’ then click on the button below.

Figure 3.1: Creating credentials

Next, we will create a service account named Employees and assigned it the role Project Editor which will allow it to access and edit all the data within the API. Clicking continue will generate a JSON file that I will rename and add it to the project as Telemedicine_secret.json.

Figure 3.2: Creating service account credentials

Then open the JSON file in a text editor (I prefer Atom) :)

Inside the file, you can locate an email address property called “client_email”, if we copy that and take it over to our spreadsheet on the cloud, we can share that particular spreadsheet with the email address we provide to give us access to it from the API.

Figure 4.0: Copy the email address property client_email

Figure 4.1: Paste that copied email as a shared email address

Reading spreadsheet data with Python

Let’s move into the terminal to install gspread and oauth2client packagesand wait till all components get installed.

$ pip install gspread oauth2client

Then I’m going to create a new python file called spreadsheet.py in my favorite editor ATOM and write the following code to import gspread and ServiceAccountCredentials from oauth2client .

import gspread
from oauth2client.service_account import ServiceAccountCredentials
  1. Then, we have to define the scope and create credentials using that scope and the content of employees_secret.json file.
  2. Then I’ll create a gspread client authorizing it using those credentials.
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)


3. NOW, We can access our google sheets so we’ll call a client.open and pass it the spreadsheet name and getting access to sheet1

sheet = client.open('telemedicine_data').sheet1

4. Now we can set our employees equal to all of the records inside that sheet and print them out to the terminal.

telemedicine = sheet.get_all_records()
print(telemedicine)

Let’s go the terminal try to run our program and we’ll get a glorious list of perfectly formatted content like in the image below (See the highlighted text and you can find all the columns in our dataset).

python spreadsheet.py

Figure 5.0: Display results as a list

Whoa! It wasn’t what you’re expecting, wasn’t it? Well, trust me I got the perfect solution for that!

We can clean up the result by using pprint module, using that we can create a prettyprinter that we can use to display the result and its a much nicer way to display the output.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open('employee_reviews').sheet1
pp = pprint.PrettyPrinter()
employees = sheet.get_all_records()
pp.pprint(employees)



Figure 5.1: Formatted results using pprint

If you want to go through the full documentation of gspread click here and in the meantime I’ll follow you up with these cool tricks.

Filtering Data

#to get all the values inside the file
sheet.get_all_values()
#to get exact row values in a second row (Since 1st row is the header)
sheet.row_values(2)
#to get all the column values in the column 'place'
sheet.col_values(16)
#to extract a particular cell value
sheet.cell(1, 1).value



Figure 6.0: sheet.col_values(16) to get all the row values in column 16 which is ‘place’


Figure 6.1: sheet.row_values(2) to get all the first row values


OK! Reading is all done. YES! But, Can we do more? Oh Yes!



Insert / Update and Delete from Spreadsheet

And also, you can enter a random row and can give values to each cell by separating each word using double quotations.

row = ["I'm","inserting","a","new","row","into","a,","Spreadsheet","using","Python"]
index = 3
sheet.insert_row(row, index)

Figure 6.2: Spreadsheet after inserting our new row

Furthermore, It’s also possible to alter data inside a specific cell. Look at how it is done, I’ll change the header column name ‘id’ to ‘telemedicine_id’:

sheet.update_cell(1, 1, "telemedicine_id")

Figure 6.3: After updating the header name which is cell(1,1)


Finally, In conclusion to this article, I’m going to wrap up proving that it is not essential to create databases to simple internal apps. Spreadsheets might be your best answer!

You can access the full code here.

Photo by Paul Gilmore on Unsplash


Thank you!

If you liked this post, share it with all of your programming buddies!

Follow me on Facebook | Twitter


Learn More

Complete Python Bootcamp: Go from zero to hero in Python 3

Machine Learning A-Z™: Hands-On Python & R In Data Science

Python and Django Full Stack Web Developer Bootcamp

Complete Python Masterclass

The Python Bible™ | Everything You Need to Program in Python

Python Tutorial for Beginners (2019) - Learn Python for Machine Learning and Web Development

MongoDB with Python Crash Course - Tutorial for Beginners

Learning Python: From Zero to Hero

A Complete Machine Learning Project Walk-Through in Python

MySQL Databases With Python Tutorial

An A-Z of useful Python tricks

How to automatically insert a copied row after a specific value in a cell with VBA

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself.

It might be easier to ignore the tag component and use a specific set of values that the code looks for, that's also possible as the values marked as bundles are always the same in the column. What I mean is, instead of looking for the -edubnd tag, the code just looks for a specific value in that column,

I have created a sample table below that is similar-enough to my table in excel that it should serve to illustrate the question.

I'm currently filtering out the dataset, copying it into a different excel document, then running this:

Sub insertrows()

Dim I As Long

Dim xCount As Integer

LableNumber:

xCount = 2

For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1

Rows(I).Copy

Rows(I).Resize(xCount).Insert

Next

Application.CutCopyMode = False

End Sub

_

CURRENT TABLE:
column1   |    column2    |  column3 |  column3
A | pear | blue | 10
A | apple | orange | 50
A | orange | yellow | 30
A | kiwi | yellow | 20
A | orange-edubnd | blue | 100
A | apple | green | 10
A | pear-edubnd | green | 50
A | mango | pink | 60

_

DESIRED TABLE

Note: the copied row after each distinct column2 with the -edubnd tag

 column1   |    column2    |  column3 |  column3

A | pear | blue | 10
A | apple | orange | 50
A | orange | yellow | 30
A | kiwi | yellow | 20
A | orange-edubnd | blue | 100
A | orange-edubnd | blue | 100
A | orange-edubnd | blue | 100
A | apple | green | 10
A | pear-edubnd | green | 50
A | pear-edubnd | green | 50
A | pear-edubnd | green | 50
A | mango | pink | 60