Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have an excel file with many sheets and relations between theme (ie, formulas that relates sheets to each other). I wanna create the user interface for this excel file.in other words I wanna create desktop application that has many tabs and menus and user by selecting tabs or menus,sees corresponding sheet from excel file in the form (in the application interface) and can data entries and see the results in other tabs from other sheets.also I wanna create cool ui for the application.

How I can do this? Note: I am using Eclipse-jee-indigo for java and Microsoft visual studio 2010 for C#. Microsoft visual studio has also toolbox to create GUI for C# applications. and also there are many GUI designer like jgoodies as eclipse plugin and dotnetbar for C#.

share|improve this question

closed as not a real question by MichaelT, Martijn Pieters, BЈовић, Glenn Nelson, thorsten müller Mar 7 '13 at 12:49

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This question: stackoverflow.com/questions/2654932/… might give you an idea of how to access an Excel file from code. –  FrustratedWithFormsDesigner Aug 14 '12 at 18:06
add comment

6 Answers 6

Don't -- create a cool UI encompassing whatever cool thing said spreadsheet does. Do not attempt to build a UI on top of a spreadsheet as a datastore -- especially a complex spreadsheet that is already an app in it's own right -- if you can help it. Sometime in your professional life you will probably have to do this, but it is a challenge best left until then.

If you absolutely must do this to yourself, you probably want to use something builtin to excel such as Visual Basic for Applications.

share|improve this answer
    
Agree entirely. Launching Excel from within another application (to use as user input tool and output display grid) and read/writing data to/from the file is not fun. The VSTO wrappers help hide full horror of COM; but what bleeds through is still lousy. Handling anything related to making sure the file is saved is a nightmare as well. For more fun, you can end up with headless orphan excel processes clogging up your system. –  Dan Neely Aug 14 '12 at 18:28
add comment

KISS - is a principle that you should not violate

Building a UI on top of an Excel sheet that already has complex dependencies on other sheets may introduce several complexities that you are NOT aware by now.

I would advice to use a an advanced UI layer, once you have migrated your excel data to a database, otherwise it would be better to stick with KISS - reference from Wiki .

share|improve this answer
add comment

If you're using Excel spreadsheets for storing and manipulating data, and you're capable of programming in a full-featured language, it's important that you ask yourself the question "is Excel appropriate for this application?".

For example, I've seen an Excel spreadsheet used to track defects ("scrap") on the factory floor of a complex manufacturing plant...the spreadsheet was kept in a shared folder, and could be edited by anyone on the factory floor or in factory offices. Read/write conflicts (data lost due to multiple edits being made simultaneously, with the last to be saved overwriting all previous edits) were likely commonplace (and went undetected), and, as the spreadsheet was used to assign blame for ruining products to specific departements (at values sometimes over $200,000 for a single item), there were likely also edit wars...with no tracking of who edited the document and when.

The sad thing is that if they'd hired someone to develop their application in .net or Java, using a relational database, they'd have been logging who made what edits, preventing read/write conflicts, and keeping better track of what was scrapped..and it would have cost much less to develop the application. Instead, they hired a full-time consultant for over two years writing (uncommented) VBA code to maintain their scrap tracking excel file...despite the fact it was obvious from day one that Excel cannot be made suitable to the task being requested of it.

If your application manipulates data for a single user, then Excel is an OK choice, perhaps even an excellent choice. While I've never done it myself, I believe it's also possible (but difficult) to add another spreadsheet to an Excel document and implement a GUI on that spreadsheet which feeds data to and retrieves data from other tabs, providing an alternative, potentially more user-friendly UI to your existing spreadsheets, using the built-in VBA programming language, which may be all you'd need.

That said, if multiple users need to interact with the same data, it's usually time to switch to a relational database, and move your business logic from the spreadsheet into a full-featured programming language that can actually protect the integrity of the data. Exporting data to a format that Excel can read, so it can be plugged in to an existing data analysis spreadsheet is sometimes worthwhile if you find it difficult to re-implement some of the graphs or other analysis tools in the spreadsheet, but with appropriate libraries, it should be possible to re-implement everything that was actually useful in the spreadsheet.

share|improve this answer
add comment

Most of the warnings here from those other people are reasonable, but there are cases where it makes sense to add some GUI functionality to Excel sheets. I have done this before by myself, and my favorite tool for that is Excel-DNA. That's a lightweight, version-independent tool for writing Excel plugins, which is much easier to use than VSTO. It allows you to combine the full GUI capabilities of the .NET framework with the full access to the Excel object model, like as if you were programming with the build-in Excel VBA.

For example, Excel-DNA allows you to use C# for writing user-defined functions which can be easily started by a button placed on your Excel sheets, by a menu, a ribbon or a keyboard-shortcut directly from Excel. These functions can contain all the GUI functionality you need without almost no "friction" between the C# code and the Excel document.

share|improve this answer
add comment

There are 3rd party tools that allows you to display MS Excel sheets in your application without using Excel itself (so you could possibly avoid COM interface).

Some of these tools honor macros to a certain extent (not sure how much).

In general, as many advised don't try to embed Excel in your application's UI. In fact, that feature was taken off quietly by MS after 2005 or so.

You can always launch Excel from your application (as a stand-alone application) and let the user change the data in Excel.

You can also build a form in your application and get/send one or more fields from/to to the Excel file without launching Excel from within your UI.

Yet, another option is to create a form in VBA inside your spreadsheet and let the work on the sheet be a separate task from your application.

share|improve this answer
add comment

Other people here have warned you against the problems in automating Excel using the COM/PIA interfaces, and these are ALL good warnings. However, if you find yourself forced to write your UI over an Excel datastore anyway, for business reasons or historical reasons or any reason whatsoever, there are some ways to reduce the pain.

My main recommendation is not to use the Excel application itself, which is clunky and hard to automate, but to treat your spreadsheet file as a database, and access it directly. This will spare you a lot of interop pain, though it might limit some functionality (like using Excel formulas and such).

You can connect directly to Excel files using OleDB or ODBC provider, and I've seen what seems to be a nice implementation of Linq to Excel, though I haven't actually tried it yet.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.