Basic but useful properties of Worksheet in Excel C# VSTO

Basic but useful properties of Worksheet in Excel C# VSTO

Worksheet:

A worksheet looks like a database having Rows and Columns. It is a collection of cells where you manipulate the data. Excel Worksheet has many useful properties, we will refer some of them with example here:

  • Visibility : Hiding/Unhiding worksheet

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Hidden example:

//get current workbook reference
excel.Workbook xlWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook;
xlWorksheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVeryHidden;

Visibility example:

//get current workbook reference
excel.Workbook xlWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook;
xlWorksheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;

Type : This property refers to a Sheet Type (Please refer post Get List of Unsupported Sheet in C# VSTO)

Check sheet type using “Type” property :

if (xlSheet.Type == Excel.XlSheetType.xlWorksheet)
{
	return true;
}

Hidden : This property ensures to Hide/Unhide Rows/Column in a Sheet

if (xlWorkbook.Sheets[sheetName].Rows[rowIndex].Hidden!=true)
{
	xlWorkbook.Sheets[sheetName].Rows[rowIndex].Hidden = true;
}

CodeName : This is another useful property which excel offers. Consider a scenario, You are referring a sheet name in your code as a common practice. What if user goes and rename the same sheet!!! Ah!!! the code gone messy; nothing works. The solution is to refer CodeName to make it dynamic. CodeName property refers Sheet Name defined by excel not defined by user in front end.

Getting Code Name of a Sheet:

public static string GetCodeNameFromSheetName(Excel.Workbook xlBook, string sheetName)
{
	try
	{
		Excel.Worksheet xlSheet = xlBook.Sheets[sheetName];
		return xlSheet.CodeName;
	}
	catch (Exception ex)
	{
		return string.Empty;
	}
}

Let’s reverse the logic to get Sheet Name form CodeName property:

public static Excel.Worksheet GetSheetNameFromCodeName(Excel.Workbook xlBook,string sheetName)
{
	try
	{
		return (Excel.Worksheet)xlBook.Sheets[sheetName];
	}
	catch (Exception ex)
	{
		return null;
	}
}

Next>>Special/Short Hand Operators in C#

Leave a Reply

Your email address will not be published.