Embed PDF document into MS-Excel file using Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi ,
is it possible to add Pdf documnet(Byte[]) in excel using aspose Java ?
we should only show pdf icon initially, when user opens the excel, and click the icon we should able to open corresponding pdf.

Please let us know is this feature support by Aspose Java? can you also share some sample code…if it works well we would like to use this product for our projects

2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

It is possible to embed pdf document into MS-Excel file using Aspose.Cells for Java. Please see the following code. It embeds pdf into MS-Excel document. We have attached the source and output files for your reference.

Java

Workbook workbook = new Workbook();


//Get the first worksheet.

Worksheet sheet = workbook.getWorksheets().get(0);

//Define a string variable to store the image path.

String imgFile = “F:\Shak-Data-RW\Downloads\pdf-icon.jpg”;


File file = new File(imgFile);

byte[] binaryImg = new byte[(int)file.length()];

FileInputStream fis = new FileInputStream(file);

fis.read(binaryImg);


String pdfFile = “F:\Shak-Data-RW\Downloads\source.pdf”;

file = new File(pdfFile);

byte[] binaryPdf = new byte[(int)file.length()];

fis = new FileInputStream(file);

fis.read(binaryPdf);


sheet.getOleObjects().add(0, 0, 40, 40, binaryImg);

//Set embedded ole object data.

sheet.getOleObjects().get(0).setObjectData(binaryPdf);

sheet.getOleObjects().get(0).setFileType(OleFileType.PDF);


//Save the excel file

workbook.save(“F:\Shak-Data-RW\Downloads\output.xls”);

Please also see the following documentation article for your reference.

Managing OLE Objects

3

Thank you mshakeel.faiz

1) This code is working fine to attache Pdf in to excel cell, but when i double click on the pdf icon the pdf image opening in excel it self? can you guide me how to do that.

2) suppose when we are reading bunch of values from DB…how we can avoid hard coding the cell Nums?

//Adding a string value to the cell
Cell cell = cells.get(“A1”);
cell.setValue(“Hello World”);

3) Also my requirement is the row cell values mixed with text and number and attachments…I am trying with following code…is this correct way?

import java.io.File;
import java.io.FileInputStream;
import java.util.Calendar;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.OleFileType;
import com.aspose.cells.Style;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;


public class AddCellData {

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

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

//Adding a string value to the cell
Cell cell = cells.get(“A1”);
cell.setValue(“Hello World”);


//Adding a string value to the cell
// cell = cells.get(“B1”);

String imgFile = “C:\Project_LIBS\Aspose\Test\pdf.png”;
File file = new File(imgFile);

byte[] binaryImg = new byte[(int)file.length()];
FileInputStream fis = new FileInputStream(file);
fis.read(binaryImg);

String pdfFile = “C:\Project_LIBS\Aspose\Test\1.pdf”;
file = new File(pdfFile);
byte[] binaryPdf = new byte[(int)file.length()];
fis = new FileInputStream(file);
fis.read(binaryPdf);

worksheet.getOleObjects().add(0, 1, 20, 20, binaryImg);
worksheet.getOleObjects().get(0).setObjectData(binaryPdf);
worksheet.getOleObjects().get(0).setFileType(OleFileType.PDF);
fis.close();

//Adding a string value to the cell
cell = cells.get(“C1”);
cell.setValue(100);


//Saving the Excel file
workbook.save(“C:\output.xls”);

}

}

4

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Well, the attached pdf is embedded inside the xls document. When you will double click it, it will open in MS-Excel and also in Adobe Acrobat.

You can access the cells using rows and columns indices and also by cell names. Please see the following documentation articles for your reference.
Accessing Cells of a Worksheet
Adding Data to Cells

Your code looks fine. I have checked your attached output.xls file, it shows text in the cells and also the pdf icon which opens fine when it is double clicked.

If you have any questions, please feel free to ask. We will look into your requirements and help you asap.

5

Hi mshakeel.faiz, Thanks for your quick reply.


when user double click on the embedded pdf , Just I want open in Adobe Acrobat ,it should not open in excel. Can you please tell how can we do this.

The other question is how will close opened pdf(image) in MS-Excel?
6

Hi,

Thanks for your posting and using Aspose.Cells for Java.

I think, it is not possible. Whenever you will double click it, it will open the PDF in MS-Excel and Adobe. Could you create such a file manually using MS-Excel where it opens the PDF in Adobe only and provide it to us. We will look into your issue and advise you asap.

The opened image cannot be closed. MS-Excel will create a new image on double clicking and update the older image with the new one.

7

Hi ,


Thank you for your quick reply.
Manually I have tried by attaching the pdf file into excel…when i did double click on the excel pdf icon it’s not opening any pdf image in the excel…It’s opening only outside withe adobe.

.it’s one of the major requirement to our project…can you Please help on this .
8

Hi,

Thanks for your posting and using Aspose.Cells.

We have looked into this issue and found you need to add the following line to display pdf as icon and when you will double click it, it will open the pdf outside the xls file into Adobe. We have also attached the output xls file for your reference.

sheet.getOleObjects().get(0).setDisplayAsIcon(true);

Java


Workbook workbook = new Workbook();


//Get the first worksheet.

Worksheet sheet = workbook.getWorksheets().get(0);

//Define a string variable to store the image path.

String imgFile = “F:\Shak-Data-RW\Downloads\pdf-icon.jpg”;


File file = new File(imgFile);

byte[] binaryImg = new byte[(int)file.length()];

FileInputStream fis = new FileInputStream(file);

fis.read(binaryImg);


String pdfFile = “F:\Shak-Data-RW\Downloads\source.pdf”;

file = new File(pdfFile);

byte[] binaryPdf = new byte[(int)file.length()];

fis = new FileInputStream(file);

fis.read(binaryPdf);


sheet.getOleObjects().add(0, 0, 40, 40, binaryImg);

//Set embedded ole object data.

sheet.getOleObjects().get(0).setObjectData(binaryPdf);

sheet.getOleObjects().get(0).setFileType(OleFileType.PDF);

sheet.getOleObjects().get(0).setDisplayAsIcon(true);


//Save the excel file

workbook.save(“F:\Shak-Data-RW\Downloads\output.xls”);



9

Hi Thanks for your quick reply.

Now it’s working perfectly…Pdf is opening outside…

But seeing some issue when i am attaching xlsx …Here is the code…can you Please help us to fix this.
Also can you tell how we have to apply licence to aspose cell…Thank you.


import java.io.File;
import java.io.FileInputStream;

import com.aspose.cells.OleFileType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;


public class CellPdf {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();


//Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().get(0);

//Define a string variable to store the image path.
String imgFile = “C:\Project_LIBS\Aspose\Test\pdf.png”;


File file = new File(imgFile);
byte[] binaryImg = new byte[(int)file.length()];
FileInputStream fis = new FileInputStream(file);
fis.read(binaryImg);


String pdfFile = “C:\Project_LIBS\Aspose\Test\1.pdf”;
file = new File(pdfFile);
byte[] binaryPdf = new byte[(int)file.length()];
fis = new FileInputStream(file);
fis.read(binaryPdf);


sheet.getOleObjects().add(0, 0, 40, 40, binaryImg);

//Set embedded ole object data.
sheet.getOleObjects().get(0).setObjectData(binaryPdf);
sheet.getOleObjects().get(0).setFileType(OleFileType.PDF);
sheet.getOleObjects().get(0).setDisplayAsIcon(true);
String excelimgFile = “C:\Project_LIBS\Aspose\Test\excelIcon.jpeg”;
file = new File(excelimgFile);
binaryImg = new byte[(int)file.length()];
fis = new FileInputStream(file);
fis.read(binaryImg);


String excelFile = “C:\Project_LIBS\Aspose\Test\Test.xlsx”;
file = new File(excelFile);
byte[] binaryxls = new byte[(int)file.length()];
fis = new FileInputStream(file);
fis.read(binaryxls);


sheet.getOleObjects().add(0, 5, 40, 40, binaryImg);

//Set embedded ole object data.
sheet.getOleObjects().get(1).setObjectData(binaryxls);
sheet.getOleObjects().get(1).setFileType(OleFileType.XLSX);
sheet.getOleObjects().get(1).setDisplayAsIcon(true);


//Save the excel file
workbook.save(“C:\output3.xlsx”);

}
}
10

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Please use the following code to embed xlsx file as an Ole object into an Excel spreadsheet. We have also attached the output file for your reference.

For applying license, please see the following documentation article for your reference.

Licensing

Java

String imgFile =“F:\Shak-Data-RW\Downloads\excel-icon.JPG”;

String xlsxFile =“F:/Shak-Data-RW/Downloads/source.xlsx”;



//We need the byte[] byte array for both image and xlsx file to continue

File file = new File(imgFile);

byte[] binaryImg = new byte[(int)file.length()];

FileInputStream fis = new FileInputStream(file);

fis.read(binaryImg);


file = new File(xlsxFile);

byte[] binaryXlsx = new byte[(int)file.length()];

fis = new FileInputStream(file);

fis.read(binaryXlsx);



//Insert OLE object into a workbook

Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


int idxOle = worksheet.getOleObjects().add(0,0,40,40, binaryImg);


OleObject objOle = worksheet.getOleObjects().get(idxOle);


objOle.setObjectData(binaryXlsx);

objOle.setProgID(“Excel”);

objOle.setSourceFullName(“source.xlsx”);

objOle.setDisplayAsIcon(true);


workbook.save(“F:/Shak-Data-RW/Downloads/output.xlsx”, SaveFormat.XLSX);