导入开发包
pip
install
xlrd
==
1.2.0
解析xls文件
# filename 文件路径
# file_contents 文件上传解析文件内容
def
parse_xls
(
filename
=
None
,
file_contents
=
None
)
:
result
=
{
"error"
:
{
}
,
"data_list"
:
[
]
,
"model_name"
:
""
}
work_sheet
=
None
try
:
if
filename
:
work_sheet
=
xlrd
.
open_workbook
(
filename
)
elif
file_contents
:
work_sheet
=
xlrd
.
open_workbook
(
filename
=
None
,
file_contents
=
file_contents
)
tb
=
work_sheet
.
sheets
(
)
[
0
]
if
tb
.
nrows
>
RES_LIMIT
:
raise
Exception
(
"当前xls上传的数据不能超过%d行"
,
RES_LIMIT
)
name
,
datas
=
translate_xlsx_field_2_string
(
work_sheet
)
result
[
'data_list'
]
=
datas
result
[
'model_name'
]
=
RES_MAPPING
.
get
(
name
)
except
Exception
as
e
:
logger
.
exception
(
e
)
result
[
'error'
]
=
str
(
e
)
return
result
解析内容
def
translate_xlsx_field_2_string
(
work_sheet
)
:
rows_data
=
[
]
tb
=
work_sheet
.
sheets
(
)
[
0
]
n_row
=
tb
.
nrows
# 第一条为标题，不要了
for
i
in
range
(
1
,
n_row
)
:
one_row
=
[
]
for
j
in
range
(
0
,
len
(
tb
.
row
(
i
)
)
)
:
if
tb
.
cell
(
i
,
j
)
.
ctype
in
[
2
,
4
]
:
value
=
str
(
tb
.
cell_value
(
i
,
j
)
)
.
strip
(
)
one_row
.
append
(
value
if
value
else
None
)
elif
tb
.
cell
(
i
,
j
)
.
ctype
==
0
:
one_row
.
append
(
None
)
elif
tb
.
cell
(
i
,
j
)
.
ctype
==
3
:
date_v
=
xlrd
.
xldate_as_tuple
(
tb
.
cell_value
(
i
,
j
)
,
work_sheet
.
datemode
)
one_row
.
append
(
datetime
.
datetime
(
*
date_v
)
.
strftime
(
"%Y-%m-%d %H:%M:%S"
)
)
else
:
value
=
tb
.
cell_value
(
i
,
j
)
.
strip
(
)
one_row
.
append
(
value
if
value
else
None
)
# 空行没数据
if
one_row
.
count
(
None
)
==
len
(
one_row
)
:
continue
rows_data
.
append
(
one_row
)
return
tb
.
name
,
rows_data
例子
## demo01
for
single_xls
in
request
.
FILES
:
rs
=
parse_xls
(
filename
=
None
,
file_contents
=
request
.
FILES
[
single_xls
]
.
read
(
)
)
if
rs
[
'error'
]
:
errors_info
.
append
(
{
"xls_name"
:
request
.
FILES
[
single_xls
]
.
name
,
"error"
:
rs
[
'error'
]
}
)
# 校验资源在对应的后台是否存在冲突
data_json
=
rs
[
'data_list'
]
model_name
=
rs
[
'model_name'
]
model_data
[
model_name
]
=
data_json
## demo02
base_dir
=
os
.
path
.
abspath
(
os
.
path
.
join
(
os
.
getcwd
(
)
)
)
xls_path
=
"/"
.
join
(
[
base_dir
,
UPLOAD_PATH
,
str
(
_task_id
)
,
folder_name
,
file_name
]
)
logger
.
info
(
"the xls_path = %s"
,
xls_path
)
# 读取资源文件
result
=
parse_xls
(
filename
=
xls_path
)
