{"id":961,"date":"2014-08-24T12:26:40","date_gmt":"2014-08-24T04:26:40","guid":{"rendered":"http:\/\/www.yeetrack.com\/?p=961"},"modified":"2014-08-24T12:26:40","modified_gmt":"2014-08-24T04:26:40","slug":"java%e8%af%bb%e5%86%99excel","status":"publish","type":"post","link":"https:\/\/www.yeetrack.com\/?p=961","title":{"rendered":"Java\u8bfb\u5199Excel"},"content":{"rendered":"<h1>Java\u8bfb\u5199Excel<\/h1>\n<p>\u5de5\u4f5c\u4e2d\u7ecf\u5e38\u9700\u8981\u5bf9Excel\u8fdb\u884c\u8bfb\u5199\u64cd\u4f5c\uff0cjava\u64cd\u4f5cexcel\u6587\u4ef6\u6bd4\u8f83\u6d41\u884c\u7684\u662fapache poi\u5305\uff0cexcel\u5206\u4e3axls\uff082003\uff09\u548cxlsx\uff082007\uff09\u4e24\u79cd\u683c\u5f0f\uff0c\u64cd\u4f5c\u8fd9\u4e24\u79cd\u683c\u5f0f\u7684excel\u9700\u8981\u4e0d\u540c\u7684poi\u5305\u3002<\/p>\n<ul>\n<li>xls\u683c\u5f0f\n<pre><code>  &lt;dependency&gt;\n      &lt;groupId&gt;org.apache.poi&lt;\/groupId&gt;\n      &lt;artifactId&gt;poi&lt;\/artifactId&gt;\n      &lt;version&gt;3.11-beta1&lt;\/version&gt;\n  &lt;\/dependency&gt;\n<\/code><\/pre>\n<\/li>\n<li>xlsx\u683c\u5f0f\n<pre><code>  &lt;dependency&gt;\n      &lt;groupId&gt;org.apache.poi&lt;\/groupId&gt;\n      &lt;artifactId&gt;poi-ooxml&lt;\/artifactId&gt;\n      &lt;version&gt;3.11-beta1&lt;\/version&gt;\n  &lt;\/dependency&gt;\n<\/code><\/pre>\n<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<h2>\u8bfbxls<\/h2>\n<pre><code>    File file = new File(\"src\/test\/resources\/test.xls\");\n    POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new       FileInputStream(file));\n    HSSFWorkbook hssfWorkbook =  new HSSFWorkbook(poifsFileSystem);\n    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);\n\n    int rowstart = hssfSheet.getFirstRowNum();\n    int rowEnd = hssfSheet.getLastRowNum();\n    for(int i=rowstart;i&lt;=rowEnd;i++)\n    {\n        HSSFRow row = hssfSheet.getRow(i);\n        if(null == row) continue;\n        int cellStart = row.getFirstCellNum();\n        int cellEnd = row.getLastCellNum();\n\n        for(int k=cellStart;k&lt;=cellEnd;k++)\n        {\n            HSSFCell cell = row.getCell(k);\n            if(null==cell) continue;\n            System.out.print(\"\" + k + \"  \");\n            \/\/System.out.print(\"type:\"+cell.getCellType());\n\n            switch (cell.getCellType())\n            {\n                case HSSFCell.CELL_TYPE_NUMERIC: \/\/ \u6570\u5b57\n                                System.out.print(cell.getNumericCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_STRING: \/\/ \u5b57\u7b26\u4e32\n                    System.out.print(cell.getStringCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_BOOLEAN: \/\/ Boolean\n                    System.out.println(cell.getBooleanCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_FORMULA: \/\/ \u516c\u5f0f\n                    System.out.print(cell.getCellFormula() + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_BLANK: \/\/ \u7a7a\u503c\n                    System.out.println(\" \");\n                    break;\n                case HSSFCell.CELL_TYPE_ERROR: \/\/ \u6545\u969c\n                    System.out.println(\" \");\n                    break;\n                default:\n                    System.out.print(\"\u672a\u77e5\u7c7b\u578b   \");\n                    break;\n            }\n\n        }\n        System.out.print(\"\\n\");\n    }\n<\/code><\/pre>\n<h2>\u8bfbxlsx<\/h2>\n<pre><code>    File file = new File(\"src\/test\/resources\/test.xlsx\");\n\n    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);\n    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);\n\n    int rowstart = xssfSheet.getFirstRowNum();\n    int rowEnd = xssfSheet.getLastRowNum();\n    for(int i=rowstart;i&lt;=rowEnd;i++)\n    {\n        XSSFRow row = xssfSheet.getRow(i);\n        if(null == row) continue;\n        int cellStart = row.getFirstCellNum();\n        int cellEnd = row.getLastCellNum();\n\n        for(int k=cellStart;k&lt;=cellEnd;k++)\n        {\n            XSSFCell cell = row.getCell(k);\n            if(null==cell) continue;\n\n            switch (cell.getCellType())\n            {\n                case HSSFCell.CELL_TYPE_NUMERIC: \/\/ \u6570\u5b57\n                    System.out.print(cell.getNumericCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_STRING: \/\/ \u5b57\u7b26\u4e32\n                    System.out.print(cell.getStringCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_BOOLEAN: \/\/ Boolean\n                    System.out.println(cell.getBooleanCellValue()\n                            + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_FORMULA: \/\/ \u516c\u5f0f\n                    System.out.print(cell.getCellFormula() + \"   \");\n                    break;\n                case HSSFCell.CELL_TYPE_BLANK: \/\/ \u7a7a\u503c\n                    System.out.println(\" \");\n                    break;\n                case HSSFCell.CELL_TYPE_ERROR: \/\/ \u6545\u969c\n                    System.out.println(\" \");\n                    break;\n                default:\n                    System.out.print(\"\u672a\u77e5\u7c7b\u578b   \");\n                    break;\n            }\n\n        }\n        System.out.print(\"\\n\");\n    }\n<\/code><\/pre>\n<h2>\u5199xls<\/h2>\n<pre><code>    HSSFWorkbook workbook = null;\n    workbook = new HSSFWorkbook();\n    \/\/\u83b7\u53d6\u53c2\u6570\u4e2a\u6570\u4f5c\u4e3aexcel\u5217\u6570\n    int columeCount = 6;\n    \/\/\u83b7\u53d6List size\u4f5c\u4e3aexcel\u884c\u6570\n    int rowCount = 20;\n    HSSFSheet sheet = workbook.createSheet(\"sheet name\");\n    \/\/\u521b\u5efa\u7b2c\u4e00\u680f\n    HSSFRow headRow = sheet.createRow(0);\n    String[] titleArray = {\"id\", \"name\", \"age\", \"email\", \"address\", \"phone\"};\n    for(int m=0;m&lt;=columeCount-1;m++)\n    {\n        HSSFCell cell = headRow.createCell(m);\n        cell.setCellType(HSSFCell.CELL_TYPE_STRING);\n        sheet.setColumnWidth(m, 6000);\n        HSSFCellStyle style = workbook.createCellStyle();\n        HSSFFont font = workbook.createFont();\n        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);\n        short color = HSSFColor.RED.index;\n        font.setColor(color);\n        style.setFont(font);\n        \/\/\u586b\u5199\u6570\u636e\n        cell.setCellStyle(style);\n        cell.setCellValue(titleArray[m]);\n\n    }\n    int index = 0;\n    \/\/\u5199\u5165\u6570\u636e\n    for(RowEntity entity : pRowEntityList)\n    {\n        \/\/logger.info(\"\u5199\u5165\u4e00\u884c\");\n        HSSFRow row = sheet.createRow(index+1);\n        for(int n=0;n&lt;=columeCount-1;n++)\n            row.createCell(n);\n        row.getCell(0).setCellValue(entity.getId());\n        row.getCell(1).setCellValue(entity.getName());\n        row.getCell(2).setCellValue(entity.getAge());\n        row.getCell(3).setCellValue(entity.getEmail());\n        row.getCell(4).setCellValue(entity.getAddress());\n        row.getCell(5).setCellValue(entity.getPhone());\n        index++;\n    }\n\n    \/\/\u5199\u5230\u78c1\u76d8\u4e0a\n    try {\n        FileOutputStream fileOutputStream = new FileOutputStream(new File(path));\n        workbook.write(fileOutputStream);\n        fileOutputStream.close();\n    } catch (FileNotFoundException e) {\n        e.printStackTrace();\n    } catch (IOException e) {\n        e.printStackTrace();\n    }\n<\/code><\/pre>\n<h2>\u5199xlsx<\/h2>\n<p>\u548c\u5199xls\u7c7b\u4f3c\uff0c\u4f7f\u75282007\u5bf9\u5e94\u7684\u5bf9\u8c61\u5373\u53ef\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Java\u8bfb\u5199Excel \u5de5\u4f5c\u4e2d\u7ecf\u5e38\u9700\u8981\u5bf9Excel\u8fdb\u884c\u8bfb\u5199\u64cd\u4f5c\uff0cjava\u64cd\u4f5cexcel\u6587\u4ef6\u6bd4\u8f83\u6d41\u884c\u7684\u662fapache poi\u5305\uff0cexcel\u5206\u4e3axls\uff082003\uff09\u548cxlsx\uff082007\uff09\u4e24\u79cd\u683c\u5f0f\uff0c\u64cd\u4f5c\u8fd9\u4e24\u79cd&#46;&#46;&#46;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[33],"tags":[8,7,5],"class_list":["post-961","post","type-post","status-publish","format-standard","hentry","category-coding","tag-java","tag-7","tag-5"],"views":7586,"_links":{"self":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/961","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=961"}],"version-history":[{"count":1,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/961\/revisions"}],"predecessor-version":[{"id":962,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/961\/revisions\/962"}],"wp:attachment":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=961"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=961"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=961"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}