CODEWITHSUNDEEP
c#excelvisual-studio-2017excel-interop
MrVimes
MrVimes

Reputation: 3312

Using the available version of Excel for C# app development

Is there an elegant/straightforward way to make use of Microsoft Excel in a C# app, without being tied down to a specific version of Microsoft Excel?

Background...

I have in the past written scripts using vbscript which use Microsoft Excel (if installed) to automate the process of generating excel spreadsheets. set xl = createObject("Excel.Application")

The scripts work seamlessly regardless of which excel version is installed in my experience/scenario (I have Office 365 with Excel 2016 installed on my development machine, and my scripts run on a Microsoft Windows 2008 R2 server running a standalone copy of Excel 2010)

I wish to step things up a bit and start developing C# apps (winforms or console) that do the same job. I have developed a test app which can use the version of excel installed on my development machine. I need the app to be able to run on the Windows Server 2008 R2 server running Excel 2010.

Project->Add Reference

COM

"Microsoft Excel 16.0 Object Library"

Then add the using statement... using Excel = Microsoft.Office.Interop.Excel;

Then some basic winforms test code...

    public Form1()
    {
        InitializeComponent();

        SimpleExcelTest();
    }

    void SimpleExcelTest() {

        Excel.Application xl = new Excel.Application();
        Excel.Workbook wb = xl.Workbooks.Add();
        Excel.Worksheet ws = wb.Worksheets[1];

        ws.Cells[1, 1] = "Testing";
        wb.SaveAs("C:\\temp\\test.xlsx");
        wb.Close();
    }

Which works fine.

But this references the specific version of Excel running on my machine. This won't run on the target machine. The application crashes with the following error...

Problem signature:

Problem Event Name: CLR20r3

Problem Signature 01: AutomatedReportGenerator.exe

Problem Signature 02: 1.0.0.0

Problem Signature 03: 59552369

Problem Signature 04: AutomatedReportGenerator

Problem Signature 05: 1.0.0.0

Problem Signature 06: 59552369

Problem Signature 07: 2

Problem Signature 08: 9c

Problem Signature 09: PSZQOADHX1U5ZAHBHOHGHLDGIY4QIXHX

OS Version: 6.1.7601.2.1.0.272.7

Locale ID: 2057

Additional Information 1: 0a9e

Additional Information 2: 0a9e372d3b4ad19135b953a78882e789

Additional Information 3: 0a9e

Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

Edit:I will put in a try catch to see if I can catch a more useful error message, and also try the library that two answers have suggested then accept the earliest answer. Later today.

Is there an alternative/better way to use Excel in a C# App to generate spreadsheets, regardless of which version of excel is installed (as long as its recent enough to be compatible with the code)

Perhaps a C# equivalent of vbscript's set xl = createObject("Excel.Application")

Upvotes: 0

Views: 999

Answers (2)

Sujit Singh
Sujit Singh

Reputation: 831

Try using EPPLus, it's super easy to use and comes up with many useful features like it takes care of special characters in cells, etc. You can download EPPlus from http://epplus.codeplex.com/ . Nuget page of EPPlus is https://www.nuget.org/packages/EPPlus

I would not recommend using Microsoft Excel Interop because of it's complexities.

One code sample is 

 using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName)))
            {
               ExcelWorksheet ws = pck.Workbook.Worksheets[excelSheetName];

                ws.Cells["A" + rowCnt].Value = firstName;
                ws.Cells["B" + rowCnt].Value = lastName;
                ws.Cells["C" + rowCnt].Value = occupation;

                pck.Save();

            }

Thanks.

Upvotes: 2

Michael Weinand
Michael Weinand

Reputation: 401

Have you tried looking into not using Excel Interop and using a library that can read/write the format? EPPlus is one I've used countless times and it has a lot of functionality: https://www.nuget.org/packages/EPPlus

Upvotes: 3

Related Questions