使用Python从Excel中提取列 [英] Extract columns from Excel using Python

问题描述

我有一个带有ff：row / col结构的Excel文件

I have an Excel file with the ff: row/col structure

ID   English   Spanish   French
 1   Hello     Hilo      Halu
 2   Hi        Hye       Ghi
 3   Bus       Buzz      Bas

我想阅读Excel文件，提取行和列值，并根据英文，西班牙文和法文列创建3个新文件。

I would like to read the Excel file, extract the row and col values, and create 3 new files base on the columns English, Spanish, and French.

所以我会有一些像：

英文文件：

"1" = "Hello"
"2" = "Hi"
"3" = "Bus"

我一直在使用xlrd。我可以打开，读取和打印文件的内容。但是，这正是我使用这个命令（Excel文件已经打开）：

I've been using xlrd. I can open, read, and print the contents of the file. However, this is what I get using this command (with the Excel file already open):

for index in xrange(0,2):
    theWord = '\n' + str(sh.col_values(index, start_rowx=index, end_rowx=1)) + '=' + str(sh.col_values(index+1, start_rowx=index, end_rowx = 1))
    print theWord

OUTPUT：

[u'Parameter/Variable/Key/String']=[u'ENGLISH'] <-- is this a list?, didn't the str() use to strip it out?

在那里做什么你？
如何删除方括号？

What's the u doing there? How can I remove the square brackets?

推荐答案

u 表示它是一个unicode字符串，当您调用 str（）时，它将被放在那里。如果你把字符串写到一个文件，它不会在那里。你得到的是从列1行。这是因为您使用 end_rowx = 1 它返回一个包含一个元素的列表。

The u means it is a unicode string, it gets put there when you call str(). If you write the string out to a file it wont be there. What you are getting is 1 row from the column. It's because you are using end_rowx=1 it returns a list with one element.

尝试获取列值列表：

ids = sh.col_values(0, start_rowx=1)
english = sh.col_values(1, start_rowx=1)
spanish = sh.col_values(2, start_rowx=1)
french = sh.col_values(3, start_rowx=1)

然后您可以将 zip 他们转换为元组列表：

and then you can zip them into tuple lists:

english_with_IDS = zip(ids, english)
spanish_with_IDS = zip(ids, spanish)
french_with_IDS = zip(ids, french)

以下形式：

("1", "Hello"),("2", "Hi"), ("3", "Bus")

如果要打印对：

for id, word in english_with_IDS:
       print id + "=" + word

col_values 返回一列列值，如果您想要单个值可以调用 sh.cell_value（rowx，cellx）。

col_values returns a list of column values, if you want single values you can call sh.cell_value(rowx, cellx).

这篇关于使用Python从Excel中提取列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！