I’m constantly amazed at the time that Microsoft wastes on OLE automation, when it typically isn’t even the best way of integrating with Excel. Easily the best way to generate (or read, for that matter) an Excel document is to use NPOI. (There are competitors, such as ExcelLibrary, but they lack the depth of features of NPOI.) The version on NuGet is pretty up to date. The one catch is that the API is hermetic. Here’s some tips:
- You need to create a HSSFWorkbook.
- You should create any styles and fonts before you start to process the cells. (You don’t have to, but it’ll be easier to understand the code.)
- The correct way to create a custom data format is workbook.CreateDataFormat().GetFormat(“0.00”), which will return a short. You then assign the number to the dataformat of the cell style.
It’s also blindingly fast compared to most other techniques. Finally, there’s quite extensive questions on stackoverflow about it.
Colour Coding 