How to Write Colorful Excel in Python | Excel with xlsxwriter in Python - Tutorial

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

Channel: Ritman

Views: 10,944

Keywords: python write excel, python csv tutorial, python write csv, python csv module, write excel in python, python xlsxwriter, python, python tutorial, programming, programming tutorial for beginners, python tutorial beginners, beginners tutorial, python coding, python language, python language basics, python basics, python language basics for beginners, python basic programs, python programs for beginners, learn python, python online, python 3, python tutor

Id: Jw67JDBjBvY

Length: 17min 11sec (1031 seconds)

Published: Tue Oct 13 2020