Save Excel cell range as image with activeX

Hi! I have a nice formatted table in excel and would like to save this table (meaning a cell range) as jpg file using activeX with Matlab. Is that possible?

I do already have the following code

exl = actxserver('excel.application');
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open('myFile.xlsx');
exlSheet1 = exlFile.Sheets.Item('Sheet1');
dat_range = 'A1:F19';
rngObj = exlSheet1.Range(dat_range);

Now I would nee something like rngObj.export('myExport.jpg') but I couldn't find anything....

[Fangjun Jiang]

consider copying to clipboard

Answer 1

[Guillaume]

With activex, you can do anything you can do with excel directly (as long as excel exposes it programmatically). So if you know how to do that in excel, we can find which function to call. If it can't be done in excel, then you can't do it with matlab (through activeX at least).

I don't know of any way to export bits of excel as images but it's not something I've ever looked at. Saying that, a quick search finds ExportAsFixedFormat. It can only export to pdf or xpf, the code would be:

rngObj.ExportAsFixedFormat('xlTypePDF', fullfile(somefolder, 'somename.pdf'));

edit: As suggested by Fangjun there's the Range.CopyPicture method. This puts a picture of the range on the clipboard, which is not easy to retrieve in matlab. In particular, matlab clipboard function is unable to retrieve it. However, since you're using activeX, you also have access to .Net, so:

rngObj.CopyPicture(1, 2);  %copy range to clipboard as bitmap
%the first 1 could be 2. Not sure what the difference is between xlPrinter and xlScreen
%the 2 has to be 2 to get a bitmap and not a metafile
NET.addAssembly('System.Windows.Forms');  %to be done only once per matlab session.
assert(System.Windows.Forms.Clipboard.ContainsImage, 'Clipboard does not contain an image. Cannot proceed')
bitmap = System.Windows.Forms.Clipboard.GetImage;
bitmap.Save(fullfile(somefolder, 'somename.png'));  %can also use bmp or jpg as extension

If somehow, you wanted to retrieve the image from the clipboard directly in matlab, then it's a fairly complicated process. Fortunately, I recently had to implement some code to import a .Net bitmap into matlab, so I know how to do that:

%this bit same as before
NET.addAssembly('System.Windows.Forms');  %to be done only once per matlab session.
assert(System.Windows.Forms.Clipboard.ContainsImage, 'Clipboard does not contain an image. Cannot proceed')
bitmap = System.Windows.Forms.Clipboard.GetImage;
%get content of .Net bitmap in matlab:
bitmapdata = bitmap.LockBits(System.Drawing.Rectangle(0, 0, bitmap.Width, bitmap.Height), System.Drawing.Imaging.ImageLockMode.ReadOnly, System.Drawing.Imaging.PixelFormat.Format24bppRgb);  %lock the bitmap so we can get the bits. For simplicity lock as 24bpprgb
buffer = NET.createArray('System.Byte', bitmapdata.Stride * bitmapdata.Height);   %create array to copy the bits into. Assume that Stride is positive (should be when 24bpp)
System.Runtime.InteropServices.Marshal.Copy(bitmapdata.Scan0, buffer, 0, buffer.Length);  %use interop to copy the bits
bitmap.UnlockBits(bitmapdata);  %release the bitmap bits
%now shuffle the bits to match matlab ordering:
img = reshape(uint8(buffer), [], bitmap.Height);
img = reshape(img(1:bitmap.Width*3, :), 3, bitmap.Width, bitmap.Height);
img = permute(img, [3, 2, 1]);
img = img(:, :, [3, 2, 1]);
imshow(img);
%phew!

[Fangjun Jiang]

Good to know this!

[Sven Schömer]

That works, thank you!

Answer 2

[Fangjun Jiang]

Range.CopyPicture method (Excel)

[Guillaume]

Well spotted! The trouble is that this puts the image on the clipboard (as a metafile at that) and matlab is not really able to retrieve that (clipboard doesn't know how to read it)