Linking Excel In C#

Question

So I'm working in Microsoft Visual Studio 2015 and want to be able to link the project to a Excel Spreadsheet.

What I want to achieve is this; simply be able to create a program where I can use the number in a specific cell and then do some calculations with it. As an example, take cell D4 and then create a poisson distribution using the value as the mean.

- https://support.microsoft.com/en-us/kb/302084 - http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm — Ray Krungkaew, Sep 22 '16 at 11:06
Ask your favorite search engine. Keywords are "c# excel" and/or "c# office interop" — Axel Kemper, Sep 22 '16 at 11:06
you can use openXml [link] (https://msdn.microsoft.com/en-us/library/office/bb448854.aspx) which does not require any license. If you use excel Interop it requires license to use it on client machine. — Nikunj Ratanpara, Sep 22 '16 at 11:27

JohnG · Accepted Answer · 2016-09-22T14:06:12.520

You need to add a reference to Microsoft.Office.Core and Microsoft.Office.Interop.Excel in the projects References. Then add the references to your class.

using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

Then you can open/create/manipulate excel sheets in your code. lots of info for that on the web.

Update to comment--

This example is opening a new worksheet to save when done.

Excel.Application xlApp;
Excel.Workbook myWorkBook;
Excel.Worksheet myWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
myWorkBook = xlApp.Workbooks.Add(misValue);
myWorkSheet =  (Excel.Worksheet)myWorkBook.Worksheets.get_Item(1);

Done some work on the sheet

myWorkSheet.Cells[curXLRow, 5] = "PF";
myWorkSheet.Cells[curXLRow, 6] = "PA";

Then Save the file

string fileName = "C:\\MyFileName.xlsx";
myWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
myWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(myWorkSheet);
releaseObject(myWorkBook);
releaseObject(xlApp);

Hope this helps. I dont have an example to open an existing excel file. But look up:

xlApp.Workbooks.Open(lots of parameters...);

i used this but confused to what I put in the area marked Excel.Worksheet excelSheet = WHAT DO I PUT HERE Excel.Range rng = (Excel.Range)excelSheet.Cells[10, 10]; — George Perkins, Sep 22 '16 at 11:47

score 0 · Answer 2 · edited May 23 '17 at 10:27

As already suggested, it is recommended to use Open XML SDK 2.5 with EPPlus, rather than Interop. The reasons are explained here.

Getting started with EPPlus is very easy as shown here in a few examples:

open the Excel file using FileInfo
create a ExcelPacakge from it
get worksheet reference from Workbook.Worksheets["WorksheetName"]
use worksheet reference to read and write cell values and formulas and much more

Linking Excel In C#

2 Answers2

Linked