c# excel chart

 

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

 

 

通过自动化过程，使用诸如 Visual C# .NET 这样的语言编写的应用程序就可以用编程方式来控制其他应用程序。利用 Excel 的自动化功能，您可以执行诸如新建工作簿、向工作簿添加数据或创建图表等操作。对于 Excel 和其他 Microsoft Office 应用程序，几乎所有可以通过用户界面手动执行的操作也都可以通过使用“自动化”功能以编程方式来执行。

Excel 通过一种对象模型来公开这一程序功能。该对象模型是一些类和方法的集合，这些类和方法充当 Excel 的逻辑组件。例如，有 Application 对象、 Workbook 对象和 Worksheet 对象，其中每一种对象都包含 Excel 中那些组件的功能。要从 Visual C# .NET 访问该对象模型，可以设置对类型库的项目引用。

本文将阐述如何为 Visual C# .NET 设置对 Excel 类型库的适当项目引用，并提供使 Excel 自动运行的代码示例。

为 Microsoft Excel 创建自动化客户端

1. 启动 Microsoft Visual Studio .NET。
2. 在文件菜单上，单击新建，然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。Form1 是默认创建的窗体。
3. 添加对 Microsoft Excel 对象库的引用。为此，请按照下列步骤操作：
   1. 在项目菜单上，单击添加引用。
   2. 在 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) 可供下载
   3. 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。
4. 在视图菜单上，选择工具箱以显示工具箱，然后向 Form1 添加一个按钮。
5. 双击 Button1。出现该窗体的代码窗口。
6. 在代码窗口中，将以下代码

   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;
   }
   					

7. 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾：

   using Excel = Microsoft.Office.Interop.Excel;
   using System.Reflection;