[alibaba/easyexcel]怎么设置单元格属性为文本,在这个https://github.com/alibaba/easyexcel/issues/920我没有找到解决方案,请问解决了吗

2024-05-17 821 views
2

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy implements CellWriteHandler {

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat dataFormat = workbook.createDataFormat();
    cellStyle.setDataFormat(dataFormat.getFormat("@"));

    //居中
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    //设置边框
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);

    cell.setCellStyle(cellStyle);

}

@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) {

}

private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

@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) {
        Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
        Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
        if (columnWidth >= 0) {
            if (columnWidth > 255) {
                columnWidth = 255;
            }

            Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
            if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
            }

        }
    }
}

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

}

回答

3

为什么poi这样设置可以实现我想要的效果而easyexcel就不行呢?问题在哪 @Test public void test2() throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(" test1 ");

    /**设置单元格格式为文本格式*/
    XSSFCellStyle textStyle = workbook.createCellStyle();
    XSSFDataFormat format = workbook.createDataFormat();
    textStyle.setDataFormat(format.getFormat("@"));

    XSSFRow row = spreadsheet.createRow(0);
    Cell cell = row.createCell(0);

    cell.setCellStyle(textStyle);//设置单元格格式为"文本"
    cell.setCellType(XSSFCell.CELL_TYPE_STRING);

    FileOutputStream out = new FileOutputStream(new File("PoiWritesheet1.xlsx"));
    workbook.write(out);
    out.close();
    System.out.println("Writesheet.xlsx written successfully" );
}
9
1707
0

使用注解: @ContentStyle(dataFormat = 49)