exl办公自动化安装及实现

最新推荐文章于 2023-02-23 16:08:55 发布

huasan_2004

最新推荐文章于 2023-02-23 16:08:55 发布

阅读量300

点赞数

文章标签： python

本文链接：https://blog.csdn.net/huasan_2004/article/details/105507271

版权

本文介绍了如何使用Python的pip工具安装xlrd, xlwt和xlutils库，这些库用于读写Excel文件。在Python 2.7环境下，详细展示了安装过程，并提供了一个简单的Excel数据操作示例，涉及查找和更新价格信息。" 114611028,10546767,Java实现：求解二叉树第K层节点数,"['二叉树算法', 'Java编程', '数据结构']

摘要由CSDN通过智能技术生成

一、安装

C:\Users\Administrator>python

Python 2.7.17 (v2.7.17:c2f86d86e6, Oct 19 2019, 21:01:17) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

1、C:\Users\Administrator>python -m pip install --upgrade pip

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. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Collecting pip
Downloading https://files.pythonhosted.org/packages/54/0c/d01aa759fdc501a58f431eb594a17495f15b88da142ce14b5845662c13f3/pip-20.0.2-py2.py3-none-any.whl (1.4MB)
|████████████████████████████████| 1.4MB 449kB/s
Installing collected packages: pip
Found existing installation: pip 19.2.3
Uninstalling pip-19.2.3:
Successfully uninstalled pip-19.2.3
Successfully installed pip-20.0.2

2、C:\Users\Administrator>python -m pip install xlrd

3、C:\Users\Administrator>python -m pip install xlwt

4、C:\Users\Administrator>python -m pip install xlutils

DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Collecting xlutils
Downloading xlutils-2.0.0-py2.py3-none-any.whl (55 kB)
|████████████████████████████████| 55 kB 201 kB/s
Requirement already satisfied: xlwt>=0.7.4 in c:\python27\lib\site-packages (from xlutils) (1.3.0)
Requirement already satisfied: xlrd>=0.7.2 in c:\python27\lib\site-packages (from xlutils) (1.2.0)
Installing collected packages: xlutils
WARNING: The script margins.exe is installed in 'C:\Python27\Scripts' which is not on PATH.
Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed xlutils-2.0.0

二、实现

模拟从给客户报价，有总表一份，和待报价表，需要从总表中查询各项单价。

实现代码如下：

# -*- coding: utf-8 -*-
import json
import xlutils
import xlrd
from xlrd import open_workbook
from xlutils.copy import copy

rb = open_workbook('baojia.xls')
rc = open_workbook('c.xls')

#检查表单名字：
#rc.sheet_names()

#通过sheet_by_index获取的sheet没有write()方法，但有ncols，nrows等。
rbs = rb.sheet_by_index(0)
rcs = rc.sheet_by_index(0)

#找出总表价格列bi
for bi in range(rbs.ncols):
if rbs.cell(rowx=0,colx=bi).value==u'价格':
print bi
break
# print 在报价表的+bi+列

#找出待报表价格列ci和待报价行数cn
cn=rcs.nrows-1
for ci in range(rcs.ncols):
if rcs.cell(rowx=0,colx=ci).value==u'价格':

print ci
print cn
break

#递归查价并写入列表 bj[]：

bj = []
for rownum in range(1,rcs.nrows):
   cxn=rcs.cell(rowx=rownum,colx=0).value
#   print cxn
   for rownum2 in range(1,rbs.nrows):
       bjn=rbs.cell(rowx=rownum2,colx=0).value
#       print bjn
       if cxn==bjn:
       bj.append(rbs.cell(rowx=rownum2,colx=bi).value)

#打印bj[]
print(bj)

# 利用xlutils.copy函数，将xlrd.Book转为xlwt.Workbook，再用xlwt模块进行存储
w_rc = copy(rc)

w_rcs = w_rc.get_sheet(0)

for i in range(cn):
# print bj[i]
w_rcs.write(i+1,ci,bj[i])

w_rc.save('c.xls')

写在最后：其实也不算什么自动化，用xls表的函数们完全可以实现，用python有2点好处，1是比xls函数快；2是如果将总表做成在线报价系统，python比较好作而已。