MAC:python操作excel的环境配置
本人python小白,有一个事情是要分析一下excel表格中的数据,excel中数据量巨大,直接用眼睛看不太现实,因此准备写个python脚本来操作,本来以为这个事情很简单,只要写好python代码然后运行即可。没想到踩了不少的坑。现记录如下:
python源码如下:#readexcel.py
#!/usr/bin/env python3
# coding=UTF-8
import xlrd
from datetime import date,datetime
file = 'test.xlsx'
def read_excel():
wb = xlrd.open_workbook(filename=file)#打开文件
print(wb.sheet_names())#获取所有表格名字
sheet1 = wb.sheet_by_index(0)#通过索引获取表格
sheet2 = wb.sheet_by_name('Sheet2')#通过名字获取表格
print(sheet1,sheet2)
print(sheet1.name,sheet1.nrows,sheet1.ncols)
rows = sheet1.row_values(2)#获取行内容
cols = sheet1.col_values(3)#获取列内容
print(rows)
print(cols)
print(sheet1.cell(1,0).value)#获取表格里的内容,三种方式
print(sheet1.cell_value(1,0))
print(sheet1.row(1)[0].value)
if __name__ == '__main__':
read_excel()
Excel的数据如下所示(一些测试数据):
Mac终端运行:python readexcel.py
提示如下错误:
Traceback (most recent call last):
File "readexcel.py", line 4, in <module>
import xlrd
ImportError: No module named xlrd
ok,操作excel的工具xlrd,不属于python标准库,需要单独安装,命令为:
sudo pip3 install xlrd
提示如下错误:
sudo: pip: command not found
好,继续安装pip
sudo easy_install pip
看到如下信息:
Searching for pip
Reading https://pypi.python.org/simple/pip/
Download error on https://pypi.python.org/simple/pip/: [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:590) -- Some packages may not be found!
Couldn't find index page for 'pip' (maybe misspelled?)
Scanning index of all packages (this may take a while)
Reading https://pypi.python.org/simple/
Download error on https://pypi.python.org/simple/: [SSL: TLSV1_ALERT_PROTOCOL_VERSION] tlsv1 alert protocol version (_ssl.c:590) -- Some packages may not be found!
No local packages or download links found for pip
error: Could not find suitable distribution for Requirement.parse('pip')
没有找到pip,为什么呢,打开https://pypi.python.org/simple/pip/ 该网址发现,目标网址已经被重定向了,查看easy_install的源码,发现难以直接找到该网址的设置位置。换别的方法:
sudo curl https://bootstrap.pypa.io/get-pip.py | python
收到如下信息:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1662k 100 1662k 0 0 99k 0 0:00:16 0:00:16 --:--:-- 80053
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7.
Collecting pip
Using cached https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl
Collecting wheel
Using cached https://files.pythonhosted.org/packages/7c/d7/20bd3c501f53fdb0b7387e75c03bd1fce748a1c3dd342fc53744e28e3de1/wheel-0.33.0-py2.py3-none-any.whl
Installing collected packages: pip, wheel
Could not install packages due to an EnvironmentError: [Errno 13] Permission denied: '/Library/Python/2.7/site-packages/pip'
Consider using the `--user` option or check the permissions.
权限不足,好吧,仔细思考下,应该是python的权限不足,改为如下指令:
sudo curl https://bootstrap.pypa.io/get-pip.py | sudo python
如下所示,安装成功
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1662k 100 1662k 0 0 75235 0 0:00:22 0:00:22 --:--:-- 72068
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7.
The directory '/Users/wangguoqiang/Library/Caches/pip/http' or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
The directory '/Users/wangguoqiang/Library/Caches/pip' or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
Collecting pip
Downloading https://files.pythonhosted.org/packages/d7/41/34dd96bd33958e52cb4da2f1bf0818e396514fd4f4725a79199564cd0c20/pip-19.0.2-py2.py3-none-any.whl (1.4MB)
100% |████████████████████████████████| 1.4MB 221kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/7c/d7/20bd3c501f53fdb0b7387e75c03bd1fce748a1c3dd342fc53744e28e3de1/wheel-0.33.0-py2.py3-none-any.whl
Installing collected packages: pip, wheel
Successfully installed pip-19.0.2 wheel-0.33.0
然后继续:
sudo pip install xlrd
也提示安装成功:
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7.
The directory '/Users/wangguoqiang/Library/Caches/pip/http' or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
The directory '/Users/wangguoqiang/Library/Caches/pip' or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
Collecting xlrd
Downloading https://files.pythonhosted.org/packages/b0/16/63576a1a001752e34bf8ea62e367997530dc553b689356b9879339cf45a4/xlrd-1.2.0-py2.py3-none-any.whl (103kB)
100% |████████████████████████████████| 112kB 335kB/s
Installing collected packages: xlrd
Successfully installed xlrd-1.2.0
OK,终于看到了曙光,运行一下试试:
python readexcel.py
成了:
[u'Sheet1', u'Sheet2']
(<xlrd.sheet.Sheet object at 0x1021fc350>, <xlrd.sheet.Sheet object at 0x1021fc390>)
(u'Sheet1', 4, 4)
[123.0, u'def', u'123def', u'def123']
[u't4', u'abc123', u'def123', u'def1232']
123.0
123.0
123.0