{"id":10008028,"date":"2023-07-04T09:59:26","date_gmt":"2023-07-04T01:59:26","guid":{"rendered":"https:\/\/ityet.com\/?p=10008028"},"modified":"2023-07-04T09:59:26","modified_gmt":"2023-07-04T01:59:26","slug":"%e5%bd%93%e4%bd%bf%e7%94%a8poi%e6%89%93%e5%bc%80excel%e6%96%87%e4%bb%b6%e9%81%87%e5%88%b0out-of-memory%e6%97%b6%e8%af%a5%e5%a6%82%e4%bd%95%e5%a4%84%e7%90%86%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10008028","title":{"rendered":"\u5f53\u4f7f\u7528POI\u6253\u5f00Excel\u6587\u4ef6\u9047\u5230out of memory\u65f6\u8be5\u5982\u4f55\u5904\u7406\uff1f"},"content":{"rendered":"

\u6458\u8981\uff1a\u672c\u6587\u7531\u8461\u8404\u57ce\u6280\u672f\u56e2\u961f\u4e8e\u601d\u5426\u539f\u521b\u5e76\u9996\u53d1\u3002\u8f6c\u8f7d\u8bf7\u6ce8\u660e\u51fa\u5904\uff1a\u8461\u8404\u57ce\u5b98\u7f51<\/a>\uff0c\u8461\u8404\u57ce\u4e3a\u5f00\u53d1\u8005\u63d0\u4f9b\u4e13\u4e1a\u7684\u5f00\u53d1\u5de5\u5177\u3001\u89e3\u51b3\u65b9\u6848\u548c\u670d\u52a1\uff0c\u8d4b\u80fd\u5f00\u53d1\u8005\u3002<\/p><\/blockquote>\n

\u5f53\u6211\u4eec\u5f00\u53d1\u5904\u7406Excel\u6587\u4ef6\u65f6\uff0cApache POI \u662f\u8bb8\u591a\u4eba\u9996\u9009\u7684\u5de5\u5177\u3002\u4f46\u662f\uff0c\u968f\u7740\u9700\u6c42\u7684\u589e\u52a0\u3001\u5de5\u7a0b\u590d\u6742\uff0c\u5728\u6253\u5f00\u590d\u6742\u7684Excel\u6587\u4ef6\u7684\u65f6\u5019\u53ef\u80fd\u4f1a\u51fa\u73b0\u4e00\u4e9b\u5f02\u5e38\u60c5\u51b5\u3002<\/p>\n

\u6839\u636e\u6d4b\u8bd5\uff0c\u5f53\u6253\u5f0050\u4e07\u4e2a\u5355\u5143\u683c\u6570\u636e\u7684\u65f6\u5019\uff0c\u5c31\u4f1a\u9047\u5230OOM\uff08OutOfMemory\uff09\u7684\u95ee\u9898\uff1b\u6216\u8005\u5f53\u6253\u5f00\u5305\u542b\u670920\u4e07\u4e2a\u5408\u5e76\u5355\u5143\u683c\uff08\u5305\u542bborder\u6216\u8005\u80cc\u666f\u8272\uff09\u7684\u65f6\u5019\uff0c\u4e5f\u4f1a\u9047\u5230OOM\uff08OutOfMemory\uff09\u7684\u95ee\u9898\u3002<\/p>\n

\u4f7f\u7528\u7684\u662fWorkbookFactory\uff0c\u76f4\u63a5\u6253\u5f00Excel\u6587\u4ef6\uff0c\u4ee3\u7801\u5982\u4e0b\uff1a<\/p>\n

File file = new File("testFile.xlsx");\n\nWorkbook workbook = WorkbookFactory.create(file);\n\n\/\/\u6253\u5f00\u6587\u4ef6\u540e\u8fdb\u884c\u5176\u4ed6\u5904\u7406<\/code><\/pre>\n
\u4ee5\u4e0a\u4ee3\u7801\u5728\u5904\u7406\u5927\u578bExcel\u6587\u4ef6\u65f6\u4f1a\u5bfc\u81f4OOM\u95ee\u9898\u7684\u53d1\u751f\u3002<\/p>\n
\u5728\u7f51\u4e0a\u67e5\u4e86\u4e00\u4e0b\uff0c\u6709\u4e24\u4e2a\u65b9\u6cd5\uff1a<\/p>\n
    \n
  1. \u53ef\u4ee5\u628a\u6587\u4ef6\u8f6c\u5316\u4e3aCSV\u7136\u540e\u5bfc\u5165\u3002<\/li>\n
  2. \u628aExcel\u6587\u4ef6\u98ce\u683c\u4e3a\u5c0f\u7684Excel\u6587\u4ef6\uff0c\u5206\u522b\u6784\u5efaworkbook\uff0c\u7136\u540e\u8fdb\u884c\u5904\u7406\u3002<\/li>\n<\/ol>\n
    \u7b2c\u4e00\u4e2a\u529e\u6cd5\uff0c\u5bf9\u4e8e\u4ec5\u5bfc\u5165\u6570\u636e\u65f6\u5f88\u6709\u6548\u3002\u4f46\u5f53Excel\u662f\u6709\u6837\u5f0f\u7684\u60c5\u51b5\u65f6\uff0c\u628aExcel\u8f6c\u6210CSV\u5c31\u4f1a\u5bfc\u81f4\u6837\u5f0f\u4e22\u5931\uff0c\u6240\u4ee5pass\u4e86\u8fd9\u4e2a\u65b9\u6cd5\u3002<\/p>\n
    \u4f3c\u4e4e\u53ef\u4ee5\u8003\u8651\u4e00\u4e0b\u7b2c\u4e8c\u4e2a\u529e\u6cd5\uff0c\u628a\u6587\u4ef6\u5206\u5272\u6210\u591a\u4e2a\u5c0f\u6587\u4ef6\uff0c\u5206\u522b\u6784\u5efaworkbook\uff0c\u7136\u540e\u53bb\u5904\u7406\u3002<\/p>\n
    \u4e8e\u662f\u624b\u52a8\u628aExcel\u6587\u4ef6\u62c6\u5206\u5f00\uff0c\u628a\u4ee3\u7801\u7b80\u5355\u6539\u4e86\u4e00\u4e0b\uff0c\u8fdb\u884c\u6d4b\u8bd5\u3002<\/p>\n
    File file = new File("test.xlsx");\n\nFile file1 = new File("test1.xlsx");\n\nFile file2 = new File("test2.xlsx");\n\nFile file3 = new File("test3.xlsx");\n\nFile file4 = new File("test4.xlsx");\n\nFile file5 = new File("test5.xlsx");\n\nFile file6 = new File("test6.xlsx");\n\nWorkbook workbook = WorkbookFactory.create(file);\n\nWorkbook workbook1 = WorkbookFactory.create(file1);\n\nWorkbook workbook2 = WorkbookFactory.create(file2);\n\nWorkbook workbook3 = WorkbookFactory.create(file3);\n\nWorkbook workbook4 = WorkbookFactory.create(file4);\n\nWorkbook workbook5 = WorkbookFactory.create(file5);\n\nWorkbook workbook6 = WorkbookFactory.create(file6);<\/code><\/pre>\n
    \u4f46\u8fd8\u662f\u9047\u5230\u4e86\u95ee\u9898\uff0c\u8fd8\u662f\u51fa\u73b0\u4e86oom\u7684\u95ee\u9898\uff0c\u4f7f\u7528\u7684\u662funit test\u505a\u7684\u6d4b\u8bd5\uff0c\u62a5\u9519\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n
    ...\n\nat org.gradle.process.internal.worker.child.ActionExecutionWorker.execute(ActionExecutionWorker.java:56)\n\nat org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:113)\n\nat org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:65)\n\nat worker.org.gradle.process.internal.worker.GradleWorkerMain.run(GradleWorkerMain.java:69)\n\nat worker.org.gradle.process.internal.worker.GradleWorkerMain.main(GradleWorkerMain.java:74)\n\nCaused by: java.lang.OutOfMemoryError: GC overhead limit exceeded\n\nat java.util.Arrays.copyOfRange(Arrays.java:3664)\n\nat java.lang.String.\/<init\/>(String.java:207)\n\nat com.sun.org.apache.xerces.internal.xni.XMLString.toString(XMLString.java:190)\n\nat com.sun.org.apache.xerces.internal.util.XMLAttributesImpl.getValue(XMLAttributesImpl.java:523)\n\nat com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser\/$AttributesProxy.getValue(AbstractSAXParser.java:2321)\n\n...<\/code><\/pre>\n
    \u7ecf\u8fc7\u4e00\u4e9b\u5c1d\u8bd5\uff0c\u53d1\u73b0\u662f\u540c\u4e00\u65f6\u95f4\u6784\u5efa\u7684workbook\u592a\u591a\u4e86\uff0c\u5f53\u51cf\u5c11\u52304\u4e2a\u65f6\uff0c\u5355\u5143\u6d4b\u8bd5\u5c31\u53ef\u4ee5\u6b63\u5e38\u8dd1\u5b8c\u3002<\/p>\n
    \u8fd9\u6837\u6765\u770b\uff0cPOI\u7684\u95ee\u9898\u8fd8\u771f\u662f\u8ba9\u4eba\u633a\u5934\u75bc\u3002\u6d4b\u8bd5\u7684\u65f6\u5019\uff0c\u6587\u4ef6\u662f\u53ef\u4ee5\u77e5\u9053\u88ab\u5206\u4e3a\u51e0\u4e2a\u7684\uff0c\u4f46\u662f\u5b9e\u9645\u5e94\u7528\u65f6\uff0c\u5c31\u6ca1\u6cd5\u9884\u6d4b\u6587\u4ef6\u7684\u6570\u91cf\u3002\u6b64\u5916\u6839\u636e\u6d4b\u8bd5\u6765\u770b\uff0cworkbook\u7684\u6570\u91cf\uff0c\u53ef\u80fd\u662f\u8ddfExcel\u6587\u4ef6\u7684\u5927\u5c0f\u76f8\u5173\uff0c\u8fd9\u4f1a\u5bfc\u81f4\u540e\u7eed\u5f00\u53d1\u65f6\u53ef\u80fd\u4f1a\u9047\u5230\u66f4\u591a\u7684\u95ee\u9898\u3002<\/p>\n
    \u7ee7\u7eed\u7f51\u4e0a\u51b2\u6d6a\uff0c\u770b\u5230\u9664\u4e86POI\u7684\u4f18\u5316\u65b9\u6cd5\uff0c\u8fd8\u770b\u5230\u6709EasyExcel\u548cGcExcel\u7b49\u5176\u4ed6\u4ea7\u54c1\u3002<\/p>\n
    \u7b80\u5355check\u4e86\u4e00\u4e0b\uff0cEasyExcel\u662f\u5f00\u6e90\u7684\uff0c\u4e3b\u8981\u662f\u5bf9\u9ad8\u5e76\u53d1\u7684\u8bfb\u5199\u573a\u666f\u505a\u5f97\u5f88\u597d\u3002GcExcel\u662f\u5546\u4e1a\u8f6f\u4ef6\uff0cAPI\u5f88\u5168\u3002<\/p>\n
    \u90a3\u53ef\u4ee5\u5206\u522b\u4f7f\u7528\u8fd9\u4e24\u4e2a\u7ec4\u4ef6\u9a8c\u8bc1\u4e00\u4e0b\uff0c\u6211\u4eec\u4e3b\u8981\u60f3\u89e3\u51b3\u7684\u95ee\u9898\u6709\u4e24\u4e2a\uff1a<\/p>\n
      \n
    1. \u5927\u91cf\u6570\u636e\u548c\u6837\u5f0f\u7684Excel\u6587\u4ef6\u80fd\u4e00\u6b21\u6027\u6253\u5f00<\/li>\n
    2. \u53ef\u4ee5\u6709\u529e\u6cd5\u4fdd\u7559\u6837\u5f0f\u6216\u8005\u64cd\u590d\u5236\u6837\u5f0f<\/li>\n<\/ol>\n
      \u5bf9\u4e8e\u95ee\u98981\uff0cEasyExcel\u548cGcExcel\u90fd\u53ef\u4ee5\u505a\u7684\u5f88\u597d\uff0c\u6ca1\u6709\u51fa\u73b0OOM\u7684\u95ee\u9898\u4e86\u3002\u4ee3\u7801\u4e0a\u4e24\u4e2a\u7ec4\u4ef6\u98ce\u683c\u4e0d\u592a\u4e00\u6837\uff0cGcExcel\u548cPOI\u6bd4\u8f83\u76f8\u4f3c\uff0c\u662f\u76f4\u63a5\u6784\u5efaworkbook\u3002POI\u7ed9\u7684\u4f8b\u5b50\u662f\u901a\u8fc7\u6ce8\u89e3\uff0c\u66f4\u50cf\u662f\u53cd\u5e8f\u5217\u5316\u7684\u4f53\u9a8c\uff0c\u540c\u65f6\u6bcf\u6b21\u8bfb\u53d6\u8981\u5199\u4e00\u4e2a\u76d1\u542c\u5668\uff0c\u901a\u8fc7\u76d1\u542c\u5668\u5904\u7406\u7279\u6b8a\u903b\u8f91\u3002<\/p>\n
      \u5bf9\u4e8e\u95ee\u98982\uff0c\u5199\u4e86\u4e00\u4e0bUT\uff0c\u4ee3\u7801\u5206\u522b\u5982\u4e0b\uff1a<\/p>\n
      \u5148\u770b\u770bEasyExcel,<\/p>\n
      \u9996\u5148EasyExcel\u9700\u8981\u5b9a\u4e49\u4e00\u4e2aData\u7c7b\uff0c\u6765\u8bfb\u53d6\u6570\u636e\u3002<\/p>\n
      @Getter\n\n@Setter\n\n@EqualsAndHashCode\n\npublic class DemoData {\n\nprivate String cell1;\n\nprivate String cell2;\n\n}<\/code><\/pre>\n
      \u5b9a\u4e49\u4e00\u4e2alistener\u7c7b\uff0c\u5904\u7406style\u7684\u903b\u8f91\u9700\u8981\u5728invoke\u91cc\u8fdb\u884c\u5904\u7406\uff0c\u6ca1\u627e\u5230EasyExcel\u76f8\u5173\u7684API\uff0c\u8fd8\u662f\u4f7f\u7528\u5230\u4e86POI\u672c\u8eab\u7684API\u6765\u5904\u7406\u6837\u5f0f\u76f8\u5173\u7684\u5185\u5bb9\u3002<\/p>\n
      @Slf4j\n\npublic class DemoListener implements ReadListener\/<DemoData\/> {\n\nprivate int rowNum = 0;\n\nprivate Sheet sheet;\n\n@Override\n\npublic void invoke(DemoData data, AnalysisContext context) {\n\nif (sheet == null) {\n\nsheet = (Sheet) context.readSheetHolder().getReadSheet();\n\n}\n\nRow row = sheet.getRow(rowNum);\n\n\/\/ \u83b7\u53d6\u7b2c\u4e00\u5217\n\nCell cell0 = row.getCell(0);\n\nCellStyle style0 = cell0.getCellStyle();\n\n\/\/ \u521b\u5efa\u6837\u5f0f\u5bf9\u8c61\n\nWorkbook workbook = sheet.getWorkbook();\n\nCellStyle newStyle = workbook.createCellStyle();\n\n\/\/ \u590d\u5236\u539f\u6709\u6837\u5f0f\u5230\u65b0\u521b\u5efa\u7684\u6837\u5f0f\u5bf9\u8c61\u4e2d\n\nnewStyle.cloneStyleFrom(style0);\n\n\/\/ TODO: \u5176\u4ed6\u64cd\u4f5c\n\nrowNum++;\n\n}\n\n@Override\n\npublic void doAfterAllAnalysed(AnalysisContext context) {\n\n}\n\n}<\/code><\/pre>\n
      \u4ece\u5b98\u7f51\u770b\u5230\uff0c\u5728EasyExcel 2.0.0-beta1\u4ee5\u540e\uff0c\u53ef\u4ee5\u4f7f\u7528extra\u65b9\u6cd5\u83b7\u53d6\u6279\u6ce8\uff0c\u8d85\u94fe\u63a5\uff0c\u5408\u5e76\u5355\u5143\u683c\u4fe1\u606f\u3002\u4f46\u662f\u5982\u679c\u6709border\u6216\u8005\u5176\u4ed6\u7684\u6837\u5f0f\uff0c\u4f3c\u4e4e\u597d\u50cf\u4e0d\u80fd\u7528\u8fd9\u4e2a\u65b9\u6cd5\u3002<\/p>\n
      \u7ecf\u8fc7\u7b80\u5355\u7684\u6d4b\u8bd5\uff0c\u95ee\u9898\u53ef\u4ee5\u89e3\u51b3\uff0c\u4f46\u662f\u6837\u5f0f\u5904\u7406\u8d77\u6765\u8fd8\u662f\u6bd4\u8f83\u590d\u6742\u3002<\/p>\n
      \u5bf9\u4e8eGcExcel\uff0c\u6839\u636e\u5b98\u65b9\u6587\u6863\u4ee3\u7801\u4e66\u4e0a\u5f88\u7b80\u5355\u3002\u76f4\u63a5\u57fa\u4e8eRange\u7684\u6982\u5ff5\u5c31\u53ef\u4ee5\u901a\u8fc7set\/get\u65b9\u6cd5\u83b7\u53d6\u5404\u79cd\u6837\u5f0f\u3002  
      https:\/\/www.grapecity.com.cn\/developer\/grapecitydocuments\/exc…<\/a><\/p>\n
      \"\"<\/p>\n
      \u505a\u4e00\u4e0b\u7b80\u5355\u7684\u6d4b\u8bd5\u5427\uff0c\u7528\u8d77\u6765\u5f88\u7b80\u5355\uff0c\u53ea\u8981\u7406\u89e3Excel\u76f8\u5173\u7684\u6982\u5ff5\u5c31\u53ef\u4ee5\u8f7b\u677e\u83b7\u53d6\u5230style\u3002<\/p>\n
      @Test\n\npublic void testRepeatCreateObject() throws IOException {\n\nString fileName = "test.xlsx";\n\nWorkbook workbook = new Workbook();\n\nworkbook.open(fileName);\n\nIWorksheet sheet = workbook.getWorksheets().get(0);\n\nIStyle style = sheet.getRange(0,0).getStyle();\n\nSystem.out.println("font "+style.getFont().getName());\n\nSystem.out.println("border "+style.getBorders().getLineStyle().name());\n\n}<\/code><\/pre>\n
      \u81f3\u6b64\uff0c\u6574\u4f53\u4e0a\u770b\uff0c\u559c\u6b22\u4f7f\u7528\u5f00\u6e90\u7684\u8bdd\uff0c\u53ef\u4ee5\u9009\u62e9EasyExcel\u3002EasyExcel\u63d0\u4f9b\u4e86\u53cd\u5e8f\u5217\u5316\u4e00\u6837\u7684\u6ce8\u89e3\u65b9\u5f0f\uff0c\u8bfb\u53d6\u6570\u636e\u3002\u5728\u6570\u636e\u8bfb\u53d6\u65b9\u9762\u5f88\u7b80\u5355\u3002\u4f46\u662f\u5728\u6837\u5f0f\u5904\u7406\u4e0a\uff0c\u5f97\u4f9d\u8d56\u4e8b\u4ef6\u673a\u5236\u53bb\u5904\u7406\uff0c\u8fd9\u4e2a\u8fd8\u662f\u6709\u4e00\u70b9\u9ebb\u70e6\u7684\u3002<\/p>\n
      \u5982\u679c\u662f\u505a\u5546\u4e1a\u9879\u76ee\u5f00\u53d1\uff0c\u53ef\u4ee5\u8003\u8651GcExcel\u3002GcExcel\u5728API\u4e0a\u5341\u5206\u7b80\u5355\u6613\u7528\uff0c\u53e6\u5916\u5728\u6d4b\u8bd5\u4e2d\u53d1\u73b0\uff0c\u6253\u5f00\u6587\u4ef6\u7684\u901f\u5ea6\u4e5f\u5feb\u5f88\u591a\uff0c\u53ef\u4ee5\u964d\u4f4e\u5f00\u53d1\u6210\u672c\u3002<\/p>\n
      \u6269\u5c55\u94fe\u63a5\uff1a<\/strong><\/p>\n
      \u5728\u670d\u52a1\u5668\u7aef\u5bfc\u5165\u5bfc\u51faExcel<\/a><\/p>\n
      \u5982\u4f55\u7528C1\u5b9e\u73b0\u5e94\u7528\u7a0b\u5e8f\u4e0e\u5fae\u8f6fExcel\u7684\u4ea4\u4e92<\/a><\/p>\n
      \u4e2d\u56fd\u5f0f\u590d\u6742\u62a5\u8868\u5f00\u53d1\u6559\u7a0b\uff081\uff09\u2014\u7c7bExcel\u5355\u7ef4\u5ea6\u4ea4\u53c9\u8868<\/a><\/p>\n
      https:\/\/segmentfault.com\/a\/1190000043965811<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      \u6458\u8981\uff1a\u672c\u6587\u7531\u8461\u8404\u57ce\u6280\u672f\u56e2\u961f\u4e8e\u601d\u5426\u539f\u521b\u5e76\u9996\u53d1\u3002\u8f6c\u8f7d\u8bf7\u6ce8\u660e\u51fa\u5904\uff1a\u8461\u8404\u57ce\u5b98\u7f51\uff0c\u8461\u8404\u57ce\u4e3a\u5f00\u53d1\u8005\u63d0\u4f9b\u4e13\u4e1a\u7684\u5f00\u53d1\u5de5\u5177\u3001\u89e3\u51b3 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-10008028","post","type-post","status-publish","format-standard","hentry","category-share"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008028","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10008028"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008028\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10008028"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10008028"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10008028"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}