We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Chart-generation in xlsx format

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:

  1. 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.

  2. 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;
    }
}

}

@GEWA-COMP_GmbH,
This issue is reproduced here and logged for an investigation and fix. We will update you here once any update is ready to share. This issue is logged as:

CELLSNET-47420 - Different result of ChartType.Line in XLS and XLSX

@GEWA-COMP_GmbH,
Please remove the following codes:

//settings for X-Axis
chart.CategoryAxis.MaxValue = MAX_YEAR;
//Set the min value of value axis
chart.CategoryAxis.MinValue = MIN_YEAR;

If the chart is line chart and CategoryAxis is not date axis, please do not set max and min value of axis.

Thank you very much. Now it works in XLSX (issue 1).

Can you comprehend the issue 2?
I want to prevent to show the “0” if the value doesn’t exist.

Best regards

@GEWA-COMP_GmbH,
We are glad to know that your one issue is resolved. We are analyzing your second issue and will share our feedback soon.

@GEWA-COMP_GmbH,
If you want to add an empty value for chart data, please use “#N/A”, see following codes:
strB.Append(btgEl?.priceVal.ToString("#0.00").Replace(",", “.”) ?? “#N/A”);

BTW, it is better that you enter chart source data into a hidden worksheet, not const values.

@GEWA-COMP_GmbH,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47420”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

The issues you have found earlier (filed as CELLSNET-47420) have been fixed in Aspose.Cells for .NET v20.7. This message was posted using Bugs notification tool by ahsaniqbalsidiqui