com.alibaba.excel.metadata.CellData Java Examples

@Override
public Enum convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
    String cellDataStr = cellData.getStringValue();

    EnumFormat annotation = contentProperty.getField().getAnnotation(EnumFormat.class);
    Class enumClazz = annotation.value();
    String[] fromExcel = annotation.fromExcel();
    String[] toJavaEnum = annotation.toJavaEnum();

    Enum anEnum = null;
    if (ArrayUtils.isNotEmpty(fromExcel) && ArrayUtils.isNotEmpty(toJavaEnum)) {
        Assert.isTrue(fromExcel.length == toJavaEnum.length, "fromExcel 与 toJavaEnum 的长度必须相同");
        for (int i = 0; i < fromExcel.length; i++) {
            if (Objects.equals(fromExcel[i], cellDataStr)) {
                anEnum = EnumUtils.getEnum(enumClazz, toJavaEnum[i]);
            }
        }
    } else {
        anEnum = EnumUtils.getEnum(enumClazz, cellDataStr);
    }

    Assert.notNull(anEnum, "枚举值不合法");
    return anEnum;
}
 

@Override
public CellData convertToExcelData(Enum value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {

    String enumName = value.name();

    EnumFormat annotation = contentProperty.getField().getAnnotation(EnumFormat.class);
    String[] fromExcel = annotation.fromExcel();
    String[] toJavaEnum = annotation.toJavaEnum();

    if (ArrayUtils.isNotEmpty(fromExcel) && ArrayUtils.isNotEmpty(toJavaEnum)) {
        Assert.isTrue(fromExcel.length == toJavaEnum.length, "fromExcel 与 toJavaEnum 的长度必须相同");
        for (int i = 0; i < toJavaEnum.length; i++) {
            if (Objects.equals(toJavaEnum[i], enumName)) {
                return new CellData(fromExcel[i]);
            }
        }
    }
    return new CellData(enumName);
}
 

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
    List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        Assert.assertEquals(1L, beforeCellCreate);
        Assert.assertEquals(1L, afterCellCreate);
        Assert.assertEquals(0L, afterCellDataConverted);
        Assert.assertEquals(0L, afterCellDispose);
        Assert.assertEquals(1L, beforeRowCreate);
        Assert.assertEquals(1L, afterRowCreate);
        Assert.assertEquals(0L, afterRowDispose);
        Assert.assertEquals(1L, beforeSheetCreate);
        Assert.assertEquals(1L, afterSheetCreate);
        Assert.assertEquals(1L, beforeWorkbookCreate);
        Assert.assertEquals(1L, afterWorkbookCreate);
        Assert.assertEquals(0L, afterWorkbookDispose);
        afterCellDispose++;
    }
}
 

@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
    Integer relativeRowIndex, Boolean isHead) {
    boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
    if (!needSetWidth) {
        return;
    }
    Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
    if (maxColumnWidthMap == null) {
        maxColumnWidthMap = new HashMap<Integer, Integer>(16);
        cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
    }
    Integer columnWidth = dataLength(cellDataList, cell, isHead);
    if (columnWidth < 0) {
        return;
    }
    if (columnWidth > MAX_COLUMN_WIDTH) {
        columnWidth = MAX_COLUMN_WIDTH;
    }
    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
    }
}
 

private List<ConverterData> data() throws Exception {
    List<ConverterData> list = new ArrayList<ConverterData>();
    ConverterData converterData = new ConverterData();
    converterData.setDate(DateUtils.parseDate("2020-01-01 01:01:01"));
    converterData.setBooleanData(Boolean.TRUE);
    converterData.setBigDecimal(BigDecimal.ONE);
    converterData.setLongData(1L);
    converterData.setIntegerData(1);
    converterData.setShortData((short)1);
    converterData.setByteData((byte)1);
    converterData.setDoubleData(1.0);
    converterData.setFloatData((float)1.0);
    converterData.setString("测试");
    converterData.setCellData(new CellData("自定义"));
    list.add(converterData);
    return list;
}
 

private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
    if (isHead) {
        return cell.getStringCellValue().getBytes().length;
    }
    CellData cellData = cellDataList.get(0);
    CellDataTypeEnum type = cellData.getType();
    if (type == null) {
        return -1;
    }
    switch (type) {
        case STRING:
            return cellData.getStringValue().getBytes().length;
        case BOOLEAN:
            return cellData.getBooleanValue().toString().getBytes().length;
        case NUMBER:
            return cellData.getNumberValue().toString().getBytes().length;
        default:
            return -1;
    }
}
 

@Override
public void processRecord(XlsReadContext xlsReadContext, Record record) {
    XlsReadSheetHolder xlsReadSheetHolder = xlsReadContext.xlsReadSheetHolder();
    if (record instanceof LastCellOfRowDummyRecord) {
        // End of this row
        LastCellOfRowDummyRecord lcrdr = (LastCellOfRowDummyRecord)record;
        xlsReadSheetHolder.setRowIndex(lcrdr.getRow());
        xlsReadContext.readRowHolder(new ReadRowHolder(lcrdr.getRow(), xlsReadSheetHolder.getTempRowType(),
            xlsReadContext.readSheetHolder().getGlobalConfiguration(), xlsReadSheetHolder.getCellMap()));
        xlsReadContext.analysisEventProcessor().endRow(xlsReadContext);
        xlsReadSheetHolder.setCellMap(new LinkedHashMap<Integer, Cell>());
        xlsReadSheetHolder.setTempRowType(RowTypeEnum.EMPTY);
    } else if (record instanceof MissingCellDummyRecord) {
        MissingCellDummyRecord mcdr = (MissingCellDummyRecord)record;
        xlsReadSheetHolder.getCellMap().put(mcdr.getColumn(),
            CellData.newEmptyInstance(mcdr.getRow(), mcdr.getColumn()));
    }
}
 

@Override
public CellData convertToExcelData(Float value, ExcelContentProperty contentProperty,
    GlobalConfiguration globalConfiguration) {
    if (ONE.equals(value)) {
        return new CellData(Boolean.TRUE);
    }
    return new CellData(Boolean.FALSE);
}
 

@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    String stringValue = cellData.getStringValue();
    if (DELETE.equals(stringValue)){
        return 1;
    }else {
        return 0;
    }
}
 

@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    String stringValue = cellData.getStringValue();
    if (DELETE.equals(stringValue)){
        return 1;
    }else {
        return 0;
    }
}
 

public static void afterCellDataConverted(WriteContext writeContext, CellData cellData, Cell cell, Head head,
    Integer relativeRowIndex, Boolean isHead) {
    List<WriteHandler> handlerList =
        writeContext.currentWriteHolder().writeHandlerMap().get(CellWriteHandler.class);
    if (handlerList == null || handlerList.isEmpty()) {
        return;
    }
    for (WriteHandler writeHandler : handlerList) {
        if (writeHandler instanceof CellWriteHandler) {
            ((CellWriteHandler) writeHandler).afterCellDataConverted(writeContext.writeSheetHolder(),
                writeContext.writeTableHolder(), cellData, cell, head, relativeRowIndex, isHead);
        }
    }
}
 

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
    List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        return;
    }
    merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
}
 

@Override
public Double convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
    GlobalConfiguration globalConfiguration) {
    if (cellData.getBooleanValue()) {
        return ONE;
    }
    return ZERO;
}
 

@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    if (integer==0){
        return new CellData(ENABLE);
    }else {
        return new CellData(NOT_ENABLE);
    }
}
 

public static void afterCellDispose(WriteContext writeContext, CellData cellData, Cell cell, Head head,
    Integer relativeRowIndex, Boolean isHead) {
    List<CellData> cellDataList = new ArrayList<CellData>();
    if (cell != null) {
        cellDataList.add(cellData);
    }
    afterCellDispose(writeContext, cellDataList, cell, head, relativeRowIndex, isHead);
}
 

private Object buildUserModel(Map<Integer, CellData> cellDataMap, ReadHolder currentReadHolder,
    AnalysisContext context) {
    ExcelReadHeadProperty excelReadHeadProperty = currentReadHolder.excelReadHeadProperty();
    Object resultModel;
    try {
        resultModel = excelReadHeadProperty.getHeadClazz().newInstance();
    } catch (Exception e) {
        throw new ExcelDataConvertException(context.readRowHolder().getRowIndex(), 0,
            new CellData(CellDataTypeEnum.EMPTY), null,
            "Can not instance class: " + excelReadHeadProperty.getHeadClazz().getName(), e);
    }
    Map<Integer, Head> headMap = excelReadHeadProperty.getHeadMap();
    Map<String, Object> map = new HashMap<String, Object>(headMap.size() * 4 / 3 + 1);
    Map<Integer, ExcelContentProperty> contentPropertyMap = excelReadHeadProperty.getContentPropertyMap();
    for (Map.Entry<Integer, Head> entry : headMap.entrySet()) {
        Integer index = entry.getKey();
        if (!cellDataMap.containsKey(index)) {
            continue;
        }
        CellData cellData = cellDataMap.get(index);
        if (cellData.getType() == CellDataTypeEnum.EMPTY) {
            continue;
        }
        ExcelContentProperty excelContentProperty = contentPropertyMap.get(index);
        Object value = ConverterUtils.convertToJavaObject(cellData, excelContentProperty.getField(),
            excelContentProperty, currentReadHolder.converterMap(), currentReadHolder.globalConfiguration(),
            context.readRowHolder().getRowIndex(), index);
        if (value != null) {
            map.put(excelContentProperty.getField().getName(), value);
        }
    }
    BeanMap.create(resultModel).putAll(map);
    return resultModel;
}
 

@Override
public CellData convertToExcelData(Date value, ExcelContentProperty contentProperty,
    GlobalConfiguration globalConfiguration) {
    if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
        return new CellData(
            BigDecimal.valueOf(DateUtil.getExcelDate(value, globalConfiguration.getUse1904windowing())));
    } else {
        return new CellData(BigDecimal.valueOf(
            DateUtil.getExcelDate(value, contentProperty.getDateTimeFormatProperty().getUse1904windowing())));
    }
}
 

@Override
public void invoke(Map<Integer, CellData> cellDataMap, AnalysisContext context) {
    ReadHolder currentReadHolder = context.currentReadHolder();
    if (HeadKindEnum.CLASS.equals(currentReadHolder.excelReadHeadProperty().getHeadKind())) {
        context.readRowHolder()
            .setCurrentRowAnalysisResult(buildUserModel(cellDataMap, currentReadHolder, context));
        return;
    }
    context.readRowHolder().setCurrentRowAnalysisResult(buildStringList(cellDataMap, currentReadHolder, context));
}
 

@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    if (integer==0){
        return new CellData(NOT_DELETE);
    }else {
        return new CellData(DELETE);
    }
}
 

protected CellData converterAndSet(WriteHolder currentWriteHolder, Class clazz, Cell cell, Object value,
    ExcelContentProperty excelContentProperty, Head head, Integer relativeRowIndex) {
    if (value == null) {
        return new CellData(CellDataTypeEnum.EMPTY);
    }
    if (value instanceof String && currentWriteHolder.globalConfiguration().getAutoTrim()) {
        value = ((String)value).trim();
    }
    CellData cellData = convert(currentWriteHolder, clazz, cell, value, excelContentProperty);
    if (cellData.getFormula() != null && cellData.getFormula()) {
        cell.setCellFormula(cellData.getFormulaValue());
    }
    if (cellData.getType() == null) {
        cellData.setType(CellDataTypeEnum.EMPTY);
    }
    WriteHandlerUtils.afterCellDataConverted(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE);
    switch (cellData.getType()) {
        case STRING:
            cell.setCellValue(cellData.getStringValue());
            return cellData;
        case BOOLEAN:
            cell.setCellValue(cellData.getBooleanValue());
            return cellData;
        case NUMBER:
            cell.setCellValue(cellData.getNumberValue().doubleValue());
            return cellData;
        case IMAGE:
            setImageValue(cellData, cell);
            return cellData;
        case EMPTY:
            return cellData;
        default:
            throw new ExcelDataConvertException(cell.getRow().getRowNum(), cell.getColumnIndex(), cellData,
                excelContentProperty, "Not supported data:" + value + " return type:" + cell.getCellType()
                    + "at row:" + cell.getRow().getRowNum());
    }
}
 

@Override
public void startElement(XlsxReadContext xlsxReadContext, String name, Attributes attributes) {
    XlsxReadSheetHolder xlsxReadSheetHolder = xlsxReadContext.xlsxReadSheetHolder();
    xlsxReadSheetHolder.setColumnIndex(PositionUtils.getCol(attributes.getValue(ExcelXmlConstants.ATTRIBUTE_R),
        xlsxReadSheetHolder.getColumnIndex()));

    // t="s" ,it's means String
    // t="str" ,it's means String,but does not need to be read in the 'sharedStrings.xml'
    // t="inlineStr" ,it's means String
    // t="b" ,it's means Boolean
    // t="e" ,it's means Error
    // t="n" ,it's means Number
    // t is null ,it's means Empty or Number
    CellDataTypeEnum type = CellDataTypeEnum.buildFromCellType(attributes.getValue(ExcelXmlConstants.ATTRIBUTE_T));
    xlsxReadSheetHolder.setTempCellData(new CellData(type));
    xlsxReadSheetHolder.setTempData(new StringBuilder());

    // Put in data transformation information
    String dateFormatIndex = attributes.getValue(ExcelXmlConstants.ATTRIBUTE_S);
    Integer dateFormatIndexInteger;
    if (StringUtils.isEmpty(dateFormatIndex)) {
        dateFormatIndexInteger = DEFAULT_FORMAT_INDEX;
    } else {
        dateFormatIndexInteger = Integer.parseInt(dateFormatIndex);
    }
    XSSFCellStyle xssfCellStyle =
        xlsxReadContext.xlsxReadWorkbookHolder().getStylesTable().getStyleAt(dateFormatIndexInteger);
    int dataFormat = xssfCellStyle.getDataFormat();
    xlsxReadSheetHolder.getTempCellData().setDataFormat(dataFormat);
    xlsxReadSheetHolder.getTempCellData().setDataFormatString(BuiltinFormats.getBuiltinFormat(dataFormat,
        xssfCellStyle.getDataFormatString(), xlsxReadSheetHolder.getGlobalConfiguration().getLocale()));
}
 

@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
    String stringValue = cellData.getStringValue();
    if (DELETE.equals(stringValue)){
        return 1;
    }else {
        return 0;
    }
}
 

@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
    Integer relativeRowIndex, Boolean isHead) {
    boolean needSetWidth = relativeRowIndex != null && (isHead || relativeRowIndex == 0);
    if (!needSetWidth) {
        return;
    }
    Integer width = columnWidth(head, cell.getColumnIndex());
    if (width != null) {
        width = width * 256;
        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width);
    }
}
 

private CellData doConvert(WriteHolder currentWriteHolder, Class clazz, Cell cell, Object value,
    ExcelContentProperty excelContentProperty) {
    Converter converter = null;
    if (excelContentProperty != null) {
        converter = excelContentProperty.getConverter();
    }
    if (converter == null) {
        converter = currentWriteHolder.converterMap().get(ConverterKeyBuild.buildKey(clazz));
    }
    if (converter == null) {
        throw new ExcelDataConvertException(cell.getRow().getRowNum(), cell.getColumnIndex(),
            new CellData(CellDataTypeEnum.EMPTY), excelContentProperty,
            "Can not find 'Converter' support class " + clazz.getSimpleName() + ".");
    }
    CellData cellData;
    try {
        cellData =
            converter.convertToExcelData(value, excelContentProperty, currentWriteHolder.globalConfiguration());
    } catch (Exception e) {
        throw new ExcelDataConvertException(cell.getRow().getRowNum(), cell.getColumnIndex(),
            new CellData(CellDataTypeEnum.EMPTY), excelContentProperty,
            "Convert data:" + value + " error,at row:" + cell.getRow().getRowNum(), e);
    }
    if (cellData == null || cellData.getType() == null) {
        throw new ExcelDataConvertException(cell.getRow().getRowNum(), cell.getColumnIndex(),
            new CellData(CellDataTypeEnum.EMPTY), excelContentProperty,
            "Convert data:" + value + " return null,at row:" + cell.getRow().getRowNum());
    }
    return cellData;
}
 

private void addOneRowOfHeadDataToExcel(Row row, Map<Integer, Head> headMap, int relativeRowIndex) {
    for (Map.Entry<Integer, Head> entry : headMap.entrySet()) {
        Head head = entry.getValue();
        int columnIndex = entry.getKey();
        WriteHandlerUtils.beforeCellCreate(this, row, head, columnIndex, relativeRowIndex, Boolean.TRUE);
        Cell cell = row.createCell(columnIndex);
        WriteHandlerUtils.afterCellCreate(this, cell, head, relativeRowIndex, Boolean.TRUE);
        cell.setCellValue(head.getHeadNameList().get(relativeRowIndex));
        WriteHandlerUtils.afterCellDispose(this, (CellData) null, cell, head, relativeRowIndex, Boolean.TRUE);
    }
}
 

public ExcelDataConvertException(Integer rowIndex, Integer columnIndex, CellData cellData,
    ExcelContentProperty excelContentProperty, String message) {
    super(message);
    this.rowIndex = rowIndex;
    this.columnIndex = columnIndex;
    this.cellData = cellData;
    this.excelContentProperty = excelContentProperty;
}
 

@Override
public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
    GlobalConfiguration globalConfiguration) {
    if (cellData.getBooleanValue()) {
        return ONE;
    }
    return ZERO;
}
 

/**
 * Convert it into a Java object
 *
 * @param cellData
 * @param field
 * @param contentProperty
 * @param converterMap
 * @param globalConfiguration
 * @param rowIndex
 * @param columnIndex
 * @return
 */
public static Object convertToJavaObject(CellData cellData, Field field, ExcelContentProperty contentProperty,
    Map<String, Converter> converterMap, GlobalConfiguration globalConfiguration, Integer rowIndex,
    Integer columnIndex) {
    Class clazz;
    if (field == null) {
        clazz = String.class;
    } else {
        clazz = field.getType();
    }
    if (clazz == CellData.class) {
        Type type = field.getGenericType();
        Class classGeneric;
        if (type instanceof ParameterizedType) {
            ParameterizedType parameterizedType = (ParameterizedType)type;
            classGeneric = (Class)parameterizedType.getActualTypeArguments()[0];
        } else {
            classGeneric = String.class;
        }
        CellData cellDataReturn = new CellData(cellData);
        cellDataReturn.setData(doConvertToJavaObject(cellData, classGeneric, contentProperty, converterMap,
            globalConfiguration, rowIndex, columnIndex));
        return cellDataReturn;
    }
    return doConvertToJavaObject(cellData, clazz, contentProperty, converterMap, globalConfiguration, rowIndex,
        columnIndex);
}
 

@Override
protected void setStringValue(XlsxReadContext xlsxReadContext) {
    // This is a special form of string
    CellData tempCellData = xlsxReadContext.xlsxReadSheetHolder().getTempCellData();
    XSSFRichTextString richTextString = new XSSFRichTextString(tempCellData.getStringValue());
    tempCellData.setStringValue(richTextString.toString());
}
 

@Override
public void endElement(XlsxReadContext xlsxReadContext, String name) {
    XlsxReadSheetHolder xlsxReadSheetHolder = xlsxReadContext.xlsxReadSheetHolder();
    CellData tempCellData = xlsxReadSheetHolder.getTempCellData();
    StringBuilder tempData = xlsxReadSheetHolder.getTempData();
    CellDataTypeEnum oldType = tempCellData.getType();
    switch (oldType) {
        case DIRECT_STRING:
        case STRING:
        case ERROR:
            tempCellData.setStringValue(tempData.toString());
            break;
        case BOOLEAN:
            tempCellData.setBooleanValue(BooleanUtils.valueOf(tempData.toString()));
            break;
        case NUMBER:
        case EMPTY:
            tempCellData.setType(CellDataTypeEnum.NUMBER);
            tempCellData.setNumberValue(new BigDecimal(tempData.toString()));
            break;
        default:
            throw new IllegalStateException("Cannot set values now");
    }

    // set string value
    setStringValue(xlsxReadContext);

    if (tempCellData.getStringValue() != null
        && xlsxReadContext.currentReadHolder().globalConfiguration().getAutoTrim()) {
        tempCellData.setStringValue(tempCellData.getStringValue());
    }

    tempCellData.checkEmpty();
    xlsxReadSheetHolder.getCellMap().put(xlsxReadSheetHolder.getColumnIndex(), tempCellData);
}