{"id":3556,"date":"2013-10-15T12:32:19","date_gmt":"2013-10-15T07:02:19","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=3556"},"modified":"2013-10-15T12:32:19","modified_gmt":"2013-10-15T07:02:19","slug":"create-excel-file-in-java-using-apache-poi-library","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/java\/create-excel-file-in-java-using-apache-poi-library\/","title":{"rendered":"Create Excel File in Java using Apache POI Library"},"content":{"rendered":"

Recently, I came across requirement to create ExcelSheet from thin Java Client used by Salesforce. So, I though to share my experience on Creating Excel Sheet in Java. As we know that Java is product of Oracle and Excel is product of Microsoft. Off-course, There will be no standard functionality available in Java to achieve our requirement of creating Excel Sheet. However thanks to Apache<\/strong> for their POI<\/strong> Library. POI Stands for “Poor Obfuscation Implementation” as the file formats created by this library is obfuscated poorly with help of reverse Engineering. Anyways, we don’t have to bother about it and thankful to them for providing such wonderful library.<\/p>\n

Apache POI library can be used to create Excel Sheet, Word Document and PowerPoint. In this post, we will be totally focusing on Excel Sheet using “XSSF (XML Spreadsheet Format)<\/strong>” component.<\/p>\n

Prerequisite :<\/strong>
\nAdd all jar files downloaded from Apache POI download site in Java Program’s build path.<\/p>\n

Demo Code :<\/strong><\/p>\n

\npackage in.shivasoft;\n\nimport java.awt.Desktop;\nimport java.io.File;\nimport java.io.FileOutputStream;\n\nimport org.apache.poi.hssf.util.CellRangeAddress;\nimport org.apache.poi.ss.usermodel.Cell;\nimport org.apache.poi.ss.usermodel.CellStyle;\nimport org.apache.poi.ss.usermodel.IndexedColors;\nimport org.apache.poi.ss.usermodel.Row;\nimport org.apache.poi.ss.usermodel.Sheet;\nimport org.apache.poi.ss.util.CellReference;\nimport org.apache.poi.xssf.streaming.SXSSFWorkbook;\n\n\/**\n* @author Jitendra Zaa\n*\n*\/\npublic class CreateExcelSheet {\n\n\tstatic SXSSFWorkbook  wb ;\n\tstatic Sheet sh ;\n\n\t\/**\n\t * This method demonstrates how to Auto resize Excel column\n\t *\/\n\tprivate static void autoResizeColumns()\n\t{\n\t\tfor(int colIndex = 0; colIndex < 10 ; colIndex++)\n\t\t{\n\t\t\tsh.autoSizeColumn(colIndex);\n\t\t}\n\t}\n\n\t\/**\n\t * This method will return Style of Header Cell\n\t * @return\n\t *\/\n\tprivate static CellStyle getHeaderStyle()\n\t{\n\t\tCellStyle style = wb.createCellStyle();\n\t    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());\n\t    style.setFillPattern(CellStyle.SOLID_FOREGROUND);\n\n\t    style.setBorderBottom(CellStyle.BORDER_THIN);\n\t    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderLeft(CellStyle.BORDER_THIN);\n\t    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderRight(CellStyle.BORDER_THIN);\n\t    style.setRightBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderTop(CellStyle.BORDER_THIN);\n\t    style.setTopBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setAlignment(CellStyle.ALIGN_CENTER);\n\n\t    return style;\n\t}\n\n\t\/**\n\t * This method will return style for Normal Cell\n\t * @return\n\t *\/\n\tprivate static CellStyle getNormalStyle()\n\t{\n\t\tCellStyle style = wb.createCellStyle();\n\n\t    style.setBorderBottom(CellStyle.BORDER_THIN);\n\t    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderLeft(CellStyle.BORDER_THIN);\n\t    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderRight(CellStyle.BORDER_THIN);\n\t    style.setRightBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setBorderTop(CellStyle.BORDER_THIN);\n\t    style.setTopBorderColor(IndexedColors.BLACK.getIndex());\n\t    style.setAlignment(CellStyle.ALIGN_CENTER);\n\n\t    return style;\n\t}\n\n\t\/**\n\t * @param args\n\t *\/\n\tpublic static void main(String[] args) throws Exception {\n\n\t\t wb =  new SXSSFWorkbook(100); \/\/ keep 100 rows in memory, exceeding rows will be flushed to disk\n\t\tsh = wb.createSheet("Sample sheet");\n\n\t    CellStyle headerStle= getHeaderStyle();\n\t    CellStyle normalStyle = getNormalStyle();\n\n\t\t for(int rownum = 0; rownum < 1000; rownum++){\n\t            Row row = sh.createRow(rownum);\n\t            for(int cellnum = 0; cellnum < 10; cellnum++){\n\n\t                Cell cell = row.createCell(cellnum);\n\t                String address = new CellReference(cell).formatAsString();\n\t                cell.setCellValue(address);\n\n\t                if(rownum == 0)\n\t                {\n\n\t                \tcell.setCellStyle(headerStle);\n\t                }\n\t                else\n\t                {\n\t                \tcell.setCellStyle(normalStyle);\n\t                }\n\t            }\n\n\t        }\n\n\t\t \/\/Below code Shows how to merge Cell\n\t\t sh.addMergedRegion(new CellRangeAddress(\n\t\t            0, \/\/first row (0-based)\n\t\t            0, \/\/last row  (0-based)\n\t\t            0, \/\/first column (0-based)\n\t\t            5  \/\/last column  (0-based)\n\t\t    ));\n\n\t\t autoResizeColumns();\n\n\t\t \/**\n\t\t  * To Auto-resize Row, We have to follow two steps\n\t\t  * 1. Set WordWrap property in CellStyle to true\n\t\t  * 2. Set setHeightInPoints of row likw this :\n\t\t  *  \trow.setHeightInPoints((totalHtmlLineBreak * sh.getDefaultRowHeightInPoints()));\n\t\t  *  \tWhere totalHtmlLineBreak is total lines for auto height\n\t\t  *\/\n\n\t        File f = new File("c:\/DeleteThis\/2\/Example2.xlsx");\n\n\t        if(!f.exists())\n\t        {\n\t        \t\/\/If directories are not available then create it\n\t        \tFile parent_directory = f.getParentFile();\n\t        \tif (null != parent_directory)\n\t        \t{\n\t        \t    parent_directory.mkdirs();\n\t        \t}\n\n\t        \tf.createNewFile();\n\t        }\n\n\t        FileOutputStream out = new FileOutputStream(f,false);\n\t        wb.write(out);\n\t        out.close();\n\n\t        \/\/ dispose of temporary files backing this workbook on disk\n\t        wb.dispose();\n\t        System.out.println("File is created");\n\t        \/\/Launch Excel File Created\n\t        Desktop.getDesktop().open(f);\n\t}\n}\n<\/pre>\n
Code Written above is very simple and self Explanatory. This is the Output of above code,<\/p>\n
\"Create<\/a>
Create Excel File in Java<\/figcaption><\/figure>\n
I have collected few common Questions asked on Apache POI, lets discuss it.<\/span><\/p>\n
1. How to Auto\u00a0Re-size\u00a0Column in Generated Excel Sheet?<\/strong>
\n Ans :<\/strong> We can use “autoSizeColumn(int ColumnNumber)” method of Sheet Object.<\/p>\n
2. How to add Styles in Cell or Column in Excel Sheet ?<\/strong>
\n Ans :<\/strong> As shown in above code, We have Object named “CellStyle” used in method “getHaderStyle()”. We can set Background Color as well and all other stuff.<\/p>\n
3. How to merge Columns in Excel ?<\/strong>
\n Ans :<\/strong> Using below code snippet<\/p>\n
\n\/\/Below code Shows how to merge Cell\n\t\t sh.addMergedRegion(new CellRangeAddress(\n\t\t            0, \/\/first row (0-based)\n\t\t            0, \/\/last row  (0-based)\n\t\t            0, \/\/first column (0-based)\n\t\t            5  \/\/last column  (0-based)\n\t\t    ));\n<\/pre>\n
4. How to Auto –\u00a0Re-size\u00a0row in Generated\u00a0<\/strong>Excel Sheet ?<\/strong>
\n Ans:<\/strong> To Auto-re-size row, we have to follow two steps:<\/p>\n
    \n
  • Set Word Wrap property to true in CellStyle<\/li>\n
  • Call method setHeightInPoints on row object<\/li>\n<\/ul>\n
    Sample Code :<\/strong><\/p>\n
    \n            \/**\n\t\t  * To Auto-resize Row, We have to follow to steps\n\t\t  * 1. Set WordWrap property in CellStyle to true\n\t\t  * 2. Set setHeightInPoints of row likw this :\n\t\t*\/\n\t\t    \trow.setHeightInPoints((totalHtmlLineBreak * sh.getDefaultRowHeightInPoints()));\n\t\t  \/**\n\t\t    *  \tWhere totalHtmlLineBreak is total lines for auto height\n\t\t  *\/\n<\/pre>\n
    I am waiting for your feedback and suggestions on this post. Happy Coding !!! \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
    Recently, I came across requirement to create ExcelSheet from thin Java Client used by Salesforce. So, I though to share my experience on Creating Excel Sheet in Java. As we know that Java is product of Oracle and Excel is product of Microsoft. Off-course, There will be no standard functionality available in Java to achieve […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[38,39,93,329],"class_list":["post-3556","post","type-post","status-publish","format-standard","hentry","category-java","tag-apache","tag-apache-poi","tag-excel","tag-java"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2453,"url":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/microsot-excel-change-content-of-alternate-cell-on-change-of-the-cell-value-using-macros\/","url_meta":{"origin":3556,"position":0},"title":"microsot Excel – Change content of alternate cell on change of the cell value using macros","author":"Jitendra","date":"September 28, 2011","format":false,"excerpt":"Using VBA script to change the content of alternate column on the basis of value changed in other column of excel sheet","rel":"","context":"In "Microsoft"","block_context":{"text":"Microsoft","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/microsoft\/"},"img":{"alt_text":"Change content of alternate cell on change of the cell value using macros","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/09\/Change-content-of-alternate-cell-on-change-of-the-cell-value-using-macros.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":3070,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/simple-guide-to-setup-ssl-in-tomcat\/","url_meta":{"origin":3556,"position":1},"title":"Simple guide to setup SSL in Tomcat","author":"Jitendra","date":"September 9, 2012","format":false,"excerpt":"I have enabled SSL in tomcat many times however initially I struggled to get it in running condition. So I thought to share a simple approach I am following now days. Step 1: Run tool \"Keytool\"\u009d provided by the JRE to create a \"keystore file\"\u009d. The command to run tool\u2026","rel":"","context":"In "JAVA"","block_context":{"text":"JAVA","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/"},"img":{"alt_text":"Tomcat SSL keytool to create keystore file","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/09\/Tomcat-SSL-keytool-to-create-keystore-file.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/09\/Tomcat-SSL-keytool-to-create-keystore-file.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/09\/Tomcat-SSL-keytool-to-create-keystore-file.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1482,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/servlet\/how-container-handles-the-servlet-request\/","url_meta":{"origin":3556,"position":2},"title":"How container handles the Servlet request","author":"Jitendra","date":"February 12, 2011","format":false,"excerpt":"How container handles the Servlet request or How the apache Tomcat works","rel":"","context":"In "Servlet"","block_context":{"text":"Servlet","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/servlet\/"},"img":{"alt_text":"Client Browse Servlet URL","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/02\/Client-Browse-Servlet-URL.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":5434,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/creating-custom-ant-plugin-or-task-in-java\/","url_meta":{"origin":3556,"position":3},"title":"Creating custom ANT plugin or Task in Java","author":"Jitendra","date":"March 19, 2016","format":false,"excerpt":"Tutorial to create custom Task in ANT with complete source code","rel":"","context":"In "JAVA"","block_context":{"text":"JAVA","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/"},"img":{"alt_text":"Create custom ANT Task in Java","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/03\/Creating-ANT-plugin.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/03\/Creating-ANT-plugin.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/03\/Creating-ANT-plugin.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/03\/Creating-ANT-plugin.gif?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/03\/Creating-ANT-plugin.gif?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":3218,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/salesforce-interview-question-part-15\/","url_meta":{"origin":3556,"position":4},"title":"Salesforce Interview Question \u2013 Part 15","author":"Jitendra","date":"May 24, 2013","format":false,"excerpt":"141 : User Wants to set the starting day in Calendar as \"Monday\" instead of \"Sunday\". How to get it done? Ans : Change the user locale to \"English ( United Kingdom ) \" in Personal information or User record. 142 : Why CSS is not working in PDF created\u2026","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1720,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/how-to-create-jsp-custom-tag-using-tag-interface-or-tagsupport\/","url_meta":{"origin":3556,"position":5},"title":"How to Create JSP Custom Tag \u2013 using Tag interface or TagSupport","author":"Jitendra","date":"March 17, 2011","format":false,"excerpt":"Tutorial of creating JSP Custom Tag \u2013 using Tag interface or TagSupport in JAVA","rel":"","context":"In "JAVA"","block_context":{"text":"JAVA","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/"},"img":{"alt_text":"How to Create Custom Tag in JSP","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/03\/How-to-Create-Custom-Tag-in-JSP.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/3556","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=3556"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/3556\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=3556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=3556"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=3556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}