如何将Python数组写入Excel Spread sh

Question

我正在尝试使用Python将以下数组写入Excel电子表格：

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

在电子表格数组中应该看到：

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

有没有人可以显示一些Python代码来完成这个任务？ 提前谢谢你

费利克斯

Answer 1

使用熊猫数据帧！

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是str或现有ExcelWriter对象中的文件路径。

Answer 2

我见过的用Python编写excel电子表格的最常见方法是使用OpenPyXL，这是Python的非本机库。另一个我听说偶尔使用的是XlsxWriter，不过，它也是非本地的。这两个站点都有关于如何最好地使用库的优秀文档，但下面是我为演示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)

编辑：例如，您的请求可以这样写：

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

老实说，这可能不起作用，但我太累了，无法测试。我想你已经明白了。

Answer 3

下面是一种使用XlsxWriter模块的方法：

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()

输出：