【第二篇 · 番外 01】

【第二篇 · 番外 01】Excel 与 MySQL 双向同步学完第二篇数据库篇我们来做一个真实的活把 HR 发过来的 Excel 员工表导入 MySQL再从 MySQL 导出一张漂亮的工资报表回给 HR。两个方向都是企业里天天要干的事正好展示 excelOperation 库和 mariadb 驱动配合的完整闭环。本文所有代码与输出均在仓颉 SDK 1.2.0 MySQL 5.7 下逐行实测。一、场景HR 和系统的语言不通HR 用 Excel 管人事系统用 MySQL 存数据。两边要对上话就要解决两个问题方向典型需求难点Excel → DBHR 发过来一份新员工名单批量入库长工号不能丢精度身份证/社保号、日期要识别、坏行不能让整批垮掉DB → Excel月底要出部门工资报表、给领导看大标题要好看、同部门要合并单元格、列宽要合适CSV 也能做但打开乱码中文 UTF-8 BOM 问题、大数字变科学计数法、不能合并单元格——这正是.xlsx存在的理由。本文用excelOperation库零第三方依赖纯仓颉实现配合第二篇 07 课的 mariadb 驱动搭一个独立 CLI 工具。二、新建工程与依赖cjpm init一个可执行工程ExcelDbClicjpm.toml改成[package] cjc-version 1.2.0 name ExcelDbCli version 1.0.0 output-type executable compile-option -Woff unused [dependencies] excelApi {git https://atomgit.com/wp_upala/excel_api, branch main} excelAnnotation {git https://atomgit.com/wp_upala/excel_annotation, branch main} mariadb {git https://gitcode.com/Cangjie-SIG/mariadb-driver.git, branch master} [target.x86_64-w64-mingw32] [target.x86_64-w64-mingw32.bin-dependencies] path-option [D:\\software\\stdx\\stdx]三处要注意stdx 路径path-option指向本机 stdx 解压目录作者机器上是D:\software\stdx\stdx双反斜杠\\按你机器改。excelAnnotation 必须显式声明它是编译期宏包cjpm 不允许靠传递依赖直接使用它声明的ExcelEntity宏。mariadb 地址和第二篇 07 课完全一样如果你之前已经拉过缓存这里不会重新下载。compile-option -Woff unused是为了压掉 mariadb 驱动和 excel 宏生成的无害 warning。执行cjpm update拉齐依赖。三、Employee 实体用注解映射 Excel 列和 DB 列一张表、一个实体、一次定义双向复用package ExcelDbCli import excelAnnotation.ExcelEntity import excelAnnotation.ExcelColumn ExcelEntity public class Employee { ExcelColumn[工号] private var _empNo: String; // VARCHAR(32)11 位以上不丢精度 ExcelColumn[姓名] private var _name: String; ExcelColumn[部门] private var _department: String; ExcelColumn[入职日期] private var _hireDate: String; // 字符串承载 yyyy-MM-dd方便读写两边 ExcelColumn[工资] private var _salary: String; // 工资用 String写 Excel 时不丢小数位 public init(empNo: String, name: String, department: String, hireDate: String, salary: String) { this._empNo empNo; this._name name; this._department department; this._hireDate hireDate; this._salary salary; } public mut prop empNo: String { get() { return this._empNo; } set(empNo) { this._empNo empNo; } } public mut prop name: String { get() { return this._name; } set(name) { this._name name; } } public mut prop department: String { get() { return this._department; } set(department) { this._department department; } } public mut prop hireDate: String { get() { return this._hireDate; } set(hireDate) { this._hireDate hireDate; } } public mut prop salary: String { get() { return this._salary; } set(salary) { this._salary salary; } } }ExcelEntity和ExcelColumn是编译期注解宏——编译时会自动为 Employee 生成excelFromRow读 Excel 时用和excelWriter()写 Excel 时用。写 DB 时我们自己用 SQL 的?占位符跟第二篇 10 课的插入写法一样。四、DB 连接 建表先用 SQL 客户端把表建好建表语句直接执行不写进仓颉代码里CREATETABLEIFNOTEXISTSemployee(emp_noVARCHAR(32)PRIMARYKEY,nameVARCHAR(50)NOTNULL,departmentVARCHAR(30)NOTNULL,hire_dateDATENOTNULL,salaryDECIMAL(10,2)NOTNULL);然后 DB 操作独立成一个Db类静态方法方便调用import std.database.sql.* import mariadb.cdbc // 必须显式 import触发驱动静态注册 class Db { public static func connect(): Connection { let driver DriverManager.getDriver(mariadb).getOrThrow() let ds driver.open( mariadb://127.0.0.1:3306, [ (username, root), (password, wangpeng), // 换成你自己的 (database, tumo_governance) ] ) return ds.connect() } }一个容易踩坑的点import mariadb.cdbc虽然没显式调用但必须保留——mariadb 驱动靠这个 import 的副作用把自己注册进DriverManager删了它再跑DriverManager.getDriver(mariadb)就会返回 None。五、DB → Excel导出带大标题和合并单元格的报表这是给领导看的所以要漂亮import std.collection.ArrayList import std.collection.HashMap import excelApi.ExcelUtils class Db { // 前面省略 connect public static func exportExcel(path: String): Unit { let conn Db.connect() let stmt conn.prepareStatement( SELECT emp_no, name, department, DATE_FORMAT(hire_date, %Y-%m-%d), salary FROM employee ORDER BY department, emp_no ) let rs stmt.query() let list ArrayListEmployee() while (rs.next()) { let empNo rs.getString(1) let name rs.getString(2) let department rs.getString(3) let hireDate rs.getString(4) let salary rs.getFloat64(5).format(.2).toString() let emp Employee(empNo, name, department, hireDate, salary) list.add(emp) } rs.close() stmt.close() conn.close() // 给各列设宽 let widths HashMapString, Int64() widths.add(工号, 22) widths.add(姓名, 12) widths.add(部门, 14) widths.add(入职日期, 14) widths.add(工资, 12) ExcelUtils.writeData(path, list, Employee.excelWriter(), title: 员工信息表, // 第 1 行跨列合并居中的大标题 sheetName: 员工, autoMerge: [部门], // 同部门相邻行自动合并 colWidths: widths, titleRowHeight: 32.0, // 磅 headerRowHeight: 24.0) println(导出完成${list.size} 行 - ${path}) } }实测导出的 xlsx 文件里row 0 是合并的员工信息表大标题5 列全合并居中row 1 是表头row 2 起是数据部门列相邻同值自动合并。六、Excel → DB导入 事务 upsert 跳过坏行HR 发过来的表可能带大标题、可能有几行脏数据、可能有重复工号需要更新——这三个坑一起处理import std.convert.* class Db { // 前面省略 public static func importExcel(path: String): Unit { // headerRow: 1 —— 跳过大标题行表头在第 2 行 let list: ArrayListEmployee ExcelUtils.readDataEmployee(path, Employee.excelFromRow, headerRow: 1) println(读取到 ${list.size} 行) let conn Db.connect() var ok 0 var fail 0 let ins conn.prepareStatement( INSERT INTO employee (emp_no, name, department, hire_date, salary) VALUES (?, ?, ?, STR_TO_DATE(?, %Y-%m-%d), ?) ON DUPLICATE KEY UPDATE nameVALUES(name), departmentVALUES(department), hire_dateVALUES(hire_date), salaryVALUES(salary) ) let tx conn.createTransaction() tx.begin() try { for (e in list) { try { ins.set(1, e.empNo) ins.set(2, e.name) ins.set(3, e.department) ins.set(4, e.hireDate) let sal Float64.parse(e.salary) ins.set(5, sal) ins.update() ok 1 } catch (_: Exception) { fail 1 // 单行失败不影响其他行 } } tx.commit() println(导入完成成功 ${ok}失败 ${fail}) } catch (ex: Exception) { tx.rollback() println(导入失败${ex}) } ins.close() conn.close() } }headerRow是这里的关键参数导出时我们写了大标题title表头被挤到了第 2 行所以headerRow: 1让库跳过第 0 行的大标题用第 1 行当真正的表头。如果你的 Excel 没有大标题、表头就在第 1 行这个参数可以省略默认 0。七、完整 main跑一遍完整闭环main(): Int64 { // 1. DB → Excel println( 1. 导出 Excel ) Db.exportExcel(test-export.xlsx) // 2. Excel → DB读回刚才导出的文件验证双向一致 println( 2. 再从 Excel 导入 ) Db.importExcel(test-export.xlsx) return 0 }加上之前写的Db.report()按部门分组统计人数和平均工资完整跑一遍 1. 导出 Excel 导出完成5 行 - test-export.xlsx 2. 再从 Excel 导入 读取到 5 行 导入完成成功 5失败 0八、小结这次学到了什么技巧在哪为什么重要ExcelEntityExcelColumnEmployee 类写注解、编译期生成映射代码零运行时反射开销headerRow: 1跳过大标题importExcelExcel 常带标题行读的时候必须让库知道哪一行是真正的表头mariadb 驱动的显式 importimport mariadb.cdbc编译器不知道这个 import 有副作用看起来没用但删了就找不到驱动ON DUPLICATE KEY UPDATESQL导入时遇到已存在的主键自动更新不用先删再插什么时候用.xlsx什么时候用 CSV需要大标题、合并单元格、长数字不丢精度、日期格式——用excelOperation只有纯数据导出、没人直接打开——用 CSV第一篇 30 课 GeoJSON 实战里有 CSV 写法。excelOperation 库的完整文档和仓库在excelApihttps://atomgit.com/wp_upala/excel_apiexcelAnnotationhttps://atomgit.com/wp_upala/excel_annotation扩展方向把 main 里的导入/导出改成命令行子命令cjpm run import xxx.xlsx接第二篇 03 课的命令行参数解析或者挂到 22 课的 Todo REST API 上做一个批量导入员工的接口。系列说明本系列基于 Windows 平台 CIDE 仓颉 SDK1.2.0 MySQL 5.7 编写。本文所有代码、导出结果与统计输出均在上述环境下逐行实测编译运行通过。如遇 SDK / 驱动版本差异导致的细节出入以你本地版本为准欢迎评论区交流。 遇到问题扫码联系作者跟着课程练习时如果在 SDK 安装、环境变量配置、编译报错或调试上卡住欢迎扫码加作者企业微信直接咨询请备注仓颉课程离线环境下图片可能加载不出来也可以在 CIDE 菜单Help ▸ 联系作者 / Contact中查看同一张二维码应用内置兜底图无需联网。 工具下载本系列全程使用的仓颉 IDE ——CIDE免费开源、社区版GitCode 仓库 / 安装包下载https://gitcode.com/wp_upala/cide打开页面后进入发行版Releases两种包任选其一安装版下载CIDE-版本-x64-Setup.exe双击安装适合日常长期使用免安装版Portable下载CIDE-版本-x64-Portable.zip解压到任意目录即用不写注册表、不留安装痕迹拷到 U 盘也能在别的电脑直接运行包内附《使用说明.txt》。适合先试用、或在受限电脑上学习本系列课程。仓颉 SDK 请前往仓颉编程语言官网下载https://cangjie-lang.cn