Comprehensive Apache POI Tutorial: Excel File Handling in Java

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.3</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.3</version> </dependency> 

// Import necessary Apache POI classes for Excel handling import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; /** * ExcelReader class provides functionality to read and process Excel files (.xlsx format) * using Apache POI library. */ public class ExcelReader { /** * Reads an Excel file and prints its contents to the console. * This method reads the first sheet of the Excel file and processes each cell. * * @param filePath The path to the Excel file to be read */ public static void readExcel(String filePath) { // Use try-with-resources to automatically close the FileInputStream and Workbook // This ensures proper resource cleanup even if exceptions occur try (FileInputStream fis = new FileInputStream(filePath); Workbook workbook = new XSSFWorkbook(fis)) { // Get the first sheet (worksheet) from the workbook // Sheets are zero-indexed: 0 = first sheet, 1 = second sheet, etc. Sheet sheet = workbook.getSheetAt(0); // Iterate through each row in the sheet // The enhanced for loop automatically handles empty rows by skipping them for (Row row : sheet) { // Iterate through each cell in the current row for (Cell cell : row) { // Get the cell value and print it followed by a tab for formatting System.out.print(getCellValue(cell) + "\t"); } // Move to the next line after processing all cells in a row System.out.println(); } } catch (IOException e) { // Handle any IO exceptions that might occur during file operations // This could include file not found, permission issues, or corrupt files e.printStackTrace(); } } /** * Extracts the value from a cell based on its data type. * Handles different cell types including strings, numbers, dates, booleans, formulas, and blank cells. * * @param cell The cell from which to extract the value * @return The cell value as an appropriate Java object (String, Double, Date, Boolean, etc.) */ private static Object getCellValue(Cell cell) { // Switch statement to handle different cell types switch (cell.getCellType()) { case STRING: // Return string value for text cells return cell.getStringCellValue(); case NUMERIC: // Check if the numeric value represents a date if (DateUtil.isCellDateFormatted(cell)) { // Return Date object for date-formatted cells return cell.getDateCellValue(); } else { // Return Double for regular numeric values return cell.getNumericCellValue(); } case BOOLEAN: // Return Boolean value for true/false cells return cell.getBooleanCellValue(); case FORMULA: // Handle formula cells by evaluating them return evaluateFormula(cell); case BLANK: // Return empty string for empty cells return ""; default: // Return empty string for any other cell types (ERROR, _NONE, etc.) return ""; } } /** * Evaluates a formula cell and returns its computed value. * This method calculates the result of Excel formulas programmatically. * * @param cell The formula cell to evaluate * @return The computed value of the formula as an appropriate Java object */ private static Object evaluateFormula(Cell cell) { // Get the formula evaluator from the workbook // The CreationHelper is used to create various helper objects FormulaEvaluator evaluator = cell.getSheet().getWorkbook() .getCreationHelper().createFormulaEvaluator(); // Evaluate the formula and get the result as a CellValue object CellValue cellValue = evaluator.evaluate(cell); // Handle the evaluated result based on its data type switch (cellValue.getCellType()) { case STRING: // Return the string result of the formula return cellValue.getStringValue(); case NUMERIC: // Return the numeric result of the formula return cellValue.getNumberValue(); case BOOLEAN: // Return the boolean result of the formula return cellValue.getBooleanValue(); default: // Return empty string for other result types (blank, error, etc.) return ""; } } } 

/** * AdvancedExcelReader class provides enhanced Excel file processing capabilities * with better error handling, empty cell management, and structured data processing. */ public class AdvancedExcelReader { /** * Processes an Excel file by reading its contents and handling different data types appropriately. * This method automatically handles both .xls and .xlsx formats using WorkbookFactory. * * @param filePath The path to the Excel file to be processed */ public static void processExcelFile(String filePath) { // Use try-with-resources to automatically close the workbook // WorkbookFactory.create() automatically detects Excel format (.xls or .xlsx) try (Workbook workbook = WorkbookFactory.create(new File(filePath))) { // Get the first sheet from the workbook Sheet sheet = workbook.getSheetAt(0); // Configuration option to skip header row // Set to true if the first row contains column headers boolean skipHeader = true; // Determine the starting row index based on skipHeader flag // If skipHeader is true, start from row 1 (second row), otherwise from row 0 (first row) int startRow = skipHeader ? 1 : 0; // Iterate through all rows in the sheet from startRow to the last row // getLastRowNum() returns the last row index (0-based) for (int i = startRow; i <= sheet.getLastRowNum(); i++) { // Get the current row - may be null if the row is empty Row row = sheet.getRow(i); // Skip null rows (completely empty rows in Excel) if (row == null) continue; // Process the current row to extract and handle cell values processRow(row); } } catch (IOException e) { // Handle IO exceptions such as file not found, permission issues, or corrupt files e.printStackTrace(); } } /** * Processes an individual row by iterating through all its cells and handling each cell's data type. * Uses Row.MissingCellPolicy.CREATE_NULL_AS_BLANK to handle missing cells gracefully. * * @param row The Row object to be processed */ private static void processRow(Row row) { // Iterate through all cells in the row from first to last cell // getLastCellNum() returns the number of cells (1-based), so we use < instead of <= for (int j = 0; j < row.getLastCellNum(); j++) { // Get the cell at column j // CREATE_NULL_AS_BLANK policy ensures we get a blank cell instead of null // for missing cells (cells that were never created in Excel) Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); // Handle each cell based on its data type switch (cell.getCellType()) { case STRING: // Handle text cells handleString(cell.getStringCellValue()); break; case NUMERIC: // Handle numeric cells - check if it's a date or regular number if (DateUtil.isCellDateFormatted(cell)) { // Handle date-formatted numeric values handleDate(cell.getDateCellValue()); } else { // Handle regular numeric values handleNumber(cell.getNumericCellValue()); } break; case BOOLEAN: // Handle boolean (true/false) cells handleBoolean(cell.getBooleanCellValue()); break; case FORMULA: // Handle formula cells - get the formula string itself handleFormula(cell.getCellFormula()); break; case BLANK: // Handle empty cells handleEmptyCell(); break; // Note: Other cell types like ERROR are not handled in this switch statement } } } /** * Handles string cell values by printing them to console. * * @param value The string value from the cell */ private static void handleString(String value) { System.out.println("String: " + value); } /** * Handles numeric cell values by printing them to console. * * @param value The numeric value from the cell as a double */ private static void handleNumber(double value) { System.out.println("Number: " + value); } /** * Handles date cell values by printing them to console. * * @param value The date value from the cell */ private static void handleDate(Date value) { System.out.println("Date: " + value); } /** * Handles boolean cell values by printing them to console. * * @param value The boolean value from the cell */ private static void handleBoolean(boolean value) { System.out.println("Boolean: " + value); } /** * Handles formula cells by printing the formula string to console. * Note: This prints the formula text, not the calculated result. * * @param formula The formula string from the cell */ private static void handleFormula(String formula) { System.out.println("Formula: " + formula); } /** * Handles empty cells by printing a message to console. * This method is called for cells that are truly blank (not just containing empty strings). */ private static void handleEmptyCell() { System.out.println("Empty cell"); } } 

// Import necessary Apache POI classes for Excel creation and styling import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io.IOException; import java.util.*; /** * ExcelGenerator class provides functionality to create Excel files from Java collections * Supports dynamic data structure using List of Maps with proper formatting and styling */ public class ExcelGenerator { /** * Creates an Excel file from a List of Maps data structure * Each Map represents a row, with keys matching the header names * * @param data List of Maps containing the data to export * @param headers Array of column headers in desired order * @param filePath Output file path for the Excel file */ public static void createExcelFromList(List<Map<String, Object>> data, String[] headers, String filePath) { // Use try-with-resources to ensure proper cleanup of workbook and output stream try (Workbook workbook = new XSSFWorkbook(); // Create new XSSF workbook for .xlsx format FileOutputStream fos = new FileOutputStream(filePath)) { // Output stream to write file // Create a new sheet named "Data" in the workbook Sheet sheet = workbook.createSheet("Data"); // Create the header row with styling createHeaderRow(sheet, headers, workbook); // Create data rows from the List of Maps createDataRows(sheet, data, headers, workbook); // Write the workbook content to the output stream (file) workbook.write(fos); } catch (IOException e) { // Handle any IO exceptions during file creation e.printStackTrace(); } } /** * Creates the header row with styled cells * * @param sheet The worksheet to add headers to * @param headers Array of header names * @param workbook The workbook for creating styles */ private static void createHeaderRow(Sheet sheet, String[] headers, Workbook workbook) { // Create the first row (row 0) for headers Row headerRow = sheet.createRow(0); // Create a custom style for header cells CellStyle headerStyle = createHeaderStyle(workbook); // Iterate through headers and create cells for (int i = 0; i < headers.length; i++) { Cell cell = headerRow.createCell(i); // Create cell at column i cell.setCellValue(headers[i]); // Set header text cell.setCellStyle(headerStyle); // Apply header styling } } /** * Creates data rows from the List of Maps * * @param sheet The worksheet to add data to * @param data List of Maps containing row data * @param headers Array of headers to maintain column order * @param workbook The workbook for cell creation and styling */ private static void createDataRows(Sheet sheet, List<Map<String, Object>> data, String[] headers, Workbook workbook) { int rowNum = 1; // Start from row 1 (row 0 is headers) // Iterate through each Map (each representing a row of data) for (Map<String, Object> rowData : data) { // Create a new row for each data item Row row = sheet.createRow(rowNum++); // Iterate through headers to maintain column order for (int i = 0; i < headers.length; i++) { String header = headers[i]; // Get current column header Object value = rowData.get(header); // Get value from map using header as key // Create cell with appropriate data type handling createCell(row, i, value, workbook); } } // Auto-size all columns to fit content after all data is added for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); // Adjust column width to fit content } } /** * Creates a cell with proper data type handling and formatting * Supports String, Number, Boolean, Date, and null values * * @param row The row to add the cell to * @param column The column index for the cell * @param value The value to put in the cell (can be various types) * @param workbook The workbook for creating styles and formats */ private static void createCell(Row row, int column, Object value, Workbook workbook) { // Create cell at specified column Cell cell = row.createCell(column); // Handle different data types with appropriate Excel cell types if (value == null) { // Handle null values as empty strings cell.setCellValue(""); } else if (value instanceof String) { // Handle string values cell.setCellValue((String) value); } else if (value instanceof Number) { // Handle numeric values (Integer, Double, Float, etc.) // Convert to double since Excel uses double precision cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Boolean) { // Handle boolean values cell.setCellValue((Boolean) value); } else if (value instanceof Date) { // Handle date values with proper formatting cell.setCellValue((Date) value); // Create date format style CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); // Set date format pattern (month/day/year hour:minute) dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); // Apply date formatting to the cell cell.setCellStyle(dateStyle); } // Note: Additional data types can be added here as needed } /** * Creates a styled CellStyle for header cells * Features: Bold white text on blue background * * @param workbook The workbook for creating styles and fonts * @return Configured CellStyle for headers */ private static CellStyle createHeaderStyle(Workbook workbook) { // Create new cell style CellStyle style = workbook.createCellStyle(); // Create and configure font Font font = workbook.createFont(); font.setBold(true); // Make text bold font.setColor(IndexedColors.WHITE.getIndex()); // White text color // Apply font to style style.setFont(font); // Set background color to blue with solid fill pattern style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); return style; } /** * Example usage demonstrating how to use the ExcelGenerator * Creates sample employee data and exports to Excel */ public static void main(String[] args) { // Create sample data as List of Maps List<Map<String, Object>> data = new ArrayList<>(); // First employee record Map<String, Object> row1 = new HashMap<>(); row1.put("Name", "John Doe"); row1.put("Age", 30); row1.put("Salary", 50000.0); row1.put("HireDate", new Date()); data.add(row1); // Second employee record Map<String, Object> row2 = new HashMap<>(); row2.put("Name", "Jane Smith"); row2.put("Age", 25); row2.put("Salary", 45000.0); row2.put("HireDate", new Date()); data.add(row2); // Define column headers in desired order String[] headers = {"Name", "Age", "Salary", "HireDate"}; // Generate Excel file createExcelFromList(data, headers, "employees.xlsx"); System.out.println("Excel file generated successfully!"); } } 

public class LargeExcelGenerator { public static void createLargeExcel(String filePath, int rowCount) { // Keep 100 rows in memory, exceeding rows will be flushed to disk try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); FileOutputStream fos = new FileOutputStream(filePath)) { Sheet sheet = workbook.createSheet("Large Data"); // Create header Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("ID"); headerRow.createCell(1).setCellValue("Value"); // Create data rows for (int i = 1; i <= rowCount; i++) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(i); row.createCell(1).setCellValue(Math.random() * 1000); // Periodically flush rows to disk if (i % 1000 == 0) { ((SXSSFSheet) sheet).flushRows(100); } } workbook.write(fos); workbook.dispose(); // Clean up temporary files } catch (IOException e) { e.printStackTrace(); } } } 

public class FormulaExample { public static void createExcelWithFormulas(String filePath) { try (Workbook workbook = new XSSFWorkbook(); FileOutputStream fos = new FileOutputStream(filePath)) { Sheet sheet = workbook.createSheet("Formulas"); // Create data for (int i = 0; i < 5; i++) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(i + 1); } // Create formula cell Row formulaRow = sheet.createRow(5); Cell formulaCell = formulaRow.createCell(0); formulaCell.setCellFormula("SUM(A1:A5)"); // Evaluate formula FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(formulaCell); workbook.write(fos); } catch (IOException e) { e.printStackTrace(); } } } 

public class ExcelFormatHandler { public static Workbook createWorkbook(boolean useXlsx) { if (useXlsx) { return new XSSFWorkbook(); } else { return new HSSFWorkbook(); } } public static Workbook readWorkbook(String filePath) throws IOException { FileInputStream fis = new FileInputStream(filePath); if (filePath.endsWith(".xlsx")) { return new XSSFWorkbook(fis); } else if (filePath.endsWith(".xls")) { return new HSSFWorkbook(fis); } else { throw new IllegalArgumentException("Unsupported file format"); } } } 

public class SafeExcelHandler { public static void safeReadExcel(String filePath) { Workbook workbook = null; FileInputStream fis = null; try { fis = new FileInputStream(filePath); workbook = WorkbookFactory.create(fis); // Process workbook processWorkbook(workbook); } catch (IOException | EncryptedDocumentException | InvalidFormatException e) { System.err.println("Error processing Excel file: " + e.getMessage()); } finally { // Close resources safely try { if (workbook != null) { workbook.close(); } if (fis != null) { fis.close(); } } catch (IOException e) { System.err.println("Error closing resources: " + e.getMessage()); } } } private static void processWorkbook(Workbook workbook) { // Your processing logic here } }