Excel grid control in Visual Studio 2005 app

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Any User - 31 Jan 2007 09:44 GMT

Hello

I have an Excel spreadsheet, that holds some tabular data, and a macro,
that exports it daily do SQL Server 2000 table.

Now I want to build VS2005 application, that embeds Excel grid, that is
connected directly to SQL Server.

I've seen such application (unfortunately closed source), so I know,
that this is possible. But I don't know, how to start, since I'm a
DotNet beginner.

Can someone point me, where to start?

Thanks!

Reply to this Message

RobinS - 31 Jan 2007 23:19 GMT

I think this is the kind of thing that Visual Studio Tools for Office does.
The only other way I could think of is to imbed the grid in a webpage, and
I don't think that will suffice.

Robin S.
----------------------------

> Hello
>
[quoted text clipped - 11 lines]
>
> Thanks!

Reply to this Message

Oliver Sturm - 01 Feb 2007 19:43 GMT

Hello RobinS,

>I think this is the kind of thing that Visual Studio Tools for Office
>does. The only other way I could think of is to imbed the grid in a
>webpage, and I don't think that will suffice.

I can't claim I've looked into this in all detail, but I think at least by
default VSTO works the other way round: it allows you to develop
extensions for Office applications, which can go so far as to get rid of
all the standard Office UI and replace it with your own (within limits).
At least this works in conjunction with Office 2007, not sure how far it
goes with 2003. In any case, if your UI and your application functionality
are rather complex, I believe this is not the way to go. Please correct me
if I'm missing something.

A few years ago the answer to the OP's question would have been easy:
embed an OLE document of the right type in your form and you're done - MFC
was able to do the rest for you. With .NET this has become rather more
complicated. Originally, Microsoft were planning to include a control
called ActiveDocumentHost in .NET 2, but it was removed before .NET 2 went
RTM (http://www.shawnburke.com/default.aspx?document=128&userinterface=9).

Currently I think if you can't make do with the WebBrowser control (this
works in some instances to activate OLE docs), you'd have to write that
thing yourself... dig out your Inside OLE 2 and get going :-)

Oliver Sturm

Signature

http://www.sturmnet.org/blog

Reply to this Message

RobinS - 01 Feb 2007 22:54 GMT

> Hello RobinS,
>
[quoted text clipped - 24 lines]
>
> Oliver Sturm

Oliver,

I agree with you, I don't really think he can do it, but I've only seen
quick demos of VSTO, and didn't want to rule it out. But I do think you're
right, it's for Excel Applications, and not for imbedding Excel into a VB
application, but since I wasn't 100% sure, didn't want to post that.

The only way I think this would work is imbedding it in IE, and I guess
you'd have to do the updates using ADO. I'd actually just us a DataGrid;
bringing Excel into the mix is too difficult. What if they put in formulas
and stuff like that, how are you going to save that to a database?

If I was going to use Excel today, I'd use OLE Automation. I've done a lot
of that to create reports. If I needed user input, I'd stick with Windows
Forms.

I went to the Vista Launch in San Francisco on Tuesday, and the Office 2007
stuff is really cool. They showed a lot of updating it here and having the
results show up there, but I think the whole thing had Sharepoint behind
it, which is a completely different beast altogether. (It looked cool,
though!)

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

Reply to this Message

Oliver Sturm - 02 Feb 2007 09:01 GMT

Hello RobinS,

>The only way I think this would work is imbedding it in IE, and I guess
>you'd have to do the updates using ADO. I'd actually just us a DataGrid;
>bringing Excel into the mix is too difficult. What if they put in formulas
>and stuff like that, how are you going to save that to a database?

Well, you wouldn't. Embedding Excel in your application allows you to edit
Excel files in a window that looks like it's an integral part of your
application, that's all. Of course an Excel sheet can, in turn, be
configured to retrieve its data from an external source, which may happen
to be the same data source (i.e. database) that your application also
uses. But even though Excel is visualizing its output inside one of your
application windows, it's still Excel running there and it does its own
job very much like it would if it were running on its own.

>If I was going to use Excel today, I'd use OLE Automation. I've done a lot
>of that to create reports. If I needed user input, I'd stick with Windows
>Forms.

But that's exactly the crux - for certain kinds of input, Windows Forms is
pretty bad. Theoretically you could create a control that would emulate
the table-oriented editing capabilities that Excel has, but that's
extremely complex and why bother if Excel is already there? OLE (note the
"E" - it stands for "embedding") was going to solve that dilemma, and I do
think it's a pity that the idea has been more or less abandoned in .NET.
It was probably never perfect, but it allowed for amazing functionality if
used correctly. Of course it's still possible today if you use the
existing APIs... only it's a lot of work and there are fewer developers
around than ever who know how to code that kind of thing themselves. OLE
automation may suffice to remote control an application, but it doesn't
give you the integrated look and feel and handling that object embedding
does.

>I went to the Vista Launch in San Francisco on Tuesday, and the Office
>2007 stuff is really cool. They showed a lot of updating it here and
>having the results show up there, but I think the whole thing had
>Sharepoint behind it, which is a completely different beast altogether.
>(It looked cool, though!)

Yeah, I know it does :-) Sharepoint or not - Microsoft is envisioning
Office to be the platform for applications these days, not "just" the tool
that's being used by other applications. And granted, VSTO does amazing
things in many ways. But it doesn't give developers the freedom to
integrate in the way they want (focusing on their own applications as the
central platform instead of Office) and it also doesn't do much to improve
the often stupid and restricted data and event models that most Office
applications work with (but that's a completely different topic).

>Ts'i mahnu uterna ot twan ot geifur hingts uto.

Lbh qba'g fnl!

Oliver Sturm

Signature

http://www.sturmnet.org/blog

Reply to this Message

RobinS - 03 Feb 2007 00:23 GMT

> Hello RobinS,

<snip>

> Embedding Excel in your application allows you to edit Excel files in a
> window that looks like it's an integral part of your application, that's
[quoted text clipped - 4 lines]
> Excel running there and it does its own job very much like it would if it
> were running on its own.

**You're right about that.

>>If I was going to use Excel today, I'd use OLE Automation. I've done a
>>lot of that to create reports. If I needed user input, I'd stick with
[quoted text clipped - 13 lines]
> application, but it doesn't give you the integrated look and feel and
> handling that object embedding does.

I guess I haven't had any problems using Windows Forms for anything I've
done, except in VB6 the form couldn't be larger than the real estate, and
that was annoying, but sort of circumventable with Tab controls.

I've done a *lot* of reporting with OLE Automation, and am really hoping it
still works with Office 2007. I'm not sure how much of it is going to be
broken. My users in the corporate world *love* Excel, and being able to
dump their data and muck around with it makes them really, really happy.

I haven't ever used Excel for modifying data because I'm more
anal-retentive than that -- I *really* like to validate the heck out of
data before trying to store it. In VB6 I beat the Heirarchical Grid into
submission (phew, that was exhausting), and in VB2005 I'm using the
DataGridView, although I still prefer to have an edit screen for a specific
record because it gives me more control.

>><SNIP>Vista Launch/cool stuff/Sharepoint
>
[quoted text clipped - 6 lines]
> improve the often stupid and restricted data and event models that most
> Office applications work with (but that's a completely different topic).

I agree with that. I think they're driving Office because customers found
ways to do things with it that Microsoft never thought of and now they can
use that to market to other people. Who ever thought you'd need more than
65,000(ish) rows in a spreadsheet, or more than 255 columns? Frankly, I
think there's a point where it's too much data to absorb, but you know how
customers are -- they know best.

I was annoyed that VSTO was a separate product than VS2005. I would have
liked to play around with it, but no way was I going to buy two of them.
Why couldn't it be a plug-in for VS2005?

During the presentations at the launch, someone said the new one is an
add-on to VS2005. It apparently works with Office2003, too. And it actually
is an add-on, but if you have VS2005Pro, you don't get any "document-level
customizations or other functionality that is part of the full version of
VSTO 2005; VSTO 2005 SE adds only the application-level features..." So you
have to have Team Suite or original VSTO to get the whole shebang.

The only big problem I had with the demos at the Launch is I couldn't
easily tell what was Sharepoint, what was InfoPath, and what was Office. I
suppose that's part of the selling point, but as a developer, I need to
know what I'm going to need to know, if you know what I mean. And they
didn't show the code connecting the stuff, or tell how you're supposed to
do that. At one point, I did see some .net code (for 2 seconds while they
were flipping through screens) with a Sharepoint namespace, but that's all
I know about it.

The WPF stuff is pretty cool; I'm learning that now, and it *really* lets
you imbed stuff all over the place. Haven't tried imbedding office in it
yet, though.

>>Ts'i mahnu uterna ot twan ot geifur hingts uto.
>
> Lbh qba'g fnl!

What in the world does *that* mean?? It doesn't even have enough characters
for me to figure it out by pattern recognition (I do cryptograms). Please
don't leave me in suspense!

Robin S.

Reply to this Message

Oliver Sturm - 03 Feb 2007 10:47 GMT

Hello RobinS,

>I've done a lot of reporting with OLE Automation, and am really hoping it
>still works with Office 2007. I'm not sure how much of it is going to be
>broken. My users in the corporate world love Excel, and being able to dump
>their data and muck around with it makes them really, really happy.

Yeah, they always love to do that... I think it should still work in 2007,
although of course some details may have changed as usual.

>I really like to validate the heck out of data before trying to store it.

Certainly a healthy attitude.

>Who ever thought you'd need more than 65,000(ish) rows in a spreadsheet,
>or more than 255 columns?

Why, everybody! That must have been one of the major points of criticism,
at least since Excel included an environment that allowed to automate
certain tasks... I remember those complaints from Excel 2.0 days, at least.

>I was annoyed that VSTO was a separate product than VS2005. I would have
>liked to play around with it, but no way was I going to buy two of them.
>Why couldn't it be a plug-in for VS2005?

You should get a subscription. People are complaining a lot about that as
well, but it's definitely value for money...

>The WPF stuff is pretty cool; I'm learning that now, and it really lets
>you imbed stuff all over the place. Haven't tried imbedding office in it
>yet, though.

Hehe... that's a different thing :-)

>>Lbh qba'g fnl!
>
>What in the world does that mean?? It doesn't even have enough characters
>for me to figure it out by pattern recognition (I do cryptograms). Please
>don't leave me in suspense!

I didn't make it very complicated... it's a (usenet) standard ROT13 "code".

Oliver Sturm

Signature

http://www.sturmnet.org/blog

Reply to this Message

RobinS - 04 Feb 2007 19:31 GMT

Comments below...

> Hello RobinS,

>>Who ever thought you'd need more than 65,000(ish) rows in a spreadsheet,
>>or more than 255 columns?
[quoted text clipped - 3 lines]
> certain tasks... I remember those complaints from Excel 2.0 days, at
> least.

Apparently so. I don't see how a person can assimilate that much
information.

>>I was annoyed that VSTO was a separate product than VS2005. I would have
>>liked to play around with it, but no way was I going to buy two of them.
>>Why couldn't it be a plug-in for VS2005?
>
> You should get a subscription. People are complaining a lot about that as
> well, but it's definitely value for money...

Aren't they like $2,000?

<snip WPF>

>>>Lbh qba'g fnl!
>>
[quoted text clipped - 4 lines]
> I didn't make it very complicated... it's a (usenet) standard ROT13
> "code".

Guess I haven't been going UseNet long enough, but with the help of
Wikipedia, I figured it out. I did UseNet years ago when I used to do
Vax/VMS System Support (I was a project lead on a huge software project,
but our system manager left. How did I end up with the System Support?
Let's just say, never go to the bathroom during a staff meeting) but only
started in again last year.

Unir n avpr qnl!

Robin S.

Reply to this Message

Oliver Sturm - 12 Feb 2007 16:40 GMT

Hello RobinS,

>>Why, everybody! That must have been one of the major points of criticism,
>>at least since Excel included an environment that allowed to automate
[quoted text clipped - 3 lines]
>Apparently so. I don't see how a person can assimilate that much
>information.

No, I agree. But the problem is that a macro/VBA program running in Excel
can make automated use of that space, which is a very common approach. And
in those cases the restriction seemed rather arbitrary (definitely once
there was 32 bit somewhere in Microsoft's Windows marketing blurb, and
don't forget that Excel programmers don't necessarily understand about
binary encodings and such things), and so can obviously lead to problems
in cases that are not that far removed from the imaginable :-)

>>You should get a subscription. People are complaining a lot about that as
>>well, but it's definitely value for money...
>
>Aren't they like $2,000?

It totally depends on which one you get, but yes, they might be around
that price tag. And if you're quoting USD, be glad you're not in Europe,
where they're quite a lot more expensive. But then - do you know of any
craftsman or manufacturer who spends significantly less than that for the
tools and commodities of his trade during the course of a year? And of
course, where and how do you get hold of your copy of VS so that it's much
cheaper?

>Guess I haven't been going UseNet long enough, but with the help of
>Wikipedia, I figured it out. I did UseNet years ago when I used to do
>Vax/VMS System Support (I was a project lead on a huge software project,
>but our system manager left.

Maybe that's not everybody's definition, but we're using UseNet right now,
aren't we?

>Unir n avpr qnl!

Thanks, you too.

Oliver Sturm

Signature

http://www.sturmnet.org/blog

Reply to this Message

RobinS - 13 Feb 2007 01:47 GMT

> Hello RobinS,
>
[quoted text clipped - 13 lines]
> about binary encodings and such things), and so can obviously lead to
> problems in cases that are not that far removed from the imaginable :-)

That's a good point; I don't innately think of using Excel as a database,
but you're right, many use it as such.

>>>You should get a subscription. People are complaining a lot about that
>>>as well, but it's definitely value for money...
[quoted text clipped - 8 lines]
> course, where and how do you get hold of your copy of VS so that it's
> much cheaper?

Well, I got my copies of Visual Studio Pro 2005 and SQLServer 2005 at the
Worldwide Launch event for those products in San Francisco in Nov/Dec 2005
for free. You're absolutely right, if I had to buy those two products, I
might consider buying an MSDN subscription. I just got a copy of Microsoft
Vista Ultimate and Office 2007 at the Vista launch a couple of weeks ago.

:-D So I should be set for a while. 6 months maybe. <G>

>>Guess I haven't been going UseNet long enough, but with the help of
>>Wikipedia, I figured it out. I did UseNet years ago when I used to do
[quoted text clipped - 3 lines]
> Maybe that's not everybody's definition, but we're using UseNet right
> now, aren't we?

Well, yes, but I've only been back on usenet since around October of last
year, and hadn't run across the ROT13.

Have fun.

Robin

Reply to this Message

Rate this thread:

Excel grid control in Visual Studio 2005 app

Free Magazines