Java 使用 Apache POI 导出 Excel 文件

1. Excel 依赖包 POI

1.1 Maven 配置

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.7</version>
</dependency>

1.2 资源下载

Apache POI 官方下载

2. Java 代码示例

2.1 Excel 工具类

package com.mk.util;
import com.mk.bean.LogInfo;
import org.apache.poi.hssf.usermodel.*;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.util.CellRangeAddress;

public class Excels {
    public static byte[] export(String sheetName, String title, List<LogInfo> list, int start, int end) {
        if (list == null || start > end) {
            return null;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        initWorkbook(workbook, sheetName, title, list, start, end);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
            byte[] bytes = os.toByteArray();
            return bytes;
        } catch (IOException ex) {
            Logger.getLogger(Excels.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                os.close();
            } catch (IOException ex) {
                Logger.getLogger(Excels.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    private static final int MAX_ROWS = 65535;
    private static void initWorkbook(HSSFWorkbook workbook, String sheetName, String title, List<LogInfo> list, int start, int end) {
        assert end - start <= MAX_ROWS - 2;
        HSSFSheet sheet = workbook.createSheet(sheetName);
        initTitle(workbook, sheet, title, 0, 16, 800, 0, 0, 0, 2);
        HSSFCellStyle attrCellStyle = createStyle(workbook, 10, true, HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle defaultCellStyle = createStyle(workbook, 10, true, HSSFFont.BOLDWEIGHT_NORMAL);
        HSSFRow attrRow = sheet.createRow(1);
        final int baseFont = 10;
        sheet.setColumnWidth(0, baseFont * 400);
        sheet.setColumnWidth(1, baseFont * 800);
        sheet.setColumnWidth(2, baseFont * 1200);
        HSSFCell cell = attrRow.createCell(0);
        cell.setCellValue("索引");
        cell.setCellStyle(attrCellStyle);
        cell = attrRow.createCell(1);
        cell.setCellValue("时间");
        cell.setCellStyle(attrCellStyle);
        cell = attrRow.createCell(2);
        cell.setCellValue("信息");
        cell.setCellStyle(attrCellStyle);
        DateFormat dateFomater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 2; start < end; i++) {
            LogInfo logInfo = list.get(start++);
            HSSFRow row = sheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(logInfo.getIndex());
            cell.setCellStyle(defaultCellStyle);
            cell = row.createCell(1);
            cell.setCellValue(dateFomater.format(logInfo.getTime()));
            cell.setCellStyle(defaultCellStyle);
            cell = row.createCell(2);
            cell.setCellValue(logInfo.getInfo());
            cell.setCellStyle(defaultCellStyle);
        }
    }
    private static void initTitle(HSSFWorkbook workbook, HSSFSheet sheet, String title, int rowIndex, int fontSize, int height, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFCellStyle titleCellStyle = createStyle(workbook, fontSize, true, HSSFFont.BOLDWEIGHT_BOLD);
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.setHeight((short) height);
        HSSFCell titleCell = titleRow.createCell(rowIndex);
        titleCell.setCellValue(title);
        titleCell.setCellStyle(titleCellStyle);
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }
    private static HSSFCellStyle createStyle(HSSFWorkbook workbook, int fontSize, boolean wrapText, short boldweight) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) fontSize);
        font.setBoldweight(boldweight);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setWrapText(wrapText);
        cellStyle.setFont(font);
        return cellStyle;
    }
}

2.2 数据模型

package com.mk.bean;
import java.util.Date;

public class LogInfo {
    private int index;
    private Date time;
    private String info;

    public int getIndex() {
        return index;
    }

    public void setIndex(int index) {
        this.index = index;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }
}

2.3 测试代码

package com.mk.testmaven;
import com.mk.bean.LogInfo;
import com.mk.util.Excels;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        List<LogInfo> list = new ArrayList<>();
        LogInfo logInfo = new LogInfo();
        logInfo.setIndex(0);
        logInfo.setInfo("你好");
        logInfo.setTime(new Date(2018, 9, 12));
        list.add(logInfo);
        logInfo = new LogInfo();
        logInfo.setIndex(1);
        logInfo.setInfo("问你");
        logInfo.setTime(new Date(2018, 9, 15));
        list.add(logInfo);
        logInfo = new LogInfo();
        logInfo.setIndex(2);
        logInfo.setInfo("世界");
        logInfo.setTime(new Date(2018, 9, 17));
        list.add(logInfo);
        byte[] bs = Excels.export("1", "日志", list, 0, list.size());
        try (FileOutputStream outputStream = new FileOutputStream("D:/log.xls")) {
            outputStream.write(bs);
            outputStream.flush();
        } catch (Exception e) {
        }
    }
}