//</Snippet7>
private void Sheet1_Startup(object sender, System.EventArgs e)
{
//<Snippet11>
System.Threading.Thread.CurrentThread.CurrentUICulture =
new System.Globalization.CultureInfo("de");
//</Snippet11>
//THIS SNIPPET USED BY MORE THAN ONE TOPIC
//----------------------------------------
//<Snippet10>
System.Threading.Thread.CurrentThread.CurrentUICulture =
new System.Globalization.CultureInfo(
Application.LanguageSettings.get_LanguageID(
Office.MsoAppLanguageID.msoLanguageIDUI));
//</Snippet10>
//<Snippet8>
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
this.Controls.AddNamedRange(this.Range["A1"], "namedRange1");
//</Snippet8>
//<Snippet9>
namedRange1.Value2 = Properties.Resources.ListCreateUnscheduledOrder;
//</Snippet9>
}
//---------------------------------------------------------------------
void Test2()
{
//<Snippet50>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.get_Range("A1"), "NamedRange1");
//</Snippet50>
//<Snippet51>
DateTime dt = DateTime.Now;
NamedRange1.Value2 = dt;
//</Snippet51>
//<Snippet52>
object value = NamedRange1.Value2;
if (value != null)
{
if (value is double)
{
dt = DateTime.FromOADate((double)value);
}
else
{
DateTime.TryParse((string)value, out dt);
}
}
MessageBox.Show(dt.ToString());
//</Snippet52>
}
private void CalculateTotal()
{
Microsoft.Office.Tools.Excel.NamedRange totalRange =
this.Controls.AddNamedRange(this.Range["B2", "B6"], "TotalRange");
int[] fields = new int[] { 1, 2, 3, 4, 5 };
totalRange.Subtotal(1, Excel.XlConsolidationFunction.xlSum, fields, missing, missing, Excel.XlSummaryRow.xlSummaryBelow);
}
private void Sheet1_Startup(object sender, System.EventArgs e)
{
nr = this.Controls.AddNamedRange(this.Range["A2"], "NamedRange1");
nr.Value2 = "This text was added by using code";
this.BeforeDoubleClick +=
new Excel.DocEvents_BeforeDoubleClickEventHandler(
DoubleClick);
}
//to run this example, add data to range b2, f6 before running this code
private void Listing_2_21()
{
Microsoft.Office.Tools.Excel.NamedRange valueRange = this.Controls.AddNamedRange(this.Range["B2", "F6"], "ValueRange");
valueRange.NumberFormat = "#,###.00";
//add some processing code here
//reset the range B2, F6 to its default format
valueRange.NumberFormat = "General";
//This line throws an exception because the currency keyword is not supported
//valueRange.NumberFormat = "Currency";
}
private void CreateNamedRange()
{
//<Snippet3>
Excel.Range range1 = Globals.Sheet1.Range["A1", "D5"];
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
Globals.Sheet1.Controls.AddNamedRange(range1, "ChartSource");
//</Snippet3>
//<Snippet4>
Globals.Sheet1.Controls.Remove("ChartSource");
//</Snippet4>
}
/// <summary>
/// This routine customizes the worksheet with prefetched data
/// </summary>
private void CustomizedData()
{
//set a namedrange
Microsoft.Office.Tools.Excel.NamedRange formattedRange =
this.Controls.AddNamedRange(this.Range["A1", "D10"], "formattedRange");
//note range names
Microsoft.Office.Tools.Excel.NamedRange preFilledRange =
this.Controls.AddNamedRange(this.Range["A2", "A9"], "PreFilledRange");
//formattedRange.ShrinkToFit = true;
formattedRange.ShowErrors();
//auto fill days of the week
Microsoft.Office.Tools.Excel.NamedRange firstCell =
this.Controls.AddNamedRange(this.Range["A2", missing], "FirstCell");
//note must seed the value
firstCell.Select();
firstCell.Value2 = "Monday";
//note must use the firstcell range that points to A1 for the autofill to work
firstCell.AutoFill(Application.get_Range("A2:A6", missing), Excel.XlAutoFillType.xlFillWeekdays);
preFilledRange.BorderAround(missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, missing);
preFilledRange.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1, true, false, true, false, true, true);
//get a reference to the header cell
Microsoft.Office.Tools.Excel.NamedRange MergeRange = this.Controls.AddNamedRange(this.Range["A1", "D1"], "MergeRange");
//format the header cell
MergeRange.EntireRow.Font.Bold = true;
MergeRange.Value2 = "Time Sheet [Week - " + DateTime.Now.ToString("hh/MM/yyyy") + "]";
MergeRange.EntireRow.Font.Background = Excel.XlBackground.xlBackgroundTransparent;
//turn off merged prompt dialog and then merge
Application.DisplayAlerts = false;
MergeRange.Merge(true);
Application.DisplayAlerts = true;
//setup the range for data entry
Microsoft.Office.Tools.Excel.NamedRange valueRange =
this.Controls.AddNamedRange(this.Range["B2", "B6"], "ValueRange");
valueRange.NumberFormat = "#,###.00";
valueRange.Font.Bold = true;
valueRange.BorderAround(missing, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, missing);
valueRange.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatColor2, true, false, true, false, true, true);
Microsoft.Office.Tools.Excel.NamedRange commentRange = this.Controls.AddNamedRange(this.Range["B2", missing], "CommentRange");
//add the comment
commentRange.AddComment("Enter your hours worked here.");
}
public void CreateVstoNamedRange(Excel.Range range, string name)
{
if (!this.Controls.Contains(name))
{
namedRange1 = this.Controls.AddNamedRange(range, name);
namedRange1.Selected += new Excel.DocEvents_SelectionChangeEventHandler(
namedRange1_Selected);
}
else
{
MessageBox.Show("A named range with this specific name " +
"already exists on the worksheet.");
}
}
//</Snippet9>
public void AddNamedRange2()
{
//<Snippet10>
Worksheet worksheet = Globals.Factory.GetVstoObject(Application.ActiveSheet);
Microsoft.Office.Tools.Excel.NamedRange namedRange1 = worksheet.Controls.AddNamedRange(
worksheet.Range["A1"], "MyNamedRange");
//</Snippet10>
//<Snippet11>
namedRange1.RefersTo = "=Sheet1!$A$1:$B$1";
//</Snippet11>
}
//</Snippet83>
//---------------------------------------------------------------------
//<Snippet21>
private void ListSheets()
{
int index = 0;
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
Globals.Sheet1.Controls.AddNamedRange(
Globals.Sheet1.Range["A1"], "NamedRange1");
foreach (Excel.Worksheet displayWorksheet in Globals.ThisWorkbook.Worksheets)
{
NamedRange1.Offset[index, 0].Value2 = displayWorksheet.Name;
index++;
}
}
private void Listing_2_15()
{
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
Controls.AddNamedRange(this.Range["A1", "A10"], "namedRange1");
Microsoft.Office.Tools.Excel.NamedRange namedRange2 =
Controls.AddNamedRange(this.Range["A1", missing], "namedRange2");
namedRange1.Merge(false);
namedRange2.Merge(false);
namedRange1.BorderAround(missing, Excel.XlBorderWeight.xlThin,
Excel.XlColorIndex.xlColorIndexAutomatic, missing);
namedRange1.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1,
true, false, true, false, true, true);
}
//</Snippet1>
private void Test1()
{
//<Snippet2>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
//</Snippet2>
//<Snippet3>
double width = (double)NamedRange1.ColumnWidth;
//</Snippet3>
//<Snippet4>
MessageBox.Show("Column width: " + width.ToString());
//</Snippet4>
}
//</Snippet15>
//---------------------------------------------------------------------
void Test2()
{
//<Snippet13>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.Range["A5", missing], "NamedRange1");
//</Snippet13>
//<Snippet14>
SetPropertyInternational(NamedRange1.InnerObject, "Formula", "=SUM(12, 34)");
//</Snippet14>
//<Snippet16>
string formula = (string)
GetPropertyInternational(NamedRange1.InnerObject, "Formula");
MessageBox.Show(formula);
//</Snippet16>
}
//---------------------------------------------------------------------
private void Test1()
{
//<Snippet28>
this.Range["A1"].Value2 = "Monday";
this.Range["A2"].Value2 = "Tuesday";
Microsoft.Office.Tools.Excel.NamedRange dayRange =
this.Controls.AddNamedRange(this.Range["A1", "A7"], "dayRange");
this.Range["A1", "A2"].AutoFill(dayRange.InnerObject, Excel.XlAutoFillType.xlFillDays);
//</Snippet28>
//<Snippet1>
Microsoft.Office.Tools.Excel.Chart employeeData;
employeeData = this.Controls.AddChart(25, 110, 200, 150, "employees");
employeeData.ChartType = Excel.XlChartType.xl3DPie;
// Gets the cells that define the data to be charted.
Excel.Range chartRange = this.get_Range("A5", "D8");
employeeData.SetSourceData(chartRange, missing);
//</Snippet1>
}
//</Snippet8>
//---------------------------------------------------------------------
private void Test2()
{
//<Snippet6>
Microsoft.Office.Tools.Excel.ListObject list1 =
this.Controls.AddListObject(this.Range["A1", "B3"], "list1");
//</Snippet6>
//<Snippet7>
list1.Resize(this.Range["A1", "C5"]);
//</Snippet7>
//<Snippet4>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
//</Snippet4>
//<Snippet5>
NamedRange1.RefersTo = "=Sheet1!$A$1:$B$1";
//</Snippet5>
//<Snippet3>
Microsoft.Office.Tools.Excel.NamedRange textInCell;
textInCell = this.Controls.AddNamedRange(this.get_Range("A1"), "cellText");
textInCell.Value2 = "Hello world!";
//</Snippet3>
//<Snippet2>
Microsoft.Office.Tools.Excel.ListObject employeeData;
employeeData = this.Controls.AddListObject(this.get_Range("$A$1:$D$4"), "employees");
//</Snippet2>
}
private void SetBindingContext()
{
namedRange1 = this.Controls.AddNamedRange(
this.Range["A1"], "namedRange1");
// Create a button that scrolls through the data
// displayed in the NamedRange.
button1 = this.Controls.AddButton(50, 20, 100, 20,
"button1");
button1.Text = "Display next item";
button1.Click += new EventHandler(button1_Click);
// Create a data table with one column.
ds = new DataSet();
DataTable table = ds.Tables.Add("Customers");
DataColumn column1 = new DataColumn("Names", typeof(string));
table.Columns.Add(column1);
// Add the names to the table.
DataRow row;
for (int i = 0; i < customerNames.Length; i++)
{
row = table.NewRow();
row["Names"] = customerNames[i];
table.Rows.Add(row);
}
// Create a new Binding that links the Value2 property
// of the NamedRange and the Names column.
//<Snippet4>
Binding binding1 = new Binding("Value2", ds, "Customers.Names", true);
namedRange1.DataBindings.Add(binding1);
//</Snippet4>
}
//<Snippet1>
private void Sheet1_Startup(object sender, System.EventArgs e)
{
Microsoft.Office.Tools.Excel.NamedRange nr =
this.Controls.AddNamedRange(this.Range["A2"], "NamedRange1");
nr.Value2 = "This text was added by using code";
}
//---------------------------------------------------------------------
void Test()
{
string getPasswordFromUser = "******";
//<Snippet47>
Excel.Range rng = this.Application.get_Range("A1");
rng.Value2 = "Range value";
//</Snippet47>
//<Snippet46>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.get_Range("A1"), "NamedRange1");
NamedRange1.Value2 = "Range value";
//</Snippet46>
//<Snippet44>
this.Application.ActiveWorkbook.Sheets.FillAcrossSheets(
this.Application.get_Range("rangeData"),
Excel.XlFillWith.xlFillWithAll);
//</Snippet44>
//<Snippet30>
this.dateComment.AddComment("Comment added " + DateTime.Now.ToString());
//</Snippet30>
//<Snippet29>
if (this.dateComment.Comment != null)
{
this.dateComment.Comment.Delete();
}
//</Snippet29>
//<Snippet28>
Globals.Sheet1.Unprotect(getPasswordFromUser);
//</Snippet28>
//<Snippet27>
Globals.Sheet1.Protect(getPasswordFromUser, true);
//</Snippet27>
//<Snippet26>
((Excel.Worksheet)Globals.ThisWorkbook.Sheets[1])
.Visible = Excel.XlSheetVisibility.xlSheetHidden;
//</Snippet26>
//<Snippet25>
Globals.Sheet1.Visible = Excel.XlSheetVisibility.xlSheetHidden;
//</Snippet25>
//<Snippet24>
int totalSheets = this.Application.ActiveWorkbook.Sheets.Count;
Globals.Sheet1.Move(Globals.ThisWorkbook.Sheets[totalSheets]);
//</Snippet24>
//<Snippet23>
Globals.Sheet1.PrintPreview();
//</Snippet23>
//<Snippet22>
Globals.Sheet1.PrintOut(1, 1, 2, true);
//</Snippet22>
//<Snippet20>
((Excel.Worksheet) this.Application.ActiveWorkbook.Sheets[1]).Select();
//</Snippet20>
//<Snippet19>
Globals.Sheet1.Select();
//</Snippet19>
//<Snippet18>
((Excel.Worksheet) this.Application.ActiveWorkbook.Sheets[4]).Delete();
//</Snippet18>
//<Snippet17>
Globals.Sheet1.Delete();
//</Snippet17>
//<Snippet16>
Globals.Sheet1.Copy(Globals.ThisWorkbook.Sheets[3]);
//</Snippet16>
//<Snippet15>
Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)Globals.ThisWorkbook.Worksheets.Add();
//</Snippet15>
//<Snippet14>
this.Application.DefaultFilePath = @"C:\temp";
//</Snippet14>
this.Application.DefaultFilePath = this.defaultSavePath.Value2.ToString();
//<Snippet13>
System.Windows.Forms.MessageBox.Show(this.Application.DefaultFilePath);
//</Snippet13>
this.defaultSavePath.Value2 = this.Application.DefaultFilePath;
//<Snippet9>
Excel.Workbook wb = this.Application.Workbooks[1];
// Before Book1 is saved:
wb = this.Application.Workbooks["Book1"];
// After Book1 is saved:
wb = this.Application.Workbooks["Book1.xlsx"];
//</Snippet9>
//<Snippet8>
Globals.ThisWorkbook.Activate();
//</Snippet8>
//<Snippet7>
((Microsoft.Office.Interop.Excel._Workbook)
this.Application.Workbooks[1]).Activate();
//</Snippet7>
//<Snippet3>
Globals.ThisWorkbook.Close(false);
//</Snippet3>
//<Snippet2>
this.Application.Workbooks.Open(@"C:\Test\YourWorkbook.xlsx");
//</Snippet2>
//<Snippet1>
Excel.Workbook newWorkbook = this.Application.Workbooks.Add();
//</Snippet1>
}
//---------------------------------------------------------------------
void Test3()
{
//<Snippet45>
Globals.Sheet1.CheckSpelling();
//</Snippet45>
//<Snippet32>
this.data2001.Group();
//</Snippet32>
//<Snippet33>
this.Application.get_Range("data2001");
this.Application.get_Range("data2002")
.Group();
this.Application.get_Range("dataAll")
.Group();
//</Snippet33>
//<Snippet34>
this.data2001.Ungroup();
this.data2002.Ungroup();
this.dataAll.Ungroup();
//</Snippet34>
//<Snippet35>
this.Application.get_Range("data2001").Ungroup();
this.Application.get_Range("data2002").Ungroup();
this.Application.get_Range("dataAll").Ungroup();
//</Snippet35>
//<Snippet48>
Microsoft.Office.Tools.Excel.NamedRange nr =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
nr.Value2 = "Hello World";
//</Snippet48>
//<Snippet82>
Globals.Sheet1.PrintPreview();
//</Snippet82>
//<Snippet81>
this.Application.Workbooks.OpenXML(@"C:\Test.xml");
//</Snippet81>
//<Snippet80>
this.Application.Workbooks.OpenText(@"C:\Test.txt",
missing, 3,
Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone,
missing, missing, missing, true, missing, missing, missing,
missing, missing, missing, missing, missing, missing);
//</Snippet80>
//<Snippet79>
this.fruitList.Range.Sort(
this.fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
this.fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending,
missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlYes, missing, missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
//</Snippet79>
//<Snippet78>
this.Fruits.Sort(
this.Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending,
this.Fruits.Columns[2, missing], missing, Excel.XlSortOrder.xlAscending,
missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo, missing, missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
//</Snippet78>
//<Snippet77>
this.Application.Calculate();
//</Snippet77>
//<Snippet75>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.get_Range("A1"), "NamedRange1");
//</Snippet75>
//<Snippet76>
NamedRange1.Calculate();
//</Snippet76>
//<Snippet74>
Globals.ThisWorkbook.SendMail("*****@*****.**", "July Sales Figures");
//</Snippet74>
//<Snippet73>
Microsoft.Office.Core.FileDialog fd =
this.Application.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
fd.AllowMultiSelect = true;
fd.Filters.Clear();
fd.Filters.Add("Excel Files", "*.xlsx;*.xlw");
fd.Filters.Add("All Files", "*.*");
if (fd.Show() != 0)
{
fd.Execute();
}
//</Snippet73>
//<Snippet70>
System.Security.Principal.WindowsIdentity user;
user = System.Security.Principal.WindowsIdentity.GetCurrent();
//</Snippet70>
//<Snippet71>
Microsoft.Office.Tools.Excel.NamedRange userID;
userID = this.Controls.AddNamedRange(this.Range["A1"], "userID");
userID.Value2 = user.Name;
//</Snippet71>
//<Snippet67>
Excel.Range rng2 = this.Application.get_Range("A1");
rng2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//</Snippet67>
//<Snippet65>
Microsoft.Office.Tools.Excel.NamedRange rng =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
//</Snippet65>
//<Snippet66>
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//</Snippet66>
//<Snippet54>
Microsoft.Office.Tools.Excel.NamedRange rangeStyles =
this.Controls.AddNamedRange(this.Range["A1"], "rangeStyles");
rangeStyles.Value2 = "'Style Test";
rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//</Snippet54>
//<Snippet53>
Excel.Style style = Globals.ThisWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
//</Snippet53>
}
