Working with Excel and C#

I had the task of building a small app to re-order the tabs in a spreadsheet and get rid of those which are unnessary. However doing this in C# is surprisingly difficult mainly because the Microsoft.Office.Interop.Excel was intended to work primarily with VB/ VBA but was customised to work with C# as well. The task was really simple in terms of functionality as it would need to read an xml file which contained the required worksheets (tabs) in the order which we want them, so the app would need to iterate through that list and copy the specific worksheet to another workbook and save it. Unfortunately i ran into many problems in getting this to work, such as the ‘missing’ value needed to be substituted for (Type.Missing), the worksheet couldn’t be copied, it was difficult to add a new worksheet to the new workbook, the workbook would have 3 empty worksheets by default and you couldn’t delete them all at the beginning because a workbook needs to have at least one worksheet etc. Here are the codes i ended up doing:

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Xml;
 using System.Configuration;

// import the excel library
 using Microsoft.Office.Interop.Excel;

namespace Tam.Spreadsheet
 {
 public class Process
 {
 public delegate void OnFinishProcessingHandler(string message);
 public static event OnFinishProcessingHandler OnFinishProcessing;

public static void Start()
 {
 // This is going to be the message that we are going to build up as we go along and convey to the user
 StringBuilder message = new StringBuilder();

// start a new excel application
 Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();

try
 {
 // load the funds document
 XmlDocument fundsData = new XmlDocument();
 fundsData.Load(ConfigurationManager.AppSettings["funds"]);

// store the fund codes (tabs) in the order they are presented in the xml file
 List<string> funds = new List<string>();
 XmlNode root = fundsData.DocumentElement;
 XmlNodeList allFunds = root.SelectNodes("//funds/fund");

message.Append("Reading xml file...\r\n");

foreach (XmlNode fund in allFunds)
 {
 funds.Add(fund.Attributes["code"].Value);
 }

xlsApp.DisplayAlerts = false; // please do not bother with the alerts!
 xlsApp.Visible = false; // no need to open the excel program visually (ie on the screen)
 xlsApp.SheetsInNewWorkbook = 1; // set the number of sheets to 1 (default is 3)

if (xlsApp != null)
 {
 message.Append("Processing excel file...\r\n");

// create a new workbook to work with...
 Workbook newWorkbook = xlsApp.Workbooks.Add(Type.Missing); // (XlWBATemplate.xlWBATWorksheet)

// the original workbook
 Workbook originalWorkbook = xlsApp.Workbooks.Open(ConfigurationManager.AppSettings["filename"],
 0,
 false,
 5,
 "",
 "",
 false,
 Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
 "",
 true,
 false,
 0,
 true,
 false,
 false);

// We'll need a count for the worksheets
 int count = 0;

// which workbooks do we want?
 foreach (string sheetName in funds)
 {
 count++;

// do we have the required sheet
 bool IsSheetAvailable = false;

foreach (Worksheet ws in originalWorkbook.Worksheets)
 {
 if (ws.Name.Trim().ToLower() == sheetName.Trim().ToLower())
 {
 // Yupie, the sheet is available for copy!
 IsSheetAvailable = true;

ws.Copy(newWorkbook.Worksheets[count], Type.Missing);

// no need to search any further for this sheet
 break;
 }
 }

if (!IsSheetAvailable)
 {
 // the worksheet couldn't be found, but we still need to create a tab for it
 // with the actual name of the tab and the word 'missing' prepended to it
 message.Append("Missing Worksheet : " + sheetName + "\r\n");

// add a blank worksheet to the workbook
 // Note: we are making a copy of the last worksheet (which is blank) to the workbook
 Worksheet newSheet = new Worksheet();
 newSheet = (Worksheet)newWorkbook.Worksheets[newWorkbook.Worksheets.Count];
 newSheet.Copy(newWorkbook.Worksheets[count], Type.Missing);

// Now we need to rename the worksheet to the actual name (as the name would be called 'Sheet1 (2)' by default)
 Worksheet currentSheet = new Worksheet();
 currentSheet = (Worksheet)newWorkbook.Worksheets[count];
 currentSheet.Name = "Missing fund " + sheetName;
 }
 }

// Since a workbook needs to have at least one worksheet as a rule of thumb and we have now added
 // all the sheets we are interested in, we need to get rid of the default sheet which was created for us
 // and this sheet would be the last one as we have been adding sheets to the beginning of the workbook
 Worksheet unwantedSheet = new Worksheet();
 unwantedSheet = (Worksheet)newWorkbook.Worksheets[newWorkbook.Worksheets.Count];
 unwantedSheet.Delete();

message.Append("Saving new excel file...\r\n");

// save the new excel file now...
 newWorkbook.SaveAs(ConfigurationManager.AppSettings["newFilename"],
 Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
 Type.Missing,
 Type.Missing,
 false,
 false,
 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing,
 Type.Missing);

// close the workbook
 xlsApp.Workbooks.Close();

// exit the excel application
 xlsApp.Quit();

message.Append("Successfully processed excel file...\r\n");
 }
 else
 {
 message.Append("Please check that Microsoft Excel is installed on this machine. Aborting processing...");
 }

message.Append("*** Finished ***");

}
 catch (Exception ex)
 {
 message.Append("ERROR: " + ex.ToString());

// exit the excel application
 xlsApp.Quit();
 }

if (OnFinishProcessing != null)
 {
 OnFinishProcessing(message.ToString());
 }
 //return message.ToString();
 }
 }
 }