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

User · Accepted Answer

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

User · Answer

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   lt xls file name gt    sheet   wkbk sheets  Sheet1     Write value  foo  to cell E5 on Sheet1 sheet 4 4  value  foo  wkbk save   wkbk close

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    quot Ilir Meta quot    rating   0 06         id   2   name    quot Abdelmadjid Tebboune quot    rating   4 0         id   3   name    quot Alexander Lukashenko quot    rating   3 1         id   4   name    quot Miguel D  az-Canel quot    rating   0 32     create xlsx file  quot my-xlsx-file xlsx quot   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

User · Answer

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    gt  row print sheet1 1  1  data  assert sheet1 1  1  data     Column 2 Row 2    hf workbook save

User · Answer

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

[python] Writing to an Excel spreadsheet

Examples related to python

Examples related to excel

Examples related to csv