How to keep source data for the Excel Charts constant ... Please H

Z

zoddiax

Hi,

I am creating excel charts using C#. The range for creating chart is from
Row1 to Row400. After the chart is created, first 100 rows need to be
deleted. When I delete the first 100 rows (Row1 to Row100), the data range
for chart reduces to 300 (Row1 to Row300). I dont want this to happen.

I want that the source data range should always remain constant i.e. from
Row1 to Row400.

Code Snippet

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,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);



/* Format the chart for white background formatting */

xlChart.PlotArea.Interior.ColorIndex = 48;

xlChart.ChartTitle.Font.Size = 10;

xlChart.ChartArea.Border.ColorIndex = 1;

xlXAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);

xlYAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);

xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;

xlXAxis.TickLabelSpacing = 25;

xlXAxis.TickMarkSpacing = 25;

xlXAxis.TickLabels.Font.Size = 9;

xlYAxis.TickLabels.Font.Size = 9;

xlYAxis.TickLabels.NumberFormat = "0";

xlXAxis.Border.ColorIndex = 1;

xlYAxis.Border.ColorIndex = 1;

xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);

xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);

xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);

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

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

xlSeriesB.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesF.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesH.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesJ.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesJ.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesB.Border.ColorIndex = 1;

xlSeriesF.Border.ColorIndex = 6;

xlSeriesH.Border.ColorIndex = 9;

xlSeriesJ.Border.ColorIndex = 5;



/* Format the Volume axis */

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

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlSeriesVol.Fill.ForeColor.SchemeColor = 19;

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

Jon Peltier

Delete the rows before creating the chart, Or redefine the chart's source
data using Chart.SetSourceData. You could also define Names in the worksheet
that keep this constant, although removing row 1 means removing a common
reference point.

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


zoddiax said:
Hi,

I am creating excel charts using C#. The range for creating chart is from
Row1 to Row400. After the chart is created, first 100 rows need to be
deleted. When I delete the first 100 rows (Row1 to Row100), the data range
for chart reduces to 300 (Row1 to Row300). I dont want this to happen.

I want that the source data range should always remain constant i.e. from
Row1 to Row400.

Code Snippet

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,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);



/* Format the chart for white background formatting */

xlChart.PlotArea.Interior.ColorIndex = 48;

xlChart.ChartTitle.Font.Size = 10;

xlChart.ChartArea.Border.ColorIndex = 1;

xlXAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);

xlYAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);

xlXAxis.TickLabels.Orientation =
Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;

xlXAxis.TickLabelSpacing = 25;

xlXAxis.TickMarkSpacing = 25;

xlXAxis.TickLabels.Font.Size = 9;

xlYAxis.TickLabels.Font.Size = 9;

xlYAxis.TickLabels.NumberFormat = "0";

xlXAxis.Border.ColorIndex = 1;

xlYAxis.Border.ColorIndex = 1;

xlSeriesB = (Excel.Series)xlChart.SeriesCollection(1);

xlSeriesF = (Excel.Series)xlChart.SeriesCollection(2);

xlSeriesH = (Excel.Series)xlChart.SeriesCollection(3);

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

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

xlSeriesB.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesF.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesH.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesJ.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;

xlSeriesB.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesF.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesH.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesJ.Border.Weight = Excel.XlBorderWeight.xlThin;

xlSeriesB.Border.ColorIndex = 1;

xlSeriesF.Border.ColorIndex = 6;

xlSeriesH.Border.ColorIndex = 9;

xlSeriesJ.Border.ColorIndex = 5;



/* Format the Volume axis */

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

xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

xlSeriesVol.Fill.ForeColor.SchemeColor = 19;

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

Problem with Excel Charting ... Please Help 7

Top