鳄鱼笔记(三) --> java<--[java讀取oracle表字段,生成XSSFWorkbook xlsx]

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代表字段介紹為空鳄鱼笔记(三) --> java<--[java讀取oracle表字段,生成XSSFWorkbook xlsx]

二、下載Excel , 維護並上傳

1、維護數據

鳄鱼笔记(三) --> java<--[java讀取oracle表字段,生成XSSFWorkbook xlsx]

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>

到此數據上傳成功!

上一篇:xlsx-style导出excel


下一篇:matlab数据处理-mat\scope\xlsx\txt格式(电机控制类)