Excel in a browser control
mathias....@consultec.se
I want to be able to use excel for editing spreadsheets in my
application made with c++ 6.0.
The only catch is that the xls-file is stored in a database, so first
the
file is saved to the temp folder, and after that the user can start
editing
the excel sheet. After editing the file some values has to be
collected from
certain cells and the xls file has to be saved in the database again.
I have
tried to do this some different ways, but havent managed to make it
work as
it should.
1. Start excel with the spreadsheet as a parameter. In this case I
could
use a fomula one component to read the values that I want.
Problems: I don't know when the user is finished. The user might
change the
excel version of the file making it unreadable to formula one.
2. Excel automation.
The samples that I have seen are more like excel exports. Create an
excel
sheet and save it. No window is displayed.
Problem. I would want to let the user edit the excel sheet. I want to
notice
when the user is finished so that I can extract my values before
closing
excel.
3. Excel in a browser control. See sample in C++.net at:
http://www.codeproject.com/KB/office/Embedding_Excel.aspx
Problem: Can't translate the code below to that makes sure that the
excel file
and the excel process is closed:
private void webBrowser1_Navigated(object
sender,WebBrowserNavigatedEventArgs e)
{
// Creation of the workbook object
if((m_Workbook=RetrieveWorkbook(m_ExcelFileName))==null)return;
// Create the Excel.Application
m_XlApplication=(Microsoft.Office.Interop.Excel.Application)m_Workbook.Application;
}
[DllImport("ole32.dll")] static extern int GetRunningObjectTable
(uint reserved,out IRunningObjectTable pprot);
[DllImport("ole32.dll")] static extern int CreateBindCtx(uint
reserved,out
IBindCtx pctx);
public Workbook RetrieveWorkbook(string xlfile)
{
IRunningObjectTable prot=null;
IEnumMoniker pmonkenum=null;
try
{
IntPtr pfetched=IntPtr.Zero;
// Query the running object table (ROT)
if(GetRunningObjectTable(0,out prot)!=0||prot==null)
return
null;
prot.EnumRunning(out pmonkenum); pmonkenum.Reset();
IMoniker[] monikers=new IMoniker[1];
while(pmonkenum.Next(1,monikers,pfetched)==0)
{
IBindCtx pctx; string filepathname;
CreateBindCtx(0,out pctx);
// Get the name of the file
monikers[0].GetDisplayName(pctx,null,out
filepathname);
// Clean up
Marshal.ReleaseComObject(pctx);
// Search for the workbook
if(filepathname.IndexOf(xlfile)!=-1)
{
object roval;
// Get a handle on the workbook
prot.GetObject(monikers[0],out roval);
return roval as Workbook;
}
}
}
catch
{
return null;
}
finally
{
// Clean up
if(prot!=null) Marshal.ReleaseComObject(prot);
if(pmonkenum!=null) Marshal.ReleaseComObject(pmonkenum);
}
return null;
}
protected override void OnClosed(object sender, EventArgs e)
{
try
{
// Quit Excel and clean up.
if(m_Workbook!=null)
{
m_Workbook.Close(true,Missing.Value,Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(m_Workbook);
m_Workbook=null;
}
if(m_XlApplication!=null)
{
m_XlApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject
(m_XlApplication);
m_XlApplication=null;
System.GC.Collect();
}
}
catch
{
MessageBox.Show("Failed to close the application");
}
}
-------------------------------------
All help in this matter is greatly appritiated!
Mathias Håkanssson
Consultec ByggProgram AB
Joseph M. Newcomer
On Thu, 16 Oct 2008 02:01:05 -0700 (PDT), mathias....@consultec.se wrote:
>Hi all,
>
>I want to be able to use excel for editing spreadsheets in my
>application made with c++ 6.0.
>The only catch is that the xls-file is stored in a database, so first
>the
>file is saved to the temp folder, and after that the user can start
>editing
>the excel sheet. After editing the file some values has to be
>collected from
>certain cells and the xls file has to be saved in the database again.
>I have
>tried to do this some different ways, but havent managed to make it
>work as
>it should.
>
> 1. Start excel with the spreadsheet as a parameter. In this case I
>could
>use a fomula one component to read the values that I want.
>Problems: I don't know when the user is finished. The user might
>change the
>excel version of the file making it unreadable to formula one.
****
Note that for things you don't want changed, you can protect the cells. For example, you
can allow the user to input data values but not change formulae.
But the problem of knowing when the user is done remains.
****
>
>2. Excel automation.
>The samples that I have seen are more like excel exports. Create an
>excel
>sheet and save it. No window is displayed.
>Problem. I would want to let the user edit the excel sheet. I want to
>notice
>when the user is finished so that I can extract my values before
>closing
>excel.
****
I have sample code for using PowerPoint automation in my PowerPoint Indexer program, and
you might get some ideas from that. I can tell when the presentation has been closed, if
I recall. It has been a while since I wrote it.
****
>
>3. Excel in a browser control. See sample in C++.net at:
>http://www.codeproject.com/KB/office/Embedding_Excel.aspx
>
>Problem: Can't translate the code below to that makes sure that the
>excel file
>and the excel process is closed:
****
Yes, what you want is an ebedded document here. But I don't do JavaScript, so have no
idea what any of this code is trying to do. But take a look at automation interfaces
because this is just a JavaScript wrapper onthe fundamental interfaces, so if you study
the interfaces you should get some idea.
A good NG to use is microsoft.public.office.automation.developer. These folks were a
great help to me in getting started in PowerPoint automation.
joe
****
Joseph M. Newcomer [MVP]
email: newc...@flounder.com
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm