The xlrd module implements reading the contents of excel files, and the xlwt module implements writing excel files.
Install
pip install xlrd pip install xlwt
xlrd module usage
The name of the excel document is Contact.xls and the content is as follows:
(1) Open the excel file and get all sheets
import xlrd
#Open Excel file to read data
data = xlrd.open_workbook('Contact.xls',formatting_info=True)
#Default formatting_info=False, please add the parameter formatting_info=True to save the format of the previous Excel data
sheet_name = data.sheet_names()
# Get all sheet names
print(sheet_name) # ['Bank 2', 'Bank 3']
(2) Get the sheet name based on the subscript
# Get the sheet name based on the subscript sheet2_name = data.sheet_names()[1]print(sheet2_name) # 'Bank 3'
(3) Get the sheet content based on the sheet index or name, and also get the sheet name, number of rows, and number of columns
# Get the sheet content based on the sheet index or name, and also get the sheet name, number of columns, and number of rows sheet2 =
data.sheet_by_index(1)print('sheet2 name: {}, sheet2 column number: {}, sheet2 row number: {}'.format(sheet2.name, sheet2.ncols, sheet2.nrows))
# sheet2 name: bank 3# sheet2 column number: 7# sheet2 row number: 5
# Get the sheet content based on the sheet name, and also get the sheet name, number of columns, and number of rows
sheet1 = data.sheet_by_name('Bank 2')print('sheet1 name: {}, sheet1 column number: {}, sheet1 row number: {}'.format(sheet1.name, sheet1.ncols, sheet1.nrows))
# sheet1 name: bank 2 # sheet1 column number: 8 # sheet1 row number: 6
(4) Get the value of the entire row and column based on the sheet name
# Get the value of the entire row and column based on the sheet name
sheet1 = data.sheet_by_name('Bank 2')
print(sheet1.row_values(3))
# ['', 'Zhang2', 'Development', 'IT Coding', 999.0, 133111.0, 41463.0, '[email protected]'] The date is 2013/7/7, but it is actually displayed as a floating point number 41463.0
print(sheet1.col_values(3))
# ['', 'Job Responsibilities', '', 'IT Coding', 'Network Maintenance', '']
(5) Get the content of the specified cell
# Get the contents of the specified cell print(sheet1.cell(1,0).value) # Contents in row 2 and column 1: organization name print(sheet1.cell_value(1,0)) # Contents in row 2 and column 1: organization name print(sheet1.row(1)[0].value) # Contents in row 2 and column 1: organization name
(6) Get the data type of cell content
# Get the data type of cell content print(sheet1.cell(1,0).ctype) # Contents in row 2 and column 1: The name of the institution is of type string print(sheet1.cell(3,4).ctype) # The content of row 4 and column 5: 999 is number type print(sheet1.cell(3,6).ctype) # The content of row 4 and column 7: 2013/7/8 is date type # illustrate: ctype: 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error
(7) How to obtain unit content as date type
Use xlrd’s xldate_as_tuple to process into date format
from datetime import datetime,date
if sheet1.cell(3,6).ctype == 3 : print(sheet1.cell(3, 6).value) # 41463.0
date_value = xlrd.xldate_as_tuple(sheet1.cell(3, 6).value, data.datemode) print(date_value) # (2013, 7, 8, 0, 0, 0)
print(date(*date_value[:3])) # 2013-07-08
print(date(*date_value[:3]).strftime('%Y/%m/%d')) # 2013/07/08
(8) How to obtain the unit content as number (convert to integer type)
if sheet1.cell(3, 5).ctype == 2: print(sheet1.cell(3, 5).value) # 133111.0
num_value = int(sheet1.cell(3, 5).value) print(num_value) # 133111
(9) Get the contents of merged cells
Requires merged_cells attribute
# Here, you need to add a parameter when reading the file and set the formatting_info parameter to True. The default is False. Otherwise, the merged_cells attribute may be called to obtain a null value.<br>
# Pay attention to adding the parameter formatting_info=True. After modification, the format of the previous data can be saved.
data = xlrd.open_workbook('Contact.xls',formatting_info=True)
sheet1 = data.sheet_by_name('Bank 2')print(sheet1.merged_cells)
# [(0, 1, 0, 8), (2, 6, 0, 1)]<br># The meaning of the four parameters returned by merged_cells is: (row, row_range, col, col_range), where [row, row_range) includes row, # does not include row_range, the same is true for col, and the subscript starts from 0. #(0, 1, 0, 8) means merging 1 column to 8 columns (2, 6, 0, 1) means merging 3 rows to 6 rows<br># Get the contents of merged two cells respectively:
print(sheet1.cell(0,0).value)
# bank2
print(sheet1.cell_value(2, 0))
# bank2
Rule: Just get the low index of row and col returned by merge_cells!
It is more convenient to use the following method
merge_value = []for (row,row_range,col,col_range) in sheet1.merged_cells:
merge_value.append((row,col))
print(merge_value) # [(0, 0), (2, 0)]for v in merge_value: print(sheet1.cell(v[0], v[1]).value)# bank 2#
Common methods
1. import xlwt #Import module
2. workbook = xlwt.Workbook(encoding='utf-8') #Create workbook object
3. worksheet = workbook.add_sheet('sheet1') #Create a worksheet
4. worksheet.write(0, 0, 'hello') #Write content into the table
5. worksheet.write_merge(0, 0, 0, 2, 'test')#Merge cells
6. worksheet.col(0)=6000#Set the width of the column
7. worksheet.row(0).height = 1000#Set cell height
8. workbook.save('hello.xls') #Save the table as hello.xls
Practical application
import xlwtfrom datetime import datetime,date
def set_style(name, height, bold=False, format_str=''):
style = xlwt.XFStyle() # Initialize style
font = xlwt.Font() # Create font for style
font.name = name # 'Times New Roman'
font.bold = bold
font.height = height
borders= xlwt.Borders() # Create borders for the style
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 style
wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet') # Add sheetws.col(0).width = 200*30 # Set the width of the first column ws.write(0, 0, 1234.56,set_style('Times New Roman' ,220,bold=True,format_str='#,##0.00'))
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: color 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['red']#Background color styleOK.pattern = pattern
ws.write(2, 0, 1,style=styleOK)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3 + B3"))
wb.save('example.xls') # Save xls
contact form
picture
import xlwtfrom datetime import datetime, date
def set_style(name, height, bold=False, format_str='',align='center'):
style = xlwt.XFStyle() # Initialize style
font = xlwt.Font() # Create font for style
font.name = name # 'Times New Roman'
font.bold = bold
font.height = height
borders = xlwt.Borders() # Create borders for the style
borders.left = 2
borders.right = 2
borders.top = 0
borders.bottom = 2
alignment = xlwt.Alignment() # Set alignment
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', 'Telephone', 'Joining date', 'Mobile phone', 'Email']
col1 = ['King 1', 'King 2', 'King 3']
col2 = ['666', '777','888']
col3 = ['2014-08-09','2014-08-11','2015-08-09']# Write the first row of data ws.write_merge(
0,
0,
0, 6, 'Contact List',
set_style( 'Times New Roman', 320,
bold=True,
format_str='')) # Merge cells 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 borders for the style borders.left = 2borders.right = 2borders.top = 6borders.bottom = 2
font = xlwt.Font() # Create font for style font.name = 'Times New Roman' font.bold = True
font.height = 220
styleOK.pattern = pattern
styleOK.borders = borders
styleOK.font = font
# Write the second row 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 the data in the first column of rows 3-6 ws.write_merge(2, 2 + len(col1)-1,
0,
0, 'x institution',
set_style( 'Times New Roman', 320,
bold=True,
format_str='')) # Merge cells
# Write 1 column of data starting from row 3 for index, 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 starting from row 3 for index, 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 5 columns of data starting from row 3 for index, val in enumerate(col3):
ws.col(4).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,'Technical Department', style=styleOK)
ws.write(4, 5,'186777233', style=styleOK)
ws.write(4, 6,'[email protected]', style=styleOK)
wb.save('test.xls') # Save xls