excelPoi-导出类型为数值

ExcelPoi

开发中遇到的导出问题，要把导出的数字，由文本改为数值，类型改为数字

• 首先把导出的文本改为数字很简单，只要在@EXCEL(type = 10)即可，但是我们要把单元格类型由文本类型改为数值类型

• 由于我用的是ExcelPoi封装好的工具类：ExcelExportUtil.exportExcel ( ),他会使用默认的ExcelExportStylerDefaultImpl类去实现他的样式

看源码：

/**
 * 样式的默认实现
 * @author JueYue
 *  2015年1月9日 下午5:36:08
 */
public class ExcelExportStylerDefaultImpl extends AbstractExcelExportStyler
                                          implements IExcelExportStyler {

这时候我们要自定义自己的样式，只需要重写他的实现类即可

public class CustomExcelExportStyler  extends ExcelExportStylerDefaultImpl {

    private CellStyle cellStyle;
    public CustomExcelExportStyler(Workbook workbook) {
        super(workbook);
        createNumberCellStyler();

    }
    private void createNumberCellStyler() {
        cellStyle = workbook.createCellStyle();
        DataFormat df = workbook.createDataFormat();
        this.cellStyle.setAlignment(HorizontalAlignment.CENTER);
        this.cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置数字样式
        this.cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0.00"));
        //设置格式为数值类型
        this.cellStyle.setDataFormat(df.getFormat("0.00_ "));
        this.cellStyle.setWrapText(true);
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) {
        if (entity != null && ExcelSystemParm.EXCEL_COVERT_MONEY.equals(entity.getDict())){
            entity.setType(BaseEntityTypeConstants.DOUBLE_TYPE);
            return cellStyle;
        }

        return super.getStyles(noneStyler, entity);
    }
}

这里我只设置了他的数字样式和数值格式，但是我们要告诉他要使用我们自定义的样式类，

我们只需要一步：exportParams.setStyle(CustomExcelExportStyler.class);

看看他的源码：这边 style 使用的是默认的 实现类

/**
 * Excel 导出参数
 *
 * @author JueYue
 * @version 1.0 2013年8月24日
 */
@Data
public class ExportParams extends ExcelBaseParams {
    /**
     * Excel 导出style
     */
    private Class<?>  style = ExcelExportStylerDefaultImpl.class;
}

但是我想要指定字段变成数值，我们要怎么做呢

• 先定一个全局变量 EXCEL_NUMBER="数字“
• 在@EXCEL (dict = EXCEL_NUMBER )
• 通过反射拿到@excel注解中的值，把他的Style改成使用我们自定义的样式实现类

Field[] fields = pojoClass.getDeclaredFields();
for (Field field : fields) {
    Excel excel = field.getAnnotation(Excel.class);
    if (excel!=null && excel.dict()!=null){
        if (ExcelSystemParm.EXCEL_NUMBER.equals(excel.dict())){
            exportParams.setStyle(CustomExcelExportStyler.class);
        }
    }
}

这样就完成了 OK

接下来我们来剖析一下源码

/*关键代码，一键生成--------------
*
*exportParams-Excel导出参数
*pojoClass-实体类：User.class
*list-导出数据集合
*/
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

• exportExcel方法

/**
 * @param entity    表格标题属性
 * @param pojoClass Excel对象Class
 * @param dataSet   Excel对象数据List
 */
public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
                                   Collection<?> dataSet) {
    //判断表格是 HSSF 还是 XSSF
    Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
    //创建表格
    new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
    return workbook;
}

• createSheet方法（创建所有的表拿到所有的字段）

public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass,
                        Collection<?> dataSet) {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel export start ,class is {}", pojoClass);
        LOGGER.debug("Excel version is {}",
                entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }
    if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    try {
        List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
        // 得到所有字段
        Field[]     fileds   = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget  = pojoClass.getAnnotation(ExcelTarget.class);
        String      targetId = etarget == null ? null : etarget.value();
        getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,
                null, null);
        //获取所有参数后,后面的逻辑判断就一致了
        createSheetForMap(workbook, entity, excelParams, dataSet);
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
}

• createSheetForMap方法

public void createSheetForMap(Workbook workbook, ExportParams entity,
                              List<ExcelExportEntity> entityList, Collection<?> dataSet) {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel version is {}",
                entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }
    if (workbook == null || entity == null || entityList == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    super.type = entity.getType();
    if (type.equals(ExcelType.XSSF)) {
        MAX_NUM = 1000000;
    }
    if (entity.getMaxNum() > 0) {
        MAX_NUM = entity.getMaxNum();
    }
    Sheet sheet = null;
    try {
        sheet = workbook.createSheet(entity.getSheetName());
    } catch (Exception e) {
        // 重复遍历,出现了重名现象,创建非指定的名称Sheet
        sheet = workbook.createSheet();
    }
    if (entity.isReadonly()) {
        sheet.protectSheet(UUID.randomUUID().toString());
    }
    if (dataSet.getClass().getClass().getName().contains("Unmodifiable")) {
        List dataTemp = new ArrayList<>();
        dataTemp.addAll(dataSet);
        dataSet = dataTemp;
    }
    insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
}

• insertDataToSheet方法（重点插入数据）

protected void insertDataToSheet(Workbook workbook, ExportParams entity,
                                 List<ExcelExportEntity> entityList, Collection<?> dataSet,
                                 Sheet sheet) {
    try {
        dataHandler = entity.getDataHandler();
        if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {
            needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
        }
        dictHandler = entity.getDictHandler();
        i18nHandler = entity.getI18nHandler();
        // 创建表格样式
        setExcelExportStyler((IExcelExportStyler) entity.getStyle()
                .getConstructor(Workbook.class).newInstance(workbook));
        Drawing                 patriarch   = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
        List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
        if (entity.isAddIndex()) {
            excelParams.add(indexExcelEntity(entity));
        }
        excelParams.addAll(entityList);
        sortAllParams(excelParams);
        int index = entity.isCreateHeadRows()
                ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
        int titleHeight = index;
        setCellWith(excelParams, sheet);
        setColumnHidden(excelParams, sheet);
        short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);
        setCurrentIndex(1);
        
        //重点-遍历数据
        Iterator<?>  its      = dataSet.iterator();
        //tempList 的作用是当 excel 中数据超出 excel 限制的时候，创建新的表格
        List<Object> tempList = new ArrayList<Object>();
        while (its.hasNext()) {
            Object t = its.next();
            //重点
            index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
            //把已经保存的数据保存到 tempList 中
            tempList.add(t);
            //当index 超出 excel最大容量的时候退出循环
            if (index >= MAX_NUM) {
                break;
            }
        }
        if (entity.getFreezeCol() != 0) {
            sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
        }
		
        mergeCells(sheet, excelParams, titleHeight);
		//便利去除掉 dataSet 中已经存放到excel中的数据，如果去除掉之后还有，则创建新的表格
        its = dataSet.iterator();
        for (int i = 0, le = tempList.size(); i < le; i++) {
            its.next();
            its.remove();
        }
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("List data more than max ,data size is {}",
                    dataSet.size());
        }
        // 发现还有剩余list 继续循环创建Sheet
        if (dataSet.size() > 0) {
            createSheetForMap(workbook, entity, entityList, dataSet);
        } else {
            // 创建合计信息
            addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);
        }

    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
}

• createCells方法

public int[] createCells(Drawing patriarch, int index, Object t,
                         List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,
                         short rowHeight, int cellNum) {
    try {
        ExcelExportEntity entity;
        Row               row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);
        if (rowHeight != -1) {
            row.setHeight(rowHeight);
        }
        int maxHeight = 1, listMaxHeight = 1;
        // 合并需要合并的单元格
        int margeCellNum = cellNum;
        int indexKey     = 0;
        if (excelParams != null && !excelParams.isEmpty()) {
            indexKey = createIndexCell(row, index, excelParams.get(0));
        }
        cellNum += indexKey;
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            //不论数据是否为空都应该把该列的数据跳过去
            if (entity.getList() != null) {
                Collection<?> list  = getListCellValue(entity, t);
                int           tmpListHeight = 0;
                if (list != null && list.size() > 0) {
                    int tempCellNum = 0;
                    for (Object obj : list) {
                        int[] temp = createCells(patriarch, index + tmpListHeight, obj, entity.getList(), sheet, workbook, rowHeight, cellNum);
                        tempCellNum = temp[1];
                        tmpListHeight += temp[0];
                    }
                    cellNum = tempCellNum;
                    listMaxHeight = Math.max(listMaxHeight, tmpListHeight);
                } else {
                    cellNum = cellNum + getListCellSize(entity.getList());
                }
            } else {
                Object value = getCellValue(entity, t);
				//当entity.getType = String 类型的时候创建 String 类型的单元格
                if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                    createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                            entity);
				//当entity.getType = String 类型的时候创建 Double类型的单元格
                } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                    createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                            index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                            entity);
                //图片
                } else {
                    createImageCell(patriarch, entity, row, cellNum++,
                            value == null ? "" : value.toString(), t);
                }
                if (entity.isHyperlink()) {
                    row.getCell(cellNum - 1)
                            .setHyperlink(dataHandler.getHyperlink(
                                    row.getSheet().getWorkbook().getCreationHelper(), t,
                                    entity.getName(), value));
                }
            }
        }
        maxHeight += listMaxHeight - 1;
        if (indexKey == 1 && excelParams.get(1).isNeedMerge()) {
            excelParams.get(0).setNeedMerge(true);
        }
        for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
            entity = excelParams.get(k);
            if (entity.getList() != null) {
                margeCellNum += entity.getList().size();
            } else if (entity.isNeedMerge() && maxHeight > 1) {
                for (int i = index + 1; i < index + maxHeight; i++) {
                    if (sheet.getRow(i) == null) {
                        sheet.createRow(i);
                    }
                    sheet.getRow(i).createCell(margeCellNum);
                    sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity));
                }
                PoiMergeCellUtil.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum, margeCellNum);
                margeCellNum++;
            }
        }
        return new int[]{maxHeight, cellNum};
    } catch (Exception e) {
        LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);
    }

}