Examples with WriteCellData com.alibaba.excel.metadata.data.WriteCellData used on opensource projects

Example 1 with WriteCellData

use of com.alibaba.excel.metadata.data.WriteCellData in project RuoYi-Flowable-Plus by KonBAI-Q.

the class ExcelBigNumberConvert method convertToExcelData.

@Override
public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
    if (ObjectUtil.isNotNull(object)) {
        String str = Convert.toStr(object);
        if (str.length() > 15) {
            return new WriteCellData<>(str);
        }
    }
    WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
    cellData.setType(CellDataTypeEnum.NUMBER);
    return cellData;
}

Example 2 with WriteCellData

use of com.alibaba.excel.metadata.data.WriteCellData in project easyexcel by alibaba.

the class WriteTest method imageWrite.

/**
 * 图片导出
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link ImageDemoData}
 * <p>
 * 2. 直接写即可
 */
@Test
public void imageWrite() throws Exception {
    String fileName = TestFileUtil.getPath() + "imageWrite" + System.currentTimeMillis() + ".xlsx";
    String imagePath = TestFileUtil.getPath() + "converter" + File.separator + "img.jpg";
    try (InputStream inputStream = FileUtils.openInputStream(new File(imagePath))) {
        List<ImageDemoData> list = ListUtils.newArrayList();
        ImageDemoData imageDemoData = new ImageDemoData();
        list.add(imageDemoData);
        // 放入五种类型的图片 实际使用只要选一种即可
        imageDemoData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
        imageDemoData.setFile(new File(imagePath));
        imageDemoData.setString(imagePath);
        imageDemoData.setInputStream(inputStream);
        imageDemoData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
        // 这里演示
        // 需要额外放入文字
        // 而且需要放入2个图片
        // 第一个图片靠左
        // 第二个靠右 而且要额外的占用他后面的单元格
        WriteCellData<Void> writeCellData = new WriteCellData<>();
        imageDemoData.setWriteCellDataFile(writeCellData);
        // 这里可以设置为 EMPTY 则代表不需要其他数据了
        writeCellData.setType(CellDataTypeEnum.STRING);
        writeCellData.setStringValue("额外的放一些文字");
        // 可以放入多个图片
        List<ImageData> imageDataList = new ArrayList<>();
        ImageData imageData = new ImageData();
        imageDataList.add(imageData);
        writeCellData.setImageDataList(imageDataList);
        // 放入2进制图片
        imageData.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
        // 图片类型
        imageData.setImageType(ImageType.PICTURE_TYPE_PNG);
        // 上 右 下 左 需要留空
        // 这个类似于 css 的 margin
        // 这里实测 不能设置太大 超过单元格原始大小后 打开会提示修复。暂时未找到很好的解法。
        imageData.setTop(5);
        imageData.setRight(40);
        imageData.setBottom(5);
        imageData.setLeft(5);
        // 放入第二个图片
        imageData = new ImageData();
        imageDataList.add(imageData);
        writeCellData.setImageDataList(imageDataList);
        imageData.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
        imageData.setImageType(ImageType.PICTURE_TYPE_PNG);
        imageData.setTop(5);
        imageData.setRight(5);
        imageData.setBottom(5);
        imageData.setLeft(50);
        // 设置图片的位置 假设 现在目标 是 覆盖 当前单元格 和当前单元格右边的单元格
        // 起点相对于当前单元格为0 当然可以不写
        imageData.setRelativeFirstRowIndex(0);
        imageData.setRelativeFirstColumnIndex(0);
        imageData.setRelativeLastRowIndex(0);
        // 前面3个可以不写  下面这个需要写 也就是 结尾 需要相对当前单元格 往右移动一格
        // 也就是说 这个图片会覆盖当前单元格和 后面的那一格
        imageData.setRelativeLastColumnIndex(1);
        // 写入数据
        EasyExcel.write(fileName, ImageDemoData.class).sheet().doWrite(list);
    }
}

Example 3 with WriteCellData

use of com.alibaba.excel.metadata.data.WriteCellData in project easyexcel by alibaba.

the class WriteTest method writeCellDataWrite.

/**
 * 超链接、备注、公式、指定单个单元格的样式、单个单元格多种样式
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link WriteCellDemoData}
 * <p>
 * 2. 直接写即可
 *
 * @since 3.0.0-beta1
 */
@Test
public void writeCellDataWrite() {
    String fileName = TestFileUtil.getPath() + "writeCellDataWrite" + System.currentTimeMillis() + ".xlsx";
    WriteCellDemoData writeCellDemoData = new WriteCellDemoData();
    // 设置超链接
    WriteCellData<String> hyperlink = new WriteCellData<>("官方网站");
    writeCellDemoData.setHyperlink(hyperlink);
    HyperlinkData hyperlinkData = new HyperlinkData();
    hyperlink.setHyperlinkData(hyperlinkData);
    hyperlinkData.setAddress("https://github.com/alibaba/easyexcel");
    hyperlinkData.setHyperlinkType(HyperlinkType.URL);
    // 设置备注
    WriteCellData<String> comment = new WriteCellData<>("备注的单元格信息");
    writeCellDemoData.setCommentData(comment);
    CommentData commentData = new CommentData();
    comment.setCommentData(commentData);
    commentData.setAuthor("Jiaju Zhuang");
    commentData.setRichTextStringData(new RichTextStringData("这是一个备注"));
    // 备注的默认大小是按照单元格的大小 这里想调整到4个单元格那么大 所以向后 向下 各额外占用了一个单元格
    commentData.setRelativeLastColumnIndex(1);
    commentData.setRelativeLastRowIndex(1);
    // 设置公式
    WriteCellData<String> formula = new WriteCellData<>();
    writeCellDemoData.setFormulaData(formula);
    FormulaData formulaData = new FormulaData();
    formula.setFormulaData(formulaData);
    // 将 123456789 中的第一个数字替换成 2
    // 这里只是例子 如果真的涉及到公式 能内存算好尽量内存算好 公式能不用尽量不用
    formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
    // 设置单个单元格的样式 当然样式 很多的话 也可以用注解等方式。
    WriteCellData<String> writeCellStyle = new WriteCellData<>("单元格样式");
    writeCellStyle.setType(CellDataTypeEnum.STRING);
    writeCellDemoData.setWriteCellStyle(writeCellStyle);
    WriteCellStyle writeCellStyleData = new WriteCellStyle();
    writeCellStyle.setWriteCellStyle(writeCellStyleData);
    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
    writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景绿色
    writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    // 设置单个单元格多种样式
    WriteCellData<String> richTest = new WriteCellData<>();
    richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);
    writeCellDemoData.setRichText(richTest);
    RichTextStringData richTextStringData = new RichTextStringData();
    richTest.setRichTextStringDataValue(richTextStringData);
    richTextStringData.setTextString("红色绿色默认");
    // 前2个字红色
    WriteFont writeFont = new WriteFont();
    writeFont.setColor(IndexedColors.RED.getIndex());
    richTextStringData.applyFont(0, 2, writeFont);
    // 接下来2个字绿色
    writeFont = new WriteFont();
    writeFont.setColor(IndexedColors.GREEN.getIndex());
    richTextStringData.applyFont(2, 4, writeFont);
    List<WriteCellDemoData> data = new ArrayList<>();
    data.add(writeCellDemoData);
    EasyExcel.write(fileName, WriteCellDemoData.class).inMemory(true).sheet("模板").doWrite(data);
}

Example 4 with WriteCellData

use of com.alibaba.excel.metadata.data.WriteCellData in project easyexcel by alibaba.

the class UrlImageConverter method convertToExcelData.

@Override
public WriteCellData<?> convertToExcelData(URL value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws IOException {
    InputStream inputStream = null;
    try {
        URLConnection urlConnection = value.openConnection();
        urlConnection.setConnectTimeout(urlConnectTimeout);
        urlConnection.setReadTimeout(urlReadTimeout);
        inputStream = urlConnection.getInputStream();
        byte[] bytes = IoUtils.toByteArray(inputStream);
        return new WriteCellData<>(bytes);
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
    }
}

Example 5 with WriteCellData

use of com.alibaba.excel.metadata.data.WriteCellData in project easyexcel by alibaba.

the class ExcelWriteFillExecutor method doFill.

private void doFill(List<AnalysisCell> analysisCellList, Object oneRowData, FillConfig fillConfig, Integer relativeRowIndex) {
    if (CollectionUtils.isEmpty(analysisCellList) || oneRowData == null) {
        return;
    }
    Map dataMap;
    if (oneRowData instanceof Map) {
        dataMap = (Map) oneRowData;
    } else {
        dataMap = BeanMapUtils.create(oneRowData);
    }
    Set<String> dataKeySet = new HashSet<>(dataMap.keySet());
    RowWriteHandlerContext rowWriteHandlerContext = WriteHandlerUtils.createRowWriteHandlerContext(writeContext, null, relativeRowIndex, Boolean.FALSE);
    for (AnalysisCell analysisCell : analysisCellList) {
        CellWriteHandlerContext cellWriteHandlerContext = WriteHandlerUtils.createCellWriteHandlerContext(writeContext, null, analysisCell.getRowIndex(), null, analysisCell.getColumnIndex(), relativeRowIndex, Boolean.FALSE, ExcelContentProperty.EMPTY);
        if (analysisCell.getOnlyOneVariable()) {
            String variable = analysisCell.getVariableList().get(0);
            if (!dataKeySet.contains(variable)) {
                continue;
            }
            Object value = dataMap.get(variable);
            ExcelContentProperty excelContentProperty = ClassUtils.declaredExcelContentProperty(dataMap, writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadClazz(), variable);
            cellWriteHandlerContext.setExcelContentProperty(excelContentProperty);
            createCell(analysisCell, fillConfig, cellWriteHandlerContext, rowWriteHandlerContext);
            cellWriteHandlerContext.setOriginalValue(value);
            cellWriteHandlerContext.setOriginalFieldClass(FieldUtils.getFieldClass(dataMap, variable, value));
            converterAndSet(cellWriteHandlerContext);
            WriteCellData<?> cellData = cellWriteHandlerContext.getFirstCellData();
            // Restyle
            if (fillConfig.getAutoStyle()) {
                Optional.ofNullable(collectionFieldStyleCache.get(currentUniqueDataFlag)).map(collectionFieldStyleMap -> collectionFieldStyleMap.get(analysisCell)).ifPresent(cellData::setOriginCellStyle);
            }
        } else {
            StringBuilder cellValueBuild = new StringBuilder();
            int index = 0;
            List<WriteCellData<?>> cellDataList = new ArrayList<>();
            cellWriteHandlerContext.setExcelContentProperty(ExcelContentProperty.EMPTY);
            cellWriteHandlerContext.setIgnoreFillStyle(Boolean.TRUE);
            createCell(analysisCell, fillConfig, cellWriteHandlerContext, rowWriteHandlerContext);
            Cell cell = cellWriteHandlerContext.getCell();
            for (String variable : analysisCell.getVariableList()) {
                cellValueBuild.append(analysisCell.getPrepareDataList().get(index++));
                if (!dataKeySet.contains(variable)) {
                    continue;
                }
                Object value = dataMap.get(variable);
                ExcelContentProperty excelContentProperty = ClassUtils.declaredExcelContentProperty(dataMap, writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadClazz(), variable);
                cellWriteHandlerContext.setOriginalValue(value);
                cellWriteHandlerContext.setOriginalFieldClass(FieldUtils.getFieldClass(dataMap, variable, value));
                cellWriteHandlerContext.setExcelContentProperty(excelContentProperty);
                cellWriteHandlerContext.setTargetCellDataType(CellDataTypeEnum.STRING);
                WriteCellData<?> cellData = convert(cellWriteHandlerContext);
                cellDataList.add(cellData);
                CellDataTypeEnum type = cellData.getType();
                if (type != null) {
                    switch(type) {
                        case STRING:
                            cellValueBuild.append(cellData.getStringValue());
                            break;
                        case BOOLEAN:
                            cellValueBuild.append(cellData.getBooleanValue());
                            break;
                        case NUMBER:
                            cellValueBuild.append(cellData.getNumberValue());
                            break;
                        default:
                            break;
                    }
                }
            }
            cellValueBuild.append(analysisCell.getPrepareDataList().get(index));
            cell.setCellValue(cellValueBuild.toString());
            cellWriteHandlerContext.setCellDataList(cellDataList);
            if (CollectionUtils.isNotEmpty(cellDataList)) {
                cellWriteHandlerContext.setFirstCellData(cellDataList.get(0));
            }
            // Restyle
            if (fillConfig.getAutoStyle()) {
                Optional.ofNullable(collectionFieldStyleCache.get(currentUniqueDataFlag)).map(collectionFieldStyleMap -> collectionFieldStyleMap.get(analysisCell)).ifPresent(cell::setCellStyle);
            }
        }
        WriteHandlerUtils.afterCellDispose(cellWriteHandlerContext);
    }
    // In the case of the fill line may be called many times
    if (rowWriteHandlerContext.getRow() != null) {
        WriteHandlerUtils.afterRowDispose(rowWriteHandlerContext);
    }
}