这两天完成个需求，是使用了xlrd和xlwt这两个库
发现里面有些方法需要多次使用，由于重复用的内容太多，所以封装了这个API
API包含部分常用功能，后续使用过程中也会继续完善
上代码：
API
|
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 243 244 |
import xlrd import xlwt from xlutils.copy import copy wbDict = {} # {"test1.xls":workbook1,"test2.xls":workbook2} wbCloneDict = {} # {"test1.xls":workbook1,"test2.xls":workbook2} sheetDict = {} # {"test1.xls":{"sheet1":sheetObj1,"sheet2":sheetObj2}} #************************************ io ************************************** def getWookbook(fileName, copyed = False): if (wbCloneDict.__contains__(fileName) == False or wbDict.__contains__(fileName) == False): try: wb = xlrd.open_workbook(fileName, formatting_info=True) wbDict[fileName] = wb wbCloneDict[fileName] = copy(wb) except: wb = xlwt.Workbook(encoding="utf-8", style_compression=2) wbDict[fileName] = wb wbCloneDict[fileName] = wb if(copyed): return wbCloneDict[fileName] else: return wbDict[fileName] def getSheet(fileName, sheetName, copyed = False): if(sheetDict.__contains__(fileName) == False): sheetDict[fileName] = {} if(sheetDict[fileName].__contains__(sheetName)): sheet = sheetDict[fileName][sheetName] else: wb = getWookbook(fileName, copyed) sheetNames = getSheetNames(fileName) if(sheetNames.__contains__(sheetName)): if(copyed): sheet = wb.get_sheet(sheetNames.index(sheetName)) else: sheet = wb.sheet_by_name(sheetName) else: sheet = wb.add_sheet(sheetName,cell_overwrite_ok=True) sheetDict[fileName][sheetName] = sheet return sheet def removeDictItem(fileName): wbDict.pop(fileName) wbCloneDict.pop(fileName) sheetDict.pop(fileName) def clearDict(): wbDict.clear() wbCloneDict.clear() sheetDict.clear() #fileName 相对路径文件名 #sheetName sheet页名称 #row 写入到行数 #value 数据内容 ["网虫虫", "18", "男"] #style 格式：{"pattern": Excel.getStyle_Pattern(2)} def writeRow(fileName, sheetName, row, values, style=None): sheet = getSheet(fileName, sheetName, True) if(style != None): createdStyle = createStyle(style) for i in range(0, len(values)): sheet.write(row, i, values[i], createdStyle) else: for i in range(0, len(values)): sheet.write(row, i, values[i]) print("写入成功 文件:%s Sheet页:%s 行:%d 值:%r" % (fileName, sheetName, row, values)) #fileName 相对路径文件名 #sheetName sheet页名称 #row 写入到行数 #column 写入到列数 #value 数据内容 "网虫虫" #style 格式：{"pattern": Excel.getStyle_Pattern(2)} def writeCell(fileName, sheetName, row, column, value, style=None): sheet = getSheet(fileName, sheetName, True) if(style != None): createdStyle = createStyle(style) sheet.write(row, column, value, createStyle(style)) else: sheet.write(row, column, value) print("写入成功 文件:%s Sheet页:%s 行:%d 值:%r" % (fileName, sheetName, row, value)) #fileName 相对路径文件名 #sheetName sheet页名称 #r1 开始行数 #r2 结束行数 #c1 开始列数 #c2 结束列数 #label 文本 #style 格式：{"pattern": Excel.getStyle_Pattern(2)} def writeMerge(fileName, sheetName, r1, r2, c1, c2, label="", style=None): sheet = getSheet(fileName, sheetName, True) if(style != None): sheet.write_merge(r1, r2, c1, c2, label, createStyle(style)) else: sheet.write_merge(r1, r2, c1, c2, label) print("合并单元格成功 文件:%s Sheet页:%s label:%s" % (fileName, sheetName, label)) #保存文件 #fileName 相对路径文件名 def saveFile(fileName = None): if(fileName == None): for key in wbCloneDict: if(wbCloneDict[key] != None): wbCloneDict[key].save(key) print("保存文件“%s”" %(key)) clearDict() else: if(wbCloneDict.__contains__(fileName) and wbCloneDict[fileName] != None): wbCloneDict[fileName].save(fileName) print("保存文件“%s”" %(fileName)) removeDictItem(fileName) #读取所有内容 #fileName 相对路径文件名 #sheetName sheet页名称 def readAll(fileName, sheetName): sheet = getSheet(fileName, sheetName) datas = [[]] for i in range(0, sheet.nrows): tempDatas = [] datas.append(tempDatas) for j in range(0, sheet.ncols): cellValue = sheet.cell_value(i, j) ctype = sheet.cell(i,j).ctype if ctype == 2 and cellValue % 1 == 0: # 解决整数变小数问题 cellValue = int(cellValue) tempDatas.append(cellValue) return datas #读取一行 #fileName 相对路径文件名 #sheetName sheet页名称 #row 第几行 def readRow(fileName, sheetName, row): try: sheet = getSheet(fileName, sheetName) return sheet.row_values(row) except: print("Exception!!! readRow", fileName, sheetDict, row) return None #读取一列 #fileName 相对路径文件名 #sheetName sheet页名称 #col 第几列 def readCol(fileName, sheetName, col): try: sheet = getSheet(fileName, sheetName) return sheet.col_values(col) except: print("Exception!!! readCol", fileName, sheetDict, col) return None #读取单元格 #fileName 相对路径文件名 #sheetName sheet页名称 #row 第几行 #col 第几列 def readCell(fileName, sheetName, row, col): try: sheet = getSheet(fileName, sheetName) return sheet.cell_value(row, col) except: print("Exception!!! readCell", fileName, sheetDict, row, col) return None #************************************ Common ************************************** # 获取表所有sheet页名称 def getSheetNames(fileName): wb = getWookbook(fileName) try: return wb.sheet_names() except: return [] # 设置行高 def setHeight(fileName, sheetName, row, height): if(sheetDict.__contains__(fileName) == False or sheetDict[fileName].__contains__(sheetName) == False): return False sheet = sheetDict[fileName][sheetName] tall_style = xlwt.easyxf('font:height '+str(height)+';') sheet.row(row).set_style(tall_style) # 设置列宽 def setWidth(fileName, sheetName, col, width): if(sheetDict.__contains__(fileName) == False or sheetDict[fileName].__contains__(sheetName) == False): return False sheet = sheetDict[fileName][sheetName] sheet.col(col).width = width #************************************ Style ************************************** def createStyle(parDict = None): style = xlwt.XFStyle() # Create the Pattern if(parDict != None): if(parDict.__contains__("alignment")): style.alignment = parDict["alignment"] if(parDict.__contains__("borders")): style.borders = parDict["borders"] if(parDict.__contains__("pattern")): style.pattern = parDict["pattern"] if(parDict.__contains__("font")): style.font = parDict["font"] return style # 对齐方式，默认居中对齐 def getStyle_Alignment(horz=xlwt.Alignment.HORZ_CENTER, vert=xlwt.Alignment.VERT_CENTER): alignment = xlwt.Alignment() # Create Alignment alignment.horz = horz # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.vert = vert # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED return alignment # 单元格背景颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... def getStyle_Pattern(colorIndex, patternType=xlwt.Pattern.SOLID_PATTERN): pattern = xlwt.Pattern() # Create the Pattern pattern.pattern = patternType # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 pattern.pattern_fore_colour = colorIndex return pattern # 单元格边框 def getStyle_Borders(): borders = xlwt.Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 borders.bottom_colour = 0x3A return borders # 文字样式 # 颜色 May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... # 加粗 # 倾斜 # 下划线 def getStyle_Font(colorIndex, bold=False, italic=False,underline=False): font = xlwt.Font() # 创建一个文本格式，包括字体、字号和颜色样式特性 font.name = u'微软雅黑' # 设置其字体为微软雅黑 font.colour_index = colorIndex # 设置其字体颜色 font.bold = bold font.italic = italic font.underline = underline return font |
测试代码：
|
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 |
import Excel import xlwt writeFileName = "网虫虫测试.xls" writeSheetName = "测试Sheet1" if __name__ == '__main__': # ********************************* 写入 *********************************** style = { "alignment": Excel.getStyle_Alignment(), # 对齐样式 "borders": Excel.getStyle_Borders(), # 边框样式 "pattern": Excel.getStyle_Pattern(2), # 背景样式 "font": Excel.getStyle_Font(1, True), # 文字样式 } Excel.writeRow(writeFileName, writeSheetName, 0, ["姓名", "年龄", "性别"], style) # 写入第1行 style = { "alignment": Excel.getStyle_Alignment(), "pattern": Excel.getStyle_Pattern(10, xlwt.Pattern.NO_PATTERN), "font": Excel.getStyle_Font(4, True, True, True), } Excel.writeRow(writeFileName, writeSheetName, 1, ["网虫虫", "18", "男"], style) # 写入第2行 style = { "alignment":Excel.getStyle_Alignment(), } Excel.writeCell(writeFileName, writeSheetName, 6, 5, "网虫虫", style) # 写入第7行6列 style = { "pattern": Excel.getStyle_Pattern(5), } Excel.writeMerge(writeFileName, writeSheetName, 3, 4, 0, 2, "合并了吗", style) # 合并单元格 Excel.setWidth(writeFileName, writeSheetName, 1, 5000) # 设置第2列宽度 Excel.setHeight(writeFileName, writeSheetName, 0, 640) # 设置第1行高度 Excel.saveFile(writeFileName) # 保存Excel # ********************************* 读取 *********************************** print(Excel.readRow(writeFileName, writeSheetName, 1)) # 读取第2行 print(Excel.readCol(writeFileName, writeSheetName, 1)) # 读取第2列 print(Excel.readCell(writeFileName, writeSheetName, 0, 1)) # 读取第1行2列 |
生成Excel文件：
输出内容：
- 本文固定链接: http://www.u3d8.com/?p=1737
- 转载请注明: 网虫虫 在 u3d8.com 发表过
