Bill.Net

Hi,

Right now I am using Office automation to generate Excel and save it into a physical file, is there any way to open Excel from a stream and save as an Excel document into a stream

Thanks,



Re: Visual Studio Tools for Office Is there any way to open Excel from stream and save as an Excel document into a stream?

Geoff Darst - MSFT

Hi,

Excel implements IPersistStreamInit. If you create a set of P/Invoke definitions for this interface, you should be able to assign a Workbook object to an IPersistStreamInit object (you'll probably need to do an explicit cast). From there you should be able to call IPersistStreamInit.Load/Save as necessary.

Sincerely,

Geoff Darst

Microsoft VSTO Team





Re: Visual Studio Tools for Office Is there any way to open Excel from stream and save as an Excel document into a stream?

Bill.Net

Hi Geoff,

Thanks for your reply. I have tried to define IpersistStreamInit, but failed. Here is the code in C#.

[

InterfaceType(ComInterfaceType.InterfaceIsIUnknown),

Guid("0000010c-0000-0000-C000-000000000046"),

ComVisibleAttribute(true)]

public interface IPersist

{

void GetClassID(out Guid pClassID);

}

[ComImport, InterfaceType(ComInterfaceType.InterfaceIsIUnknown), Guid("7FD52380-4E07-101B-AE2D-08002B2EC713")]

public interface IPersistStreamInit : IPersist

{

void GetClassID(out Guid pClassID);

[PreserveSig]

int IsDirty();

void Load([In, MarshalAs(UnmanagedType.Interface)] IStream pstm);

void Save([In, MarshalAs(UnmanagedType.Interface)] IStream pstm, [In, MarshalAs(UnmanagedType.Bool)] bool fClearDirty);

void GetSizeMax([Out, MarshalAs(UnmanagedType.LPArray)] long pcbSize);

void InitNew();

}

And here is the code to cast the workbook object.

m_objExcel = new Excel.Application();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = m_objBooks.Add(XlWBATemplate.xlWBATWorksheet);

IPersistStreamInit iStream = (IPersistStreamInit)m_objBook;

it is always failed to cast m_objBook to IPersistStreamInit.

Did I do something wrong

Thank you very much.

Bill





Re: Visual Studio Tools for Office Is there any way to open Excel from stream and save as an Excel document into a stream?

Geoff Darst - MSFT

Hi Bill,

A couple of things.  First I was misremembering--Excel will query its own embedded objects for IPersistStreamInit, but it doesn't implement it itself.  Sorry for the red herring.  However, Excel does implement IPersistStorage so you can still accomplish what you are attempting--it will just be a bit more work.  What you will need to do is call the Win32 function CreateILockBytesOnHGlobal to create an ILockBytes that you can pass to StgCreateDocFileOnILockBytes.  If memory serves, you can pass null for the hGlobal parameter.  Anyway, that will give you an in-memory IStorage.  From there, you can get IPersistStorage from the Excel workbook and call IPersistStorage.InitNew/Save/SaveCompleted.  At that point you will have saved the workbook to an in-memory native storage.  If you want to put that into a managed stream, you would need to iterate the streams in the storage and use the Marshal class to marshal the memory to a managed stream.

Here's an example of how to obtain an IPersistStorage pointer.  Turns out that you need to use the Marshal class; I think the problem is that interop is proxying based on the typelib and the IPersist interfaces don't show up there.  As a result, you get an invalid cast exception.

    [ComImport(), Guid("0000010A-0000-0000-C000-000000000046"), InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIUnknown)]
    public interface IPersistStorage
    {
        void GetClassID(
               [Out]
                  out Guid pClassID);

        [PreserveSig]
        int IsDirty();

        void InitNew(IntPtr pstg);

        [PreserveSig]
        int Load(IntPtr pstg);

        void Save(IntPtr pStgSave, bool fSameAsLoad);

        void SaveCompleted(IntPtr pStgNew);

        void HandsOffStorage();
    }


    public partial class Sheet1
    {
        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
            Excel.Workbook workbook = Application.Workbooks[1];
            Guid IID_IPersistStorage = typeof(IPersistStorage).GUID;
            IntPtr punk = Marshal.GetIUnknownForObject(workbook);
            try
            {
                IntPtr pIPS;
                Marshal.QueryInterface(punk, ref IID_IPersistStorage, out pIPS);
                try
                {
                    IPersistStorage persister = (IPersistStorage)Marshal.GetObjectForIUnknown(pIPS);
                    Guid clsid;
                    persister.GetClassID(out clsid);
                    MessageBox.Show(clsid.ToString());
                }
                finally
                {
                    Marshal.Release(pIPS);
                }
            }
            finally
            {
                Marshal.Release(punk);
            }
        }

 

A couple of things worth noting.  First you must QueryInterface against an actual Excel.Workboook object (as opposed to the VSTO code behind class).  Second, inheritance doesn't work when defining COM interfaces.  Just include all of the inherited methods in the interface definition.

When you want to reload the document, you should be able to use IPersistStorage.Load.   

Hope that helps,

Geoff Darst

Microsoft VSTO Team

 

 

 





Re: Visual Studio Tools for Office Is there any way to open Excel from stream and save as an Excel document into a stream?

Bill.Net

Hi Geoff,

Many many thanks. I would say it is more complicated than I thought, but I will try. I really appreciate it.

Best Regards,

Bill