热门标签
热门文章
- 12024不可不会的StableDiffusion之反向提示词（六）
- 2本地部署Llama3 8b用Ollama和open-webui_llama3 8b 安装
- 3《我的阿勒泰》观后感（一、什么叫做有用）_我的阿勒泰观后感800字作文
- 4【重点EI会议推荐】机器学习主题方向论文征稿：2021计算机、物联网与控制工程国际学术会议（CITCE 2021)...
- 5【论文阅读】Spatio-Temporal Graph Convolutional Networks:...Traffic Forecasting[时空图卷积网络:用于交通预测的深度学习框架]（1）_spatio-temporal graph convolutional networks: a de
- 6川土微电子|CA-IS1200G全差分隔离运放ADC简介
- 7Transformer是什么？看完这篇你就醍醐灌顶
- 8【附源码】Java计算机毕业设计基于微信小程序的房屋交易平台（程序+LW+部署）_微信小程序购房代码
- 9区块链项目主持词_IT项目的三大主持人：谁赢得了比赛
- 10微信公众号关联微信小程序并跳转_公众号跳转小程序
当前位置: article > 正文
带你实现java根据表结构动态导入导出Excel_java 根据表结构生产excel模板
作者：你好赵伟 | 2024-07-14 19:16:28
赞
踩
java 根据表结构生产excel模板
你知道的越多，你不知道的越多
点赞再看，养成习惯
如果您有疑问或者见解，欢迎指教：
企鹅：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");
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/826068
推荐阅读
相关标签
