C# has features that simplify access to Office API objects. The new features include named and optional arguments, a new type called dynamic, and the ability to pass arguments to reference parameters in COM methods as if they were value parameters.
In this article, you use the new features to write code that creates and displays a Microsoft Office Excel worksheet. You write code to add an Office Word document that contains an icon that is linked to the Excel worksheet.
To complete this walkthrough, you must have Microsoft Office Excel 2007 and Microsoft Office Word 2007, or later versions, installed on your computer.
author: BillWagner ms.author: wiwagn ms.topic: include ms.date: 02/28/2023
To create a new console application
- Start Visual Studio.
- On the File menu, point to New, and then select Project. The New Project dialog box appears.
- In the Installed Templates pane, expand C#, and then select Windows.
- Look at the top of the New Project dialog box to make sure to select .NET Framework 4 (or later version) as a target framework.
- In the Templates pane, select Console Application.
- Type a name for your project in the Name field.
- Select OK.
The new project appears in Solution Explorer.
To add references
- In Solution Explorer, right-click your project's name and then select Add Reference. The Add Reference dialog box appears.
- On the Assemblies page, select Microsoft.Office.Interop.Word in the Component Name list, and then hold down the CTRL key and select Microsoft.Office.Interop.Excel. If you don't see the assemblies, you may need to install them. See How to: Install Office Primary Interop Assemblies.
- Select OK.
To add necessary using directives
In Solution Explorer, right-click the Program.cs file and then select View Code. Add the following using directives to the top of the code file:
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet1
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}To create a list of bank accounts
Paste the following class definition into Program.cs, under the Program class.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet2
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following code to the Main method to create a bankAccounts list that contains two accounts.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet3
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}To declare a method that exports account information to Excel
- Add the following method to the
Programclass to set up an Excel worksheet. Method Microsoft.Office.Interop.Excel.Workbooks.Add has an optional parameter for specifying a particular template. Optional parameters enable you to omit the argument for that parameter if you want to use the parameter's default value. Because you didn't supply an argument,Adduses the default template and creates a new workbook. The equivalent statement in earlier versions of C# requires a placeholder argument:ExcelApp.Workbooks.Add(Type.Missing).
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet4
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following code at the end of DisplayInExcel. The code inserts values into the first two columns of the first row of the worksheet.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet5
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following code at the end of DisplayInExcel. The foreach loop puts the information from the list of accounts into the first two columns of successive rows of the worksheet.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet7
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following code at the end of DisplayInExcel to adjust the column widths to fit the content.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet13
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Earlier versions of C# require explicit casting for these operations because ExcelApp.Columns[1] returns an Object, and AutoFit is an Excel Microsoft.Office.Interop.Excel.Range method. The following lines show the casting.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet14
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}C# converts the returned Object to dynamic automatically if the assembly is referenced by the EmbedInteropTypes compiler option or, equivalently, if the Excel Embed Interop Types property is true. True is the default value for this property.
To run the project
Add the following line at the end of Main.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet8
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Press CTRL+F5. An Excel worksheet appears that contains the data from the two accounts.
To add a Word document
The following code opens a Word application and creates an icon that links to the Excel worksheet. Paste method CreateIconInWordDoc, provided later in this step, into the Program class. CreateIconInWordDoc uses named and optional arguments to reduce the complexity of the method calls to Microsoft.Office.Interop.Word.Documents.Add and Microsoft.Office.Interop.Word.Selection.PasteSpecial. These calls incorporate two other features that simplify calls to COM methods that have reference parameters. First, you can send arguments to the reference parameters as if they were value parameters. That is, you can send values directly, without creating a variable for each reference parameter. The compiler generates temporary variables to hold the argument values, and discards the variables when you return from the call. Second, you can omit the ref keyword in the argument list.
The Add method has four reference parameters, all of which are optional. You can omit arguments for any or all of the parameters if you want to use their default values.
The PasteSpecial method inserts the contents of the Clipboard. The method has seven reference parameters, all of which are optional. The following code specifies arguments for two of them: Link, to create a link to the source of the Clipboard contents, and DisplayAsIcon, to display the link as an icon. You can use named arguments for those two arguments and omit the others. Although these arguments are reference parameters, you don't have to use the ref keyword, or to create variables to send in as arguments. You can send the values directly.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet9
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following statement at the end of Main.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet11
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Add the following statement at the end of DisplayInExcel. The Copy method adds the worksheet to the Clipboard.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet12
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Press CTRL+F5. A Word document appears that contains an icon. Double-click the icon to bring the worksheet to the foreground.
To set the Embed Interop Types property
More enhancements are possible when you call a COM type that doesn't require a primary interop assembly (PIA) at run time. Removing the dependency on PIAs results in version independence and easier deployment. For more information about the advantages of programming without PIAs, see Walkthrough: Embedding Types from Managed Assemblies.
In addition, programming is easier because the dynamic type represents the required and returned types declared in COM methods. Variables that have type dynamic aren't evaluated until run time, which eliminates the need for explicit casting. For more information, see Using Type dynamic.
Embedding type information instead of using PIAs is default behavior. Because of that default, several of the previous examples are simplified. You don't need any explicit casting. For example, the declaration of worksheet in DisplayInExcel is written as Excel._Worksheet workSheet = excelApp.ActiveSheet rather than Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet. The calls to AutoFit in the same method also would require explicit casting without the default, because ExcelApp.Columns[1] returns an Object, and AutoFit is an Excel method. The following code shows the casting.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet14
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}To change the default and use PIAs instead of embedding type information, expand the References node in Solution Explorer, and then select Microsoft.Office.Interop.Excel or Microsoft.Office.Interop.Word. If you can't see the Properties window, press F4. Find Embed Interop Types in the list of properties, and change its value to False. Equivalently, you can compile by using the References compiler option instead of EmbedInteropTypes at a command prompt.
To add additional formatting to the table
Replace the two calls to AutoFit in DisplayInExcel with the following statement.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet15
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}The Microsoft.Office.Interop.Excel.Range.AutoFormat method has seven value parameters, all of which are optional. Named and optional arguments enable you to provide arguments for none, some, or all of them. In the previous statement, you supply an argument for only one of the parameters, Format. Because Format is the first parameter in the parameter list, you don't have to provide the parameter name. However, the statement might be easier to understand if you include the parameter name, as shown in the following code.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/program.csSnippet16
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
using System;
using System.Collections.Generic;
//<Snippet1>
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
//</Snippet1>
namespace OfficeProgrammingWalkthrough
{
class Program
{
static void Main(string[] args)
{
//<Snippet3>
// Create a list of accounts.
var bankAccounts = new List<Account> {
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
//</Snippet3>
//<Snippet8>
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
//</Snippet8>
//<Snippet11>
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
//</Snippet11>
}
//<Snippet4>
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
//</Snippet4>
//<Snippet9>
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial( Link: true, DisplayAsIcon: true);
}
//</Snippet9>
static void DisplayInExcelFull(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
//<Snippet5>
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
//</Snippet5>
//<Snippet6>
// Establish column headings. Both parameters must be supplied in
// Visual C# 2008 or earlier versions.
//workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
//workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
// Move to the next row.
//workSheet.get_Range("A2", Type.Missing).Select();
//</Snippet6>
//<Snippet7>
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
//</Snippet7>
// Columns returns a Range object that represents all the columns
// on the active worksheet. AutoFit fits the width of the columns
// to their content.
//ExcelApp.Columns.AutoFit();
//<Snippet13>
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//</Snippet13>
//<Snippet14>
((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();
//</Snippet14>
//<Snippet15>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet15>
//<Snippet16>
// Call to AutoFormat in Visual C# 2010.
workSheet.Range["A1", "B3"].AutoFormat(Format:
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
//</Snippet16>
//<Snippet12>
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
//</Snippet12>
//ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
//dynamic val = Type.Missing;
//ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
//ExcelApp.get_Range("A5:C6").Copy();
}
// These examples are in Named and Optional, main topic. Can be deleted here.
static void TestExcelCom()
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;
// Using named and optional arguments, you can supply arguments for
// only the parameters for which you do not want to use the default
// value, and omit arguments for the other parameters. In the following
// call, a value is sent for only one parameter.
excelApp.get_Range("A1", "B4").AutoFormat( Format: myFormat );
// In Visual C# 2008 and earlier versions, you need to supply an argument for
// every parameter. The following call supplies a value for the first
// parameter, and sends a placeholder value for the other six. The
// default values are used for those parameters.
excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
//<Snippet2>
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
//</Snippet2>
}Press CTRL+F5 to see the result. You can find other formats in the listed in the Microsoft.Office.Interop.Excel.XlRangeAutoFormat enumeration.
Example
The following code shows the complete example.
sample · csharpdocs/csharp/advanced-topics/interop/snippets/OfficeInterop/walkthrough.csSnippet18
// Snippets 5000 Note:
// The container used for building our samples doesn't include the Office assemblies.
// This sample will generate a few errors in the CI build. Those are expected,
// so the build passes.
//
// If you update this sample, make sure to build it locally.
// Then, make sure no new errors are added.
//<Snippet18>
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
namespace OfficeProgrammingWalkthruComplete
{
class Walkthrough
{
static void Main(string[] args)
{
// Create a list of accounts.
var bankAccounts = new List<Account>
{
new Account {
ID = 345678,
Balance = 541.27
},
new Account {
ID = 1230221,
Balance = -127.44
}
};
// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);
// Create a Word document that contains an icon that links to
// the spreadsheet.
CreateIconInWordDoc();
}
static void DisplayInExcel(IEnumerable<Account> accounts)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a particular template.
// Because no argument is sent in this example, Add creates a new workbook.
excelApp.Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// Earlier versions of C# require explicit casting.
//Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";
var row = 1;
foreach (var acct in accounts)
{
row++;
workSheet.Cells[row, "A"] = acct.ID;
workSheet.Cells[row, "B"] = acct.Balance;
}
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
// Call to AutoFormat in Visual C#. This statement replaces the
// two calls to AutoFit.
workSheet.Range["A1", "B3"].AutoFormat(
Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
// Put the spreadsheet contents on the clipboard. The Copy method has one
// optional parameter for specifying a destination. Because no argument
// is sent, the destination is the Clipboard.
workSheet.Range["A1:B3"].Copy();
}
static void CreateIconInWordDoc()
{
var wordApp = new Word.Application();
wordApp.Visible = true;
// The Add method has four reference parameters, all of which are
// optional. Visual C# allows you to omit arguments for them if
// the default values are what you want.
wordApp.Documents.Add();
// PasteSpecial has seven reference parameters, all of which are
// optional. This example uses named arguments to specify values
// for two of the parameters. Although these are reference
// parameters, you do not need to use the ref keyword, or to create
// variables to send in as arguments. You can send the values directly.
wordApp.Selection.PasteSpecial(Link: true, DisplayAsIcon: true);
}
}
public class Account
{
public int ID { get; set; }
public double Balance { get; set; }
}
}
//</Snippet18>