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

  • Thread starter Thread starter zoddiax
  • Start date Start date
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;
 
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...
 
Back
Top