python讀寫Excel檔案的函數--使用xlrd/xlwt，

python讀寫Excel檔案的函數--使用xlrd/xlwt，

python中讀取Excel的模組或者說工具有很多，如以下幾種：

Packages	文檔下載	說明
openpyxl	Download | Documentation | Bitbucket	The recommended package for reading and writing Excel 2010 files (ie: .xlsx)
xlsxwriter	Download | Documentation | GitHub	An alternative package for writing data, formatting information, in particular, charts in the Excel 2010 format (ie: .xlsx)
xlrd	Download | Documentation | GitHub	This package is for reading data and formatting information from older Excel files (ie: .xls)
xlwt	Download | Documentation | GitHub	This package is for writing data and formatting information to older Excel files (ie: .xls)
xlutils	Download | Documentation | GitHub	This package collects utilities that require both xlrd and xlwt, including the ability to copy and modify or filter existing excel files.
python-xlsx	Download | Documentation | GitHub	This package is for creating and modifying Microsoft Excel .xlsx files from Office 2007 and later.
pyExcelerator	Download | Sourceforge	Generating Excel 97+ files with Python 2.4+ (need decorators), importing Excel 95+ files

關於上面幾種工具的優缺點對比分析，可以參考博文《用Python讀寫Excel檔案》，文章有詳細的說明。

 

雖然上面比較推薦的工具是openpyxl，但是由於其不支援xls，還是決定使用xlrd/xlwt來實現Excel的匯入匯出。

在使用前，請確保已安裝xlrd/xlwt模組，可使用Pip進行安裝；另匯出資料到Excel有用到Django(輸出瀏覽器時)，可安裝引入模組或者注釋掉相關代碼。

1 import xlrd2 import xlwt3 from datetime import date,datetime4 from django.http import HttpResponse, HttpRequest

具體實現如下：

  1 '''  2 # 讀取Excel資料  3 #   4 # 參數:  5 #         file_name    : xls檔案，含路徑  6 #         col_list    : 讀取資料後對應的欄欄位，如: ['id' , 'name' , 'value']  7 # 返回: List  8 '''  9 def readExcel(file_name , col_list): 10     # 判斷檔案是否存在，以及是否以xls尾碼 11     if not os.path.isfile(file_name) or os.path.basename(file_name).split('.')[1] != 'xls': 12         return returnInfo(-1 , 'file is not valid') 13      14     try: 15         # 開啟Excel檔案 16         curBook = xlrd.open_workbook(file_name) 17          18         # 擷取Sheet表, Sheet索引起始為0. 19         sheet1 = curBook.sheet_by_index(0) 20          21         # 或者，通過Sheet名稱擷取相應的Sheet 22         #sheet1_name = curBook.sheet_names()[0] 23         #sheet1 = curBook.sheet_by_name(sheet1_name) 24              25         # 擷取Sheet行數 26         rowNum = sheet1.nrows 27         # 擷取Sheet列數 28         #colNum = sheet1.ncols 29         # 此處，以實際接受的欄位為準 30         colNum = len(col_list) 31          32         # 用於接收資料 33         dataList = [] 34          35         # 預設從第二行開始讀取，第一行為欄位標題 36         ''' 37             # 讀取儲存格的值 : A2 38             sheet1.cell(1,0).value 39             sheet1.cell_value(rowx=1, colx=0)     40             sheet1.row(1)[0].value.encode('utf-8') 41              42             # 儲存格的類型 43             # ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error 44             sheet1.cell(1,0).ctype 45         ''' 46          47         # 迴圈讀取行資料 48         for i in range(1 , rowNum): 49             curRow = {} 50             # 讀取行內各列 51             for j in range(colNum): 52                 #  53                 if sheet1.cell(i,j).ctype == 3: 54                     # 如果儲存格的資料為日期類型，讀取後得到是浮點數；此處進行格式化處理 55                     date_value = xlrd.xldate_as_tuple(sheet1.cell_value(i,j),curBook.datemode) 56                     curRow[col_list[j]] = date(*date_value[:3]).strftime('%Y-%m-%d') 57                 else: 58                     curRow[col_list[j]] = sheet1.cell(i,j).value 59             # 行資料儲存到list 60             dataList.append(curRow) 61      62     # 異常處理 63     except Exception as e: 64         print('Error:', e) 65         return returnInfo(-1 , 'file read failed') 66      67     return returnInfo(0 , 'success' , dataList) 68  69      70 ''' 71 # 寫入資料到Excel 72 #  73 # 參數: 74 #         dataList    : 資料列表，如[{'id':1,'name':'ice cream','value':66},...] 75 #         file_title    : 檔案標題 76 #         col_list    : 欄欄位及欄位標題，如: [['id','序號'],['name','名稱'],...] 77 #         isSave        : 是否儲存到指定路徑；否表示輸出到瀏覽器 78 #         savePath    : 儲存路徑 79 # 返回: Mixed 80 ''' 81 def writeExcel(dataList , file_title , col_list , isSave = False , savePath = ''): 82     # 添加尾碼，指定檔案的名稱 83     fileName = file_title + time.strftime("_%Y%m%d%H%M%S", time.localtime()) + '.xls' 84      85     try: 86         # 建立workbook對象 87         curBook = xlwt.Workbook() 88         # 設定編碼 89         curBook.encoding='gbk' 90         # 添加Sheet表；其中cell_overwrite_ok，表示是否可以覆蓋儲存格 91         sheet1 = curBook.add_sheet(u'sheet1',cell_overwrite_ok = True) 92          93         # 行數 94         rowNum = len(dataList) 95         # 列數 96         colNum = len(col_list) 97          98         # 第一行，合併儲存格，設定檔案標題 99         # write_merge(x, x + h, y, y + w, string, style)，x表示行，y表示列，h表示跨行個數，w表示跨列個數100         sheet1.write_merge(0 , 0 , 0 , colNum-1 , file_title , set_style('華文中宋',320))101         102         # 第二行，設定欄位標題103         colTitleStyle = set_style('華文宋體',240)104         for k in range(0,colNum):105             sheet1.write(1 , k , col_list[k][1] , colTitleStyle)106             107         # 第三行起，開始寫入資料108         for i in range(0,rowNum):109             for j in range(0,colNum):110                 sheet1.write(i+2 , j , dataList[i][col_list[j][0]])111         112         113         if isSave:114             # 如儲存xls到路徑115             full_filename = os.path.join(savePath , fileName)116             # 執行儲存117             curBook.save(full_filename)118             return returnInfo()119         else:120             # 否則輸出到瀏覽器121             response = HttpResponse(content_type='application/vnd.ms-excel;charset=utf-8;name="' + file_title + '.xls"')122             response['Content-Disposition'] = 'attachment; filename=' + fileName123             # 儲存返回124             curBook.save(response)125             return response126     # 異常處理127     except Exception as e:128         print('Error:', e)129         return returnInfo(-1 , 'data export failed')130     131     132     133 '''134 # 設定樣式    135 # 136 # 參數:137 #         font_name    : 字型138 #         font_height    : 字型大小，註：20 = 1pt139 #         font_bold    : 字型是否加粗140 #         border        : 是否設定邊框141 # 返回: Style142 '''    143 def set_style(font_name = 'Times New Roman' , font_height = 220 , font_bold = False , border = False):144     # 初始化Style145     style = xlwt.XFStyle() 146     147     # 設定字型樣式148     font = xlwt.Font() 149     font.name = font_name150     font.color_index = 4151     font.height = font_height # 152     font.bold = font_bold153     style.font = font154     155     # 設定邊框屬性156     if border:157         borders= xlwt.Borders()158         borders.left= 1159         borders.right= 1160         borders.top= 1161         borders.bottom= 1162         style.borders = borders163     164     # 置中對齊，'general': 0 , 'left': 1 , 'centre': 2 , 'right': 3, ...165     style.alignment.horz = 2166     # 水平對齊，HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, ...167     # 豎直對齊，VERT_TOP, VERT_CENTER, VERT_BOTTOM, ...168     #style.alignment.horz = xlwt.Alignment.HORZ_CENTER169     #style.alignment.vert = xlwt.Alignment.VERT_CENTER170     171     # 設定背景顏色172     #pattern = xlwt.Pattern()173     #pattern.pattern = xlwt.Pattern.SOLID_PATTERN174     #pattern.pattern_fore_colour = 5175     #style.pattern = pattern176     177     # 其他，可參見xlwt源碼178     179     # 或者使用easyxf180     #style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on' , num_format_str='#,##0.00')181     182     # 返回樣式183     return style