Producing multiple worksheets in excel using C#

Question

I need to produce a C# program which will create two intro worksheets, and a 3rd worksheet that will be made repeatedly for every entry in a database. I am unsure which is the best way to produce multiple worksheets. This last sheet must be able to retain some formating and range names. Is copying this last sheet better, or is creating a new worksheet better?

Answer 1

It depends on what you mean by better. If it's easier to start with a clean slate for each sheet and just recopy the elements that are common, then creating new worksheets is better.

You could also create a temporary "template" sheet with the common elements already on it, and make copies of that.

Documentation for the Copy function is here:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.copy(VS.80).aspx

Answer 2

Thank you for your reply, is if possible to copy a "template" worksheet and retain the formating and ranged names form the original or not? – Slapdash 58 mins ago

Yes; that's possible. In the situation you describe I'd go for the copying option.

Answer 3

Copying the third worksheet would be best. Using Microsoft.Office.Interop.Excel.dll, this is what the copy could look like (mExcelWorkbook is the template Workbook you want to edit):

  /// <summary>
  /// Copy a worksheet to the specified index
  /// </summary>
  /// <param name="worksheetIndex"></param>
  private void CopyWorksheetToIndex(int worksheetIndex, int copyToIndex)
  {
     if (mExcelWorkbook != null)
     {
        // Reference to the worksheet
        Worksheet sheet_to_copy = (Worksheet)mExcelWorkbook.Worksheets[worksheetIndex];
        // Set the Tab Name to something
        sheet_to_copy.Name = "Copied From " + worksheetIndex + " To " + copyToIndex;
        // Copy the worksheet to the end of the worksheets
        sheet_to_copy.Copy(System.Reflection.Missing.Value, mExcelWorkbook.Worksheets[copyToIndex]);
     }
  }

Then you could just call like this:

  if (mExcelWorkbook != null)
  {
     CopyWorksheetToIndex(3, mExcelWorkbook.Worksheets.Count);
  }

Answer 4

SpreadsheetGear for .NET can do it.

See the "Worksheet with Chart to Multiple Worksheets with Charts" sample on the Excel Reporting samples page here (there are other samples on that page which might prove useful).

You can download a free trial here.

Disclaimer: I own SpreadsheetGear LLC