首页 > 代码库 > 大数据导入EXCEL
大数据导入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）：
所以这种方式，也有其弊端。
大数据导入EXCEL