Exporting Picture Object to Excel

Magneto

Does anybody have any example on exporting a picture object from a report to Excel? For example, CompanyInformation.Picture?

garak

the picture is stored in your BLOB field. so you must export the blob to a directory and in EXCEL import this File.

You know how you can open an excel Worksheet with excel automation:?:
If not search the forum for this.

Here an example to add a picture into word.

calcfields(YourBLOBField);
IF YourBLOBField.HASVALUE THEN BEGIN
  YourBLOBField.EXPORT(FilePathAndName); //or your fileextension
   xlSheet.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);
END; 

Regards

Magneto

garak wrote:

You know how you can open an excel Worksheet with excel automation:?:
If not search the forum for this.

I've just been using table Excel Buf to create an Excel file off reports.

calcfields(YourBLOBField);
IF YourBLOBField.HASVALUE THEN BEGIN
YourBLOBField.EXPORT(FilePathAndName); //or your fileextension
xlSheet.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);
END;

I'm assuming the xlSheet is an automation? What specific automation is this? I don't have much experience on this specific automation but I know what they are, and I know how to use automation. Thanks for your help and have a great weekend.

kriki

[Topic moved from Upcoming version NAV forum to Navision forum]

Magneto

Thanks.

Xypher

Utilizing the MSXML automation yourself may save you time and effort in regards to this situation. Although I am not sure if you can import an image (Object) to Excel directly via Stream (I will look into this) but you can do so with the code provided by garak.

(If you do decide to use the automation check out my post 'Export to Excel with Performance' on creating faster Excel exports if you have a fair amount of data you wish to export (and are displeased with the time it takes to generate the document using Excel Buffer or anything else.))

Magneto

Xypher wrote:

Utilizing the MSXML automation yourself may save you time and effort in regards to this situation. Although I am not sure if you can import an image (Object) to Excel directly via Stream (I will look into this) but you can do so with the code provided by garak.

(If you do decide to use the automation check out my post 'Export to Excel with Performance' on creating faster Excel exports if you have a fair amount of data you wish to export (and are displeased with the time it takes to generate the document using Excel Buffer or anything else.))

I haven't gotten the chance to play with your coding as I'm in a time crunch... I do have some specific questions:

What specific automation is this? How do I relate it to the excel file I'm building with the Excel Buffer table? At this point, I'm still building the rows & columns. I still haven't issued the createbook function.

Xypher

If you are still at the beginning stages of planning the export I would suggest taking a brief look at my code.

The MSXML automation you will find is labeled 'Microsoft XML, v#.0' where # (the version) is entirely dependent on which Microsoft Office or MSXML "Core Services" you have installed. (You can easily download/install the latest version of MSXML free of charge whether or not you have Office installed.)

The two main issues I foresee you possibly running into is:

A) Primarily not being able to import your image to the Excel document you are creating through 'Excel Buffer' (Contains no function allowing Image importation) The time in which it takes for Excel Buffer to construct your Excel document (since it makes references Cell-By-Cell and slows the process down greatly)

Magneto

I'm actually trying to put a document with a logo (i.e., Sales Order Confirmation) to Excel. I know. it does not make sense but my customer just loves Excel. I'll have to play with your code tonight and see where that leads me. I can't study it right here at the customer site. If you have any more tips, please let me know. Thanks.

Xypher

In your case, since the Excel document is most likely going to be rather small, you could benefit from making an additional function in Excel Buffer (if you have the access)

ajhvdb

The code "garak" provided should work for you. We use the same in a solution we created. Export the blob to your temp folder and add the shape in excel.

An other approach is; Use a template excel sheet in which the logo is already inserted. Open this, put your data in and save as ...

Magneto

ajhvdb wrote:

The code "garak" provided should work for you. We use the same in a solution we created. Export the blob to your temp folder and add the shape in excel.

An other approach is; Use a template excel sheet in which the logo is already inserted. Open this, put your data in and save as ...

I got the blob stored in a temporary folder. However, I'm missing one more step. This might sound like a really dumb question... how do you add the shape in Excel via code in Navision? :oops:

Xypher

Is "garak"'s code not working for you?

Specifically,

xlSheet.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);

santoshmkcet

now the situation has arrived at a particular juncture where you can niether swallow nor spit as we all want an optimal solution that is to be "desired".

Savatage

santoshmkcet wrote:

now the situation has arrived at a particular juncture where you can niether swallow nor spit as we all want an optimal solution that is to be "desired".

:shock:

garak

xlSheet.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);

xlSheet ist the Automation of Excel Worksheet.

for an better help, let show us your code.

Xypher

This is true garak, that line of code is for the automation.

I can only assume he has attempted to move on to using the automation since 'Excel Buffer' does not give you this freedom.

garak

And where is there the Problem with "Excel Buffer" :?:

The Function CreateSheet() in "Excel Buffer" table generates the Shape.
So where is the Problem ? New Function in this Table Like

SetPathToPicture(PicturePath:Text[1024])

Here we store in a global variable the Path

FilePathAndName := PicturePath;

in function CreateSheet() we check the Value of the variable FilePathAndName and check if folder and file exist. if both is true, we import the Picture in our shape.

CreateSheet()
....
....

//New Code +++++++
if (FilePathAndName <> '') and (Exists(FilePathAndName)) then
  XlWrkSht.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);
//New Code ------------

.....
.....

regards

Magneto

I've been so busy but now that things are slowing down, I should be able to get back to this one. Thank you so much.

Djou2424

I'm bringing up an old post.

I'd like to be able to export a blob to excel using the excel buffer table.
I read the solution proposed by garak but I think I'm missing something.

Let's assume that I want to export X items from the inventory to excel.
Each item has a custom field to store the blob for the image.
I would like to have the image in row 1, column 1, and then the no. of the item just under it, row 2, column 1.
I then skip 2 lines, and I do the same with item #2, and so on, until I reached item #X.

The way I understand the excel buffer table is that you enter a record for each value you want to send to excel, and at the end, you call the createsheet function to create the worksheet. Creating a new function to set the path of the image isn't working as I have multiple blob to export to excel.

Is there another way to do it?