.Net – Reading Excel file in C# and transferring it to a matrix

There is an ERP joke, claiming that the third most popular button of an ERP system is “Export to Excel” (after OK and Cancel).

Thus, working with Excel from .Net is happens often. There are some pitfalls, that you should know about, but in general if you are seasoned VBA developer then you would be able to do miracles in C# with Excel the same way you do them in the VBEditor. The idea of the article is to show how to read Excel file to a C# array and how to perform some actions with it. In general, the task is to read the first 6 columns and 5 rows of the following file:

Then to color in red every cell, which has a cell on the left or on the right with the same value. Like this:

The reading task is a bit tough. The standard Excel range cannot be directly parsed to any C# Array, thus some tricks like this are needed:

Once the range is parsed, then the task becomes trivial – check the values and change background color:

Yup! That’s it. The execution is speeded up, because the animations are disabled and the Excel application is invisible:

And in general, the only prerequisite to work with the code below is to add the Microsoft.Office.Interop.Excel library from References in C#:

In general, this is all. Here comes the code, enjoy it:

🙂

About

VBA Developer

Tagged with: , , , ,