Python output to excel

How to Convert Dictionary to Excel in Python

In this article, you will see everything about how to convert Dictionary to Excel in Python with the help of examples. Most of the time you will need to convert Python dictionaries or a list of Python dictionaries into excel. Then this article is going to be very helpful for you.

Prerequisites

Before going further into this article you will need to install the following packages using the pip command, If you have already installed then you can ignore them.

 pip install pandas pip install openpyxl

How to convert Dictionary to excel in Python

Here we will see a total of two ways to convert Python dictionaries into excel. The first is converting a single dictionary to excel and the second will be converting a list of dictionaries into excel.

Convert Dictionary to Excel in Python

Here I’m gonna convert only a single Python Dictionary to an excel file using the Python Pandas library.

In this example, we will see how to convert the Python dictionary to excel using the Python pandas module.

Example: Convert a dictionary to excel

 import pandas as pd student = # convert into dataframe df = pd.DataFrame(data=student, index=[1]) #convert into excel df.to_excel("students.xlsx", index=False) print("Dictionary converted into excel. ")

After the above code is executed successfully, students.xlsx will create.

Convert List of Dictionary to Excel in Python

Sometimes you may want to convert the list of Python dictionaries to an Excel file. then again, you can follow the below approach.

Most of the time you have a large list of dictionaries and you want to convert them into excel, then it is also possible, Let’s see how can you do that.

Example: Convert a list of dictionaries to excel

 import pandas as pd student = [, , , ] # convert into dataframe df = pd.DataFrame(data=student) #convert into excel df.to_excel("students.xlsx", index=False) print("Dictionary converted into excel. ")

Conclusion

So we have seen all about how to convert a dictionary to excel in Python with the help of examples. This is one of the most important especially when you are generating reports from large dictionaries into an excel file because dictionaries are not understandable by non-techie guys so you have an option to convert it into an excel file.

I hope this article will help you. if you like this article, please share and keep visiting for further interesting articles.

Related Articles:-

• How to convert a dictionary to CSV
• How to convert Excel to Dictionary in Python
• How to convert string to DateTime in Python
• How to sort the list of Dictionaries in Python
• How to convert Tuple to List In Python

Thanks for your valuable time… 🙏🙏

Источник

Python Export to Excel

1. Export Data to Excel With the DataFrame.to_excel() Function in Python
2. Export Data to Excel With the xlwt Library in Python
3. Export Data to Excel With the openpyxl Library in Python
4. Export Data to Excel With the XlsWriter Library in Python

This tutorial will demonstrate different methods to write tabular data to an excel file in Python.

Export Data to Excel With the DataFrame.to_excel() Function in Python

If we want to write tabular data to an Excel sheet in Python, we can use the to_excel() function in Pandas DataFrame .

A pandas DataFrame is a data structure that stores tabular data. The to_excel() function takes two input parameters: the file’s name and the sheet’s name. We must store our data inside a pandas DataFrame and then call the to_excel() function to export that data into an Excel file.

We need to have the pandas library already installed on our system for this method to work. The command to install the pandas library is given below.

A working demonstration of this approach is given below.

import pandas as pd list1 = [10,20,30,40] list2 = [40,30,20,10] col1 = "X" col2 = "Y" data = pd.DataFrame() data.to_excel('sample_data.xlsx', sheet_name='sheet1', index=False) 

In the above code, we exported the data inside list1 and list2 as columns into the sample_data.xlsx Excel file with Python’s to_excel() function.

We first stored the data inside both lists into a pandas DataFrame . After that, we called the to_excel() function and passed the names of our output file and the sheet.

Keep in mind that this method will only work as long as the length of both lists is equal. If the lengths aren’t equal, we can compensate for the missing values by filling the shorter list with the None value.

This is the easiest method to write data to an Excel-compatible file in Python.

Export Data to Excel With the xlwt Library in Python

The xlwt library is used to write data into old spreadsheets compatible with Excel versions from 95 to 2003 in Python. It is the standard way for writing data to Excel files in Python.

It is also fairly simple and gives us more control over the Excel file than the previous method. We can create an object of the xlwt.Workbook class and call the .add_sheet() function to create a new sheet in our workbook.

We can then use the write() method to write our data. This write() function takes the row index (starting from 0), the column index (also starting from 0), and the data to be written as input parameters.

We need to install the xlwt library on our machine for this method to work. The command to install the library is given below.

A brief working example of this method is given below.

import xlwt from xlwt import Workbook  wb = Workbook()  sheet1 = wb.add_sheet('Sheet 1') #sheet1.write(row,col, data, style) sheet1.write(1, 0, '1st Data') sheet1.write(2, 0, '2nd Data') sheet1.write(3, 0, '3rd Data') sheet1.write(4, 0, '4th Data')  wb.save('sample_data2.xls') 

In Python, we wrote data to the sample_data2.xls file with the xlwt library.

We first created an object of the Workbook class. Using this object, we created a sheet with the add_sheet() method of the Workbook class.

We then wrote our data into the newly created sheet with the write() function. Lastly, when all the data has been properly written to its specified index, we saved the workbook into an Excel file with the save() function of the Workbook class.

This is a pretty straightforward approach, but the only drawback is that we have to remember the row and column index for each cell in our file. We can’t just use A1 and A2 indices. Another disadvantage of this approach is that we can only write files with the .xls extension.

Export Data to Excel With the openpyxl Library in Python

Another method that can be used to write data to an Excel-compatible file is the openpyxl library in Python.

This approach addresses all the drawbacks of the previous methods. We don’t need to remember the exact row and column indices for each data point. Simply specify our cells like A1 or A2 in the write() function.

Another cool advantage of this approach is that it can be used to write files with the new .xlsx file extensions, which wasn’t the case in the previous approach. This method works just like the previous one.

The only difference here is that we have to initialize each cell in addition to a sheet with the cell(row,col) method in the openpyxl library.

The openpyxl is also an external library. We need to install this library for this method to work properly. The command to install the openpyxl library on our machine is below.

A simple working demonstration of this approach is given below.

import openpyxl my_wb = openpyxl.Workbook() my_sheet = my_wb.active c1 = my_sheet.cell(row = 1, column = 1) c1.value = "Maisam" c2 = my_sheet.cell(row= 1 , column = 2) c2.value = "Abbas" c3 = my_sheet['A2'] c3.value = "Excel" # for B2: column = 2 & row = 2. c4 = my_sheet['B2'] c4.value = "file" my_wb.save("sample_data3.xlsx") 

In the above code, we wrote data to the sample_data3.xlsx Excel file with the openpyxl library in Python.

We first created an object of the Workbook class. We created a sheet with the Workbook.active using this object. We also created a cell object with my_sheet.cell(row = 1, column = 1) .

Instead of writing the exact row and column number, we can also specify the cell name like A1 . We can then assign our newly created cell value with c1.value = «Maisam» .

Lastly, when all the data has been properly written to its specified index, we saved the workbook into an Excel file with the save() function of the Workbook class.

Export Data to Excel With the XlsWriter Library in Python

Another great and simple way to write data to an Excel-compatible file is the XlsWriter library in Python.

This library gives us much more control over our output file than any previous methods mentioned above. This library also supports the latest Excel compatible file extensions like xlsx .

To write data to an Excel file, we first have to create an object of the Workbook class by providing the constructor’s file name as an input parameter. We then have to create a sheet with the add_worksheet() function in the Workbook class.

After adding a sheet, we can write data with the sheet.write(cell, data) function. This sheet.write() function takes two parameters: the cell’s name and the data to be written.

After writing all the data to the sheet, we need to close our workbook with the close() method inside the Workbook class.

The XlsWriter is an external library and does not come pre-installed with Python. We first have to install the XlsWriter library on our machine for this method to work. The command to install the XlsWriter library is given below.

A working demonstration of this approach is shown below.

import xlsxwriter  workbook = xlsxwriter.Workbook('sample_data4.xlsx') sheet = workbook.add_worksheet()  sheet.write('A1', 'Maisam') sheet.write('A2', 'Abbas')  workbook.close() 

We wrote data to the sample_data4.xlsx Excel file with Python’s xlswriter library in the above code.

Maisam is a highly skilled and motivated Data Scientist. He has over 4 years of experience with Python programming language. He loves solving complex problems and sharing his results on the internet.

Related Article — Python Excel

Источник