so in this video we'll look at how to create microsoft excel in python
having this different color code and different border so yeah let's get started so this is our
simple xls file which has a two table one is about this product information second is the
the sales information of this product so here i have my spider editor so for the for writing
this excel what we're gonna use is we're gonna use this uh xls writer so i say import xls right
now in the next step what i have to do i have to define my workbook so i say book equal to
xls writer then dot then i have to say workbook and in the workbook i give my file name so i say
search.xlsx so this is my file name now what i have to do i have to close this as well
now in the next step what we have to do we have to define our first table so first table
i have a four sorry three rows so first one is is product name
and then second is product code
and the third one is skew code
so now let's copy this product name this is v-neck t-shirt this is our product name now
we have to take this product code as well so this is my product code and third one is qcor so this
is my sku code so using three variable will create uh first table so now what i have to do i have to
add the worksheet to our workbook so for that what i say s equal to book dot
add and i say add worksheet and i give the box it name which is what nothing but the sales
so now what we have to do we have to add data into this worksheet so for that what i say
s dot write and then in bracket how to define the column and row and the actual values which i want
to write so in excel what what happened like the the rows and columns start from the zero
so in our example we're gonna leave the first row as a blank so our column start from second
so index will be a 1 and the first column so i say 0 and then what i have to say is
product name and this is my value and the same way what we'll do is we create the third row
and the fourth row so for that the index will be 2 and this will be a 3 then i will say here
this is my product code and this will be my
skew code now what we have to do we have to add the uh this this column so for that we have to
add the value of this variable so now same way what will copy this and in my second row
i will add one column and we'll see this product name and simmer let me copy this
and copy here as well and here i change this row number and i say product
code and here i say 3 and i say
skew code so this will create my first table so let's save this and run it
so let's go to this one so this is our excel which is created so if i open this one so i have a three
rows and two columns so product name and then product code and skew code so this is my first
table and now close this so for the second table we have this sales information so for that what
we have is we have one csv file and this csv file will use read the csv file and try to create this
table into our microsoft excel so for reading this excel what we have to do we have to say import
csv and after that what we'll say will read that csv so for that i say with open
and i will give the name so name is let's check the name what is the name sales dot input
input dot csv and i say as csv file
now what we have to do we have to define the reader so i say csv
reader equal to csv dot reader and in break it is csv file
now what we have to do we have to read this uh the csv file by line by line so i
for that i define the for loop and say row equal to in and then say what is my csv
reader and after that what i see is so what i have i have my all these rows inside
sorry is all the rows inside my row variable so now what i have to do i have to add all this row
list into my worksheet so i say s dot write and the same way what i have to do
at this index for indexing if i look at this one so i have one two three
four five six and a table start from seven so for that i will define one index i will say xls
row index and it starts from seven so i will say six and here i will say
this this excess row index and say 0 that is my first column and inside that what i want is
row the first column of my csv so i say 0 so this this will add the first column into
the second table same way let me add all this other row of columns so i will add
one then say two and 3 then 4 same way i will change the index for csv as well so i say 1 2
3 and 4. now what i have to do i have to increment this index as well so
what i say is xls rho index plus equal to 1
so probably is look like outside so i have to add inside this so this will create my second table
so let's see let's execute this and oh let's open this excel so yeah so we have the second
table as well now our excel is ready now what we have to do we have to change the format
or we have to add the color background color and border so for that what we let's go to our
excel code and here for that what we'll define is we have to define some format so i say format one
so this is my first format and i add into my workbook and say add then say a format so in
format i mean it's the kind of dict you can define so for that i give this curly bracket and i have
to give the attributes so for attributes the first one is background color so i say bg color and
for that i have to give the value so value i think you can give some code as well
so for that i have some code so let me see where is my code so yeah this one is so these are the
chords actually we will use so this is my color chord so this is this code is for the green color
and again what i have to do i have to define the border so for that i say uh the single chord i say
border and the attributes i will say one same way well copy this and we'll define the second format
so second format will be the same but we just have to change the color so what we have to do we
have to add the orange color so this is the cold for orange color so let's add this orange color
so now we have to apply this the format to each and every element of this excel so if i go to
this excel so here it has a green color so the element i mean the the column index equal to zero
so in our code so this is what we have so here after this element i say format one
and not that probably here so as a format one same way here as a format one
so here i format one same way uh in the second for orange i add this
format two format two and format 2. now here again i have to say
format 2
now save this and see first let me close this and let's run this what it says format where is the
probably this one so if i go to cells so now the problem is that
in the in this this header of the second table it is coming orange but i want the greens
for that what will put some conditions i say if excellence rho and x equal to 6 then
says format 2 equal to format 1 else
i say format 2 equal to 4 and 2. so this will change i mean see
i mean if it's a header it will change the format so let's save this and run this what is happening
permission d9 so probably look like something is open yeah this one so again let's run this
and let's open this so yeah all become green so something wrong with this one
so probably don't do like this where probably as a format and here i will say format
say format format format format and format so yeah i will
maybe this one this one so if it is the first one say one then two let's close this
and run this
so now yeah now it is ready see so if you look at you know the excel the each cell does not
have expected length so if you want to alter the length of the cell so for what we can use
is probably we can use something called set column so let's try to implement that so i says start set
column and then i say
zero comma one comma uh probably i will set 18 so this is my for my first column
same way we'll define for all the columns so i say two three four
hold on let's start from zero so it's a zero then one say two then three then four
now let's close this
now let's execute this
and if i go to my source excel see now we have you know the correct cell length
which is set equal to 18 so now let's close all this uh both the set and open one by one
so here's my desktop inside the python then this is my sample okay and
if i open this the one which we created yeah is the same one and the cells and having all this
the format yeah i think that's all for this video hope you got a good understanding how we can
format the each and every cell in the excel and even how we can apply this color code or border
yeah you can play around with other options as well and yeah thank you for watching this video