Chart X-Axis date and time

Question

I create an Excel document using EPPlus and C#. In a content worksheet I have several Rows containing a datetime and a value. Then I use the following code to create a chart:

    var wsPressure = pck.Workbook.Worksheets.Add( "Pressure" );
    var chartPressure = wsPressure.Drawings.AddChart( "PressureChart", eChartType.Line );
    chartPressure.SetSize( 1280, 1024 );
    var serie1 = (ExcelLineChartSerie)chartPressure.Series.Add( "=Content!$A$2:$A$" + dataRow, "=Content!$D$2:$D$" + dataRow );
    serie1.Header = wsContent.Cells[1, 1].Value.ToString();

The chart is printed but the time is lost. Therefore for any days with more than one value a vertical line is drawn. If I create a chart manually in Excel this works fine. How to do the same with EPPlus?

Sample data:

A  B  C  D
1  -  -  01/01/2015
4  -  -  01/01/2015
2  -  -  01/02/2015
3  -  -  01/03/2015
6  -  -  01/03/2015
5  -  -  01/03/2015

Here the problematic data is 01/01/2015 and 01/03/2015. Espicially the last one is hard, as the value in the middle it completely lost.

Ernie S Ernie S · Accepted Answer · 2015-01-26T22:41:22

The problem is with a Line chart in excel will, by default, try to determine the type of x axis it is and conclude it is a Date type which is only half right in your case. If you then change that to "Text" under axis options it will treat everything as categories and include the time component. Note this will NOT space them "properly" by date/time differences so the distance between, say, 1/1 and 1/2 will be the same as 1/2 and 1/6 - this may or may not be what you want. Either way, I do not see an option to set the X eAxisType in EPP since it is read only and set at construction of the chart object. I think you would have to use manual XML manipulation if you want to mess it.

Are you maybe after an XY scatter plot instead? Something like this might get you what you want:

[TestMethod]
public void Chart_DateTime_Test()
{
    //http://stackoverflow.com/questions/28158702/chart-x-axis-date-and-time
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var pck = new ExcelPackage(existingFile))
    {
        var wsContent = pck.Workbook.Worksheets.Add("Content");
        var wsPressure = pck.Workbook.Worksheets.Add("Pressure");

        //Some data
        wsContent.Cells["A1"].Value = "A";
        wsContent.Cells["B1"].Value = "B";
        wsContent.Cells["C1"].Value = "C";
        wsContent.Cells["D1"].Value = "D";

        wsContent.Cells["A2"].Value = 1;
        wsContent.Cells["A3"].Value = 4;
        wsContent.Cells["A4"].Value = 2;
        wsContent.Cells["A5"].Value = 3;
        wsContent.Cells["A6"].Value = 6;
        wsContent.Cells["A7"].Value = 5;

        wsContent.Cells["D2"].Value = new DateTime(2015, 1, 1, 8, 15, 0);
        wsContent.Cells["D3"].Value = new DateTime(2015, 1, 1, 15, 15, 0);
        wsContent.Cells["D4"].Value = new DateTime(2015, 1, 2, 8, 15, 0);
        wsContent.Cells["D5"].Value = new DateTime(2015, 1, 3, 8, 15, 0);
        wsContent.Cells["D6"].Value = new DateTime(2015, 1, 3, 15, 15, 0);
        wsContent.Cells["D7"].Value = new DateTime(2015, 1, 3, 20, 15, 0);

        const int dataRow = 7;

        const string FORMATDATE = "m/d/yy h:mm;@";
        wsContent.Cells[2, 4, dataRow, 4].Style.Numberformat.Format = FORMATDATE;

        //var chartPressure = wsPressure.Drawings.AddChart("PressureChart", eChartType.Line);
        var chartPressure = wsPressure.Drawings.AddChart("PressureChart", eChartType.XYScatterLines);
        chartPressure.SetSize(1280, 1024);

        //var serie1 = (ExcelLineChartSerie)chartPressure.Series.Add("=Content!$A$2:$A$" + dataRow, "=Content!$D$2:$D$" + dataRow);
        var serie1 = (ExcelScatterChartSerie)chartPressure.Series.Add(wsContent.Cells[2, 1, dataRow, 1], wsContent.Cells[2, 4, dataRow, 4]);
        serie1.Header = wsContent.Cells[1, 1].Value.ToString();

        pck.Save();
    }
}

Chart X-Axis date and time

1 Answers