How to write python array to Excel spread sheet
I am trying to write the following array to an Excel spreadsheet using Python:
array = [ [a1,a2,a3], [a4,a5,a6], [a7,a8,a9], [a10, a11, a12, a13, a14]]
In the table of the table you should see:
a1 a4 a7 a10
a2 a5 a8 a11
a3 a6 a9 a12
a13
a14
Can anyone show some Python code for this? Thank you in advance,
Felix
source to share
Here's one way to do it using the XlsxWriter module :
import xlsxwriter
workbook = xlsxwriter.Workbook('arrays.xlsx')
worksheet = workbook.add_worksheet()
array = [['a1', 'a2', 'a3'],
['a4', 'a5', 'a6'],
['a7', 'a8', 'a9'],
['a10', 'a11', 'a12', 'a13', 'a14']]
row = 0
for col, data in enumerate(array):
worksheet.write_column(row, col, data)
workbook.close()
Output:
source to share
The most common way I've seen to write an Excel spreadsheet using Python is using OpenPyXL , which is native to python. Another one I have heard is sometimes used is the XlsxWriter , again, it is not native. Both sites have excellent documentation on how to make the best use of the libraries, but below is some simple code I wrote to demonstrate OpenPyXL :
from openpyxl import workbook
from openpyxl.cell import get_column_letter
workbook = Workbook() # the master workbook
output_file_name = "outfile.xlsx" # what "workbook" will be saved as
worksheet = workbook.active() # all workbooks have one worksheet already selected as the default
worksheet.title = "foo"
worksheet['A3'] = "=SUM(A1, A2)" # set up basic formula
wb.save(output_file_name)
EDIT: For example, your request could be written like this:
## imports and stuff ##
array = [ [a1,a2,a3], [a4,a5,a6], [a7,a8,a9], [a10, a11, a12, a13, a14]]
workbook = Workbook()
worksheet = workbook.active()
numrows = len(array)
letter = 'A'
for r in range(0, numrows):
if r == 0: letter = 'A'
if r == 1: letter = 'B'
if r == 2: letter = 'C'
...
numcols = len(array[r])
for c in range(0, numcols):
worksheet[letter.join(c)] = array[r][c]
To be honest, it might not even work, but I'm too tired to check. I think you get the idea.
source to share
print array
This prints the array to the Python console with square brackets denoting the start and end of lines. Select it all and copy to Excel folder. Click on the insert icon → Text Import Wizard. This should result in this .
Select "Fixed Width" and click "Next" to get this
Click Next and click Finish. This will do it. You still need to remove the trailing brackets from some of the cells.
source to share
Use the pandas data frame!
import pandas as pd
array = [['a1', 'a2', 'a3'],
['a4', 'a5', 'a6'],
['a7', 'a8', 'a9'],
['a10', 'a11', 'a12', 'a13', 'a14']]
df = pd.DataFrame(array).T
df.to_excel(excel_writer = "C:/Users/Jing Li/Desktop/test.xlsx")
excel_writer - The path to the file in str or an existing ExcelWriter object.
source to share