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);
3

View comments

In this tutorial I’m going to show how to:
• Create an ASP.NET Core MVC Application and Publish to Azure
• Create/Purchase a Custom Domain from a Custom Domain Provider
• Configure Custom Domain on Azure
Alt Text
You can find the PDF version of this tutorial here.
You can find all my Azure/.Net tutorials here and here.
Enjoy !!
0

Add a comment

About Me
About Me
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.