Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Alternatives to Server side Excel manipulation for .NET

Posted on 2010-11-15
10
Medium Priority
?
904 Views
Last Modified: 2012-05-10
I'm looking for a viable alternative to Excel automation to manipulate, read and create excel files in an ASP.NET website context.

I'm aware of a couple of component builders that support these scenario's:
- Aspose Cells,
- softartisans OfficeWriter
- Spreadsheetgear

But these all have one thing in common, they're very expensive.

I'd prefer a solution that supports both Office 2003 file formats and Office 2007 file formats, but a good solution that only supports either, is no problem for me.

I've also found a few links to System.IO.Packaging, but I haven't been able to find a good tutorial that shows how to create and manipulate excel files, other than through complex XML manipulation.
0
Comment
Question by:Jesse Houwing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34135028
If you are ready to invest some time, there is a very intriguing direction. Excel files can be read and written (including all formatting and formulas) using XML. Every Excel file is actually made up of numerous XML files clubbed together to get the final effect - One Excel file. You can read these XML files directly as well as write to them. You dont need the Excel application at all. Just a thorough understanding of the Excel file Internals should be enough. I have seen a very big enterprise application built on this concept which is currently running in a multi-user (500+ users) multi-location environment (3 continents - 8 locations). It has an "generic Excel engine" that can read and write Excel file and sync with a standard database.

Here is an article to start a thought.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3787-Understanding-Excel-File-Internals.html
0
 
LVL 17

Author Comment

by:Jesse Houwing
ID: 34135084
This is what System.IO.Packaging tries to make simpler, but as I need a less experience developer to build this, I'll need more than just free XML access as the solution...
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 34135118
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 17

Author Comment

by:Jesse Houwing
ID: 34135479
I'm a big fan of Aspose too, but there is no budget in this project for their wonderful components. I feel we should just buy an enterprise license and be done with it, but that's me.

I'll have a look at ExcelPackage, it looks like it might suit our needs perfectly!
0
 
LVL 28

Expert Comment

by:sybe
ID: 34135955
It really depends how complicated the Excel is.

Simple solution is to send a HTML-table with content-type "application/vnd.ms-excel" and it will open in Excel on the client machine. But this allows only a single worksheet.

Then there is the single-XML-approach. Office-XML is a special XML-format which can contain many Excel features. It can create multiple worksheets in a file and it really isn't that difficult. You can take a desired Excel file and do "save as XML" to get an example of what it looks like. Don't be overwhelmed by the <styles> section, it seems that excel creates a seperate style tag for every single cell (a big overkill). I use XSLT and XML to create Excel files on the fly this way. Disadvantage is this does not support all features in Excel.

> other than through complex XML manipulation

Not sure how experienced you are with XML, but it really isn't that complicated once you are used to it.





0
 
LVL 17

Author Comment

by:Jesse Houwing
ID: 34136647
Multiple worksheets is a requirement I have to deal with, so save as HTML is no option. We also need to read the data back in, where HTML is far from ideal.

I know the XML manipulation isn't that difficult, but its a lot less readable than a few lines of code doing a transfer of data from a database to an object. The source of our data is already in objects (from Linq to Sql).

When doing XML translations we also bumped into issues with merged cells, and objects such as graphs and other things already in the workbook. When you're always creating the files from scratch and don't have to read them back in, your solution sounds ideal, but as we're having to roundtrip them, I'm first going to give ExcelPackage a try.
0
 
LVL 28

Expert Comment

by:sybe
ID: 34143174
> objects such as graphs and other things already in the workbook

You mean you do not start from scratch? if you have things "already in the workbook".

That makes it all so much easier. Create a connection to your not-from-scratch excel file, and use that connection to fill the workbook from your datasource (for which you need a different connection of course).
0
 
LVL 17

Author Comment

by:Jesse Houwing
ID: 34143701
We need to support both scenario's, in fact we need
- to create excel files from scratch
- read these (and similar excel files)
- manipulate existing (either from this app, but also user created) excel files

We also need these to be able to work concurrently and in an ASP.NET context. Which makes OleDb connections to Excel a less than ideal solution, unfortunately...
0
 
LVL 28

Expert Comment

by:sybe
ID: 34144604
It is not very clear what it is that you want. It sounds like you need a desktop application, not a web-based.

> to create excel files from scratch
Actually there is not a real difference between creating excel files from scratch and using an empty excel file to start from. In both cases you'll probably need a limited number of templates. If you want users to design their excel totally freely, you need something like google docs.

> read these (and similar excel files)
Users can not "read" excel files on the server. They can download them.

> manipulate existing (either from this app, but also user created) excel files
Same issue: users can not manipulate excel files on the server through http. They can be downloaded, edited and uploaded again. For that you do not need anything special apart from download and upload of files.


0
 
LVL 17

Author Comment

by:Jesse Houwing
ID: 34145304
@emoreau we actually are using ExcelPackagePlus, the continuation of ExcelPackage
It can be found here: http://epplus.codeplex.com/

@sybe, I wasn't talking about users reading them, its actually code doing the reading, doing some manipulations, updating values, changing references and such and then writing the whole thing into a database. I'm pretty sure of what I need. And I'm very happy with the answer provided by emoreau.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question