百万数据读写到excel

最新推荐文章于 2024-07-02 15:25:22 发布

不要做发抖的小喵喵

最新推荐文章于 2024-07-02 15:25:22 发布

阅读量455

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/qq_22899021/article/details/95599301

版权

java 专栏收录该内容

31 篇文章 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");
    }