How to write Python Array into Excel Spread sheet

Question

I am trying to write following array into Excel spreadsheet using Python:

array = [ [a1,a2,a3], [a4,a5,a6], [a7,a8,a9], [a10, a11, a12, a13, a14]]

At spreadsheet array should look:

a1  a4  a7  a10
a2  a5  a8  a11
a3  a6  a9  a12
            a13
            a14

Is anyone can show some Python code to do it? Thank you in advance,

Felix

Answer 1

Use 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 is File path in str or existing ExcelWriter object.

Answer 2

Here is 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:

Answer 3

The most common way that I've seen of writing to an excel spreadsheet with Python is by using OpenPyXL, a library non-native to python. Another that I've heard that is occasionally used is the XlsxWriter, again, though, it's non-native. Both sites have great documentation on how to best use the libraries but below is some simple code I wrote up 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 as such:

## 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]

Honestly this might not even work but I'm too tired to test. I think you get the idea though.

Answer 4

print array

This prints the array in the Python console with square brackets marking the beginning and end of rows. Select the whole of that and copy-paste to Excel. Click on the paste icon -> Text Import Wizard. That should bring up this.

Choose Fixed Width and click Next to get this

Click Next and click Finish. That will do it. You'll still need to delete the ending brackets from some of the cells.

Answer 5

If you are already using pandas for your task, you can use it easily to create a dataframe and turn it into an Excel sheet. If not, it is easier to use xlsxwriter rather than pandas because pandas is a little heavy library.

pip install XlsxWriter

import xlsxwriter

workbook = xlsxwriter.Workbook("MyExcel.xlsx")
worksheet = workbook.add_worksheet()

There are multiple ways to write the data to excel. You can use write_row(), write_column() or write cell by cell also.

write_row(row, column, data)

row = [1, 2, 3, 4, 5]

worksheet.write_row(1, 2, row)

write_column(row, column, data)

column= [1, 2, 3, 4, 5]

worksheet.write_column(1, 2, column)

write(row, column, data)

worksheet.write(0, 1, "Hello")
worksheet.write(0, 2, "World")

Finally, close the workbook.

workbook.close()

Like this, there are many ways to write data and also you can conditional formatting to your cells or rows or columns. The documentation can be found here.