Attach txt file inside excel sheet

Aspose.Cells Product Family
1

Hi,


I have one requirement for:-
Attach text file into Excel sheet by java program. After clicked the text file the contents will be displayed.
I got the code for attaching image in exel sheet but could you please provide me the solution for my requirement.

Regards,
RSY Narayan
2

Hi RSY Narayan,

Thank you for considering Aspose products.

I think you mean to embed a TXT file in an Excel spreadsheet, so that when the icon of OleObject is clicked, the contents of text file should be displayed in this native application. Please have a look at the below provided source code snippet that achieves the same as discussed above. Please also check the attached output file for your reference.

Java


//Get the image file.
File file = new File(myDir + “txt_icon.gif”);
//Get the picture into the streams.
byte[] img = new byte[(int)file.length()];
FileInputStream fis = new FileInputStream(file);
fis.read(img);

//Get the txt file into the streams.
file = new File(myDir + “txt\a.txt”);
byte[] data = new byte[(int)file.length()];
fis = new FileInputStream(file);
fis.read(data);

//Instantiate a new Workbook.
Workbook wb = new Workbook();
//Get the first worksheet.
Worksheet sheet = wb.getWorksheets().get(0);

//Add an Ole object into the worksheet with the image
//shown in MS Excel.
int oleObjIndex = sheet.getOleObjects().add(0, 0, 20, 20, img);
OleObject oleObj = sheet.getOleObjects().get(oleObjIndex);

//Set embedded ole object data.
oleObj.setObjectData(data);
oleObj.setNativeSourceFullName(“a.txt”);

//Save the excel file
wb.save(myDir + “output.xlsx”, SaveFormat.XLSX);
3

Hi,

Thanks for quick response.

I have tested your code and it is working fine properly and also I have done formatting of cells, which is working fine. But currently, I facing the problem for BackGround Color of cell. As per aspose.cell standard I used methods for background color but it is not working.

I am attaching the java code and output of excel. I am also attaching expected output excel sheet.

1) You will get "InsertingOleObject.java’ file inside zip file “InsertingOleObject.zip”.
2) book1.xls file is current output file after run the java file.
3) I need the output as per excel sheet “Expected_Output.xls”.

Could you please check and tell me how it will work?

Regards,
RSY Narayan

4

Hi,


Well, you need to use Style.setForegroundColor() method instead with BackgroundType as SOLID pattern. Please see the updated code segment that works fine.

e.g

Sample code:

// First Header Row
if(i == 0){
System.out.println(“Header columns”);
cell = cells.get(i,0);
cell.putValue(“Report ID”);
Style style1 = cell.getStyle();
style1.setHorizontalAlignment(TextAlignmentType.CENTER);
style1.setVerticalAlignment(TextAlignmentType.CENTER);
style1.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style1.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style1.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style1.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style1.setForegroundColor(Color.getBlack());
style1.setPattern(BackgroundType.SOLID);
cell.setStyle(style1);
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
// Program ID
cell = cells.get(i,1);
cell.putValue(“Program ID”);
Style style2 = cell.getStyle();
style2.setHorizontalAlignment(TextAlignmentType.CENTER);
style2.setVerticalAlignment(TextAlignmentType.CENTER);
//Setting the line of the top border
style2.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style2.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style2.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style2.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style2.setForegroundColor(Color.getYellow());
style2.setPattern(BackgroundType.SOLID);

cell.setStyle(style2);
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
// Report Title
cell = cells.get(i,2);
cell.putValue(“Report Title”);
Style style3 = cell.getStyle();
style3.setHorizontalAlignment(TextAlignmentType.CENTER);
style3.setVerticalAlignment(TextAlignmentType.CENTER);
//Setting the line of the top border
style3.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style3.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style3.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style3.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style3.setForegroundColor(Color.getYellow());
style3.setPattern(BackgroundType.SOLID);
cell.setStyle(style3);;
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
// From Date
cell = cells.get(i,3);
cell.putValue(“From Date”);
Style style4 = cell.getStyle();
style4.setHorizontalAlignment(TextAlignmentType.CENTER);
style4.setVerticalAlignment(TextAlignmentType.CENTER);
//Setting the line of the top border
style4.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style4.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style4.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style4.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style4.setForegroundColor(Color.getYellow());
style4.setPattern(BackgroundType.SOLID);

cell.setStyle(style4);
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
// Report Format
cell = cells.get(i,4);
cell.putValue(“Report Format”);
Style style5 = cell.getStyle();
style5.setHorizontalAlignment(TextAlignmentType.CENTER);
style5.setVerticalAlignment(TextAlignmentType.CENTER);
//Setting the line of the top border
style5.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style5.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style5.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style5.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style5.setForegroundColor(Color.getYellow());
style5.setPattern(BackgroundType.SOLID);

cell.setStyle(style5);
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
// Report Document
cell = cells.get(i,5);
cell.putValue(“Report Document”);
Style style6 = cell.getStyle();
style6.setHorizontalAlignment(TextAlignmentType.CENTER);
style6.setVerticalAlignment(TextAlignmentType.CENTER);
//Setting the line of the top border
style6.setBorder(BorderType.TOP_BORDER,CellBorderType.THIN,Color.getBlack());
style6.setBorder(BorderType.BOTTOM_BORDER,CellBorderType.THIN,Color.getBlack());
style6.setBorder(BorderType.LEFT_BORDER,CellBorderType.THIN,Color.getBlack());
style6.setBorder(BorderType.RIGHT_BORDER,CellBorderType.THIN,Color.getBlack());
style6.setForegroundColor(Color.getYellow());
style6.setPattern(BackgroundType.SOLID);

cell.setStyle(style6);
font = cell.characters(0, cell.getStringValue().length()).getFont();
font.setBold(true);
font.setColor(Color.getBlue());
…

//…
5

Hi Amjad,

Thanks for quick response.
As per your suggestion, I have done the same way and it is working properly.

Regards,
RSY Narayan

6

Hi,


Good to know that your issue is resolved now. Feel free to contact us any time if you need further help if you have any other queries or issue, we will glad to help you soon.

Thank you.