Directory
Method 1: Read by line
Method 2: According to the one-to-one correspondence between table headers and table data
Method 3: Read by column
I talked about how to read the database data in Django and write it into excel. Today, let’s talk about how to read the data in excel (it can be written into the database after reading)?
The xlrd module needs to be installed before starting
pip install -i https://pypi.douban.com/simple xlrd
The latest version is installed by default, and it supports excel files in xls format. If it is an xlsx file format, you may need to install an old version of xlrd
Method 1: Read by line
import xlrd
class ReadExcel(View):
def post(self, request):
excel_file = request.FILES['excel_file'] # Get the uploaded Excel file
workbook = xlrd.open_workbook(file_contents=excel_file.read()) # Open Excel file
sheet_names = workbook.sheet_names() # Get the names of all worksheets
# Method 1: By row
data = [] # Store the data of all worksheets
for sheet_name in sheet_names:
sheet = workbook.sheet_by_name(sheet_name) # Get a specific worksheet
rows = []
for row_num in range(sheet.nrows): # Iterate each row of data
row_values = sheet.row_values(row_num) # Get the value of the current row
rows.append(row_values)
data.append({
'sheet_name': sheet_name,
'rows': rows,
})
return JsonResponse({"res": data})
Use the book_data.xls table file, the content is as follows
Output result:
{
"res": [
{
"sheet_name": "Book Sheet",
"rows": [
[
"Book Name",
"address",
"Era"
],
[
""Python + Cookbook" third edition Chinese v3.0.0.pdf",
"Tianjin",
"1999.11"
],
[
"Django 2.2 official document Chinese version.pdf",
"Dongcheng District, Beijing",
"1980.07"
],
[
"MySQL must know and know (text version).pdf",
"Drum Tower District, Nanjing",
"2020.08"
],
[
"PyCharm Knowledge Manual V1.0.pdf",
"Hefei City, Anhui",
"1987.12"
],
[
"Smooth Python.pdf",
"Nanjing Gaochun District",
"2000.01"
],
[
"In-depth understanding of Python Chinese version HD (Dive + into + Python)@www.java1234.com.pdf",
"LuoYang HeNan",
"2023.10"
]
]
},
{
"sheet_name": "Sheet2",
"rows": [
[
"Picture name",
"path"
],
[
"test",
"Test path"
]
]
},
{
"sheet_name": "Sheet3",
"rows": []
}
]
}
Method 2: According to the one-to-one correspondence between table header and table data
import xlrd
class ReadExcel(View):
def post(self, request):
excel_file = request.FILES['excel_file'] # Get the uploaded Excel file
workbook = xlrd.open_workbook(file_contents=excel_file.read()) # Open Excel file
sheet_names = workbook.sheet_names() # Get the names of all worksheets
# Method 2: According to the one-to-one correspondence between table header and table data
data = [] # Store the data of all worksheets
for sheet_name in sheet_names:
sheet = workbook.sheet_by_name(sheet_name) # Get a specific worksheet
if sheet.nrows != 0:
rows = []
first_row = sheet.row_values(0) # Original header data
# first_row = ['name', 'address', 'year'] # Use the corresponding English field to replace the Chinese header name obtained by first_row from the table above
for row_num in range(1, sheet.nrows): # Iterate each row of data
row_values = sheet.row_values(row_num) # Get the value of the current row
zip_data = dict(zip(first_row, row_values))
rows.append(zip_data)
data.append({
'sheet_name': sheet_name,
'rows': rows,
})
return JsonResponse({"res": data})
The result is as follows:
{
"res": [
{
"sheet_name": "Book Sheet",
"rows": [
{
"Book Name": ""Python + Cookbook" Third Edition Chinese v3.0.0.pdf",
"Address": "Tianjin City",
"Year": "1999.11"
},
{
"Book Name": "Django 2.2 Official Document Chinese Version.pdf",
"Address": "Dongcheng District, Beijing",
"Year": "1980.07"
},
{
"Book Name": "MySQL must know and know (text version).pdf",
"Address": "Gulou District, Nanjing",
"Year": "2020.08"
},
{
"Book Name": "PyCharm Knowledge Manual V1.0.pdf",
"Address": "Hefei City, Anhui",
"Year": "1987.12"
},
{
"Book Name": "Smooth Python.pdf",
"Address": "Gaochun District, Nanjing",
"Year": "2000.01"
},
{
"Book Name": "In-depth understanding of Python Chinese version HD (Dive + into + Python)@www.java1234.com.pdf",
"Address": "Luoyang, Henan",
"Year": "2023.10"
}
]
},
{
"sheet_name": "Sheet2",
"rows": [
{
"Picture Name": "Test",
"Path": "Test Path"
}
]
}
]
}
Method 3: Read by column
import xlrd
class ReadExcel(View):
def post(self, request):
excel_file = request.FILES['excel_file'] # Get the uploaded Excel file
workbook = xlrd.open_workbook(file_contents=excel_file.read()) # Open Excel file
sheet_names = workbook.sheet_names() # Get the names of all worksheets
# Method 3: by column
data = [] # Store the data of all worksheets
for sheet_name in sheet_names:
sheet = workbook.sheet_by_name(sheet_name) # Get a specific worksheet
if sheet.nrows != 0:
cols = []
cols_data = {}
for col_num in range(sheet.ncols): # Iterate each column of data
first_row = sheet.col_values(col_num, 0, 1)[0] # Get the data of the first row of each column (that is, the header)
col_values = sheet.col_values(col_num, 1) # Get data from the second row of the column
cols_data. update({first_row: col_values})
cols.append(cols_data)
data.append({
'sheet_name': sheet_name,
'cols': cols,
})
return JsonResponse({"res": data})
The result is as follows:
{
"res": [
{
"sheet_name": "Book Sheet",
"cols": [
{
"Book Name": [
""Python + Cookbook" third edition Chinese v3.0.0.pdf",
"Django 2.2 official document Chinese version.pdf",
"MySQL must know must know (text version).pdf",
"PyCharm Knowledge Manual V1.0.pdf",
"Smooth Python.pdf",
"In-depth understanding of Python Chinese version HD (Dive + into + Python)@www.java1234.com.pdf"
],
"address": [
"Tianjin",
"Dongcheng District, Beijing",
"Drum Tower District, Nanjing",
"Hefei City, Anhui",
"Nanjing Gaochun District",
"LuoYang HeNan"
],
"Era": [
"1999.11",
"1980.07",
"2020.08",
"1987.12",
"2000.01",
"2023.10"
]
}
]
},
{
"sheet_name": "Sheet2",
"cols": [
{
"Picture name": [
"test"
],
"Path": [
"Test path"
]
}
]
}
]
}
There are three ways to read
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledgePython entry skill treeBasic skillsData file reading and writing 319325 people are studying systematically