Java POI Excel Guide: Write & Read .xlsx with Formulas, Styles & Dates

Maven Dependencies

Create a Maven project and add the poi-ooxml dependency (version 5.2.1) for .xlsx support, plus junit 4.12 for testing. The article notes that modern Excel files use the .xlsx format, so only poi-ooxml is required. Refresh Maven to download the JARs.

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.1</version>
</dependency>
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
  <scope>test</scope>
</dependency>

Project Structure

The project contains three main parts: com.jobs.employee – a Java Bean entity class holding employee data (name, daily salary, work days, pay time). com.jobs.exceltest – JUnit test class with WriteExcel and ReadExcel methods. src/tempfile – directory where generated Excel files are stored.

The entity class is shown below:

package com.jobs;

import java.util.Date;

public class employee {
    //姓名
    private String empName;
    //每日薪水
    private Integer dayMoney;
    //出勤天数
    private Integer workDays;
    //领取薪水的时间
    private Date payTime;

    public employee() {
    }

    public employee(String empName, Integer dayMoney, Integer workDays, Date payTime) {
        this.empName = empName;
        this.dayMoney = dayMoney;
        this.workDays = workDays;
        this.payTime = payTime;
    }

    //相关字段的 Get 和 Set 方法，省略...
}

Writing Excel ( WriteExcel )

The WriteExcel method creates an XSSFWorkbook (for .xlsx), defines three cell styles (title bold centered, data centered, date formatted as yyyy-MM-dd HH:mm:ss), sets column 5 width to 5000 units (width * 256), and writes sample data with a formula column that multiplies daily salary by work days.

Key implementation details:

Uses try-with-resources for Workbook and FileOutputStream.

Creates sheet named "员工薪水" (Employee Salary).

Title row uses CellStyle with bold font and center alignment.

Data rows use a centered style; date column uses a style with a data format.

Formula column (column E, index 4) uses Cell.setCellFormula("C" + rowNum + "*D" + rowNum) where rowNum is 1-based Excel row number.

Sample data: three employees with names "候胖胖", "任肥肥", "李敦敦", daily salaries 200, 180, 150, work days 21, 22, 25, and timestamps incremented by 12 and 15 minutes from a base Calendar set to 2022-04-05 10:11:20.

Finally writes workbook to tempfile/money.xlsx.

@Test
public void WriteExcel() throws Exception {
    File f = new File("tempfile/money.xlsx");
    try (Workbook wb = new XSSFWorkbook();
         FileOutputStream os = new FileOutputStream(f)) {
        // .xls 使用 HSSFWorkbook（现在基本上很少用到了）
        // .xlsx 使用 XSSFWorkbook
        Sheet st = wb.createSheet("员工薪水");

        // 统一设置单元格样式
        // 标题行：字体加粗，居中
        CellStyle cstitle = wb.createCellStyle();
        cstitle.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        cstitle.setFont(font);

        // 数据行：居中
        CellStyle csfield = wb.createCellStyle();
        csfield.setAlignment(HorizontalAlignment.CENTER);

        // 日期列：居中 + 格式
        CellStyle csdate = wb.createCellStyle();
        csdate.setAlignment(HorizontalAlignment.CENTER);
        csdate.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

        // 第5列（日期列）列宽设大一些
        // 参数：列索引，列宽 * 256
        st.setColumnWidth(5, 5000);

        // 模拟数据库数据
        List<employee> emplist = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        // Calendar 月份从0开始，即 2022-04-05 10:11:20
        calendar.set(2022, 3, 5, 10, 11, 20);
        emplist.add(new employee("候胖胖", 200, 21, calendar.getTime()));
        calendar.add(Calendar.MINUTE, 12);
        emplist.add(new employee("任肥肥", 180, 22, calendar.getTime()));
        calendar.add(Calendar.MINUTE, 15);
        emplist.add(new employee("李敦敦", 150, 25, calendar.getTime()));

        // 标题行
        String[] titles = {"序号", "姓名", "每日薪水", "出勤天数", "薪水统计", "领取时间"};
        Row rowtitle = st.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            Cell celltitle = rowtitle.createCell(i);
            celltitle.setCellValue(titles[i]);
            celltitle.setCellStyle(cstitle);
        }

        // 数据行
        int rowindex = 1;
        for (employee emp : emplist) {
            Row rowtemp = st.createRow(rowindex);

            // 序号
            Cell Cell0 = rowtemp.createCell(0);
            Cell0.setCellValue(rowindex);
            Cell0.setCellStyle(csfield);

            // 姓名
            Cell Cell1 = rowtemp.createCell(1);
            Cell1.setCellValue(emp.getEmpName());
            Cell1.setCellStyle(csfield);

            // 每日薪水
            Cell Cell2 = rowtemp.createCell(2);
            Cell2.setCellValue(emp.getDayMoney());
            Cell2.setCellStyle(csfield);

            // 出勤天数
            Cell Cell3 = rowtemp.createCell(3);
            Cell3.setCellValue(emp.getWorkDays());
            Cell3.setCellStyle(csfield);

            // 薪水统计（使用 Excel 公式）
            // 注意：Excel 行号和列号从1开始
            int formulaRowIndex = rowindex + 1;
            Cell Cell4 = rowtemp.createCell(4);
            Cell4.setCellFormula("C" + formulaRowIndex + "*D" + formulaRowIndex);
            Cell4.setCellStyle(csfield);

            // 领取时间
            Cell Cell5 = rowtemp.createCell(5);
            Cell5.setCellValue(emp.getPayTime());
            Cell5.setCellStyle(csdate);

            rowindex++;
        }

        wb.write(os);
    }
}

Reading Excel ( ReadExcel )

The ReadExcel method opens the generated money.xlsx, retrieves the "员工薪水" sheet, and uses an XSSFFormulaEvaluator to evaluate formula cells. It iterates rows with an Iterator, skipping the header row (rowSeq == 0). For each data row, it reads cells using type-specific getters:

Numeric cells (sequence, daily salary, work days, formula result) via getNumericCellValue() cast to int.

String cell (name) via getStringCellValue().

Date cell (pay time) via getDateCellValue() formatted with SimpleDateFormat.

Formula cell (column 4) evaluated with formulaEvaluator.evaluateInCell() to get the computed numeric value.

The author emphasizes that using the wrong getter (e.g., calling getStringCellValue() on a numeric cell) throws an exception. The output is printed as comma-separated Chinese commas (，).

@Test
public void ReadExcel() throws Exception {
    try (XSSFWorkbook wb = new XSSFWorkbook("tempfile/money.xlsx")) {
        Sheet st = wb.getSheet("员工薪水");
        XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator(wb);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        int rowSeq = 0;
        Iterator<Row> iterator = st.iterator();
        while (iterator.hasNext()) {
            Row row = iterator.next();
            List<String> list = new ArrayList<>();
            if (rowSeq == 0) {
                // 读取标题行，全是字符串
                // 一般情况下，你能提前知道列数，demo 中有 6 列
                for (int i = 0; i < 6; i++) {
                    list.add(row.getCell(i).getStringCellValue());
                }
            } else {
                // 数据行：必须按单元格数据类型用对应方法读取
                // 序号
                list.add(String.valueOf((int) row.getCell(0).getNumericCellValue()));
                // 姓名
                list.add(row.getCell(1).getStringCellValue());
                // 每日薪水
                list.add(String.valueOf((int) row.getCell(2).getNumericCellValue()));
                // 出勤天数
                list.add(String.valueOf((int) row.getCell(3).getNumericCellValue()));
                // 薪水统计（公式计算结果）
                XSSFCell xssfCell = formulaEvaluator.evaluateInCell(row.getCell(4));
                list.add(String.valueOf((int) xssfCell.getNumericCellValue()));
                // 领取时间
                Date date = row.getCell(5).getDateCellValue();
                list.add(sdf.format(date));
            }
            System.out.println(String.join("，", list));
            rowSeq++;
        }
    }
}

Generated Excel Output

The article includes a screenshot of the resulting money.xlsx file showing the header row and three data rows with the formula column displaying calculated totals (4200, 3960, 3750) and formatted timestamps.

Console Output

Running ReadExcel prints each row to the console, as shown in the screenshot.

Fixing log4j2 Errors

When running the tests, the console shows:

ERROR StatusLogger Log4j2 could not find a logging implementation. Please add log4j-core to the classpath. Using SimpleLogger to log to the console...

The solution requires two steps:

Add the log4j-core dependency (version 2.17.2).

Place a log4j2.xml configuration file in src/main/resources (or resources directory) with a console appender and root logger at INFO level.

<dependency>
  <groupId>org.apache.logging.log4j</groupId>
  <artifactId>log4j-core</artifactId>
  <version>2.17.2</version>
</dependency>

<Configuration status="warn">
  <Appenders>
    <Console name="Console" target="SYSTEM_OUT">
      <PatternLayout pattern="[%-5p] %d %c - %m%n"/>
    </Console>
    <!-- File appender commented out -->
  </Appenders>
  <Loggers>
    <Root level="INFO">
      <AppenderRef ref="Console"/>
    </Root>
    <!-- Logger for file commented out -->
  </Loggers>
</Configuration>

After adding the dependency and configuration, refresh Maven and rerun the tests; the error disappears.