Read xsl format file - source code
import xlrd
# Read the working object in the xls filewb = xlrd.open_workbook('Sample Files/xxx School of Physics and Information Technology.xls')
print(wb)
# Get all worksheet namessheet_names = wb.sheet_names()
# print(sheet_names)
# Select the specific worksheet object to readsheet = wb.sheet_by_name(sheet_names[0])
# print(sheet)
# View the number of rows and columns of the worksheet# print(,) # 329 6
# Get data for each row and each column in a worksheet through a loopfor row in range():
for col in range():
# print(row,col)
# Get data in specific cell format through the value attribute in the cell object value = (row, col).value
# print(value)
'''
# Format the data outside the first line if row > 0:
# Convert the data in the first column into the format of year, month and day if col == 0:
value = xlrd.xldate_as_tuple(value,0)
# print(value)
# :>2d means that the month and date retain two digits value = f'{value[0]}Year{value[1]:>02d}moon{value[2]:>02d}day'
# print(value)
else:
# Keep two decimal places except the data in the first column value = f'{value:.2f}'
print(value,end='\t')
print() # Implement the effect of line breaking'''Read xsl format file - source code analysis
This code uses the xlrd library to read and process an Excel file called xxx School of Physics and Information Technology.xls. The following is a detailed analysis of the code:
1. Import the library
import xlrd
xlrd is a Python library for reading Excel files.
2. Open the workbook
wb = xlrd.open_workbook('Sample Files/xxx School of Physics and Information Technology.xls')
print(wb)
The xlrd.open_workbook function is used to open an Excel file and return a workbook object wb.
print(wb) prints the workbook object, which usually displays the file path and some metadata.
3. Get all worksheet names
sheet_names = wb.sheet_names() # print(sheet_names)
wb.sheet_names() Returns a list containing all worksheet names.
4. Select the specific worksheet object to read
sheet = wb.sheet_by_name(sheet_names[0]) # print(sheet)
wb.sheet_by_name(sheet_names[0]) Get the first sheet object sheet according to the sheet name.
5. View the number of rows and columns of the worksheet
# print(, ) # 329 6
Returns the number of rows in the worksheet.
Returns the number of columns in the worksheet.
6. Get data for each row and each column in the worksheet through a loop
for row in range():
for col in range():
value = (row, col).value
# Format the data outside the first line if row > 0:
# Convert the data in the first column into the format of year, month and day if col == 0:
value = xlrd.xldate_as_tuple(value, 0)
value = f'{value[0]}Year{value[1]:>02d}moon{value[2]:>02d}day'
else:
# Keep two decimal places except the data in the first column value = f'{value:.2f}'
print(value, end='\t')
print() # Implement the effect of line breakingOuter loop for row in range() traverses each row.
The inner loop for col in range() traverses each column.
(row, col).value Gets the value of the specified cell.
If the current row is not the first row (i.e. row > 0), the data is formatted:
- If the current column is the first column (i.e. col == 0), the date is formatted as year, month, and day.
- Otherwise, keep the value in two decimal places.
print(value, end='\t') prints the formatted value and separates it with the tab character \t.
print() prints an empty line to achieve the effect of a new line.
summary
The main function of this code is to read an Excel file and format the data in it:
Format the date in the first column into the form of year, month, and day.
Keep other values two decimal places.
Use tabs to separate the data for each column and wrap the line after each row is finished.
In this way, the code can effectively process and display data in Excel files.
Write data to xsl file - source code
import xlwt
import random
# Create a workbook objectwb = ()
# Create a worksheet objectsheet = wb.add_sheet('Class 1 and 3')
# Define student namesstudents = ['Zhang San','Li Si','Wang Wu','Lill','Flying','Runtu']
# Generate Chinese, mathematics and English scores through list generationscores = [[(50,151) for i in range(3)] for j in range(6)]
# print(scores)
# Create header datatitles = ['Name','Chinese','math','English']
for index,title in enumerate(titles):
# print(index,title)
# Add data to sheet worksheet # The first parameter represents the working row The second parameter represents the column The third parameter represents the specific content (0,index,title)
# Write the student's name and scores into the file in turnfor row in range(len(scores)):
# print(row)
(row+1,0,students[row]) # Write relevant data from the second line for col in range(len(scores[row])):
(row+1,col+1,scores[row][col])
# Write data to workbook('Sample Files/Exam Scores.xls')Write data to xsl file - source code analysis
This code uses the xlwt library to create an Excel file and writes the student's name and randomly generated Chinese, math, and English scores. The following is a detailed analysis of the code:
1. Import the library
import xlwt import random
xlwt is a Python library for creating and writing Excel files.
The random library is used to generate random numbers.
2. Create a workbook object
wb = ()
() Create a new workbook object wb.
3. Create a worksheet object
sheet = wb.add_sheet('Class 1 and 3')
wb.add_sheet('Grade 1 and Class 3') Add a worksheet called 'Grade 1 and Class 3' to the workbook and return the sheet object sheet.
4. Define student names
students = ['Zhang San', 'Li Si', 'Wang Wu', 'Lill', 'Flying', 'Runtu']
The students list contains the names of six students.
5. Generate grade data
scores = [[(50, 151) for i in range(3)] for j in range(6)]
Use the list generation formula to generate a two-dimensional list scores containing 6 students' Chinese, math, and English scores.
(50, 151) Generates a random integer between 50 and 150.
6. Create header data
titles = ['Name', 'Chinese', 'math', 'English']
The titles list contains header information.
7. Write header data
for index, title in enumerate(titles):
(0, index, title)
Use enumerate(titles) to traverse the title list, obtaining both the index index and the title title.
(0, index, title) Writes the header data to the first row of the worksheet.
8. Write student names and grades
for row in range(len(scores)):
(row + 1, 0, students[row]) # Write relevant data from the second line for col in range(len(scores[row])):
(row + 1, col + 1, scores[row][col])
The outer layer loops through each row of the scores list.
(row + 1, 0, students[row]) Write student names to the second row of the worksheet and the first column of each row after it.
The inner layer loops through each element of the scores[row] list.
(row + 1, col + 1, scores[row][col]) Write the score to the second row of the worksheet and the corresponding column of each row after it.
9. Save the workbook
('Sample Files/Exam Scores.xls')
('Sample File/Exam Score.xls') Save the workbook as a file named 'Exam Score.xls'.
summary
The main function of this code is to create an Excel file and write the student's name and randomly generated Chinese, math, and English scores. By using the xlwt library, the code can easily create and manipulate Excel files, realizing the storage and management of data.
Read xslx format file - source code
import openpyxl
# Load the workbookwb = openpyxl.load_workbook(r'Sample File\xx Master Information.xlsx')
# Get the worksheet nameprint() # ['Sheet1', 'Sheet2', 'Sheet3']
# Get the worksheet to operatesheet = [0]
# Get the range of cells in the worksheet# print() # A1:E64814
# Get the number of rows and columns# print(sheet.max_row,sheet.max_column) # 64814 5
# Get the value of the specified cell# print((3,3).value)
# Get the values of multiple cells# print(sheet['A2:C5'])
# Get all the data in the cellfor row in range(2,69):
for col in "ABCDE":
value = sheet[f"{col}{row}"].value
print(value,end='\t')
print() # Line breakRead xslx format file - source code analysis
This code uses the openpyxl library to load and operate an Excel file named xx master's information.xlsx. The following is a detailed analysis of the code:
1. Import the library
import openpyxl
openpyxl is a Python library for reading and writing Excel files.
2. Load the workbook
wb = openpyxl.load_workbook(r'Sample File\Tsinghua Master Information.xlsx')
openpyxl.load_workbook(r's example file\xx master's information.xlsx') Loads the specified Excel file and returns a workbook object wb.
3. Get the worksheet name
print() # ['Sheet1', 'Sheet2', 'Sheet3']
Returns a list containing all worksheet names.
4. Get the worksheet to operate
sheet = [0]
[0] Gets the first sheet object sheet in the workbook.
5. Get the range of cells in the worksheet
print() # A1:E64814
Returns the range of cells in the worksheet, for example A1:E64814.
6. Get the number of rows and columns
print(sheet.max_row,sheet.max_column) # 64814 5
sheet.max_row Returns the maximum number of rows in the worksheet.
sheet.max_column Returns the maximum number of columns in the worksheet.
7. Get the value of the specified cell
print((3,3).value)
(3,3).value Gets the value of the cell in row 3, column 3.
8. Get the values of multiple cells
print(sheet['A2:C5'])
sheet['A2:C5'] Gets the values of all cells in the rectangular area from A2 to C5.
9. Get all the data in the cell
for row in range(2,69):
for col in "ABCDE":
value = sheet[f"{col}{row}"].value
print(value,end='\t')
print() # Line breakOuter loop for row in range(2,69) traversal from line 2 to line 68.
The inner loop for col in "ABCDE" traverses columns from A to E.
sheet[f"{col}{row}"].value Gets the value of the specified cell.
print(value,end='\t') prints the value of the cell and separates it with the tab character \t.
print() prints an empty line to achieve the effect of a new line.
summary
The main function of this code is to load an Excel file and read data from it. By using the openpyxl library, the code can easily access and manipulate cell data in Excel files, realizing the reading and processing of data.
Write data to xslx file - source code
import random
import openpyxl
# Create a workbookwb = ()
# Add worksheetsheet =
= 'Test score'
# Write data Define titletitles = ("Name", "Arrow", "shooting", "football")
# Write the title to the filefor col_index, title in enumerate(titles):
(1, col_index + 1, title)
# Define student namesstudents = ("echo", "Hour hand", "Little Dream", "Big Box", "Xiao Yu", "snowy", "Chenchen", "Boss", "Gscsed")
# Write name and grades into the filefor row_index, student in enumerate(students):
(row_index + 2, 1, student)
for col_index in range(2, 5):
(row_index + 2, col_index, (50, 101))
# Save data to workbook('Sample File\Test Score.xlsx')Write data to xslx file - source code analysis
This code uses the openpyxl library to create an Excel file and writes the student's name and randomly generated archery, shooting, and football scores. The following is a detailed analysis of the code:
1. Import the library
import random import openpyxl
The random library is used to generate random numbers.
openpyxl is a Python library for creating and writing Excel files.
2. Create a workbook
wb = ()
() Create a new workbook object wb.
3. Add worksheets
sheet = = 'Test score'
Gets the active worksheet object.
= 'Test Score' Set the title of the activity sheet to 'Test Score'.
4. Write data Define title
titles = ("Name", "Arrow", "shooting", "football")
The titles tuple contains the column title.
5. Write the title to the file
for col_index, title in enumerate(titles):
(1, col_index + 1, title)
Use enumerate(titles) to traverse the titles tuple, obtaining both the index col_index and the title title.
(1, col_index + 1, title) Writes the title to the first row of the worksheet.
6. Define student names
students = ("echo", "Hour hand", "Little Dream", "Big Box", "Xiao Yu", "snowy", "Chenchen", "Boss", "Gscsed")
The students tuple contains the student's name.
7. Write name and grades into the file
for row_index, student in enumerate(students):
(row_index + 2, 1, student)
for col_index in range(2, 5):
(row_index + 2, col_index, (50, 101))
The outer layer loops through each element of the students tuple.
(row_index + 2, 1, student) Writes student names to the second row of the worksheet and the first column of each row after it.
The inner layer circulates through the three columns of archery, shooting, and football.
(row_index + 2, col_index, (50, 101)) Writes the randomly generated scores to the second row of the worksheet and the corresponding columns of each row after it.
8. Save data to workbook
('Sample File\Test Score.xlsx')
('Sample File\Test Score.xlsx') Save the workbook as a file named 'Test Score.xlsx'.
summary
The main function of this code is to create an Excel file and write the student's name and randomly generated archery, shooting, soccer scores. By using the openpyxl library, the code can easily create and manipulate Excel files, realizing the storage and management of data.
This is the end of this article about operating xsl&xslx files based on Python. For more related contents of Python xsl&xslx files, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!