How to Iterate and Read Rows and Column. Openpyxl Tutorial #3

hello everyone in this video we will learn about couple of important function that will allow us to extract value from the actual sheet in my python file i already imported open pi excel and created a workbook and a worksheet object if you don't know about workbook and worksheet i will recommend you to check out my first video in this series where i explained how workbook and worksheet work first one we will look is it a row function to use an interrow we have to call that on worksheet object we can say ws dot rows so in here we have to specify couple of arguments first one is minimum row maximum row then minimum column and maximum column so the way it will work is let's go to our actual see here this is first column second column third column and so on same way this is the first row second row third row four row and so on my iter function i have to specify minimum row where i want to start so if i specify minimum row of three it will start from third row same way if i specify a minimum column of 2 it will start from a second column same way the maximum row and maximum column if i specify maximum column value in there it will go up to that column so if i have specify maximum column of 4 it will go up to a d column same way if i have specify a maximum row of 7 it will give me value all the way from row 1 to row 7. let's add our value since i have a couple of column and few rows i will start from row one column one so i'll specify minimum row equal to one and maximum row we can go all the way up to eight but let's specify seven ignore one row same way minimum column i'll specify one and we have only two columns so i'll say max column is equal to two i have specified minimum row maximum row minimum column and maximum column i can see that in a variable let's call it row and this will give us and generator object now let me quickly print this so if i run my python file now python function it will give us a generator object we can use this generator object and perform operation on that to extract the values so i can use a loop here for row in a rows print row this will give us cell locations here you can see it's giving us one tuple for each row so here we have one tuple for cell a1 and b1 another tuple or shell a2 b2 and so on we can also extract the value out of this rows directly since i have only two rows i'll use trouble and packing so i can say a and b which represent column a and b i can say here a dot value and b dot value if i rerun my python file instead of saying cell locations we can see the actual values of those cells i can also store this value in python object and then use that for further operation to do that let's uh quickly create a two blank list and i can append those values in those list so i'll say names dot append in first row we have names so i'll say a dot value second list i'll say b dot value now if i print those two list after processing rerun python file this time instead of just getting value we have stored that in a list we can use this list for further operations that's all for it a row let's look into intercolumn function now inter column works same way as with a row in here we have to specify minimum row maximum row minimum column and maximum column you can see see that in here those are minimum thing we need to specify i can say minimum row start from 1 minimum maximum row go up to 5 minimum column he can start from one and maximum column you can specify two here this is again uh give us an iterator object so let's save that in column variable let me correct this typo and i can print those calls this will again give us an iterator object so if i read in my python file again it's an iterator object i can again use for loop here so for call in columns print call and this will give us all the cell location for each column here you can see it's giving us tuple for each column so in either row function it was giving us tuple for each row here we have couple for each column first tuple we have value start from a1 and goes all together to maximum column number that we specified if i am not mistaken i have specified maximum row of five it will start from first row and give us all the value till row five it will give us this part for both columns same thing it will give us same values for column b i can use here tuple unpacking but you know in each tuple we have 5 values so that is not recommended you can use other functions to extract this value and store it in either python list or tuple or dictionary one important thing i want to mention here this minimum row and minimum column are not mandatory if if you are always starting from first row so if i remove those minimum row here in minimum column and rerun our python file it's giving a same output in here you can say even though we haven't specified to start from first column it only started from first and gave us value until the maximum row so here it's giving us uh a1 to a5 same in second tuple it's giving us value from cell b1 to b5 so same thing in eta row as well you don't have to specify minimum row and minimum column if you are starting from first one a similar thing you can do for maximum if you want to start from certain row or column but extract value all the way till the end then you don't have to specify maximum row if you just specify minimum column it will give you value i can quickly show that to you so let's remove maximum column as well and rerun our python file and you can see it's giving us both columns even though i haven't specified so it will give all the column and all the value until the end so same thing if i don't specify maximum row it will give us all values so let's remove that as well and directly run that so now i have two tuples starting from one to eighth row in the first couple in the second tuple i have all the values for b column so if i go here in first couple it's giving us from row one to row eight and in uh second table it's giving us this value from row one to row eight this we call because only those values exist but uh it a row and enter column i'll say use with maximum and minimum values to extract all the values we have another two important function so let me show you that quickly uh to extract all the rows you can simply call dot rows this will give you all the rules in the actual seat i can save that in a rows column and again this will give you an iterator object you can directly cast this value in a list or tuple so let me use a list here and if i print my rows and rerun my excel file you can see here so we have tuple for all the rows so here first couple starting from e1 b1 all the way till 8th row that ending from a8 to b8 that has value so again it's giving us tuple for each row all the way till the 8th same way we can use columns function and this will return all the column from the actual sheet so if i print this again and run our python function it will give us tuple for each column so in here first tuple start from here all the way till a8 so this is values of first column and here we have second tuple which has value of second row so it's giving value of uh this row which has value up to eight same thing for column b i have value from d1 to b8 and that's what we have b1 to b8 uh that's all for this video thank you for watching and i will see you in next one

Channel: Python Bits

Views: 36,824

Keywords: python tutorial, python programming, learn python, tkinter openpyxl, python for beginners, read excel in python, write excel in python, write excel in python openpyxl, 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, openpyxl iter_rows, Openpyxl iter_cols

Id: Ua_uCedQD_c

Length: 9min 24sec (564 seconds)

Published: Fri Jan 08 2021