大数据导入EXCEL

    大数据导入实现过程中，出现最常见的两个问题：超出行数限制和内存溢出！

    18天的数据，总共是500w条，如何将500w条记录存入excel中，我当时想过两种实现方式：PLSQL DEVELOPER和Java poi！

    PLSQL DEVELOPER

    有两种实现方法：

    1、在新建一个SQL WINDOW，执行你要导出数据的查询语句，查询完之后，在结果显示的地方点击向下的箭头，让它全部显示，这可能需要一点点时间，显示结束后，右键点击显示结果的地方，选中 copy to excel（xls和xlsx，前者是03及以前版本，每个sheet只能显示65535条记录；后者是07及以后版本每个sheet可以显示1048576条记录）。

    2、在新建一个REPORT WINDOW，执行你要导出数据的查询语句，查询完之后，点击屏幕右边绿色圆饼状图标（export results），后面的操作就很简单了，不再废话。

    两种方式比较实现起来简单，易操作，但是有很严重的弊端：其一，PLSQL DEVELOPER一次导出excel数据有限，只有几十万条，超出范围，则内存溢出；其二，如果分页查询或者条件查询，则分批的数据又不能导入同一个excel中。挺痛苦的~

    在简单方法行不通的时候，只能走向更加复杂的程序之路...

    Java poi

    在使用Java poi之前，尝试过JXL，但是个人觉得Java poi更加顺手，这并不是说JXL不好用，JXL更多地面向的是底层，比较麻烦点，但更加灵活；而Java poi封装地更多，使用起来更加顺手。

    其实这些都不是重点！

    重点是在实现过程中如何处理上面两个最常见的问题：超出行数限制和内存溢出！

    内存溢出：

    一个经常处理大数据，公司硬件却跟不上的软肋，真心耗费时间！最常用的解决途径就是分批处理，结合Java 虚拟机观察一次处理中在不导致内存溢出的前提下，最大能处理的数据量，以达到虚拟机的充分利用。

    在oracle查询数据这一段，写个分页查询，分页查询完后，都放入到一个集合中，具体实现过程，暂且不表！

    超出行数限制：

    如果将oracle中查询出500w数据一股脑儿导入excel，又会遇到另一个棘手问题：超出行数限制。

    如果到的是xls格式，我就让程序循环跑起来，循环一次，导入65535条；xlsx格式的，就让它循环一次，导入1048576条，如此循环下去，直到程序跑完！

    请看本人代码示例：  

public class XlsDto2Excel {  
    @Autowired
    private ToDBDao toDBDao;
    /**
     *
     * @param xls
     * XlsDto实体类的一个对象
     * @throws Exception
     * 在导入Excel的过程中抛出异常
     */
    public void toExcel(String date, int count) {

        int PAGESIZE = 65535;
        // declare a new workbook 声明一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();

        // declare a row object reference 声明一个新行
        HSSFRow r = null;

        // declare a cell object reference 声明一个单元格
        HSSFCell c0, c1, c2, c3, c4, c5 = null;

        HSSFCell[] firstcell = new HSSFCell[6];

        // create 2 cell styles 创建2个单元格样式
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFCellStyle cs2 = wb.createCellStyle();

        // create 2 fonts objects 创建2个单元格字体
        HSSFFont f = wb.createFont();
        HSSFFont f2 = wb.createFont();

        // Set font 1 to 12 point type, blue and bold 设置字体类型1到12号，蓝色和粗体
        f.setFontHeightInPoints((short) 12);
        f.setColor(HSSFColor.RED.index);
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // Set font 2 to 10 point type, red and bold 设置字体类型2到10号，黑色和粗体
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(HSSFColor.BLACK.index);
        f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // Set cell style and formatting 设置单元格样式和格式
        cs.setFont(f);
        // 水平布局：居中
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // cs.setDataFormat(df.getFormat("#,##0.0"));

        // Set the other cell style and formatting 设置其他单元格样式和格式
        cs2.setBorderBottom(cs2.BORDER_THIN);
        cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        cs2.setFont(f2);
        // 水平布局：居中
        cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 从数据库，获取总的集合大小
        List list = this.toDBDao.selectFatherData(date);

        // 获取循环次数（在此结果上+1），一次循环下，子循环PAGESIZE次，最后一次循环，子循环mod次
        int circleCount = list.size() / PAGESIZE;

        int mod = list.size() % PAGESIZE;

        String firstOrderId = "";

        String orderTime = "";

        for (int i = 0; i < circleCount + 1; i++) {
          
            // create a new sheet 创建一个新工作表，但一个sheet加载满65535条记录后，自动生成一个新的sheet，以保证不会超出行数限制
            HSSFSheet sheet = wb.createSheet("第" + i+ "页");
            /*
             * 设置表头
             */
            HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
            
            String[] names = new String[6];
            names[0] = "访问编号";
            names[1] = "浏览数";
            names[2] = "平均访问时长";
            names[3] = "订单编号";
            names[4] = "下单时间";
            names[5] = "初始时间";

            for (int j = 0; j < 6; j++) {

                firstcell[j] = firstrow.createCell(j);
                firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
                firstcell[j].setCellStyle(cs2);
            }
            //最后一次循环
            if (i == circleCount) {
                
                for (int rownum = 1; rownum < mod; rownum++) {

                    // 获取行对象
                    r = sheet.createRow(rownum);

                    HashMap father = (HashMap) list.get(rownum + PAGESIZE * i);

                    for (int cellnum = 0; cellnum < 6; cellnum++) {

                        /*
                         * 获取列对象
                         */
                        c0 = r.createCell(0);
                        c1 = r.createCell(1);
                        c2 = r.createCell(2);
                        c3 = r.createCell(3);
                        c4 = r.createCell(4);
                        c5 = r.createCell(5);
                        /*
                         * 给列对象赋值
                         */
                        c0.setCellValue(father.get("sessionId").toString());
                        c0.setCellStyle(cs2);

                        c1.setCellValue(father.get("visitPages").toString());
                        c1.setCellStyle(cs2);

                        c2.setCellValue(father.get("perVisitsTime").toString());
                        c2.setCellStyle(cs2);

                        if (null != father.get("firstOrderId")) {

                            firstOrderId = father.get("firstOrderId")
                                    .toString();

                        }
                        c3.setCellValue(firstOrderId);
                        c3.setCellStyle(cs2);

                        if (null != father.get("orderTime")) {

                            orderTime = father.get("orderTime").toString();

                        }
                        c4.setCellValue(orderTime);
                        c4.setCellStyle(cs2);

                        c5.setCellValue(father.get("initTime").toString());
                        c5.setCellStyle(cs2);

                    }

                }

            } else {

                for (int rownum = 1; rownum <= PAGESIZE; rownum++) {

                    // 获取行对象
                    r = sheet.createRow(rownum);

                    HashMap father = (HashMap) list.get(rownum + PAGESIZE * i);

                    for (int cellnum = 0; cellnum < 6; cellnum++) {

                        /*
                         * 获取列对象
                         */
                        c0 = r.createCell(0);
                        c1 = r.createCell(1);
                        c2 = r.createCell(2);
                        c3 = r.createCell(3);
                        c4 = r.createCell(4);
                        c5 = r.createCell(5);
                        /*
                         * 给列对象赋值
                         */
                        c0.setCellValue(father.get("sessionId").toString());
                        c0.setCellStyle(cs2);

                        c1.setCellValue(father.get("visitPages").toString());
                        c1.setCellStyle(cs2);

                        c2.setCellValue(father.get("perVisitsTime").toString());
                        c2.setCellStyle(cs2);

                        if (null != father.get("firstOrderId")) {

                            firstOrderId = father.get("firstOrderId")
                                    .toString();

                        }
                        c3.setCellValue(firstOrderId);
                        c3.setCellStyle(cs2);

                        if (null != father.get("orderTime")) {

                            orderTime = father.get("orderTime").toString();

                        }
                        c4.setCellValue(orderTime);
                        c4.setCellStyle(cs2);

                        c5.setCellValue(father.get("initTime").toString());
                        c5.setCellStyle(cs2);

                    }
                }

            }

            sheet.autoSizeColumn((short) 0); // 根据内容调整第一列宽度，不过不设置，默认情况下，按照表头自动调整宽度
            sheet.autoSizeColumn((short) 4); // 根据内容调整第五列宽度，不过不设置，默认情况下，按照表头自动调整宽度
            sheet.autoSizeColumn((short) 5); // 根据内容调整第六列宽度，不过不设置，默认情况下，按照表头自动调整宽度
        }
        // Save保存
        FileOutputStream out;
        try {
            out = new FileOutputStream("d://workbook.xls");
            wb.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("--执行完毕--");

    }
}

    以上代码导入的是xls格式。

    说明：虽然，用xlsx格式方式可以一次导入100w条记录，但是代码执行要慢上许多，我也没搞清楚具体原因。不过，大家可以在导入同样多数据情况下，用xls和xlsx两种方式对比一下。

    其实除了以上两种方式之外，经本人查找资料，还找到了另一个更加方便的方式，那就是搭建数据源ODBC，连接excel，数据传输。

    如何搭建oracle ODBC，网上资料一查一大把，这里我就不再赘述！

    搭建完之后，我们就来操作excel，新建一个excel，07版的更好，选中数据。

    

    选择来自数据连接向导—>其他/高级—>选择带有oracle的选项，接着输入用户名、密码和连接地址，连接成功后，会显示oracle中的所有表名，注意这些表明排列是没有顺序的，但是我们可以快速索引到我们想要导出数据的表名，比如一个表名叫做father_user_behavior，我们可以这样定位：先按f，再按a，再按t，这样基本上可以直接定位到你想要的表名，接下来的操作就很简单了，不多说！

    需要注意的是，当数据超出1048576条，同样会出现问题（它不会自动生成第二个sheet）：

    

   所以这种方式，也有其弊端。