We continue with our series of posts dedicated to seeing how to interact with different programs through their API from an application in NET6 written in C#.
In the previous post, we saw how to connect Microsoft Word and perform actions with it through an application in NET6. We commented that one of the frequent requirements of IoT is the integration of technologies. Among them, the APIs of the programs occupy a particularly important place.
We started with the API of Microsoft Office applications because, as expected from Microsoft itself, they are very well done and documented. Today we are going to see how to connect an Excel application.
For example, let’s imagine that we could use an Excel sheet to record the values we take from a sensor connected to an ESP32, create a series of graphs, and finally save this report in a file folder.
However, I must stress once again that, although it is an interesting option, it does not mean that it is the best option. For example, it would not be too correct for you to use an Excel sheet simply as a database. Or to avoid making a dashboard with graphs.
In each project, the best option from the overall perspective of the project should be analyzed. In most projects, using the API to connect to an Excel will not be the best option. But, at times, it may be a project requirement, and therefore it is interesting to be able to do it.
How to connect to Microsoft Excel with a NET6 application
Connecting to the Excel API will be very similar to the example we saw for Microsoft Word. First of all, we will create a simple NET6 console application.
Next, we must add the references to the Microsoft Excel assemblies. In the ‘COM’ references tab, look for “Microsoft Excel xx.0 Object Library”, where xx will be the version of Microsoft Excel installed on your computer.
Next, replace the code with the following,
using System.Reflection;
//Start Microsoft.Office.Interop.Excel and get Application object.
var applicationExcel = new Microsoft.Office.Interop.Excel.Application();
applicationExcel.Visible = true;
//Get a new workbook.
var workbook = (Microsoft.Office.Interop.Excel._Workbook)(applicationExcel.Workbooks.Add(Missing.Value));
var worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
//Add table headers going cell by cell.
worksheet.Cells[1, 1] = "A";
worksheet.Cells[2, 1] = "B";
worksheet.Cells[3, 1] = "C";
worksheet.Cells[4, 1] = "D";
//Fill B1:B4 with a formula(=RAND() * 100000) and apply format.
var range = worksheet.get_Range("B1", "B4");
range.Formula = "=RAND() * 100000";
range.NumberFormat = "$0.00";
applicationExcel.Visible = true;
applicationExcel.UserControl = true;
Console.ReadLine();This is a summarized and corrected version of the examples provided by Microsoft available at this link https://learn.microsoft.com/es-es/previous-versions/office/troubleshoot/office-developer/automate-excel-from-visual-c
If we run the code, we will see that a new Microsoft Excel application starts, fills the cells in the first row with the letters A-D, and then inserts a formula in the second row. Logically, in your project, you would put the appropriate logic.
As we can see, it is not difficult to connect to a Microsoft Excel from an application in NET6. From there we could perform all the actions that we normally have in an Excel sheet, such as adding tabs, formulas, graphs…
That’s all for today. In the next post, we will see how to do the same with Microsoft Outlook. Until next time!
Download the code
All the code from this post is available for download on Github. 