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.