本文介绍了如何在Windows环境下使用Anaconda、PyCharm进行Python编程，特别是针对Excel文件的自动化处理。文章提到了在读取xlsx文件时遇到的问题及解决方法，探讨了xlsxwriter和openpyxl库的优缺点，并展示了Excel的拓展应用，如整理文件名到Excel、生成二维码和使用Excel朗读英文单词。此外，还提到了环境配置中ConEmu和Clink的使用。
首先由于我之前已经安装好了
想要重新在Windows界面上配置一个学习环境来进行拓展学习
于是
打开conda prompt
查看已有环境
conda info -e
最后添加我需要的环境
conda create -n environment_name python=X.X
更多详细操作查看这篇文章
为了在windows系统更好的使用命令行
我下载了ConEmu和对应的Clink
感兴趣的伙伴可以搜索试试
excel相关代码操作
在我的新建的excel虚拟幻境里
首先下载xlrd这个库
但是有雷点！！
pip install xlrd
第一行代码就出了一下问题
raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+‘; not supported’)
xlrd.biffh.XLRDError: Excel xlsx file; not supported
解决方法就是
原因，xlrd为2.0.1版本，更新版本后，xlrd不支持xlsx格式数据的读取了。。。这。。。
参考这篇文章
如果下载了也没关系
先卸载就行
pip uninstall xlrd
再下载低版本
pip install xlrd==1.2.0
就成功读取xlsx文件啦
读取excel文件
import xlrd
import pyexcel_xls
#xlsx = xlrd.open_workbook('d:/7月下旬入库表.xlsx')
xlsx = xlrd.open_workbook('C:/Users/Administrator/Desktop/excelcode/7月下旬入库表.xlsx')
table = xlsx.sheet_by_index(0)
# 通过sheet名查找：xlsx.sheet_by_name("7月下旬入库表")
# 通过索引查找：xlsx.sheet_by_index(3)
print(table.cell_value(0, 0))
print(table.cell_value(1, 2))
print(table.cell(1, 2).value)
print(table.row(1)[2].value)
for i in range(0, xlsx.nsheets):
table = xlsx.sheet_by_index(i)
print(table.cell_value(0, 0))
# 获取所有sheet名字：xlsx.sheet_names()
# 获取sheet数量：xlsx.nsheets
for i in xlsx.sheet_names():
table = xlsx.sheet_by_name(i)
print(table.cell_value(3, 3))
写入excel文件
import xlwt
new_workbook = xlwt.Workbook()
worksheet = new_workbook.add_sheet('new_test')
worksheet.write(0, 0, 'test')
new_workbook.save('C:/Users/Administrator/Desktop/excelcode/test.xls')
excel模板格式读取
代码如下
from xlutils.copy import copy
import xlrd
import xlwt
tem_excel = xlrd.open_workbook('D:/日统计.xls', formatting_info=True)#格式信息一起打开，一定得保证文档后缀为xls，不能为xlsx，否则无法读取
tem_sheet = tem_excel.sheet_by_index(0)
new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
style = xlwt.XFStyle()
font = xlwt.Font()# 添加字体
font.name = '微软雅黑'
font.bold = True
font.height = 360# 字体号数18×20
style.font = font
borders = xlwt.Borders()#添加边框
borders.top = xlwt.Borders.THIN#细线框
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders
alignment = xlwt.Alignment()#对齐
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
"""
new_sheet.write(2, 1, 12)
new_sheet.write(3, 1, 18)
new_sheet.write(4, 1, 19)
new_sheet.write(5, 1, 15)
"""
#按照这个格式写入数据
new_sheet.write(2, 1, 12, style)
new_sheet.write(3, 1, 18, style)
new_sheet.write(4, 1, 19, style)
new_sheet.write(5, 1, 15, style)
new_excel.save('D:/填写.xls')
样式修改的更多
from xlutils.copy import copy
import xlrd
import xlwt
tem_excel = xlrd.open_workbook('C:/Users/Administrator/Desktop/excelcode/日统计.xls', formatting_info=True)
tem_sheet = tem_excel.sheet_by_index(0)
new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '宋体'
font.bold = True
font.height = 360
style.font = font
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
alignment.vert = xlwt.Alignment.VERT_TOP
style.alignment = alignment
style_red = xlwt.XFStyle()
font_red = xlwt.Font()
font_red.name = '宋体'
font_red.bold = True
font_red.height = 360
font_red.colour_index = 2
style_red.font = font_red
borders_red = xlwt.Borders()
borders_red.top = xlwt.Borders.THIN
borders_red.bottom = xlwt.Borders.THIN
borders_red.left = xlwt.Borders.THIN
borders_red.right = xlwt.Borders.THIN
style_red.borders = borders_red
alignment_red = xlwt.Alignment()
alignment_red.horz = xlwt.Alignment.HORZ_LEFT
alignment_red.vert = xlwt.Alignment.VERT_TOP
style_red.alignment = alignment_red
style_lishu18 = xlwt.XFStyle()
font_lishu18 = xlwt.Font()
font_lishu18.name = '隶书'
font_lishu18.bold = True
font_lishu18.height = 360
style_lishu18.font = font_lishu18
borders_lishu18 = xlwt.Borders()
borders_lishu18.top = xlwt.Borders.THIN
borders_lishu18.bottom = xlwt.Borders.THIN
borders_lishu18.left = xlwt.Borders.THIN
borders_lishu18.right = xlwt.Borders.THIN
style_lishu18.borders = borders_lishu18
alignment_lishu18 = xlwt.Alignment()
alignment_lishu18.horz = xlwt.Alignment.HORZ_CENTER
alignment_lishu18.vert = xlwt.Alignment.VERT_CENTER
style_lishu18.alignment = alignment_lishu18
style_lishu22 = xlwt.XFStyle()
font_lishu22 = xlwt.Font()
font_lishu22.name = '隶书'
font_lishu22.bold = True
font_lishu22.height = 440
style_lishu22.font = font_lishu22
borders_lishu22 = xlwt.Borders()
borders_lishu22.top = xlwt.Borders.THIN
borders_lishu22.bottom = xlwt.Borders.THIN
borders_lishu22.left = xlwt.Borders.THIN
borders_lishu22.right = xlwt.Borders.THIN
style_lishu22.borders = borders_lishu22
alignment_lishu22 = xlwt.Alignment()
alignment_lishu22.horz = xlwt.Alignment.HORZ_CENTER
alignment_lishu22.vert = xlwt.Alignment.VERT_CENTER
style_lishu22.alignment = alignment_lishu22
zhangsan_num = int(input('请输入张三粮配入库量：'))
lisi_num = int(input('请输入李四粮食入库量：'))
wangwu_num = int(input('请输入王五小麦入库量：'))
zhaoliu_num = int(input('请输入赵六麦子专营入库量：'))
stylex = lambda x: style_red if x > 10 else style
new_sheet.write(0, 0, tem_sheet.cell_value(0, 0), style_lishu22)
new_sheet.write(1, 0, tem_sheet.cell_value(1, 0), style_lishu18)
new_sheet.write(1, 1, tem_sheet.cell_value(1, 1), style_lishu18)
new_sheet.write(2, 0, tem_sheet.cell_value(2, 0), style_lishu18)
new_sheet.write(3, 0, tem_sheet.cell_value(3, 0), style_lishu18)
new_sheet.write(4, 0, tem_sheet.cell_value(4, 0), style_lishu18)
new_sheet.write(5, 0, tem_sheet.cell_value(5, 0), style_lishu18)
new_sheet.write(2, 1, zhangsan_num, stylex(zhangsan_num))
new_sheet.write(3, 1, lisi_num, stylex(lisi_num))
new_sheet.write(4, 1, wangwu_num, stylex(wangwu_num))
new_sheet.write(5, 1, zhaoliu_num, stylex(zhaoliu_num))
new_excel.save('C:/Users/Administrator/Desktop/excelcode/填写2.xls')
一些拓展库
xlsxwriter 不支持更换格式比如字体样式和边框等，但是可以写超过256列或者行的excel
openpyxl性能不是特别稳定，不能打开xls格式的文件
excel的拓展应用
把文件名快速整理到 Excel 中
这个代码比较简单
import os
import xlwt
file_dir='E:/'
#os.listdir(file_dir)
new_workbook = xlwt.Workbook()
worksheet = new_workbook.add_sheet('new_test')
n=0
for i in os.listdir(file_dir):
worksheet.write(n, 0, i)
n+=1
new_workbook.save('C:/Users/Administrator/Desktop/excelcode/Ediskfile.xls')
Excel 填充画二维码
首先需要用到from PIL import Image 这个库
但是使用pip install PIL来安装 却报错
结果显示：
Could not find a version that satisfies the requirement PIL (from versions: )No matching distribution found for PIL
错误原因：
现在已经用Pillow代替PIL，PIL较多用于2.7版本的Python中
解决方案：
pip install Pillow
引入时也有原来的：
import image变为：from PIL import Image
就可以成功引用啦！
# -*- coding: utf-8 -*-
"""
Created on Fri Aug 13 11:12:40 2022
@author:Laney_Midory
csdn:Laney_Midory
"""
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill,Color
from PIL import Image
workbook=Workbook()
worksheet=workbook.active#读取其中自带的工作表
im=Image.open('C:/Users/Administrator/Desktop/excelcode/excel-useful/2.png')
im_width=im.size[0]
im_height=im.size[1]
pix=im.load()#导出其中的每一个像素
for row in range(1,im_height):
for col in range(1,im_width):
cell=worksheet.cell(column=col,row=row)
pixpoint=pix[col-1,row-1]#读取每一个像素点
pixColor="FF%02X%02X%02X"%(pixpoint[0],pixpoint[1],pixpoint[2])
fill=PatternFill(patternType='solid',fgColor=Color(rgb=pixColor))
cell.fill=fill
worksheet.row_dimensions[row].height=6#设置单元格的高
for col in range(1,im_width):
worksheet.column_dimensions[get_column_letter(col)].width=1#设置单元格的宽
workbook.save('C:/Users/Administrator/Desktop/excelcode/scan.xlsx')
让 Excel 朗读英文单词
这里要用到一些新库
首先是安装bs4
Beautiful Soup 简称 BS4（其中 4 表示版本号）是一个 Python 第三方库，它可以从 HTML 或 XML 文档中快速地提取指定的数据。
这里还需要安装lxml解析
pip install lxml
详细的应用可以查看这篇博客
这个代码中包含Python中的 .join()用法，可以查看这篇文章
代码如下
# -*- coding: utf-8 -*-
"""
Created on Fri Aug 13 13:19:25 2022
@author:Laney_Midory
csdn:Laney_Midory
"""
from gettext import translation
import xlrd
from bs4 import BeautifulSoup
import requests
import time
import xlsxwriter as wx
import win32com.client
xlsx=xlrd.open_workbook('C:/Users/Administrator/Desktop/excelcode/excel-useful/words.xlsx')
table=xlsx.sheets()[0]
dst_wb=wx.Workbook('C:/Users/Administrator/Desktop/excelcode/excel-useful/words_trans.xlsx')
worksheet=dst_wb.add_worksheet()
for row in range(0,table.nrows):
time.sleep(1)#每一秒读取一个单词
word=table.cell(row,0).value
url='http://www.youdao.com/w/eng/'+word
web_data=requests.get(url).text
soup=BeautifulSoup(web_data,'lxml')
meaning=str(soup.select('#phrsListTab>div.trans-container>ul>li')).replace('<li>','').replace('</li>','')
translation=meaning[1:-1]
print(word)
worksheet.write(row,0,word)
worksheet.write(row,1,translation)
dst_wb.close()
speaker=win32com.client.Dispatch("SAPI.SpVoice") #连接到SAPI.SpVoice COM服务
xlsx=xlrd.open_workbook('C:/Users/Administrator/Desktop/excelcode/excel-useful/words_trans.xlsx')
table=xlsx.sheets()[0]
for row in range(0,table.nrows):
time.sleep(1)#每一秒读取一个单词
word=table.cell(row,0).value
word_segment=[]
for i in word:
word_segment.append(i)
word_segment.append('-')
word_2=''.join(word_segment)
speaker.Speak(str(table.cell(row,0).value))
speaker.Speak(str(word_2))
speaker.Speak(str(table.cell(row,0).value))
speaker.Speak(str(table.cell(row,1).value))
扫一扫
413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
