esaypoi生成自定义excel

一、引用包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

二、工具类（创建excel）

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

/**
 * @author yms
 * esayExcel简易操作工具类
 */
public class PoiExcelUtils {
    /**
     * 创建样式
     *
     * @param workbook         工作薄
     * @param isSetCenter      是否文本居中
     * @param isSetBold        是否加粗
     * @param isSetBorderStyle 是否添加边框
     * @param fontSize         字体大小
     * @return
     */
    private static XSSFCellStyle getXSSFCellStyle(XSSFWorkbook workbook, boolean isSetCenter, boolean isSetBold, boolean isSetBorderStyle, int fontSize) {
        //创建格式对象
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        if (true == isSetCenter) {
            // 左右居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 上下居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        //设置字体
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        //粗体显示
        font.setBold(isSetBold);
        cellStyle.setFont(font);
        if (true == isSetBorderStyle) {
            //下边框
            cellStyle.setBorderBottom(BorderStyle.THIN);
            //左边框
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //上边框
            cellStyle.setBorderTop(BorderStyle.THIN);
            //右边框
            cellStyle.setBorderRight(BorderStyle.THIN);
        }
        return cellStyle;
    }

    /**
     * 创建excel工作薄对象
     *
     * @return
     */
    public static XSSFWorkbook createExcel() {
        //创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 设置第一个sheet的名称
        XSSFSheet sheet = workbook.createSheet("数据表");
        // 设置excel宽度和高度
        sheet.setDefaultRowHeight((short) (3 * 256));
        sheet.setDefaultColumnWidth(10);

        // 创建行数，创建一个单元格，第一行
        XSSFRow row0 = sheet.createRow(0);
        // 设置行的高度
        row0.setHeightInPoints(40);
        // 放入单元格中
        row0.createCell(0).setCellValue("第一行");
        //第二行
        XSSFRow row1 = sheet.createRow(1);
        row1.setHeightInPoints(20);
        row1.createCell(0).setCellValue("第二行");
        //第三行
        XSSFRow row2 = sheet.createRow(2);
        row2.setHeightInPoints(30);
        row2.createCell(0).setCellValue("序号");
        row2.createCell(1).setCellValue("名称");
        //循环数据
        for (int i = 0; i < 10; i++) {
            XSSFRow row = sheet.createRow(i + 3);
            row.setHeightInPoints(15);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue("数据" + i);
        }
        //合并列数
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 1);
        sheet.addMergedRegion(cra);
        CellRangeAddress cra1 = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(cra1);

        //设置表格样式
        row0.getCell(0).setCellStyle(getXSSFCellStyle(workbook, true, false, false, 20));
        row1.getCell(0).setCellStyle(getXSSFCellStyle(workbook, true, false, false, 12));
        XSSFCellStyle xssfCellStyle = getXSSFCellStyle(workbook, true, false, true, 12);
        //修改列宽
        for (int i = 0; i < 2; i++) {
            sheet.setColumnWidth(i, 256 * 3);
        }
        return workbook;
    }
}

三、下载文件或导出

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;

/**
 * @author yms
 */
public class ExcelFileUtil {
    /**
     * 下载excel文件
     *
     * @param workbook 工作薄
     * @param fileName 文件名 ---xxx.xlsx
     * @param response 响应体
     */
    public void downExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] bytes = os.toByteArray();
            response.addHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            if (bytes != null) {
                OutputStream out = response.getOutputStream();
                out.write(bytes, 0, bytes.length);
                out.flush();
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成excel文件
     *
     * @param workbook 工作薄
     * @param fileName 文件名 ---xxx.xlsx
     */
    public void writeExcel(Workbook workbook, String fileName) {
        try {
            //文件写入
            File file = new File(fileName);
            FileOutputStream flout = new FileOutputStream(file);
            workbook.write(flout);
            flout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}