C# and Excel best practices [closed]

Question

I am doing a lot of MS Excel interop i C# (Visual Studio 2012) using Microsoft.Office.Interop.Excel. It requires a lot of tiresome manual code to include Excel formulas, doing formatting of text and numbers, and making graphs.

I would like it very much if any of you have some input on how I do the task better. I have been looking at Visual Studio Tools for Office, but I am uncertain on its functions. I get it is required to make Excel add-ins, but does it help doing Excel automation?

I have desperately been trying to find information on working with Excel in Visual Studio 2012 using C#. I did found some good but short tutorials. However I really would like a book an the subject to learn the field more in depth regarding functionality and best practices. Searching Amazon with my limited knowlegde only gives me book on VSTO using older versions of Visual Studio.

I would not like to use VBA. My applications use Excel mainly for visualizing compiled from different sources. I also to data processing where Excel is not required. Futhermore, I can write C# but not VB.

Answer 1

I've used this before, found it very good and very easy for reading & writing Excel sheets. Not sure about the more advanced stuff (It's there, I just never tried it) but give it a look. Works on the OOXML format (.xlsx) and there's no COM interop or VBA or anything, pure .Net

http://epplus.codeplex.com/

Example code to make a pie-chart:

//ExcelPackage is a class within the EPPlus DLL
var p = new ExcelPackage();
p.Workbook.Worksheets.Add("sheetName")
var ws = p.Workbook.Worksheets.First();

//Add the piechart
var pieChart = ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D) as ExcelPieChart;

//Set top left corner to row 1 column 2
pieChart.SetPosition(1, 0, 2, 0);
pieChart.SetSize(400, 400);
pieChart.Series.Add(ExcelRange.GetAddress(4, 2, row-1, 2), ExcelRange.GetAddress(4, 1, row-1, 1));    
pieChart.Title.Text = "Extension Size";

//Set datalabels and remove the legend
pieChart.DataLabel.ShowCategory = true;
pieChart.DataLabel.ShowPercent = true;
pieChart.DataLabel.ShowLeaderLines = true;
pieChart.Legend.Remove();

Just filling in rows, columns and formatting is simpler than the above :)

Answer 2

see http://exceldna.codeplex.com/, used extensively for this specific purpose with a big user base and being maintained and enhancing.

Also a few tips:

• Create helpers to format specific ranges based on injected conditions
• Use R1C1 references to offset or your code will become a mess
• Try to use named ranges as often as possible as this creates the post formatting easier
• Abstract away your code into style classes and do not add it "in line" with your logic