How to create a cool user interface for an excel file in java or C#? [closed]

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.

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 .

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.

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.

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.

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.