java根据模板导出数据XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCellStyle

使用的方式

使用XSSFWorkbook、XSSFCellStyle、XSSFSheet、XSSFRow、CellRangeAddress根据模板导出excel数据

说明

导出的excel模板：第1行到第5行都是固定的模板数据

注意图中的圈中的都是有合并项的

导出后的数据样式：

下面图中，第6行到第9行都是db中获取的数据

 定义测试数据类

@Data
@EqualsAndHashCode(callSuper = false)
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName("eb_test_data")
@ApiModel(description = "测试数据")
public class TestData implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @TableId(type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "订单id")
    private Integer orderId;

    @ApiModelProperty(value = "订单号")
    private String orderSn;

    @ApiModelProperty(value = "用户ID")
    private Integer uid;

    @ApiModelProperty(value = "用户编码")
    private String uCode;

    @ApiModelProperty(value = "用户名称")
    private String uName;

    @ApiModelProperty(value = "原等级名称")
    private String prevLevelName;

    @ApiModelProperty(value = "评定等级ID")
    private String nextLevelName;

    @ApiModelProperty(value = "总商品订购额")
    private BigDecimal subscribeAmount;

    @ApiModelProperty(value = "其中酒类推荐订购额")
    private BigDecimal windTypeSubscribeAmount;

    @ApiModelProperty(value = "订单月平均数量")
    private Integer monthAvgOrderNum;

    @ApiModelProperty(value = "酒类型数据上报率，默认100.00")
    private BigDecimal windTypeUpreportRate;

    @ApiModelProperty(value = "快消品陈列数")
    private Integer recommendDisplayNum;

    @ApiModelProperty(value = "商品零售价")
    private Integer productRetailPrice;
}

测试导出：数据和样式到在这里面处理

创建一个类，把下面的方法都放到这个创建的类中即可（这里我分开放了，不然有点太长了）

需要的包 

import org.apache.commons.lang3.RandomStringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;

测试方法： 

    @Test
    public void test() throws IOException, InvalidFormatException {
        //查询要处理的数据
        List<TestData> testDataList = this.getTestDataList();

        String downLoadUrl = "D://test.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook(new File(downLoadUrl));

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 自动换行
        cellStyle.setWrapText(true);
//        cellStyle.getFont().setBold(true);
        XSSFSheet sheet = workbook.getSheetAt(0);
        //导出日期
        XSSFRow row1 = sheet.getRow(1);
        XSSFCell cell3 = row1.getCell(10);
        cell3.setCellValue("日期：" + yyyyMMdd.format(new Date()));
        cell3.setCellStyle(cellStyle);

        // 写入数据
        for (int i = 0; i < testDataList.size(); i++) {
            XSSFRow row = sheet.createRow(i + 5);
            TestData infoResponse = testDataList.get(i);
            createCellAndSetValue(row, 0, String.valueOf(i + 1), cellStyle);
            createCellAndSetValue(row, 1, String.valueOf(infoResponse.getId()), cellStyle);
            createCellAndSetValue(row, 2, infoResponse.getUCode(), cellStyle);
            createCellAndSetValue(row, 3, infoResponse.getUName(), cellStyle);
            createCellAndSetValue(row, 4, infoResponse.getPrevLevelName(), cellStyle);
            createCellAndSetValue(row, 5, Optional.ofNullable(infoResponse.getSubscribeAmount()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 6, Optional.ofNullable(infoResponse.getWindTypeSubscribeAmount()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 7, Optional.ofNullable(infoResponse.getMonthAvgOrderNum()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 8, Optional.ofNullable(infoResponse.getWindTypeUpreportRate()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 9, Optional.ofNullable(infoResponse.getRecommendDisplayNum()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 10, Optional.ofNullable(infoResponse.getProductRetailPrice()).map(String::valueOf).orElse(""), cellStyle);
            createCellAndSetValue(row, 11, infoResponse.getNextLevelName(), cellStyle);
            sheet.setColumnWidth(11, Double.valueOf(infoResponse.getNextLevelName().length() * 3.8 * 256).intValue());
        }

        XSSFCellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setWrapText(true);
        //合并单元格 备注信息
        CellRangeAddress cellRangeAddress = new CellRangeAddress(testDataList.size() + 5, testDataList.size() + 5 + 2, 0, 11);
        sheet.addMergedRegion(cellRangeAddress);
        setBorderStyle(BorderStyle.THIN, cellRangeAddress, sheet);
        XSSFRow sheetRow = sheet.createRow(testDataList.size() + 5);
        // 这一步的样式要注意 todo
        XSSFCell sheetRowCell = sheetRow.createCell(11);
        XSSFCellStyle endCellStyle = workbook.createCellStyle();
        endCellStyle.setBorderBottom(BorderStyle.THIN);
        endCellStyle.setBorderLeft(BorderStyle.THIN);
        endCellStyle.setBorderRight(BorderStyle.THIN);
        endCellStyle.setBorderTop(BorderStyle.THIN);
        sheetRowCell.setCellStyle(endCellStyle);
        endCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        endCellStyle.setWrapText(true);
        XSSFCell sheetRowCell2 = sheetRow.createCell(0);
        sheetRowCell2.setCellValue("备注：1、“原等级”一栏为空，代表该商户为首次评级。\n" +
                "     2、“随机抽查调整项”中两项指标为默认完成项，常规值缺省，每季度由第三方客服进行随机抽查，若抽查结果未达标，则手动调整该项指标及“评定等级”项。");
        sheetRowCell2.setCellStyle(endCellStyle);

        XSSFRow rowAddFour = sheet.createRow(testDataList.size() + 5 + 5);
        XSSFCell cell = rowAddFour.createCell(1);
        cell.setCellValue("上报人");
        XSSFCell cell1 = rowAddFour.createCell(4);
        cell1.setCellValue("销售经理");
        XSSFCell cell2 = rowAddFour.createCell(8);
        cell2.setCellValue("销售部总经理");

        File tempFile = new File(System.getProperty("java.io.tmpdir"), "根据模板放入数据" + System.currentTimeMillis() + ".xlsx");
        FileOutputStream out = new FileOutputStream(tempFile);
        workbook.write(out);
        out.close();
    }

 模拟填入数据方法：

/**
 * 获取原等级和评定的下等级
 *  这里是模拟的等级和数据
 * @return
 */
private List<TestData> getTestDataList() {
    List<TestData> testDataList = new ArrayList<>(20);

    for (int i = 0; i < 18; i++) {
        TestData response = new TestData();
        response.setId(i);
        response.setUCode(RandomStringUtils.random(9));
        response.setUName("uName" + i);
        response.setPrevLevelName(i + " -> 原来等级名称");// 原来等级名称
        response.setSubscribeAmount(BigDecimal.ONE.add(BigDecimal.valueOf(i)));
        response.setRecommendDisplayNum(i);
        response.setWindTypeSubscribeAmount(BigDecimal.TEN.add(BigDecimal.valueOf(i)));
        response.setMonthAvgOrderNum(i * 3);
        response.setWindTypeUpreportRate(BigDecimal.TEN.multiply(BigDecimal.valueOf(i)).add(BigDecimal.valueOf(8)));
        response.setRecommendDisplayNum(i);
        response.setProductRetailPrice(i + 9);
        response.setNextLevelName("下一等级名称 = " + i);
        testDataList.add(response);
    }
    return testDataList;
}

设置excel样式：

/**
 * 设置边框
 *
 * @param border
 * @param region
 * @param sheet
 */
public void setBorderStyle(BorderStyle border, CellRangeAddress region, XSSFSheet sheet) {
    RegionUtil.setBorderBottom(border, region, sheet);   //下边框
    RegionUtil.setBorderLeft(border, region, sheet);     //左边框
    RegionUtil.setBorderRight(border, region, sheet);    //右边框
    RegionUtil.setBorderTop(border, region, sheet);      //上边框
}

/**
 * 设置样式
 *
 * @param row
 * @param index
 * @param value
 * @param cellStyle
 */
private static void createCellAndSetValue(XSSFRow row, int index, String value, XSSFCellStyle cellStyle) {
    XSSFCell cell = row.createCell(index);
    cell.setCellValue(value);
    cell.setCellStyle(cellStyle);
}

这个写法有点复杂，使用easyExcel会比较简单，但是我没有使用

如有问题欢迎指正