游戏蛮牛学习群(纯技术交流,不闲聊):159852603
游戏蛮牛 手机端
开启辅助访问
 找回密码
 注册帐号

扫一扫,访问微社区

开发者专栏

关注:2332

当前位置:游戏蛮牛 技术专区 开发者专栏

__________________________________________________________________________________
开发者干货区版块规则:

  1、文章必须是图文形式。(至少2幅图)
      2、文章字数必须保持在1500字节以上。(编辑器右下角有字数检查)
      3、本版块只支持在游戏蛮牛原创首发,不支持转载。
      4、本版块回复不得无意义,如:顶、呵呵、不错......【真的会扣分的哦】
      5、......
__________________________________________________________________________________
查看: 110|回复: 0

[网虫虫] [Python]Python操作Excel之封装xlwt\xlrd API

[复制链接]  [移动端链接]
排名
1831
昨日变化
3

37

主题

177

帖子

1934

积分

Rank: 9Rank: 9Rank: 9

UID
178893
好友
14
蛮牛币
2835
威望
0
注册时间
2016-10-28
在线时间
764 小时
最后登录
2018-10-20

专栏作家

发表于 2018-10-9 17:19:55 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?注册帐号

x
这两天完成个需求,是使用了xlrd和xlwt这两个库
发现里面有些方法需要多次使用,由于重复用的内容太多,所以封装了这个API
API包含部分常用功能,后续使用过程中也会继续完善
上代码:
API
[Python] 纯文本查看 复制代码
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):
            tempDatas.append(sheet.cell_value(i, j))
    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

测试代码:
[Python] 纯文本查看 复制代码
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 发表过


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册帐号

本版积分规则

快速回复 返回顶部 返回列表