C# and Excel Suggetion

[rene.marxis]

Hi

I need some hints for the following problem.

We have some customer that has one Excel file composed of about 20
sheets and he wants to convert this file to a small C# application.
It's not that he does not want to use Excel at all, but there is data
that has to be entered inside the first sheet and from this data all
other sheets are calulated.
Now he wants us to write some app, where his customers are able to
enter the required data into text-fields and this data should be
passed to an excel file. Doing this excel would calculate all other
sheet-fields.
Now those calculated values must be grabed and passed to an other
excel file, which does NOT hold the formulas for the calculation but
also has some additional charts and graphics which relay on the
calculated values.
This new excel file should then be shown to the user.

I thought of holding the "master" file inside a database and just
opening the excel sheet via automation in memory (just don t call
Show) and pass the entered values to this file and then opening a new
file (from a template) passing the calculated values, deleting the
temporary "master" file and then showing the final one.
The sheets of the "master" file will be password protected (to have at
least a litte security).

What do you think of this approach? Would you recomend to do the
complete proceses different?

_thanks in advance

rene

 

[sloan]

A couple of basics:

You can use an IDataReader to read excel. You can also load the excel data
into a dataset.

Excel also has a "xml standard". To see this quickly, load an excel file
up, do a "Save As (Xml)", and then load the result file into notepad.

The last time I used the xml/excel thing, you could NOT embedd object graphs
into it.

the third hint is:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry
but this one doesn't exactly apply to you.

..............

You may want to look at the asrose excel toolkit as well.
http://www.aspose.com/Products/Aspose.Grid/Default.aspx


Those are some hints. Not a direct answer though.

 

[sloan]

You can read through this post as well:
http://groups.google.com/group/micr...read/ae62d85dcd203a82/746597359ae8d268?lnk=st

 

[Peter Duniho]

[...]
Now he wants us to write some app, where his customers are able to
enter the required data into text-fields and this data should be
passed to an excel file.

Personally, it seems to me that you would be better off programming the
user-input form in Excel, rather than trying to get an external C#
application to work with Excel.

Not that the latter can't be done, but you'll have to jump through some
hoops, whereas if you code in Excel itself, it'll just work. You'll have
direct access to the spreadsheet(s) you want to interact with.

You might have to learn Visual Basic (very easy, especially if you already
know the Microsoft .NET languages), since I'm not sure whether you can
code C# directly from within the Excel macro IDE.

Alternatively, you mentioned using automation, which I think you ought to
be able to use simply to get the user input into Excel and operate on
whatever Excel objects you want. I don't see any particular problem
keeping the data in a database rather than an Excel spreadsheet
(especially if it's really database-like in nature), but I don't think
that aspect of the question has a direct bearing on how you implement the
UI.

Pete

 

[rene.marxis]

Hi

thanks for your answers.
First to your suggestion Pete: Our customer just don't want his
customers to see the excel sheet where the formulas are included. Of
course he will we able to "track" the creation of that temporary file
between creation, copy and deletion. But its not that easy for him.
Also the sheets will be protected. Thats why i thought putting the
data (binary file) inside a database, and create it just for a short
period of time. But i'll think about the idea to code the UI in Excel
directly.

For sloan: I started implementing the program with ADO.Net (i think
thats what IDataReader also does) but its much to complicated, as the
data inside the template-file is not really organized. I can't do just
a select, because the data is formated to look "great".
For now i did some VB-Class module that used late binding to open the
template and the final document and entering the data
with .Range("A1") = 12.
This is pretty fast and works ok till now. For getting the Data i
would have to do a select on every cell, and there are about 4000-5000
cells to read ...
But i ll also give it a try and take a look if ADO.Net is usable in an
better way.

I ll let you know

_rene

 

[Kalpesh]

Rene,

I worked on an application similar to this & used VB6 to do the work

1) The VB6 app will take the input (fixed set of data) in a windows
forms app
2) It will automate excel in the background. One needs to take care
that if user is running excel, the automated excel should not
interfere with it & vice versa
It is possible that user could have excel open, prior to running the
app OR open the app & then open the excel again

So, one needs to make sure that the code lets the user open & manage
user instance of excel. Also, it should not show automated excel
instance to the user.

3) Pass the data to the automated instance, let it do the calculation
4) Pull the data from the known output cells & push it to a html file
in a browser


Hope that helps

Kalpesh