What I currently have is:
Is there a way to get this output?:
Furthermore, I noticed that the values are not the one of the table but the first day of the month. Why? Is this an Excel "feature"?
EDIT:
I create the Excel from scratch. First the cell are filled:
- Code: Select all
IEnumerable<Tuple<DateTime, int>> mitarbeiterJahre = from s in statistikenPerYear
from b in s.Value.Bueros
where b.Key == keyValuePair.Key
group b by Tuple.Create(b.Key, s.Key, b.Value) into g
orderby g.Key.Item2 descending
select Tuple.Create(g.Key.Item2, g.Sum(x => x.Value.Mitarbeiter));
cell = ws.Cell(16, 1);
cell.Style.Font.SetBold();
cell.Value = $"Anzahl Mitarbeiter";
cell = ws.Cell(17, 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
cell.Value = date.ToString("dd.MM.yyyy");
cell = ws.Cell(17, 2);
cell.Value = statistiken.Mitarbeiter;
for (int i = 0; i < mitarbeiterJahre.Count(); i++)
{
cell = ws.Cell(18 + i, 1);
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
cell.Value = mitarbeiterJahre.ElementAt(i).Item1.ToString("dd.MM.yyyy");
cell = ws.Cell(18 + i, 2);
cell.Value = mitarbeiterJahre.ElementAt(i).Item2;
}
Then the chart is created:
- Code: Select all
Chart lineChart = sheet.Charts.Add(ExcelChartType.Line);
lineChart.ChartTitle = string.Empty;
lineChart.LeftColumn = 4;
lineChart.TopRow = 16;
lineChart.RightColumn = 12;
lineChart.BottomRow = 35;
lineChart.DataRange = sheet.Range["A17:B21"];
lineChart.SeriesDataFromRange = false;
lineChart.PrimaryValueAxis.Visible = false;
lineChart.PrimaryCategoryAxis.Visible = false;
ChartSerie csLine = lineChart.Series[0];
csLine.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
for (int i = 0; i < csLine.Values.Count; i++)
{
csLine.DataPoints[i].DataLabels.Color = Color.Black;
csLine.DataPoints[i].DataLabels.Position = DataLabelPositionType.Below;
csLine.DataPoints[i].DataLabels.Delimiter = " - ";
csLine.DataPoints[i].DataLabels.HasValue = true;
csLine.DataPoints[i].DataLabels.HasCategoryName = true;
csLine.DataPoints[i].DataFormat.MarkerStyle = ChartMarkerType.Circle;
csLine.DataPoints[i].DataFormat.MarkerSize = 7;
csLine.DataPoints[i].DataFormat.MarkerForegroundColor = Color.FromArgb(74, 126, 187);
csLine.DataPoints[i].DataFormat.MarkerBackgroundColor = Color.FromArgb(74, 126, 187);
}
lineChart.PlotArea.Fill.Visible = false;
lineChart.Legend.Delete();
I'm working with "de-DE" CultureInfo.