SpringMVC使用POI导出Excel

对于现在大部分的应用管理系统中，会很多用到导出Excel，这里我根据项目整理了一下导出的具体实现，导出的列表可是任意的List<Object>，具体需要根据你自己需求而定（ExcelEntity）,另外需要自己定义标题（List<String> titles）和内容List<String> keys就行

 

1.引入maven包

<!-- poi office -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.16</version>
        </dependency>

2.Controller-直接返回文件下载

@RequestMapping(value = "/export_excel", method = RequestMethod.POST)
    public void excel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //你导出的列表-你具体业务的列表
        //ExcelEntity根据你的业务替换
        List<ExcelEntity> list = new ArrayList<>();

        ExcelData data = new ExcelData();
        data.setSheetName("sheet名称");
        //导出标题
        List<String> titles = new ArrayList();
        titles.add("id");
        titles.add("名称");
        titles.add("内容");
        data.setTitles(titles);

        //对应的内容-与你导出实体的字段一致
        List<String> keys = new ArrayList();
        keys.add("id");
        keys.add("name");
        keys.add("content");
        data.setKeys(keys);

        data.setRows(list);

        ExportExcelUtil.exportExcel(response,"test.xlsx",data);
    }

3.ExcelEntity-对应列表实体类

@Data
public class ExcelEntity implements Serializable {


    private Integer id;

    private String name;

    private String content;

}

4.ExcelData-导出组装数据

@Data
public class ExcelData implements Serializable {

    // 表头导航列名称
    private List<String> titles;

    //对应数据的列字段名称
    private List<String> keys;

    // 数据
    private List<?> rows;

    // 页签名称
    private String sheetName;

    public ExcelData() {
    }

    public ExcelData(List<String> titles, List<String> keys, List<?> rows, String sheetName) {
        this.titles = titles;
        this.keys = keys;
        this.rows = rows;
        this.sheetName = sheetName;
    }
}

5.ExportExcelUtil-具体导出工具类

注意：BeanUtil是一个类转换工具类，在另一篇博客中https://blog.youkuaiyun.com/u010096717/article/details/81941960

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * @Auther: cjw
 * @Date: 2018/8/14 11:48
 * @Description:
 */
@Component
public class ExportExcelUtil {

    /**
     * 导出Excel方法
     * @param response 返回数据
     * @param fileName 导出文件的名称
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 设置header
        response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        // 下载文件的名称
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    /**
     * 设置Sheet 名称
     * @param data
     * @param out
     * @throws Exception
     */
    private static void exportExcel(ExcelData data, OutputStream out) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getSheetName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);

            wb.write(out);
        } finally {
            wb.close();
        }
    }

    /**
     * 写入表头和内容
     * @param wb
     * @param sheet
     * @param data
     */
    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {

        int rowIndex = 0;
        //标题栏
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        String[] keys = data.getKeys().toArray(new String[]{});
        //Beanutil 类转换
        writeRowsToExcel(wb, sheet, BeanUtil.convertListMap(data.getRows(),true),keys, rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);

    }

    /**
     * 写入表头
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        ((XSSFFont) titleFont).setBold(true);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Row titleRow = sheet.createRow(rowIndex);
        colIndex = 0;

        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }

        rowIndex++;
        return rowIndex;
    }

    /**
     * 写入内容
     * @param wb
     * @param sheet
     * @param rows
     * @param keys
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<Map<String, Object>> rows, String[] keys, int rowIndex) {
        int colIndex = 0;

        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        // dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(68, 171, 103)));

        for (Map<String, Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            // dataRow.setHeightInPoints(25);
                colIndex = 0;
            for (int k = 0; k < keys.length; k++) {
                Object objValue = rowData.get(keys[k]);
                Cell cell = dataRow.createCell(colIndex);
                String value = "";
                if (objValue != null) {
                    //如果是时间，则把时间戳转换为字符串格式
                    if (keys[k].toLowerCase().contains("time") && objValue != null) {
                        try {
                            value = DateUtil.timeStampToString(Long.valueOf(objValue.toString()));
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    } else {
                        value = objValue.toString();
                    }
                    cell.setCellValue(value);
                }else {
                    cell.setCellValue("");
                }
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 自动设置行宽
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    /**
     * 设置格式
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }
}