Problem with Excel Charting ... Please Help

Z

zoddiax

I am drawing an excel chart using C#. But the chart is occupying entire plot
area no matter how many rows i give. I want to leave some space at the end of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400 rows on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers, Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
 
J

Jon Peltier

Is the first column for X values? Does row 7 contain column headers? If both
of these answers are "yes", then clear cell B7. If only the first is true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend the
range to include row 6.

- Jon
 
Z

zoddiax

No my first cloumn is not for x values which i specify by following line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400", Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");
xlSeriesB.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;
xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


Jon Peltier said:
Is the first column for X values? Does row 7 contain column headers? If both
of these answers are "yes", then clear cell B7. If only the first is true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
I am drawing an excel chart using C#. But the chart is occupying entire
plot
area no matter how many rows i give. I want to leave some space at the end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400 rows on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
 
J

Jon Peltier

I don't use the ChartWizard method to create charts. I don't know why I
don't, maybe I never stumbled across it.

I create the chart using <sheet>.ChartObjects.Add, and I generally add the
series one by one, defining the data for each as I go. SetSourceData also
could work, but I prefer my way, as it seems to give more control. Control
is what you are hunting for.

Here is what I do in VBA, I'll leave conversion to C# to you:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
No my first cloumn is not for x values which i specify by following line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart =
(Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400", Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");
xlSeriesB.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType =
Excel.XlChartType.xlColumnClustered;
xlZAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


Jon Peltier said:
Is the first column for X values? Does row 7 contain column headers? If
both
of these answers are "yes", then clear cell B7. If only the first is
true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
I am drawing an excel chart using C#. But the chart is occupying entire
plot
area no matter how many rows i give. I want to leave some space at the
end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400 rows
on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
Click to expand...
 
Z

zoddiax

I saw your code in your website but its all in VBA and i am not able to
convert it into C# because i have never worked with VBA. It would be of great
help if you can show me a little conversion of the following lines of your
code from VBA to C#. Remaining i will try. You just give me a start.
Following is your code.

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
.Chart.ChartType = xlXYScatterLines
End With
End Sub

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub



Jon Peltier said:
I don't use the ChartWizard method to create charts. I don't know why I
don't, maybe I never stumbled across it.

I create the chart using <sheet>.ChartObjects.Add, and I generally add the
series one by one, defining the data for each as I go. SetSourceData also
could work, but I prefer my way, as it seems to give more control. Control
is what you are hunting for.

Here is what I do in VBA, I'll leave conversion to C# to you:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
No my first cloumn is not for x values which i specify by following line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart =
(Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400", Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");
xlSeriesB.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType =
Excel.XlChartType.xlColumnClustered;
xlZAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


Jon Peltier said:
Is the first column for X values? Does row 7 contain column headers? If
both
of these answers are "yes", then clear cell B7. If only the first is
true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I am drawing an excel chart using C#. But the chart is occupying entire
plot
area no matter how many rows i give. I want to leave some space at the
end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400 rows
on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
Click to expand...
 
Z

zoddiax

I think i got the problem. This is because my actual data is only from A7 to
A300. But gradually the data will be filled up to A400. I know that excel
will automatically adjust that, but my problem is that i want some space at
the end of the chart.The problem statement is

xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = (Excel.Range)xlSheetScript.get_Range("A7", "A400");

When i comment the above 2 lines i get the x axis numbered from 1 to 400 and
i also get some space at the end. But then i also want to display the x axis
values as the values in A7 to A400 which are dates.

I dont understand how can i do that

When i comment the following 2 lines

zoddiax said:
I saw your code in your website but its all in VBA and i am not able to
convert it into C# because i have never worked with VBA. It would be of great
help if you can show me a little conversion of the following lines of your
code from VBA to C#. Remaining i will try. You just give me a start.
Following is your code.

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
.Chart.ChartType = xlXYScatterLines
End With
End Sub

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub



Jon Peltier said:
I don't use the ChartWizard method to create charts. I don't know why I
don't, maybe I never stumbled across it.

I create the chart using <sheet>.ChartObjects.Add, and I generally add the
series one by one, defining the data for each as I go. SetSourceData also
could work, but I prefer my way, as it seems to give more control. Control
is what you are hunting for.

Here is what I do in VBA, I'll leave conversion to C# to you:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
No my first cloumn is not for x values which i specify by following line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart =
(Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400", Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");
xlSeriesB.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType =
Excel.XlChartType.xlColumnClustered;
xlZAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */
xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


:

Is the first column for X values? Does row 7 contain column headers? If
both
of these answers are "yes", then clear cell B7. If only the first is
true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I am drawing an excel chart using C#. But the chart is occupying entire
plot
area no matter how many rows i give. I want to leave some space at the
end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400 rows
on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

I don't do C#. However, if you compare the C# code you posted with
equivalent VBA code (such as I've posted), you should at least be able to
see which commands to use. There also must be some kind of Object Browser in
VS to help you get the syntax right.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
I saw your code in your website but its all in VBA and i am not able to
convert it into C# because i have never worked with VBA. It would be of
great
help if you can show me a little conversion of the following lines of your
code from VBA to C#. Remaining i will try. You just give me a start.
Following is your code.

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
.Chart.ChartType = xlXYScatterLines
End With
End Sub

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub



Jon Peltier said:
I don't use the ChartWizard method to create charts. I don't know why I
don't, maybe I never stumbled across it.

I create the chart using <sheet>.ChartObjects.Add, and I generally add
the
series one by one, defining the data for each as I go. SetSourceData also
could work, but I prefer my way, as it seems to give more control.
Control
is what you are hunting for.

Here is what I do in VBA, I'll leave conversion to C# to you:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
No my first cloumn is not for x values which i specify by following
line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart =
(Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400",
Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7",
"A400");
xlSeriesB.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType =
Excel.XlChartType.xlColumnClustered;
xlZAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange =
(Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


:

Is the first column for X values? Does row 7 contain column headers?
If
both
of these answers are "yes", then clear cell B7. If only the first is
true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend
the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I am drawing an excel chart using C#. But the chart is occupying
entire
plot
area no matter how many rows i give. I want to leave some space at
the
end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400
rows
on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);

xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

What's "get_Range"? Looks like a function that returns a range. How does it
turn the two arguments into a range?

In VBA one might use one of these to define a range starting at A7 and
ending at A400:

Set rXValues = xlSheet.Range(xlSheet.Range("A7"), xlSheet.Range("A400"))
Set rXValues = xlSheet.Range("A7:A400")
Set rXValues = xlSheet.Range("A7").Resize(394, 1)

etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


zoddiax said:
I think i got the problem. This is because my actual data is only from A7
to
A300. But gradually the data will be filled up to A400. I know that excel
will automatically adjust that, but my problem is that i want some space
at
the end of the chart.The problem statement is

xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = (Excel.Range)xlSheetScript.get_Range("A7", "A400");

When i comment the above 2 lines i get the x axis numbered from 1 to 400
and
i also get some space at the end. But then i also want to display the x
axis
values as the values in A7 to A400 which are dates.

I dont understand how can i do that

When i comment the following 2 lines

zoddiax said:
I saw your code in your website but its all in VBA and i am not able to
convert it into C# because i have never worked with VBA. It would be of
great
help if you can show me a little conversion of the following lines of
your
code from VBA to C#. Remaining i will try. You just give me a start.
Following is your code.

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
.Chart.ChartType = xlXYScatterLines
End With
End Sub

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub



Jon Peltier said:
I don't use the ChartWizard method to create charts. I don't know why I
don't, maybe I never stumbled across it.

I create the chart using <sheet>.ChartObjects.Add, and I generally add
the
series one by one, defining the data for each as I go. SetSourceData
also
could work, but I prefer my way, as it seems to give more control.
Control
is what you are hunting for.

Here is what I do in VBA, I'll leave conversion to C# to you:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


No my first cloumn is not for x values which i specify by following
line

xlSeriesB.XValues = xlSheetScript.get_Range("A7", "A400");

and my data has no headers.

Following is the code once again
********************************************************
/* Open the destination file */
xlBookScript =
(Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlSheetExtra =
(Excel.Worksheet)xlBookScript.Worksheets["Extra"];
xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];
xlChart =
(Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlRange =
xlSheetScript.get_Range("B7:B400,F7:F400,H7:H400,O7:O400",
Type.Missing);
xlChart.ChartWizard(xlRange,
Excel.XlChartType.xlLineMarkers, Type.Missing,
Excel.XlRowCol.xlColumns, Type.Missing,
Type.Missing, false,
sSheetType, Type.Missing, Type.Missing,
Type.Missing);

/* Format the chart for white background formatting */
xlChart.PlotArea.Interior.ColorIndex = 48;
xlChart.ChartTitle.Font.Size = 13;
xlXAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);
xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
xlXAxis.TickLabels.NumberFormat = "dd-MMM-yy";
xlXAxis.TickLabels.Font.Size = 9;
xlYAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
xlYAxis.TickLabels.NumberFormat = "0";
xlYAxis.TickLabels.Font.Size = 9;
xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);
xlSeriesB.XValues = xlSheetScript.get_Range("A7",
"A400");
xlSeriesB.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);
xlSeriesF.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;
xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);
xlSeriesH.MarkerStyle =
Excel.XlMarkerStyle.xlMarkerStyleNone;
xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

/* Format the Volume axis */
xlSeriesVol =
(Excel.Series)xlChart.SeriesCollection(4);
xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;
xlSeriesVol.ChartType =
Excel.XlChartType.xlColumnClustered;
xlZAxis =
(Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);
xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;
xlZAxis.MajorTickMark =
Excel.XlTickMark.xlTickMarkNone;
xlZAxis.MaximumScale = 5 * xlZAxis.MaximumScale;
xlZAxis.MinimumScale = 0;

/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");
xlRange =
(Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;
xlRange =
(Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);
xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;
xlSheetExtra.Shapes.Item(sChartNo).Height = 380;
xlSheetExtra.Shapes.Item(sChartNo).Width = 710;
xlBookScript.Close(true, Type.Missing, Type.Missing);
************************************************************


:

Is the first column for X values? Does row 7 contain column headers?
If
both
of these answers are "yes", then clear cell B7. If only the first is
true,
put headers in D6, J6, and O6, and make sure B6 is blank, and extend
the
range to include row 6.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


I am drawing an excel chart using C#. But the chart is occupying
entire
plot
area no matter how many rows i give. I want to leave some space at
the
end
of
the chart.



Suppose i am drawing chart for 400 rows, I want to see all the 400
rows
on
the x axis without omitting the blank rows. How can i do that.



Following is my code




Code Block/* Open the destination file */

xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing);

xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

xlSheetScript =
(Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);

xlRange =
xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400",
Type.Missing);

xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers,
Type.Missing,

Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

sSheetType, Type.Missing, Type.Missing, Type.Missing);







xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlSecondary);

xlZAxis.TickLabelPosition =
Excel.XlTickLabelPosition.xlTickLabelPositionNone;

xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

xlZAxis.MinimumScale = 0;







/* Give the location for the chart */

xlChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Extra");

xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Top =
(float)(double)xlRange.Top;

xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1,
Type.Missing);

xlSheetExtra.Shapes.Item(sChartNo).Left =
(float)(double)xlRange.Left;

xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

xlBookScript.Close(true, Type.Missing, Type.Missing);
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to keep source data for the Excel Charts constant ... Please H 1
Excel 2007 C# Charting problem 1

Top