Search code examples
c#excelrowyaxisx-axis

C# Excel Interop Chart Column Series Problem

I am reading a 16 Row, 270 Column Excel file. After some algorithms and comparisons, I create a new Excel and a new chart in the new Excel. The chart is incorrect when Excel contains more than 16 columns.

Here is my chart creation code;

xlRange2 = xlWorksheet2.UsedRange;
xlRange2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;    
xlRange2.Borders.Weight = Excel.XlBorderWeight.xlThin;
rowCount2 = xlRange2.Rows.Count;
colCount2 = xlRange2.Columns.Count;

Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorksheet2.ChartObjects(Type.Missing);
xlRange2 = xlWorksheet2.Range[xlWorksheet2.Cells[1, 2], xlWorksheet2.Cells[16, colCount2]]; //Here is my Y-Axis Values and Series Names

Excel.Chart ct2 = xlWorksheet2.Shapes.AddChart(null, 1, 275, 650, 350).Chart;
var missing = System.Type.Missing;

ct2.ChartWizard(xlRange2, Excel.XlChartType.xlLineMarkers, missing, missing, missing, missing, missing, missing, "Frequency[Hz]", "Absorption Coefficient[-]", missing);

Excel.Series oSeries2 = (Excel.Series)ct2.SeriesCollection(1);
oSeries2.XValues = xlWorksheet2.get_Range("A2", "A16");  //Here is my X-Axis Values

Correct Output Example Picture:

This one is correct

Wrong Output Example Picture:

enter image description here

Should Look Like This Picture:

enter image description here

Solution

  • SOLVED

    Problem: Program couldn't decide whether my values(Y Axis or for my project Absorption Coefficient) are rows or columns. Excel was doing it with its own algorithm. When I use this code my problem has been solved.

    ct2.SetSourceData(chart_range, Excel.XlRowCol.xlColumns);

    All code with explanation;

    xlRange2 = xlWorksheet2.UsedRange; //Compute used range in excel file
xlRange2.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //Draw cell borders 
xlRange2.Borders.Weight = Excel.XlBorderWeight.xlThin;
rowCount2 = xlRange2.Rows.Count; //Count used rows
colCount2 = xlRange2.Columns.Count; //Count used columns

//Add empty xlLineMarkers chart type
//Location (x,y) = (1,275)
//Size: (650,350)
Excel.Shape chart_shape=xlWorksheet2.Shapes.AddChart(Excel.XlChartType.xlLineMarkers, 1, 275, 650, 350);

//Define chart with shape
Excel.Chart ct2 = chart_shape.Chart;

//This code provide the source data range which was B1 to B16 and end of the column range
Excel.Range chart_range = xlWorksheet2.Range[xlWorksheet2.Cells[1, 2], xlWorksheet2.Cells[16, colCount2]];

//This line is the solution of my problem, my source(Y axis values) data are columns...
//...because of this reason I use ct2.SetSourceData(chart_range, Excel.XlRowCol.xlColumns);
//If your sources are on the row you should use ct2.SetSourceData(chart_range, Excel.XlRowCol.xlRows); 
ct2.SetSourceData(chart_range, Excel.XlRowCol.xlColumns);

//Set the X axis values
//For me A2 to A16
Excel.Range axis_range = xlWorksheet2.get_Range("A2", "A16");
Excel.Series series = (Excel.Series)ct2.SeriesCollection(1);
series.XValues = axis_range;

//Y Axis Label Configuration
Excel.Axis axis = (Excel.Axis)ct2.Axes(Excel.XlAxisType.xlValue,Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Absorption Coefficient [-]";

//X Axis Label
Excel.Axis Xaxis = (Excel.Axis)ct2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
Xaxis.HasTitle = true;
Xaxis.AxisTitle.Text = "Frequency [Hz]";

//If you want to see the chart on pictureBox use following code
//In addition, you can use 'null' instead of 'misValue'
object misValue = System.Reflection.Missing.Value;
//Export Chart as a picture into the project folder
//Such as C:\Users\....\bin\Debug\net5.0-windows
ct2.Export((Directory.GetCurrentDirectory() + "\\excelChartV5.bmp"), "BMP", misValue);

//To show in pictureBox the exported picture
pictureBox1.Image = new Bitmap((Directory.GetCurrentDirectory() + "\\excelChartV5.bmp"));