Form export, fill in data, import form
- Requirements: form export, fill in data, import data
-
- Table files are stored in the frontend
- Form files are not stored
Requirements: form export, fill in data, import data
Analysis needs:
(1) About table export
There are three situations for exporting tables on the front-end page: ① The table file is stored in the back-end, the back-end returns the file stream to the front-end, and the front-end calls the back-end interface to download the file; ②The table file is stored in the frontend, and the frontend downloads the file; ③The table file is not stored, and the frontend exports the corresponding table according to some fields of tableData.
(2) Fill in the data
Fill in the data in Excel, so I won’t say more;
(3) Import data
Read the Excel table to get the required data, and data processing may be required. In short, importing data is to hand over an object array to the backend
Form files are stored in the front end
As shown in the figure, the table file template.xlsx is stored in the static folder under the public
The page layout and the excel that the user needs to fill in are as follows
Note: Reserving 2 digits involved in the amount is not rounding, but directly retaining 2 digits, and the others are removed. For example: 200.098 reserves 2 digits, and the result is 200.09. Therefore, when importing data, the value needs to be processed, and the processing method is as follows:
num=Math. floor(num*100)/100
Code Implementation
1. template
<div class="buttons">
<el-button type="primary" size="small"
@click="DownloadTemplate"
>
download template
</el-button>
<el-upload
action=""
class="upload"
accept=".xlsx,.xls"
:auto-upload="false"
:show-file-list="false"
:on-change="importData"
>
<el-button type="primary" size="small">
Import Data
</el-button>
</el-upload>
</div>
<el-table
:data="newData"
style="width: 100%"
>
<el-table-column
prop="name"
label="name"
width="180"
/>
<el-table-column
prop="date"
label="date of birth"
/>
<el-table-column
prop="last"
label="Last month"
/>
<el-table-column
prop="next"
label="next month"
/>
<el-table-column
prop="sum"
label="total"
/>
</el-table>
</div>
2.js
import * as XLSX from 'xlsx'
//Equivalent to a dictionary correspondence
const tempObj = {<!-- -->
name: 'name',
date of birth: 'date',
last month: 'last',
next month: 'next'
}
data () {<!-- -->
return {<!-- -->
newData: []
}
},
methods: {<!-- -->
// Click the button to trigger a to download the file
DownloadTemplate () {<!-- -->
let a = document. createElement('a')
a.href = './static/template.xlsx'
a.download = 'Recent consumption statistics.xlsx'
a.style.display = 'none'
document. body. appendChild(a)
a. click()
a. remove()
},
ImportData (file) {<!-- --> // The parameter is file, indicating the file to be uploaded
const types = file.name.slice(file.name.lastIndexOf('.')) // get file extension
const fileType = ['.xlsx', '.xls'].some(item => item === types) // Determine whether the file type is xlsx or xls
if (!fileType) {<!-- --> // If the file type is incorrect, prompt the user to re-upload
this.$message.warning('The file format is wrong! Please re-upload')
return // If the file type is incorrect, end the function
}
this.newData = [] // Initialize newData to store the read data
const reader = new FileReader() // create a file reader
reader.readAsBinaryString(file.raw) // read the file content into a binary string
reader.onload = () => {<!-- --> // When the file is read successfully
const binary = reader.result // Get the binary string of the file content
const wb = XLSX.read(binary, {<!-- --> // convert binary string to workbook object
type: 'binary'
})
// Convert the first sheet in the workbook object to JSON format (skip the first two lines)
const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {<!-- --> range: 2 })
console. log(outdata)
outdata.map((item, index) => {<!-- -->
// Make necessary judgments on the read fields
this.JudgeEmpty(item['date of birth'], 'date of birth', index)
this.JudgeEmpty(item['name'], 'name', index)
this.JudgeEmptyAndNumber(item['last month'], 'last month', index)
this.JudgeEmptyAndNumber(item['next month'], 'next month', index)
let obj = {<!-- -->}
for (let key in tempObj) {<!-- -->
if (!item.hasOwnProperty(key)) {<!-- --> continue }
if (key === 'last month' || key === 'next month') {<!-- -->
obj[tempObj[key]] = Math. floor(item[key] * 100) / 100
} else if (key === 'date of birth') {<!-- -->
obj[tempObj[key]] = this.$util.formatExcelDate(item[key])
} else {<!-- --> obj[tempObj[key]] = item[key] }
}
this.newData.push(obj)
})
this. DataProcess()
}
},
DataProcess () {<!-- -->
// Involves the need for automatic calculation and processing by the system
this.newData.forEach(ele => {<!-- -->
this.$set(ele, 'sum', Math.floor((ele.last + ele.next) * 100) / 100)
})
console. log(this. newData)
// After processing, call the interface next and submit newData to the backend
},
// The field in Excel is judged to be empty
JudgeEmpty (a, str, i) {<!-- -->
i + +
if (typeof a === 'undefined') {<!-- -->
this.$message.error('In Excel, row ' + i + '<' + str + '> column data is incomplete, please add!')
this. newData = []
throw Error
}
},
// The field in Excel is judged to be empty and the filling type must be a number
JudgeEmptyAndNumber (a, str, i) {<!-- -->
i + +
if (typeof a === 'undefined') {<!-- -->
this.$message.error('In Excel, row ' + i + '<' + str + '> column data is incomplete, please add!')
this. newData = []
throw Error
}
if (isNaN(a)) {<!-- -->
this.$message.error('In Excel, row ' + i + '<' + str + '> column data type is incorrect, please fix it!')
this. newData = []
throw Error
}
}
}
Form files are not stored
Look at the picture:
Some points to note about filling in excel are the same as when storing excel templates at the front end
Dependency download:
npm install moment
npm install vue-json-excel?
npm install xlsx?
Code Implementation
1. template
<download-excel
class="export-excel-wrapper"
:data="tableData"
:fields="json_fields"
header="Recent consumption statistics template table"
type="xls"
worksheet="My Worksheet"
name="Recent consumption statistics template table"
>
<el-button type="primary" size="small">
export template
</el-button>
</download-excel>
<!-- The import data button is the same as the front-end storage excel -->
<el-table
:data="tableData"
style="width: 100%"
>
<el-table-column
prop="name"
label="name"
width="180"
/>
<el-table-column
prop="gender"
label="gender"
width="180"
/>
<el-table-column
prop="date"
label="date of birth"
/>
<el-table-column
prop="last"
label="Last month"
/>
<el-table-column
prop="next"
label="next month"
/>
<el-table-column
prop="sum"
label="Total"
/>
<el-table-column
prop="remarks"
label="Remarks"
/>
</el-table>
2.js
import * as XLSX from 'xlsx'
let tempObj = {<!-- -->
date of birth: 'date',
last month: 'last',
next month: 'next',
Remarks: 'remarks'
}
data () {<!-- -->
return {<!-- -->
// Exported fields (the first two fields have values, the last four have no values)
json_fields: {<!-- -->
name: 'name',
gender: 'gender',
date of birth: 'xxx',
Last month: 'xxx',
next month: 'xxx',
Remarks: 'xxx'
},
// The initial fake data returned by the backend to the frontend
tableData: [
{<!-- -->
name: 'yxx',
gender: 'female'
},
{<!-- -->
name: 'wx',
gender: 'male'
}
],
// newly imported data
newData: []
}
},
methods: {<!-- -->
ImportData (file) {<!-- -->
const types = file.name.slice(file.name.lastIndexOf('.')) // get file extension
const fileType = ['.xlsx', '.xls'].some(item => item === types) // Determine whether the file type is xlsx or xls
if (!fileType) {<!-- --> // If the file type is incorrect, prompt the user to re-upload
this.$message.warning('The file format is wrong! Please re-upload')
return // If the file type is incorrect, end the function
}
this.newData = [] // Initialize newData to store the read data
const reader = new FileReader() // create a file reader
reader.readAsBinaryString(file.raw) // read the file content into a binary string
reader.onload = () => {<!-- --> // Do the following when the file is read successfully
const binary = reader.result // Get the binary string of the file content
const wb = XLSX.read(binary, {<!-- --> // convert binary string to workbook object
type: 'binary'
})
// Convert the first sheet in the workbook object to JSON format, skipping 1 row (header row)
const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {<!-- --> range: 1 })
console. log(outdata)
outdata.map((item, index) => {<!-- -->
this.JudgeEmpty(item['date of birth'], 'date of birth', index)
this.JudgeEmptyAndNumber(item['last month'], 'last month', index)
this.JudgeEmptyAndNumber(item['next month'], 'next month', index)
this.JudgeEmpty(item['remarks'], 'remarks', index)
let obj = {<!-- -->}
// eslint-disable-next-line guard-for-in
for (let key in tempObj) {<!-- -->
// eslint-disable-next-line no-prototype-builtins
if (!item.hasOwnProperty(key)) {<!-- --> return }
if (key === 'last month' || key === 'next month') {<!-- -->
obj[tempObj[key]] = Math. floor(item[key] * 100) / 100
} else if (key === 'date of birth') {<!-- -->
obj[tempObj[key]] = this.$util.formatExcelDate(item[key])
} else {<!-- --> obj[tempObj[key]] = item[key] }
}
this.newData.push(obj)
})
this. DataProcess()
}
},
DataProcess () {<!-- -->
this.tableData.forEach((ele, i) => {<!-- -->
// eslint-disable-next-line guard-for-in
for (let key in this.newData[i]) {<!-- --> // splice the old and new data objects together
this.$set(ele, key, this.newData[i][key])
}
// Involves the need for automatic calculation and processing by the system
this.$set(ele, 'sum', Math.floor((ele.last + ele.next) * 100) / 100)
})
// After processing, call the interface next and submit newData to the backend
}
}
The method for processing excel input as a date is placed in the public code util.js, as follows:
import * as moment from 'moment'
const START_TIME = '1900/01/01'
const FORMAT = 'YYYY/MM/DD'
export default {<!-- -->
// Date conversion: convert August 27, 1999 or 1999/8/27 to 1996/08/27
formatExcelDate (num) {<!-- -->
let duration = num - 1
// The num of 1900/2/29 is 60
if (num > 60) {<!-- -->
// For the date that needs to be parsed with num greater than 60, the day that is more than 1900/2/29 should be subtracted
duration = num - 2
}
return moment(START_TIME).add(moment.duration({<!-- --> days: duration }))
.format(FORMAT)
},
install (innerVue) {<!-- -->
innerVue.prototype.$util = this
}
}