Here are some code pieces which is helpful in VSTO development. (Office Excel Development with Visual Studio .Net)
• To get ActiveWorkbook
Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;
• To get ActiveSheet
Excel.Worksheet NewActiveWorksheet = NewActiveWorkbook.ActiveSheet;
• To Show message in status bar
Application.StatusBar = "Report Generated.";
• To check whether a particular Add-In is installed
if (CheckAddIn("BabelFishHelper.xlam"))
{
// show message
}
private bool CheckAddIn(string Name)
{
// check whether the babelfish add in exists
foreach (Excel.AddIn ai in Application.AddIns)
{
if (ai.Name == Name)
{
return ai.Installed;
}
}
return false;
}
• Set a value to range
Excel.Range YearMonthTitle = NewActiveWorkbook.Sheets[1].Range["year_month_title"];
YearMonthTitle.Value2 = Title;
• Delete a sheet without confirmation
NewActiveWorkbook.Sheets[1].Delete();
• Delete a sheet without confirmation
Application.DisplayAlerts = false;
NewActiveWorkbook.Sheets[1].Delete();
Application.DisplayAlerts = true;
• Hide a sheet
Globals.Sheet1.Visible = Excel.XlSheetVisibility.xlSheetHidden;
• Set a name range read-only
Globals.Sheet9.Cells.Locked = false;
Excel.Range year_month_title = Globals.Sheet9.Range["year_month_title"];
year_month_title.Locked = true;
Globals.Sheet9.Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
• Set a sheet read-only
Globals.Sheet3.Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
NewActiveWorkbook.Sheets[1].Protect(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
• Clear clip board
Clipboard.Clear();
Sometimes you get bellow exception when you try to clear the clipboard. In that case use bellow code.
private void ClearClipBoard()
{
try
{
System.Threading.Thread.Sleep(10000);
Clipboard.Clear();
}
catch (Exception ex)
{
mLoggingMessage = "ClearClipBoard : Clipboard Clear Error";
Logging.ErrorLogging(mLoggingMessage, ex);
ClearClipBoard();
}
}
• Get used range of a sheet
Excel.Range settings_NamedRange = Globals.Sheet9.UsedRange;
• Get rows of used range
foreach (Excel.Range range in NamedRange.Rows)
{
// do process
}
• Get a sheet from a workbook
Excel.Worksheet newSheet = NewActiveWorkbook.Sheets[2];
• Setting a value to a cell
newSheet.Range[“A1”, missing].Value2 = “new value”;
• Setting a value to a cell without flicker
Application.ScreenUpdating = true;
newSheet.Range[“A1”, missing].Value2 = “new value”;
Application.ScreenUpdating = false;
• When a cell contains value and formula, remove the formula and retain only the value
Application.ScreenUpdating = true;
string Function = “=GetSingleValue("1/1/2011 00:00:00 AM", "1/1/2011 00:00:00 AM", "30s", "Raw")”;
newSheet.Range[CellId, missing].Value2 = Function.ToString();
Application.ScreenUpdating = false;
// remove the formula
Excel.Range CopyRange = (Excel.Range) newSheet.Range[CellId, missing];
CopyRange.Copy(System.Type.Missing);
Excel.Range PasteRange = (Excel.Range) newSheet.Range[CellId, missing];
PasteRange.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
• Get Row/Column id of a name range
Excel.Range NamedRange = newSheet.Range["year_month_title"];
int NamedRangeRow = NamedRange.Row;
int NamedRangeColumn = NamedRange.Column;
• Insert a new row (with formatting)
Excel.Range WholeRange = ((Excel.Range) newSheet.Rows[NamedRangeRow, System.Type.Missing]).EntireRow;
WholeRange.Copy(System.Type.Missing);
Excel.Range PasteRange = ((Excel.Range) newSheet.Rows[NamedRangeRow, System.Type.Missing]).EntireRow;
PasteRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, System.Type.Missing);
• Setting a value to a range
Excel.Range range1 = newSheet.Cells[NamedRangeRow, NamedRangeColumn];
range1.WrapText = true;
range1.Value = “new value”;
• Copy the whole workbook to a new workbook
Globals.ThisWorkbook.Worksheets.Copy();
Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;
• Copy a worksheet to a new workbook
Globals.Sheet1.Copy();
Excel.Workbook NewActiveWorkbook = Application.ActiveWorkbook;
Excel.Worksheet NewActiveWorksheet = NewActiveWorkbook.ActiveSheet;
• Setting a formula array to a cell
string StartCellId = “A1”;
string EndCellId = “B1”;
newSheet.Range[StartCellId, EndCellId].FormulaArray = Function.ToString();
• Break external links
NewActiveWorkbook .BreakLink("name_of _the_link", Excel.XlLinkType.xlLinkTypeExcelLinks);
View comments