你知道的越多，你不知道的越多
点赞再看，养成习惯
如果您有疑问或者见解，欢迎指教：
企鹅：869192208
前言
最近在甲方爸爸的要(威)求(逼)下，项目经理带来了客户的全新需求，希望能够在原有编辑表单填写的过程中，简化列表数据输入过程，通过下载列表对应的数据表的表结构作为 excel 模板，然后客户只需要根据模板提示填写 excel 内容，再导入到数据库中，最终回显到编辑列表。
通过简化这个需求，抽象出核心功能，那就是根据表结构，动态实现数据模板的导出和数据的导入，趁着清明节三天假期，研究一下这个需求的实现逻辑。
思路
- 使用 POI 作为 excel 的导入导出支撑
- 需要同时支持 oracle 和 mysql 数据库
- 导出：
- sql 查出数据库指定表的表字段名、数据类型和注释等信息
- 将表的字段名，数据类型和注释信息分别输出到 excel 前三行，形成模板
- 导入：
- 先读取字段和字段类型，将其存入 LinkedHashMap
- 获取一共有多少个字段需要导入，以此为数据循环的截止参数（字段数量和每行的数据个数一致）
- 获取表中的内容，因为前三列分别是字段名、字段类型和描述，从第4行开始才是 excel 中需要插入数据库的内容
- 循环获取数据每一行的每一个单元格，根据单元格的数据类型，将数据转换成 String 类型的数据，存储到 List 集合
- 组装插入数据库的 insert 语句，表名是动态传入的，跟导出模板一致；字段分别是 excel 模板解析出来的首行字段，以",“分隔；插入数据以”?“充当占位符，以”,"分隔
- 将LinkedHashMap存储的字段对应的数据类型为基础，将数据填充到每一个"?"占位符
代码实现
代码中实现了 mysql 和 oracle 两种数据库的动态导入导出 excel，目前代码运行的是 oracle 数据库的操作，如果需要运行 mysql 数据库的操作，需要在 main 方法里面，将 mysql 的连接参数和获取表结构的代码注释去掉，同时将 oracle 的连接参数和获取表结构的代码注释即可。
建表语句
oracle：
-- ----------------------------
-- Table structure for APPR_EXCEL_DEMO
-- ----------------------------
DROP TABLE "APPR_EXCEL_DEMO";
CREATE TABLE "APPR_EXCEL_DEMO" (
"ID" VARCHAR2(50 BYTE) NOT NULL ,
"USER_NAME" VARCHAR2(255 BYTE) NULL ,
"SEX" NUMBER(2) NULL ,
"BIRTHDAY" DATE NULL ,
"PID" VARCHAR2(50 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON TABLE "APPR_EXCEL_DEMO" IS 'excel导入导出测试表';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."ID" IS '主键';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."USER_NAME" IS '用户名称';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."SEX" IS '性别';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."BIRTHDAY" IS '生日';
COMMENT ON COLUMN "APPR_EXCEL_DEMO"."PID" IS '父id';
-- ----------------------------
-- Indexes structure for table APPR_EXCEL_DEMO
-- ----------------------------
-- ----------------------------
-- Checks structure for table APPR_EXCEL_DEMO
-- ----------------------------
ALTER TABLE "APPR_EXCEL_DEMO" ADD CHECK ("ID" IS NOT NULL);
-- ----------------------------
-- Primary Key structure for table APPR_EXCEL_DEMO
-- ----------------------------
ALTER TABLE "APPR_EXCEL_DEMO" ADD PRIMARY KEY ("ID");
mysql
-- Source Database(数据库名称)：exceldemo
-- ----------------------------
-- Table structure for appr_excel_demo
-- ----------------------------
DROP TABLE IF EXISTS `appr_excel_demo`;
CREATE TABLE `appr_excel_demo` (
`id` varchar(50) NOT NULL COMMENT '主键',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名-字符串',
`sex` int(2) DEFAULT NULL COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`pid` varchar(50) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
package excel;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* <h2>根据表结构动态导入导出Excel</h2>
*
* @author xymy
* @date 2021-01-05 19:21
*/
public class ExcelDemo {
//oracle查询数据库表结构sql
private static final String getTableColumnsOracle = "SELECT A.DATA_TYPE \"data_type\", B.COLUMN_NAME \"column_name\", substr(B.COMMENTS,0,decode(instr(B.COMMENTS,'@'),NULL,255,0,255,instr(B.COMMENTS,'@'))-1) \"column_comment\"\n" +
"\t\t FROM USER_TAB_COLUMNS A\n" +
"\t\t LEFT JOIN USER_COL_COMMENTS B\n" +
"\t\t ON B.TABLE_NAME = A.TABLE_NAME\n" +
"\t\t AND A.COLUMN_NAME = B.COLUMN_NAME\n" +
"\t\t WHERE A.TABLE_NAME = ?\n" +
"order by b.COLUMN_NAME";
//mysql查询数据库表结构sql
//private static final String getTableColumnsMysql = "select column_name,column_comment,data_type,column_type,is_nullable from information_schema.columns where table_name=? and table_schema=? order by ORDINAL_POSITION asc";
private static final String getTableColumnsMysql = "SELECT\n" +
"\ta.column_name,\n" +
"\ta.column_comment,\n" +
"\ta.data_type,\n" +
"\ta.column_type,\n" +
"\ta.is_nullable,\n" +
"\tCASE\n" +
"WHEN b.COLUMN_NAME = a.column_name THEN\n" +
"\t'true'\n" +
"ELSE\n" +
"\t'false'\n" +
"END is_pk\n" +
"FROM\n" +
"\tinformation_schema. COLUMNS a\n" +
"JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.table_name = b.table_name\n" +
"WHERE\n" +
"\ta.table_name = ?\n" +
"AND a.table_schema = ?\n" +
"ORDER BY\n" +
"\ta.ORDINAL_POSITION ASC";
//定义excel操作的根目录
private static String PATH="D:/excel/";
private static DruidDataSource dataSource=null;
/**
* 构造函数完成数据库的连接和连接对象的生成
* @throws Exception
*/
public ExcelDemo(){
}
public void getDbConnect(String url, String driverClassName, String username, String password) throws Exception {
try{
if(dataSource==null){
dataSource=new DruidDataSource();
//设置连接参数
dataSource.setUrl(url);
dataSource.setDriverClassName(driverClassName);
dataSource.setUsername(username);
dataSource.setPassword(password);
//配置初始化大小、最小、最大
dataSource.setInitialSize(1);
dataSource.setMinIdle(1);
dataSource.setMaxActive(20);
//连接泄漏监测
dataSource.setRemoveAbandoned(true);
dataSource.setRemoveAbandonedTimeout(30);
//配置获取连接等待超时的时间
dataSource.setMaxWait(20000);
//配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
dataSource.setTimeBetweenEvictionRunsMillis(20000);
//防止过期
dataSource.setValidationQuery("SELECT 'x' from dual");
dataSource.setTestWhileIdle(true);
dataSource.setTestOnBorrow(true);
}
}catch(Exception e){
throw e;
}
}
/**
* 取得已经构造生成的数据库连接
* @return 返回数据库连接对象
* @throws Exception
*/
public Connection getConnect(String url, String driverClassName, String username, String password) throws Exception{
Connection con=null;
try {
getDbConnect(url, driverClassName, username, password);
con=dataSource.getConnection();
} catch (Exception e) {
throw e;
}
return con;
}
public static void main(String[] args) throws Exception {
//测试的表
String tableName = "appr_excel_demo";
//mysql测试数据库
String tableSchema = "exceldemo";
//配置mysql连接参数
/*String url = "jdbc:mysql://127.0.0.1:3306/exceldemo?useUnicode=true&characterEncoding=utf-8";
String driverClassName = "com.mysql.jdbc.Driver";
String username = "root";
String password = "123456";*/
//配置oracle连接参数
String url = "jdbc:oracle:thin:@127.0.0.1:1521:oanet";
String driverClassName = "oracle.jdbc.driver.OracleDriver";
String username = "root";
String password = "123456";
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
ExcelDemo dbConnect = new ExcelDemo();
Connection connection = dbConnect.getConnect(url, driverClassName, username, password);
//mysql获取数据库表结构
/*PreparedStatement ps = connection.prepareStatement(getTableColumnsMysql);
ps.setString(1, tableName);
ps.setString(2, tableSchema);*/
//oracle获取数据库表结构
PreparedStatement ps = connection.prepareStatement(getTableColumnsOracle);
ps.setString(1, tableName);
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getString("data_type") + " | " + rs.getString("column_name") + " | " + rs.getString("column_comment"));
Map<String, String> map = new HashMap<String, String>();
map.put("columnName", rs.getString("column_name"));
map.put("dataType", rs.getString("data_type"));
map.put("desc", rs.getString("column_comment"));
list.add(map);
}
list.stream().forEach(l -> {
System.out.println(l.get("columnName") + "," + l.get("dataType") + "," + l.get("desc"));
});
//将库表的结构导出到excel形成模板
exportExcel(list, "testWrite07excel.xlsx");
//将excel的数据导入到数据库
importExcel(connection, tableName, "testRead07excel.xlsx");
}
/**
* 导入excel
* @throws Exception
*/
private static void importExcel(Connection connection, String tableName, String fileName) throws Exception {
//获取文件
FileInputStream fileInputStream = new FileInputStream(PATH + fileName);
//获取工作薄
Workbook workbook = new XSSFWorkbook(fileInputStream);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//所有字段和类型的集合
LinkedHashMap<String, String> columns = new LinkedHashMap<>();
//记录一行有多少单元格
int cellCount = 0;
//获取0-字段名称、1-字段类型、2-字段描述
Row rowColumnName = sheet.getRow(0);
Row rowDataType = sheet.getRow(1);
if (rowColumnName != null && rowDataType != null){
//获取一行中有多少个单元格
cellCount = rowColumnName.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//获取单元
Cell cellColumnName = rowColumnName.getCell(cellNum);
Cell cellDataType = rowDataType.getCell(cellNum);
if (cellColumnName != null){
//获取类型
String cellColumnNameValue = cellColumnName.getStringCellValue();
String cellDataTypeValue = cellDataType.getStringCellValue();
columns.put(cellColumnNameValue, cellDataTypeValue);
System.out.print(cellColumnNameValue+","+ cellDataTypeValue + " | ");
}
}
System.out.println();
}
//获取表中的内容,从第4行开始是excel中需要插入数据库的内容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 3; rowNum < rowCount; rowNum++) {
//一行记录所有值的集合
List<String> values = new ArrayList<>();
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
//读取列
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("【" + (rowNum+1) + "-" + (cellNum+1) + "】");
Cell cell = rowData.getCell(cellNum);
//匹配列的数据类型
if (cell != null){
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("【STRING】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔值
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字类型
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = formatter.format(date);
}else{
// 不是日期格式，则防止当数字过长时以科学计数法显示
System.out.print("【转换成字符串】");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("【BLANK】");
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
values.add(cellValue);
}
}
}
//将数据插入数据库
//组装所有的插入字段
String inserts = "";
//组装所有的插入值的占位符
String v = "";
List<String> types = new ArrayList<>();
for (String s : columns.keySet()) {
System.out.println("key=" + s + " value=" + columns.get(s));
inserts = inserts + s + ",";
v = v + "?,";
types.add(columns.get(s));
}
String insertSql = "insert into "+tableName+"("+inserts.substring(0, inserts.lastIndexOf(","))+")values("+v.substring(0, v.lastIndexOf(","))+")";
System.out.println(insertSql);
PreparedStatement ps = connection.prepareStatement(insertSql);
//构造真正的需要插入的值
for (int i = 1; i<= types.size(); i++){
String type = types.get(i-1);
String value = values.get(i-1);
System.out.println(type + "-" + value);
if (type.contains("varchar")){//字符串
ps.setString(i, value);
}else if (type.contains("int") || type.contains("number")){//数字
ps.setInt(i, Integer.parseInt(value));
}else if (type.contains("date")){//日期
if (!value.isEmpty()){
ps.setTimestamp(i, new Timestamp(
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value).getTime()));
}else {
ps.setTimestamp(i, null);
}
}
}
int execute = ps.executeUpdate();
System.out.println(execute);
}
fileInputStream.close();
}
/**
* 导出excel
* @throws Exception
*/
private static void exportExcel(List<Map<String, String>> list, String fileName) throws Exception {
//创建簿
Workbook workbook = new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写数据
for (int rowNum = 0; rowNum < 3; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < list.size(); cellNum++) {
Cell cell = row.createCell(cellNum);
if (rowNum == 0){
cell.setCellValue(list.get(cellNum).get("columnName"));
}else if (rowNum == 1){
cell.setCellValue(list.get(cellNum).get("dataType"));
}else if (rowNum == 2){
cell.setCellValue(list.get(cellNum).get("desc"));
}
}
}
System.out.println("导入模板完成");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + fileName);
workbook.write(fileOutputStream);
fileOutputStream.close();
}
}
扫一扫
1056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
