{"id":4867,"date":"2020-03-27T08:45:50","date_gmt":"2020-03-27T00:45:50","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=4867"},"modified":"2020-03-27T08:45:50","modified_gmt":"2020-03-27T00:45:50","slug":"java%e4%b8%ad%e5%a6%82%e4%bd%95%e8%bf%9b%e8%a1%8c%e8%af%bb%e5%86%99excel%e7%9a%84%e6%93%8d%e4%bd%9c","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/4867.html","title":{"rendered":"Java\u4e2d\u5982\u4f55\u8fdb\u884c\u8bfb\u5199Excel\u7684\u64cd\u4f5c"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u6574\u7406\u603b\u7ed3\u4e00\u4e0b\u5728Java\u4e2d\u5982\u4f55\u501f\u52a9Apache POI\u5b9e\u73b0Excel\u7684\u8bfb\u3001\u5199\u64cd\u4f5c\uff0c\u65b9\u4fbf\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u548c\u7406\u89e3\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<h4 class=\"wp-block-heading\">\u5199Excel\u7684\u57fa\u672c\u6b65\u9aa4<\/h4>\n\n\n\n<ol class=\"wp-block-list\"><li>\u521b\u5efa\u4e00\u4e2a workbook<\/li><li>\u521b\u5efa\u4e00\u4e2a sheet<\/li><li>\u91cd\u590d\u5982\u4e0b\u6b65\u9aa4\u76f4\u5230\u6570\u636e\u88ab\u5199\u5165\u5b8c\u6210\uff1a<ul><li>\u521b\u5efa\u4e00\u4e2a row<\/li><li>\u5728 row \u4e2d\u521b\u5efa cells \uff0c\u5982\u679c\u8981\u8bbe\u7f6e\u683c\u5f0f\u7684\u8bdd\u53ef\u4ee5\u7528 CellStyle<\/li><\/ul><\/li><li>\u5c06 workbook \u5199\u5165 FileOutputStream<\/li><li>\u5173\u95ed FileOutputStream<\/li><li>\u5173\u95ed workbook<\/li><\/ol>\n\n\n\n<h5 class=\"wp-block-heading\">\u4e00\u4e2a\u7b80\u5355\u7684\u5199Excel\u7684\u4ee3\u7801\u6837\u4f8b<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>import org.apache.poi.ss.usermodel.Cell;\nimport org.apache.poi.ss.usermodel.Row;\nimport org.apache.poi.xssf.usermodel.XSSFSheet;\nimport org.apache.poi.xssf.usermodel.XSSFWorkbook;\n\nimport java.io.FileOutputStream;\nimport java.io.IOException;\n\npublic class WriteExcel {\n    public static void main(String&#91;] args) {\n        XSSFWorkbook workbook = new XSSFWorkbook();\n        XSSFSheet sheet = workbook.createSheet(\"Java-Books\");\n\n        Object&#91;]&#91;] bookData = {\n                {\"Head First Java\", \"Kathy Serria\", 79},\n                {\"Effective Java\", \"Joshua Bloch\", 36},\n                {\"Clean Code\", \"Robert martin\", 42},\n                {\"Thinking in Java\", \"Bruce Eckel\", 35},\n        };\n\n        int rowCount = 0;\n        for (Object&#91;] aBook : bookData) {\n            Row row = sheet.createRow(rowCount++);\n\n            int columnCount = 0;\n\n            for (Object field : aBook) {\n                Cell cell = row.createCell(columnCount++);\n                if (field instanceof String) {\n                    cell.setCellValue((String) field);\n                } else if (field instanceof Integer) {\n                    cell.setCellValue((Integer) field);\n                }\n            }\n\n        }\n\n        \/\/ \u5c06 workbook \u5199\u5165 FileOutputStream \u5b58\u6587\u4ef6\n        try (FileOutputStream outputStream = new FileOutputStream(\"JavaBooks.xlsx\")) {\n            workbook.write(outputStream);\n        } catch (IOException e) {\n            e.printStackTrace();\n        }\n        workbook.close();\n    }\n}<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u4e00\u4e2a\u8bfbExcel\u5185\u5bb9\u53ca\u5176\u5143\u4fe1\u606f\u7684\u4ee3\u7801\u6837\u4f8b<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>import org.apache.poi.ooxml.POIXMLProperties;\nimport org.apache.poi.ss.usermodel.Cell;\nimport org.apache.poi.ss.usermodel.Row;\nimport org.apache.poi.ss.usermodel.Sheet;\nimport org.apache.poi.ss.usermodel.Workbook;\nimport org.apache.poi.xssf.usermodel.XSSFWorkbook;\nimport org.openxmlformats.schemas.officeDocument.x2006.customProperties.CTProperties;\n\nimport java.io.*;\nimport java.util.Iterator;\n\npublic class ReadExcel {\n    public static void main(String&#91;] args) {\n        String excelFilePath = \"JavaBooks.xlsx\";\n        \n        try {\n            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));\n            Workbook workbook = new XSSFWorkbook(inputStream);\n\n            int sheetNum = workbook.getNumberOfSheets();\n            System.out.println(\"Number of sheet: \" + sheetNum);\n            for (int i = 0; i &lt; sheetNum; i++) {\n                Sheet sheet = workbook.getSheetAt(i);\n                System.out.println(sheet.getSheetName());\n                Iterator&lt;Row> iterator = sheet.iterator();\n\n                while (iterator.hasNext()) {\n                    Row nextRow = iterator.next();\n                    Iterator&lt;Cell> cellIterator = nextRow.cellIterator();\n\n                    while (cellIterator.hasNext()) {\n                        Cell cell = cellIterator.next();\n\n                        switch (cell.getCellType()) {\n                            case STRING:\n                                System.out.print(cell.getStringCellValue());\n                                break;\n                            case BOOLEAN:\n                                System.out.print(cell.getBooleanCellValue());\n                                break;\n                            case NUMERIC:\n                                System.out.print(cell.getNumericCellValue());\n                                break;\n                        }\n                        System.out.print(\" - \");\n                    }\n                    System.out.println();\n                }\n                System.out.println();\n            }\n            System.out.println();\n\n            \/* Get properties info of excel *\/\n            POIXMLProperties props = ((XSSFWorkbook) workbook).getProperties();\n\n            \/* Let us set some core properties now *\/\n            POIXMLProperties.CoreProperties coreProp = props.getCoreProperties();\n            System.out.println(coreProp.getCreator());\n            System.out.println(coreProp.getCategory());\n            coreProp.setCreator(\"zero\"); \/\/ set document creator\n            coreProp.setDescription(\"set Metadata via Apache POI in Java\");\n            coreProp.setCategory(\"Programming\"); \/\/ category\n\n            \/* We can set some custom Properties now *\/\n            POIXMLProperties.CustomProperties customProp = props.getCustomProperties();\n            \/\/ java.lang.IllegalArgumentException: A property with this name already exists in the custom properties\n            \/\/ addProperty() \u4e0d\u80fd\u5bf9\u5df2\u6709\u7684 name \u8fdb\u884c\u518d\u6b21\u6dfb\u52a0\uff0c\u5426\u5219\u4f1a\u629b\u51fa\u5f02\u5e38\n            customProp.addProperty(\"Author\", \"zero\");\/\/ String\n            customProp.addProperty(\"Year\", 2020); \/\/ Number Property\n            customProp.addProperty(\"Published\", true); \/\/ Yes No Property\n            customProp.addProperty(\"Typist\", \"zero\");\n            CTProperties ctProperties = customProp.getUnderlyingProperties();\n            for (int i = 0; i &lt; ctProperties.sizeOfPropertyArray(); i++) {\n                System.out.print(i + \"\\t\");\n                System.out.println(ctProperties.getPropertyArray(i));\n            }\n\n            POIXMLProperties.ExtendedProperties extendedProp = props.getExtendedProperties();\n            System.out.println(extendedProp.getCompany()); \/\/ null\n            extendedProp.setCompany(\"test\");\n            System.out.println(extendedProp.getCompany()); \/\/ test\n\n            \/* Save modification to properties *\/\n            FileOutputStream outputStream = new FileOutputStream(new File(excelFilePath));\n            workbook.write(outputStream);\n            outputStream.close();\n\n            workbook.close();\n            inputStream.close();\n\n        } catch (FileNotFoundException e) {\n            e.printStackTrace();\n        } catch (IOException e) {\n            e.printStackTrace();\n        }\n\n    }\n}<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p><strong>How to Read Excel Files in Java using Apache POI<\/strong><br> <a href=\"https:\/\/www.codejava.net\/coding\/how-to-read-excel-files-in-java-using-apache-poi\">https:\/\/www.codejava.net\/coding\/how-to-read-excel-files-in-java-using-apache-poi<\/a><\/p>\n\n\n\n<p>Java Example to Read Password-protected Excel Files Using Apache POI<br> <a href=\"https:\/\/www.codejava.net\/coding\/java-example-to-read-password-protected-excel-files-using-apache-poi\">https:\/\/www.codejava.net\/coding\/java-example-to-read-password-protected-excel-files-using-apache-poi<\/a><\/p>\n\n\n\n<p>Java Example to Update Existing Excel Files Using Apache POI<br> <a href=\"https:\/\/www.codejava.net\/coding\/java-example-to-update-existing-excel-files-using-apache-poi\">https:\/\/www.codejava.net\/coding\/java-example-to-update-existing-excel-files-using-apache-poi<\/a><\/p>\n\n\n\n<p>How to Write Excel Files in Java using Apache POI<br> <a href=\"https:\/\/www.codejava.net\/coding\/how-to-write-excel-files-in-java-using-apache-poi\">https:\/\/www.codejava.net\/coding\/how-to-write-excel-files-in-java-using-apache-poi<\/a><\/p>\n\n\n\n<p> <a href=\"https:\/\/mvnrepository.com\/artifact\/org.apache.poi\/poi\">https:\/\/mvnrepository.com\/artifact\/org.apache.poi\/poi<\/a><br> <a href=\"https:\/\/mvnrepository.com\/artifact\/org.apache.poi\/poi-ooxml\">https:\/\/mvnrepository.com\/artifact\/org.apache.poi\/poi-ooxml<\/a><br> <a href=\"https:\/\/mvnrepository.com\/artifact\/org.apache.commons\/commons-compress\">https:\/\/mvnrepository.com\/artifact\/org.apache.commons\/commons-compress<\/a><\/p>\n\n\n\n<p>Java \u4f7f\u7528 POI \u64cd\u4f5c Excel<br> <a href=\"https:\/\/juejin.im\/post\/5c09e559e51d451da152df9c\">https:\/\/juejin.im\/post\/5c09e559e51d451da152df9c<\/a><\/p>\n\n\n\n<p>POI\u6838\u5fc3\u7c7b<br> <a href=\"https:\/\/www.yiibai.com\/apache_poi\/apache_poi_core_classes.html\">https:\/\/www.yiibai.com\/apache_poi\/apache_poi_core_classes.html<\/a><\/p>\n\n\n\n<p>java excel\u6dfb\u52a0\u6c34\u5370<br> <a href=\"https:\/\/www.jianshu.com\/p\/5ebf2217f0be\">https:\/\/www.jianshu.com\/p\/5ebf2217f0be<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/3454975\/writing-to-excel-in-java\">https:\/\/stackoverflow.com\/questions\/3454975\/writing-to-excel-in-java<\/a><\/p>\n\n\n\n<p>Java Code Examples for org.apache.poi.POIXMLProperties<br> <a href=\"https:\/\/www.programcreek.com\/java-api-examples\/?api=org.apache.poi.POIXMLProperties\">https:\/\/www.programcreek.com\/java-api-examples\/?api=org.apache.poi.POIXMLProperties<\/a><\/p>\n\n\n\n<p>Java Examples for org.apache.poi.POIXMLProperties<br> <a href=\"https:\/\/www.javatips.net\/api\/org.apache.poi.poixmlproperties\">https:\/\/www.javatips.net\/api\/org.apache.poi.poixmlproperties<\/a><\/p>\n\n\n\n<p>how to write metadata into Excel workbooks using Apache POI in Java<br> <a href=\"https:\/\/stackoverflow.com\/questions\/43796819\/how-to-write-metadata-into-excel-workbooks-using-apache-poi-in-java\">https:\/\/stackoverflow.com\/questions\/43796819\/how-to-write-metadata-into-excel-workbooks-using-apache-poi-in-java<\/a><\/p>\n\n\n\n<p><strong>Read \/ Write Metadata &#8211; Excel POI Example<\/strong><br> <a href=\"https:\/\/thinktibits.blogspot.com\/2014\/07\/read-write-metadata-excel-poi-example.html\">https:\/\/thinktibits.blogspot.com\/2014\/07\/read-write-metadata-excel-poi-example.html<\/a><\/p>\n\n\n\n<p>poi4.0.0\u8bfb\u53d6excel\u6587\u4ef6\u65f6\u62a5java.lang.NoClassDefFoundError: org\/apache\/commons\/compress\/archivers\/zip\/ZipFile<br> <a href=\"https:\/\/blog.csdn.net\/redsoft_mymuch\/article\/details\/84099902\">https:\/\/blog.csdn.net\/redsoft_mymuch\/article\/details\/84099902<\/a><\/p>\n\n\n\n<p>JAVA\u7b80\u5355\u5feb\u901f\u7684\u8bfb\u5199Excel\u4e4bEasyExcel<br> <a href=\"https:\/\/www.lifengdi.com\/archives\/article\/1393\">https:\/\/www.lifengdi.com\/archives\/article\/1393<\/a><br> <a href=\"https:\/\/alibaba-easyexcel.github.io\/index.html\">https:\/\/alibaba-easyexcel.github.io\/index.html<\/a><br> <a href=\"https:\/\/github.com\/alibaba\/easyexcel\">https:\/\/github.com\/alibaba\/easyexcel<\/a><\/p>\n\n\n\n<p>java excel\u6dfb\u52a0\u6c34\u5370<br> <a href=\"https:\/\/www.jianshu.com\/p\/5ebf2217f0be\">https:\/\/www.jianshu.com\/p\/5ebf2217f0be<\/a><br> <a href=\"https:\/\/juejin.im\/post\/5d1edde051882542cf1009dc\">https:\/\/juejin.im\/post\/5d1edde051882542cf1009dc<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u6574\u7406\u603b\u7ed3\u4e00\u4e0b\u5728Java\u4e2d\u5982\u4f55\u501f\u52a9Apache POI\u5b9e\u73b0Excel\u7684\u8bfb\u3001\u5199\u64cd\u4f5c\uff0c\u65b9\u4fbf\u6709 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,7,12],"tags":[1593,535,61],"class_list":["post-4867","post","type-post","status-publish","format-standard","hentry","category-knowledgebase-2","category-programing","category-tools","tag-apache-poi","tag-excel","tag-java"],"views":6769,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4867","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=4867"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4867\/revisions"}],"predecessor-version":[{"id":4868,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4867\/revisions\/4868"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=4867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=4867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=4867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}