hello everyone I am Zhengyin
We usually operate it in our work and study Excel file format , Especially when there are a lot of data , It is very easy to make mistakes to identify the operation by manpower . I'll show you today Python To handle Excel file
tool :
python3.7
Pycharm
Excel
xlwt&xlrd
read Excel Document content :
There is a folder named “ Stock data .xlsx” of Excel file , You can operate it as follows .
import xlrd # use xlrd Modular open_workbook Function open assignment Excel File and obtain Book object （ workbook ） wb =
xlrd.open_workbook(' Stock data .xlsx') # adopt Book Object's sheet_names Method to get all the form names sheetname =
wb.sheet_names()[0] # Get by the specified form name Sheet object （ Worksheet ） sheet =
wb.sheet_by_name(sheetname) # adopt Sheet Object's nrows and ncols Property to get the number of rows and columns of the form
print(sheet.nrows, sheet.ncols) for row in range(sheet.nrows): for col in
range(sheet.ncols): # adopt Sheet Object's cell Method to get the specified Cell object （ Cell ） #
adopt Cell Object's value Property to get a value in a cell value = sheet.cell(row, col).value #
Perform data format processing for other rows except the first row if row > 0: #
Section 1 Columnar xldate Type is converted to tuple and then formatted as “ specific date ” Format of if col == 0: #
xldate_as_tuple The second argument to the function has only 0 and 1 Two values #
among 0 Represented by 1900-01-01 Base date ,1 Represented by 1904-01-01 Base date value =
xlrd.xldate_as_tuple(value, 0) value =
f'{value[0]} year {value[1]:>02d} month {value[2]:>02d} day ' #
For other columns number Type a floating-point number that retains two significant digits after the decimal point else: value =
f'{value:.2f}' print(value, end='\t') print() # Gets the data type of the last cell # 0
- Null value ,1 - character string ,2 - number ,3 - date ,4 - Boolean ,5 - error last_cell_type =
sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1) print(last_cell_type) #
Get the value of the first row （ list ） print(sheet.row_values(0)) # Gets the data of the specified row and column range （ list ） #
The first parameter represents the row index , The second and third parameters represent the beginning of the column （ contain ） And end （ Excluding ） Indexes print(sheet.row_slice(3, 0, 5))
write in Excel Document content
Excel File writing can be done through xlwt Modular Workbook Class to create a workbook object , By workbook object add_sheet Method to add a worksheet , Through the
write Method to write data to a specified cell , Finally, the save Method to write the workbook to the specified file or memory . The following code operates the writing of a student transcript .
import random import xlwt student_names = [' Guan Yu ', ' Zhang Fei ', ' Zhao Yun ', ' ma chao ', ' Huangzhong ']
scores = [[random.randint(40, 100) for _ in range(3)] for _ in range(5)] #
Create workbook object （Workbook） wb = xlwt.Workbook() # Create sheet object （Worksheet） sheet =
wb.add_sheet(' Class 2, grade 1 ') # Add header data titles = (' full name ', ' language ', ' mathematics ', ' English ') for index,
title in enumerate(titles): sheet.write(0, index, title) # Write student names and test scores to cells
for row in range(len(scores)): sheet.write(row + 1, 0, student_names[row])
for col in range(len(scores[row])): sheet.write(row + 1, col + 1,
scores[row][col]) # preservation Excel workbook wb.save(' Examination transcript .xlsx')
Excel File style adjustment
You can also XFStyle
Object to set different styles for cells . Mainly including fonts （Font）, Alignment （Alignment）, frame （Border） And background （Background） Settings for .
header_style = xlwt.XFStyle() pattern = xlwt.Pattern() pattern.pattern =
xlwt.Pattern.SOLID_PATTERN # 0 - black ,1 - white ,2 - red ,3 - green ,4 - blue ,5 - yellow ,6 - Pink ,7
- Cyan pattern.pattern_fore_colour = 5 header_style.pattern = pattern titles =
(' full name ', ' language ', ' mathematics ', ' English ') for index, title in enumerate(titles):
sheet.write(0, index, title, header_style)
Set the position of the header
align = xlwt.Alignment() # Vertical alignment align.vert = xlwt.Alignment.VERT_CENTER #
Horizontal alignment align.horz = xlwt.Alignment.HORZ_CENTER header_style.alignment =
align 1
Set cell width and height
# Set row height to 40px sheet.row(0).set_style(xlwt.easyxf(f'font:height {20 * 40}'))
titles = (' full name ', ' language ', ' mathematics ', ' English ') for index, title in enumerate(titles): #
Set column width to 200px sheet.col(index).width = 20 * 200 # Set cell data and style
sheet.write(0, index, title, header_style)
Thank you for your support
Technology