Re: Problem with create Excel graph in c#

[Dino Chiesa [MSFT]]

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/...1E-3060-4F71-A6B4-01FEBA508E52&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+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]

2 things:
- defer the placement of the chart into the sheet, until after you are
finished designing the chart

[...]

Thats it .. pretty simple .. thanks a lot.

 

[seser]

Thanks a lot for, this useful code.

And I want to ask something more, how can we place a chart to the position ex. cell(10,12)

 

[sheril1981]

useful help for c# excel chart

http://csharp.net-informations.com/excel/csharp-excel-chart.htm

thanks.