As I planned I solved this problem in a "terrible" way. Since I do not know how to solve it better, I have decided to publish my solution - maybe it will be helpful for those who face similar issue or maybe somebody will share his experience and suggest a more elegant approach.
So the initial conditions are: I have an Excel workbook which has a generic file embedded as OLE object (packaged object) into its worksheet. I need to develop a C# code in a VSTO project to save this embedded file to the hard disk. I will have one little simplification here - though the embedded file is generic, I will know its extension (e.g. "zip" or "bin") in my code. For easier explanation I will use "zip" extension.
My "terrible" solution in a nutshell:
1) Since I cannot use OLEObject.Object property (I could not find how to deal with it or how to cast it to some persistable interface) copy the OLE object to the clipboard ( OLEObject.Copy() )
2) Get the clipboard data and to the object cast to IDataObject interface
3) Get the object data from the IDataObject object specifying "Native" format (on which I could not find description).
4) Create a memory stream from the just obtained "Native" format object data.
5) Parse the obtained memory stream and determine the stream position where the actual file content starts and ends.
6) Extract and copy the actual file content from memory stream to filestream (thus saving the ZIP file to the disk)
Here is the code which I develop to implement the above solution:
using Microsoft.Office.Interop.Excel;
/* ... */
private void CopyEmbeddedFileToDisk(string fileLocation)
{
/* Some code */
try
{
/*
* Retrieves OLE Object from worksheet
*/
OLEObject embeddedOLEObject = (OLEObject) internalWorkSheet.OLEObjects(1);
/*
* Copies data from Excel OLE Object to clipboard and retrieves it back as object
*/
embeddedOLEObject.Copy();
IDataObject iData = Clipboard.GetDataObject();
string objType = "Native";
Object objToSave = iData.GetData(objType);
Clipboard.SetDataObject("");
/*
* Creates stream to process object data.
*/
MemoryStream objStream = (MemoryStream) objToSave;
long sizeOfMyObj = objStream.Length;
/*
* Specifies embedded file extension, which is stored in object stream "header" 3 times in filenames
*/
string fileExtension = ".ZIP";
/*
* Finds 3 occurrences of fileExtension. The "Native" format has a content header, which
* contains the filename 3 times: one pure filename and then 2 filenames with full path.
* After last filename there are 5 extra bytes and then the actual file content starts.
* The actual file content ends 2 bytes before the "Native" format data ends
*/
long startOffset = 5L;
long endOffset = 2L;
long foundPosition = 0;
for (int occurrence = 1; occurrence <= 3; occurrence++)
{
foundPosition = FindInStream(fileExtension, false, foundPosition, objStream) + fileExtension.Length;
if (foundPosition == -1)
break;
}
/*
* If the specified string was found 3 times, then goes on and extracts the actual file content
*/
if (foundPosition != -1)
{
/*
* Position where actual file content starts.
*/
long fileContentStartPosition = foundPosition + startOffset;
/*
* Copying file content from the object stream to the filestream
*/
byte b;
FileStream fileStream = new
FileStream(fileLocation, FileMode.Create);
objStream.Seek(fileContentStartPosition, SeekOrigin.Begin);
for (long i = 1; i <= sizeOfMyObj - fileContentStartPosition - endOffset; i++)
{
b = (byte) objStream.ReadByte();
fileStream.WriteByte(b);
}
objStream.Flush();
objStream = null;
fileStream.Flush();
fileStream.Close();
File.SetAttributes(fileLocation, FileAttributes.Normal);
}
}
catch (Exception error)
{
/* some code */ MessageBox.Show("Error: " + error.Message + "\n\n" + error.StackTrace.ToString());
}
}
/// <summary>
/// Finds a string of characters in a stream.
/// </summary>
/// <param name="textToFind">
/// String to find, e.g. ".ZIP"
/// </param>
/// <param name="caseSensitive">
/// Specifies whether the search will be case sensitive (true) or not (false)
/// </param>
/// <param name="startPos">
/// Position in stream to start search from
/// </param>
/// <param name="sourceStream">
/// Source stream where the search will be performed
/// </param>
/// <returns>
/// Position in stream where the search string is found. Or (-1) if the string is not found
/// </returns>
private long FindInStream (string textToFind, bool caseSensitive, long startPos, MemoryStream sourceStream)
{
/*
* Converts sought string to an array of chars and gets the array length
*/
char[] charsToFind = textToFind.ToCharArray();
long textToFindLength = textToFind.Length;
/*
* Converts source stream to an array of bytes and gets the array length
*/
byte[] bufArray = sourceStream.ToArray();
long bufLength = bufArray.Length;
/*
* Performs string search in the source stream
*/
for (long x = startPos; x < bufLength; x++)
{
bool firstByteFound;
/*
* Finds out whether the first byte of the string is found in the stream
*/
if (caseSensitive)
{
firstByteFound = (bufArray[x] == (byte) charsToFind[0]);
}
else
{
firstByteFound = (bufArray[x] == (byte) (charsToFind[0].ToString().ToLower())[0]) ||
(bufArray[x] == (byte) (charsToFind[0].ToString().ToUpper())[0]);
}
if (firstByteFound)
{
long y;
/*
* Finds out whether all next bytes of the string follow the first found byte in the stream
*/
for (y = 1; y < textToFindLength; y++)
{
if ((x + y) >= bufLength)
break;
bool nextByteFound;
if (caseSensitive)
{
nextByteFound = (bufArray[x + y] != (byte) charsToFind[ y]);
}
else
{
nextByteFound = (bufArray [x + y] != (byte) (charsToFind[ y].ToString().ToLower())[0] &&
bufArray [x + y] != (byte) (charsToFind[ y].ToString().ToUpper())[0]);
}
if (nextByteFound)
break;
}
if (y == textToFindLength)
{
sourceStream.Flush();
return (x);
}
}
}
sourceStream.Flush();
return (-1);
}
Sorry for any formatting problems in the code above. I would be happy to use the OLEObject.Object property, so if anybody can show me a code sample that makes use of OLEObject.Object, please share your experience.
I also would like to add that I don't like the solution I proposed above - I am not sure what this "Native" clipboard format is, whether it is same on all platforms and in all locales. I guessed about the "Native" format structure by just comparing the Native format data with the original data (original file). So my approach is at least not that serious. It is also very resource consuming - transferring potentially huge data to clipboard and streams (in case the embedded file is very big). But I could not find how to cope with this problem better.
Glad if my solution helps anybody else,
Anton
Tonioant | 
index > Visual Studio Tools for Office > Saving OLEObject content to a file