Java导出无规则excel，如报销单，申请单，结算单等

普通业务我们导出的excel文件大多是有规则的表记录导出，即表头+表记录。但是如果业务上让我们去导出一些复杂的excel结构，比如导出结算单，申请单这些，如下：

类似这种excel文件格式的导出，现有的框架并不能很友好的支持，我们需要些一堆复杂的代码，而且还不能复用。

针对这种业务本人编写了以下的excel处理工具，希望可以帮助到大家。

ZlExcel类

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

/**
 * cell
 */
public class ZlCell {

    /**
     * 单元格ID
     */
    private String cellNo;

    private int cellIndex;

    /**
     * 行
     */
    private ZlRow row;
    //单元格格式
    private CellStyle style;
    //单元格
    private Cell cell ;
    //工作簿
    private XSSFWorkbook wb;

    private ZlSheet sheet;
    /**
     * 字体
     */
    private Font font;

    private Object content;

    /**
     * 背景色
     */
    private IndexedColors background;

    /**
     * 字体颜色
     */
    private IndexedColors fontColor;

    private BorderStyle borderTop;

    private BorderStyle borderLeft;

    private BorderStyle borderBottom;

    private BorderStyle borderRight;

    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,int width,String url){
        this.wb = wb;
        this.sheet = sheet;
        this.row = row;
        this.cell = row.getRow().createCell(index);
        this.cellIndex = index;
        //宽度
        if(width > 0){
            setColumnWidth(width);
        }
        //设置图片
        addImage(url);
    }

    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,Object content){
        this(wb,sheet,row,index,content,false,0,(short)11);
    }

    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,Object content,boolean hold){
        this(wb,sheet,row,index,content,hold,0,(short)11);
    }


    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,Object content,boolean hold,int width){
        this(wb,sheet,row,index,content,hold,width,(short)11);
    }


    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,Object content,boolean hold,short fontSize){
        this(wb,sheet,row,index,content,hold,0,fontSize);
    }

    public ZlCell(XSSFWorkbook wb,ZlSheet sheet,ZlRow row,Integer index,Object content,boolean hold,int width,short fontSize){
        this.wb = wb;
        this.sheet = sheet;
        this.row = row;
        this.cell = row.getRow().createCell(index);
        this.cellIndex = index;
        this.style = wb.createCellStyle();
        this.font = wb.createFont();
        //默认宋体
        this.font.setFontName("宋体");
        this.font.setBold(hold);
        this.font.setFontHeightInPoints(fontSize);

        this.style.setFont(font);
        //默认横 纵向居中
        this.style.setAlignment(HorizontalAlignment.CENTER);
        this.style.setVerticalAlignment(VerticalAlignment.CENTER);
        //默认添加边线
        this.style.setBorderTop(BorderStyle.THIN);
        this.style.setBorderBottom(BorderStyle.THIN);
        this.style.setBorderLeft(BorderStyle.THIN);
        this.style.setBorderRight(BorderStyle.THIN);
        this.cell.setCellStyle(style);
        this.cellNo = UUID.randomUUID().toString();
        setCellValue(content);
        //宽度
        if(width > 0){
            setColumnWidth(width);
        }
    }

    public void setContent(Object content) {
        this.content = content;
        setCellValue(content);
    }

    private void setCellValue(Object content){
        this.content = content;
        if(content instanceof  Number){
            this.cell.setCellValue(((Number) content).doubleValue());
        }else if(content instanceof String){
            this.cell.setCellValue((String) content);
        }else if(content instanceof Date){
            this.cell.setCellValue((Date) content);
        }else if(content instanceof Boolean){
            this.cell.setCellValue((Boolean) content);
        }else{
            this.cell.setCellValue(String.valueOf(content));
        }
    }


    /**
     * 设置字体类型 如宋体
     */
    public ZlCell setFontName(String font){
        this.font.setFontName(font);
        return this;
    }

    /**
     * 设置是否为粗体
     */
    public ZlCell setFontBold(boolean bold){
        this.font.setBold(bold);
        return this;
    }

    /**
     * 设置字体高度
     */
    public ZlCell setFontHeight(short height){
        this.font.setFontHeightInPoints(height);
        return this;
    }

    /**
     * 设置横向内容方向
     */
    public ZlCell setHorizontalAlignment(HorizontalAlignment alignment){
        this.style.setAlignment(alignment);
        this.cell.setCellStyle(style);
        return this;
    }

    /**
     * 设置纵向内容方向
     */
    public ZlCell setVerticalAlignment(HorizontalAlignment alignment){
        this.style.setAlignment(alignment);
        this.cell.setCellStyle(style);
        return this;
    }

    /**
     * 上边线
     */
    public ZlCell setBorderTop(BorderStyle borderStyle){
        this.style.setBorderTop(borderStyle);
        this.cell.setCellStyle(style);
        return this;
    }

    /**
     * 下边线
     */
    public ZlCell setBorderBottom(BorderStyle borderStyle){
        this.style.setBorderBottom(borderStyle);
        this.cell.setCellStyle(style);
        return this;
    }

    /**
     * 左边线
     */
    public ZlCell setBorderLeft(BorderStyle borderStyle){
        this.style.setBorderLeft(borderStyle);
        this.cell.setCellStyle(style);
        return this;
    }

    /**
     * 右边线
     */
    public ZlCell setBorderRight(BorderStyle borderStyle){
        this.style.setBorderRight(borderStyle);
        this.cell.setCellStyle(style);
        return this;
    }

    public ZlCell mergeCell(String endRowNo,String endCellNo){
        int firstRow = this.row.getRowIndex();
        int firstCell = this.getCellIndex();
        List<ZlRow> rows =  this.sheet.getRows();
        ZlRow endRow = rows.stream().filter(r->r.getRowNo().equalsIgnoreCase(endRowNo)).findFirst().orElse(null);
        if(endRow == null){
            return this;
        }
        ZlCell endCell = endRow.getCells().stream().filter(c->c.getCellNo().equalsIgnoreCase(endCellNo)).findFirst().orElse(null);
        if(endCell == null){
            return this;
        }
        if(firstRow == endRow.getRowIndex()){
            //如果是同行的列合并
            for(int i = firstCell ; i<= endCell.getCellIndex() ; i++){
                for(ZlCell cell : endRow.getCells()){
                    if(cell.getCellIndex() == i){
                        cell.setCellNo(this.cellNo);
                    }
                }
            }
        }
        endCell.setCellNo(this.cellNo);
        CellRangeAddress region = new CellRangeAddress(firstRow, endRow.getRowIndex(), firstCell, endCell.getCellIndex());
        this.sheet.getSheet().addMergedRegion(region);
        return this;
    }


    public ZlCell setColumnWidth(int width){
        this.sheet.getSheet().setColumnWidth(this.cell.getColumnIndex(),width * 256);
        return this;
    }


    public String getCellNo() {
        return cellNo;
    }

    public void setCellNo(String cellNo) {
        this.cellNo = cellNo;
    }

    public int getCellIndex() {
        return cellIndex;
    }

    public void setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
    }

    public Object getContent() {
        return content;
    }

    public ZlCell setBackground(IndexedColors background) {
        this.background = background;
        for(ZlCell cell :this.row.getCells()){
            if(cell.getCellNo().equalsIgnoreCase(this.cellNo)){
                cell.getCell().getCellStyle().setFillBackgroundColor(background.getIndex());
            }
        }

        return this;
    }

    public ZlCell setFontColor(IndexedColors fontColor) {
        this.fontColor = fontColor;
        this.font.setColor(fontColor.getIndex());
        this.cell.getCellStyle().setFont(this.font);
        return this;
    }

    public Cell getCell() {
        return cell;
    }


    public ZlCell addImage(String filePath){
        try {
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            File file = new File(filePath);
            BufferedImage bufferImg = ImageIO.read(file);
            String imageType = filePath.substring(filePath.length()-3);
            ImageIO.write(bufferImg, imageType, outputStream);
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
                    this.cellIndex, this.row.getRowIndex(),  this.cellIndex+1, this.row.getRowIndex() +1);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            //插入图片
            this.sheet.getPatriarch().createPicture(anchor, wb.addPicture(outputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return this;
    }


    private ByteArrayOutputStream inputToOutput(InputStream inputStream){
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            byte[] buffer = new byte[1024];

            int len;
            while ((len = inputStream.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return baos;
    }
}

ZlRow类

import lombok.Data;
import org.apache.poi.ss.usermodel.Row;
import java.util.LinkedList;
import java.util.UUID;
/**
 * row
 */
@Data
public class ZlRow {

    /**
     * 行ID
     */
    private String rowNo;
    //行
    private Row row;
    //sheet对象
    private ZlSheet sheet;

    private int rowIndex;
    /**
     * 所有列
     */
    private LinkedList<ZlCell> cells = new LinkedList<>();

    public ZlRow(ZlSheet sheet,Integer index){
        this(sheet,index,23);
    }

    public ZlRow(ZlSheet sheet,Integer index,Integer height){
        this.rowIndex = index;
        this.sheet = sheet;
        this.row = this.sheet.getSheet().createRow(index);
        //默认设置行数为23
        this.row.setHeightInPoints(height);
        this.rowNo = UUID.randomUUID().toString();
    }

    private ZlRow addCell(ZlCell cell){
        this.cells.addLast(cell);
        return this;
    }

}

ZlSheet类

import lombok.Data;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * sheet
 */
@Data
public class ZlSheet {

    /**
     * 工作簿
     */
    private XSSFWorkbook wb;

    private XSSFSheet sheet;

    /**
     * 当前行
     */
    private int rowIndex = 0;

    /**
     * 当前列
     */
    private int cellIndex = 0;

    /**
     * 当前操作的row
     */
    private ZlRow nowRow;

    /**
     * 当前操作的cell
     */
    private ZlCell nowCell;


    private XSSFDrawing patriarch;

    /**
     * 所有行
     */
    private LinkedList<ZlRow> rows = new LinkedList<>();


    public ZlSheet(XSSFWorkbook wb){
        this(wb,"sheet");
    }

    public ZlSheet(XSSFWorkbook wb,String sheetName){
        this.wb = wb;
        this.sheet = this.wb.createSheet(sheetName);
        //隐藏网格线  是否显示网格线  true显示，false 不显示
        this.sheet.setDisplayGridlines(false);
        this.patriarch = sheet.createDrawingPatriarch();
    }


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

    /**
     * 创建下一个cell
     */
    public ZlSheet createNextCell(Object content){
        if(this.nowRow == null){
            nextRow();
        }
        ZlCell cell = new ZlCell(this.wb,this,this.nowRow,this.cellIndex,content);
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }

    /**
     * 创建下一个cell
     */
    public ZlSheet createNextCell(Object content,boolean hold){
        ZlCell cell = new ZlCell(this.wb,this,this.nowRow,this.cellIndex,content,hold);
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }

    /**
     * 创建下一个cell
     */
    public ZlSheet createNextCell(Object content,boolean hold,int width){
        ZlCell cell = new ZlCell(this.wb,this,this.nowRow,this.cellIndex,content,hold,width);
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }

    /**
     * 创建下一个cell
     */
    public ZlSheet createNextCell(Object content,boolean hold,int width,short fontSize){
        ZlCell cell = new ZlCell(this.wb,this,this.nowRow,this.cellIndex,content,hold,width,fontSize);
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }


    /**
     * 创建下一个图片cell
     */
    public ZlSheet createNextImageCell(int width,String url){
        ZlCell cell = new ZlCell(this.wb,this,this.nowRow,this.cellIndex,width,url);
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }

    /**
     * 创建下一个cell 用多个单元格合并
     * @param indexNumber  下一个单元格的结束索引
     * @param content   单元格内容
     * @param hold   是否粗体
     */
    public ZlSheet createCell(Integer indexNumber,Object content,boolean hold){
        this.createCell(indexNumber,content,hold,0,(short)13);
        return this;
    }

    /**
     * 创建下一个cell 用多个单元格合并
     * @param indexNumber  下一个单元格的结束索引
     * @param content   单元格内容
     * @param hold   是否粗体
     * @param fontSize  字体大小
     */
    public ZlSheet createCell(Integer indexNumber,Object content,boolean hold,short fontSize){
        this.createCell(indexNumber,content,hold,0,fontSize);
        return this;
    }

    /**
     * 创建下一个cell 用多个单元格合并
     * @param indexNumber  下一个单元格的结束索引
     * @param content   单元格内容
     * @param width   单元格宽度
     * @param hold   是否粗体
     */
    public ZlSheet createCell(Integer indexNumber,Object content,int width,boolean hold){
        this.createCell(indexNumber,content,hold,width,(short)13);
        return this;
    }

    /**
     * 创建一个大的cell单元格，合并多个横向单元格
     */
    public ZlSheet createCell(Integer indexNumber,Object content,boolean hold,int width,short fontSize){
        ZlCell firstCell = null;
        ZlCell lastCell = null;
        for(int i = this.cellIndex ; i < indexNumber ; i ++){
            ZlCell cell = new ZlCell(this.wb,this,this.nowRow,i,content,hold,width,fontSize);
            if(firstCell == null) {
                firstCell = cell;
            }
            if(i == indexNumber - 1){
                lastCell = cell;
            }
            this.nowRow.getCells().addLast(cell);
        }
        if(firstCell != null && lastCell != null) {
            firstCell.mergeCell(this.nowRow.getRowNo(), lastCell.getCellNo());
        }
        this.nowCell = firstCell;
        this.cellIndex = indexNumber;
        return this;
    }

    /**
     * 供 newCell使用的方法
     */
    public ZlSheet createNextCell(ZlCell cell){
        this.cellIndex = this.cellIndex + 1;
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }

    /**
     * 创建一个可以自定义cell各种风格
     */
    public ZlCell newCell(Object content,boolean hold,int width,short fontSize){
        return new ZlCell(this.wb,this,this.nowRow,this.cellIndex,content,hold,width,fontSize);
    }

    public ZlCell newMergeCell(Integer indexNumber,Object content,boolean hold){
        return newMergeCell(indexNumber,content,hold,0,(short)13);
    }

    public ZlCell newMergeCell(Integer indexNumber,Object content,boolean hold,short fontSize){
        return newMergeCell(indexNumber,content,hold,0,fontSize);
    }

    /**
     * 创建一个大的cell单元格，合并多个横向单元格 newCell使用的方法
     */
    public ZlCell newMergeCell(Integer indexNumber,Object content,boolean hold,int width,short fontSize){
        ZlCell firstCell = null;
        ZlCell lastCell = null;
        for(int i = this.cellIndex ; i < indexNumber ; i ++){
            ZlCell cell = new ZlCell(this.wb,this,this.nowRow,i,content,hold,width,fontSize);
            if(firstCell == null) {
                firstCell = cell;
            }
            if(i == indexNumber - 1){
                lastCell = cell;
            }
            this.nowRow.getCells().addLast(cell);
        }
        if(firstCell != null && lastCell != null) {
            firstCell.mergeCell(this.nowRow.getRowNo(), lastCell.getCellNo());
        }
        this.nowCell = firstCell;
        this.cellIndex = indexNumber;
        return firstCell;
    }

    /**
     * 供 newCell使用的方法
     */
    public ZlSheet createCell(ZlCell cell){
        this.nowCell = cell;
        this.nowRow.getCells().addLast(cell);
        return this;
    }


    /**
     * 合并上方单元格
     */
    public ZlSheet mergeTop(Integer indexNumber){
        ZlCell topCell  = this.rows.get(this.rowIndex - 1 - indexNumber).getCells().get(this.cellIndex - 1);
        topCell.setContent(this.nowCell.getContent());
        topCell.mergeCell(this.nowRow.getRowNo(),this.nowCell.getCellNo());
        return this;
    }


    /**
     * 下一行
     */
    public ZlSheet nextRow(){
        this.nextRow(23);
        return this;
    }

    /**
     * 下一行
     */
    public ZlSheet nextRow(int height){
        ZlRow row = new ZlRow(this,this.rowIndex,height);
        this.rows.addLast(row);
        this.nowRow = row;
        this.cellIndex = 0;
        this.rowIndex = this.rowIndex + 1;
        return this;
    }


    /**
     * 写入文件
     */
    public void writeFile(String path){
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        try {
            this.wb.write(stream);
            File file = new File(path);
            if(!file.exists()){
                file.createNewFile();
            }
            FileOutputStream outputStream = new FileOutputStream(file);
            stream.writeTo(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

下面是测试：

import lombok.Data;
import lombok.experimental.Accessors;
@Data
@Accessors(chain = true)
public class BaseEntity {

    private String username;

    private String projectName;

    private String startTime;

    private String calMoney;

    private String remark;



public static void main(String[] args) {
        List<BaseEntity> entities = new ArrayList<>();
        BaseEntity entity = new BaseEntity()
                .setUsername("张无忌").setStartTime("2022-04-01").setProjectName("启航计划")
                .setCalMoney("10000")
                .setRemark("备注");
        BaseEntity entity1 = new BaseEntity()
                .setUsername("杨潇").setStartTime("2021-05-01").setProjectName("学业辅导")
                .setCalMoney("20000")
                .setRemark("你好世界");
        entities.add(entity);
        entities.add(entity1);
        //1.创建Excel对象
        XSSFWorkbook wb = new XSSFWorkbook();
        ZlSheet sheet = new ZlSheet(wb);
        sheet.nextRow(34)
                .createCell(sheet.newMergeCell(11,"应收结算单",true,(short)18).setBackground(IndexedColors.BLUE))
                .nextRow(34)
                .createNextCell(sheet.newCell("结算公司名称：",true,30,(short)14).setBackground(IndexedColors.BLUE))
                .createCell(4,"上海至和教育",false,(short)15)
                .nextRow()
                .nextRow(25)
                .createNextCell("渠道负责人：",true,25,(short)13)
                .createCell(3,"张三",false,15,(short)13)
                .createNextCell("结算总金额",true,25,(short)13)
                .createCell(6, BigDecimal.valueOf(1000),false,15,(short)13)
                .createCell(9,"结算日期",true,10,(short)13)
                .createCell(11,"2022-03-01",false,15,(short)13)
                .nextRow()
                .nextRow(25)
                .createCell(11,"项目明细",true,(short)18)
                .nextRow(23)
                .createNextCell("学生姓名",true,25,(short)13)
                .createCell(3,"项目名称",true,15,(short)13)
                .createCell(5,"服务开始时间",true,18,(short)13)
                .createCell(7,"项目结算金额",true,15,(short)13)
                .createCell(11,"其他备注",true,15,(short)13);
        for(BaseEntity en : entities){
            sheet.nextRow(23)
                    .createNextCell(en.getUsername(),false,25,(short)13)
                    .createCell(3,en.getProjectName(),false,15,(short)13)
                    .createCell(5,en.getStartTime(),false,18,(short)13)
                    .createCell(7,en.getCalMoney(),false,15,(short)13)
                    .createCell(11,en.getRemark(),false,15,(short)13)
                    .createNextImageCell(30,"D:\图片\IMG_6180.JPG");
        }
        sheet.nextRow().nextRow(25)
                .createCell(11,"结算信息",true,(short)18)
                .nextRow(23)
                .createNextCell("付款方名称",true,25,(short)13)
                .createCell(5,"",true,15,(short)13)
                .createCell(7,"收款方名称",true,15,(short)13)
                .createCell(11,"上海天华科技有限公司",true,0,(short)13)
                .nextRow(23)
                .createNextCell("税号",true,25,(short)13)
                .createCell(5,"",true,15,(short)13)
                .createCell(7,"税号",true,15,(short)13)
                .createCell(11,"49872587324879285",true,0,(short)13)
                .nextRow(23)
                .createNextCell("银行账号",true,25,(short)13)
                .createCell(5,"",true,15,(short)13)
                .createCell(7,"银行账号",true,15,(short)13)
                .createCell(11,"3597824878256328",true,0,(short)13)
                .nextRow(23)
                .createNextCell("银行支行信息",true,25,(short)13)
                .createCell(5,"",true,15,(short)13)
                .createCell(7,"银行支行信息",true,15,(short)13)
                .createCell(11,"中国银行上海浦东分行营业部",true,0,(short)13)
                .nextRow(23)
                .createNextCell("签字",true,25,(short)13)
                .createCell(5,"",true,15,(short)13)
                .createCell(7,"签字",true,15,(short)13)
                .createCell(11,"",true,0,(short)13);
        sheet.writeFile("D:\文档\test\test1.xlsx");
    }

}