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 for their POI 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.
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)” component.
Prerequisite :
Add all jar files downloaded from Apache POI download site in Java Program’s build path.
Demo Code :
package in.shivasoft; import java.awt.Desktop; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFWorkbook; /** * @author Jitendra Zaa * */ public class CreateExcelSheet { static SXSSFWorkbook wb ; static Sheet sh ; /** * This method demonstrates how to Auto resize Excel column */ private static void autoResizeColumns() { for(int colIndex = 0; colIndex < 10 ; colIndex++) { sh.autoSizeColumn(colIndex); } } /** * This method will return Style of Header Cell * @return */ private static CellStyle getHeaderStyle() { CellStyle style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); return style; } /** * This method will return style for Normal Cell * @return */ private static CellStyle getNormalStyle() { CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); return style; } /** * @param args */ public static void main(String[] args) throws Exception { wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk sh = wb.createSheet("Sample sheet"); CellStyle headerStle= getHeaderStyle(); CellStyle normalStyle = getNormalStyle(); for(int rownum = 0; rownum < 1000; rownum++){ Row row = sh.createRow(rownum); for(int cellnum = 0; cellnum < 10; cellnum++){ Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); if(rownum == 0) { cell.setCellStyle(headerStle); } else { cell.setCellStyle(normalStyle); } } } //Below code Shows how to merge Cell sh.addMergedRegion(new CellRangeAddress( 0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); autoResizeColumns(); /** * To Auto-resize Row, We have to follow two steps * 1. Set WordWrap property in CellStyle to true * 2. Set setHeightInPoints of row likw this : * row.setHeightInPoints((totalHtmlLineBreak * sh.getDefaultRowHeightInPoints())); * Where totalHtmlLineBreak is total lines for auto height */ File f = new File("c:/DeleteThis/2/Example2.xlsx"); if(!f.exists()) { //If directories are not available then create it File parent_directory = f.getParentFile(); if (null != parent_directory) { parent_directory.mkdirs(); } f.createNewFile(); } FileOutputStream out = new FileOutputStream(f,false); wb.write(out); out.close(); // dispose of temporary files backing this workbook on disk wb.dispose(); System.out.println("File is created"); //Launch Excel File Created Desktop.getDesktop().open(f); } }
Code Written above is very simple and self Explanatory. This is the Output of above code,
I have collected few common Questions asked on Apache POI, lets discuss it.
1. How to Auto Re-size Column in Generated Excel Sheet?
Ans : We can use “autoSizeColumn(int ColumnNumber)” method of Sheet Object.
2. How to add Styles in Cell or Column in Excel Sheet ?
Ans : 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.
3. How to merge Columns in Excel ?
Ans : Using below code snippet
//Below code Shows how to merge Cell sh.addMergedRegion(new CellRangeAddress( 0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) ));
4. How to Auto – Re-size row in Generated Excel Sheet ?
Ans: To Auto-re-size row, we have to follow two steps:
- Set Word Wrap property to true in CellStyle
- Call method setHeightInPoints on row object
Sample Code :
/** * To Auto-resize Row, We have to follow to steps * 1. Set WordWrap property in CellStyle to true * 2. Set setHeightInPoints of row likw this : */ row.setHeightInPoints((totalHtmlLineBreak * sh.getDefaultRowHeightInPoints())); /** * Where totalHtmlLineBreak is total lines for auto height */
I am waiting for your feedback and suggestions on this post. Happy Coding !!! 🙂
Leave a Reply