JIYIK CN >

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

DataFrame.to_excel()Export data to Excel using the function in Python

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

Pandas DataFrameis a data structure that stores tabular data. to_excel()The function has two input parameters: file name and worksheet name. We have to store the data in pandas DataFrameand then call to_excel()the function to export the data into an Excel file.

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

pip install pandas

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({col1: list1, col2: list2})
data.to_excel("sample_data.xlsx", sheet_name="sheet1", index=False)

sample_data.xlsxdocument:

In the above code, we use Python's to_excel()function to export the data in list1and list2as columns into sample_data.xlsxan Excel file.

We first store the data from both lists into pandas DataFrame. Afterwards, we call the output to_excel()function and pass it the name of our output file and worksheet.

Keep in mind that this method only works if the lengths of both lists are equal. If the lengths are not equal, we can Nonecompensate for the missing values ​​by padding the shorter list with values.

This is the simplest way to write data to an Excel-compatible file in Python.

xlwtExport data to Excel using the Python library

xlwtLibrary for writing data to legacy spreadsheets compatible with Excel versions from 95 to 2003 in Python. This is the standard way of writing data to Excel files in Python.

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

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

We need to install the library on our machine xlwtfor this method to work. Given below is the command to install the library.

pip install xlwt

A short working example of this approach 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")

sample_data2.xlsdocument:

In Python, we use xlwtthe library to write data to sample_data2.xlsa file.

We first created an Workbookobject of the class. Using this object, we created a worksheet using the method Workbookof the class .add_sheet()

We then use write()the function to write the data to the newly created worksheet. Finally, when all the data has been correctly written to its specified index, we save the workbook to an Excel file using the function Workbookof the class .save()

This is a very simple approach, but the only drawback is that we have to remember the row and column index of each cell in the file. We can't just use the A1and A2indexes. Another drawback of this approach is that we can only write .xlsfiles with a .

openpyxlExport data to Excel using the Python library

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

This approach solves all the shortcomings of the previous methods. We don't need to remember the exact row and column index of each data point. Just write()specify our cells in a function like A1or A2.

Another cool advantage of this method is that it can be used to write .xlsxfiles with new file extensions, which was not the case with the previous method. This method works just like the previous one.

The only difference here is that we have to initialize each cell and a worksheet using the method openpyxlfrom the library .cell(row,col)

openpyxlIt is also an external library. We need to install this library for this method to work properly. The command to install the library on our machine openpyxlis as follows.

pip install openpyxl

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")

sample_data3.xlsxdocument:

In the above code, we use openpyxlthe library in Python to write data to sample_data3.xlsxan Excel file.

We first created an Workbookobject of the class. We used this object to create a Workbook.activeworksheet with . We also my_sheet.cell(row = 1, column = 1)created a cell object using .

Instead of writing out the exact row and column numbers, we can also specify the cell name, such as A1. We can then c1.value = "Maisam"assign our newly created cell value with .

Finally, when all data has been correctly written to its specified index, we save the workbook to an Excel file using the function Workbookof the class .save()

XlsWriterExport data to Excel using the Python library

Another easy-to-use way to write data to an Excel-compatible file is the library in Python XlsWriter.

This library gives us more control over the output file than any of the previous methods mentioned above. The library also supports the latest Excel compatible file extensions such as xlsx.

To write data to an Excel file, we first have to create an object of the class by providing the file name as an input parameter to the Workbookconstructor. Then we have to create a worksheet Workbookusing the function in the class .add_worksheet()

After adding the worksheet, we can use sheet.write(cell, data)the function to write the data. This sheet.write()function takes two parameters: the name of the cell and the data to be written.

After writing all the data to the worksheet, we need to close our workbook using the method Workbookin the class .close()

XlsWriteris an external library and does not come pre-installed with Python. We first have to install the library on our machine XlsWriterfor this method to work. Given below is XlsWriterthe command to install the library.

pip install XlsxWriter

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()

sample_data4.xlsxdocument:

In the above code, we use Python's xlswriterlibrary to write data to sample_data4.xlsxan Excel file.