How To Create And Add Data To Excel Files In Python | Xlsxwriter Tutorial

programmers often deal with a lot of data and if you are a python programmer or wish to become one you will eventually have to deal with storing the program's output data in an efficient way be it web scraping aiml web dev or automation script although saving data in files like txt json xml or html is nice it may not provide a way to analyze the data easily and that's where excel files can be a big game changer since excel files can be analyzed and the data within them can be easily sorted or trimmed based on needs with the help of softwares like microsoft excel these files provide a user-friendly way to store data what's up pythoners this is visit vivedi and in this video we are gonna learn how you can create excel files in python and how you can store data in them this video will specifically focus on creating and storing data in excel file if you guys want a video on reading data from an excel file then do mention it in the comments below and i will make a video on it so without any further ado let's begin so we are gonna start off with an empty python script right here you can name the script whatever you want i just named it excel dot pi now to start off we will first import a module named xlsx writer this is a module you can find in python module packages which is used to create excel files in python you may find other modules too that could do the same work but i personally find this one to be the most commonly used and easy to understand first you need to open the command prompt and type pip install xlsx writer the command will install the xlsx writer module onto your local system once that is done you can go back to your text editor by the way i'm using visual studio code you can try any ide or text editor you want and then type import xlsx writer this line will import the xlsx writer module onto your r script so that we can use all the classes and functions present within this module the first step to creating the excel file is to create the excel file so we will write workbook equals to xlsxwriter.workbook and then within round brackets we are going to write all about python excel [Music] this line will create an instance of workbook class which represents the new excel file that we are creating we have to pass the name all about python excel as the excel's name you can name it whatever you like next we will add a seat to our newly created excel file an excel can consist of one or more than once excel sheet which contains the data you can see the seats at the bottom left of your screen when you open an excel in microsoft excel we are gonna write work seat equals to workbook dot add underscore worksheet if you guys want to name the worksheet you can pass the name of the worksheet as a string onto the add underscore worksheet function something like first seat now in order to enter data in excel we need data so i'm just going to quickly get some data and we can continue with our code one hour later so we are ready with the data right here we have a variable by the name data which is a list containing multiple dictionaries each containing data of an individual we have a name phone email address and country information with us now one thing that you should understand is that the data in excel can be added using a for loop so it will be better that the data you're gonna save in the excel is present in the form of a collection in python script on which you can use a for loop just like we have a list here so we are going to start by adding headers for the data we are going to write in excel so we will write worksheet dot write within round brackets we are going to write 0 comma 0 comma and then within single quotes we are going to write hashtag in the next line we will write worksheet dot right then again within round brackets we are going to write 0 comma 1 comma and then within single quotes name with n capital in the next line we are going to write worksheet dot write again round brackets and within that we will write 0 comma 2 comma and then within single quotes phone with b capital in the next line we are gonna write worksheet dot write again within round brackets we are going to write 0 comma 3 comma and then within single quotes email with e capital next we will write worksheet dot right and then within round brackets we are going to write 0 comma 4 comma and then within single quotes address and then in the next line we are going to write work seed dot right and then within round brackets 0 comma 5 comma and then within single quotes country with c capital write function is used to insert data at a specific row and column in excel the first argument is the row number the second argument is the column number and the third argument is the text you want to enter since we are writing data at the top of the excel the row number is zero and the column number changes as we are writing data in the same row now we will quickly create a for loop that will go through each dictionary object present inside the data list we will use the enumerate function to also get the index of the iteration which will be used to add data onto each row we will write for index comma entry in enumerate then within round brackets data and then colon we are gonna write worksheet dot right then within round brackets index plus one comma zero comma str and then within round brackets index and then we close all the round brackets and in the next line we write worksheet dot right then within round brackets index plus 1 comma 1 comma entry and then within square brackets and single quotes we write name in the next line we write worksheet dot write then within round brackets we write index plus 1 comma 2 comma entry and then within square brackets we write within single and also within single quotes phone and then in the next line we write worksheet dot write within round brackets index plus one comma three comma entry and then within square brackets and single quotes we write email and then in the next line we write worksheet dot write index plus 1 comma 4 comma entry and then within square brackets and single quotes address and then for the last line we write worksheet dot write index plus 1 comma 5 comma entry within square brackets in single quotes country the for loop iterates to the entries in the data and we can use the worksheet dot write function to keep adding data in excel we are using index 1 as we have already added data at the excel at index 0. now to close of the excel we will write workbook dot close followed by round brackets now let's save the script and run it to see how it works as you can see the script has created an excel file by the name all about python excel the exact same name which we gave as an input to the script if we open the script we can see the name of the seat as first seat again the same name which we gave as an input and we can see all our data inside the excel if you want to make this code reusable such that you can use it for your other python projects you can create a function something like this you can pass values to the function and the function will automatically perform the above explained operations and create the excel for you as you can see here the function requires parameters the name of the excel the name of the seat the list of keys containing the data list and finally the data list the excel generated will look the same as it was created above all of the code that i have used in this video is uploaded on github the link is in the description of this video that's all there is in this video hope you liked this video if you did don't forget to subscribe to the channel for more such interesting videos with this it's time for me to go this is visay dvd signing off [Music]

Channel: All About Python

Views: 70,352

Keywords: How To Create Excel Files In Python, how to create and add data to excel files in python, xlsxwriter tutorial, create excel file, writing excel file, write excel file, write excel in python, write excel in python xlsxwriter, write excel python, excel read and write in python, excel formatting in python, python excel automation, python excel tutorial, python excel manipulation, python excel data processing, xlsxwriter, using python to automate excel, allaboutpython

Id: 9ALJlSV3Dzw

Length: 9min 46sec (586 seconds)

Published: Sun Jan 09 2022