Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I really need help, please.


I look at the chart, and both series have the right values but don't display them, but they only display the first and last values.

I managed to get the data for the columns to work, but then the x-axis did not work. When the x-axis works, I get the first and last data columns.

I use Dispose(true) in the routine that opens everything. I tried to use
ExcelHelper excelHelper = new ExcelHelper(path); but I can't find it in the Project Reference at the assembly list and the Com list

private void UpdateChartSeries()
{
Excel.Worksheet chartWorksheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item("Totals");//Select the sheet the chart is on

//diaable the alearts
frmFoodPantry.objApp.DisplayAlerts = false;
Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;
Excel.Worksheet totalWorksheet = (Excel.Worksheet)sheets.get_Item("Totals");
totalWorksheet.Activate();
//Unprotect the Totals sheet
totalWorksheet.Unprotect();

//Select the chart
Object chTotals = totalWorksheet.ChartObjects("ChartTotals");


// Access Bar chart
Excel.Chart chart = totalWorksheet.ChartObjects("ChartTotals").Chart;
////Excel.Chart chart = chTotals as Excel.Chart;

//chart.PlotArea.Select();
Excel.ChartObjects chartObj = (Excel.ChartObjects)chartWorksheet.ChartObjects();
Excel.Chart xlChart = chartWorksheet.ChartObjects("ChartTotals").chart;

Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(misValue);

Excel.SeriesCollection serColl = chart.SeriesCollection();
serColl.Item(1).Delete();
serColl.Item(1).Delete();

Excel.Series HouseHolds = oSeriesCollection.NewSeries();
Excel.Series People = oSeriesCollection.NewSeries();

Excel.Range series1_range = chartWorksheet.get_Range("C3", "C" + emptyRow.ToString());
Excel.Range series2_range = chartWorksheet.get_Range("D3", "D" + emptyRow.ToString());

HouseHolds.Values = series1_range;
People.Values = series2_range;

// X-Axis
String xAxis = "B3" + "," + "B" + emptyRow.ToString();
Excel.Series xAxisSeries = (Excel.Series)xlChart.SeriesCollection(1);
xAxisSeries.XValues = "";
xAxisSeries.XValues = chartWorksheet.get_Range(xAxis);

////series1.Name = "HouseHolds";
////series2.Name = "People";

////xlChart.SeriesCollection("HouseHolds").Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
////xlChart.SeriesCollection("People").Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;


editsMade = true;
}

What I have tried:

////Excel.Range chartRange;
////Excel.Worksheet xlWorkSheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item(1);
////Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
////Excel.ChartObject myChart = (Excel.ChartObject)chartObj.Add(10, 80, 300, 250);
////Excel.Chart chartPage = myChart.Chart;
////Excel.Range chartRange1 = chartWorksheet.get_Range(("C3", "C" + emptyRow.ToString()), ("D3", "D" + emptyRow.ToString())); // ;
////xlChart.SetSourceData(chartRange, misValue);
////chartPage.Name = "HouseHolds";
////Charting sreies
////Charting.SeriesCollection Series = chartRange;

////Excel.Range chartRange;
////Excel.Worksheet xlWorkSheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item(1);
////Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
////Excel.ChartObject myChart = (Excel.ChartObject)chartObj.Add(10, 80, 300, 250);
////Excel.Chart chartPage = myChart.Chart;
////Excel.Range chartRange2 = chartWorksheet.get_Range("D3", "D" + emptyRow.ToString());
////xlChart.SetSourceData(chartRange, misValue);





// Series 1 values
////totalWorksheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item(1);
////Excel.Range chartRange = totalWorksheet.get_Range("C3", "C" + emptyRow.ToString());


//Set series 1 to be equal to the number of familes
////xlChart.SeriesCollection("HouseHolds").Values = "=Totals!$C$3:$C" + emptyRow.ToString();

// Series 1
////string houseHolds = @"=Totals!$C$3:$C" + emptyRow.ToString();
////Excel.Series HouseHolds = (Excel.Series)xlChart.SeriesCollection("HouseHolds");
////HouseHolds.Values = chartWorksheet.get_Range(houseHolds);
////// Set the color for the bar
////xlChart.SeriesCollection("HouseHolds").Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;

////// Series 2
////string strPeople = @"=Totals!$D$3:$D" + emptyRow.ToString();
////Excel.Series People = (Excel.Series)xlChart.SeriesCollection("People");
////People.Values = chartWorksheet.get_Range(strPeople);
////// Set the color for the bar
////xlChart.SeriesCollection("People").Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;

// X-Axis
////String xAxis = "B3" + "," + "B" + emptyRow.ToString();
////Excel.Series xAxisSeries = (Excel.Series)xlChart.SeriesCollection(1);
////xAxisSeries.XValues = chartWorksheet.get_Range(xAxis);

////String houseHolds = "C3" + "C" + emptyRow.ToString();
////SeriesCollection.Add = (houseHolds);
////String people = "D3" + "D" + emptyRow.ToString();
////Series.ReferenceEquals() = people;
Posted
Updated 22-Aug-22 15:12pm
v6
Comments
Richard MacCutchan 22-Aug-22 4:05am    
Maybe you should ask Graeme.

1 solution

private void UpdateChartSeries()
{
Excel.Worksheet chartWorksheet = (Excel.Worksheet)frmFoodPantry.theWorkbook.Worksheets.get_Item("Totals");//Select the sheet the chart is on

// Diaable the alearts
frmFoodPantry.objApp.DisplayAlerts = false;
Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;
Excel.Worksheet totalWorksheet = (Excel.Worksheet)sheets.get_Item("Totals");
totalWorksheet.Activate();
// Unprotect the Totals sheet
totalWorksheet.Unprotect();

// Select the chart
Object chTotals = totalWorksheet.ChartObjects("ChartTotals");


// Access Bar chart
Excel.Chart chart = totalWorksheet.ChartObjects("ChartTotals").Chart;

// chart.PlotArea.Select();
Excel.ChartObjects chartObj = (Excel.ChartObjects)chartWorksheet.ChartObjects();
Excel.Chart xlChart = chartWorksheet.ChartObjects("ChartTotals").chart;

Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(misValue);

Excel.SeriesCollection serColl = chart.SeriesCollection();
// Delete Series 1 this will make series 2 to be series 1
serColl.Item(1).Delete();
// Delete series 2
serColl.Item(1).Delete();

// Define the new series for the number of House Holds
Excel.Series HouseHolds = oSeriesCollection.NewSeries();
// Define the new series for the Number of People
Excel.Series People = oSeriesCollection.NewSeries();

// Get the range of the vaules for the House Hold
HouseHolds.Values = chartWorksheet.get_Range("C3", "C" + emptyRow.ToString());
// Get the range of the vaules for the Number of People in the House Hold
People.Values = chartWorksheet.get_Range("D3", "D" + emptyRow.ToString());
// Set the series names
HouseHolds.Name = "HouseHolds";
People.Name = "People";

// Set the Bar colors
HouseHolds.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbRed;
People.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbBlue;

// Set the values for the X-Axis
Excel.Series xAxisSeries = oSeriesCollection.NewSeries();
// Set the x-axis
xAxisSeries.XValues = chartWorksheet.get_Range("B3", "B" + emptyRow.ToString()); ////chartWorksheet.get_Range(xAxis);

editsMade = true;
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900