ASP.NET MVC / C# - How to Convert/Create Excel (xls) with formatting from DataTable/List in MVC?

Question

I was able to create Excel xls file from DataTable following this link. But how do I format the contents of the XLS file?

Thanks.

score 1 · Answer 1 · answered Mar 25 '11 at 18:16

1

Using this technique you cannot format the contents. You will need to generate a real Excel binary file using some third party control for this purpose. Here's one worth checking out. You may also take a look at this article.

answered Mar 25 '11 at 18:16

Darin Dimitrov

1,023,142
271
3,287
2,928

score 1 · Accepted Answer · answered Mar 25 '11 at 18:19

1

I have personally used NPOI with good results.

answered Mar 25 '11 at 18:19

Atanas Korchev

30,562
8
59
93

Seems like NPOI would be a great fit. Can they take in a DataSet and generate Excel? How about a generic list of object? Thanks. – Saxman Mar 25 '11 at 20:43
You should iterate the data yourself and create the Excel document. It's very easy actually - check the examples. – Atanas Korchev Mar 25 '11 at 21:19

score 0 · Answer 3 · edited May 23 '17 at 12:26

The solution in that link simply creates a tab-separated value file, which can only contain the cell data, not the formatting information. If you need the formatting then you either save HTML markup with an extension of .xls (not an approach I'd normally recommend), or you nede to create a "genuine" Excel file... but I don't know what library options you have in C# to do this (other than using .net

However, there's a number of libraries listed in the responses to this previous question

score 0 · Answer 4 · answered Mar 25 '11 at 18:33

If you prefer to avoid 3rd party libraries, you have two other reasonable alternatives:

Personally I'm a fan of SpreadsheetML (one of the formats supported by Excel 2003+), since it's relatively easy to just manually generate one from scratch. Just create a file that looks right and save to SpreadsheetML format, then use that as a guideline when generating your own.

An alternative is to start with a preformated "clean" Excel document (with table headings but no data). You can then insert rows of data into it using the Jet.OLEDB provider.

This has numerous disadvantages over using a 3rd party library:
1. Cannot format the data you are inserting. Only allows formatting of the table headings etc.
2. Compatibility issues on some versions of Windows.
3. Handles some types of data poorly.

It has a few advantages over using a 3rd party library:
1. From Microsoft. Fewer bugs, free, less risk of malicious code.
2. Very simple to use, assuming you're already comfortable working with SQL.

ASP.NET MVC / C# - How to Convert/Create Excel (xls) with formatting from DataTable/List in MVC?

4 Answers4