{"id":1570,"date":"2015-11-02T16:49:48","date_gmt":"2015-11-02T08:49:48","guid":{"rendered":"https:\/\/cowmanchiang.me\/wp\/?p=1570"},"modified":"2023-10-31T15:43:12","modified_gmt":"2023-10-31T07:43:12","slug":"java-read-write-xls-files","status":"publish","type":"post","link":"https:\/\/cowmanchiang.me\/wp\/?p=1570","title":{"rendered":"[Java] Write xls files"},"content":{"rendered":"
public String createFile(Object object) {\n    try {\n        Excel excel = (Excel) object;\n        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();\n        for (SheetContent sheetContent:excel.getSheets()) {\n            HSSFSheet sheet = hssfWorkbook.createSheet(sheetContent.getName());\n\n            List<HSSFCellStyle> formats = new ArrayList<>();\n            \/** format *\/\n            for (String format:sheetContent.getFormats()) {\n                HSSFCellStyle cs = hssfWorkbook.createCellStyle();\n                HSSFDataFormat df = hssfWorkbook.createDataFormat();\n                cs.setDataFormat(df.getFormat(format));\n                formats.add(cs);\n            }\n\n            \/** header *\/\n            HSSFRow headerRow = sheet.createRow(0);\n            for (int i = 0; i < sheetContent.getHeaders().size(); i++) {\n                HSSFCell cell = headerRow.createCell(i);\n                cell.setCellType(HSSFCell.CELL_TYPE_STRING);\n                cell.setCellValue(sheetContent.getHeaders().get(i));\n            }\n\n            \/** content *\/\n            for (int i = 0; i < sheetContent.getRows().size(); i ++) {\n                HSSFRow dataRow = sheet.createRow(i + 1);\n                for (int j = 0; j < sheetContent.getRows().get(i).getCells().size(); j++) {\n                    HSSFCell cell = dataRow.createCell(j);\n                    if (StringUtils.isNotEmpty(sheetContent.getFormats().get(j))) {\n                        cell.setCellValue(Double.parseDouble(sheetContent.getRows().get(i).getCells().get(j)));\n                        cell.setCellStyle(formats.get(j));\n                    } else {\n                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);\n                        cell.setCellValue(sheetContent.getRows().get(i).getCells().get(j));\n                    }\n                }\n            }\n\n            \/** auto size *\/\n            for (int i = 0; i < sheet.getRow(0).getPhysicalNumberOfCells(); i++) {\n                sheet.autoSizeColumn(i);\n            }\n        }\n\n        FileOutputStream out = new FileOutputStream(new File("Excel.xls"));\n        hssfWorkbook.write(out);\n        out.close();\n\n        return "ok";\n    } catch (Exception e) {\n        LOG.error(Constants.EXCEPTION_PREFIX, e);\n        return null;\n    }\n}\n\npublic class Excel {\n    List<SheetContent> sheets;\n\n    public List<SheetContent> getSheets() {\n        return sheets;\n    }\n\n    public Excel setSheets(List<SheetContent> sheets) {\n        this.sheets = sheets;\n        return this;\n    }\n\n    public Excel addSheets(SheetContent sheet) {\n        if (this.sheets == null) {\n            this.sheets = new ArrayList<>();\n        }\n        this.sheets.add(sheet);\n        return this;\n    }\n}\n\npublic class SheetContent {\n    private String name;\n    private List<String> formats;\n    private List<String> headers;\n    private List<Row> rows;\n\n    public String getName() {\n        return name;\n    }\n\n    public SheetContent setName(String name) {\n        this.name = name;\n        return this;\n    }\n\n\n    public List<String> getFormats() {\n        return formats;\n    }\n\n    public SheetContent setFormats(List<String> formats) {\n        this.formats = formats;\n        return this;\n    }\n\n    public List<String> getHeaders() {\n        return headers;\n    }\n\n    public SheetContent setHeaders(List<String> headers) {\n        this.headers = headers;\n        return this;\n    }\n\n    public List<Row> getRows() {\n        return rows;\n    }\n\n    public SheetContent setRows(List<Row> rows) {\n        this.rows = rows;\n        return this;\n    }\n}\n\npublic class Row {\n    private List<String> cells;\n\n    public List<String> getCells() {\n        return cells;\n    }\n\n    public Row setCells(List<String> cells) {\n        this.cells = cells;\n        return this;\n    }\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"
public String createFile(Object object) { try { Excel excel = (Excel) object; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); for (SheetContent sheetContent:excel.getSheets()) { HSSFSheet sheet = hssfWorkbook.createSheet(sheetContent.getName()); List<HSSFCellStyle> formats = new ArrayList<>(); \/** format *\/ for (String format:sheetContent.getFormats()) { HSSFCellStyle cs = … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[94,51],"tags":[],"class_list":["post-1570","post","type-post","status-publish","format-standard","hentry","category-excel","category-java"],"_links":{"self":[{"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1570","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1570"}],"version-history":[{"count":1,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1570\/revisions"}],"predecessor-version":[{"id":1897,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1570\/revisions\/1897"}],"wp:attachment":[{"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cowmanchiang.me\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}