{"id":154,"date":"2013-04-01T23:23:35","date_gmt":"2013-04-02T04:23:35","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=154"},"modified":"2013-04-01T23:23:35","modified_gmt":"2013-04-02T04:23:35","slug":"read-excel-files-from-python","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/04\/01\/read-excel-files-from-python\/","title":{"rendered":"Read Excel files from Python"},"content":{"rendered":"

Use the excellent\u00a0xlrd<\/a>\u00a0package, which works on any platform. That means you can read Excel files from Python in Linux! Example usage:<\/p>\n

Open the workbook<\/p>\n

\n
\n\n\n\n
\n
\n
import<\/code> xlrd<\/code><\/div>\n
wb <\/code>=<\/code> xlrd.open_workbook(<\/code>'myworkbook.xls'<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Check the sheet names<\/p>\n

\n
\n\n\n\n
\n
\n
wb.sheet_names()<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Get the first sheet either by index or by name<\/p>\n

\n
\n\n\n\n
\n
\n
sh <\/code>=<\/code> wb.sheet_by_index(<\/code>0<\/code>)<\/code><\/div>\n
sh <\/code>=<\/code> wb.sheet_by_name(u<\/code>'Sheet1'<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Iterate through rows, returning each as a list that you can index:<\/p>\n

\n
\n\n\n\n
\n
\n
for<\/code> rownum <\/code>in<\/code> range<\/code>(sh.nrows):<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>print<\/code> sh.row_values(rownum)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

If you just want the first column:<\/p>\n

\n
\n\n\n\n
\n
\n
first_column <\/code>=<\/code> sh.col_values(<\/code>0<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Index individual cells:<\/p>\n

\n
\n\n\n\n
\n
\n
cell_A1 <\/code>=<\/code> sh.cell(<\/code>0<\/code>,<\/code>0<\/code>).value<\/code><\/div>\n
cell_C4 <\/code>=<\/code> sh.cell(rowx<\/code>=<\/code>3<\/code>,colx<\/code>=<\/code>2<\/code>).value<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

(Note Python indices start at zero but Excel starts at one)<\/p>\n

Turns out the\u00a0put_cell()\u00a0method isn\u2019t supported, so ignore the following section (Thanks for the heads up, John!)<\/p>\n

Put something in the cell:<\/span><\/p>\n

\n
\n\n\n\n
\n
\n
row <\/code>=<\/code> 0<\/code><\/div>\n
col <\/code>=<\/code> 0<\/code><\/div>\n
ctype <\/code>=<\/code> 1<\/code>\u00a0 # see below<\/code><\/div>\n
value <\/code>=<\/code> 'asdf'<\/code><\/div>\n
xf <\/code>=<\/code> 0<\/code>\u00a0 # extended formatting (use 0 to use default)<\/code><\/div>\n
sh.put_cell(row, col, ctype, value, xf)<\/code><\/div>\n
sh.cell(<\/code>0<\/code>,<\/code>0<\/code>)\u00a0 <\/code># text:u'asdf'<\/code><\/div>\n
sh.cell(<\/code>0<\/code>,<\/code>0<\/code>).value\u00a0 <\/code># 'asdf'<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Use the excellent\u00a0xlrd\u00a0package, which works on any platform. That means you can read Excel files from Python in Linux! Example usage: Open the workbook import… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-154","post","type-post","status-publish","format-standard","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=154"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/154\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}