Click here to Skip to main content
15,885,767 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Greetings. This has been bugging me for hours! I am creating a excel chart object from data in cells.
Data looks like this:
Time | Temp
22:35:22 | 50
22:35:23 | 60
22:35:24 | 70
22:35:25 | 65
22:35:26 | 55
22:35:27 | 45
22:35:28 | 50
22:35:29 | 55
22:35:30 | 65
22:35:31 | 60

When I create the chart object in c#, I cannot figure out how to specify the category (x-axis) labels like when you select data in the Excel app for a chart, you have the right column to select the x-axis labels (picture: here).
Whenever the chart is created, the default x-axis labels are 1,2,3,etc., but I want it to be the timestamp in column A.

Here's the code I'm using:
C#
var excel = new Excel.Application();
excel.Workbooks.Add();

Excel._Worksheet sheet = excel.ActiveSheet;

var labels = new List<string>();
int x = 1;
foreach (string line in listBox1.Items)
{
    string[] values = line.Split(',');
    ((Excel.Range)sheet.Cells[x, "A"]).NumberFormat = "HH:MM:SS";
    ((Excel.Range)sheet.Cells[x, "A"]).Value = ConvertFromUnixTimestamp(Convert.ToDouble(values[0])).ToLocalTime();
    sheet.Cells[x, "B"] = values[1];
    sheet.Cells[x, "C"] = values[2];
    labels.Add(ConvertFromUnixTimestamp(Convert.ToDouble(values[0])).ToLocalTime().ToString());
    x++;
}
sheet.Columns[1].AutoFit();
sheet.Columns[2].AutoFit();
sheet.Columns[3].AutoFit();

var charts = sheet.ChartObjects() as Excel.ChartObjects;
var chartObject = charts.Add(60, 10, 300, 300) as Excel.ChartObject;
var chart = chartObject.Chart;

var range = sheet.get_Range("C1", "C"+x.ToString());

Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Caption = "Time";
xAxis.CategoryNames = (Excel.Range)sheet.get_Range("A1", "A" + x.ToString());

chart.SetSourceData(range);

chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers;
chart.ChartWizard(Source: range,
    Title: "Temperature Log",
    CategoryTitle: "Time",
    ValueTitle: "Temp");


sheet.SaveAs(saver.FileName);

excel.Quit();


Please help! Extremely bugging me!
Thank you,
Sam
Posted

Include both columns in the range that you pass to SetSourceData, and make sure the top left cell is blank (remove the "Time" label). Excel will parse the range the way you want, so you don't need to specify X or Y values.
 
Share this answer
 
Comments
zimmersm 29-Jan-15 18:52pm    
Thank you, that worked. I just had to leave the entire first row blank (not sure why)... I also got rid of the whole axis section.
Weird about leaving the entire first row blank. Those (except for the first column) should be used as series names (legend entries) in the chart.

Maybe now with the axis code gone, you could try again with only the first cell blank.
 
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