Re: embed multiple files in excel using Aspose.Cells

Hi Ramna,


Thank you for contacting Aspose support.

We would request you to please give a try to the latest version of Aspose.Cells for .NET 8.1.0.2 in your original project. In case the problem persists, please provide us an executable sample application replicating the problem along with your template files (if any).

Hi,

Thanks for the reply. I’ve already been using 8.1.0.2 version. Anyhow i tried the dlls you attached but its of no use. I’m attaching a sample application based on my requirement. Please provide me solution.

Hi Ramna,


Thank you for providing your sample application. We are currently looking into it to provide a solution or report the behavior as a bug. We will shortly get back to you with more updates in this regard.

Hi Ramna,


Thank you for your patience.

We have identified a possible problem in your shared project, as it seems you are overwriting the OleObject’s data for every file type. Your existing code looks like as provided below,

C#

//Set embedded ole object data.
sheetReport.OleObjects[0].ObjectData = objectData;
sheetReport.OleObjects[0].DisplayAsIcon = true;
// sheetReport.OleObjects[r].HasLine = false;
if (Path.GetExtension(path) == “.pdf”)
{
sheetReport.OleObjects[0].FileFormatType = FileFormatType.Pdf;
}
else if (Path.GetExtension(path) == “.docx”)
{
sheetReport.OleObjects[0].FileFormatType = FileFormatType.Docx;
}
else if (Path.GetExtension(path) == “.pptx”)
{
sheetReport.OleObjects[0].FileFormatType = FileFormatType.Pptx;
}

However, changing it to as follow generates a correct spreadsheet with multiple embeded objects in a row,

C#
//Set embedded ole object data. sheetReport.OleObjects[i].ObjectData = objectData; sheetReport.OleObjects[i].DisplayAsIcon = true; if (Path.GetExtension(path) == ".pdf") { sheetReport.OleObjects[i].FileFormatType = FileFormatType.Pdf; } else if (Path.GetExtension(path) == ".docx") { sheetReport.OleObjects[i].FileFormatType = FileFormatType.Docx; } else if (Path.GetExtension(path) == ".pptx") { sheetReport.OleObjects[i].FileFormatType = FileFormatType.Pptx; }

Could you please confirm, is this the problem you are facing on your side?

No, I need the three files to be embedded in an a single cell or a single row. What i have given to u guys is a sample where i hard coded it. If the ‘i’ in sheetReport.OleObjects[i].Objectdata represents the row number, then aspose.cells is able to embed only a single file in a row…This is as per my understanding…Please correct me if i’m wrong.

Hi Ramna,


The Aspose.Cells APIs actually embeds the object irrelevant of cells. However, the API requires the location of upper left row & column just to tell where to post the picture of embedded object. Please check the attached spreadsheet that we have generated on our end while using your code with changes suggested in our previous response. You will observe the two embedded objects are aligned horizontally, giving a view as they are in the same row. Hopefully you want similar results.

yeah that helps, Could you please post the code for it.

Hi Ramna,


The code is pretty much the same as of your project with little modifications as discussed here. Please check the below code for your reference.

C#

var wb = new Workbook();
var sheetReport = wb.Worksheets[0];
sheetReport.Name = “Report”;
Cells cells = sheetReport.Cells;
string imageUrl=string.Empty;
var paths = (ConfigurationManager.GetSection(“Paths”) as NameValueCollection);

for (int i = 0; i < paths.AllKeys.Count()-1;i++ )
{
string path = paths[i];
if (Path.GetExtension(path) == “.pdf”)
{
imageUrl =
Path.Combine(
Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
.Replace(@“bin\Debug”, “”), @“Images\pdficon.jpg”);
}
else if (Path.GetExtension(path) == “.docx”)
{
imageUrl =
Path.Combine(
Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
.Replace(@“bin\Debug”, “”), @“Images\wordicon.jpg”);
}
else if (Path.GetExtension(path) == “.pptx” || Path.GetExtension(path) == “.ppt”)
{
imageUrl =
Path.Combine(
Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
.Replace(@“bin\Debug”, “”), @“Images\ppticon.jpg”);
}
//Get the picture into the streams.
FileStream fs = File.OpenRead(imageUrl);
//Define a byte array.
byte[] imageData = new Byte[fs.Length];
//Obtain the picture into the array of bytes from streams.
fs.Read(imageData, 0, imageData.Length);
//Close the stream.
fs.Close();
fs = File.OpenRead(path);
//Define an array of bytes.
byte[] objectData = new Byte[fs.Length];
//Store the file from streams.
fs.Read(objectData, 0, objectData.Length);
//Close the stream.
fs.Close();

sheetReport.Cells.InsertColumn(0);
sheetReport.OleObjects.Add(0, 0, 25, 25, imageData);
//Set embedded ole object data.
sheetReport.OleObjects[i].ObjectData = objectData;
sheetReport.OleObjects[i].DisplayAsIcon = true;
// sheetReport.OleObjects[r].HasLine = false;
if (Path.GetExtension(path) == “.pdf”)
{
sheetReport.OleObjects[i].FileFormatType = FileFormatType.Pdf;
}
else if (Path.GetExtension(path) == “.docx”)
{
sheetReport.OleObjects[i].FileFormatType = FileFormatType.Docx;
}
else if (Path.GetExtension(path) == “.pptx”)
{
sheetReport.OleObjects[i].FileFormatType = FileFormatType.Pptx;
}
}
wb.Save(@“Sample.xlsx”, SaveFormat.Xlsx);


Please feel free to write back in case you need our further assistance.

Hi Babar,

Thanks for the clarification. But the issue is that when i loop the code you mentioned for each row, it throws an an error at Save “Buffer cannot be null. Parameter name: buffer”. Can you provide code to loop it for each row.

Hi Ramna,


Thank you for bringing this issue to our knowledge. You are right about the exception while adding OleObjects to consecutive rows. We are currently looking into this matter to suggest an appropriate solution, otherwise, we will log the bug in our database for further investigation.

Hi Ramna,


Thank you for your patience. We have tried on our end to get rid of the said exception while adding multiple rows of OleObjects to the spreadsheet. Unfortunately, we couldn’t get it to work therefore we have logged an investigative ticket in our bug tracking system, and requested the core development team to investigate the matter. The ticket Id for your future reference is CELLSNET-42778. Please spare us little time to properly analyze the problem cause. In the meanwhile, we will keep you posted with updates in this regard.

PS. Attached to this post is the simplified code, that we tried on our end to get this scenario to work without observing an exception.

We are sorry for the inconvenience caused to you.

Hi,

Thanks for using Aspose.Cells.

We have evaluated your issue further. Please change your code as the following:

C#


static void insertObjectsInOneRow(NameValueCollection paths, int row, int column)

{

for (int i = 0; i < paths.AllKeys.Count(); i++)

{

string path = paths[i];

string imageUrl = getFileType(path);


int index = sheet.OleObjects.Add(row, column++, 25, 25, getImageData(imageUrl));

//Set embedded ole object data.

sheet.OleObjects[index].ObjectData = getFileData(path);

sheet.OleObjects[index].DisplayAsIcon = true;

if (Path.GetExtension(path) == “.pdf”)

{

sheet.OleObjects[index].FileFormatType = FileFormatType.Pdf;

}

else if (Path.GetExtension(path) == “.docx”)

{

sheet.OleObjects[index].FileFormatType = FileFormatType.Docx;

}

else if (Path.GetExtension(path) == “.pptx”)

{

sheet.OleObjects[index].FileFormatType = FileFormatType.Pptx;

}

}

}