[Python] How to Write List to Excel [openpyxl, pandas]

This article shows how to write list to Excel.

Use "openpyxl", a library for Excel manipulation in Python.

As a supplement, I also show how to write a list to Excel using "pandas".

How to use "openpyxl"

"openpyxl" is the most common library for Excel manipulation in Python.

In the beginning, I show basic usage of "openpyxl".

Installing "openpyxl" in Python

"openpyxl" is external library.

External libraries in Python can also be downloaded and installed using pip which is a package manager.

To install "openpyxl", you run the following in your terminal or command line.

> pip install openpyxl

Basic usage of "openpyxl"

I show basic usage of "openpyxl".

This is an example code to create a new Excel Workbook.

import openpyxl

path_excel = r'C:\***** any path *****\test.xlsx'

wb = openpyxl.Workbook()
sheet = wb.worksheets[0]

sheet.cell(2, 1, 'Apple')

wb.save(path_excel)
wb.close()

> path_excel = r'C:***** any path *****\test.xlsx'

Specify the path of the new Excel file.

By prefixing a string literal with "r", you can create a raw string in Python.

"Raw string" treats the backslash character "\n" as a literal character, not newline.

> wb = openpyxl.Workbook()

A new Excel file is created.

> sheet = wb.worksheets[0]

The 0th Excel sheet is specified.

> sheet.cell(2, 1, 'Apple')

The string "Apple" is written to the second row and first column of the sheet specified earlier.

> wb.save(path_excel)

The Excel file is saved.

> wb.close()

The Excel file is closed.

Write list into existing Excel file

This is an example code to write list into existing Excel file.

import openpyxl

path_excel = r'C:\***** any path *****\test.xlsx'

wb = openpyxl.load_workbook(path_excel)
sheet = wb.worksheets[0]

sheet.cell(3, 1, 'Banana')

wb.save(path_excel)
wb.close()

> wb = openpyxl.load_workbook(path_excel)

Existing Excel file is opened.

sponsored link

How to write list to Excel using "openpyxl"

So, to get to the point.

I show how to write list to Excel using "openpyxl".

I show the following two ways to write list to Excel.

• Using sheet.append( )
• Using user-defined function

Using sheet.append( )

Using "sheet.append( )", the list is written to Excel line by line.

This is an example code.

import openpyxl

list_2D = [
[11, 12, 13, 14],
[21, 22, 23, 24],
[31, 32, 33, 34],
]

path_excel = r'C:\***** any path *****\test.xlsx'

wb = openpyxl.Workbook()
sheet = wb.worksheets[0]

for row in list_2D :
    sheet.append(row)

wb.save(path_excel)
wb.close()

> for row in list_2D :
> sheet.append(row)

Rows are taken one by one from list_2D and written to Excel.

Using user-defined function

Using "user-defined function", the list is written to Excel.

This is an example code.

import openpyxl

def write_list_2d(sheet, l_2d, start_row, start_col):
    for y, row_data in enumerate(l_2d):
        for x, cell_data in enumerate(row_data):
            sheet.cell(row=start_row + y, column=start_col + x, value=l_2d[y][x])

list_2D = [
[11, 12, 13, 14],
[21, 22, 23, 24],
[31, 32, 33, 34],
]

path_excel = r'C:\***** any path *****\test.xlsx'

wb = openpyxl.Workbook()
sheet = wb.worksheets[0]

write_list_2d(sheet, list_2D, 2, 1)

wb.save(path_excel)
wb.close()

Using "def statement", a function is defined that takes the elements from list one by one and writes them to Excel.

> write_list_2d(sheet, list_2D, 2, 1)

Arguments are passed to user-defined function.

I referred to this site.

sponsored link

How to write list to Excel using "pandas"

As a supplement, I show how to use pandas to write list to Excel.

This is an example code.

import pandas as pd

list_2D = [
[11, 12, 13, 14],
[21, 22, 23, 24],
[31, 32, 33, 34],
]

path_excel = r'C:\***** any path *****\test.xlsx'

df = pd.DataFrame(list_2D)

df.to_excel(path_excel, sheet_name='test', index=False, header=False, startrow=1, startcol=0)

> df = pd.DataFrame(list_2D)

The list is converted to a DataFrame object.

> df.to_excel(path_excel, sheet_name='test', index=False, header=False, startrow=1, startcol=0)

DataFrame object is output to Excel.

sponsored link