Hello,
in our product we use chart-generation in Excel. The code below is some example how we make it. But there are some problems:
-
if I save generated chart as xlsx-format than lines and x-axes values are not visible, but for SaveFormat.Excel97To2003 is everything OK. I need the same for xlsx-format.
-
I use in code
chart.PlotEmptyCellsType = PlotEmptyCellsType.NotPlotted; chart.PlotVisibleCells = true;
and try to put string.Empty for not existing values to prevent to plot them. But it doesn’t work – all this values always are converted to 0 and will be ploted. How can I realize it?
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Aspose.Cells;
using Aspose.Cells.Charts;
namespace TestChartEmptyCells
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
}
private void button1_Click(object sender, EventArgs e)
{
const int MIN_YEAR = 2010;
const int MAX_YEAR = 2020;
Workbook wb = new Workbook();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet currentWS = wb.Worksheets[0];
Series aSerie = null;
StringBuilder strB = null;
int chartIndex = currentWS.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 0, 0, 25, 10);
Aspose.Cells.Charts.Chart chart = currentWS.Charts[chartIndex];
//not show not defined values
chart.PlotEmptyCellsType = PlotEmptyCellsType.NotPlotted;
chart.PlotVisibleCells = true;
//settings for X-Axis
chart.CategoryAxis.MaxValue = MAX_YEAR;
//Set the min value of value axis
chart.CategoryAxis.MinValue = MIN_YEAR;
//Set the major unit
chart.CategoryAxis.MajorUnit = 1;
//Set the number of categories or series between tick-mark labels.
chart.CategoryAxis.TickLabelSpacing = 1;
chart.CategoryAxis.TickLabels.NumberFormat = "0000";
chart.CategoryAxis.TickLabels.Font.Size = 8;
chart.CategoryAxis.Title.Text = "Jahr";
//settings for Y-Axis
chart.ValueAxis.Title.Text = "Beitrag, €";
chart.ValueAxis.TickLabelPosition = TickLabelPositionType.Low;
chart.Legend.Position = LegendPositionType.Bottom;
chart.PlotArea.Area.ForegroundColor = System.Drawing.Color.White;
Dictionary<int, PriceObj> btg1 = new Dictionary<int, PriceObj>();
Dictionary<int, PriceObj> btg2 = new Dictionary<int, PriceObj>();
Random rnd = new Random();
StringBuilder xValues = new StringBuilder();
xValues.Append("{");
for (int i= MIN_YEAR; i<= MAX_YEAR; i++)
{
btg1[i] = new PriceObj(rnd.NextDouble() * 500);
btg2[i] = new PriceObj(rnd.NextDouble() * 500);
xValues.Append(i.ToString());
if (i < MAX_YEAR) xValues.Append(",");
}
xValues.Append("}");
btg2[MIN_YEAR] = null;
btg2[MIN_YEAR+1] = null;
btg2[MIN_YEAR+2] = null;
List<Dictionary<int, PriceObj>> currentPKVTarifs = new List<Dictionary<int, PriceObj>>();
currentPKVTarifs.Add(btg1);
currentPKVTarifs.Add(btg2);
int iCounter = 0;
foreach (Dictionary<int, PriceObj> pT in currentPKVTarifs)
{
strB = new StringBuilder();
strB.Append("{");
for (int i = MIN_YEAR; i <= MAX_YEAR; i++)
{
//copy+past Jahr-Column for every year in history
PriceObj btgEl = pT[i];
strB.Append(btgEl?.priceVal.ToString("#0.00").Replace(",", ".") ?? string.Empty);
if (i < MAX_YEAR) strB.Append(",");
}
strB.Append("}");
aSerie = chart.NSeries[chart.NSeries.Add(strB.ToString(), true)];
aSerie.Name = "Tarif" + (++iCounter).ToString();
aSerie.XValues = xValues.ToString();
aSerie.Border.Color = System.Drawing.Color.Red;
aSerie.Border.Weight = Aspose.Cells.Drawing.WeightType.MediumLine;
}
string storedFileXlsx = @"d:\temp\test.xlsx";
if (System.IO.File.Exists(storedFileXlsx)) System.IO.File.Delete(storedFileXlsx);
wb.Save(storedFileXlsx, SaveFormat.Xlsx);
string storedFileXls = @"d:\temp\test.xls";
if (System.IO.File.Exists(storedFileXls)) System.IO.File.Delete(storedFileXls);
wb.Save(storedFileXls, SaveFormat.Excel97To2003);
}
}
public class PriceObj
{
public double priceVal { get; set; } = 0;
public PriceObj(double _price)
{
priceVal = _price;
}
}
}