Apache POI, Java

Reading an Excel file using Apache POI in Java

Here is the code to read an Excel file using Apache POI library in Java. The Apache POI library is a Java API which allows us to manipulate Microsoft documents.

Make sure you download and add the POI JAR file to your project’s class path before running the code. The Apache POI JAR file can be found here.

	public static void main(String[] args) throws IOException {
		
		// Location of the source file
		String sourceFilePath = "C:/Users/devesh_sharma/Documents/TestFile.xls";
		
		FileInputStream fileInputStream = null;
		
		// Array List to store the excel sheet data
		List excelSheetData = new ArrayList();
		
        try {
            
        	// FileInputStream to read the excel file
            fileInputStream = new FileInputStream(sourceFilePath);
 
            // Create an excel workbook
            HSSFWorkbook excelWorkBook = new HSSFWorkbook(fileInputStream);
            
            // Retrieve the first sheet of the workbook.
            HSSFSheet excelSheet = excelWorkBook.getSheetAt(0);
 
            // Iterate through the sheet rows and cells. 
            // Store the retrieved data in an arrayList
            Iterator rows = excelSheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
 
                List cellData = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    cellData.add(cell);
                }
 
                excelSheetData.add(cellData);
            }
            
            // Print retrieved data to the console
            for (int rowNum = 0; rowNum < excelSheetData.size(); rowNum++) {
            	
                List list = (List) excelSheetData.get(rowNum);
                
                for (int cellNum = 0; cellNum < list.size(); cellNum++) {
                    
                	HSSFCell cell = (HSSFCell) list.get(cellNum);
                	
                    if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    	System.out.print(cell.getRichStringCellValue().getString() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    	System.out.print(cell.getNumericCellValue() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                    	System.out.println(cell.getBooleanCellValue() + " ");
                    }
                }
                System.out.println("");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
            	fileInputStream.close();
            }
        }
	}
}

If you are looking for code to create/write an excel file using Apache POI in Java, please refer to this post.

Apache POI, Java

Auto sizing columns in Excel files created with POI in Java

So, I was going through my usage reports of the Amazon EC2 Cloud Computing platform and it turned out to be quite a frustrating experience. Reason? Have a look yourself.

Noticed anything? Well, the columns are not auto sized or expanded to include the full text. It is frustrating from the usability point of view as the user will have to expand each column to see the full text.

If you are creating Excel files in Java using Apache POI API, it is very simple to auto size the columns. After you are done adding data to the sheet, you just need to call a method to auto fit the column width.

Here is the code to auto size the first 10 columns of the spread sheet.


// Create the spreadsheet
HSSFSheet spreadSheet = workBook.createSheet("Hello_World");

// Create rows and write to the sheet

// Auto size the column widths
for(int columnIndex = 0; columnIndex < 10; columnIndex++) {
     spreadSheet.autoSizeColumn(columnIndex);
}

Note: If you are looking for how to create excel files using Apache POI API in Java, please refer this post.

Apache POI, Java

Creating an Excel file in Java

Here is the code to write data to an excel file in Java using Apache POI library. The Apache POI library is a Java API which allows us to manipulate Microsoft documents.

More details about Apache POI library can be found at Apache POI Project Page

Make sure you download and add the POI JAR file to your project’s class path before running the code. The Apache POI JAR file can be found here.

	public static void main(String[] args) throws IOException {
		
		// Directory path where the xls file will be created
		String destinationFilePath = "C:/Users/devesh_sharma/Documents/HelloWorld.xls";

		// Create object of FileOutputStream
		FileOutputStream fout = new FileOutputStream(destinationFilePath);
		
		// Build the Excel File
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		HSSFWorkbook workBook = new HSSFWorkbook();

		// Create the spreadsheet
		HSSFSheet spreadSheet = workBook.createSheet("Hello_World");
		
		// Create the first row
		HSSFRow row = spreadSheet.createRow((short) 0);
		
		// Create the cells and write to the file
		HSSFCell cell;
		
		// Write Hello
		cell = row.createCell(0);
		cell.setCellValue(new HSSFRichTextString("Hello"));
		
		// Write World
		cell = row.createCell(1);
		cell.setCellValue(new HSSFRichTextString("World"));
		
		workBook.write(outputStream);
		
		outputStream.writeTo(fout);
		outputStream.close();
		fout.close();
	}