TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

<< Back to C-SHARP

C# Excel Interop Example

Handle Microsoft Excel files with the Microsoft.Office.Interop.Excel namespace. Read XLS files.
Excel creates XLS and XLSX files. These files are hard to read in C# programs. They are handled with the Microsoft.Office.Interop.Excel assembly. This assembly sometimes creates performance issues. Step-by-step instructions are helpful.
Interop. You must include a namespace to use Excel in your C# program. You have to use Visual Studio's GUI to add an assembly to your program to use Excel interop. Use the Add Reference command for this.

Tip: Add the Microsoft.Office.Interop.Excel assembly by going to Project -> Add Reference.

Class: First, we make a new C# class file in Visual Studio and you can call it something like ExcelInterop.

Methods: If you look at Excel interop objects in IntelliSense, you will see many functions. You will only need to use a few.

Class that stores Application: C# /// <summary> /// This class contains the Excel Interop code we need. /// It can be shared in many places to avoid duplication. /// </summary> class ExcelReaderInterop { /// <summary> /// Store the Application object we can use in the member functions. /// </summary> Application _excelApp; /// <summary> /// Initialize a new Excel reader. Must be integrated /// with an Excel interface object. /// </summary> public ExcelReaderInterop() { _excelApp = new Application(); } }
Interop class. We need a place to store our Excel information, so we use our special class. Put an Application object called _excelApp as a member. In the constructor, make the _excelApp a new Application object.Class

Open: Put a public function on the class, and it can use the _excelApp we already have. This code uses the Workbooks.Open method.

Next: Open a workbook with Workbooks.Open. Send the workbook we open to another function called ExcelScanInternal.

Finally: Close the workbook and release all the memory. You will need to deal with exceptions in the catch block.

Note: I don't have the information about all the detailed exceptions that can be thrown. Just catch them in one statement.

Catch
Method that opens Excel workbooks: C# /// <summary> /// Open the file path received in Excel. Then, open the workbook /// within the file. Send the workbook to the next function, the internal scan /// function. Will throw an exception if a file cannot be found or opened. /// </summary> public void ExcelOpenSpreadsheets(string thisFileName) { try { // // This mess of code opens an Excel workbook. I don't know what all // those arguments do, but they can be changed to influence behavior. // Workbook workBook = _excelApp.Workbooks.Open(thisFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // // Pass the workbook to a separate function. This new function // will iterate through the worksheets in the workbook. // ExcelScanInternal(workBook); // // Clean up. // workBook.Close(false, thisFileName, null); Marshal.ReleaseComObject(workBook); } catch { // // Deal with exceptions. // } }
Loop. To get data from workbooks, you will need to loop over the sheets. An Excel workbook has one or more sheets. In Excel, you can switch between sheets by clicking on the tabs on the bottom.

Tip: In Excel Interop, sheets are indexed starting at 1. This is similar to Visual Basic but not the C# language.

Example: The object array is directly usable in the C# language. Once you get the objects, you don't need to do any more Interop.

And: This provides a huge performance boost. There are benefits to reducing calls to Excel Interop.

Method that gets sheets: C# /// <summary> /// Scan the selected Excel workbook and store the information in the cells /// for this workbook in an object[,] array. Then, call another method /// to process the data. /// </summary> private void ExcelScanInternal(Workbook workBookIn) { // // Get sheet Count and store the number of sheets. // int numSheets = workBookIn.Sheets.Count; // // Iterate through the sheets. They are indexed starting at 1. // for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++) { Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum]; // // Take the used range of the sheet. Finally, get an object array of all // of the cells in the sheet (their values). You can do things with those // values. See notes about compatibility. // Range excelRange = sheet.UsedRange; object[,] valueArray = (object[,])excelRange.get_Value( XlRangeValueDataType.xlRangeValueDefault); // // Do something with the data in the array with a custom method. // ProcessObjects(valueArray); } }
Performance. If you do your Excel processing wrong, you could be waiting a long time to process even 10 spreadsheets at once. Using the Cells[] indexer on a range, or getting the range with the Range property or get_Range() is slow.

Note: This document is based on .NET 3.5. The approach shown here is the fastest one.

Excel interop benchmark Cells[]: 30.0 seconds get_Range(), Cells[]: 15.0 seconds UsedRange, get_Value(): 1.5 seconds [fastest]
Compatibility. There is a compatibility issue with the Excel program on the Apple Mac OS X platform. Excel stores OA dates in different formats on Macs and PCs. The code above shows one solution, which is to use get_Value() instead of Value2.FromOADate

Note: The function get_Value() returns objects that do not vary based on the original platform.

Charts. It is also possible to create charts directly in your Excel workbooks. The ChartWizard method is useful for this. Also, you can use new features from the .NET Framework 4.0 to simplify how you use Excel.

Example: The range of the data we want to chart is encoded in the topLeft and bottomRight constants. We use the ChartObjects property and Add a Chart.

Next: We set the range of the chart with SetSourceData. We use the XlChartType.xlLine enumerated constant and call ChartWizard().

Tip: Include the Microsoft.Office.Interop.Excel namespace by right-clicking on References and selecting Add Reference.

C# program that creates Excel chart from data using Microsoft.Office.Interop.Excel; class Program { const string fileName = "C:\\Book1.xlsx"; const string topLeft = "A1"; const string bottomRight = "A4"; const string graphTitle = "Graph Title"; const string xAxis = "Time"; const string yAxis = "Value"; static void Main() { // Open Excel and get first worksheet. var application = new Application(); var workbook = application.Workbooks.Open(fileName); var worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; // Add chart. var charts = worksheet.ChartObjects() as Microsoft.Office.Interop.Excel.ChartObjects; var chartObject = charts.Add(60, 10, 300, 300) as Microsoft.Office.Interop.Excel.ChartObject; var chart = chartObject.Chart; // Set chart range. var range = worksheet.get_Range(topLeft, bottomRight); chart.SetSourceData(range); // Set chart properties. chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; chart.ChartWizard(Source: range, Title: graphTitle, CategoryTitle: xAxis, ValueTitle: yAxis); // Save. workbook.Save(); } }
Notes, parameters. We use the named parameters and default parameters functionality in the .NET Framework 4.0. In older versions of the .NET Framework, you will need to specify the Missing value yourself.

Note: Some code contributed by Randall Kelsey was adapted for the charting example.

Notes, charts. The program requires an Excel document named Book1.xlsx located at C:\Book1.xlsx. In this file, you need to add four values in the first column. The program, upon execution, will create a chart based on those four values.

Tip: Many options can be changed to create different charts based on different data ranges.

Summary. We saw how to get started with Excel interop and avoid severe performance problems with the assembly. It is confusing to call the complicated Open method with Type.Missing in more places than necessary.
© TheDeveloperBlog.com
The Dev Codes

Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf