python - Writing to an Excel spreadsheet

Question

I am new to Python. I need to write some data from my program to a spreadsheet. I've searched online and there seem to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest to write to a .csv file (never used CSV and don't really understand what it is).

The program is very simple. I have two lists (float) and three variables (strings). I don't know the lengths of the two lists and they probably won't be the same length.

I want the layout to be as in the picture below:

The pink column will have the values of the first list and the green column will have the values of the second list.

So what's the best way to do this?

P.S. I am running Windows 7 but I won't necessarily have Office installed on the computers running this program.

import xlwt

x=1
y=2
z=3

list1=[2.34,4.346,4.234]

book = xlwt.Workbook(encoding="utf-8")

sheet1 = book.add_sheet("Sheet 1")

sheet1.write(0, 0, "Display")
sheet1.write(1, 0, "Dominance")
sheet1.write(2, 0, "Test")

sheet1.write(0, 1, x)
sheet1.write(1, 1, y)
sheet1.write(2, 1, z)

sheet1.write(4, 0, "Stimulus Time")
sheet1.write(4, 1, "Reaction Time")

i=4

for n in list1:
    i = i+1
    sheet1.write(i, 0, n)



book.save("trial.xls")

I wrote this using all your suggestions. It gets the job done but it can be slightly improved.

How do I format the cells created in the for loop (list1 values) as scientific or number?

I do not want to truncate the values. The actual values used in the program would have around 10 digits after the decimal.

Answer 1

import xlwt

def output(filename, sheet, list1, list2, x, y, z):
    book = xlwt.Workbook()
    sh = book.add_sheet(sheet)

    variables = [x, y, z]
    x_desc = 'Display'
    y_desc = 'Dominance'
    z_desc = 'Test'
    desc = [x_desc, y_desc, z_desc]

    col1_name = 'Stimulus Time'
    col2_name = 'Reaction Time'

    #You may need to group the variables together
    #for n, (v_desc, v) in enumerate(zip(desc, variables)):
    for n, v_desc, v in enumerate(zip(desc, variables)):
        sh.write(n, 0, v_desc)
        sh.write(n, 1, v)

    n+=1

    sh.write(n, 0, col1_name)
    sh.write(n, 1, col2_name)

    for m, e1 in enumerate(list1, n+1):
        sh.write(m, 0, e1)

    for m, e2 in enumerate(list2, n+1):
        sh.write(m, 1, e2)

    book.save(filename)

for more explanation: https://github.com/python-excel

Answer 2

Use DataFrame.to_excel from pandas. Pandas allows you to represent your data in functionally rich datastructures and will let you read in excel files as well.

You will first have to convert your data into a DataFrame and then save it into an excel file like so:

In [1]: from pandas import DataFrame
In [2]: l1 = [1,2,3,4]
In [3]: l2 = [1,2,3,4]
In [3]: df = DataFrame({'Stimulus Time': l1, 'Reaction Time': l2})
In [4]: df
Out[4]: 
   Reaction Time  Stimulus Time
0              1              1
1              2              2
2              3              3
3              4              4

In [5]: df.to_excel('test.xlsx', sheet_name='sheet1', index=False)

and the excel file that comes out looks like this:

Note that both lists need to be of equal length else pandas will complain. To solve this, replace all missing values with None.

Answer 3

• xlrd/xlwt (standard): Python does not have this functionality in it's standard library, but I think of xlrd/xlwt as the "standard" way to read and write excel files. It is fairly easy to make a workbook, add sheets, write data/formulas, and format cells. If you need all of these things, you may have the most success with this library. I think you could choose openpyxl instead and it would be quite similar, but I have not used it.

  To format cells with xlwt, define a XFStyle and include the style when you write to a sheet. Here is an example with many number formats. See example code below.

• Tablib (powerful, intuitive): Tablib is a more powerful yet intuitive library for working with tabular data. It can write excel workbooks with multiple sheets as well as other formats, such as csv, json, and yaml. If you don't need formatted cells (like background color), you will do yourself a favor to use this library, which will get you farther in the long run.

• csv (easy): Files on your computer are either text or binary. Text files are just characters, including special ones like newlines and tabs, and can be easily opened anywhere (e.g. notepad, your web browser, or Office products). A csv file is a text file that is formatted in a certain way: each line is a list of values, separated by commas. Python programs can easily read and write text, so a csv file is the easiest and fastest way to export data from your python program into excel (or another python program).

  Excel files are binary and require special libraries that know the file format, which is why you need an additional library for python, or a special program like Microsoft Excel, Gnumeric, or LibreOffice, to read/write them.

---

import xlwt

style = xlwt.XFStyle()
style.num_format_str = '0.00E+00'

...

for i,n in enumerate(list1):
    sheet1.write(i, 0, n, fmt)

Answer 4

I surveyed a few Excel modules for Python, and found openpyxl to be the best.

The free book Automate the Boring Stuff with Python has a chapter on openpyxl with more details or you can check the Read the Docs site. You won't need Office or Excel installed in order to use openpyxl.

Your program would look something like this:

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

stimulusTimes = [1, 2, 3]
reactionTimes = [2.3, 5.1, 7.0]

for i in range(len(stimulusTimes)):
    sheet['A' + str(i + 6)].value = stimulusTimes[i]
    sheet['B' + str(i + 6)].value = reactionTimes[i]

wb.save('example.xlsx')

Answer 5

CSV stands for comma separated values. CSV is like a text file and can be created simply by adding the .CSV extension

for example write this code:

f = open('example.csv','w')
f.write("display,variable x")
f.close()

you can open this file with excel.

Answer 6

import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# Insert an image.
worksheet.insert_image('B5', 'logo.png')

workbook.close()

Answer 7

Try taking a look at the following libraries too:

xlwings - for getting data into and out of a spreadsheet from Python, as well as manipulating workbooks and charts

ExcelPython - an Excel add-in for writing user-defined functions (UDFs) and macros in Python instead of VBA

Answer 8

The xlsxwriter library is great for creating .xlsx files. The following snippet generates an .xlsx file from a list of dicts while stating the order and the displayed names:

from xlsxwriter import Workbook


def create_xlsx_file(file_path: str, headers: dict, items: list):
    with Workbook(file_path) as workbook:
        worksheet = workbook.add_worksheet()
        worksheet.write_row(row=0, col=0, data=headers.values())
        header_keys = list(headers.keys())
        for index, item in enumerate(items):
            row = map(lambda field_id: item.get(field_id, ''), header_keys)
            worksheet.write_row(row=index + 1, col=0, data=row)


headers = {
    'id': 'User Id',
    'name': 'Full Name',
    'rating': 'Rating',
}

items = [
    {'id': 1, 'name': "Ilir Meta", 'rating': 0.06},
    {'id': 2, 'name': "Abdelmadjid Tebboune", 'rating': 4.0},
    {'id': 3, 'name': "Alexander Lukashenko", 'rating': 3.1},
    {'id': 4, 'name': "Miguel Díaz-Canel", 'rating': 0.32}
]

create_xlsx_file("my-xlsx-file.xlsx", headers, items)

---

💡 Note 1 - I'm purposely not answering to the exact case the OP presented. Instead, I'm presenting a more generic solution IMHO most visitors seek. This question's title is well-indexed in search engines and tracks lots of traffic

💡 Note 2 - If you're not using Python3.6 or newer, consider using OrderedDict in headers. Before Python3.6 the order in dict was not preserved.

---

Answer 9

OpenPyxl is quite a nice library, built to read/write Excel 2010 xlsx/xlsm files:

https://openpyxl.readthedocs.io/en/stable

The other answer, referring to it is using the deperciated function (get_sheet_by_name). This is how to do it without it:

import openpyxl

wbkName = 'New.xlsx'        #The file should be created before running the code.
wbk = openpyxl.load_workbook(wbkName)
wks = wbk['test1']
someValue = 1337
wks.cell(row=10, column=1).value = someValue
wbk.save(wbkName)
wbk.close

Answer 10

The easiest way to import the exact numbers is to add a decimal after the numbers in your l1 and l2. Python interprets this decimal point as instructions from you to include the exact number. If you need to restrict it to some decimal place, you should be able to create a print command that limits the output, something simple like:

print variable_example[:13]

Would restrict it to the tenth decimal place, assuming your data has two integers left of the decimal.

Answer 11

You can try hfexcel Human Friendly object-oriented python library based on XlsxWriter:

from hfexcel import HFExcel

hf_workbook = HFExcel.hf_workbook('example.xlsx', set_default_styles=False)

hf_workbook.add_style(
    "headline", 
    {
       "bold": 1,
        "font_size": 14,
        "font": "Arial",
        "align": "center"
    }
)

sheet1 = hf_workbook.add_sheet("sheet1", name="Example Sheet 1")

column1, _ = sheet1.add_column('headline', name='Column 1', width=2)
column1.add_row(data='Column 1 Row 1')
column1.add_row(data='Column 1 Row 2')

column2, _ = sheet1.add_column(name='Column 2')
column2.add_row(data='Column 2 Row 1')
column2.add_row(data='Column 2 Row 2')


column3, _ = sheet1.add_column(name='Column 3')
column3.add_row(data='Column 3 Row 1')
column3.add_row(data='Column 3 Row 2')

# In order to get a row with coordinates:
# sheet[column_index][row_index] => row
print(sheet1[1][1].data)
assert(sheet1[1][1].data == 'Column 2 Row 2')

hf_workbook.save()

Answer 12

If your need is to modify an existing workbook, the safest way would be to use pyoo. You need to have some libraries installed and it takes a few hoops to jump through but once its set up, this would be bulletproof as you are leveraging the wide and solid API's of LibreOffice / OpenOffice.

Please see my Gist on how to set up a linux system and do some basic coding using pyoo.

Here is an example of the code:

#!/usr/local/bin/python3
import pyoo
# Connect to LibreOffice using a named pipe 
# (named in the soffice process startup)
desktop = pyoo.Desktop(pipe='oo_pyuno')
wkbk = desktop.open_spreadsheet("<xls_file_name>")
sheet = wkbk.sheets['Sheet1']
# Write value 'foo' to cell E5 on Sheet1
sheet[4,4].value='foo'
wkbk.save()
wkbk.close()