Functions for reading and writing Excel files using python-using xlrd/xlwt,

Functions for reading and writing Excel files using python-using xlrd/xlwt,

There are many modules or tools for reading Excel in python, such as the following:

Packages	Document Download	Description
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 particle, 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

For the comparative analysis of the advantages and disadvantages of the above tools, refer to the blog "Read and Write Excel files with Python", the article has a detailed description.

 

Although openpyxl is the recommended tool above, it still decides to use xlrd/xlwt to import and export Excel because it does not support xls.

Before using the plug-in, make sure that the xlrd/xlwt module has been installed. You can use Pip to install the plug-in, and export data to Excel to use Django (when outputting the browser ), you can install the import module or comment out the relevant code.

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

The specific implementation is as follows:

1 '''2 # Read Excel Data 3 #4 # parameter: 5 # file_name: xls file, including Path 6 # col_list: column fields corresponding to the data read, such: ['id', 'name', 'value'] 7 # Return: List 8''' 9 def readExcel (file_name, col_list): 10 # determine whether the file exists, and whether xls suffix 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 # Open the Excel file 16 curBook = xlrd. open_workbook (file_name) 17 18 # obtain the Sheet table. The start of the Sheet index is 0. 19 sheet1 = curBook. sheet_by_index (0) 20 21 # Or, use the Sheet name to obtain the corresponding Sheet 22 # sheet1_name = curBook. sheet_names () [0] 23 # sheet1 = curBook. sheet_by_name (sheet1_name) 24 25 # obtain the number of Sheet rows 26 rowNum = sheet1.nrows 27 # obtain the number of Sheet columns 28 # colNum = sheet1.ncols 29 # Here, take the actually accepted field as the standard 30 colNum = len (col_list) 31 32 # used to receive data 33 dataList = [] 34 35 # Read from the second row by default, first behavior column title 36 ''' 37 # Read cell value: A2 38 sheet1.cell ). value 39 sheet1.cell _ value (rowx = 1, colx = 0) 40 sheet1.row (1) [0]. value. encode ('utf-8') 41 42 # cell type 43 # ctype: 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error 44 sheet1.cell (1, 0 ). ctype 45 ''' 46 47 # cyclically read row data 48 for I in range (1, rowNum ): 49 curRow ={} 50 # Read the 51 for j in range (colNum) of each column in the row: 52 #53 if sheet1.cell (I, j ). ctype = 3: 54 # If the data of a cell is of the date type, it is obtained as a floating point number. Here, format 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 # Save row data to list 60 dataList. append (curRow) 61 62 # Exception Handling 63 failed t 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 # Write Data to Excel 72 #73 # parameter: 74 # dataList: Data list, for example, [{'id': 1, 'name': 'ice cream', 'value': 66},...] 75 # file_title: file title 76 # col_list: column field and column title, for example, [['id', 'sequence number '], ['name ', 'name'],...] 77 # isSave: whether to save to the specified path; no indicates output to the browser 78 # savePath: Save path 79 # Return: Mixed 80 ''' 81 def writeExcel (dataList, file_title, col_list, isSave = False, savePath = ''): 82 # Add a suffix and specify the file name 83 fileName = file_title + time. strftime ("_ % Y % m % d % H % M % S", time. localtime () + '.xls '84 85 try: 86 # create a workbook object 87 curBook = xlwt. workbook () 88 # code 89 curBook. encoding = 'gbk' 90 # Add a Sheet table. cell_overwrite_ OK indicates whether the table_91 sheet1 = curBook can be overwritten. add_sheet (u 'sheet1', cell_overwrite_ OK = True) 92 93 # number of rows 94 rowNum = len (dataList) 95 # Number of columns 96 colNum = len (col_list) 97 98 # first line, merge cells and set the file title 99 # write_merge (x, x + h, y, y + w, string, style). x indicates rows, y indicates columns, and h indicates the number of cross-rows, w indicates the number of Cross columns 100 sheet1.write _ merge (0, 0, 0, colNum-1, file_title, set_style (' 文中', 320) 101 102 # second row, set the column title 103 colTitleStyle = set_style (' 文 ', 240) 104 for k in range (0, colNum): 105 sheet1.write (1, k, col_list [k] [1], colTitleStyle) 106 107 # starting from the third row, write data 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 # For example, save the xls file to the path 115 full_filename = OS. path. join (savePath, fileName) 116 # Save the 117 curBook. save (full_filename) 118 return returnInfo () 119 else: 120 # Otherwise, output to the browser 121 response = HttpResponse (content_type = 'application/vnd. ms-excel; charset = UTF-8; name = "'+ file_title + '.xls"') 122 response ['content-disposition'] = 'attachment; filename = '+ fileName123 # Save and return 124 curBook. save (response) 125 return response126 # Exception Handling 127 failed t Exception as e: 128 print ('error: ', e) 129 return returnInfo (-1, 'Data export failed ') 130 131 132 133 ''' 134 # Set style 135 #136 # parameter: 137 # font_name: font 138 # font_height: font_size, note: 20 = 1pt139 # font_bold: font bold 140 # border: set border 141 # Return: Style142 ''' 143 def set_style (font_name = 'times New Roman ', font_height = 220, font_bold = False, border = False): 144 # initialize Style145 style = xlwt. XFStyle () 146 147 # Set the font style 148 font = xlwt. font () 149 font. name = font_name150 font. color_indexing = 4151 font. height = font_height #152 font. bold = font_bold153 style. font = font154 155 # Set the border attribute 156 if border: 157 borders = xlwt. borders () 158 borders. left = 1159 borders. right = 1160 borders. top = 1161 borders. bottom = 1162 style. borders = borders163 164 # center alignment, 'Total': 0, 'left': 1, 'center': 2, 'right': 3 ,... 165 style. alignment. horz = 2166 # horizontal alignment, HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT ,... 167 # vertical alignment, VERT_TOP, VERT_CENTER, VERT_BOTTOM ,... 168 # style. alignment. horz = xlwt. alignment. HORZ_CENTER169 # style. alignment. vert = xlwt. alignment. VERT_CENTER170 171 # Set the background color 172 # pattern = xlwt. pattern () 173 # pattern. pattern = xlwt. pattern. SOLID_PATTERN174 # pattern. pattern_fore_colour = 5175 # style. pattern = pattern176 177 # others, see xlwt Source Code 178 179 # Or use easyxf180 # style0 = xlwt. easyxf ('font: name Times New Roman, color-index red, bold on ', num_format_str =' #,## 0.00 ') 181 182 # return style 183 return style