Hands-on Python Openpyxl Project

hello everyone in this video we will automate the real life scenario so i have created a dummy data and we will try to automate the excel task as per our requirement so our requirement is to basically get the data from one file and append that to the master file and our second requirement is to create a report of the daily data using a master file so for a first task let me go to the folder so i can show you what i was talking about this is our master sheet it basically contain id name email gender total purchases and a lifetime reward balance and we have another file which is a daily seat so this sheet is getting generated daily and our job is to basically add this today's purchase and total rewards into this total purchase and lifetime reward in this daily sheet we only have ids so what we need to do is take this id find the relevant column in this master excel seat so first id was 18 so we have to find this 18 number column and here this belongs to total purchase so right now it's a three and the total per today's purchase is 8 so we have to do 3 plus 8 in here so it will be 11 similar way in lifetime reward balance the current value is 2369 and we have to add this 4 into that so it will become 2371 so that's our first task so let's go back to vs code and start writing code so very first thing we need to do is import open pi excel so i'll say import open pi excel and just to make sure open pi x is installed i'll just run this file so i'll say python and my file name is project dot pi and i did not get any error so everything is working very first thing we need to do is basically import both files and create a workbook object so for master file i'll say master data equal to open pi excel dot load workbook and in here we have to pass file name so my file name is here this master sheet let me move this in here so we can see better so i will pass here the name master data dot excel f x and similar way we have to create another workbook object for the daily data so i'll say daily data equal to open pi excel dot load workbook and the name of that file is daily sheet dot xlsx so i'll say daily underscore seed dot xlsx now this is a workbook object and we have to grab a particular sheet to interact with so to create a sheet first i will create a sheet for the master data i'll say master seed equal to master data and in here we have to pass sheet name so for our master data sheet name is data so i'll just say data and similar way we have to create a daily sheet and in here we have to pass daily data and the sheet name so our sheet name here is seat one right so let's just run our program to make sure the imports are working correctly so i'll just re-run my python program and i get an error that says no search file or directory exist okay so this means the the value i passed here for master data is wrong let's make sure the name is correct and actually the name of this actual thing is master suite not master data so let me correct that real quick save the program and rerun it this time i did not get the error so that means our workbook and worksheet object has been created now very first thing we need to do is get how many rows in our daily sheet there is inbuilt function of max row in open pi excel but i i'm not going to use that because sometimes it gives a false data uh what i mean by false data is basically sometime it count blank rows or a blank cell as a values so to avoid that i will create my own function that will give us number of rows basically what we will do is i will create a loop and iterate through each row and in open pi excel when there is an actual value exist it will return the value but if the cell is blank or row is blank it will give a none so it will give us value till this cell and as soon as this come to this row it will give us a none so what i will write is basically a loop where i'll try to test if there is a data and if data doesn't exist then i will break the loop so that way we can get the total number of rows so let's start writing code in our python file so since we don't know how many time we need to do iteration i will use a while loop here so i'll do something like this while true and then i'll say i'll create some random variable here in that variable i will basically start with 1 and add one after each iteration and if data doesn't exist then i will break the loop so let's use good variable names that we can understand so first i'll say is data equal to true so instead of using directly true here i will use the variable is data so i can uh set that as false when there is no value so that way we can break out of loop so i'll say here false sorry i would have to say here is data if it is exist right i'll create another variable called daily row count and i will set it as 1. now the reason i am starting here at 1 because opened by excel index start at 1. in most of the programming realms usually index started 0 but this open pi excel this particular module is using index from 1 so that's why i'm using index of 1 here to make sure it doesn't count from 0 now i will start iterating over each row so now i can basically get the data from each row i'll say daily sheet dot cell and in here row equal to daily row count the goal of this loop is to just get a number of row so i don't want to iterate over each column i will just iterate over first column and as soon as i found none i will break that loop so i'll say here column equal to 1 dot value and also i will do an increment a 1 after each iteration so that way this loop continue and add 1 in this loop if the value found and if there is a no value right if this data returns none i'll say none then i'll simply say is data equal to false so this will break the loop as soon as there is none value formed right so let's print our daily row count and see if it is giving us right result so to run a program i will just simply say python project pi and if i run it it giving us 12 so this 12 basically it's telling us there are 12 row count so let's go to our excel sheet and quickly verify that so this is our daily sheet and in here you can see we have value from row 1 to row 11 right and the reason we are getting a 12 because we have started our counting at one ah this is not a big of a deal we can subtract it or one later or we can just simply start from zero here either way it's fine i'm just gonna keep it like this and later when we start using uh this value i will subtract it so this is working now the similar way let's get the number of row for the master data sheet so i'll just quickly put here a comment so we can refer it back and i'll say rule get row count for master seat and i will use the same approach so let me just simply copy this code and paste it here and then we can make the modification and also i don't want to print this anymore because we know it's returning the correct count so i'll keep the ease data true again i'll use the same variable because we are not using it we are just using it to iterate our loop so but in this variable instead of using daily row count i'll say master row count and same thing i have to use in here i want to capture the master sheet row count in a separate variable so i can refer that later and also in here i have to replace the same thing and we are using data making it none if it is none then we are breaking the loop so now let's print instead of printing daily row count let's print master row count and make sure it's working as expected so let's re-run our program and it's still giving me 12. why is that oh because we are still referring to daily sheet here so here we have to refer to the master sheet so let me just quickly replace that here master sheet so now it's basically getting a value of for cell from each row and testing it if there is data if there is no data then make the is data variable false so we can break out of that so let's quickly save our program and rerun it and you can see here we are getting thousand two so let's go to our master sheet and make sure it's the same count this is daily sheet this is master data sheet and here quite a bit of data so to scroll back okay so there are value in thousand and one rows and our program is written in thousand and two and it is expected one uh this one additional because we are starting our row count at one we can substract that later so now we have a maximum number of row in a daily sheet and mastered sheet that contain a data next task basically is to get data from daily sheet and what we will do is basically extract the data and store it into uh let's use list of dictionaries so what we will do basically is let me go to daily excel sheet quickly so we will basically create dictionary for each row in this daily sheet and put it inside a list so that way we have list of dictionaries so let's work on that so first thing we need to do is create a empty list so i'll say today's data equal to blank so now we have to iterate over each column of daily sheet right we will use this daily row count right we have identified that electoral rule so we will use a for loop and iterate over that excel sheet for a 12 time actually 11 time because one time we have started from one but again i'm going to use a for loop here so i'll say for i in a range one tell daily row count and then grab the data now here if you remember our daily row count is 12 right but actual data column are 11 but if you remember in one of our our pre previous video in this series i mentioned that the range function start from the first value and go till this value minus 1 so here we don't have to basically do that minus 1 operation that i have mentioned previously that's a good thing now what i will do is for each iteration i will create a blank dictionary so let's call it row data and create a blank dictionary and in each dictionary we will use the same name to set the key and value attribute so first one is id so i will say id here equal to and we have to grab that from a daily sheet so i'll say daily sheet dot shell and row equal to i and column equal to uh we have to grab the data from the first column because that's the id so i will say one similar way i will repeat the same process for the today's purchase column and today's reward call so simply i'll just copy this and paste it couple time and name it today's reward actually this uh today's purchase come before today's reward so i'll use this today's reward in a third column and here today's and this one will be today's purchases right and also we have to change the column number so id is a first column uh purchase is a second column and reward is a third column so let's quickly change that and after this dictionary is created for the first iteration we will simply append that to our today's data list so let's see here row data right and let me add a print statement so we can see the output today's data right and let's run our program again and you can see we are getting uh actually this is very difficult to look but if you see here it's returning a cell location and i understand why because uh to get a value we have to add a dot value attribute so let me quickly add dot values to all three columns now if i rerun our program i'll just simply say python project or pi and this time it's giving if you see here this list and this 1002 is basically coming from this print statement we have to look at the second print statement which is this list and then here you can see uh it's returning the dictionary for each row so this is our first dictionary which is returning the first row this is a second dictionary which is returning the second row and we can quickly compare that to make sure we are getting correct data so if i go to our daily sheet and in here you can see the second row id is 18 today's purchase is eight and today's reward is four if i go back quickly here you can see in output we're getting id equal to 18 today's purchase is eight and today's reward is four so we are successfully getting all of the data needed now our next task is to basically append that data into master data sheet so before that let me just copy this one row in here so i don't forget how i structured my data so i'll just add it here something like this so basically when i try to refer back to this today's data list and we don't need this print statement so i'll just comment it out also i'll comment out this print statement as well and now our next step is to write daily sheet data into master excel sheet uh we have to do operation first find a row using the id call second go to the total purchase column purchase sal actually and add today's purchase there so this today's purchase will come from our today's data and this total purchase basically will come from this row so let's say if there is an we need to update fourth id we will basically take 23 and in 23 add our value from today's purchase with similar id and put the sum there and the next operation we have to do is similar one but for the reward so we will say go to total reward balance and i add that to this reward all right so let's start with that so this that will be our next step so first we need to do is basically iterate over master data set and find the rows that we need to update so what i will do is for i in a range and in this time i will start with row number 2 here the reason for that is the first row is for headers and we don't want to update the headers so if i start from 2 basically it will start grabbing data from the second row and give me data all the way till the end so here start from second row and go all the way till the maximum number of column that we have already extracted in this master row count variable so i will simply copy that variable and add that here a similar thing here if you remember this master row count a it was giving us one extra row count but we don't need to remove it the reason is the range value will start from the first value give us value all the way till this this value minus one so python is already taking care of removing that extra one for us and now first thing i will do is grab a ids for each row so in our master data sheet ids are stored in first column so i will say um id equal to master sheet dot shell and in here we have to iterate basically over to rows so i'll say row equal to i and the column equal to 1 dot value so this will give us id for each row now the next thing we need to do is basically take this id and check if that id exists in today's data if that id exists then we have to update that particular row after we get the id i will iterate over this today's data and check if that id exists in there so i'll simply say here for row in today's data and in here i will write the conditional statement so what i will say is basically if row id and the reason i am saying id here basically this today's data is the list of dictionaries right and every time i iterate it will give me the one dictionary so in that one dictionary i am getting the id right and i am checking if that id is equal to the id we get from the master call this way we can we can get the particular data for particular row right now if id is equal to id i will say here uh grab the value from this row for today's purchase and today's reward so i'll say here today's purchase equal to row and in row we name uh if i'm not mistaken we name it today's reward oh i already have kept it here for a reference so i'll just use that and similar way we will get the value for today's reward and similar way i will copy the key from here and add it here so now we have the today's purchase and today's reward data from our daily see right now we will get the total purchase and a lifetime reward from the master sheet so to do that we simply have to say let me add a comment get data from master seed i'll see here total purchase equal to master seed dot shall and in here we have to use the i throw back because this is iterating over every row right and when this id match we have to get that particular row so i'll say row equal to i but the column we have to basically grab the column number for that particular column of purchase total purchase so total purchase column is this is one two three four five six six number column six dot value and similar way we have to get the total reward and total reward is seventh number column so i'll say here total reward master sheet i won but the column number is seven and dot value so we are getting it correct now there are two ways uh one we can directly uh sum these two up so i can add total purchase plus today's purchase and set it to the same cell or to just make it easy i will do the sum operation and then i will set it so and i'll say add values of today's purchase into total purchase [Music] not purchase i'll say today's data into total data so here i'll say new total purchase equal to total purchase plus today's purchase and similar way new total reward equal to total reward plus today's reward so now we have new total for the purchase and reward and we have to basically set that in this cells where the id is matching i can simply basically take the same thing here and make it equal to new values so now our sixth column everything else remains same i'll keep the row equal to i column is equal to c column six is a total purchase so i'll simply just set it to new total purchase and similar way i will do that for total reward i'll say column seven and instead of using a new total purchase we have to use here new total reward so now this way it will set the new sum in those both column so just quickly repeat what we have done here first we are using a for loop to iterate over this master data set and we are grabbing the id now we are using that id to look into this today's data yeah this today's data is basically is a list of dictionary we are checking if this id exists in today's data and if that exists right we will grabbing the today's purchase and today's reward from it and at the same time we are also grabbing total purchase and total reward from the master data sheet adding the both values so adding total purchase into today's purchase and total reward and 2 days reward and setting it as a new variable and after that appending that new values into that same column of total purchase and this reward balance all right i i think this will do the trick but before we run our program since we are making a change in excel sheet we have to add a save command so i'll say not wb but i have to say ma what workbook object we use here we use the master sheet so i'll say master sheet not sheet we have to save actually workbook so i'll say master data dot save and i will use the same name because we don't want to create a new sheet every time every day you want to just keep updating the master data with the today's purchases in this this should do the trick but before i do that let's uh make sure to grab couple of values to make sure it doing what it's supposed to do so uh let me go to a daily sheet and it should update data with id of 18. so uh what we will get what will be the new data all data and new data that way we can compare it so for row 18 uh current row 18 the id of 18 here uh the today's total purchase is three so i'll just say three total purchase new one should be uh this three plus our today's purchase so three plus 8 it should be 11 similar way i will get the reward so current reward is for row 18 is 2 3 6 9 2 3 6 and the new balance should be 2 3 6 9 plus 4 so 2 3 7 3 and let's just repeat for one more column so it will it should also operate the row 90. in row 19 current data for number 19 is 14. so total purchase is 14 and it will add the today's purchase that is three so new value should be 17 similar way the reward balance is 9179 that is current and the new should be total plus two days so it should add it to will be so it should be 9 1 8 1. all right so this is for our quality check now let's run our program to see if it is doing what it's supposed to do and before that let me close this actual streets to make sure not overriding anything let's quickly go to our terminal and run python project pi and we get the error that is saying line 64. what is saying is uh basically one of the value is string and it's supposed to be integer because in python you cannot add a string into integer so for today's reward and let me just comment this out just to make sure you don't overwrite directly what i will do here is print type for both total reward so instead of doing this let's quickly comment out these two things and i'll say print type total reward print type today's report so that way we will know what is each type and then we can fix the error and also to comment this too you know it's returning uh since we are inside a loop it's returning uh too many class uh i think there's a simple way to fix it uh because in python you cannot concat a string with the integer what i will do is make sure this uh today's purchase always return integer value so i'll just wrap it around in that way it will always return the intelligent value and now i think that issues up should be fixed and i'll just uncomment uh this code and also uncomment the last line let's make sure our actual sheet is also closed yep looks like it's closed right and uh what it's it will do is basically in row 18 current value is 3 it will update it with 18 for purchase for balance current total value is 23 16 and its current will be updated with 23.73 and similar way for the row 19 current value is 14 in total purchase and it will be updated with 17 total reward balance is 9179 and it will be updated with 9189 all right so let's run our program hopefully we won't get any error this time so i'll say python project pi and we did not get any error so let's go back to our master data sheet and see if that new value are reflected and i'm in my master data set now so let's quickly go to the column where id equal to 18 and in here you can see this total purchase is become 11 and that is what we accept expected and total reward balance should be 2370 and that is what is reflected here similar way let's go to row with id equal to 90 this is the row with height equal to 19 and the total purchase should be is 17 let's go back and compare that is correct value the total purchase is 17 and total reward balance should be 9181 and we are getting the 9181 so we are getting correct values this is how basically you can take data from one actual sheet and update it to a master data scene and this is just a one scenario i i quickly made that up but you can use similar scenarios or you can create a new scenarios like this and update the multiple excel sheet at the same time now we have to create a new daily report and that report supposed to be contained first name last name email gender total purchase and lifetime reward balance column and this is for today's so what we need to do now is basically uh extract the data from this master data sheet for today's purchases so only extract the values that has been updated today and create a new excel sheet from there right and there is one requirement for header that header should be 12 point times new roman and bold so let's work on that uh very first thing we need to do uh first i will uncomment this thing and just to make sure that our data is don't get updated every time we run the code and i'll also remove this so first thing we will do is to create a blank workbook object okay so and in open by excel you can do that basically uh using open pi excel dot workbook so what this will do is uh create a blank object that we can use and write data in that and then we can save it as excel file and also uh let me assign a variable to this so i'll say daily report all right and also we have to grab worksheet object of the current workbook so i can simply just say let's call it ws and say daily report dot active so now this will give us a active sheet of this workbook object so that way uh when we create the excel file it will have one sheet and in that one sheet uh whatever operation we will do it will be written there so basically it will have one sheet in that excel file and we will update our data in that particular sheet right all right so first thing we need to do here is get this header values right from the master sheet so let's say get headers and i'll use the same approach that we have done previously so i'll just use the loop here so first let me use a boolean variable and we'll start from the column one right and in this actually i'll call it headers header values equal to a blank list so i will append the header names in this list so i can simply say while true and every in every iteration i want to increase the count so i'll increase column count here class equal to 1 and we have to get the data for this first row for each column until there is no data so i'll say data equal to master sheet dot shall and row is always 1 because all of the headers are in the first row so i'll say row one but here column equal to column count so what basically this will do is it will iterate to each row since we are increasing by one so i'll just say value and if data is not equal to none so basically what it do is it will start from the first column right it write over all columns and when it reached to this s column this data equal to be none right and at that time we don't want to append that in our header values but just want to break the loop so i'll say here if data is not equal to none here supposed to be header values dot append and add here data supposed to be indented also i'll use the else condition here so else basically when data is equal to none i'll just simply put is data equal to false so that way we can this loop will be break and also this supposed to be is data not directly to otherwise that loop will be infinite right so let's make sure we are getting everything correctly so i will print here header values to make sure we are getting everything as expected and run our program python project.hi alright so now you can see here we are getting all of the header values from our master sheet right also it is asking us to set the header style that's supposed to be uh 12 point font should be times newton 1 and bold so we have seen that ha in one of our previous video how we can set the font style so first thing we need to do is import font and i don't know why this was get embed so let me remove that i'll just say from open by excel dot styles import font and space here now we can define font style so i will call it a header style equal to font in here we can pass number of arguments so first thing i will pass name and this is a font name and we want it to be times new roman so i'll just quickly go in here and copy that name times new roman so copy that add that in here we can also add a size so we need a 12 point size and we also need to set it to bold so i'll just say bold equal to true and i will apply this header style to the first row all right so to apply a value what we can do is we remove the spread statement we have to iterate over each cell in this header value right first we have to add that value in a cell and then apply this header style so to do that we can basically uh enumerate over that excel sheet and before i do that let's just first add the text so what i can do is basically add iterate over this header values right and add that in each cell of the new actual sheet so what i will just say is i and column name in enumerate header values uh basically what this will do is uh it will give us i i is index okay and the column name is basically the actual value of this header uh so i can just quickly show you what it will do if i print i right it will give us the index of that particular value in a call so if i run a program again and you can see here it's giving us from 1 0 to 5 because there are like 6 values in here and i can similar way i can also print the column name and this will give us the actual value so let me save it and rerun the program and this time we are getting the actual values right so what i will do is use this i for a column number right to iterate over each column and at this in each column i'll add this call name as a value but this i is starting from 0 so we have to add one there so i'll just simply say call index equal to i plus one and now we have our worksheet object right worksheet object we have created here and we can say cell row equal to 1 column equal to call index dot value equal to uh we have to set it equal to this name call name and we don't need this print statement anymore right and also let's save that workbook to make sure it is creating a workbook and adding the headers so to do that we have to say this daily report right and at the run operation so i'll say dailyreport.savu and call it a daily underscore report uh send dot xlsx right so now if i run my program it's supposed to be creating here new workbook object get the worksheet from that new workbook object and at the same time we will get the data from the master data sheet to extract the headers right and add that header into this new worksheet object and see that entire workbook in a file called daily reports and excel effects so let's run our program real quick and we did not get any errors so let's go back to that folder and make sure that daily reports and file is created so i am in a folder now and here you can see we have the file daily report send and all of the headers are correctly appended however uh if you can see here font style is calibri and size is 11 also it's not bold now we will apply our uh header style to make sure it's matching with our requirement so i can say here uh same thing i'll get the same cell but instead of value we have to get a font property right and set it to header style and that header style is this one which has a time new roman font size 12 and bold so if i rerun my program again now go back to that folder and reopen the excel sheet you can see now the headers are set to times new roman size 12 and are also bold and that applies to all of the headers so it's working as expected our next job is to append the data so we have to grab the data from current master data sheet and append it to that new report so first thing i will do is get the ids of today's data from the daily sheet so uh we have that in our daily sheet we have this ids column right so i'll simply grab it and even i don't even have to interact with the daily sheet because we have stored our daily data in this today's data list right so i can simply just uh iterate over that and get the values let me comment that since we are not making the change so what i will do is basically say uh create a new list id is equal to blank list and append all of the ids here so i'll say for data in today's data and in here we have to get data id and we should append that into ids list right so if i print this it should give me all of the ids for today's data so let's read in our program yep and it is giving us all of the ideas now this first one uh is actually coming from the first row which is header and we can just simply remove that using a pop operation so i can simply say ids dot pop and in a pop operation you have to pass the index value so if i pass an index value uh when i say id is pop zero so it will remove the element at the zeroth index from this ids list so now let's rerun our program and you can see it's removed this id value from the list right so now we have the list what we need to do is now uh create another list for the ids in this right now we have to get today's data from our master sheet and append it to this daily report so to do that i will create another list uh final data we'll call it create a blank list and again iterate over uh the master data sheet and i'll just say range here i will say i here i have to put in range now start from a second row and here is our master see the reason i am starting from our second row because the first row is a header also two and we have to iterate till the end well till the maximum data exist and we have stored that maximum number of rows in this master row count right so i will use that same variable in here say master row count right and grab the id so id will be in a first column so i'll say id is equal to master sheet dot cell row equal to i and column equal to 1 dot value so now it will uh what this will do is it's read over the master sheet right and give us id for each row now our job is to make sure when this id matches with this any value in this id's sheet list we have to grab data for that particular row so what i will say is if id in ids create another blank list and say here for now once we find uh the matching id right so now let's say if i find a matching and there is a matching id of 18 right we know that so after we find this matching id i want to grab this data and to grab this data we have to iterate our column right so uh to do that there are like one two three four five six seven columns right but we don't want to get the first column id because in our requirement we just have to start from the first name right so we will basically when we find a matching id we will start from a second column iterate over all of the column and extract the data so i can say that i can i can do that using a for loop so i'll use a g variable in here since we already use i for j in a range and start from 2 to 8 and i will use this g variable to iterate over a column so i can say here uh alice master sheet right dot uh shell and row i'm using in this i throw so i will use that and for column i have to use j and use the value in here what basically this will do is it will give me whenever this id matches it will give me the data from that entire row okay and we just don't want to simply print this data so i will i have i have created this a blank list i want to append that the data in a list right so i'll just say list lst dot append and add the data right and this is a this will be a list for one row right we have to get the row data every time this id matches right so here i will we have this it will create a one blank append the data and we will up append this list in our final data so i will say final data dot append and here we have to append less right so again what we are doing here is we are iterating over each row in a master data set and when this id matches with the id in today's data right we have stored that id in this list right so when we find the id in this column matches with our today's id we will grab the data of that particular row and we are creating a list and storing a row values in that right and appending that row data list into our final data so this final data will be a nested list we can quickly print that and make sure it's giving us correct values so i'll just say print final data to run our program okay i'm getting an error and lsd is not different all right i get it the reason for that is lsd is only exist till here so i have to basically move indent my code in here and this should fix the issue so let's rerun rerun our program and this time you can see here this is a nested list you can see and each component of this list is another list and this first one is basically coming from this print statement so we don't need that let me quickly comment that out and rerun to make sure we don't confuse this all right so this is an ester list and each list in here is giving us value that is matching with that particular list all right so yeah this row supposed to be i not one because what is happening currently is when it finds the matching id it's just retaining the data from the first row and that is a header that's why we're seeing this repeating now i have fixed the issue so hopefully this should work now let me rerun our code and this time looks like it worked so this is the data from the first matching id and first matching id should be number id 18 so we can quickly confirm that so first id in our daily sheet is 18 so i can go to my master sheet and compare the data here so first one is 18 the first name is dill red hall del redhall the email is matching the gender column is matching this 19 is matching so this 19 is not matching and why is that so currently it's 11 and it's adding a 8. oh i get it so what it's doing is basically uh we are getting the value from the runtime right and the runtime in here we are every time we run our program it adds the value however we are not saving it but in our current run time that values are still getting updated so this is expected and i don't want to change it the reason is uh this this is first time we are creating a program right then every time we run this program moving forward we don't have to run the program multiple time we just have to run a program one time and it will update all of the values so one time you will run it will get the values from daily data append it to the master data set and then also create a new data set at the same time right so i just want to keep it as is i don't want to change anything in our program uh so in here uh it's giving us the values so i'll just comment this out the final data is working and to append the data in our actual sheet i just have to we can simply do that using a for loop i can say for data in final data ws the ws is the object we created here for the new data set and it's right here ws dot append and all here we have to enter data and then we have to save our excel sheet and this is a simple way to append a nested list data into worksheet object and if i let's quickly go back to our daily report excel sheet and there is no data currently so i close this now if i re-run our program i'll close all of the actual in fact so if i re-run our program i'm expecting there will be a data in our daily report sign so here is my terminal i'll clear this and rerun our python file no error so let's go back and check our daily report now you can see the daily reporters are updated with the data right now uh this program uh we don't have to change anything in here as soon as long as you are getting the data in the same format so i'll also uncomment this master data see so what this will do basically is achieve both requirement so let's go back to our requirement and make sure everything is achieved so first thing we are adding a daily transaction so adding today's purchases in total purchases adding today's reward in a lifetime reward column so we are doing the same thing right and then at the same time we are also creating a new report that need new report is containing this column so you can verify that new report is containing first name last name email gender total purchases in reward balance columns right the headers are 12 point 12 point times new omen and bold so that is matching so everything is working as expected and this is how you can automate the various access tasks using open pi excel i hope you like this tutorial and please don't forget to subscribe my channel and hit the like button and please put that in comment if you want to see video on another subject or if you want to see anything particular that you would like to automate thank you

Channel: Python Bits

Views: 3,487

Rating: 5 out of 5

Keywords: python, python tutorial, python programming, learn python, python for beginners, read excel in python, write excel in python, excel read and write in python, openpyxl tutorials, read excel files in python, python how to read microsoft excel files, openpyxl, microsoft excel python, excel python, python xlsx, python openpyxl, openpyxl tutorial, excel, openpyxl iterate through rows, openpyxl iter_rows, Openpyxl iter_cols, automate excel formula, excel formulas

Id: NDTRRaVjIFk

Length: 62min 29sec (3749 seconds)

Published: Sun Apr 18 2021