How to avoid exception when adding worksheets with same name?

8 Dec 20233 minutes to read

Microsoft Excel throws exception when adding worksheet with existing worksheet name in a workbook and XlsIO does the same. But in some case, if you want to add worksheets with the same name using XlsIO then you can avoid the exception in XlsIO by setting IgnoreSheetNameException property of IApplication as true.

The following code snippet shows how to add two worksheets with same name in a workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);

  //Set IgnoreSheetNameException property as true 
  application.IgnoreSheetNameException = true;

  //Create worksheets with same name
  IWorksheet sheet_1 = workbook.Worksheets.Create("Sheet");
  IWorksheet sheet_2 = workbook.Worksheets.Create("Sheet");

  //Saving the workbook as stream
  FileStream file = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);

  //Set IgnoreSheetNameException property as true 
  application.IgnoreSheetNameException = true;

  //Create worksheets with same name
  IWorksheet sheet_1 = workbook.Worksheets.Create("Sheet");
  IWorksheet sheet_2 = workbook.Worksheets.Create("Sheet");

  string fileName = "Output.xlsx";
  workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)

  'Set IgnoreSheetNameException property as true
  application.IgnoreSheetNameException = true

  'Create worksheets with same name
  Dim sheet_1 As IWorksheet = workbook.Worksheets.Create("Sheet")
  Dim sheet_2 As IWorksheet = workbook.Worksheets.Create("Sheet")

  Dim fileName As String = "Output.xlsx"
  workbook.SaveAs(fileName)
End Using

See Also