使用Node.js获取EXCEL表格数据

数据收集

因为原始数据都在一张 Excel 表中，再加上我对 office 操作并不怎么熟悉，于是决定把表格中需要用到的数据都导入到数据库中，再进行操作：

使用 xlsx 获取所需的数据

javascript 复制代码

const xlsx = require('xlsx');
// 读取需要使用的表格
const workbook = xlsx.readFile('studentData.xlsx');
// 使用主表1（第一个表格）
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// 创建学员类
class Student {
	constructor(obj) {
		for (let key in obj) {
			this[key] = obj[key];
		}
	}
	code; // 学生编号 F
	signUpTime; // 报名时间 B
	consultant; // 咨询师 I
	consultingAssistant; // 咨询助理 K
	enterTime; // 入学时间 D
	enterClass; // 入学班级 C
	hasUpdate; // 是否上报证书 N
	studentName; // 学员姓名 E
	phoneNum; // 学员电话 H
	idCard; // 学员身份证号 G
	lessonName; // 课程名称 L
}

/**
 * 通过表格地址查找当前表格信息
 * @param {string} address 表格地址
 * @param {string} nullValue 空值
 */
function getValueByAddress(address, nullValue = '') {
	const desired_cell = worksheet[address];
	const desired_value = desired_cell ? desired_cell.v : nullValue;
	return desired_value;
}

/**
 * 根据表格行号查找查找当前行学生信息
 * @param {number} rowNum 行号
 */
function getStudentByRow(rowNum) {
	return new Student({
		code: getValueByAddress(`F${rowNum}`), // 学生编号 F
		signUpTime: getValueByAddress(`B${rowNum}`), // 报名时间 B
		consultant: getValueByAddress(`I${rowNum}`), // 咨询师 I
		consultingAssistant: getValueByAddress(`K${rowNum}`), // 咨询助理 K
		enterTime: getValueByAddress(`D${rowNum}`), // 入学时间 D
		enterClass: getValueByAddress(`C${rowNum}`), // 入学班级 C
		hasUpdate: getValueByAddress(`N${rowNum}`, '否'), // 是否上报证书 N
		studentName: getValueByAddress(`E${rowNum}`), // 学员姓名 E
		phoneNum: getValueByAddress(`H${rowNum}`), // 学员电话 H
		idCard: getValueByAddress(`G${rowNum}`), // 学员身份证号 G
		lessonName: getValueByAddress(`L${rowNum}`), // 课程名称 L
	});
}

/**
 * 获取从起始行数到结束行数之间的数据
 * @param {number} startNum 开始行数
 * @param {number} endNum 结束行数
 */
async function handler(startNum, endNum) {
	for (let i = startNum; i <= endNum; i++) {
		const stu = getStudentByRow(i); // 此时已经可以拿到学生信息的数据了
	}
}

封装数据库链接的函数

javascript 复制代码

const mysql = require('mysql');

// 数据库配置
const MYSQL_CONFIG = {
	user: '',
	password: '',
	port: '',
	database: '',
	host: '',
};

/**
 * 数据库链接函数
 * @param {string} sql sql语句
 * @param {string | string[]} val 参数
 */
function query(sql, val) {
	return new Promise((resolve, reject) => {
		pool.getConnection((err, connection) => {
			if (err) {
				reject(err);
			}
			connection.query(sql, val, (err, fields) => {
				if (err) {
					console.log(err);
				}
				resolve(fields);
				connection.release();
			});
		});
	});
}

对 Excel 中五位数时间戳进行处理

这块直接用的从网上找的大佬写的转换函数：

javascript 复制代码

/**
 * 将Excel的五位数时间戳转换为标准时间
 * @param {timestamp} numb 五位数时间戳
 */
function formatDate(numb) {
	var second = 25569,
		day_timestamp = 24 * 60 * 60 * 1000;
	//把五位数的numb转化为时间戳类型，单位是毫秒
	var cTime = (numb - second) * day_timestamp;
	//如果numb为空或者不为时间类型，则原数据赋值
	if (cTime.toString() == 'NaN' || cTime <= 0) {
		return numb;
	} else {
		//转化为时间格式
		return timestamptodate('Y-m-d', cTime);
	}
}
function timestamptodate(format, timestamp) {
	var a,
		jsdate = timestamp ? new Date(timestamp) : new Date();
	var pad = function (n, c) {
		if ((n = n + '').length < c) {
			return new Array(++c - n.length).join('0') + n;
		} else {
			return n;
		}
	};
	var txt_weekdays = [
		'Sunday',
		'Monday',
		'Tuesday',
		'Wednesday',
		'Thursday',
		'Friday',
		'Saturday',
	];
	var txt_ordin = {
		1: 'st',
		2: 'nd',
		3: 'rd',
		21: 'st',
		22: 'nd',
		23: 'rd',
		31: 'st',
	};
	var txt_months = [
		'',
		'January',
		'February',
		'March',
		'April',
		'May',
		'June',
		'July',
		'August',
		'September',
		'October',
		'November',
		'December',
	];
	var f = {
		// Day
		d: function () {
			return pad(f.j(), 2);
		},
		D: function () {
			return f.l().substr(0, 3);
		},
		j: function () {
			return jsdate.getDate();
		},
		l: function () {
			return txt_weekdays[f.w()];
		},
		N: function () {
			return f.w() + 1;
		},
		S: function () {
			return txt_ordin[f.j()] ? txt_ordin[f.j()] : 'th';
		},
		w: function () {
			return jsdate.getDay();
		},
		z: function () {
			return ((jsdate - new Date(jsdate.getFullYear() + '/1/1')) / 864e5) >> 0;
		},

		// Week
		W: function () {
			var a = f.z(),
				b = 364 + f.L() - a;
			var nd2,
				nd = (new Date(jsdate.getFullYear() + '/1/1').getDay() || 7) - 1;
			if (b <= 2 && (jsdate.getDay() || 7) - 1 <= 2 - b) {
				return 1;
			} else {
				if (a <= 2 && nd >= 4 && a >= 6 - nd) {
					nd2 = new Date(jsdate.getFullYear() - 1 + '/12/31');
					return date('W', Math.round(nd2.getTime() / 1000));
				} else {
					return (1 + (nd <= 3 ? (a + nd) / 7 : (a - (7 - nd)) / 7)) >> 0;
				}
			}
		},

		// Month
		F: function () {
			return txt_months[f.n()];
		},
		m: function () {
			return pad(f.n(), 2);
		},
		M: function () {
			return f.F().substr(0, 3);
		},
		n: function () {
			return jsdate.getMonth() + 1;
		},
		t: function () {
			var n;
			if ((n = jsdate.getMonth() + 1) == 2) {
				return 28 + f.L();
			} else {
				if ((n & 1 && n < 8) || (!(n & 1) && n > 7)) {
					return 31;
				} else {
					return 30;
				}
			}
		},

		// Year
		L: function () {
			var y = f.Y();
			return !(y & 3) && (y % 1e2 || !(y % 4e2)) ? 1 : 0;
		},
		//o not supported yet
		Y: function () {
			return jsdate.getFullYear();
		},
		y: function () {
			return (jsdate.getFullYear() + '').slice(2);
		},

		// Time
		a: function () {
			return jsdate.getHours() > 11 ? 'pm' : 'am';
		},
		A: function () {
			return f.a().toUpperCase();
		},
		B: function () {
			// peter paul koch:
			var off = (jsdate.getTimezoneOffset() + 60) * 60;
			var theSeconds =
				jsdate.getHours() * 3600 +
				jsdate.getMinutes() * 60 +
				jsdate.getSeconds() +
				off;
			var beat = Math.floor(theSeconds / 86.4);
			if (beat > 1000) beat -= 1000;
			if (beat < 0) beat += 1000;
			if (String(beat).length == 1) beat = '00' + beat;
			if (String(beat).length == 2) beat = '0' + beat;
			return beat;
		},
		g: function () {
			return jsdate.getHours() % 12 || 12;
		},
		G: function () {
			return jsdate.getHours();
		},
		h: function () {
			return pad(f.g(), 2);
		},
		H: function () {
			return pad(jsdate.getHours(), 2);
		},
		i: function () {
			return pad(jsdate.getMinutes(), 2);
		},
		s: function () {
			return pad(jsdate.getSeconds(), 2);
		},
		//u not supported yet

		// Timezone
		//e not supported yet
		//I not supported yet
		O: function () {
			var t = pad(Math.abs((jsdate.getTimezoneOffset() / 60) * 100), 4);
			if (jsdate.getTimezoneOffset() > 0) t = '-' + t;
			else t = '+' + t;
			return t;
		},
		P: function () {
			var O = f.O();
			return O.substr(0, 3) + ':' + O.substr(3, 2);
		},
		//T not supported yet
		//Z not supported yet

		// Full Date/Time
		c: function () {
			return (
				f.Y() +
				'-' +
				f.m() +
				'-' +
				f.d() +
				'T' +
				f.h() +
				':' +
				f.i() +
				':' +
				f.s() +
				f.P()
			);
		},
		//r not supported yet
		U: function () {
			return Math.round(jsdate.getTime() / 1000);
		},
	};

	return format.replace(/[\ ]?([a-zA-Z])/g, function (t, s) {
		if (t != s) {
			// escaped
			ret = s;
		} else if (f[s]) {
			// a date function exists
			ret = f[s]();
		} else {
			// nothing special
			ret = s;
		}
		return ret;
	});
}

存入数据库

最后在循环中将每个学生信息对象存入数据库中就大功告成了：

javascript 复制代码

const SQL =
	'INSERT INTO student(' +
	'code,signUpName,consultant,consultingAssistant,enterTime,' +
	'enterClass,hasUpdate,studentName,phoneNum,idCard,lessonName)' +
	' VALUES (?,?,?,?,?,?,?,?,?,?,?)';
/**
 *
 * @param {number} startNum 开始行数
 * @param {number} endNum 结束行数
 */
async function handler(startNum, endNum) {
	for (let i = startNum; i <= endNum; i++) {
		const stu = getStudentByRow(i);
		const result = await query(SQL, [
			stu.code,
			formatDate(stu.signUpTime),
			stu.consultant,
			stu.consultingAssistant,
			stu.enterTime && stu.enterTime.toString().indexOf('+') === -1
				? formatDate(stu.enterTime)
				: stu.enterTime,
			stu.enterClass,
			stu.hasUpdate,
			stu.studentName,
			stu.phoneNum,
			stu.idCard,
			stu.lessonName,
		]);
		console.log('插入数据库成功，插入ID为：' + result.insertId);
	}
}

调用 handler 函数并传入开始行和结束行，收集数据就完成了。

2025年10月13日

Vite项目配置本地HTTPS

2024年6月25日

React Native 开发环境安装踩坑

2024年3月13日

Neekko33's Blog

使用Node.js获取EXCEL表格数据

2022年05月20日

数据收集

使用 xlsx 获取所需的数据

封装数据库链接的函数

对 Excel 中五位数时间戳进行处理

存入数据库

Vite项目配置本地HTTPS

React Native 开发环境安装踩坑

《JavaScript 高级程序设计》第10-16章