java 导出之excel

Springboot之Excel导出

1.简介

            excel导出用的工具是easyExcel,其中导出主要步骤是准备环境，导出模板文件流，封装表头，封装数据list，书写策略改模式。其他看官方文档。

2.环境

　　　　　
　　　　//具体哪个真正有用不知道，这三个绝对够了
　　　　　<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- alibaba easyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

3.导出文件流

　　　　　　　//创建输入流
　　　　　　　　　ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            EasyExcel.write(outputStream, CmXcmzVo.class).excelType(ExcelTypeEnum.XLSX)
                    // 是否自动关流
                    .autoCloseStream(Boolean.FALSE)
　　　　　　　　　　　　　　// 添加表单的绑定策略
                    .registerWriteHandler(new CmXczwUtils(mergeRowIndex, mergeColumnIndex,MyMergeList))
　　　　　　　　　　　　　　// 添加的表头
                    .head(sinkRemoveHead(bean))
                    .sheet("显示的名字")
　　　　　　　　　　　　　　// 要导出的数据
                    .doWrite(list);
            HttpHeaders httpHeaders = new HttpHeaders();
            String fileName = new String("xcmz.xlsx".getBytes("UTF-8"), "iso-8859-1");
            httpHeaders.add("content-disposition", "attachment;filename=" + fileName);
            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            ResponseEntity<byte[]> filebyte = new ResponseEntity<byte[]>(outputStream.toByteArray(), httpHeaders,
                    HttpStatus.CREATED);

4.封装表头

//三行表头，表头的样式可调

private List<List<String>> sinkRemoveHead(CmZwkqDto bean) {
        String biaotou = "";
        String erbiaotou="";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");

        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add(biaotou);
        head0.add(erbiaotou);
        head0.add("序号");
        List<String> head1 = new ArrayList<String>();
        head1.add(biaotou);
        head1.add(erbiaotou);
        head1.add("姓名");

        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

 5.封装数据

按照导出表中对应的VO对象封装list即可

VO对象

@Data
@NoArgsConstructor
@AllArgsConstructor 
@Builder
@ContentRowHeight(25)     //单元格内容高度
@HeadRowHeight(35)　　　　　//表头
@ColumnWidth(17)          //列宽，以属性为准
//头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 9)
public class CmXcmzVo {
    @ColumnWidth(8)       //属性列宽
    private String xh;
    @ColumnWidth(12)
    private String name;
    @ColumnWidth(32)
    private String zwmc;
    @ColumnWidth(12)
    private String csny;
}

  6.书写策略

/**
 *  * @FileName: ExcelFillCellMergeStrategy.java
 *  * @creator lee
 *  * @date Dec 28, 2020
 *
 * @editor  * @Description: 
 *  * @version V1.0
 *  
 */import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.awt.BorderLayout;
import java.util.ArrayList;
import java.util.List;

public class CmXczwUtils implements CellWriteHandler {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;
    private ArrayList<int[]> MyMergeList;

    public CmXczwUtils() {
    }
　　// 构造函数当前行和当前列
    public CmXczwUtils(int mergeRowIndex, int[] mergeColumnIndex, ArrayList<int[]> MyMergeList) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.MyMergeList = MyMergeList;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
            Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
            Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @SuppressWarnings("rawtypes")
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @SuppressWarnings("rawtypes")
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        // 当前行
        int curRowIndex = cell.getRowIndex();
        // 当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
        // 修改样式表头

        if (curRowIndex == 1) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle(); // 字体
            cellStyle.setAlignment(HorizontalAlignment.LEFT); // 设置垂直对齐的样式为居左对齐;
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cell.setCellStyle(cellStyle);
        }

        if (curRowIndex >= 3) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            // 字体
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 设置垂直对齐的样式为居中对齐;
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            cell.setCellStyle(cellStyle);
            Font cellFont = workbook.createFont();
            // 加粗
            cellFont.setBold(false);
            // 设置自动换行
            cellStyle.setWrapText(true);
        }

    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
        // 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        /*
         * Object curData = cell.getCellType() == CellType.STRING ?
         * cell.getStringCellValue() : cell.getNumericCellValue(); Cell preCell =
         * cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData
         * = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() :
         * preCell.getNumericCellValue();
         */

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        /*
         * if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet();
         * List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean
         * isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++)
         * { CellRangeAddress cellRangeAddr = mergeRegions.get(i); //
         * 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex -
         * 1, curColIndex)) { sheet.removeMergedRegion(i);
         * cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr);
         * isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress
         * cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex,
         * curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } }
         */
    }

}

策略可以很灵活的控制导出的excel导出。主要有合并单元格，列宽，换行，单元格边框，颜色和字体。

通过判断可以定向改变内容。例如可以将阿拉伯数字改为中国的大写数字。

样式判断：用行和列做判断时是指这些行列已经渲染，例如当前列= =4，是指渲染第五列触发这个策略，从0列开始