How to Format Data in Excel using Python. Openpyxl Tutorial #8

welcome back everyone in this video we will learn how to work with different data format in excel using the open pi excel so first let's import open pi excel and create workbook and worksheet object and it's done now let's quickly go back to our excel sheet here i have added three additional column date multiplier and skill level so if i inspect this date you can see its data type is a date similar way if i inspect the multiplier its data type is a number data type for skill level is a tax so if i set value for this date column using open pi excel let's see what happened we know that we can simply do that using ws and provide here cell location we will target here cell c4 let me add that c4 equal to and let me send some date here and again we are making a change in an excel sheet so we have to run save command let's run our python program and we did not get any error so i'm expecting the c4 value to be 11 11 20. let me close this one and reopen it you can see new value we inserted using open pi excel it's here let's quickly inspect this so it's directly set to a date so that's good let's try for another column i will target ws and target this multiplier and multiplier is in d column i will target d4 so i'll say d4 equal to make it 20 and this will set similar way target for e4 and we will call it uh we already call an expert so let's call it beginner rerun our python file if we go back and open our excel file and this two new value are added let's inspect this one set to a number so that's good let's inspect this skill level that is also set to a number but if you remember this skill level column is a text so let me inspect this first value that i added manually and you can see that is set to a text and this one is set to a number so we have to fix this issue to do that we can use open pi excel's number module we need to import that we can say from open pi excel dot styles import numbers and in here particularly for e4 cell we can say w dash e4 dot number format equal to numbers dot format underscore tags if i rerun my python command again and close this and reopen excel file and let's inspect this e4 cell again you can see it's changed to text format now that way you can change format of particular cell numbers provide a different format so in here date is already in date format but if you want to change that one as well we can simply say c4 dot number format equal to numbers dot format here there are multiple methods you can set it as general number uh there are all number types you can set percentage percentage with i believe this is for decimal point and all those different date types for us let's simply use date date time and save it and re-run my python file again no change but this date format if it is on different format it will get converted to a date format since we have supplied that format date underscored date time so that way you can change format of data type in excel sheet using open pi excel that's all for video thank you for watching i'll see you in next one

Channel: Python Bits

Views: 14,264

Keywords: python tutorial, python programming, learn python, 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 iterate through rows, format excel data

Id: w4JxCH7r75M

Length: 4min 42sec (282 seconds)

Published: Mon Jan 18 2021