java poi工具类导出excel

导出excel

package com.cardvalue.scm.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author liouwb
 * @date 2018-02-02
 */
public class ExcelFileUtil {

    /**
     * 生成Excel文件
     *
     * @param fileName 文件名
     * @param data     数据
     * @param response HttpServletResponse
     */
    public static void export(String fileName, ExcelData data, HttpServletResponse response) throws Exception {
        response.setContentType("application/force-download;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;fileName=" +
                URLEncoder.encode(fileName, "UTF-8"));

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(data.getName().isEmpty() ? "Sheet1" : data.getName());
        writeTitles(wb, sheet, data.getTitles());
        writeRows(wb, sheet, data.getRows());
        try {
            wb.write(response.getOutputStream());
        } finally {
            wb.close();
        }
        response.flushBuffer();
    }


    /**
     * 添加标题
     *
     * @param wb     Workbook
     * @param sheet  标签
     * @param titles 标题
     */
    private static void writeTitles(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
        XSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);

        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(titleStyle);
        }
    }


    /**
     * 添加数据
     *
     * @param wb    Workbook
     * @param sheet 标签
     * @param rows  数据
     */
    private static void writeRows(XSSFWorkbook wb, XSSFSheet sheet, List<List<Object>> rows) {
        Font dataFont = wb.createFont();
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setFont(dataFont);

        for (int i = 0; i < rows.size(); i++) {
            List<Object> rowData = rows.get(i);
            // 第二行开始写入数据
            XSSFRow row = sheet.createRow(i + 1);

            for (int j = 0; j < rowData.size(); j++) {
                XSSFCell cell = row.createCell(j);
                Object cellData = rowData.get(j);

                if (cellData == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(cellData.toString());
                }
                cell.setCellStyle(dataStyle);
            }
        }

        // 自动调整列宽
        for (int i = 0; i < rows.get(0).size(); i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = sheet.getColumnWidth(i) + 100;
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }
}

用到的ExcelData.java类

package com.cardvalue.scm.util;

import java.util.List;

/**
 * @author liouwb
 * @date 2018-02-02
 */
public class ExcelData {
    // 页签名称
    private String name;

    // 表头
    private List<String> titles;

    // 数据
    private List<List<Object>> rows;

    public ExcelData() {
    }

    public ExcelData(String name, List<String> titles, List<List<Object>> rows) {
        this.name = name;
        this.titles = titles;
        this.rows = rows;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }

    public List<List<Object>> getRows() {
        return rows;
    }

    public void setRows(List<List<Object>> rows) {
        this.rows = rows;
    }
}