首先大数据量的下载，一般的Excel下载操作是不可能完成的，会导致内存溢出

　　SXSSFWorkbook 是专门用于大数据了的导出　　

　　这个参数，会指定一个sheet可读取的row数目，超过该数目的row，会被写入到磁盘文件中，进而不能在通过getRow访问到，通过这种方式，内存使用空间就缩小很多了。 需要注意的是，如果该值指定为-1，说明不限制行数，所有记录都写入内存中；该值不能取0，因为这意味着任何新row都会写入磁盘，进而不能访问，但是新row还没来得及createCell

　　当写sheet的时候，临时文件可能远比结果文件要大，所以提供了压缩临时文件的接口，通过压缩，磁盘上的xml的临时文件将会被打包，进而降低磁盘占用空间。

 先上代码再说

  1.  1 /**
  2.  2      * Excel通用下载方法
  3.  3      * @param request
  4.  4      * @param response
  5.  5      * @param fileName
  6.  6      *            文件名
  7.  7      * @param titleNameList
  8.  8      *            标题头部
  9.  9      * @param cellNameList
  10. 10      *               行字段的name，主要用于map的get(),无则为null        
  11. 11      * @param dataList
  12. 12      *            内容   支持类型 对象  map集合
  13. 13      */
  14. 14     public static <E> void downLoad(HttpServletRequest request, HttpServletResponse response, String fileName,
  15. 15             List<String> titleNameList,List<String> cellNameList, List<E> dataList) {
  16. 16         OutputStream os = null;
  17. 17         try {
  18. 18             response.setContentType("application/force-download"); // 设置下载类型
  19. 19         
  20. 20             response.setHeader("Content-Disposition", "attachment;filename=" + fileName); // 设置文件的名称
  21. 21             os = response.getOutputStream(); // 输出流
  22. 22             SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 内存中保留 1000 条数据，以免内存溢出，其余写入 硬盘
  23. 23             // 获得该工作区的第一个sheet
  24. 24             Sheet sheet1 = wb.createSheet("sheet1");
  25. 25             String mapstring=null;
  26. 26             int excelRow = 0;
  27. 27             List<String> fieldNameList = new ArrayList<>();
  28. 28             // 标题行
  29. 29             Row titleRow = (Row) sheet1.createRow(excelRow++);
  30. 30             int titleSize = titleNameList.size();
  31. 31             for (int i = 0; i < titleSize; i++) {
  32. 32                 Cell cell = titleRow.createCell(i);
  33. 33                 cell.setCellValue(titleNameList.get(i));
  34. 34             }
  35. 35             int dataSize = dataList.size();
  36. 36             for (int i = 0; i < dataSize; i++) {
  37. 37                 // 明细行
  38. 38                 Row contentRow = (Row) sheet1.createRow(excelRow++);
  39. 39                 Object object = dataList.get(i);
  40. 40                 if (object instanceof Map) {
  41. 41                     Map<String, Object> objectMap = (Map<String, Object>) object;
  42. 42                     for (int j = 0; j < titleSize; j++) {
  43. 43                         mapstring=objectMap.get(cellNameList.get(j))+"";
  44. 44                         Cell cell = contentRow.createCell(j);
  45. 45                         if (!"".equals(mapstring)&&mapstring!=null&&!"null".equals(mapstring)) {
  46. 46                             cell.setCellValue(mapstring);
  47. 47                         }else {
  48. 48                             cell.setCellValue("");
  49. 49                         }
  50. 50                     }
  51. 51                 } else {
  52. 52                     
  53. 53                     if (i == 0) {
  54. 54                         if (cellNameList!=null&&cellNameList.size()>0) {
  55. 55                             fieldNameList.addAll(cellNameList);
  56. 56                         }else {
  57. 57                         Field[] fields = object.getClass().getDeclaredFields();
  58. 58                         for (Field field : fields) {
  59. 59                             field.setAccessible(true);
  60. 60                             fieldNameList.add(field.getName());
  61. 61                         }
  62. 62                         }
  63. 63                     }
  64. 64                     for (int j = 0; j < titleSize; j++) {
  65. 65                         Cell cell = contentRow.createCell(j);
  66. 66                         Field field = object.getClass().getDeclaredField(fieldNameList.get(j));
  67. 67                         field.setAccessible(true);
  68. 68                         Object fieldObj=field.get(object) ;
  69. 69                         if (fieldObj != null&&!"null".equals(fieldObj)) {
  70. 70                             cell.setCellValue(fieldObj.toString());
  71. 71                         } else {
  72. 72                             cell.setCellValue("");
  73. 73                         }
  74. 74 
  75. 75                     }
  76. 76                     
  77. 77 
  78. 78                 }
  79. 79             }
  80. 80             wb.write(os);
  81. 81         } catch (Exception e) {
  82. 82             e.printStackTrace();
  83. 83         } finally {
  84. 84             try {
  85. 85                 if (os != null) {        
  86. 86                     os.close();
  87. 87                 }
  88. 88             } catch (IOException e) {
  89. 89                 e.printStackTrace();
  90. 90             } // 关闭输出流
  91. 91         }
  92. 92 
  93. 93     }

 

读取大数据的Excel文件

 

直接上代码 

  1. public class SheetHandler extends DefaultHandler {
  2.     /**
  3.      * 单元格中的数据可能的数据类型
  4.      */
  5.     enum CellDataType {
  6.         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
  7.     }
  9.     /**
  10.      * 共享字符串表
  11.      */
  12.     private SharedStringsTable sst;
  14.     /**
  15.      * 上一次的索引值
  16.      */
  17.     private String lastIndex;
  19.     /**
  20.      * 文件的绝对路径
  21.      */
  22.     private String filePath = "";
  24.     /**
  25.      * 工作表索引
  26.      */
  27.     private int sheetIndex = 0;
  29.     /**
  30.      * sheet名
  31.      */
  32.     private String sheetName = "";
  34.     /**
  35.      * 总行数
  36.      */
  37.     private int totalRows=0;
  39.     /**
  40.      * 一行内cell集合
  41.      */
  42.     private List<String> cellList = new ArrayList<String>();
  44.     /**
  45.      * 判断整行是否为空行的标记
  46.      */
  47.     private boolean flag = false;
  49.     /**
  50.      * 当前行
  51.      */
  52.     private int curRow = 1;
  54.     /**
  55.      * 当前列
  56.      */
  57.     private int curCol = 0;
  59.     /**
  60.      * T元素标识
  61.      */
  62.     private boolean isTElement;
  64.     /**
  65.      * 异常信息，如果为空则表示没有异常
  66.      */
  67.     private String exceptionMessage;
  69.     /**
  70.      * 单元格数据类型，默认为字符串类型
  71.      */
  72.     private CellDataType nextDataType = CellDataType.SSTINDEX;
  74.     private final DataFormatter formatter = new DataFormatter();
  76.     /**
  77.      * 单元格日期格式的索引
  78.      */
  79.     private short formatIndex;
  81.     /**
  82.      * 日期格式字符串
  83.      */
  84.     private String formatString;
  86.     //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
  87.     private String preRef = null, ref = null;
  89.     //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
  90.     private String maxRef = null;
  91.     
  92.     private int maxCol=0;
  93.     
  94.     private int nowcol;
  95.     /**
  96.      * 单元格
  97.      */
  98.     private StylesTable stylesTable;
  99.     
  100.     /**
  101.      * 日期格式化 yyyy-MM-dd
  102.      */
  103.     private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
  104.     /**
  105.      * 日期格式化
  106.      */
  107.     private SimpleDateFormat sdf2=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  108.     
  109.     /**
  110.      * 现在的时间
  111.      */
  112.     private String dateTime=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
  113.     /**
  114.      * 一个表格的所有数据
  115.      */
  116.     private List<List<String>> sheetList=new  ArrayList<>();
  117.     /**
  118.      * 操作人员
  119.      */
  120.     private String userName;
  121.     
  122.     private static int startElements=0;
  123.     
  124.     private static int endElement=0;
  125. /*    public static void main(String[] args) {
  126.         SheetHandler sheetHandler=new SheetHandler();
  127.         File file=new File("C:\\Users\\Administrator\\Desktop\\jichu.xlsx");
  128.         
  129.         try {
  130.             sheetHandler.process(new FileInputStream(file), "周光林");
  131.         } catch (FileNotFoundException e) {
  132.             // TODO Auto-generated catch block
  133.             e.printStackTrace();
  134.         } catch (Exception e) {
  135.             // TODO Auto-generated catch block
  136.             e.printStackTrace();
  137.         }
  138.     }*/
  139.     /**
  140.      * 遍历工作簿中所有的电子表格
  141.      * 并缓存在mySheetList中
  142.      *
  143.      * @param filename
  144.      * @throws Exception
  145.      */
  146.     public List<List<String>> process(InputStream in,String userName) throws Exception {
  147.         this.userName=userName;
  148.         OPCPackage pkg = OPCPackage.open(in);
  149.         
  150.         XSSFReader xssfReader = new XSSFReader(pkg);
  151.         stylesTable = xssfReader.getStylesTable();
  152.         SharedStringsTable sst = xssfReader.getSharedStringsTable();
  153.         XMLReader parser = XMLReaderFactory.createXMLReader();
  154.         this.sst = sst;
  155.         parser.setContentHandler(this);
  156.         XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
  157.         while (sheets.hasNext()) { //遍历sheet
  158.             curRow = 1; //标记初始行为第一行
  159.             sheetIndex++;
  160.             InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
  161.             sheetName = sheets.getSheetName();
  162. //            System.err.println(new BufferedReader(new InputStreamReader(sheet)).readLine());
  163.             InputSource sheetSource = new InputSource(sheet);
  164.             parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
  165.             sheet.close();
  166.         }
  167.         if (in!=null) {
  168.             in.close();
  169.         }
  170.         
  171.         return sheetList; //返回该excel文件的总行数，不包括首列和空行
  172.     }
  174.     /**
  175.      * 第一个执行
  176.      *
  177.      * @param uri
  178.      * @param localName
  179.      * @param name
  180.      * @param attributes
  181.      * @throws SAXException
  182.      */
  183.     @Override
  184.     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
  185.         //c => 单元格
  186.         if ("c".equals(name)) {
  187.             //当前单元格的位置
  188.             ref = attributes.getValue("r");
  189.             //设定单元格类型
  190.             this.setNextDataType(attributes);
  191.         }
  192.         //当元素为t时
  193.         if ("t".equals(name)) {
  194.             isTElement = true;
  195.         } else {
  196.             isTElement = false;
  197.         }
  199.         //置空
  200.         lastIndex = "";
  201.     }
  203.     /**
  204.      * 第二个执行
  205.      * 得到单元格对应的索引值或是内容值
  206.      * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
  207.      * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
  208.      * @param ch
  209.      * @param start
  210.      * @param length
  211.      * @throws SAXException
  212.      */
  213.     @Override
  214.     public void characters(char[] ch, int start, int length) throws SAXException {
  215.         lastIndex += new String(ch, start, length);
  216.     }
  218.     /**
  219.      * 第三个执行
  220.      *
  221.      * @param uri
  222.      * @param localName
  223.      * @param name
  224.      * @throws SAXException
  225.      */
  226.     @Override
  227.     public void endElement(String uri, String localName, String name) throws SAXException {
  228.         //t元素也包含字符串
  229.         if (isTElement) {//这个程序没经过
  230.             //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
  231.             String value = lastIndex.trim();
  232.             cellList.add(curCol, value);
  233.             curCol++;
  234.             isTElement = false;
  235.             //如果里面某个单元格含有值，则标识该行不为空行
  236.             if (value != null && !"".equals(value)) {
  237.                 flag = true;
  238.             }
  239.         } else if ("v".equals(name)) {
  240.             
  241.             //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
  242.             String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
  243.             //补全单元格之间的空单元格
  244.             if (preRef!=null) {
  245.             if (!ref.equals(preRef)) {
  246.                 int len = countNullCell(ref, preRef);
  247.                 for (int i = 0; i < len; i++) {
  248.                     
  249.                     cellList.add(curCol, "");
  250.                     curCol++;
  251.                 }
  252.             }
  253.             }
  254.                 preRef=ref;
  255.             
  256.             
  257.             cellList.add(curCol, value);
  258.             curCol++;
  259.             //如果里面某个单元格含有值，则标识该行不为空行
  260.             if (value != null && !"".equals(value)) {
  261.                 flag = true;
  262.             }
  263.         } else {
  264.             //如果标签名称为row，这说明已到行尾，调用optRows()方法
  265.             if ("row".equals(name)) {
  266.                 //默认第一行为表头，以该行单元格数目为最大数目
  267.                 if (curRow == 1) {
  268.                     maxRef = ref;
  269.                     maxCol=curCol;
  270.                 }
  271.                 //补全一行尾部可能缺失的单元格
  272.                 if (maxRef != null) {
  273.                     int len = countNullCell(maxRef, ref);
  274.                     for (int i = 0; i <= len; i++) {
  275.                         cellList.add(curCol, "");
  276.                         curCol++;
  277.                     }
  278.                 }
  279.                 if (flag&&curRow!=1){ //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
  280.                     nowcol=cellList.size();
  281.                     if (cellList.size()<maxCol) {
  282.                         for (int i = 0; i <maxCol-nowcol; i++) {
  283.                             cellList.add("");
  284.                         }
  285.                     }
  286.                     if (nowcol>maxCol) {
  288.                         for (int i = nowcol-1; i >maxCol-1; i--) {
  289.                             cellList.remove(i);
  290.                         }
  291.                     }
  292.                     cellList.add(userName);
  293.                     cellList.add(dateTime);
  294.                 
  295.                     sheetList.add(new ArrayList<>(cellList));
  296.                     totalRows++;
  297.                 }
  298.                 
  299.                 cellList.clear();
  300.                 curRow++;
  301.                 curCol = 0;
  302.                 preRef = null;
  303.                 ref = null;
  304.                 flag=false;
  305.             }
  306.         }
  307.     }
  309.     /**
  310.      * 处理数据类型
  311.      *
  312.      * @param attributes
  313.      */
  314.     public void setNextDataType(Attributes attributes) {
  315.         nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
  316.         formatIndex = -1;
  317.         formatString = null;
  318.         String cellType = attributes.getValue("t"); //单元格类型
  319.         String cellStyleStr = attributes.getValue("s"); //
  320.         String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
  321.  
  322.         if ("b".equals(cellType)) { //处理布尔值
  323.             nextDataType = CellDataType.BOOL;
  324.         } else if ("e".equals(cellType)) {  //处理错误
  325.             nextDataType = CellDataType.ERROR;
  326.         } else if ("inlineStr".equals(cellType)) {
  327.             nextDataType = CellDataType.INLINESTR;
  328.         } else if ("s".equals(cellType)) { //处理字符串
  329.             nextDataType = CellDataType.SSTINDEX;    
  330.         } else if ("str".equals(cellType)) {
  331.             nextDataType = CellDataType.FORMULA;
  332.         }
  334.         if (cellStyleStr != null) { //处理日期
  335.             int styleIndex = Integer.parseInt(cellStyleStr);
  336.             XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
  337.             formatIndex = style.getDataFormat();
  338.             formatString = style.getDataFormatString();
  339.         
  340.             if (formatString == null) {
  341.                 nextDataType = CellDataType.NULL;
  342.                 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
  343.             }else if (formatString.contains("m/d/yy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d")) {
  344.                 nextDataType = CellDataType.DATE;
  345.                 formatString = "yyyy-MM-dd";
  346.             }
  347.         }
  348.     }
  350.     /**
  351.      * 对解析出来的数据进行类型处理
  352.      * @param value   单元格的值，
  353.      *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
  354.      *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
  355.      * @param thisStr 一个空字符串
  356.      * @return
  357.      */
  358.     @SuppressWarnings("deprecation")
  359.     public String getDataValue(String value, String thisStr) {
  360.         
  361.         switch (nextDataType) {
  362.             // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
  363.             case BOOL: //布尔值
  364.                 char first = value.charAt(0);
  365.                 thisStr = first == \'0\' ? "FALSE" : "TRUE";
  366.                 break;
  367.             case ERROR: //错误
  368.                 thisStr = value.toString();
  369.                 
  370.                 break;
  371.             case FORMULA: //公式
  372.                 thisStr = \'"\' + value.toString() + \'"\';
  373.                 
  374.                 break;
  375.             case INLINESTR:
  376.                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
  377.                 thisStr = rtsi.toString();
  378.                 rtsi = null;
  379.                 break;
  380.             case SSTINDEX: //字符串
  381.                 String sstIndex = value.toString();
  382.                 try {
  383.                     int idx = Integer.parseInt(sstIndex);
  384.                     XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
  385.                     thisStr = rtss.toString();
  386.                     rtss = null;
  387.                 } catch (NumberFormatException ex) {
  388.                     thisStr = value.toString();
  389.                 }
  390.                 
  391.                 break;
  392.             case NUMBER: //数字
  393.                 if (formatString != null) {
  394.                     thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
  395.                 } else {
  396.                     thisStr = value;
  397.                 }
  398.                 
  399.                 thisStr = thisStr.replace("_", "").trim();
  400.                 break;
  401.             case DATE: //日期
  402.                 try {
  403.                 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
  404.                 }catch (Exception e) {
  405.                     thisStr="0000-00-00";
  406.                 }
  407.                 
  408.                 // 对日期字符串作特殊处理，去掉T
  409.                 thisStr = thisStr.replace("T", " ");
  410.                 break;
  411.             default:
  412.                 if (Integer.parseInt(value)>20000) {
  413.                      Date date = DateUtil.getJavaDate(Double.parseDouble(value));
  414.                     thisStr =sdf.format(date);
  415.                 }else {
  416.                     thisStr="11111";
  417.                 }
  418.                 
  419.                 break;
  420.         }
  421.         return thisStr;
  422.     }
  424.     public int countNullCell(String ref, String preRef) {
  425.         //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
  426.         String xfd = ref.replaceAll("\\d+", "");
  427.         String xfd_1 = preRef.replaceAll("\\d+", "");
  429.         xfd = fillChar(xfd, 3, \'@\', true);
  430.         xfd_1 = fillChar(xfd_1, 3, \'@\', true);
  432.         char[] letter = xfd.toCharArray();
  433.         char[] letter_1 = xfd_1.toCharArray();
  434.         int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
  435.         return res - 1;
  436.     }
  438.     public String fillChar(String str, int len, char let, boolean isPre) {
  439.         int len_1 = str.length();
  440.         if (len_1 < len) {
  441.             if (isPre) {
  442.                 for (int i = 0; i < (len - len_1); i++) {
  443.                     str = let + str;
  444.                 }
  445.             } else {
  446.                 for (int i = 0; i < (len - len_1); i++) {
  447.                     str = str + let;
  448.                 }
  449.             }
  450.         }
  451.         return str;
  452.     }
  454.     /**
  455.      * @return the exceptionMessage
  456.      */
  457.     public String getExceptionMessage() {
  458.         return exceptionMessage;
  459.     }
  460. }
  1. SheetHandler sheetHandler=new SheetHandler(); 
  3. List<List<String>> lists=sheetHandler.process(in,userName);

 

版权声明：本文为zhouguanglin原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/zhouguanglin/p/9959344.html