python把excel数据读入列表_将Excel列中的数据读入Python列表(Read data in Excel column into Python list)...

将Excel列中的数据读入Python列表(Read data in Excel column into Python list)

我使用python xlwings读取Excel 2013中的一列数据。列A中填充了数字。 要将此列导入python列表py_list ，我有以下代码;

import xlwings as xw

wb = xw.Book('BookName.xlsm')

sht = xw.Book('SheetName')

py_list = sht.range('A2:A40').value

如果列数据填充在A2:A40则上述代码有效。 但是，列数据可以继续增长。 数据可以增长并延伸到A2:A46或A2:A80 。 最后一行是空的。 在编译时不知道此列中有多少行数据。

如何修改代码以检测最后一行的空单元格，以便py_list可以读取数据范围？

我愿意使用其他python库来读取除xlwings之外的Excel数据。 我正在使用python v3.6

I am using python xlwings to read a column of data in Excel 2013. Column A is populated with numbers. To import this column into a python list py_list, I have the following code;

import xlwings as xw

wb = xw.Book('BookName.xlsm')

sht = xw.Book('SheetName')

py_list = sht.range('A2:A40').value

The above code works if the column data is populated at A2:A40. However, the column data can keep growing. Data can grow and stretch to A2:A46 or A2:A80. The last row is empty. It is not known at compile time how many rows of data is in this column.

How can I modify the code to detect the empty cell at the last row so that the range of data can be read by py_list?

I am open to using other python libraries to read the Excel data besides xlwings. I am using python v3.6

原文：https://stackoverflow.com/questions/45708626

2020-02-16 17:59

满意答案

我说这很多关于从csv或excel中读取文件，但我会使用pandas 。

import pandas as pd

df = pd.read_excel('filename.xlsm', sheetname=0) # can also index sheet by name or fetch all sheets

mylist = df['column name'].tolist()

另一种方法是使用动态公式，如excel中的OFFSET࿰