python3.8如何正确安装使用ExcelLibrary

前因

尝试过pip install robotframework-ExcelLibrary 会发现安装时报错。

(auto-venv) D:\Program Files\GitData\auto\AutoTest\AutoLink>pip install robotframework-ExcelLibrary==0.0.2
Collecting robotframework-ExcelLibrary==0.0.2
  Using cached robotframework-excellibrary-0.0.2.zip (61 kB)
    ERROR: Command errored out with exit status 1:
     command: 'D:\Program Files\GitData\auto\AutoTest\AutoLink\auto-venv\Scripts\python.exe' -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\\Users\\EL128\\AppData\\Local\\Temp\\pip-install-6yxp68eu\\robotframework-excellibrary\\setup.py'"'"'; __file__='"'"'C:\\Users\\EL128\\AppData\\Local\\Temp\\pip-install-6yxp68eu\\robotframework-excellibrary\\setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base 'C:\Users\EL128\AppData\Local\Temp\pip-pip-egg-info-m4sl5p0k'
         cwd: C:\Users\EL128\AppData\Local\Temp\pip-install-6yxp68eu\robotframework-excellibrary\
    Complete output (5 lines):
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "C:\Users\EL128\AppData\Local\Temp\pip-install-6yxp68eu\robotframework-excellibrary\setup.py", line 23, in <module>
        execfile(join(dirname(__file__), 'ExcelLibrary', 'version.py'))
    NameError: name 'execfile' is not defined
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.
WARNING: You are using pip version 20.2.1; however, version 21.0.1 is available.
You should consider upgrading via the 'D:\Program Files\GitData\auto\AutoTest\AutoLink\auto-venv\Scripts\python.exe -m pip install --upgrade pip' command.

通过查询相关资料得知，python3版本已经淘汰了execfile，而是采用了exce
如果python3版本要使用excelLibrary的话，需要下载源码后对其源码进行相应的修改。

新的改变

修改后的源码包可取这里：

---

也可以自行取源码后修改，关于python3将Python2支持的execfile废弃掉了，用exec代替相关：
1.首先先离线安装包，安装包解压文件放在python安装路径下：如F:\python38\Lib\site-packages文件中，如果是虚拟环境，同样也可以放到虚拟环境对应的site-packages文件中。
robotframework-ExcelLibrary
离线下载链接：
https://files.pythonhosted.org/packages/b8/e7/8c079a814e7ad288ec2fc15671d8dc526e3d537bb00e4ab2b209a63674ed/robotframework-excellibrary-0.0.2.zip

2.第二步解压文件，如下图依次进行修改。 （3.x print是个函数，必须加小括号进行函数调用）
2.1文件 setup.py

#execfile(join(dirname(file), ‘ExcelLibrary’, ‘version.py’))
exec(open(join(dirname(file), ‘ExcelLibrary’, ‘version.py’)).read())

2.2文件 ExcelLibrary.py
第一个
将所有的print xxx 语句修改为 print（xxx）

第二个
#from version import VERSION

from .version import VERSION

2.3文件 init.py

#from ExcelLibrary import ExcelLibrary
#from version import VERSION

from .ExcelLibrary import ExcelLibrary
from .version import VERSION

3.第三步我们无法通过pip安装，只能手动安装了，使用 python setup.py install

python setup.py install 属于离线安装，安装方式：下载好压缩包 ->解压 -> 在解压目录的当前文件夹下，打开终端，输入 ： python setup.py install　即可

(auto-venv) D:\Program Files\GitData\auto\AutoTest>cd robotframework-excellibrary-0.0.2

(auto-venv) D:\Program Files\GitData\auto\AutoTest\robotframework-excellibrary-0.0.2>python setup.py install
c:\python38\lib\distutils\dist.py:274: UserWarning: Unknown distribution option: 'install_requires'
  warnings.warn(msg)
running install
running build
running build_py
copying ExcelLibrary\__init__.py -> build\lib\ExcelLibrary
running install_lib
running install_data
running install_egg_info
Writing D:\Program Files\GitData\auto\AutoTest\AutoLink\auto-venv\Lib\site-packages\robotframework_excellibrary-0.0.2-py3.8.egg-info

4、安装成功后，pip list查看

(auto-venv) D:\Program Files\GitData\auto\AutoTest\robotframework-excellibrary-0.0.2>pip list
Package                               Version
------------------------------------- ----------
aniso8601                             9.0.1
appdirs                               1.4.4
Appium-Python-Client                  0.26
APScheduler                           3.5.1
bcrypt                                3.2.0
blinker                               1.4
certifi                               2020.12.5
cffi                                  1.14.5
chardet                               3.0.4
click                                 7.1.2
ConfigArgParse                        1.2.3
cryptography                          3.4.6
decorator                             4.4.2
distlib                               0.3.1
docutils                              0.16
et-xmlfile                            1.0.1
filelock                              3.0.12
Flask                                 0.11
Flask-APScheduler                     1.8.0
Flask-BasicAuth                       0.2.0
Flask-Login                           0.2.11
Flask-Mail                            0.9.0
Flask-RESTful                         0.3.6
Flask-Script                          2.0.6
Flask-SSLify                          0.1.5
future                                0.18.2
gevent                                20.6.2
geventhttpclient                      1.4.4
greenlet                              0.4.16
idna                                  2.6
install                               1.3.4
itsdangerous                          1.1.0
Jinja2                                2.8
kitchen                               1.2.6
locust                                1.1.1
MarkupSafe                            1.1.1
msgpack                               1.0.0
natsort                               7.1.1
numpy                                 1.20.1
openpyxl                              3.0.7
paramiko                              2.7.2
Pillow                                8.1.2
pip                                   20.2.1
psutil                                5.7.2
pycparser                             2.20
Pygments                              2.8.1
PyNaCl                                1.4.0
Pypubsub                              4.0.3
python-dateutil                       2.7.3
pytz                                  2021.1
pywin32                               300
pyzmq                                 19.0.2
requests                              2.18.4
robotframework                        3.0.3
robotframework-appiumlibrary          1.4.6
robotframework-databaselibrary        1.0.1
robotframework-excellib-xlsm          2.0.3
robotframework-excellibrary           0.0.2
robotframework-excellibrary-xwfintech 0.0.3
robotframework-requests               0.4.7
robotframework-ride                   2.0b2.dev1
robotframework-seleniumlibrary        3.1.1
robotframework-sshlibrary             3.1.1
selenium                              3.141.0
setuptools                            49.2.1
six                                   1.15.0
tzlocal                               2.1
urllib3                               1.22
virtualenv                            20.0.30
Werkzeug                              0.14.1
wheel                                 0.34.2
wxPython                              4.1.1
xlrd                                  2.0.1
xlutils                               2.0.0
xlwt                                  1.3.0
zope.event                            4.4
zope.interface                        5.1.0
WARNING: You are using pip version 20.2.1; however, version 21.0.1 is available.
You should consider upgrading via the 'D:\Program Files\GitData\auto\AutoTest\AutoLink\auto-venv\Scripts\python.exe -m pip install --upgrade pip' command.

(auto-venv) D:\Program Files\GitData\auto\AutoTest\robotframework-excellibrary-0.0.2>

5.重新开启ride，导入ExcelLibrary，成功
备注：由于ride与python3.8也不兼容，目前安装也报错。正在处理中，等待后续。

参考文章：
https://blog.csdn.net/weixin_44408772/article/details/103815610
https://blog.csdn.net/cfpyl/article/details/97808447

卸载excelLibrary

手动将其卸载：

1）浏览C:\Python27\ExcelRobotTest并删除ExcelRobotTest.txt和ExcelLibrary-KeywordDocumentation.html

2）浏览C:\Python27\Lib\site-packages并删除robotframework-excellibrary-0.0.2-py2.7.egg-info和文件夹robotframework-excellibrary

采用同样的办法，安装ride

下载RIDE开发版：https://github.com/robotframework/RIDE/archive/master.zip，然后解压缩； 在命令行窗口用cd命令跳转到解压缩目录运行安装；

python setup.py install

D:\PythonProject\plugins\RIDE-master>python setup.py install
C:\Python38\lib\site-packages\setuptools\dist.py:471: UserWarning: Normalizing 'v2.0b2.dev1' to '2.0b2.dev1'
  warnings.warn(
running install
running build
running build_py
running build_scripts
running install_lib
copying build\lib\robotide\action\action.py -> C:\Python38\Lib\site-packages\robotide\action
copying build\lib\robotide\action\actioninfo.py -> C:\Python38\Lib\site-packages\robotide\action
copying build\lib\robotide\action\shortcut.py -> C:\Python38\Lib\site-packages\robotide\action
\\\
\\\
\\\
warning: manifest_maker: MANIFEST.in, line 10: 'recursive-include' expects <dir> <pattern1> <pattern2> ...

writing manifest file 'src\robotframework_ride.egg-info\SOURCES.txt'
removing 'C:\Python38\Lib\site-packages\robotframework_ride-2.0b2.dev1-py3.8.egg-info' (and everything under it)
Copying src\robotframework_ride.egg-info to C:\Python38\Lib\site-packages\robotframework_ride-2.0b2.dev1-py3.8.egg-info
running install_scripts
copying build\scripts-3.8\ride.py -> C:\Python38\Scripts
copying build\scripts-3.8\ride_postinstall.py -> C:\Python38\Scripts
Creating Desktop Shortcut to RIDE...
wxPython is installed.
4.1.1 msw (phoenix) wxWidgets 3.1.5
Result True
Desktop shortcut created for all users.

4、安装过程中，选择创建桌面快捷方式，桌面出现，安装完成，打开界面。
问题：双击桌面快捷方式运行不了ride

来源：https://blog.csdn.net/u012100968/article/details/108773220

下载地址：

火狐： https://github.com/mozilla/geckodriver/releases

谷歌：http://chromedriver.storage.googleapis.com/index.html

IE： http://selenium-release.storage.googleapis.com/index.html

Edge： https://developer.microsoft.com/en-us/microsoft-edge/tools/webdriver/

Opera：https://github.com/operasoftware/operachromiumdriver/releases

PhantomJS：http://phantomjs.org/

驱动参考链接：https://blog.csdn.net/qupan1993/article/details/99945299

安装包参考链接：https://blog.csdn.net/weixin_45223124/article/details/105854202

引入excelLibrary
手动安装了excelLibrary后，引入excelLibrary时还报错缺少如下模块：natsort、xlrd、xlwt、xlutils
逐一采用命令：pip install xxx 进行安装，如下

(autolinkVenv) D:\PythonProject\pyEvens\autolinkVenv\Scripts>pip install natsort
Collecting natsort
  Using cached natsort-7.1.1-py3-none-any.whl (35 kB)
Installing collected packages: natsort
Successfully installed natsort-7.1.1

(autolinkVenv) D:\PythonProject\pyEvens\autolinkVenv\Scripts>pip install xlrd
Collecting xlrd
  Using cached xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
Installing collected packages: xlrd
Successfully installed xlrd-2.0.1

(autolinkVenv) D:\PythonProject\pyEvens\autolinkVenv\Scripts>pip install xlwt
Collecting xlwt
  Using cached xlwt-1.3.0-py2.py3-none-any.whl (99 kB)
Installing collected packages: xlwt
Successfully installed xlwt-1.3.0

(autolinkVenv) D:\PythonProject\pyEvens\autolinkVenv\Scripts>pip install xlutils
Collecting xlutils
  Using cached xlutils-2.0.0-py2.py3-none-any.whl (55 kB)
Requirement already satisfied: xlwt>=0.7.4 in d:\pythonproject\pyevens\autolinkvenv\lib\site-packages (from xlutils) (1.3.0)
Requirement already satisfied: xlrd>=0.7.2 in d:\pythonproject\pyevens\autolinkvenv\lib\site-packages (from xlutils) (2.0.1)
Installing collected packages: xlutils
Successfully installed xlutils-2.0.0

(autolinkVenv) D:\PythonProject\pyEvens\autolinkVenv\Scripts>

引入第三方库的时候，一定要指明全路径，如：

*** Settings ***
Library     Collections
Library     RequestsLibrary
Library     D:\\PythonProject\\pyEvens\\autolinkVenv\\Lib\\site-packages\\ExcelLibrary

*** Variables ***
${excel_path}    D:\\PythonProject\\PyDatas\\鉴权接口用例.xls

且一定要用xls后缀的文档。

关键字

ExcelLibrary库常用关键字

Open Excel 打开Excel文件
Get Row Count 获取行数
Get Column Count 获取列数
Get Row Values 获取某一行的值
Get Column Values 获取某一列的值
Read Cell Data By Coordinates 通过列行编号获取值
Read Cell Data By Name 按名称读取单元格数据

如何使用excelLibrary，官方示例

*** Settings ***
Library 			ExcelLibrary
Library 			Collections

*** Variables ***
${Names}
${Num}
${Excel_File_Path}   C:\\Python27\\ExcelRobotTest\\
${SheetName}         Graph Data
${NewSheetName}      NewSheet

*** Test Cases ***
Excel Test
	Get Values and Modify Spreadsheet
	Add Date To Sheet
	Perform Function and Change Date
	Create a New Excel
	Add a New Sheet
	Check New Sheet Values

*** Keywords ***
Get Values and Modify Spreadsheet
	Open Excel Current Directory   ExcelRobotTest.xls
	${Names}=      Get Sheet Names
	Set Suite Variable   ${Names}
	${Num}=        Get Number of Sheets
	Set Suite Variable   ${Num}
	${Col}=        Get Column Count    TestSheet1
	${Row}=        Get Row Count       TestSheet1
	${ColVal}=     Get Column Values   TestSheet2   1
	${RowVal}=     Get Row Values      TestSheet2   1
	${Sheet}=      Get Sheet Values    DataSheet
	Log   ${Sheet}
	${Workbook}=   Get Workbook Values   False
	Log   ${Workbook}
	${ByName}=     Read Cell Data By Name          GraphSheet   B2
	${ByCoords}=   Read Cell Data By Coordinates   GraphSheet   1   1
	Check Cell Type      TestSheet1   0   1
	Put Number To Cell   TestSheet1   1   1   90
	Put String To Cell   TestSheet3   1   1   yellow
	Put Date To Cell     TestSheet2   1   1   1.4.1989
	Put Date To Cell     TestSheet2   1   2   12.10.1991
	Save Excel           ${Excel_File_Path}TestExcel.xls

Add Date To Sheet
	Open Excel        ${Excel_File_Path}TestExcel.xls
	Add To Date       TestSheet2   1   2   5
    Check Cell Type   TestSheet2   1   2
	Save Excel        ${Excel_File_Path}NewDateExcel.xls

Perform Function and Change Date
	Open Excel           ${Excel_File_Path}NewDateExcel.xls
	Modify Cell With     TestSheet1   1   1   *   45
	Subtract From Date   TestSheet2   1   1   1
	Save Excel           ${Excel_File_Path}FunctionExcel.xls

Create a New Excel
	Create Excel Workbook    NewExcelSheet
	Save Excel               ${Excel_File_Path}NewExcel.xls

Add a New Sheet
	Open Excel      ${Excel_File_Path}FunctionExcel.xls
	Add New Sheet   ${NewSheetName}
	Save Excel      ${Excel_File_Path}NewSheetExcel.xls

Check New Sheet Values
	Open Excel     ${Excel_File_Path}NewSheetExcel.xls
	${NewNames}=   Get Sheet Names
	${NewNum}=     Get Number of Sheets
	Should Not Be Equal As Strings    ${Names}   ${NewNames}
	Should Not Be Equal As Integers   ${Num}     ${NewNum}
	${Sheet}=      Get Sheet Values   TestSheet3   False
	Log            ${Sheet}
	${stringList}=   Convert To String   ${Sheet}
	Should Contain   ${stringList}   yellow

注意事项

ExcelLibrary库使用注意事项：
使用关键字“添加新图纸”时，用户无法在当前打开的工作簿上的此关键字之前或之后执行任何功能。使用关键字“添加新工作表”时，其他关键字所做的更改将不会保存。他们必须先添加一张工作表，然后保存工作簿，然后再使用其他任何关键字。如果他们想在工作簿上使用其他任何关键字，则必须再次打开工作簿才能这样做。
我们无法使用xlsx文件，因为尚未在xlrd库中实现该文件。进一步的信息可以在这里找到，并讨论了这里。要解决此问题，用户可以保存带有xls扩展名的excel文件，这是Microsoft Excel 97-2003工作表。

after this keyword on the currently open workbook. The changes that other keywords make will not be saved when the keyword Add New Sheet is used. They must add a sheet then save the workbook before using any other keyword. If they want to use any other keywords on the workbbok they must open the workbook again to do so.
We cannot use xlsx files as this has not been implemented in the xlrd library. Further information can be found here and discussed here. To get round this issue, the user can save the excel files with the xls extension, this is a Microsoft Excel 97-2003 Worksheet.

1.只支持xls格式的Excel文档
2.Excel表格中的数值，代码读取后会显示为浮点型
如123，读取后显示为123.00，造成传参不一致
解决方案：设置单元格格式---->文本
3.读取每行或每列数据，存储到list变量中，是一个二维数组
如（A1，123）(B1,张三)
4.数组的位置编号都是从0开始的

疑难点：
1、python读取的excel文档数字带有小数点

办法1	办法2
数字和日期的单元格内容前加上一个英文的逗号	通过程序代码判断单元格内容的ctype来解决

办法2示例：

 for i in range(rows):
    if i==0:
        continue
    row_content = []
    for j in range(cols-1):
        ctype = sheet.cell(i, j).ctype  # 表格的数据类型
        cell = sheet.cell_value(i, j)

        if ctype == 2 and cell % 1 == 0.0:  # ctype为2且为浮点
            cell = int(cell)  # 浮点转成整型
        print(cell)

官方文档

一定要查阅官方文档，比在百度google一段乱查询好

http://navinet.github.io/robotframework-excellibrary/