Hey, in the previous video we added the date here, which corresponds to that piece of code.
Now, next we need to add the table with data.
So we need to dig inside the Excel files.
So how do we read these Excel files?
Can you guess the codes?
So can you try to write something here perhaps before I do it?
Well, the idea here well, the idea now is that we need to read the Excel files to get a data frame
out of each Excel file.
And we have already done that in here.
So you could do that in there, or you can just cut it and place it in here.
So either way is the same, but I would prefer to have the code in here since we don't need the data
frame up to this point.
Therefore, I'll read it here.
It makes more sense logically.
And then I say for index row in the f dot itr rose.
So you know already how we iterate over data frames and in each iteration we need to set the font.
Perhaps sets a text color and then adds a cell.
So let's set the font to something like.
Times at a size of ten, 80, 80, 80 should do it for the text color.
So that should be gray.
And then we add the cell.
So for that, let me show you the finished PDF, how it looks like.
So we need to add product like product name amounts, price per unit, total price.
So that would be something like with 30.
Usually we set the width to zero to expand the entire horizontal length of the PDF document.
This time we will use 30 and I'll explain you why in just a bit.
Let's use eight for height and the text is going to be row.
So that would be the first value.
For now, let's ignore this header.
Let's just add the values and then we think about how to add the header.
So now I'm working on this particular cell here.
With that, I'm adding that particular cell, the product ID.
So the row is from the Excel file.
Let's see the actual name, the exact name of the product I D column products underscore ID.
So as a string product underscore ID.
So that's one cell.
Now I will not add ln is equal to one this time because I want the next cell.
So I want the next cell to be written next to this cell.
So what I did is that I set a width of 30 for this cell, right?
That's what I did here in the code.
In here a width of 30 and then the next cell.
Is going to start from here up to here.
So let's give the cell a width of.
Let me delete that line.
We don't use that this time.
So pdaf that cell a width of.
74.
This a height of eight and txt.
This time is ro.
Let's see in the Excel file.
So that is product name.
Product underscore, name.
So we have to do the same for the other cells.
Amount purchase price per unit, total price.
So let me duplicate this code like that.
And I would give a width of 30 for this one.
So that is again amount purchased.
Amount underscore.
Purchased.
Then we've got price per unit per unit and total price.
Yeah, that's what we've got.
And oh, we need parentheses at the end here.
You see this underline in red.
That means something is wrong with the syntax.
So now it should be fine.
And then we have the output method, which is outside of the for loop.
That means once the full loop ends, once the full loop iterates over all the rows of the current Excel
file.
So we're working on the first Excel file in the master full loop.
And then in that Excel file we iterate over the rows.
Once all the rows of the Excel file are exhausted, then we output the PDF file.
So let's run this and see what we've got so far.
Perhaps an error int object has no attributes replace.
Now this is a tricky error for beginners, so perhaps you might have a hard time to fix this.
And in that case a good idea is to Google this error.
So let me demonstrate you how to search for this error like that.
And then you want to mention the PDF library so you get more accurate results.
Now, I do know the cause of this error, but I'm just trying to show you what an actual the actual
problem.
So now this post here is not actually about f PDF, but it's about another library.
And the idea is that we can get some clue here.
So this error is pointing to an issue which tells you that you're using an integer, but you should
be using a string you see in single quotes here.
So the zero should be changed to a string.
So that could give us some clue.
And perhaps you could search, you could check out the other results as well.
But what I'm trying to say is that this zero product ID value gives us an integer, but the PDF library
F PDF expects a string here as the value of the text argument.
So the solution here is to convert this into a string.
Not only that, but all the.
Items.
All of the other roles as well.
So like, that's be careful with the parentheses.
So this is inside the around parentheses over str class, you see.
And let's execute again.
No error this time.
Let's check one of the values and yep, that's what we've got.
So the table is next to date.
Therefore, in date we should have created an ln argument to break the line after the date to give space
for a new line for the table.
And you might also want to have a border for the rows for these cells here.
So I'm going to add here at the end, border is equal to one and do the same for all the.
Rose.
But be careful again with the syntax.
You're adding a border argument to the cell methods.
You're not adding a border argument to the star class.
So you see the difference here.
If you wanted to add an argument to STR, you should be working inside the parentheses of STR, which
is somewhere in here equal to something.
Right.
But that's not the case.
So be careful with that.
Border.
Border again, border is equal to one.
Border is equal to one execute.
And yeah, now it looks better.
We have the product I d the name of the product and the the amount purchased which is two price per
unit and the total price is not rendered because I need to change these to another value.
So 3030.
Otherwise the cells will be too big.
Run again.
Yeah, now it looks a bit better.
In the last value in the last sale we need to add a line is equal to one as well.
Run again.
And now we've got that problem solved where if you if you notice, the table was not displaying correctly.
So now we've got the table correctly because after the last sale we give space to the next row.
So every row is printed out in its own place.
So now next, what we need to do next is add a header to this table.
So we need to say here that this is a product.
This is the product name and so on.
We can get the header from the Excel files.
So you see product D, product name, amount, purchase, etc. So how do we extract the header out of
an Excel file?
We can do that using pandas.
So let me demonstrate that to you in here first.
So we have import pandas as p d, and so we read the data frame.
Like that.
And this would be, for example, invoices 101.
I can get this path actually from clicking on the Excel file, right click, go to copy path reference,
and then you want to copy path from content to routes.
So press on that and then go here and paste that will give you the path to that file.
So let's say we got that dataframe, so we get that data frame in each iteration of the loop.
And let's suppose we are in one current iteration of the for loop.
DF is equal to that in that iteration.
And then the F that columns gives us this object.
So columns is a property of a data frame and the type we get is this index object type.
So that is an index object type, but you can convert it into a list using the list class like that.
And you get that list right.
So let's do that in here.
So we are somewhere in here and actually we need to work outside of the four loop because, you know,
we only want to add the header once it's a one header.
That's why we don't want to be inside the full loop.
So we have multiple rows which are rendered here by the full loop.
But we have one header.
Therefore we should be outside of the four loop.
So columns will be df dot columns.
So that's just a variable.
That is the property.
You could say something else here.
So don't confuse that with that.
They just happen to be the same names in my case.
And yeah, we need to convert them into a list.
And then you need to get those lines and add them here.
So this time the text arguments should be changed to columns.
Zero.
Sorry, this should go.
And then we have here columns
one.
That would be columns.
To.
Columns.
Three.
And lastly, columns.
For.
Let's run the script.
And so prove the product name amount purchase price per unit, total price.
We could make an improvements here and remove these underscore.
So can you guess how we can remove this underscores?
So we are here, right.
Let's.
Add a comment, adds a header.
And here in the full loop, we add rows to the table.
So we add the header and then we add rows.
So can you guess how we could remove the underscore?
Well, I would say we could use a list comprehension, actually.
So if you go to the Python console again and you could say so, columns is equal to the list D of those
columns, Right.
Columns is a list.
And you could update that variable by saying columns is equal to item dot, replace the underscore with
a space.
So instead of the underscore, we will add the space between product and ID and between each other names
in here.
And then we will capitalize all the words of that string for item in columns.
Execute and columns.
Now is this.
So that's how you do it.
So that is the equivalent of doing for example product ID dot three place underscore, sorry underscore
space.
So that will give you the product ID, but with the space instead of the underscore and by adding the
title there, we get product ID That's the idea.
So let's get that.
List comprehension and add it in here.
And actually, you don't need to convert this into a list.
You can just iterate over the columns index objects.
You remember I explained you that D of that column is an index and you can iterate.
This is an iterable object, just like lists.
You can iterate over that index object so you don't need to convert it into a list because that would
be unnecessary.
Yeah, that's it.
Let's run.
Check the PDF file.
There we go.
Product deeper of name, amount, purchase, etc..
Lastly, we'll just.
Give a style to bolt to those headers.
Execute again.
And that's concludes the video for today.
Let's commit the changes we did so that was at a table.
Yeah.
Thanks for following.
See you later.