利用poi进行数据的excel导出

最新推荐文章于 2020-12-04 09:39:51 发布

转载最新推荐文章于 2020-12-04 09:39:51 发布 · 139 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/kevin2kelly/blog/1486059

本文介绍了一款Java工具类，用于快速生成Excel文件。支持两种模式：一种为记录型数据，直接从对象集合生成表格；另一种为复杂表格布局，通过指定单元格位置和值动态构建。提供了详细的代码实现及示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

添加maven依赖：

<!-- poi --> 
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17-beta1</version>
</dependency>

<!-- fast json -->
<dependency> 
    <groupId>com.alibaba</groupId>
     <artifactId>fastjson</artifactId>
     <version>1.2.31</version>
</dependency>

主要代码：

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.liyang.entity.UserEntity;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

/**
 * 导出excel工具
 *
 * @author 李阳
 * @mail ly_triangle@126.com
 * 15002992382
 * 2017-07-24
 */
public class ExcelUtil {

    /**
     * 根据给定的数据在前端页面导出excel
     *
     * @param fileName 文件名
 * @param wb 工作簿
     * @param response
     * @throws IOException
     */
    public static void exportDatasExcel(String fileName, Workbook wb, HttpServletResponse response) 
        throws IOException {

         //清空前端缓存  
        response.reset(); 

        //指定前端文件mime类型  
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;fileName="
                + new String(replaceSpecialChars（fileName）.getBytes(), "iso8859-1")
                + ".xlsx");

        //字节数组输出流
        ByteArrayOutputStream out = new ByteArrayOutputStream();

        //将工作簿写进字节数组输出流中
        wb.write(out);

        //创建字节数组输入流，将输出流中的数据写进该输入流中，最后创建输入流的缓冲流，便于读取数据
        BufferedInputStream bufferIn = new BufferedInputStream(new ByteArrayInputStream(out.toByteArray()));

        //获得response输出流，并获得输出流的缓冲流，便于写入数据
        BufferedOutputStream bufferOut = new BufferedOutputStream(response.getOutputStream());

        //每次输出文件1000字节长度数据到输出流
        byte[] buffer = new byte[1000];
        int bytesRead;

        //读取输入缓冲流中的数据，经过缓冲输出流发送给前端下载
        while (-1 != (bytesRead = bufferIn.read(buffer))) {
            bufferOut.write(buffer, 0, bytesRead);
        }

        bufferOut.flush();
        bufferIn.close();
        bufferOut.close();
    }

    /**
     * 创建excel表格(记录性结果)
     *
     * @param titles  标题集合
     * @param keys    key值集合，用于读取json对象中的数据
     * @param objects 数据对象集合
     * @return
     */
    private static Workbook buildWorkbook(List<String> titles, List<String> keys, List<Object> objects, String fileName) {
        //创建工作簿
        Workbook wb = new XSSFWorkbook();  //or new HSSFWorkbook();

        //创建表格
        Sheet sheet = wb.createSheet(replaceSpecialChars(fileName));

        //第一行
        Row row = sheet.createRow(0);

        //第二行
        Row row1 = sheet.createRow(1);

        //创建数据单元格样式       
        CellStyle style = creatStyle(wb);

        //创建标题单元格样式
        CellStyle titleStyle = creatTitleStyle(wb);
        Cell cell;
        Cell cell1;

        //遍历标题，标题占两行
        for (int t = 0; t < titles.size(); t++) {

            //创建标题单元格并设置标题单元格的值
            cell = row.createCell(t);
            cell.setCellValue(titles.get(t));

            //创建第二行单元格
            cell1 = row1.createCell(t);

            //合并上下单元格（标题占两行）
            sheet.addMergedRegion(new CellRangeAddress(0, 1, t, t));

            //对单元格使用样式
            cell.setCellStyle(titleStyle );
            cell1.setCellStyle(titleStyle );
        }

        //将对象集合转换成json对象数组
        JSONArray array = JSON.parseArray(JSON.toJSONString(objects));

        //遍历json对象数组，将数据写进excel中
        for (int r = 2; r < array.size() + 2; r++) {
            JSONObject o = array.getJSONObject(r - 2);

            //创建行
            row = sheet.createRow(r);

            //创建行单元格，并赋值
            for (int c = 0; c < keys.size(); c++) {
                String value = o.get(keys.get(c)) == null ? "" : o.get(keys.get(c)).toString();
                cell = row.createCell(c);
                cell.setCellValue(value);
                cell.setCellStyle(style);
            }
        }

        // 最后一列宽自适应
        sheet.autoSizeColumn(titles.size()-1);

        //获得工作簿
        return wb;
    }

创建复杂excel表格（非记录性数据，需要一定模板）：

/**
 * 动态生成复杂excel
 *
 * @param rowSize    行数
 * @param columnSize 列数
 * @param models     单元格实例集合（每一个单元格（包括合并的单元格）起止行列四个索引和单元格的值）
 * @param fileName   文件名
 * @return
 */
public static Workbook buildWorkbook2(Integer rowSize, Integer columnSize, List<CellModel> models, String fileName) {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(replaceSpecialChars（fileName）);
    CellStyle style = creatStyle(wb);
    CellStyle titleStyle = creatTitleStyle(wb);
    Row row;
    Cell cell;

    //创建空表格
    for (int r = 0; r < rowSize; r++) {
        row = sheet.createRow(r);
        for (int c = 0; c < columnSize; c++) {
            cell = row.createCell(c);
            if (0 == r || 1 == r) {
                cell.setCellStyle(titleStyle);
            } else {
                cell.setCellStyle(style);
            }
        }
    }

    //单元格赋值并合并单元格
    models.forEach(c -> {
        Integer rowBegin = c.getRowBegin();
        Integer rowEnd = c.getRowEnd();
        Integer columnBegin = c.getColumnBegin();
        Integer columnEnd = c.getColumnEnd();
        sheet.getRow(rowBegin).getCell(columnBegin).setCellValue(c.getValue().toString());
        if (rowBegin.intValue() != rowEnd.intValue() || columnBegin.intValue() != columnEnd.intValue())
            sheet.addMergedRegion(new CellRangeAddress(rowBegin, rowEnd, columnBegin, columnEnd));
    });

    short height = sheet.getDefaultRowHeight();
    sheet.setDefaultRowHeight((short) (height * 1.7));
    sheet.setDefaultColumnWidth(15);

    return wb;
}

单元格对象：

public class CellModel {
    private Object value = ""; //单元格值
    private Integer rowBegin; //开始行索引
    private Integer rowEnd;  //结束行索引
    private Integer columnBegin;  //开始列索引
    private Integer columnEnd;  //结束列索引
    .
    .
    .
    .
    .
    略

创建标题样式：

/**
 * create title style
 * @param wb
 * @return
 */
private static CellStyle creatTitleStyle(Workbook wb) {
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setFontName("黑体");
    font.setBold(true);

    CellStyle titleStyle = wb.createCellStyle();
    titleStyle.setAlignment(HorizontalAlignment.CENTER);
    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);

    //背景色
    titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFont(font);

    return titleStyle;
}

创建数据样式：

private static CellStyle creatStyle(Workbook wb) {
    Font font = wb.createFont();

    //字号
    font.setFontHeightInPoints((short) 12);

    //字体
    font.setFontName("宋体");

    //斜体
    //font.setItalic(true);

    //加粗
    //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    //创建样式
    CellStyle style = wb.createCellStyle();

    // 给样式指定字体
    style.setFont(font);

    //纵横向居中
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);

    //四周边框
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);

    return style;
}

文件名特殊字符处理（特殊字符统一用空格替换）

private static String replaceSpecialChars(String fileName) {
    return fileName
            .replaceAll("：", " ")
            .replaceAll(":", " ")
            .replaceAll("？", " ")
            .replaceAll("\\?", " ")
            .replaceAll("\\*", " ")
            .replaceAll("]", " ")
            .replaceAll("\\[", " ")
            .replaceAll("“", " ")
            .replaceAll("”", " ")
            .replaceAll("\"", " ")
            .replaceAll("\\|", " ")
            .replaceAll(">", " ")
            .replaceAll("<", " ")
            .replaceAll("\\\\", " ")
            .replaceAll("/", " ");
}

测试1（记录性结果）：

/**
     * test
     */
    public static void main(String[] args) throws IOException {

        //构造数据对象集合
        List list = Arrays.asList(new UserEntity("kevin", "lee", 5),
                new UserEntity("david", "lee", null),
                new UserEntity("kavin", "", 6),
                new UserEntity("kavein", "lee", 65));

        //构造标题和key值列表，生成工作簿
        Workbook wb = buildWorkbook(Arrays.asList("姓名", "全名", "年龄"),
                Arrays.asList("stageName", "fullName", "sex"), list);

        //生成服务器本地文件，如需输出到前端，只需要调用exportDatasExcel方法即可
        FileOutputStream fileOut = new FileOutputStream("dataExcel.xlsx");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }

测试2（复杂结果）：

public static void main(String[] args) throws IOException {

    //构造数据对象集合
    List list = new ArrayList();
    list.add(new CellModel("标题", 0, 1, 0, 3));
    list.add(new CellModel("购买渠道", 2, 26, 0, 0));
    list.add(new CellModel("微信", 2, 13, 1, 1));
    list.add(new CellModel("网站", 14, 26, 1, 1));
    list.add(new CellModel("微信平台名称", 2, 3, 2, 3));
    list.add(new CellModel("大秦帝国", 4, 13, 2, 3));
    list.add(new CellModel("网站平台名称", 14, 15, 2, 3));
    list.add(new CellModel("饿狼传说", 16, 26, 2, 3));

    //构造标题和key值列表，生成工作簿
    Workbook wb = buildWorkbook2(27, 5, list, "domo");

    //生成服务器本地文件，如需输出到前端，只需要调用exportDatasExcel方法即可
    FileOutputStream fileOut = new FileOutputStream("domo.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

测试结果：

记录性结果excel：

复杂excel结果：

转载于:https://my.oschina.net/kevin2kelly/blog/1486059