Writing to an Excel spreadsheet

Question

I am new to Python. I need to write some data from my program to a spreadsheet. I searched on the Internet and there seem to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest writing to a .csv file (never used CSV and didn't quite understand what it was).

The program is very simple. I have two lists (float) and three variables (strings). I do not know the length of the two lists, and they probably will not be the same length.

I want the layout to be as in the image 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 is the best way to do this?

PS I use Windows 7, but I do not have to have Office installed on computers with 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. He does his job, but can be slightly improved.

How to format cells created in a for loop (list1 values) as scientific or numeric?

I do not want to truncate the values. The actual values ​​used in the program will have about 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 a more detailed explanation: https://github.com/python-excel

Answer 2

Use DataFrame.to_excel from pandas . Pandas allows you to represent your data in functionally rich data structures and allows you to read in excel files .

First you will need to convert your data to a DataFrame, and then save it to an excel file, for example:

 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:

Please note that both lists must be the same length, otherwise Pandas will complain. To resolve this issue, replace all missing values ​​with None .

Answer 3

• xlrd / xlwt (standard): Python does not have this functionality in the standard library, but I think of xlrd / xlwt as the “standard” way to read and write excel files. It's pretty easy to make a book, add sheets, write data / formulas and format cells. If you need all of these things, you can be most successful in this library. I think you could choose openpyxl instead, and that would be very similar, but I did not use it.

  To format cells using xlwt, define XFStyle and enable style when writing to a worksheet. Here is an example with many number formats . See the sample code below.

• Tablib (powerful, intuitive): Tablib is a more powerful but intuitive library for working with tabular data. He can write Excel workbooks with multiple sheets as well as other formats such as csv, json and yaml. If you do not need formatted cells (for example, background color), you will do a favor to use this library, which will help you in the future in the long run.

• csv (easy): the files on your computer are text or binary. Text files are just characters, including special ones, such as new lines and tabs, and they can be easily opened anywhere (for example, notepad, 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 read and write text easily, so a csv file is the easiest and fastest way to export data from your python program to excel (or another python program).

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

---

 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

CSV stands for comma separated values. CSV is like a text file and can be created simply by adding . 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 5

 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 6

Try also a look at the following libraries:

xlwings - for retrieving data in and from a spreadsheet from Python, as well as for processing books and charts

ExcelPython - Excel add-in for writing custom functions (UDF) and macros in Python instead of VBA

Answer 7

I reviewed several Excel modules for Python and found openpyxl the best.

The free Automate the Boring Stuff with Python book has a chapter on openpyxl with more details, or you can check out the Read the Docs website. You will not need to install Office or Excel to use openpyxl.

Your program will 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 8

OpenPyxl is a good library created for reading / writing Excel 2010 xlsx / xlsm files:

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

Another answer , citing this, uses the get_sheet_by_name ( get_sheet_by_name ) function. Here's 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 9

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

 print variable_example[:13] 

It would limit it to the tenth decimal place, considering that your data has two integers to the left of the decimal.

Answer 10

You can try the 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()