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