Using Apache POI for Excel Import and Export in Java

1. Introduction

Importing and exporting files to a database is a common feature in many systems. This demo shows the basic principles of Java‑based file import/export using Apache POI, useful for developers who have not worked with it before.

The main technologies discussed are POI and iReport; POI is used for bulk data import, while iReport handles report export. Other libraries such as jxl are mentioned but are outdated for Office 2007+.

2. Detailed POI Usage

2.1 What is Apache POI?

Apache POI is an open‑source library from the Apache Software Foundation that provides APIs for Java programs to read and write Microsoft Office file formats.

2.2 Adding POI JARs

This example uses a Maven project with poi-3.14 and poi-ooxml-3.14 . Newer versions (3.16) exist, but APIs changed after 3.15, so be aware of possible differences.

org.apache.poi
poi
3.14
org.apache.poi
poi-ooxml
3.14

2.3 POI API Overview

2.3.1 Structure

HSSF – read/write Microsoft Excel (xls) files. XSSF – read/write Microsoft Excel OOXML (xlsx) files. HWPF – read/write Microsoft Word files. HSLF – read/write Microsoft PowerPoint files. HDGF – read/write Microsoft Visio files.

2.3.2 Objects

The article focuses on HSSF and XSSF. HSSF works with Excel 2003‑format .xls files, while XSSF works with Excel 2007+ .xlsx files. Their package locations are org.apache.poi.hssf.usermodel and org.apache.poi.xssf.usermodel respectively.

Common HSSF components:

HSSFWorkbook – workbook object HSSFSheet – sheet HSSFRow – row HSSFCell – cell HSSFFont – font HSSFDataFormat – date format HSSFHeader – header HSSFFooter – footer (visible only when printing)

Common XSSF components:

XSSFWorkbook – workbook object XSSFSheet – sheet XSSFRow – row XSSFCell – cell XSSFFont – font XSSFDataFormat – date format

2.3.3 Shared Cell Type Descriptions

Both components share the same cell‑type constants, e.g., CELL_TYPE_STRING , CELL_TYPE_NUMERIC , etc.

Cell Type
Description
CELL_TYPE_BLANK
Blank cell
CELL_TYPE_BOOLEAN
Boolean (true/false)
CELL_TYPE_ERROR
Error value
CELL_TYPE_FORMULA
Formula result
CELL_TYPE_NUMERIC
Numeric data
CELL_TYPE_STRING
String (text)

2.3.4 Operational Steps (example with HSSF)

An Excel file is organized as a Workbook containing multiple Sheet s, each sheet containing rows, and each row containing cells.

Open or create an HSSFWorkbook (Excel file object).

Obtain or create a HSSFSheet from the workbook.

From the sheet obtain a HSSFRow , then a HSSFCell .

Read or write the cell.

3. Code Implementation

3.1 Result Screenshots

Typical screenshots of the two Excel formats, the code structure, and the import/export results are shown (images omitted).

3.2 Detailed Code

The example builds on a Spring + Spring MVC + MyBatis project.

Controller

package com.allan.controller;

import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.formula.functions.Mode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.allan.pojo.Student;
import com.allan.service.StudentService;

/**
 * @author 小卖铺的老爷爷
 */
@Controller
public class StudentController {
    @Autowired
    private StudentService studentService;

    /**
     * Batch import form data
     */
    @RequestMapping(value="/importExcel", method=RequestMethod.POST)
    public String importExcel(@RequestParam("myfile") MultipartFile myFile) {
        ModelAndView modelAndView = new ModelAndView();
        try {
            Integer num = studentService.importExcel(myFile);
        } catch (Exception e) {
            modelAndView.addObject("msg", e.getMessage());
            return "index";
        }
        modelAndView.addObject("msg", "数据导入成功");
        return "index";
    }

    @RequestMapping(value="/exportExcel", method=RequestMethod.GET)
    public void exportExcel(HttpServletResponse response) {
        try {
            studentService.exportExcel(response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Service Implementation

package com.allan.service.impl;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.allan.mapper.StudentMapper;
import com.allan.pojo.Student;
import com.allan.service.StudentService;

/**
 * @author 小卖铺的老爷爷
 */
@Service
public class StudentServiceImpl implements StudentService {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    @Autowired
    private StudentMapper studentMapper;

    /** Import Excel, compatible with xls and xlsx */
    @SuppressWarnings("resource")
    public Integer importExcel(MultipartFile myFile) throws Exception {
        // 1. Open or create workbook based on file suffix
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            workbook = new HSSFWorkbook(myFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            workbook = new XSSFWorkbook(myFile.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }
        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();
        if (rows == 0) {
            throw new Exception("请填写数据");
        }
        for (int i = 1; i <= rows + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Student student = new Student();
                String name = getCellValue(row.getCell(0));
                student.setName(name);
                String classes = getCellValue(row.getCell(1));
                student.setClasses(classes);
                String scoreString = getCellValue(row.getCell(2));
                if (!StringUtils.isEmpty(scoreString)) {
                    Integer score = Integer.parseInt(scoreString);
                    student.setScore(score);
                }
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String dateString = getCellValue(row.getCell(3));
                if (!StringUtils.isEmpty(dateString)) {
                    Date date = sdf.parse(dateString);
                    student.setTime(date);
                }
                studentMapper.insert(student);
            }
        }
        return rows - 1;
    }

    /** Get cell content as String */
    public String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

    /** Export Excel file */
    public void exportExcel(HttpServletResponse response) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("班级");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("分数");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("时间");
        cell.setCellStyle(style);
        List
list = studentMapper.selectAll();
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Student stu = list.get(i);
            row.createCell(0).setCellValue(stu.getName());
            row.createCell(1).setCellValue(stu.getClasses());
            row.createCell(2).setCellValue(stu.getScore());
            cell = row.createCell(3);
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));
        }
        OutputStream output = response.getOutputStream();
        response.reset();
        String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }
}

3.3 Export API Enhancements

The basic export shown above can be extended with cell merging, row height/column width adjustments, and richer styling (borders, colors, fonts, etc.).

3.3.1 Merging Cells

Use HSSFSheet.addMergedRegion(CellRangeAddress region) . The CellRangeAddress constructor takes firstRow, lastRow, firstCol, lastCol .

public int addMergedRegion(CellRangeAddress region)

3.3.2 Setting Row Height and Column Width

HSSFSheet sheet = wb.createSheet();
sheet.setDefaultRowHeightInPoints(10);
sheet.setDefaultColumnWidth(20);
sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50); // width unit = 1/256 of a character

3.3.3 Cell Style Settings

1. Create HSSFCellStyle . 2. Configure alignment, fill pattern, foreground/background colors, borders, data format, etc. 3. Apply the style to a cell or a row.

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);
cellStyle.setFillForegroundColor(HSSFColor.RED.index);
cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);
cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell.setCellStyle(cellStyle);
row.setRowStyle(cellStyle);

3.3.4 Font Style Settings

1. Create HSSFFont via HSSFWorkbook.createFont() . 2. Set font name, size, color, bold, italic, underline, etc. 3. Attach the font to a cell style and apply it.

HSSFWorkbook wb = new HSSFWorkbook();
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)20);
font.setColor(HSSFColor.BLUE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setItalic(true);
font.setUnderline(HSSFFont.U_SINGLE);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);

Using POI for Excel export involves many detailed steps, but the library provides full control over the generated file.

Source code repository: https://github.com/allanzhuo/myport.git

END