Appending a single Excel sheet to an existing Excel Spreadsheet programatically

      

This can be done via a web-interface via Excel Interop commands

I was asked to take some user input, produce an Excel spreadsheet, then append it to an exisiting workbook that had multiple static spreadsheets then spit the Excel sheet out as a download.  Excel Interop processes are not that easy to work with, and can be quite a pain.  After fumbling around, I was able to come up with a (possibly rudimentary) way to do this.

 

Now this is just a small part of a much larger program that takes in user input, and saves it to a temporary directory as an Excel spreadsheet.  Inside that directory there is also another file that doesn't change that thsi will be appended to, I call this my 'base' file.  The snippet I am providing combines the files and saves them as a seperate new file before my program sends it to the user.

 

There is likely a better way to do this without all of the saving with a stream, but I am not sure how and am wide open for better solutions!  Anyways, here is the function I ended up using.  The inputs are:

  • baseFile is the path to the static file I am appending to
  • newSheet is the path to the new file that I am appending to the base
  • newFile is the path to the final appended sheet 

 

[code:c#]

protected void AppendSheet(string baseFile, string newSheet, string newFile)
{
    try
    {
        exc = new Microsoft.Office.Interop.Excel.Application();
        exc.Workbooks.Open(baseFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        exc.Workbooks[1].Sheets.Add(Type.Missing, exc.Workbooks[1].Sheets[1], 1, newSheet);
        if (System.IO.File.Exists(newFile)) System.IO.File.Delete(newFile);
        exc.Workbooks[1].SaveAs(newFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    }
    catch (Exception ex)
    {
        //handle your exception
    }
}

[/code]

Add comment

Loading