Export and format data into Excel from C#

Question

I know this topic has been discussed but I think it has some differences. I have some list of object which I want to copy to Excel. I need to format the appearance of the spreadsheet too, if possible from Excel. I've been said that was some kind of Excel Automation library or so that allows yo to build a spreadsheet from code. This way I should be able to create that document and later open it. Any ideas on that matter? Thanks!

Is the xmlx format an option? If it is then try the [Microsoft Office Open XML SDK](http://msdn.microsoft.com/en-us/library/bb448854.aspx) — Adriano Repetti, Jun 26 '12 at 10:45
My objects are retrieved from a database using NHibernate. Any XMÑ format seems suitable form me apart from too simple csv. — David Fornas, Jun 26 '12 at 11:02
A personal statement - Excel automation is no fun. The initial appeal soons wears off! I find it slow, prone to memory leaks, extremely sensitive to its environment (service packs, anti-virus etc). Server-side automation (if you're considering it) is just a no-no. It *can* work, and many use it without problem, but as time goes by and requirements build, it starts to get in the way. — Neil Moss, Jun 26 '12 at 11:10
Is Microsoft.Office.Interop.Excel the same as Excel automation? Because I did that work but I want to know If it's a good practice. — David Fornas, Jun 26 '12 at 17:12

score 4 · Answer 1 · answered Jun 26 '12 at 10:49

4

If you creating a typical excel document that does not use Advanced features of Excel, a library like NPOI http://npoi.codeplex.com/ or at http://code.google.com/p/npoi/ renders well. Or another one called ExcelLibrary. I have personally used the NPOI library with a lot of ease. There are known complexities/problem with server-side office automation: http://support.microsoft.com/kb/257757. NPOI is suited for producing xls (pre 2007 Excel versions) files. If you are happy churning out xslx (post 2007 Excel versions) files, use the OpenXML - It is able to support almost all the features you might need on your xslx document

answered Jun 26 '12 at 10:49

John Gathogo

4,495
3
32
48

And what about Microsoft.Office.Interop.Excel? Is it more or less the same? – David Fornas Jun 26 '12 at 11:03
1

@DavidFornas: Its true. You can use Microsoft.Office.Interop.Excel - depending on the scenario and how well you can navigate the murky waters of COM. You miss one thing and you end up with multiple Excel processes running in the background for failing to delink all references to the COM objects. But there is a gotcha. You will get unexpected results if you deploy your solution to a computer that does not have Excel installed – John Gathogo Jun 26 '12 at 11:09
@DavidFornas: Also refer to this link: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp – John Gathogo Jun 26 '12 at 11:10
Thank you. I managed to do what I wanted this morning and when I have closed the computer I have seen some 'Excel is still open message'. Now I know that it's a problem. Tomorrow I'll check if I'm not closing everything right.Thanks. – David Fornas Jun 26 '12 at 17:11

score 0 · Accepted Answer · answered Jun 26 '12 at 11:01

You can export data to Excel from C#, can you give us more info about the objects you want to do the export from, if for instance you need to create and open an excel document from a Listview this is how you'd do it:

First you need to add Microsoft.Office.Interop.Excel as a reference to the project and add the following assembly to your code: using Excel = Microsoft.Office.Interop.Excel;

   //This would convert the listview1 content to Excel document and create the file in the given path        
    private void CreateExcelFromListview()
    {
        ListViewExport.ListViewToCsv(listView1, "C:\\test.csv", true);
        OpenInExcel("C:\\test.csv");
    }

    //This would open the document on screen
    public void OpenInExcel(string strFileName)
    {
        try
        {
            new Excel.Application {Visible = true}.Workbooks.Open(strFileName);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Objects come from a regular List so my idea was copying all properties in a foreach or something similar. I don't want to use not built-in libraries. — David Fornas, Jun 26 '12 at 11:06

score 0 · Answer 3 · answered Jun 26 '12 at 11:06

In case you have no joy with the OpenXML or other libraries suggested so far, you might care to look at Aspose.Cells. I've used it to good effect in two different projects without issue (it did have some color formatting quirks, but they could be worked around).

It lets you read and build Excel files (in all versions), declare formulae in cells and a heap more, and does not require Excel on the end workstation.

However, it may be overkill for your stated needs - it is not cheap starting at $999, and gets rapidly more expensive the wider you want to develop and deploy.

Indeed, it overkill. This is a small feature of a bigger application so I think I could work well without other solutions. — David Fornas, Jun 26 '12 at 11:08

Export and format data into Excel from C#

3 Answers3