2 things:
- defer the placement of the chart into the sheet, until after you are
finished designing the chart
- eliminate the setting of the title on the xlSeriesAxis (valid only for 3D
charts)
Example
// AutomateExcel.cs
//
// requires Office PIAs, specifically Microsoft.Office.Interop. Excel.dll
//
// download them all from:
//
http://www.microsoft.com/downloads/...&displaylang=en
//
// build with:
// C:/WINDOWS/ Microsoft.NET/Framework/v1.1.4322/ csc.exe /t:exe /debug+
// /r:Microsoft.Office.Interop.Excel.dll
/out:AutomateExcel.exe AutomateExcel.cs
//
//
// NB: my Excel PIA is available in
//
C:/windows/assembly/GAC/Microsoft.Office.Interop.Excel/10.0.4504.0__31bf3856
ad364e35/Microsoft.Office.Interop.Excel.dll
//
//
using System;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Excel = Microsoft.Office.Interop.Excel;
class AutomateExcel {
static object missing = System.Reflection.Missing.Value;
public static int Main() {
try {
Console.WriteLine ("Creating new Excel.Application");
Excel.Application app = new Excel.Application();
if (app == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
}
Console.WriteLine ("Making application visible");
app.Visible = true;
Console.WriteLine ("Getting the workbooks collection");
Excel.Workbooks workbooks = app.Workbooks;
Console.WriteLine ("Adding a new workbook");
// The following line is the temporary workaround for the LCID problem
// Excel._Workbook xlWb =
workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Excel._Workbook xlWb = workbooks.Add(missing);
xlWb.Saved = true; // override
Excel.Sheets sheets = xlWb.Worksheets;
Excel._Worksheet xlWks = (Excel._Worksheet) sheets.get_Item(1);
Console.WriteLine ("filling cells in sheet #1");
// This paragraph puts the value 5 to the cell A1
Excel.Range range1;
range1= xlWks.get_Range("A1", Missing.Value);
range1.Value2 = "Experimental Data Reading";
range1.AddComment("This is sample data generated by a C# program at "
+ System.DateTime.Now);
range1.RowHeight=20;
//range1.Interior.Color=??;
range1= xlWks.get_Range("A1", "H1");
range1.Interior.ColorIndex=11; // 9=maroon, dkblue=11
range1.Font.Bold = true;
range1.Font.Size = 18;
range1.Font.ColorIndex = 2; // 2=white?, see
http://www.geocities.com/davemcritchie/excel/colors.htm
const int nRows = 4;
const int nCols = 20;
const string upperLeftCell= "A2";
int endRowNum= System.Int32.Parse(upperLeftCell.Substring(1)) +
nRows - 1;
char endColumnLetter=
System.Convert.ToChar(System.Convert.ToInt32(upperLeftCell[0]) + nCols - 1);
string upperRightCell= System.String.Format("{0}{1}",
endColumnLetter,System.Int32.Parse(upperLeftCell.Substring(1))) ;
string lowerRightCell= System.String.Format("{0}{1}",
endColumnLetter,endRowNum);
// This paragraph sends single dimensional array to Excel
range1 = xlWks.get_Range(upperLeftCell, upperRightCell);
int[] array2 = new int [nCols];
for (int i=0; i < array2.Length; i++) {
array2[i] = i+1;
}
range1.Value2 = array2;
// This paragraph sends a 2D array to Excel
range1 = xlWks.get_Range(upperLeftCell, lowerRightCell);
int[,] array3 = new int [nRows, nCols];
for (int i=0; i < array3.GetLength(0); i++) {
for (int j=0; j < array3.GetLength(1); j++) {
array3[i, j] = i*i*j + 3*i*j + 2*j*j - j*j*j/8 - j*j*j*i/28;
}
}
range1.Value2 = array3;
Console.WriteLine ("building a chart from that data");
//Graph erstellen
Excel._Chart xlChart =
(Excel.Chart)xlWb.Charts.Add(missing,missing,missing,missing);
xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
xlChart.SetSourceData(xlWks.get_Range(upperLeftCell, lowerRightCell),
Excel.XlRowCol.xlRows); // xlColumns
// do not use this here; move it after finished designing the chart
//xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Sheet1");
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = "CSD Overtime Verlauf";
Excel.Axis axis;
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Monat";
axis.HasMajorGridlines = true;
axis.HasMinorGridlines = false;
#region NOTUSED_1
#if NEVER_TRUE
// see
// http://msdn.microsoft.com/library/en-us/ vbaxl10/ html/xlmthAxes.asp
//
// Excel.XlAxisType.xlSeriesAxis is valid only for 3D charts !
//
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = false;
axis.HasMajorGridlines = false;
axis.HasMinorGridlines = false;
#endif
#endregion
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Anzahl Stunden";
axis.HasMajorGridlines = true;
axis.HasMinorGridlines = false;
xlChart.WallsAndGridlines2D = false;
xlChart.HasLegend = true;
xlChart.Legend.Select();
xlChart.Legend.Position =
Excel.XlLegendPosition.xlLegendPositionRight;
Console.WriteLine ("Moving the chart to sheet1:");
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");
Console.WriteLine ("Press ENTER to finish the sample:");
Console.ReadLine();
try {
// If user interacted with Excel it will not close when the app
object is destroyed, so we close it explicitly
xlWb.Saved = true; // override
app.UserControl = false;
app.Quit();
} catch (COMException) {
Console.WriteLine ("User closed Excel manually, so we don't have to
do that");
}
}
catch (System.Exception ex) {
Console.WriteLine ("Exception : " + ex.ToString());
}
Console.WriteLine ("All done");
return 100;
}
}
"Marc Eggenberger" <nw1@devnull.ch> wrote in message
news:MPG.19722188bcc03f8298968a@news.cis.dfn.de...
> Hi there.
>
> I want to create a graph in Excel via C#.
>
> I recorded a macro in Excel to get the basics with the chart. I got the
> following in the Editor in Excel:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 06.07.2003 by *
> '
>
> '
> Charts.Add
> ActiveChart.ChartType = xl3DAreaStacked
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:W15"),
> PlotBy _
> :=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = "CSD Alstom Overtime Verlauf"
> .Axes(xlCategory).HasTitle = True
> .Axes(xlCategory).AxisTitle.Characters.Text = "Monat"
> .Axes(xlSeries).HasTitle = False
> .Axes(xlValue).HasTitle = True
> .Axes(xlValue).AxisTitle.Characters.Text = "Anzahl Stunden"
> End With
> With ActiveChart.Axes(xlCategory)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlSeries)
> .HasMajorGridlines = False
> .HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlValue)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> ActiveChart.WallsAndGridlines2D = False
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlRight
> ActiveSheet.Shapes("Chart 1").IncrementLeft -80.25
> ActiveSheet.Shapes("Chart 1").IncrementTop 75#
> End Sub
>
>
> I then tried to translate this to c#.
> I did this in c#
> (xlWb is a Excel.Workbook)
>
>
> //Graph erstellen
> Excel.Chart xlChart = (Excel.Chart)xlWb.Charts.Add
> (missing,missing,missing,missing);
>
> xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
> xlChart.SetSourceData(xlWks.get_Range(Anfang,Ende),
> Excel.XlRowCol.xlColumns);
> xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");
> xlChart.HasTitle = true;
> xlChart.ChartTitle.Text = "CSD Overtime Verlauf";
> ((Excel.Axis)xlChart.Axes
> (Excel.XlAxisType.xlCategory,Excel.XlAxisGroup.xlPrimary)).HasTitle =
> true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Monat";
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasTitle = false;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Anzahl Stunden";
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).HasMinorGridlines = false;
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
>
> xlChart.WallsAndGridlines2D = false;
> xlChart.HasLegend = true;
> xlChart.Legend.Select();
> xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight;
>
>
> But after xlChart.HasTitle = true;
> I get an error:
>
> SystemRuntime.InteropServices.COMException
> HRESULT: 0x800401A8
>
> Even when I comment everthing after xlChart.HasTitle = true;
> I get this error when the program comes to the end.
>
> Does anybody know why?
> What am I doing wrong here?
>
> --
> mfg
> Marc Eggenberger
- defer the placement of the chart into the sheet, until after you are
finished designing the chart
- eliminate the setting of the title on the xlSeriesAxis (valid only for 3D
charts)
Example
// AutomateExcel.cs
//
// requires Office PIAs, specifically Microsoft.Office.Interop. Excel.dll
//
// download them all from:
//
http://www.microsoft.com/downloads/...&displaylang=en
//
// build with:
// C:/WINDOWS/ Microsoft.NET/Framework/v1.1.4322/ csc.exe /t:exe /debug+
// /r:Microsoft.Office.Interop.Excel.dll
/out:AutomateExcel.exe AutomateExcel.cs
//
//
// NB: my Excel PIA is available in
//
C:/windows/assembly/GAC/Microsoft.Office.Interop.Excel/10.0.4504.0__31bf3856
ad364e35/Microsoft.Office.Interop.Excel.dll
//
//
using System;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Excel = Microsoft.Office.Interop.Excel;
class AutomateExcel {
static object missing = System.Reflection.Missing.Value;
public static int Main() {
try {
Console.WriteLine ("Creating new Excel.Application");
Excel.Application app = new Excel.Application();
if (app == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
}
Console.WriteLine ("Making application visible");
app.Visible = true;
Console.WriteLine ("Getting the workbooks collection");
Excel.Workbooks workbooks = app.Workbooks;
Console.WriteLine ("Adding a new workbook");
// The following line is the temporary workaround for the LCID problem
// Excel._Workbook xlWb =
workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Excel._Workbook xlWb = workbooks.Add(missing);
xlWb.Saved = true; // override
Excel.Sheets sheets = xlWb.Worksheets;
Excel._Worksheet xlWks = (Excel._Worksheet) sheets.get_Item(1);
Console.WriteLine ("filling cells in sheet #1");
// This paragraph puts the value 5 to the cell A1
Excel.Range range1;
range1= xlWks.get_Range("A1", Missing.Value);
range1.Value2 = "Experimental Data Reading";
range1.AddComment("This is sample data generated by a C# program at "
+ System.DateTime.Now);
range1.RowHeight=20;
//range1.Interior.Color=??;
range1= xlWks.get_Range("A1", "H1");
range1.Interior.ColorIndex=11; // 9=maroon, dkblue=11
range1.Font.Bold = true;
range1.Font.Size = 18;
range1.Font.ColorIndex = 2; // 2=white?, see
http://www.geocities.com/davemcritchie/excel/colors.htm
const int nRows = 4;
const int nCols = 20;
const string upperLeftCell= "A2";
int endRowNum= System.Int32.Parse(upperLeftCell.Substring(1)) +
nRows - 1;
char endColumnLetter=
System.Convert.ToChar(System.Convert.ToInt32(upperLeftCell[0]) + nCols - 1);
string upperRightCell= System.String.Format("{0}{1}",
endColumnLetter,System.Int32.Parse(upperLeftCell.Substring(1))) ;
string lowerRightCell= System.String.Format("{0}{1}",
endColumnLetter,endRowNum);
// This paragraph sends single dimensional array to Excel
range1 = xlWks.get_Range(upperLeftCell, upperRightCell);
int[] array2 = new int [nCols];
for (int i=0; i < array2.Length; i++) {
array2[i] = i+1;
}
range1.Value2 = array2;
// This paragraph sends a 2D array to Excel
range1 = xlWks.get_Range(upperLeftCell, lowerRightCell);
int[,] array3 = new int [nRows, nCols];
for (int i=0; i < array3.GetLength(0); i++) {
for (int j=0; j < array3.GetLength(1); j++) {
array3[i, j] = i*i*j + 3*i*j + 2*j*j - j*j*j/8 - j*j*j*i/28;
}
}
range1.Value2 = array3;
Console.WriteLine ("building a chart from that data");
//Graph erstellen
Excel._Chart xlChart =
(Excel.Chart)xlWb.Charts.Add(missing,missing,missing,missing);
xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
xlChart.SetSourceData(xlWks.get_Range(upperLeftCell, lowerRightCell),
Excel.XlRowCol.xlRows); // xlColumns
// do not use this here; move it after finished designing the chart
//xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Sheet1");
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = "CSD Overtime Verlauf";
Excel.Axis axis;
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Monat";
axis.HasMajorGridlines = true;
axis.HasMinorGridlines = false;
#region NOTUSED_1
#if NEVER_TRUE
// see
// http://msdn.microsoft.com/library/en-us/ vbaxl10/ html/xlmthAxes.asp
//
// Excel.XlAxisType.xlSeriesAxis is valid only for 3D charts !
//
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = false;
axis.HasMajorGridlines = false;
axis.HasMinorGridlines = false;
#endif
#endregion
axis= (Excel.Axis) xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Anzahl Stunden";
axis.HasMajorGridlines = true;
axis.HasMinorGridlines = false;
xlChart.WallsAndGridlines2D = false;
xlChart.HasLegend = true;
xlChart.Legend.Select();
xlChart.Legend.Position =
Excel.XlLegendPosition.xlLegendPositionRight;
Console.WriteLine ("Moving the chart to sheet1:");
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");
Console.WriteLine ("Press ENTER to finish the sample:");
Console.ReadLine();
try {
// If user interacted with Excel it will not close when the app
object is destroyed, so we close it explicitly
xlWb.Saved = true; // override
app.UserControl = false;
app.Quit();
} catch (COMException) {
Console.WriteLine ("User closed Excel manually, so we don't have to
do that");
}
}
catch (System.Exception ex) {
Console.WriteLine ("Exception : " + ex.ToString());
}
Console.WriteLine ("All done");
return 100;
}
}
"Marc Eggenberger" <nw1@devnull.ch> wrote in message
news:MPG.19722188bcc03f8298968a@news.cis.dfn.de...
> Hi there.
>
> I want to create a graph in Excel via C#.
>
> I recorded a macro in Excel to get the basics with the chart. I got the
> following in the Editor in Excel:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 06.07.2003 by *
> '
>
> '
> Charts.Add
> ActiveChart.ChartType = xl3DAreaStacked
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:W15"),
> PlotBy _
> :=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> With ActiveChart
> .HasTitle = True
> .ChartTitle.Characters.Text = "CSD Alstom Overtime Verlauf"
> .Axes(xlCategory).HasTitle = True
> .Axes(xlCategory).AxisTitle.Characters.Text = "Monat"
> .Axes(xlSeries).HasTitle = False
> .Axes(xlValue).HasTitle = True
> .Axes(xlValue).AxisTitle.Characters.Text = "Anzahl Stunden"
> End With
> With ActiveChart.Axes(xlCategory)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlSeries)
> .HasMajorGridlines = False
> .HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlValue)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> ActiveChart.WallsAndGridlines2D = False
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlRight
> ActiveSheet.Shapes("Chart 1").IncrementLeft -80.25
> ActiveSheet.Shapes("Chart 1").IncrementTop 75#
> End Sub
>
>
> I then tried to translate this to c#.
> I did this in c#
> (xlWb is a Excel.Workbook)
>
>
> //Graph erstellen
> Excel.Chart xlChart = (Excel.Chart)xlWb.Charts.Add
> (missing,missing,missing,missing);
>
> xlChart.ChartType = Excel.XlChartType.xlAreaStacked;
> xlChart.SetSourceData(xlWks.get_Range(Anfang,Ende),
> Excel.XlRowCol.xlColumns);
> xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Sheet1");
> xlChart.HasTitle = true;
> xlChart.ChartTitle.Text = "CSD Overtime Verlauf";
> ((Excel.Axis)xlChart.Axes
> (Excel.XlAxisType.xlCategory,Excel.XlAxisGroup.xlPrimary)).HasTitle =
> true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Monat";
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasTitle = false;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Anzahl Stunden";
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
> Excel.XlAxisGroup.xlPrimary)).HasMinorGridlines = false;
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
>
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = true;
> ((Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
> Excel.XlAxisGroup.xlPrimary)).HasMajorGridlines = false;
>
> xlChart.WallsAndGridlines2D = false;
> xlChart.HasLegend = true;
> xlChart.Legend.Select();
> xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight;
>
>
> But after xlChart.HasTitle = true;
> I get an error:
>
> SystemRuntime.InteropServices.COMException
> HRESULT: 0x800401A8
>
> Even when I comment everthing after xlChart.HasTitle = true;
> I get this error when the program comes to the end.
>
> Does anybody know why?
> What am I doing wrong here?
>
> --
> mfg
> Marc Eggenberger
通过自动化过程,使用诸如 Visual C# .NET 这样的语言编写的应用程序就可以用编程方式来控制其他应用程序。利用 Excel 的自动化功能,您可以执行诸如新建工作簿、向工作簿添加数据或创建图表等操作。对于 Excel 和其他 Microsoft Office 应用程序,几乎所有可以通过用户界面手动执行的操作也都可以通过使用“自动化”功能以编程方式来执行。
Excel 通过一种对象模型来公开这一程序功能。该对象模型是一些类和方法的集合,这些类和方法充当 Excel 的逻辑组件。例如,有 Application 对象、 Workbook 对象和 Worksheet 对象,其中每一种对象都包含 Excel 中那些组件的功能。要从 Visual C# .NET 访问该对象模型,可以设置对类型库的项目引用。
本文将阐述如何为 Visual C# .NET 设置对 Excel 类型库的适当项目引用,并提供使 Excel 自动运行的代码示例。
Excel 通过一种对象模型来公开这一程序功能。该对象模型是一些类和方法的集合,这些类和方法充当 Excel 的逻辑组件。例如,有 Application 对象、 Workbook 对象和 Worksheet 对象,其中每一种对象都包含 Excel 中那些组件的功能。要从 Visual C# .NET 访问该对象模型,可以设置对类型库的项目引用。
本文将阐述如何为 Visual C# .NET 设置对 Excel 类型库的适当项目引用,并提供使 Excel 自动运行的代码示例。
为 Microsoft Excel 创建自动化客户端
- 启动 Microsoft Visual Studio .NET。
- 在文件菜单上,单击新建,然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。Form1 是默认创建的窗体。
- 添加对 Microsoft Excel 对象库的引用。为此,请按照下列步骤操作:
- 在项目菜单上,单击添加引用。
- 在 COM 选项卡上,找到 Microsoft Excel 对象库,然后单击选择。
注意:Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA,但您可以下载 PIA。 有关 Office XP PIA 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:328912 (http://support.microsoft.com/kb/328912/ ) Microsoft Office XP 主 interop 程序集 (PIA) 可供下载 - 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装,请单击是。
- 在视图菜单上,选择工具箱以显示工具箱,然后向 Form1 添加一个按钮。
- 双击 Button1。出现该窗体的代码窗口。
- 在代码窗口中,将以下代码
private void button1_Click(object sender, System.EventArgs e) { }
private void button1_Click(object sender, System.EventArgs e) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; try { //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = true; //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value )); oSheet = (Excel._Worksheet)oWB.ActiveSheet; //Add table headers going cell by cell. oSheet.Cells[1, 1] = "First Name"; oSheet.Cells[1, 2] = "Last Name"; oSheet.Cells[1, 3] = "Full Name"; oSheet.Cells[1, 4] = "Salary"; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range("A1", "D1").Font.Bold = true; oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. string[,] saNames = new string[5,2]; saNames[ 0, 0] = "John"; saNames[ 0, 1] = "Smith"; saNames[ 1, 0] = "Tom"; saNames[ 1, 1] = "Brown"; saNames[ 2, 0] = "Sue"; saNames[ 2, 1] = "Thomas"; saNames[ 3, 0] = "Jane"; saNames[ 3, 1] = "Jones"; saNames[ 4, 0] = "Adam"; saNames[ 4, 1] = "Johnson"; //Fill A2:B6 with an array of values (First and Last Names). oSheet.get_Range("A2", "B6").Value2 = saNames; //Fill C2:C6 with a relative formula (=A2 & " " & B2). oRng = oSheet.get_Range("C2", "C6"); oRng.Formula = "=A2 & /" /" & B2"; //Fill D2:D6 with a formula(=RAND()*100000) and apply format. oRng = oSheet.get_Range("D2", "D6"); oRng.Formula = "=RAND()*100000"; oRng.NumberFormat = "$0.00"; //AutoFit columns A:D. oRng = oSheet.get_Range("A1", "D1"); oRng.EntireColumn.AutoFit(); //Manipulate a variable number of columns for Quarterly Sales Data. DisplayQuarterlySales(oSheet); //Make sure Excel is visible and give the user control //of Microsoft Excel's lifetime. oXL.Visible = true; oXL.UserControl = true; } catch( Exception theException ) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat( errorMessage, theException.Message ); errorMessage = String.Concat( errorMessage, " Line: " ); errorMessage = String.Concat( errorMessage, theException.Source ); MessageBox.Show( errorMessage, "Error" ); } } private void DisplayQuarterlySales(Excel._Worksheet oWS) { Excel._Workbook oWB; Excel.Series oSeries; Excel.Range oResizeRange; Excel._Chart oChart; String sMsg; int iNumQtrs; //Determine how many quarters to display data for. for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--) { sMsg = "Enter sales data for "; sMsg = String.Concat( sMsg, iNumQtrs ); sMsg = String.Concat( sMsg, " quarter(s)?"); DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?", MessageBoxButtons.YesNo ); if (iRet == DialogResult.Yes) break; } sMsg = "Displaying data for "; sMsg = String.Concat( sMsg, iNumQtrs ); sMsg = String.Concat( sMsg, " quarter(s)." ); MessageBox.Show( sMsg, "Quarterly Sales" ); //Starting at E1, fill headers for the number of columns selected. oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs); oResizeRange.Formula = "=/"Q/" & COLUMN()-4 & CHAR(10) & /"Sales/""; //Change the Orientation and WrapText properties for the headers. oResizeRange.Orientation = 38; oResizeRange.WrapText = true; //Fill the interior color of the headers. oResizeRange.Interior.ColorIndex = 36; //Fill the columns with a formula and apply a number format. oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs); oResizeRange.Formula = "=RAND()*100"; oResizeRange.NumberFormat = "$0.00"; //Apply borders to the Sales data and headers. oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs); oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin; //Add a Totals formula for the sales data and apply a border. oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs); oResizeRange.Formula = "=SUM(E2:E6)"; oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle = Excel.XlLineStyle.xlDouble; oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight = Excel.XlBorderWeight.xlThick; //Add a Chart for the selected data. oWB = (Excel._Workbook)oWS.Parent; oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, Missing.Value, Missing.Value ); //Use the ChartWizard to create a new chart from the selected data. oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize( Missing.Value, iNumQtrs); oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value ); oSeries = (Excel.Series)oChart.SeriesCollection(1); oSeries.XValues = oWS.get_Range("A2", "A6"); for( int iRet = 1; iRet <= iNumQtrs; iRet++) { oSeries = (Excel.Series)oChart.SeriesCollection(iRet); String seriesName; seriesName = "=/"Q"; seriesName = String.Concat( seriesName, iRet ); seriesName = String.Concat( seriesName, "/"" ); oSeries.Name = seriesName; } oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name ); //Move the chart so as not to cover your data. oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value ); oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top; oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value ); oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left; }
- 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾:
using Excel = Microsoft.Office.Interop.Excel; using System.Reflection;