开发者社区> 问答> 正文

从数据库中读取表导出成excel模板

蛮大人123 2016-03-12 10:09:19 873

screenshot
这是数据库中的一张表,根据不同的report_id存放了不同的报表模板,我想根据这个id导出不同的excel,其中table_name作为标题和sheetname,label_id和label_name为第二行和第三行的所有列

BI 数据库
分享到
取消 提交回答
全部回答(1)
  • 蛮大人123
    2019-07-17 19:00:16
    public class ExportSql {
        
        private static String path = "D:\\test\\";
    
        public static void main(String[] args) {
            String sql = "select report_id, label_id, label_name from tableName order by report_id";
            List<List<Object>> listListTable = getTable(sql);
            List<List<Object>> listList = new ArrayList<List<Object>>();
            
            for (int i = 0; i < listListTable.size(); i++) {
                if (i > 1 && !listListTable.get(i).get(0).equals(listListTable.get(i - 1).get(0))) {
                    //跟上一个id不同换个文件
                    exportExcel(listList, listListTable.get(i - 1).get(1).toString());
                    listList.clear();
                    listList.add(listListTable.get(i));
                } else {
                    listList.add(listListTable.get(i));
                }
    
                if (i == listListTable.size() - 1) {
                    exportExcel(listList, listListTable.get(i).get(1).toString());
                }
    
            }
        }
        
        //根据sql获得数据
        public static List<List<Object>> getTable(String sql){
            List<List<Object>> listList = new ArrayList<List<Object>>();
            
            Connection conn = getConnection();
            PreparedStatement pstmt;
            ResultSet rs;
            try {
                pstmt = conn.prepareStatement(sql.trim());
                rs = pstmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
    
                while (rs.next()){
                    List<Object> list = new ArrayList<Object>();
                    for (int i = 0; i < rsmd.getColumnCount(); i++){
                        list.add(rs.getString(rsmd.getColumnName(i+1)));
                    }
                    listList.add(list);
                }
            }catch (Exception e) {
                e.printStackTrace();
            }finally{
                close(conn);
            }
            return listList;
        }
        
        // 生成excel
        public static void exportExcel(List<List<Object>> listList, String tableName){
            listList.remove(1);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(tableName);
    
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(1, true);
            
            for (int i = 0; i < listList.size(); i++){
                Row row = sheet.createRow(i + 4);
                List<Object> list = listList.get(i);
                for (int j = 0; j < list.size(); j++){
                    Cell cell = row.createCell(j);
                    if (list.get(j) != null){
                        cell.setCellValue(list.get(j).toString());
                    }
                }
            }
            
            OutputStream os = null;
            try {
                os = new FileOutputStream(path + tableName + ".xls");
                wb.write(os);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    wb.close();
                    if (os != null) {
                        os.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
    
            }
    
        }
        
        public static Connection getConnection(){
            Connection conn = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/databaseName", "root", "123456");
            } catch (Exception e) {
                e.printStackTrace();
            }
            return conn;
        }
        
        public static void close(Connection conn){
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    正巧前几天写了个类似的工具,希望能帮助你。

    0 0
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程