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:
|
1 2 3 4 |
Excel.Range startCell = wk.Cells[1, 1]; Excel.Range endCell = wk.Cells[rowsCount, colsCount]; Excel.Range currentRange = wk.get_Range(startCell, endCell).Cells; object[,] matrixRead = (object[,])currentRange.Value; |
Once the range is parsed, then the task becomes trivial – check the values and change background color:
|
1 2 3 4 5 6 7 8 9 10 11 |
for (int rows = 1; rows <= rowsCount; rows++) { for (int cols = 1; cols < colsCount; cols++) { if (matrixRead[rows,cols].ToString()==matrixRead[rows,cols+1].ToString()) { currentRange.Cells[rows, cols].interior.color = Excel.XlRgbColor.rgbRed; currentRange.Cells[rows, cols+1].interior.color = Excel.XlRgbColor.rgbRed; } } } |
Yup! That’s it. The execution is speeded up, because the animations are disabled and the Excel application is invisible:
|
1 2 |
excel.Visible = false; excel.EnableAnimations = false; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
using System; using Excel = Microsoft.Office.Interop.Excel; class StartUp { static void Main() { string filePath = @"C:\Sample.xlsx"; int rowsCount = 5; int colsCount = 6; Excel.Application excel = new Excel.Application(); excel.Visible = false; excel.EnableAnimations = false; Excel.Workbook wkb = Open(excel, filePath); Excel.Worksheet wk = (Excel.Worksheet)excel.Worksheets.get_Item(1); Excel.Range startCell = wk.Cells[1, 1]; Excel.Range endCell = wk.Cells[rowsCount, colsCount]; Excel.Range currentRange = wk.get_Range(startCell, endCell).Cells; currentRange.Interior.Color = Excel.XlRgbColor.rgbWhite; object[,] matrixRead = (object[,])currentRange.Value; bool[,] matrixResult = new bool[rowsCount+1,colsCount+1]; for (int rows = 1; rows <= rowsCount; rows++) { for (int cols = 1; cols < colsCount; cols++) { if (matrixRead[rows,cols].ToString()==matrixRead[rows,cols+1].ToString()) { matrixResult[rows, cols] = true; matrixResult[rows, cols + 1] = true; } } } for (int rows = 1; rows <= rowsCount; rows++) { for (int cols = 1; cols <= colsCount; cols++) { if (matrixResult[rows, cols]) { currentRange.Cells[rows, cols].interior.color = Excel.XlRgbColor.rgbRed; } } } excel.EnableAnimations = true; wkb.Close(true); excel.Quit(); Console.WriteLine("Finished!"); } private static Excel.Workbook Open(Excel.Application excelInstance, string fileName, bool readOnly = false, bool editable = true, bool updateLinks = true) { Excel.Workbook book = excelInstance.Workbooks.Open( fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return book; } } |
🙂