百万数据读写到excel

今天药忘吃喽~ 2021-12-12 14:25 240阅读 0赞

这里采用 SXSSFWorkbook

public static void main(String[] args) throws Exception {
           
            BathDownload bathDownload = new BathDownload();
         
            String xlsFile = "d:/xlsx/poiSXXFSBigData" + "2019010" + ".xlsx";
            bathDownload.exportAmountExcelData(xlsFile);
        }

/**
         * 百万数据量读写
         *
         * @throws IOException
         */
        public void exportAmountExcelData(String path)
                throws IOException, ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
            SXSSFWorkbook wb = new SXSSFWorkbook(100);            //关键语句
            Sheet sheet = null;        //工作表对象
            int rowNo = 0;        //总行号
            int pageRowNo = 0;    //页行号
    
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/posp?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
            String user = "java";
            String password = "Akz6r0vc3CfHOFuV";
            //获取数据库连接
            Connection conn = DriverManager.getConnection(url, user, password);
            PreparedStatement stmt = conn.prepareStatement("");
    
            long startTime = System.currentTimeMillis();    //开始时间
            System.out.println("strat execute time: " + startTime);
    
            String queryCount = "select count(*) from xingye_bill_download_day_101590267206_20190602";
            ResultSet rs_total = stmt.executeQuery(queryCount);
    
            rs_total.next();
            int list_count = rs_total.getInt(1);
            System.out.println("*** 总记录数 count ** " + list_count);
           
            List<XingYeBill> list = new ArrayList();
            int page_size = 10000;// 数据库中存储的数据行数
    
    
            int export_times = list_count % page_size > 0 ? list_count / page_size
                    + 1 : list_count / page_size;
    
            System.out.println(" *** export_times ****  " + export_times);
    
            for (int j = 0; j < export_times; j++) {
                String listSql = "select merchant_num,trace_num,total_fee from xingye_bill_download_day_101590267206_20190602 "
                        +"WHERE  id > "+j * page_size+" ORDER BY id ASC  LIMIT  "+page_size+" ";
                System.out.println("******** listSql ******** " + listSql);
                ResultSet rs = stmt.executeQuery(listSql);
                while (rs.next()) {
                    XingYeBill usera = new XingYeBill();
                    usera.setTrace_num(rs.getString("trace_num"));
                    usera.setMerchant_num(rs.getString("merchant_num"));
                    usera.setTotal_fee(Double.valueOf(rs.getString("total_fee")));
                    list.add(usera);
                }
    
                // 打印每行，每行有6列数据   rsmd.getColumnCount()==6 --- 列属性的个数
                int len = list.size() < page_size ? list.size() : page_size;
                for (int i = 0; i < len; i++) {
                    if (rowNo % 100000 == 0) {
                        System.out.println("Current Sheet:" + rowNo / 100000);
                        sheet = wb.createSheet("我的第" + (rowNo / 100000) + "个工作簿");//建立新的sheet对象
                        sheet = wb.getSheetAt(rowNo / 100000);        //动态指定当前的工作表
                        pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                    }
                    rowNo++;
                    Row row_value = sheet.createRow(pageRowNo++);
                    Cell cel0_value = row_value.createCell(0);
                    cel0_value.setCellValue(list.get(i).getMerchant_num());
                    Cell cel2_value = row_value.createCell(1);
                    cel2_value.setCellValue(list.get(i).getTrace_num());
                    Cell cel3_value = row_value.createCell(2);
                    cel3_value.setCellValue(list.get(i).getTotal_fee());
    
                    if (rowNo % 10000 == 0) {
                        System.out.println("row no: " + rowNo);
                    }
                }
                if (rowNo % 10000 == 0) {
                    System.out.println("row no: " + rowNo);
                }
                list.clear();
            }
            FileOutputStream fileOut = new FileOutputStream(path);
            wb.write(fileOut);
            fileOut.close();
            wb.dispose();
            System.out.println("finish execute time: " + (System.currentTimeMillis() - startTime) / 1000 + " s");
        }