idea 2019.3.3
maven 3.6.1
springoot 2.4.3
poi 5.0.0
<?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>
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; } }
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代表字段介紹為空
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>
到此數據上傳成功!