xlsx 파일 인 파일 업로드 용 API를 만들어야합니다. multer를 사용하여 파일을 성공적으로 업로드했으며 파일을 열 수 있으므로 파일을 구문 분석하고 배열 json 객체로 변환해야합니다. 내 코드는 다음과 같습니다.
let workbook = xlsx.readFile(`uploads/${req.file.filename}`)
let worksheet = workbook.Sheets['Sheet1']
let headers = {}
let data = []
for (z in worksheet) {
if (z[0] === '!') continue
//parse out the column, row, and value
let tt = 0
for (let i = 0; i < z.length; i++) {
if (!isNaN(z[i])) {
tt = i
break
}
tt = i
}
let col = z.substring(0, tt)
let row = parseInt(z.substring(tt))
let value = worksheet[z].v
//store header names
if (row == 1 && value) {
headers[col] = value
continue
}
if (!data[row]) data[row] = {}
data[row][headers[col]] = value
}
// drop those first two rows which are empty
data.shift()
data.shift()
console.log(data)
const studentList = []
data.map(object => {
if (object['Year of Enrollment']) object['Year of Enrollment'] = parseInt(object['Year of Enrollment'])
if (object['Semester']) object['Semester'] = parseInt(object['Semester'])
if (object['Aadhar Number']) object['Aadhar Number'] = parseInt(object['Aadhar Number'])
if (object['Phone Number']) object['Phone Number'] = parseInt(object['Phone Number'])
if (object['Alternate Phone Number']) object['Alternate Phone Number'] = parseInt(object['Alternate Phone Number'])
let obj = {
"student_first_name": object['First Name'],
"student_last_name": object['Last Name'],
"student_branch": object['Branch'],
"student_year": object['Year of Enrollment'],
"student_semester": object['Semester'],
"student_gender": object['Gender'],
"student_email": object['Email'],
"student_address": object['Address'],
"student_linkedin_profile": object['Linkedin Profile'],
"student_github_profile": object['Github Profile'],
"student_aadhar_number": object['Aadhar Number'],
"student_phone_number": object['Phone Number'],
"student_date_of_birth": object['Date of Birth'],
college_id: req.body.college_id
}
if (object['Alternate Email']) obj.student_alternate_email = object['Alternate Email']
if (object['Alternate Phone Number']) obj.student_alternate_phone_number = object['Alternate Phone Number']
studentList.push(obj)
})
console.log(studentList)
출력에서 나는 이것을 얻고 있으며 생년월일에 무슨 일이 일어 났는지 모릅니다.
[
{
student_first_name: 'Ekansh',
student_last_name: 'Jain',
student_branch: 'CSE',
student_year: 2017,
student_semester: 6,
student_gender: 'male',
student_email: '[email protected]',
student_address: 'Udfr',
student_linkedin_profile: 'http://linkedin.com/ekansh',
student_github_profile: 'http://github.com/ekansh',
student_aadhar_number: 123456789011,
student_phone_number: 7894561230,
student_date_of_birth: 36270,
college_id: 'college-f9NsLo7Ww6oTIlICElG5GZD5JHKTxqbF',
student_alternate_email: '[email protected]'
}
]
엑셀 파일의 생년월일 필드에는 1999 년 4 월 20 일입니다. 출력이 다음과 같이 보이도록 도와 줄 수 있습니까?
[
{
student_first_name: 'Ekansh',
student_last_name: 'Jain',
student_branch: 'CSE',
student_year: 2017,
student_semester: 6,
student_gender: 'male',
student_email: '[email protected]',
student_address: 'Udfr',
student_linkedin_profile: 'http://linkedin.com/ekansh',
student_github_profile: 'http://github.com/ekansh',
student_aadhar_number: 123456789011,
student_phone_number: 7894561230,
student_date_of_birth: "1999-04-20",
college_id: 'college-f9NsLo7Ww6oTIlICElG5GZD5JHKTxqbF',
student_alternate_email: '[email protected]'
}
]
다음 코드와 같이 xlsx 파일 (첫 번째 줄)을 읽을 때 옵션으로 cellDates true를 전달하면됩니다.
let workbook = xlsx.readFile(`uploads/${req.file.filename}`, { cellDates: true })
let worksheet = workbook.Sheets['Sheet1']
let headers = {}
let data = []
for (z in worksheet) {
if (z[0] === '!') continue
//parse out the column, row, and value
let tt = 0
for (let i = 0; i < z.length; i++) {
if (!isNaN(z[i])) {
tt = i
break
}
tt = i
}
let col = z.substring(0, tt)
let row = parseInt(z.substring(tt))
let value = worksheet[z].v
//store header names
if (row == 1 && value) {
headers[col] = value
continue
}
if (!data[row]) data[row] = {}
data[row][headers[col]] = value
}
// drop those first two rows which are empty
data.shift()
data.shift()
console.log(data)
const studentList = []
data.map(object => {
if (object['Year of Enrollment']) object['Year of Enrollment'] = parseInt(object['Year of Enrollment'])
if (object['Semester']) object['Semester'] = parseInt(object['Semester'])
if (object['Aadhar Number']) object['Aadhar Number'] = parseInt(object['Aadhar Number'])
if (object['Phone Number']) object['Phone Number'] = parseInt(object['Phone Number'])
if (object['Alternate Phone Number']) object['Alternate Phone Number'] = parseInt(object['Alternate Phone Number'])
let obj = {
"student_first_name": object['First Name'],
"student_last_name": object['Last Name'],
"student_branch": object['Branch'],
"student_year": object['Year of Enrollment'],
"student_semester": object['Semester'],
"student_gender": object['Gender'],
"student_email": object['Email'],
"student_address": object['Address'],
"student_linkedin_profile": object['Linkedin Profile'],
"student_github_profile": object['Github Profile'],
"student_aadhar_number": object['Aadhar Number'],
"student_phone_number": object['Phone Number'],
"student_date_of_birth": object['Date of Birth'],
college_id: req.body.college_id
}
if (object['Alternate Email']) obj.student_alternate_email = object['Alternate Email']
if (object['Alternate Phone Number']) obj.student_alternate_phone_number = object['Alternate Phone Number']
studentList.push(obj)
})
console.log(studentList)
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다