代码改变世界

读写excel文件

2020-08-14 17:11  slayer2  阅读(123)  评论(0)    收藏  举报

1、读取excel

 1 import xlrd
 2 from datetime import date,datetime
 3 
 4 file = r'C:\Users\XLS\Desktop\sap.xlsx'
 5 
 6 def read_excel():
 7 
 8     wb = xlrd.open_workbook(filename=file)#打开文件
 9     print(wb.sheet_names())#获取所有表格名字
10 
11     sheet1 = wb.sheet_by_index(0)#通过索引获取表格
12     sheet2 = wb.sheet_by_name('Sheet2')#通过名字获取表格
13     print(sheet1,sheet2)
14     print(sheet1.name,sheet1.nrows,sheet1.ncols)
15 
16     
17 
18     rows = sheet1.row_values(2)#获取行内容
19     cols = sheet1.col_values(1)#获取列内容
20     print(rows)
21     string = "'" +rows[0] +"'" " '"+ rows[1]+"',"
22     print(string)
23     # print(cols)
24 
25     for i in rows:
26         print(i)
27 
28     # print(sheet1.cell(1,0).value)#获取表格里的内容，三种方式
29     # print(sheet1.cell_value(1,0))
30     # print(sheet1.row(1)[0].value)
31 
32 read_excel()
View Code

2、写入excel（只能保存xls，并且不能改变原文件）

 1 import xlwt
 2 #创建workbook和sheet对象
 3 workbook = xlwt.Workbook() #注意Workbook的开头W要大写
 4 sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
 5 sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)
 6 #向sheet页中写入数据
 7 sheet1.write(0,0,'this should overwrite1')
 8 sheet1.write(0,1,'aaaaaaaaaaaa')
 9 sheet2.write(0,0,'this should overwrite2')
10 sheet2.write(1,2,'bbbbbbbbbbbbb')
11 """
12 #-----------使用样式-----------------------------------
13 #初始化样式
14 style = xlwt.XFStyle()
15 #为样式创建字体
16 font = xlwt.Font()
17 font.name = 'Times New Roman'
18 font.bold = True
19 #设置样式的字体
20 style.font = font
21 #使用样式
22 sheet.write(0,1,'some bold Times text',style)
23 """
24 #保存该excel文件,有同名文件时直接覆盖
25 workbook.save(r'C:\Users\XLS\Desktop\test\111.xlsx')
26 print('创建excel文件完成！')
View Code

3、写入excel（可以保存成xlsx，可以覆盖原文件）

 1 from openpyxl import Workbook
 2 import re
 3 
 4 # 创建新的工作薄
 5 wb = Workbook()
 6 
 7 # grab the active worksheet
 8 ws = wb.active
 9 
10 string = '''
11 '1' 'SEL' '选择' 'X' 'X' '' '' '' 'X' ,
12                '2' 'ZNO' '对账号' '' '' '' '' '' '' ,
13                '3' 'LIFNR' '供应商编码' '' '' '' 'EKKO' 'LIFNR' '' ,
14                '3' 'NAME_ORG1' '供应商描述' '' '' '' '' '' '' ,
15                '4' 'KONNR' '采购合同' '' '' '' 'EKPO' 'KONNR' '' ,
16                '5' 'KTPNR' '合同行项目' '' '' '' 'EKPO' 'KTPNR' '' ,
17                '6' 'EBELN' '采购订单' '' '' '' 'EKPO' 'EBELN' '' ,
18                '7' 'EBELP' '订单行项目' '' '' '' 'EKPO' 'EBELP' '' ,
19                '8' 'NAME' '创建者名称' '' '' '' '' '' '' ,
20                '9' 'BUKRS' '公司代码' '' '' '' '' '' '' ,
21                '10' 'BUTXT' '公司描述' '' '' '' '' '' '' ,
22                '11' 'WERKS' '工厂' '' '' '' '' '' '' ,
23                '12' 'NAME1' '工厂描述' '' '' '' '' '' '' ,
24                '13' 'ZHTBH' '合同编号' '' '' '' '' '' '' ,
25                '14' 'ZHTMS' '合同描述' '' '' '' '' '' '' ,
26                '15' 'ZZFFS' '支付方式' '' '' '' '' '' '' ,
27                '16' 'MATNR' '物料号' '' '' '' 'EKPO' 'MATNR' '' ,
28                '17' 'TXZ01' '物料描述' '' '' '' '' '' '' ,
29                '18' 'BELNR' '入库凭证号' '' '' '' 'EKBE' 'BELNR' '' ,
30                '19' 'BUZEI' '凭证行项目' '' '' '' 'EKBE' 'BUZEI' '' ,
31                '20' 'BWART' '移动类型' '' '' '' 'EKBE' 'BWART' '' ,
32                '21' 'BTEXT' '移动类型描述' '' '' '' '' '' '' ,
33                '22' 'BUDAT' '入库日期' '' '' '' '' '' '' ,
34                '23' 'ZTERM' '收付条件代码' '' '' '' 'EKKO' 'ZTERM' '' ,
35                '24' 'ZBD1T' '折扣天数' '' '' '' 'EKKO' 'ZBD1T' '' ,
36                '25' 'MENGE01' '订单数量' '' '' '' '' '' '' ,
37                '26' 'MEINS' '单位' '' '' '' '' '' '' ,
38                '27' 'MENGE02' '未开票数量' '' '' '' '' '' '' ,
39                '28' 'WRBTR' '未开票净价' '' '' '' '' '' '' ,
40                '29' 'TEXT1' '税率(%)' '' '' '' '' '' '' ,
41                '30' 'WRBTR01' '税额' '' '' '' '' '' '' ,
42                '31' 'WRBTR02' '应开票含税金额' '' '' '' '' '' '' ,
43                '32' 'NETPR' '净单价' '' '' '' '' '' '' ,
44                '33' 'NETPR02' '含税单价' '' '' '' '' '' '' ,
45                '34' 'WAERS' '币种' '' '' '' '' '' '' ,
46                '35' 'KTEXT' '币种描述' '' '' '' '' '' '' .
47 '''
48 string = string.replace(',','')
49 string = string.replace('.','\n')
50 
51 list = re.findall("([^\n]*)[\n|.]",string)
52 
53 print(list)
54 # 使用列表填入（整行填写
55 # Data can be assigned directly to cellsrr
56 for i in list:
57     content = re.findall("'([^']*)'",i)
58     ws.append(content)
59 # print(content)
60 
61 # 使用单元格填入
62 # ws['A1'] = 42
63 #
64 # # Rows can also be appended
65 # ws.append([1, 2, 3])
66 
67 # Python types will automatically be converted
68 # import datetime
69 # ws['A2'] = datetime.datetime.now()
70 
71 # Save the file
72 wb.save(r'C:\Users\XLS\Desktop\test\114.xlsx')
View Code