react解析xlsx,excel文件,处理为数组对象格式
废话不多说直接上代码
这是效果图
这是模版
1、上传部分
import React, { Component } from "react";
import _ from "lodash";
import { Modal, Upload, Table, message, Divider } from "antd";
import { InboxOutlined, LoadingOutlined } from "@ant-design/icons";
import styles from "../style.less";
const { Dragger } = Upload;
const XLSX = window.XLSX;
class ModalBatchImport extends Component {
constructor(props) {
super(props);
this.state = {
list: [], // excel list
};
}
asyncReaderFileList = (info) => {
const that = this;
const files = info.fileList || [];
const file = files[files.length - 1]; // 获取第一个Blob对象
if (file && file.originFileObj) {
const reader = new FileReader();
reader.readAsBinaryString(file.originFileObj); // readAsBinaryString(file):将文件读取为二进制字符串
reader.onload = function (e) {
const data = e.target.result;
const wb = XLSX.read(data, {
type: "binary",
});
const json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);//sheet1
const json_two = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[1]]);//sheet2
const jsonList = [...json, ...json_two];
if (_.isEmpty(jsonList)) {
message.error("请检查文件后重新上传");
} else {
//将数据提出来转为 key:value 的形式存入数组
let list = jsonList.map((el) => {
let tags = [_.trim(el["标签"])];
if (!_.isEmpty(el)) {
Object.keys(el).map((key) => {
if (key.search('价格') != -1) {
tags.push(el[key]);
}
})
}
tags = _.uniq(tags);
return _.pickBy(
{
mode: el["房型ID(长)"] ? 'roomtype' : 'hotel',
hotel: _.trim(el["酒店ID"]),
value: el["房型ID(长)"] ? _.trim(el["房型ID(长)"]) : _.trim(el["酒店ID"]),
tags,
description: _.trim(el["备注"])
},
_.identity
);
});
list = list.filter(item=>item?.hotel);
that.setState({
list,
});
}
};
}
};
checkSubmit = () => {
const { list } = this.state;
if (_.isEmpty(list)) {
message.error("请先上传Excel文件");
} else {
this.props.handleSubmit(list);
}
};
render() {
const { visible, handleCancel, loading } = this.props;
const { list } = this.state;
const columns = [
{
title: "酒店ID",
dataIndex: "hotel",
render: (text) => text || "--",
},
{
title: "房型ID(长)",
dataIndex: "mode",
render: (text, record) => text === 'roomtype' ? _.get(record, 'value') : '--',
},
{
title: "模式",
dataIndex: "mode",
render: (text) => text === 'roomtype' ? '国内房型打标' : '酒店价格打标',
},
{
title: "标签",
dataIndex: "tags",
render: (text) => _.get(text,'length') ? text.join(',') : '--',
},
{
title: "备注",
dataIndex: "description",
render: (text) => text || '--',
},
];
return (
<Modal
title="批量导入"
centered
width={800}
visible={visible}
onOk={this.checkSubmit}
okButtonProps={{
loading,
disabled: _.isEmpty(list),
}}
onCancel={handleCancel}
>
<div className={styles.modalContent}>
<h4 className={styles.modalTitle}>第一步:下载模板</h4>
<div className={styles.modalTemplate}>
<a
href="https://cxp-cdn.oss-cn-shanghai.aliyuncs.com/common/%E6%A0%87%E7%AD%BE%E6%A8%A1%E7%89%88.xlsx"
target="_blank"
download="酒店标签模板.xlsx"
>
酒店标签模板.xlsx
</a>
<p className={styles.infoText}>*请勿更改单元格格式及表头内容</p>
<p className={styles.infoText}>*请按照样例填写单元格内容</p>
</div>
<h4 className={styles.modalTitle}>第二步:上传已编辑文档</h4>
<div className={styles.modalContent}>
<Dragger
name="file"
beforeUpload={(file) => false}
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
showUploadList={false}
onChange={this.asyncReaderFileList}
>
<p className="ant-upload-drag-icon">
{this.state.uploadLoading ? (
<LoadingOutlined />
) : (
<InboxOutlined />
)}
</p>
<p className="ant-upload-text">请将文件拖动到此处上传</p>
<p className="ant-upload-hint">请不要改变文件名称,表头信息</p>
</Dragger>
{_.isEmpty(list) ? null : (
<div>
<Divider />
<h4>
请确认文档内容无误后,点击确认按钮上传,或继续拖动文件覆盖文档
</h4>
<Table
columns={columns}
dataSource={list}
pagination={false}
scroll={{ x: true, y: '60vh', scrollToFirstRowOnChange: true }}
/>
</div>
)}
</div>
</div>
</Modal>
);
}
}
export default ModalBatchImport;
2、异步请求部分
handleBatchImport = async (data) => {
const { dispatch } = this.props;
let list = _.cloneDeep(data);
if (data.length > 500) {
//数据大于500条时,将数组每500条分割出来
list = _.chunk(data, 500);
await Promise.all(
_.map(list, (item) => {
return new Promise((resolve, reject) => {
dispatch({
type: 'cmsClientTag/batchImport',
payload: {},
callback: (bool) => {
if (bool) {
resolve(1);
} else {
reject(0);
}
},
});
});
}),
);
notification.success({ message: "批量上传成功" });
this.setState({ importVisible: false }, () => {
//可以拉去最新列表
});
} else {
dispatch({
type: "cmsClientTag/batchImport",
payload: {},
callback: (res) => {
if (res) {
notification.success({ message: "批量上传成功" });
this.setState({ importVisible: false }, () => {
//可以拉去最新列表
});
}
},
});
}
};
3、样式
.modalContent {
width: 700px;
margin: 0 auto;
.modalTitle {
margin: 1em 0;
}
}
.modalTemplate {
width: 100%;
padding: 16px 0;
text-align: center;
background: #fafafa;
border: 1px dashed #d9d9d9;
border-radius: 2px;
a {
display: block;
margin-bottom: 20px;
text-decoration: underline;
cursor: pointer;
text-underline-offset: 4px;
&:hover {
color: @primary-color;
}
}
.infoText {
color: red;
font-size: 12px;
}
}