一、Excel文件的读取与写入
1、读取操作步骤:
(1)、导入模板xlrd
(2)打开工作薄Book
(3)、指定工作薄中的表单Sheet
(4)、根据行列序号读取内容
In [10]:
#示例1:读取Stu_pack中的Excel文件内容school.xls,将结果保存以列表中 #(1)导入模块xlrd import xlrd #(2)打开工作薄Book wb = xlrd.open_workbook('../Stu_pack/school.xls') #(3)指定工作薄中的表单Sheet sheet = wb.sheet_by_index(0) #(4)根据行列序号读取内容 schools = [] #定义一个二维列表schools for row in range(sheet.nrows): school = [] #定义一维列表school for col in range(sheet.ncols): content = sheet.cell_value(row,col) school.append(content) schools.append(school) #只读取前10的信息: i = 1 for school in schools: if i<12: print(school) i = i+1
['招生单位代码', '招生单位名称', '所在省份', '是否985', '是否211', '是否自主划线', '学校类型'] ['10001', '北京大学', '北京市', '是', '是', '是', '综合类'] ['10002', '中国人民大学', '北京市', '是', '是', '是', '综合类'] ['10003', '清华大学', '北京市', '是', '是', '是', '理工类'] ['10004', '北京交通大学', '北京市', '否', '是', '否', '理工类'] ['10005', '北京工业大学', '北京市', '否', '是', '否', '理工类'] ['10006', '北京航空航天大学', '北京市', '是', '是', '是', '理工类'] ['10007', '北京理工大学', '北京市', '是', '是', '是', '理工类'] ['10008', '北京科技大学', '北京市', '否', '是', '否', '理工类'] ['10009', '北方工业大学', '北京市', '否', '否', '否', '理工类'] ['10010', '北京化工大学', '北京市', '否', '是', '否', '理工类']
2、Excel文件的写入操作步骤:
(1)导入模块:xlwt
(2)构造工作薄:Workbook
(3)为工作薄添加表单:Worksheet
(4)根据行列序号写入内容
(5)保存文件
示例2:将示例1读取的Excel文件内容,写入到另一个excel中并保存到R&Q_pic中, 对学校所在省份进行简单判断,第一行合并单元格将显示标题。
In [23]:
#(1)导入模块:xlwt import xlrd import xlwt #(2)读取文件内容 def read_excel(file_name): wb = xlrd.open_workbook(file_name) sheet = wb.sheet_by_index(0) schools = [] #定义一个二维列表schools for row in range(sheet.nrows): school = [] #定义一维列表school for col in range(sheet.ncols): content = sheet.cell_value(row,col) school.append(content) schools.append(school) return schools
In [24]:
#(3)写入文件内容 def write_excel(schools): #(2)构造工作薄:Workbook并创建对象wb wb = xlwt.Workbook(encoding = 'utf-8') #(3)为工作薄添加表单:Worksheet sheet = wb.add_sheet('上海市高校信息表') #(4)根据行列序号写入内容 sheet.write_merge(0,0,0,6,'上海市高校信息表') #合并单元格写入表头 for col in range(7): #遍历写入工作表的列表名称(列表字段) sheet.write(1,col,schools[0][col]) row_num = 2 for school in schools: if school[2]=='上海市': for col in range(7): sheet.write(row_num,col,school[col]) row_num = row_num+1 #将行自加1 (跳出内循环在自加1) wb.save('../R&Q_pic/上海市高校信息表.xls')
In [25]:
#调用函数 schools = read_excel('../Stu_pack/school.xls') write_excel(schools)
In [32]:
school = read_excel('../R&Q_pic/上海市高校信息表.xls') i = 1 for school in school: if i<13: print(school) i = i+1
['上海市高校信息表', '', '', '', '', '', ''] ['招生单位代码', '招生单位名称', '所在省份', '是否985', '是否211', '是否自主划线', '学校类型'] ['10246', '复旦大学', '上海市', '是', '是', '是', '综合类'] ['10247', '同济大学', '上海市', '是', '是', '是', '理工类'] ['10248', '上海交通大学', '上海市', '是', '是', '是', '综合类'] ['10251', '华东理工大学', '上海市', '否', '是', '否', '理工类'] ['10252', '上海理工大学', '上海市', '否', '否', '否', '理工类'] ['10254', '上海海事大学', '上海市', '否', '否', '否', '理工类'] ['10255', '东华大学', '上海市', '否', '是', '否', '理工类'] ['10256', '上海电力学院', '上海市', '否', '否', '否', '理工类'] ['10259', '上海应用技术大学', '上海市', '否', '否', '否', '理工类'] ['10264', '上海海洋大学', '上海市', '否', '否', '否', '农林类']
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [12]:
pip install wordcloud
Requirement already satisfied: wordcloud in c:\users\administrator\anaconda3\lib\site-packages (1.9.2) Requirement already satisfied: pillow in c:\users\administrator\anaconda3\lib\site-packages (from wordcloud) (9.2.0) Requirement already satisfied: matplotlib in c:\users\administrator\anaconda3\lib\site-packages (from wordcloud) (3.5.2) Requirement already satisfied: numpy>=1.6.1 in c:\users\administrator\anaconda3\lib\site-packages (from wordcloud) (1.21.5) Requirement already satisfied: cycler>=0.10 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (0.11.0) Requirement already satisfied: packaging>=20.0 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (21.3) Requirement already satisfied: kiwisolver>=1.0.1 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (1.4.2) Requirement already satisfied: pyparsing>=2.2.1 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (3.0.9) Requirement already satisfied: python-dateutil>=2.7 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (2.8.2) Requirement already satisfied: fonttools>=4.22.0 in c:\users\administrator\anaconda3\lib\site-packages (from matplotlib->wordcloud) (4.25.0) Requirement already satisfied: six>=1.5 in c:\users\administrator\anaconda3\lib\site-packages (from python-dateutil>=2.7->matplotlib->wordcloud) (1.16.0) Note: you may need to restart the kernel to use updated packages.
In [13]:
pip show wordcloud
Name: wordcloud Version: 1.9.2 Summary: A little word cloud generator Home-page: https://github.com/amueller/word_cloud Author: Andreas Mueller Author-email: t3kcit+wordcloud@gmail.com License: MIT Location: c:\users\administrator\anaconda3\lib\site-packages Requires: matplotlib, numpy, pillow Required-by: Note: you may need to restart the kernel to use updated packages.
In [ ]: