java讀取oracle表字段,生成XSSFWorkbook xlsx,維護對應數據並上傳xlsx ,按照表結構插入數據到oracle數據庫
代碼環境
1、主要工具
idea 2019.3.3
maven 3.6.1
springoot 2.4.3
poi 5.0.0
2、完整代碼
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.niuYear</groupId>
<artifactId>CommonUtilsApi</artifactId>
<version>1.0</version>
<packaging>jar</packaging>
<name>commonUtilsApi</name>
<properties>
<java.version>1.8</java.version>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<!-- <exclusions>-->
<!-- <exclusion>-->
<!-- <groupId>org.springframework.boot</groupId>-->
<!-- <artifactId>spring-boot-starter-tomcat</artifactId>-->
<!-- </exclusion>-->
<!-- </exclusions>-->
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency><!-- lombok依赖包 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<!-- Spring Boot 整合 Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<!--Oracle驱动包-->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>12.1.0.2.0</version>
</dependency>
<!-- 阿里druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
<!--poi excel生成-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
</dependencies>
<build>
<finalName>commonApi</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
<!--<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>-->
</project>
一、根據表字段生成對應XLSX
1、讀取表字段
package com.niuYear.utils;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @program: moveOut
* @description: 數據庫表結構工具類
* @author: PengFei_Ge
* @create: 2021-05-25 10:30
**/
public class DataTableUtil {
private static final String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
private static final String DATABASE_URL = "jdbc:oracle:thin:@xx.xx.xx.xx:1525:DEMO";
private static final String DATABASE_USER = "xxx";
private static final String DATABASE_PASSWORD = "xxx";
/**
* <p>加載數據庫驅動</p>
* @param
* @return {@link }
*/
public DataTableUtil() {
try {
Class.forName(DRIVER_CLASS);
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
/**
* <p>創建數據庫鏈接</p>
* @param
* @return {@link Connection}
*/
public Connection openConn() throws SQLException {
Connection conn=DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
return conn;
}
/**
* <p>執行sql</p>
* @param sql
* @param con
* @return {@link ResultSet}
*/
public ResultSet executeQuery(String sql ,Connection con) throws SQLException {
ResultSet rs = null;
try {
Statement sm = con.createStatement();
rs = sm.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/**
* <p>關閉數據庫鏈接</p>
* @param con
* @return {@link int}
*/
public int close(Connection con ){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
return 1;
}
/**
* <p>获取数据库中所有表的表名，并添加到列表结构中。</p>
* @param conn
* @return {@link List}
*/
public List getTableNameList(Connection conn) throws SQLException {
DatabaseMetaData dbmd = conn.getMetaData();
//访问当前用户ANDATABASE下的所有表
ResultSet rs = dbmd.getTables("null", "DEMO", "%", new String[] { "TABLE" });
//System.out.println("kkkkkk"+dbmd.getTables("null", "%", "%", new String[] { "TABLE" }));
List tableNameList = new ArrayList();
while (rs.next()) {
tableNameList.add(rs.getString("TABLE_NAME"));
}
return tableNameList;
}
/**
* <p>获取数据表中所有列的列名，并添加到列表结构中。</p>
* @param conn
* @param tableName bl碼
* @return {@link List}
*/
public List<Map<String,Object>> getColumnNameList(Connection conn, String tableName) throws SQLException {
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, "%", tableName, "%");
List<Map<String,Object>> list = new ArrayList();
while (rs.next()) {
Map<String,Object> map = new LinkedHashMap<String,Object>();
System.out.println(rs.getString("COLUMN_NAME")); //String => 列名称
map.put("columnName",rs.getString("COLUMN_NAME"));
System.out.println(rs.getString("TYPE_NAME")); //String => 数据源依赖的类型名称，对于 UDT，该类型名称是完全限定的
map.put("typeName",rs.getString("TYPE_NAME"));
System.out.println(rs.getString("COLUMN_SIZE")); //int => 列的大小
map.put("columnSize",rs.getString("COLUMN_SIZE"));
System.out.println(rs.getString("NULLABLE"));//int => 是否允许使用 NULL。
map.put("nullAble",rs.getString("NULLABLE"));
// System.out.println(rs.getString("COLUMN_DEF"));//默認值
// map.put("columnDef",rs.getString("COLUMN_DEF"));
System.out.println(rs.getString("REMARKS"));//String => 描述列的注释（可为 null）
map.put("remarks",rs.getString("REMARKS"));
list.add(map);
//columnNameList.add(rs.getString("COLUMN_NAME"));
// System.out.println(rs.getString("TABLE_CAT")); //String => 表类别（可为 null）
// System.out.println(rs.getString("TABLE_SCHEM")); //String => 表模式（可为 null）
// System.out.println(rs.getString("TABLE_NAME")); //String => 表名称
// System.out.println(rs.getString("DATA_TYPE")); //int => 来自 java.sql.Types 的 SQL 类型
// System.out.println(rs.getString("BUFFER_LENGTH")); //未被使用。
// System.out.println(rs.getString("DECIMAL_DIGITS"));//int => 小数部分的位数。对于 DECIMAL_DIGITS 不适用的数据类型，则返回 Null。
// System.out.println(rs.getString("NUM_PREC_RADIX"));//int => 基数（通常为 10 或 2
// System.out.println(rs.getString("COLUMN_DEF")); //String => 该列的默认值，当值在单引号内时应被解释为一个字符串（可为 null）
// System.out.println(rs.getString("SQL_DATA_TYPE")); //未使用
// System.out.println(rs.getString("SQL_DATETIME_SUB")); //未使用
// System.out.println(rs.getString("CHAR_OCTET_LENGTH")); //int => 对于 char 类型，该长度是列中的最大字节数
// System.out.println(rs.getString("ORDINAL_POSITION")); //int => 表中的列的索引（从 1 开始）
// System.out.println(rs.getString("IS_NULLABLE")); //int => 对于 char 类型，该长度是列中的最大字节数
// System.out.println(rs.getString("SCOPE_CATLOG")); //String => 表的类别，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null）
// System.out.println(rs.getString("SCOPE_SCHEMA")); //String => 表的模式，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null）
// System.out.println(rs.getString("SCOPE_TABLE")); //String => 表名称，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null）
// System.out.println(rs.getString("SOURCE_DATA_TYPE")); //short => 不同类型或用户生成 Ref 类型、来自 java.sql.Types 的 SQL 类型的源类型（如果 DATA_TYPE 不是 DISTINCT 或用户生成的 REF，则为 null）
// System.out.println(rs.getString("IS_AUTOINCREMENT")); //String => 指示此列是否自动增加
}
return list;
}
}
1、根據獲取的字段名稱生成xlsx
controller
// controller
@GetMapping("/template")
@ApiOperation(value = "Excel 生成")
public void getMoInfo(HttpServletResponse response, String tableName) throws IOException {
//设置默认的下载文件名
String name = tableName + ".xlsx";
//執行方法,把臨時文件的路徑返回給前台
String tempAddr = excelService.createExcelTemplate(tableName.toUpperCase());
//设置响应头的类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//让浏览器下载文件,name是上述默认文件下载名
response.addHeader("Content-Disposition", "attachment;filename=\"" + name + "\"");
InputStream inputStream = null;
OutputStream outputStream = null;
//在service层中已经将数据存成了excel临时文件，并返回了临时文件的路径
String downloadPath = tempAddr;
//根据临时文件的路径创建File对象，FileInputStream读取时需要使用
File file = new File(downloadPath);
try {
//通过FileInputStream读临时文件，ServletOutputStream将临时文件写给浏览器
inputStream = new FileInputStream(file);
outputStream = response.getOutputStream();
int len = -1;
byte[] b = new byte[1024];
while ((len = inputStream.read(b)) != -1) {
outputStream.write(b);
}
//刷新
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭输入输出流
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//最后才能，删除临时文件，如果流在使用临时文件，file.delete()是删除不了的
file.delete();
}
service
/**
* <p>根基表名生成Excel 模板</p>
* @param tableName 表名
* @return {@link String}
*/
String createExcelTemplate(String tableName);
serviceImpl
@Override
public String createExcelTemplate(String tableName) {
Connection con = null;
DataTableUtil dt = null;
try {
dt = new DataTableUtil();
con = dt.openConn();
if (con == null){
System.out.println("连接失败!");
}else{
System.out.println("连接成功!");
}
//List tableList = dt.getTableNameList(con);//取出当前用户的所有表
List<Map<String,Object>> tableList = dt.getColumnNameList(con, tableName);//表名称必须是大写的，取出当前表的所有列
System.out.println(tableList.size());
// 獲取表中所有字段,開始生成Excel
File directory = new File("");// 参数为空
String courseFile = directory.getCanonicalPath();
System.out.println(courseFile);
//创建工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = xssfWorkbook.createSheet();
//設置 sheet 名稱
xssfWorkbook.setSheetName(0,"模板");
// 創建標題行
XSSFRow row = sheet.createRow(0);
CellStyle style = xssfWorkbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
row.setRowStyle(style);
// 開始在首行填入字段信息
for (int i = 0 ;i<tableList.size() ;i++) {
Map<String,Object> item = tableList.get(i);
String cellValue = item.get("columnName")+";"+ item.get("typeName")+ ";"+ item.get("columnSize")
+";"+ item.get("nullAble")+";"+ item.get("remarks");
row.createCell(i).setCellValue(cellValue);
}
//创建临时文件的目录
File file = new File(courseFile);
if(!file.exists()){
file.mkdirs();
}
//临时文件路径/文件名
String downloadPath = file + "\\" +System.currentTimeMillis() + UUID.randomUUID();
OutputStream outputStream = null;
//使用FileOutputStream将内存中的数据写到本地，生成临时文件
outputStream = new FileOutputStream(downloadPath);
xssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();
return downloadPath;
} catch (Exception e) {
dt.close(con);
e.printStackTrace();
} finally {
dt.close(con);
}
return null;
}
實現結果如下圖
-- Create table
create table xxxxxx
(
sn VARCHAR2(400),
va1 NUMBER,
create_date DATE default SYSDATE,
param VARCHAR2(10) default 'ok'
)
tablespace xxxxxx
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
SN;VARCHAR2;400;1;null
SN 為字段名稱 varchar2 為oracle字段類型,400是默認最大長度,1代表可以為空,null代表字段介紹為空
二、下載Excel , 維護並上傳
1、維護數據
2、xlsx上傳解析
controller
@ApiOperation(value = "文件上传", notes = "")
@PostMapping(value = "/upFile")
public JsonResult upFile(@ApiParam(value = "文件", required = true) @RequestParam("file") MultipartFile files,
@ApiParam(value = "表名", required = true) @RequestParam("tableName") String tableName) throws IOException {
JsonResult result = excelService.upFile(files, tableName);
return result;
}
service
/**
* <p>一句話說明方法的功能</p>
* @param files 上傳文件信息
* @param tableName 表名
* @return {@link JsonResult}
*/
JsonResult upFile(MultipartFile files, String tableName) throws IOException;
serviceImpl
@Override
public JsonResult upFile(MultipartFile multiFile, String tableName) throws IOException {
try {
// 获取文件名
String fileName = multiFile.getOriginalFilename();
// 获取文件后缀
String prefix = fileName.substring(fileName.lastIndexOf("."));
if(!".xlsx".equals(prefix)&&!".XLSX".equals(prefix)){
return new JsonResult(550,"請上傳後綴為XLSX或xlsx的excel文檔!");
}
InputStream inputStream = multiFile.getInputStream();
// Workbook 讀取流
Workbook workbook = new XSSFWorkbook(inputStream);
// 獲取sheet 數目
int numberOfSheets1 = workbook.getNumberOfSheets();
// 獲取第一個sheet
String res = sheet1(workbook.getSheetAt(0),tableName);
String sucStr = "OK";
if (sucStr.equals(sucStr)){
return new JsonResult(200,"成功");
}else {
return new JsonResult(550,res);
}
} catch (IOException | SQLException e) {
e.printStackTrace();
return new JsonResult(500,e.toString());
}
}
private String sheet1(Sheet sheet, String tableName) throws SQLException {
// 第一步,先查表
DataTableUtil dt = new DataTableUtil();
Connection con = dt.openConn();
List<Map<String,Object>> columnNameList = dt.getColumnNameList(con, tableName);
// 檢查表正確性
if (columnNameList.size()==0){
return "當前表不存在";
}
// 開始讀取Excel
// 創建基本參數 錯誤信息 errmsg ,插入成功多少行信息
StringBuilder sql = new StringBuilder();
StringBuilder errMsg = new StringBuilder();
int susRows = 0;
int cellNum=0;
// 獲取excel 總行數
int rowNum = sheet.getLastRowNum();
for (int i = 1 ; i <rowNum+1 ; i++){
sql.delete(0,sql.length());
sql.append("insert into ");
sql.append(tableName);
sql.append(" values( ");
Row row = sheet.getRow(i);
cellNum = (int)row.getLastCellNum();
if (columnNameList.size()!=(cellNum)){
errMsg.append("第");
errMsg.append(i+1);
errMsg.append("行數據與基礎表數據不匹配,維護失敗!");
}else {
for (int j = 0; j < columnNameList.size(); j++) {
try {
getCellValue(sql,row.getCell(j)
,columnNameList.get(j).get("typeName").toString()
,columnNameList.get(j).get("columnSize").toString()
,columnNameList.get(j).get("nullAble").toString()
,columnNameList.get(j).get("columnName").toString());
} catch (IOException e) {
e.printStackTrace();
errMsg.append(e.getMessage()+"!");
continue;
}catch (Exception e){
e.printStackTrace();
errMsg.append(e.getMessage()+"!");
}
}
int length = sql.length();
sql.delete(sql.length()-2,sql.length());
sql.append(")");
System.out.println(errMsg);
System.out.println(sql.toString());
String s = sql.toString();
int res = dpmDropdownlistDao.insertBySql(s);
}
}
return tableName;
}
// 解析cell裡面的字符
private void getCellValue(StringBuilder sql, Cell cell, String typeName, String columnSize, String nullAble,String columnName) throws Exception {
if (cell == null){
sql.append("null,");
return;
}
switch(typeName.toUpperCase()){
case "VARCHAR2":
try {
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.length()>Integer.parseInt(columnSize)){
throw new IOException("");
}else{
sql.append("'");
sql.append(cell.getStringCellValue());
sql.append("', ");
}
} catch (Exception e) {
e.printStackTrace();
sql.append("null,");
}
break;
case "NUMBER":
try {
double numericCellValue = cell.getNumericCellValue();
sql.append(numericCellValue);
} catch (Exception e) {
e.printStackTrace();
sql.append(cell.getStringCellValue());
}
sql.append(", ");
break;
case "DATE":
try {
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String format = df.format(cell.getDateCellValue());
sql.append("to_date('");
sql.append(format);
sql.append("','yyyy/MM/dd HH24:mi:ss'), ");
} catch (Exception e) {
e.printStackTrace();
sql.append("null,");
}
break;
default:
sql.append("null,");
break;
}
}
dao
int insertBySql(@Param("strIn") String strIn);
**xml
<insert id="insertBySql" parameterType="string">
${strIn}
</insert>
到此數據上傳成功!