SoftArtisans.OfficeWriter.Shared.SARuntimeException: Cannot have a hidden Worksheet selected

Question

I am using softarisans officewriter in my web API. I am trying to copy sheet data from source excel file to destination excel file. I am facing the above error when saving the destination excel. I have added the piece of code for reference. Any help would be appreciated.

Worksheet PrevSheet = SourceExcel.Workbook.GetWorksheet(Sheet.sheetName); DestinationExcel.Workbook.Worksheets.Delete(PrevSheet.Name); DestinationExcel.Workbook.Worksheets.CopySheet(PrevSheet, PrevSheet.Position, PrevSheet.Name);

DestinationExcel.Save(); //Error in this line

score 1 · Answer 1 · edited Feb 12 '15 at 22:43

The error message is pretty clear here "Cannot have a hidden Worksheet selected.". This is because in Excel a workbook always has 1 visible worksheet that is selected. This is why you can't delete every worksheet in the workbook.

What is likely happening is through your workflow a hidden worksheet gets selected. OfficeWriter always tries to keep a sheet selected, but since we are working with the file programmatically you can get into a temp bad state. (for example delete all your visible sheets, but then add a visible one right before save)

Without the workbook and actually running your use case, this is what I believe is happening.

//Here you are deleting a worksheet, which might mean that the destination workbook no longer has visible sheets. 
DestinationExcel.Workbook.Worksheets.Delete(PrevSheet.Name);

//Now you are copying in a new worksheet from a source workbook, it might be visible.
 DestinationExcel.Workbook.Worksheets.CopySheet(PrevSheet,PrevSheet.Position,PrevSheet.Name);

A couple of ways to potentially fix this

Change the code flow, so you always have a visible sheet in the workbook, OfficeWriter will attempt to select the next visible sheet if your selected sheet is deleted.
Explicitly set your selected worksheet see http://wiki.softartisans.com/pages/viewpage.action?pageId=5308872 such as DestinationExcel.Workbook.Worksheets[0].Select();

You could do this in a more generic method where you inspect the worksheets visibility (http://wiki.softartisans.com/display/EW9/Worksheet.Visibility) and make sure you select one that is visible

Dan SoftArtisans

SoftArtisans.OfficeWriter.Shared.SARuntimeException: Cannot have a hidden Worksheet selected

1 Answers1