(upbeat music)
- [Instructor] All right, let's review my code
for this challenge.
So our challenge was to take the data in the first worksheet
of the financial sample file
and split it into multiple worksheets,
and each worksheet represents the sales data
for each of the countries in the entire dataset.
Let's take a look at my solution.
All right, so I chose to solve this challenge
by creating a function named split_workbook,
which takes the workbook to work on,
the name of the source sheet for the data,
and the column to split the data on.
So my code loads the workbook, if we look down here.
So we load the workbook
and then we call this function
with the name of the worksheet, right?
Which is in this case SalesData,
and the letter of the column to split on.
So that way I can choose
to group the data on a different column if I want to.
Let's take a look at how the function works.
So the function starts by getting the worksheet
that contains the source data,
which in our example is the sales data sheet.
Then I create a new empty set called new_sheets,
and this is going to hold the names
of each of the new sheets that will be created.
I also have a variable
that holds the name of the current worksheet.
Now, the reason I'm using a set
is because sets can only contain unique values,
which enables me to detect
if I've already started processing a particular country,
and we'll see why that's important in a moment.
The current_worksheet variable
keeps track of the sheet that I am currently adding data to.
So I use a for loop
to process all of the rows in the sales data sheet.
And you can see that I'm using the iter_rows function,
and we covered how to do that earlier in the chapter.
And I'm starting iter_rows off at row 2
in order to skip over the header values.
Then I get the index of the column,
and I actually am using a bit of a utility function
that's contained in the openpyxl utilities module.
And if you did a little bit of digging
inside the documentation,
because you might have run into this problem, right?
You probably found this function.
There are other ways, of course, to map the column letter
or the column index to the column letter
that we want to use,
but it doesn't really matter how you did that.
Okay, so I get the value
for the column from the current row,
which is going to be the country column
because in this case we're doing row B.
And then if I haven't already added the name of the country
to the new sheet set,
that means I have to create a worksheet
to represent that country.
So I add the name to the set,
and then I make the new worksheet
and set the value of current_worksheet to the new one.
Otherwise, if that sheet name is already in the set,
then that country worksheet already exists,
I've already created it,
I've already gone through this code once before
and I just need to get that sheet from the workbook.
So now that I have the proper worksheet reference,
I can copy the row of data to the right place.
So what I'm doing is I copy the value
of each cell in the row into this newrow variable,
and then I use the append function
to append that row into the new worksheet.
And again, we covered this earlier in the chapter.
So this is going to happen
for all the rows in the sales data sheet.
So for each one of the rows,
I'm going to look at the value,
I'm going to see if the sheets already been created.
If it has, then I get that worksheet reference,
otherwise I create a new one,
and I just keep adding or appending rows
onto the end of each sheet.
So when that loop completes,
I'm going to return the new sheets set
that has been created
as the return value of this function,
and we'll see why I do that in just a moment.
All right, so let's scroll down.
So at this point, after we called split_workbook,
my added_sheets result
contains the set of each of the added sheets.
And remember, we're not done yet,
we have to actually go and add the filters
to each one of those sheets.
So I loop over that set,
right, get each of the worksheets,
and then add the auto_filter
and set the dimensions, the size of that entire worksheet.
And then finally, I need to save the new workbook
as the word new plus the original name.
Now, because of the way I designed this function,
right, I can split it on any of the columns.
So I'm going to run my code again,
but this time I'm going to change the last argument
from column B to column A,
because remember, whoops, there we go.
Because remember,
column A contains data on the market segment.
If I click on FinancialSample,
you can see that column A has different segments.
We've got government, midmarket,
right, channel partners, and so on.
So now, instead of splitting on country names,
I'm going to split on segment names.
Let's save.
I'll run this again.
All right, and let's go back to Excel.
And now you can see that instead of country names,
I have data split by the different market segments.
All right, so that's my code.
How does your solution compare to mine?