将数据库字段转换为excel表格

将数据库字段转换为excel表格

以前写个一个使用了map做为数据的封装,总觉得效率低,后来换了个新思路,使用jdbc+list的方式.
因为数据库的数据是规范的,有顺序的,所以使用list完全没有问题.
将excel写入数据库请看: https://blog.csdn.net/sqlgao22/article/details/100082382

pom依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!--操作xls类型文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--操作xlsx类型文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

准备数据库表

还有表的注释,作为excel表的表头

使用的DB工具类

封装了使用的操作数据库的功能,
获取表的字段的注释;
获取表的数据封装为list
获取表的数据封装为map
详细的解释都在代码注释上.

public class DBUtil {
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String ip = "192.168.25.25";
    private static String port = "3306";
    private static String db = "dev";
    private static String name = "root";
    private static String password = "112233";
    public static String tableName = "t_user";

    private static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(generateDBUrl(ip, port, db), name, password);
        } catch (ClassNotFoundException e) {
            System.out.println("can not load jdbc driver" + e);
        } catch (SQLException e) {
            System.out.println("get connection failure" + e);
        }
        return conn;
    }

    /**
     * 生成一个连接url
     * @param ip     地址
     * @param port   端口
     * @param dbname 数据库名
     * @return
     */
    private static String generateDBUrl(String ip, String port, String dbname) {
        StringBuilder sb = new StringBuilder();
        sb.append("jdbc:mysql://").append(ip);
        sb.append(":").append(port);
        sb.append("/").append(dbname);
        //sb.append("?useUnicode=true&characterEncoding=utf-8&autoreconnect=true&useSSL=false");
        sb.append("?useUnicode=true&characterEncoding=UTF-8&autoreconnect=true&useSSL=false&rewriteBatchedStatements=true&serverTimezone=GMT%2B8");
        return sb.toString();
    }

    /**
     * 关闭数据库连接
     * @param conn
     */
    private static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取表中字段的所有注释
     * @return
     */
    public static List<String> getColumnComments() {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = "SELECT * FROM " + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
            //去掉第一列的id
            columnComments.remove(0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnComments;
    }

    /**
     * 获取数据库表中的数据,查询出来的每一行封装成一个list
     * @param selectSQL
     * @return
     */
    public static List<List<String>> selectRowToList(String selectSQL) {
        List<List<String>> result = new ArrayList<List<String>>();
        Connection conn = getConnection();
        PreparedStatement pst = null;

        try {
            pst = (PreparedStatement) conn.prepareStatement(selectSQL);
            ResultSet rs = pst.executeQuery();
            //获取元数据
            ResultSetMetaData rmd = rs.getMetaData();
            //获取总的列数
            int columnCount = rmd.getColumnCount();
            while (rs.next()) {
                List<String> row = new ArrayList<String>();
                //从第一列开始去掉id列
                for (int i = 1; i < columnCount; i++) {
                    row.add(rs.getObject(i+1).toString());
                }
                result.add(row);
            }
        } catch (Exception e) {
            System.out.println("execute selectSQL failure ");
        } finally {
            closeConnection(conn);
        }
        return result;
        /*1   周星驰   45
          2   刘德华   44*/
    }

    /**
     * 按字段别名拼装结果,每行数据返回一个map
     * @return
     */
    public static List<Map<String, Object>> selectRowToMap(String selectSQL) {
        Connection conn = getConnection();
        PreparedStatement pst = null;
        // 定义一个list用于接受数据库查询到的内容
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            pst = (PreparedStatement) conn.prepareStatement(selectSQL);
            ResultSet rs = pst.executeQuery();
            //获取元数据
            ResultSetMetaData rmd = rs.getMetaData();
            //获取总的列数
            int columnCount = rmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rsMap = new LinkedHashMap<String, Object>();
                //从1列开始,去掉id列
                for (int i = 1; i < columnCount; i++) {
                    //循环每一列，并取得该列的值，以别名方式
                    rsMap.put(rmd.getColumnLabel(i+1), rs.getString(i+1));
                }
                list.add(rsMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        return list;
        /* id=====1   name=====周星驰   age=====45
          id=====2   name=====刘德华   age=====44*/
    }
}

操作excel的工具类

比较简单,请看注释即可.

public class POIUtil {
    public static void writeExcel(List<String> titleList,List<List<String>> dataList, String filePath) throws Exception {
        //创建文件
        File path = new File(filePath);
        if (!path.exists()) {
            path.mkdirs();
        }
        String fileName = System.currentTimeMillis() + ".xlsx";
        File file = new File(path, fileName);

        //创建一个excel对象
        Workbook workbook = new XSSFWorkbook();
        //创建第一个工作簿
        Sheet sheet = workbook.createSheet("dbName");
        //将数据库字段（map的key）写入第一行
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < titleList.size(); i++) {
            //创建单元格并写入数据
            Cell cell = row0.createCell(i);
            cell.setCellValue(titleList.get(i).toString());
        }

        //将数据写入其他行
        for (int i = 0; i < dataList.size(); i++) {
            List<String> rowList = dataList.get(i);
            //控制有多少行
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < rowList.size(); j++) {
                //控制列数
                Cell cell = row.createCell(j);
                String o = rowList.get(j);
                //除掉空值,也可以写入对应类型,此处直接写入string
                cell.setCellValue(null==o?"":o);
            }
        }

        //输出文件
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
    }
}

测试

    @Test
    public void test04() throws Exception {
        //获取表头
        List<String> titleList = DBUtil.getColumnComments();
        //获取数据
        String sql = "select * from " + DBUtil.tableName + " limit 10";
        List<List<String>> lists = DBUtil.selectRowToList(sql);
        //写入表格
        POIUtil.writeExcel(titleList,lists,"d:\\11");
    }

数据已经写入了