Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "Python how to use xlrd and xlwt module to operate Excel table", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "Python how to use xlrd and xlwt module to operate Excel table" this article.

Brief introduction:

Xlrd and xlwt are the third-party libraries of python. The xlrd module reads the contents of the excel file, and the xlwt module realizes the writing of the excel file.

Installation: pip install xlrdpip install xlwt

Initial data:

ExcelLearn.xls

Personal Information form:

Name, age, address, Tom26CNJo27UKLily28USKim29JP

Class report sheet:

Exam date Class score 2020-1-1 10:20A1602021/1/2 10:40A2802022/1/3 10:50A390

Sheet3: empty

Xlrd uses:

Details: get where there is no value. Note that the index is out of bounds and an exception will be thrown.

1. Open the Excel file to read the data

2. Get the sheet name according to the subscript

3. Get the sheet content according to the sheet index or name, and get the sheet name, number of columns and number of rows.

4. Get the values of the whole row and column according to the sheet name

5. Get the contents of the specified cell

6. Get the data type of the cell content

7. How to get the content of a unit as a date type

8. How to get the content of the unit as number (converted to integer)

Import xlrd# opens the Excel file to read data data = xlrd.open_workbook ('excelLearn.xls') sheet_name = data.sheet_names () # get all sheet names print (sheet_name) # [' personal information table', 'class grade table' 'Sheet3'] # get sheet name according to subscript sheet2_name = data.sheet_names () [1] print (sheet2_name) # Class score sheet # get sheet content based on sheet index or name Also get the sheet name, number of columns, number of rows sheet1 = data.sheet_by_name ('personal information table') print ('sheet1 name: {}\ nsheet1 columns: {}\ nsheet1 rows: {}' .format (sheet1.name, sheet1.ncols) Sheet1.nrows) # sheet1 name: personal Information Table # sheet1 columns: number of sheet1 rows: 5print () sheet2 = data.sheet_by_index (1) print ('sheet2 name: {}\ nsheet2 columns: {}\ nsheet2 rows: {}' .format (sheet2.name, sheet2.ncols) Sheet2.nrows) # sheet2 name: class score sheet # sheet2 column number: "sheet2 rows:" get the values of the whole row and column based on the sheet name sheet1 = data.sheet_by_name ('personal information table') print (sheet1.row_values (0)) # first row: ['name', 'age', 'address'] print (sheet1.row_values (1)) # second line: ['Tom', 26.0' 'CN'] print (sheet1.col_values (1)) # second column: [' age', 26.0,27.0,28.0,29.0] print (sheet1.col_values (2)) # third column: ['address', 'CN',' UK', 'US',' JP'] # get the content print of the specified cell (sheet1.cell (0) 0) .value) # 1st row 1 column content: name print (sheet1.cell_value (1,1)) # 2nd row 2 column content: 26.0print (sheet1.row (2) [2] .value) # 3rd row 3 column content: UK# gets the data type of the cell content: description: ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorprint (sheet1.cell (0) 0) .ctype) # Row 1 column content: name is string type-> 1print (sheet1.cell (1,1) .ctype) # Row 2 column content: 26.0 is number type-> dateif sheet2.cell (1,0). Ctype = = 3: print (sheet2.cell (1,0) .value) # 43831.43090277778 date_value = xlrd.xldate_as_tuple (sheet2.cell (1) 0) .value, data.datemode) print (date_value) # (2020, 1, 1, 10, 20, 30) print (date (* date_value [: 3])) # 2020-01-01 print (date (* date_value [: 3]). Strftime ('% Y date_value% m date_value% d')) # 2020 Universe (convert to integer) if sheet1.cell (1) 1) .ctype = = 2: print (sheet1.cell (1,1) .value) # 26.0 num_value = int (sheet1.cell (1,1) .value) print (num_value) # 2 values IndexError: list index out of range# does not have the value of row 9 and column 9 Getting throws an exception. # print (sheet1.cell (8,8)) xlwt uses: import xlwtfrom datetime import datetimedef set_style (name, height, bold=False) Format_str=''): style = xlwt.XFStyle () # initialization style font = xlwt.Font () # create fonts for styles font.name = name # 'Times New Roman' font.bold = bold font.height = height borders = xlwt.Borders () # create borders borders.left = 6 borders.right = 6 borders.top = 6 borders.bottom = 6 style.font = font Style.borders = borders style.num_format_str = format_str return stylewb = xlwt.Workbook () ws = wb.add_sheet ('code generation sheet') # add sheetws.col (0). Width = 200 * 30 # set the width of the first column ws.write (0 0,11.22, set_style ('Times New Roman', 220,bold=True, format_str='#,##0.00') ws.write (0,1, "New text", set_style (' Times New Roman', 220,bold=True)) ws.write (1,0, datetime.now (), set_style ('Times New Roman', 220,bold=False, format_str='DD-MM-YYYY')) styleOK = xlwt.easyxf (' pattern: fore_colour light_blue) '' font: colour green, bold True ') pattern = xlwt.Pattern () # an instantiated style class pattern.pattern = xlwt.Pattern.SOLID_PATTERN # fixed style pattern.pattern_fore_colour = xlwt.Style.colour_map [' pink'] # background color styleOK.pattern = patternws.write (2,0,1, style=styleOK) ws.write (2,1,1) ws.write (2,2, xlwt.Formula ("A3+B3") wb.save ('write.xls') # Save xls

The Excel1 produced by the running result:

Xlwt uses sample 2:import xlwtfrom datetime import datetime, date def set_style (name, height, bold=False, format_str='') Align='center'): style = xlwt.XFStyle () # initialization style font = xlwt.Font () # create fonts for styles font.name = name # 'Times New Roman' font.bold = bold font.height = height borders = xlwt.Borders () # create borders borders.left = 2 borders.right = 2 borders.top = 0 borders.bottom = 2 alignment = xlwt. Alignment () # setting arrangement if align== 'center': alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER else: alignment.horz = xlwt.Alignment.HORZ_LEFT alignment.vert = xlwt.Alignment.VERT_BOTTOM style.font = font style.borders = borders style.num_format_str = format_str style.alignment = alignment return style wb = xlwt.Workbook ( ) ws = wb.add_sheet ('contact' Cell_overwrite_ok=True) # add sheetrows = ['organization name', 'name', 'department', 'phone', 'date of entry', 'mobile phone', 'mailbox'] col1 = ['Wang 1yi,' Wang 2yi, 'Wang 3'] col2 = [' 666', '777', '888'] col3 = ['2014-08-09' '2015-08-09'] # write the first row of data ws.write_merge (0,0,0,6, 'contact table', set_style ('Times New Roman', 320, bold=True) Format_str='') # merge cell styleOK = xlwt.easyxf () pattern = xlwt.Pattern () # an instantiated style class pattern.pattern = xlwt.Pattern.SOLID_PATTERN # fixed style pattern.pattern_fore_colour = xlwt.Style.colour_map ['yellow'] # background color borders = xlwt.Borders () # create a border for the style borders.left = 2borders.right = 2borders.top = 6borders.bottom = 2 font = xlwt.Font () # create a font for the style font.name = 'Times New Roman'font.bold = Truefont.height = 220 styleOK.pattern = patternstyleOK.borders = bordersstyleOK.font = font # write the second line of data for index Val in enumerate (rows): ws.col (index). Width = 150 * 30 # define column width ws.write (1, index, val, style=styleOK) # write row 3-6 first column data ws.write_merge (2,2 + len (col1)-1,0,0,'x mechanism', set_style ('Times New Roman', 320, bold=True) Format_str='')) # merge cells # write 1 column of data for index from row 3, val in enumerate (col1): ws.col (1). Width = 150 * 30 # define column width ws.write (index+2, 1, val, style=set_style ('Times New Roman', 200, bold=False, format_str='',align='')) # write 4 columns of data for index from row 3 Val in enumerate (col2): ws.col (3). Width = 150 * 30 # define column width ws.write (index+2, 3, val, style=set_style ('Times New Roman', 200, bold=False, format_str='',align='')) # write five columns of data for index, val in enumerate (col3): ws.col (4) from row 3. Width = 150 * 30 # define column width ws.write (index+2, 4, val Style=set_style ('Times New Roman', 200, bold=False, format_str='',align='')) ws.write (4,2 write2.xls'' Technical Department', style=styleOK) ws.write (4,5 ws.write 186777233, style=styleOK) ws.write (4,6 penny 166.compose, style=styleOK) wb.save ('write2.xls') # Preservation xls

The Excel2 produced by the running result:

The above is all the contents of the article "how Python uses xlrd and xlwt modules to manipulate Excel tables". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.