Excel通用编码模版

一、需要导出一个Excel，每次都CV？

         CV能解决一定问题，但是面向代码的复用性，这种方案无疑是最失败的。

         为了代码复用，我们怎么设计？

          Excel专用的写入抽离,输入Excel的对象无缝代入Excel，实现模版化代码

二、代码设计

2.1 pom引入必要包

      <!--poi读写文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>

2.2 Excel抽离为service

package com.hecore.excelservice.excel;

import com.hecore.excelservice.model.excel.BaseExcel;
import com.hecore.excelservice.model.excel.CellName;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: hecore
 * @Date: 2020/4/16 20:36
 * @Description:
 */

@Component
public class ExcelWriteService {

    /**
     * 将数据写入到excel中
     * 金额|组织|事项|科目|运营项目|发生数|占用数|预算数
     */
    public void writeExcel(String sheetName, String outFile, List<CellName> cellNameList, List<BaseExcel> result) {

        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二部，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头
        HSSFCell cell = row.createCell(0);

        for (CellName cellName:cellNameList){
            cell.setCellValue(cellName.getName());
            cell = row.createCell(cellName.getCellNum());
        }
        for(int i=0;i<result.size();i++) {
            BaseExcel baseExcel=result.get(i);
            baseExcel.init(baseExcel);
            List<String> oneData=new ArrayList<>();
            for (CellName cellName:cellNameList){
                String dealKey=cellName.getDealName();
                oneData.add((String) baseExcel.getExcelMap().get(dealKey));
            }
            HSSFRow row1 = sheet.createRow(i + 1);
            for(int j=0;j<oneData.size();j++) {
                //创建单元格设值
                row1.createCell(j).setCellValue(oneData.get(j));
            }
        }

        //将文件保存到指定的位置
        try {
            FileOutputStream fos = new FileOutputStream(outFile);
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

2.3 ExcelModel基类

package com.hecore.excelservice.model.excel;

import lombok.Data;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;

/**
 * @Author: hecore
 * @Date: 2020/5/8 16:45
 * @Description:
 */

@Data
public class BaseExcel<T> {

    public HashMap<String,String> excelMap;

    // 反射写入
    public void init(T model){
        excelMap=new HashMap<>();
        Field[] fields = model.getClass().getDeclaredFields();
        for (Field field : fields) {
            try {
                String name=field.getName();
                name = name.substring(0,1).toUpperCase()+name.substring(1);
                Method m = model.getClass().getMethod("get"+name);
                Object o=m.invoke(model);
                excelMap.put(field.getName(),null==o?null:o.toString());
            }catch (Exception e){
                throw new RuntimeException(e);
            }
        }
    }

}

package com.hecore.excelservice.model.excel;

import lombok.Data;

/**
 * @Author: hecore
 * @Date: 2020/5/8 16:32
 * @Description:
 */

@Data
public class CellName {

    /**
     * 中文列名
     */
    private String name;

    /**
     * 放置于第几列
     */
    private Integer cellNum;

    /**
     * 同步数据model使用的名称
     */
    private String dealName;

    public String getName() {
        return name;
    }

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

    public Integer getCellNum() {
        return cellNum;
    }

    public  CellName setCellNum(Integer cellNum) {
        this.cellNum = cellNum;
        return this;
    }

    public String getDealName() {
        return dealName;
    }

    public CellName setDealName(String dealName) {
        this.dealName = dealName;
        return this;
    }
}

三、使用

3.1 model类

package com.hecore.excelservice.model.budget;

import com.hecore.excelservice.model.excel.BaseExcel;
import lombok.Data;

/**
 * @Author: hecore
 * @Date: 2020/4/16 20:09
 * @Description:
 */

@Data
public class BudetDO extends BaseExcel {

    private String month;

    private String dataId;

    private String budgetAmount;

    private String actualAmount;

    private String occupyAmount;

    private String org;

    private String orgName;

    private String account;

    private String accountName;

    /**
     * 预算事项
     */
    private String matter;

    private String matterName;

    private String project;

    private String projectName;

}

3.2 Service

    @Override
    public ReturnT getBudget() {
        String baseDir=getBasePath();
        List<BudetDO> recordList= iBudgetMapper.select();
        List<BaseExcel> baseExcels=transBudgetList(recordList);
        List<CellName> reordNameList=setBudgetecordColumn();
        String sheetName="预算统计";
        String targetFile=baseDir+"/"+"budgetT"+LocalDate.now()+"_"+ LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8")) +".xls";
        excelWriteService.writeExcel(sheetName,targetFile,reordNameList,baseExcels);
        return new ReturnT(recordList.size());
    }

    private List<CellName> setBudgetecordColumn() {
        List<CellName> record=new ArrayList<>();
        record.add(new CellName().setName("组织").setCellNum(1).setDealName("org"));
        record.add(new CellName().setName("组织名称").setCellNum(2).setDealName("orgName"));
        record.add(new CellName().setName("运营项目").setCellNum(3).setDealName("project"));
        record.add(new CellName().setName("运营项目名称").setCellNum(4).setDealName("projectName"));
        record.add(new CellName().setName("预算事项").setCellNum(5).setDealName("matter"));
        record.add(new CellName().setName("预算事项名称").setCellNum(6).setDealName("matterName"));
        record.add(new CellName().setName("预算科目").setCellNum(7).setDealName("account"));
        record.add(new CellName().setName("预算科目名称").setCellNum(8).setDealName("accountName"));
        record.add(new CellName().setName("发生数").setCellNum(9).setDealName("actualAmount"));
        record.add(new CellName().setName("占用数").setCellNum(10).setDealName("occupyAmount"));
        record.add(new CellName().setName("预算数").setCellNum(11).setDealName("budgetAmount"));
        record.add(new CellName().setName("月份").setCellNum(12).setDealName("month"));
        return record;
    }

四、说明

支持Excel行列调整

支持动态Model配置

提高代码的复用性