本文实例讲述了Python实现的Excel文件读写类。分享给大家供大家参考。具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
|
#coding=utf-8########################################################filename:ExcelRW.py#author:defias#date:2015-4-27#function:read or write excel file#######################################################import xlrdimport xlwtimport xlutils.copy import os.pathclass XlsEngine(): """ The XlsEngine is a class for excel operation Usage: xlseng = XlsEngine('filePath') """ def __init__(self,xlsname): """ define class variable """ self.xls_name = xlsname #file name self.xlrd_object = None #workbook object self.isopentrue = False #file open flag def open(self): """ open a xls file Usage: xlseng.open() """ try: self.xlrd_object = xlrd.open_workbook(self.xls_name) self.isopentrue = True print('[%s,%s].'%(self.isopentrue,self.xlrd_object)) except: self.isopentrue = False self.xlrd_object = None print('open %s failed.'%self.xls_name) def info(self): """ show xls file information Usage: xlseng.info() """ if self.isopentrue == True: for sheetname in self.xlrd_object.sheet_names(): worksheet = self.xlrd_object.sheet_by_name(sheetname) print('%s:(%d row,%d col).'%(sheetname,worksheet.nrows,worksheet.ncols)) else: print('file %s is not open.'%self.xls_name) def readcell(self,sheetname='sheet1',rown=0,coln=0): """ read file's a cell content Usage: xlseng.readcell('sheetname',rown,coln) """ try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print('%s is not exit.'%sheetname) return False worksheet = self.xlrd_object.sheet_by_name(sheetname) cell = worksheet.cell_value(rown,coln) print('[file:%s,sheet:%s,row:%s,col:%s]:%s.'%(self.xls_name,sheetname,rown,coln,cell)) else: print('file %s is not open.'%self.xls_name) except: print('readcell is false! please check sheetn rown and coln is right.') def readrow(self,sheetname='sheet1',rown=0): """ read file's a row content Usage: xlseng.readrow('sheetname',rown) """ try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print('%s is not exit.'%sheetname) return False worksheet = self.xlrd_object.sheet_by_name(sheetname) row = worksheet.row_values(rown) print('[file:%s,sheet:%s,row:%s]:%s.'%(self.xls_name,sheetname,rown,row)) else: print('file %s is not open.'%self.xls_name) except: print('readrow is false! please check sheetn rown is right.') def readcol(self,sheetname='sheet1',coln=0): """ read file's a col content Usage: xlseng.readcol('sheetname',coln) """ try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print('%s is not exit.'%sheetname) return False worksheet = self.xlrd_object.sheet_by_name(sheetname) col = worksheet.col_values(coln) print('[file:%s,sheet:%s,col:%s]:%s.'%(self.xls_name,sheetname,coln,col)) else: print('file %s is not open.'%self.xls_name) except: print('readcol is false! please check sheetn coln is right.') def writecell(self,value='',sheetn=0,rown=0,coln=0): """ write a cell to file,other cell is not change Usage: xlseng.writecell('str',sheetn,rown，coln) """ try: if self.isopentrue == True: xlrd_objectc = xlutils.copy.copy(self.xlrd_object) worksheet = xlrd_objectc.get_sheet(sheetn) worksheet.write(rown,coln,value) xlrd_objectc.save(self.xls_name) print('writecell value:%s to [sheet:%s,row:%s,col:%s] is ture.'%(value,sheetn,rown,coln)) else: print('file %s is not open.'%self.xls_name) except: print('writecell is false! please check.') def writerow(self,values='',sheetn=0,rown=0,coln=0): """ write a row to file,other row and cell is not change Usage: xlseng.writerow('str1,str2,str3...strn',sheetn,rown.coln) """ try: if self.isopentrue == True: xlrd_objectc = xlutils.copy.copy(self.xlrd_object) worksheet = xlrd_objectc.get_sheet(sheetn) values = values.split(',') for value in values: worksheet.write(rown,coln,value) coln += 1 xlrd_objectc.save(self.xls_name) print('writerow values:%s to [sheet:%s,row:%s,col:%s] is ture.'%(values,sheetn,rown,coln)) else: print('file %s is not open.'%self.xls_name) except: print('writerow is false! please check.') def writecol(self,values='',sheetn=0,rown=0,coln=0): """ write a col to file,other col and cell is not change Usage: xlseng.writecol('str1,str2,str3...',sheetn,rown.coln) """ try: if self.isopentrue == True: xlrd_objectc = xlutils.copy.copy(self.xlrd_object) worksheet = xlrd_objectc.get_sheet(sheetn) values = values.split(',') for value in values: worksheet.write(rown,coln,value) rown += 1 xlrd_objectc.save(self.xls_name) print('writecol values:%s to [sheet:%s,row:%s,col:%s] is ture.'%(values,sheetn,rown,coln)) else: print('file %s is not open.'%self.xls_name) except: print('writecol is false! please check.') def filecreate(self,sheetnames='sheet1'): """ create a empty xlsfile Usage: filecreate('sheetname1,sheetname2...') """ try: if os.path.isfile(self.xls_name): print('%s is exit.'%self.xls_name) return False workbook = xlwt.Workbook() sheetnames = sheetnames.split(',') for sheetname in sheetnames: workbook.add_sheet(sheetname,cell_overwrite_ok=True) workbook.save(self.xls_name) print('%s is created.'%self.xls_name) except: print('filerator is false! please check.') def addsheet(self,sheetnames='sheet1'): """ add sheets to a exit xlsfile Usage: addsheet('sheetname1,sheetname2...') """ try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() xlrd_objectc = xlutils.copy.copy(self.xlrd_object) sheetnames = sheetnames.split(',') for sheetname in sheetnames: if sheetname in worksheets: print('%s is exit.'%sheetname) return False for sheetname in sheetnames: xlrd_objectc.add_sheet(sheetname,cell_overwrite_ok=True) xlrd_objectc.save(self.xls_name) print('addsheet is ture.') else: print("file %s is not open \n"%self.xls_name) except: print('addsheet is false! please check.')""" def chgsheet(self,sheetn,values): def clear(self):"""if __name__ == '__main__': #初始化对象 xlseng = XlsEngine('E:\\Code\\Python\\test2.xls') #新建文件，可以指定要新建的sheet页面名称，默认值新建sheet1 #print("\nxlseng.filecreate():") #xlseng.filecreate('newesheet1,newesheet2,newesheet3') #打开文件 print("xlseng.open():") xlseng.open() #添加sheet页 print("\nxlseng.addsheet():") xlseng.addsheet('addsheet1,addsheet2,addsheet3') #输出文件信息 print("\nxlseng.info():") xlseng.info() #读取sheet1页第3行第3列单元格数据（默认读取sheet1页第1行第1列单元格数据） print("\nxlseng.readcell():") xlseng.readcell('sheet1',2,2) #读取sheet1页第2行的数据（默认读取sheet1页第1行的数据） print("\nxlseng.readrow():") xlseng.readrow('sheet1',1) #读取sheet1页第3列的数据（默认读取sheet1页第1列的数据） print("\nxlseng.readcol():") xlseng.readcol('sheet1',2) #向第一个sheet页的第2行第4列写字符串数据‘I am writecell writed'(默认向第一个sheet页的第1行第1列写空字符串) print("\nxlseng.writecell():") xlseng.writecell('I am writecell writed',0,1,3) #向第一个sheet页写一行数据，各列的值为‘rowstr1,rowstr2,rowstr3'，从第3行第4列开始写入(默认向第一个sheet页写一行数据，值为‘'，从第1行第1列开始写入) print("\nxlseng.writerow():") xlseng.writerow('rowstr1,rowstr2,rowstr3',0,2,3) #向第一个sheet页写一列数据，各行的值为‘colstr1,colstr2,colstr3，colstr4'，从第4行第4列开始写入(默认向第一个sheet页写一列数据，值为‘'，从第1行第1列开始写入) print("\nxlseng.writecol():") xlseng.writecol('colstr1,colstr2,colstr3,colstr4',0,3,3) |
希望本文所述对大家的Python程序设计有所帮助。
