{"id":1737,"date":"2018-05-26T13:10:52","date_gmt":"2018-05-26T05:10:52","guid":{"rendered":"http:\/\/39.100.100.179\/?p=1737"},"modified":"2018-10-24T13:28:24","modified_gmt":"2018-10-24T05:28:24","slug":"python%e6%93%8d%e4%bd%9cexcel%e4%b9%8b%e5%b0%81%e8%a3%85xlwtxlrd-api","status":"publish","type":"post","link":"http:\/\/www.u3d8.com\/?p=1737","title":{"rendered":"Python\u64cd\u4f5cExcel\u4e4b\u5c01\u88c5xlwt\\xlrd API"},"content":{"rendered":"<p>\u8fd9\u4e24\u5929\u5b8c\u6210\u4e2a\u9700\u6c42\uff0c\u662f\u4f7f\u7528\u4e86xlrd\u548cxlwt\u8fd9\u4e24\u4e2a\u5e93<\/p>\n<p>\u53d1\u73b0\u91cc\u9762\u6709\u4e9b\u65b9\u6cd5\u9700\u8981\u591a\u6b21\u4f7f\u7528\uff0c\u7531\u4e8e\u91cd\u590d\u7528\u7684\u5185\u5bb9\u592a\u591a\uff0c\u6240\u4ee5\u5c01\u88c5\u4e86\u8fd9\u4e2aAPI<\/p>\n<p>API\u5305\u542b\u90e8\u5206\u5e38\u7528\u529f\u80fd\uff0c\u540e\u7eed\u4f7f\u7528\u8fc7\u7a0b\u4e2d\u4e5f\u4f1a\u7ee7\u7eed\u5b8c\u5584<\/p>\n<p>\u4e0a\u4ee3\u7801\uff1a<\/p>\n<p>API<\/p>\n<pre class=\"lang:python decode:true \">import xlrd\r\nimport xlwt\r\nfrom xlutils.copy import copy\r\n\r\nwbDict = {}   # {\"test1.xls\":workbook1,\"test2.xls\":workbook2}\r\nwbCloneDict = {}  # {\"test1.xls\":workbook1,\"test2.xls\":workbook2}\r\nsheetDict = {}  # {\"test1.xls\":{\"sheet1\":sheetObj1,\"sheet2\":sheetObj2}}\r\n\r\n\r\n#************************************ io **************************************\r\n\r\ndef getWookbook(fileName, copyed = False):\r\n    if (wbCloneDict.__contains__(fileName) == False or wbDict.__contains__(fileName) == False):\r\n        try:\r\n            wb = xlrd.open_workbook(fileName, formatting_info=True)\r\n            wbDict[fileName] = wb\r\n            wbCloneDict[fileName] = copy(wb)\r\n        except:\r\n            wb = xlwt.Workbook(encoding=\"utf-8\", style_compression=2)\r\n            wbDict[fileName] = wb\r\n            wbCloneDict[fileName] = wb\r\n    if(copyed):\r\n        return wbCloneDict[fileName]\r\n    else:\r\n        return wbDict[fileName]\r\n\r\ndef getSheet(fileName, sheetName, copyed = False):\r\n    if(sheetDict.__contains__(fileName) == False):\r\n        sheetDict[fileName] = {}\r\n    if(sheetDict[fileName].__contains__(sheetName)):\r\n        sheet = sheetDict[fileName][sheetName]\r\n    else:\r\n        wb = getWookbook(fileName, copyed)\r\n        sheetNames = getSheetNames(fileName)\r\n        if(sheetNames.__contains__(sheetName)):\r\n            if(copyed):\r\n                sheet = wb.get_sheet(sheetNames.index(sheetName))\r\n            else:\r\n                sheet = wb.sheet_by_name(sheetName)\r\n        else:\r\n            sheet = wb.add_sheet(sheetName,cell_overwrite_ok=True)\r\n        sheetDict[fileName][sheetName] = sheet\r\n    return sheet\r\n\r\ndef removeDictItem(fileName):\r\n    wbDict.pop(fileName)\r\n    wbCloneDict.pop(fileName)\r\n    sheetDict.pop(fileName)\r\n\r\ndef clearDict():\r\n    wbDict.clear()\r\n    wbCloneDict.clear()\r\n    sheetDict.clear()\r\n\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#row \u5199\u5165\u5230\u884c\u6570\r\n#value \u6570\u636e\u5185\u5bb9 [\"\u7f51\u866b\u866b\", \"18\", \"\u7537\"]\r\n#style \u683c\u5f0f\uff1a{\"pattern\": Excel.getStyle_Pattern(2)}\r\ndef writeRow(fileName, sheetName, row, values, style=None):\r\n    sheet = getSheet(fileName, sheetName, True)\r\n    if(style != None):\r\n        createdStyle = createStyle(style)\r\n        for i in range(0, len(values)):\r\n            sheet.write(row, i, values[i], createdStyle)\r\n    else:\r\n        for i in range(0, len(values)):\r\n            sheet.write(row, i, values[i])\r\n    print(\"\u5199\u5165\u6210\u529f \u6587\u4ef6:%s Sheet\u9875:%s \u884c:%d \u503c:%r\" % (fileName, sheetName, row, values))\r\n\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#row \u5199\u5165\u5230\u884c\u6570\r\n#column \u5199\u5165\u5230\u5217\u6570\r\n#value \u6570\u636e\u5185\u5bb9 \"\u7f51\u866b\u866b\"\r\n#style \u683c\u5f0f\uff1a{\"pattern\": Excel.getStyle_Pattern(2)}\r\ndef writeCell(fileName, sheetName, row, column, value, style=None):\r\n    sheet = getSheet(fileName, sheetName, True)\r\n    if(style != None):\r\n        createdStyle = createStyle(style)\r\n        sheet.write(row, column, value, createStyle(style))\r\n    else:\r\n        sheet.write(row, column, value)\r\n    print(\"\u5199\u5165\u6210\u529f \u6587\u4ef6:%s Sheet\u9875:%s \u884c:%d \u503c:%r\" % (fileName, sheetName, row, value))\r\n\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#r1 \u5f00\u59cb\u884c\u6570\r\n#r2 \u7ed3\u675f\u884c\u6570\r\n#c1 \u5f00\u59cb\u5217\u6570\r\n#c2 \u7ed3\u675f\u5217\u6570\r\n#label \u6587\u672c\r\n#style \u683c\u5f0f\uff1a{\"pattern\": Excel.getStyle_Pattern(2)}\r\ndef writeMerge(fileName, sheetName, r1, r2, c1, c2, label=\"\", style=None):\r\n    sheet = getSheet(fileName, sheetName, True)\r\n    if(style != None):\r\n        sheet.write_merge(r1, r2, c1, c2, label, createStyle(style))\r\n    else:\r\n        sheet.write_merge(r1, r2, c1, c2, label)\r\n    print(\"\u5408\u5e76\u5355\u5143\u683c\u6210\u529f \u6587\u4ef6:%s Sheet\u9875:%s label:%s\" % (fileName, sheetName, label))\r\n\r\n#\u4fdd\u5b58\u6587\u4ef6\r\n#fileName  \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\ndef saveFile(fileName = None):\r\n    if(fileName == None):\r\n        for key in wbCloneDict:\r\n            if(wbCloneDict[key] != None):\r\n                wbCloneDict[key].save(key)\r\n                print(\"\u4fdd\u5b58\u6587\u4ef6\u201c%s\u201d\" %(key))\r\n        clearDict()\r\n    else:\r\n        if(wbCloneDict.__contains__(fileName) and wbCloneDict[fileName] != None):\r\n            wbCloneDict[fileName].save(fileName)\r\n            print(\"\u4fdd\u5b58\u6587\u4ef6\u201c%s\u201d\" %(fileName))\r\n        removeDictItem(fileName)\r\n\r\n#\u8bfb\u53d6\u6240\u6709\u5185\u5bb9\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\ndef readAll(fileName, sheetName):\r\n    sheet = getSheet(fileName, sheetName)\r\n    datas = [[]]\r\n    for i in range(0, sheet.nrows):\r\n        tempDatas = []\r\n        datas.append(tempDatas)\r\n        for j in range(0, sheet.ncols):\r\n            cellValue = sheet.cell_value(i, j)\r\n            ctype = sheet.cell(i,j).ctype\r\n            if ctype == 2 and cellValue % 1 == 0:  # \u89e3\u51b3\u6574\u6570\u53d8\u5c0f\u6570\u95ee\u9898\r\n                cellValue = int(cellValue)\r\n            tempDatas.append(cellValue)\r\n    return datas\r\n\r\n#\u8bfb\u53d6\u4e00\u884c\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#row \u7b2c\u51e0\u884c\r\ndef readRow(fileName, sheetName, row):\r\n    try:\r\n        sheet = getSheet(fileName, sheetName)\r\n        return sheet.row_values(row)\r\n    except:\r\n        print(\"Exception!!! readRow\", fileName, sheetDict, row)\r\n        return None\r\n\r\n#\u8bfb\u53d6\u4e00\u5217\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#col \u7b2c\u51e0\u5217\r\ndef readCol(fileName, sheetName, col):\r\n    try:\r\n        sheet = getSheet(fileName, sheetName)\r\n        return sheet.col_values(col)\r\n    except:\r\n        print(\"Exception!!! readCol\", fileName, sheetDict, col)\r\n        return None\r\n\r\n#\u8bfb\u53d6\u5355\u5143\u683c\r\n#fileName \u76f8\u5bf9\u8def\u5f84\u6587\u4ef6\u540d\r\n#sheetName sheet\u9875\u540d\u79f0\r\n#row \u7b2c\u51e0\u884c\r\n#col \u7b2c\u51e0\u5217\r\ndef readCell(fileName, sheetName, row, col):\r\n    try:\r\n        sheet = getSheet(fileName, sheetName)\r\n        return sheet.cell_value(row, col)\r\n    except:\r\n        print(\"Exception!!! readCell\", fileName, sheetDict, row, col)\r\n        return None\r\n\r\n#************************************ Common **************************************\r\n# \u83b7\u53d6\u8868\u6240\u6709sheet\u9875\u540d\u79f0\r\ndef getSheetNames(fileName):\r\n    wb = getWookbook(fileName)\r\n    try:\r\n        return wb.sheet_names()\r\n    except:\r\n        return []\r\n\r\n# \u8bbe\u7f6e\u884c\u9ad8\r\ndef setHeight(fileName, sheetName, row, height):\r\n    if(sheetDict.__contains__(fileName) == False or sheetDict[fileName].__contains__(sheetName) == False): return False\r\n    sheet = sheetDict[fileName][sheetName]\r\n    tall_style = xlwt.easyxf('font:height '+str(height)+';')\r\n    sheet.row(row).set_style(tall_style)\r\n\r\n# \u8bbe\u7f6e\u5217\u5bbd\r\ndef setWidth(fileName, sheetName, col, width):\r\n    if(sheetDict.__contains__(fileName) == False or sheetDict[fileName].__contains__(sheetName) == False): return False\r\n    sheet = sheetDict[fileName][sheetName]\r\n    sheet.col(col).width = width\r\n\r\n#************************************ Style **************************************\r\n\r\ndef createStyle(parDict = None):\r\n    style = xlwt.XFStyle()  # Create the Pattern\r\n    if(parDict != None):\r\n        if(parDict.__contains__(\"alignment\")):\r\n            style.alignment = parDict[\"alignment\"]\r\n        if(parDict.__contains__(\"borders\")):\r\n            style.borders = parDict[\"borders\"]\r\n        if(parDict.__contains__(\"pattern\")):\r\n            style.pattern = parDict[\"pattern\"]\r\n        if(parDict.__contains__(\"font\")):\r\n            style.font = parDict[\"font\"]\r\n    return style\r\n\r\n# \u5bf9\u9f50\u65b9\u5f0f\uff0c\u9ed8\u8ba4\u5c45\u4e2d\u5bf9\u9f50\r\ndef getStyle_Alignment(horz=xlwt.Alignment.HORZ_CENTER, vert=xlwt.Alignment.VERT_CENTER):\r\n    alignment = xlwt.Alignment()  # Create Alignment\r\n    alignment.horz = horz  # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED\r\n    alignment.vert = vert  # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED\r\n    return alignment\r\n\r\n# \u5355\u5143\u683c\u80cc\u666f\u989c\u8272 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...\r\ndef getStyle_Pattern(colorIndex, patternType=xlwt.Pattern.SOLID_PATTERN):\r\n    pattern = xlwt.Pattern()  # Create the Pattern\r\n    pattern.pattern = patternType  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12\r\n    pattern.pattern_fore_colour = colorIndex\r\n    return pattern\r\n\r\n# \u5355\u5143\u683c\u8fb9\u6846\r\ndef getStyle_Borders():\r\n    borders = xlwt.Borders()\r\n    borders.left = 1\r\n    borders.right = 1\r\n    borders.top = 1\r\n    borders.bottom = 1\r\n    borders.bottom_colour = 0x3A\r\n    return borders\r\n\r\n# \u6587\u5b57\u6837\u5f0f\r\n# \u989c\u8272 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...\r\n# \u52a0\u7c97\r\n# \u503e\u659c\r\n# \u4e0b\u5212\u7ebf\r\ndef getStyle_Font(colorIndex, bold=False, italic=False,underline=False):\r\n    font = xlwt.Font()  # \u521b\u5efa\u4e00\u4e2a\u6587\u672c\u683c\u5f0f\uff0c\u5305\u62ec\u5b57\u4f53\u3001\u5b57\u53f7\u548c\u989c\u8272\u6837\u5f0f\u7279\u6027\r\n    font.name = u'\u5fae\u8f6f\u96c5\u9ed1'  # \u8bbe\u7f6e\u5176\u5b57\u4f53\u4e3a\u5fae\u8f6f\u96c5\u9ed1\r\n    font.colour_index = colorIndex  # \u8bbe\u7f6e\u5176\u5b57\u4f53\u989c\u8272\r\n    font.bold = bold\r\n    font.italic = italic\r\n    font.underline = underline\r\n    return font<\/pre>\n<p>\u6d4b\u8bd5\u4ee3\u7801\uff1a<\/p>\n<pre class=\"lang:python decode:true \">import Excel\r\nimport xlwt\r\n\r\nwriteFileName = \"\u7f51\u866b\u866b\u6d4b\u8bd5.xls\"\r\nwriteSheetName = \"\u6d4b\u8bd5Sheet1\"\r\n\r\nif __name__ == '__main__':\r\n    # ********************************* \u5199\u5165 ***********************************\r\n    style = {\r\n        \"alignment\": Excel.getStyle_Alignment(),  # \u5bf9\u9f50\u6837\u5f0f\r\n        \"borders\": Excel.getStyle_Borders(),  # \u8fb9\u6846\u6837\u5f0f\r\n        \"pattern\": Excel.getStyle_Pattern(2),  # \u80cc\u666f\u6837\u5f0f\r\n        \"font\": Excel.getStyle_Font(1, True),  # \u6587\u5b57\u6837\u5f0f\r\n    }\r\n    Excel.writeRow(writeFileName, writeSheetName, 0, [\"\u59d3\u540d\", \"\u5e74\u9f84\", \"\u6027\u522b\"], style)  # \u5199\u5165\u7b2c1\u884c\r\n\r\n    style = {\r\n        \"alignment\": Excel.getStyle_Alignment(),\r\n        \"pattern\": Excel.getStyle_Pattern(10, xlwt.Pattern.NO_PATTERN),\r\n        \"font\": Excel.getStyle_Font(4, True, True, True),\r\n    }\r\n    Excel.writeRow(writeFileName, writeSheetName, 1, [\"\u7f51\u866b\u866b\", \"18\", \"\u7537\"], style)  # \u5199\u5165\u7b2c2\u884c\r\n\r\n    style = {\r\n        \"alignment\":Excel.getStyle_Alignment(),\r\n    }\r\n    Excel.writeCell(writeFileName, writeSheetName, 6, 5, \"\u7f51\u866b\u866b\", style)   # \u5199\u5165\u7b2c7\u884c6\u5217\r\n\r\n    style = {\r\n        \"pattern\": Excel.getStyle_Pattern(5),\r\n    }\r\n    Excel.writeMerge(writeFileName, writeSheetName, 3, 4, 0, 2, \"\u5408\u5e76\u4e86\u5417\", style)  # \u5408\u5e76\u5355\u5143\u683c\r\n\r\n    Excel.setWidth(writeFileName, writeSheetName, 1, 5000)  # \u8bbe\u7f6e\u7b2c2\u5217\u5bbd\u5ea6\r\n    Excel.setHeight(writeFileName, writeSheetName, 0, 640)  # \u8bbe\u7f6e\u7b2c1\u884c\u9ad8\u5ea6\r\n    Excel.saveFile(writeFileName)  # \u4fdd\u5b58Excel\r\n\r\n    # ********************************* \u8bfb\u53d6 ***********************************\r\n    print(Excel.readRow(writeFileName, writeSheetName, 1))  # \u8bfb\u53d6\u7b2c2\u884c\r\n    print(Excel.readCol(writeFileName, writeSheetName, 1))  # \u8bfb\u53d6\u7b2c2\u5217\r\n    print(Excel.readCell(writeFileName, writeSheetName, 0, 1))  # \u8bfb\u53d6\u7b2c1\u884c2\u5217<\/pre>\n<p>\u751f\u6210Excel\u6587\u4ef6\uff1a<\/p>\n<p><img title=\"Python\u64cd\u4f5cExcel\u4e4b\u5c01\u88c5xlwt\\xlrd API - \u7b2c1\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" alt=\"Python\u64cd\u4f5cExcel\u4e4b\u5c01\u88c5xlwt\\xlrd API - \u7b2c1\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" loading=\"lazy\" class=\"size-full wp-image-1738 alignnone\" src=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130836.png\" width=\"460\" height=\"230\" srcset=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130836.png 460w, http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130836-300x150.png 300w\" sizes=\"(max-width: 460px) 100vw, 460px\" \/><\/p>\n<p>\u8f93\u51fa\u5185\u5bb9\uff1a<\/p>\n<p><img title=\"Python\u64cd\u4f5cExcel\u4e4b\u5c01\u88c5xlwt\\xlrd API - \u7b2c2\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" alt=\"Python\u64cd\u4f5cExcel\u4e4b\u5c01\u88c5xlwt\\xlrd API - \u7b2c2\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" loading=\"lazy\" class=\"size-full wp-image-1739 alignnone\" src=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130859.png\" width=\"576\" height=\"214\" srcset=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130859.png 576w, http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/05\/QQ\u622a\u56fe20180526130859-300x111.png 300w\" sizes=\"(max-width: 576px) 100vw, 576px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8fd9\u4e24\u5929\u5b8c\u6210\u4e2a\u9700\u6c42\uff0c\u662f\u4f7f\u7528\u4e86xlrd\u548cxlwt\u8fd9\u4e24\u4e2a\u5e93 \u53d1\u73b0\u91cc\u9762\u6709\u4e9b\u65b9\u6cd5\u9700\u8981\u591a\u6b21\u4f7f &hellip; <a href=\"http:\/\/www.u3d8.com\/?p=1737\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[308],"tags":[360,359],"_links":{"self":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1737"}],"collection":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1737"}],"version-history":[{"count":6,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1737\/revisions"}],"predecessor-version":[{"id":1827,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1737\/revisions\/1827"}],"wp:attachment":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1737"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}