Read Apache POI XSSFWorkbook in Aspose cells

Question

I'm trying to create excel file with watermark in JAVA.

I'm using below approach: 1. using Apache POI api to create excel workbook 2. consume the poi workbook in aspose cells api for adding watermar.

When i'm trying to consume POI workbook in aspose cell i'm getting error- workbook not expected. Please help, as i'm new to spring/JAVA

PFB my code:

package com.mudassir.exceltest.testExcel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
public class TestExcelApplication {

    private static String[] COLUMNs = {"Id", "Name", "Address", "Age"};
    private static List<Customer> customers = Arrays.asList(
            new Customer("1", "Jack Smith", "Massachusetts", 23),
            new Customer("2", "Adam Johnson", "New York", 27),
            new Customer("3", "Katherin Carter", "Washington DC", 26),
            new Customer("4", "Jack London", "Nevada", 33), 
            new Customer("5", "Jason Bourne", "California", 36));

    public static void main(String[] args) throws IOException {

        Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();

        Sheet sheet = workbook.createSheet("Customers");

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLUE.getIndex());

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);

        // Row for Header
        Row headerRow = sheet.createRow(0);

        // Header
        for (int col = 0; col < COLUMNs.length; col++) {
            if(col== 0){
            Cell cell = headerRow.createCell(col);
            cell.setCellValue(COLUMNs[col]);
            cell.setCellStyle(headerCellStyle);
            }
            else{
                Cell cell = headerRow.createCell(col+1);
                cell.setCellValue(COLUMNs[col]);
                cell.setCellStyle(headerCellStyle);
            }

        }

        // CellStyle for Age
        CellStyle ageCellStyle = workbook.createCellStyle();
        ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));

        int rowIdx = 1;
        for (Customer customer : customers) {
            Row row = sheet.createRow(rowIdx++);

            row.createCell(0).setCellValue(customer.getId());
            row.createCell(2).setCellValue(customer.getName());
            row.createCell(3).setCellValue(customer.getAddress());

            Cell ageCell = row.createCell(4);
            ageCell.setCellValue(customer.getAge());
            ageCell.setCellStyle(ageCellStyle);
        }

        // read the image to the stream
        final FileInputStream stream = new FileInputStream("image.png");
        final CreationHelper helper = workbook.getCreationHelper();
        final Drawing drawing = sheet.createDrawingPatriarch();

        final ClientAnchor anchor = helper.createClientAnchor();
        //anchor.setAnchorType( ClientAnchor.MOVE_AND_RESIZE );


        final int pictureIndex =
                workbook.addPicture(IOUtils.toByteArray(stream), Workbook.PICTURE_TYPE_PNG);


        anchor.setCol1( 0 );
        anchor.setRow1( 9 ); // same row is okay
        anchor.setRow2( 11 );
        anchor.setCol2( 2 );
        final Picture pict = drawing.createPicture( anchor, pictureIndex );
        //pict.resize();

        Header header = sheet.getHeader();
        header.setCenter("&[pict]");
        header.setLeft("Left First Page Header");
        header.setRight("Right First Page Header");

        sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
        sheet.addMergedRegion(new CellRangeAddress(2,2,0,1));
        sheet.addMergedRegion(new CellRangeAddress(3,3,0,1));
        sheet.addMergedRegion(new CellRangeAddress(4,4,0,1));

        com.aspose.cells.Workbook workbook1=new com.aspose.cells.Workbook(workbook);


        FileOutputStream fileOut = new FileOutputStream("customerstest.xlsx");
        workbook.write(fileOut);
        fileOut.close();
        workbook.close();
    }
}

Please assist me on how can i consume POI workbook in aspose cell workbook.

Below code statement is not working, rather throwing type mismatch error:

com.aspose.cells.Workbook workbook1=new com.aspose.cells.Workbook(workbook);

Thanks

Amjad Sahi · Answer 1 · 2018-07-06T08:08:29.477

0

Well, Aspose.Cells and POI XSSF are different APIs with diverse architectures, both have different objects and attributes. I am not sure one can easily parse one's object in other APIs or may be he cannot do that. Aspose.Cells will read and parse valid Excel workbooks (which should follow MS Excel standards and specifications).

I think you may try to save your workbook to Excel file using POI XSSF APIs and then use Aspose.Cells to read that file. If the output file (by POI XSSF) follows MS Excel standards and specifications then it should be opened fine into MS Excel too. If it is opened fine into MS Excel then surely Aspose.Cells should also load the file fine. If you find any issue where Aspose.Cells could not read the final file, then it is an issue with Aspose.Cells. Otherwise I do not think it is an issue with Aspose.Cells. In short, you can simply save the Excel Workbook (by POI XSSF) to disk (Excel file) or streams first then use Aspose.Cells APIs to load it from disk or streams, it should work fine.

I am working as Support developer/ Evangelist at Aspose.

edited Jul 06 '18 at 08:08

answered Jul 05 '18 at 18:16

Amjad Sahi

1,813
1
10
15

1

He [wants adding watermark in Excel workbook](https://stackoverflow.com/questions/51077404/apache-poi-adding-watermark-in-excel-workbook). I've told him already that Excel doesn’t come with a built-in watermark feature. And I have shown how to add picture in header to simulate watermark. `Apache poi` code for this needs using very low level classes and methods until now. Now he might think that Aspose.Cells provides some kind of "magic" for fulfilling the watermark requirement. But then he also should using Aspose Cells for creating the content, shouldn't he? – Axel Richter Jul 06 '18 at 05:32
@AxelRichter, you are right. Aspose.Cells does support adding picture in the header. So if you "Shaikh Mudassir" want to use Aspose.Cells API, you can use the sample code in this link --- ( https://docs.aspose.com/display/cellsjava/Page+Setup+Features#PageSetupFeatures-InsertaGraphicintoaHeaderorFooter ). But in this case, you will also be using Aspose.Cells for creating the content. Please note, if you want to use Aspose.Cells API, you do not need to use Apache POI XSSF at all. ... – shakeel Jul 09 '18 at 14:32
You can create all of your content using Aspose.Cells API. Thank you. ---- Note: I am working as Developer Advocate at Aspose. – shakeel Jul 09 '18 at 14:32

Read Apache POI XSSFWorkbook in Aspose cells

1 Answers1