Tuesday, July 23, 2013

Altering rotated shapes in an Excel worksheet

I've written a script to change all images in many hundreds of Excel documents to a .png format in order to reduce their file size (the largest were coming up on 25MB).



IF THE IMAGES IN THE EXCEL DOCUMENT ARE NOT IN THEIR ORIGINAL ORIENTATION i.e., if they have been rotated since being inserted in the document, THEN THE NEW .PNG IMAGES, when added to the document, ARE DISTORTED. They appear too flat and wide.




The process by which the images are changed is:



* Iterating through all workbooks in the directory

* Iterating through each sheet in the workbook

* Iterating through all shapes in the worksheet (Worksheet.Shapes)

* Extracting the shape size and location with Shape.Width, .Height, .Top, and .Left

* Copying the shape to the Clipboard and storing into a variable of type Bitmap

* Making Color.White transparent

* Saving as a .png

* Deleting the old shape (Shape.Delete)

* Inserting the new image (Shapes.AddPicture)

* Saving and closing the document



What I believe to be the relevant code is as shown below:



' Iterate over each sheet in the current workbook For Each xlsSheet In xlsBook.Worksheets xlsSheet.Unprotect() ' Unprotect the worksheet for editing ' Iterate over each Shape in the current worksheet For Each xlsShape As Excel.Shape In xlsSheet.Shapes copied = False While Not copied Try If Not xlsShape.Name.Contains("Drop Down") Then ' Ignore images with name containing Drop Down (not sure what this is) ' Store Shape properties shapeLeft = xlsShape.Left shapeTop = xlsShape.Top shapeWidth = xlsShape.Width shapeHeight = xlsShape.Height xlsShape.Copy() ' Copy the shape to the Clipboard If Clipboard.ContainsImage Then img = Clipboard.GetImage ' Store the image in a variable img.MakeTransparent(Color.White) ' Use White as the transparent colour imgName = temporaryImageLocation & xlsShape.Name & ".png" img.Save(imgName, ImageFormat.Png) ' Save the image in a temporary location (as a .png file) xlsShape.Delete() ' Delete the current Shape and replace it with the new .png file xlsSheet.Shapes.AddPicture(imgName, MsoTriState.msoFalse, MsoTriState.msoCTrue, shapeLeft, shapeTop, shapeWidth, shapeHeight) File.Delete(imgName) ' Delete the .png file in the temporary location End If End If copied = True Catch ex As System.Runtime.InteropServices.COMException MsgBox(ex.Message) End Try End While Next Next



I've thought that perhaps the code is extracting the original height and width (which will have the inverse aspect ratio of the rotated image), but this isn't the case (I've checked other documents where the images appear just fine, despite having different original sizes). I've also extracted the images manually, saved as a .png, and reinserted (so that the images are now 'not rotated') before running the code and the images then appear to be just fine.



After checking maybe a dozen or so Excel documents that have been 'compressed' as expected, I'm pretty sure this has something to do with the image rotation in the original Excel document. I don't know how to work around this so that, in future, if the code comes across a rotated image, it doesn't falter.



I've tried extracting the Shape.Rotation value and rotating the shape with Shape.IncrementRotation(-Shape.Rotation). The issue with this is that the original shape is deleted and a new one added. I've tried creating an ArrayList to keep track of the shape names and the corresponding rotation on the original shape, but the shape names are changed upon deletion of the original shape and addition of the new shape. I'm not sure what Excel's naming convention for this is. I've also tried something similar by keeping track of the Shape.Left and Shape.Top values, but this doesn't seem to work either.



In other words, I'M FINDING IT DIFFICULT TO KEEP TRACK OF WHICH ROTATION VALUE IS ASSOCIATED WITH WHICH PICTURE IN THE EXCEL DOCUMENT.
Full Post

No comments:

Post a Comment