chart.ToImage show different result from Excel Chart

Aspose.Cells Product Family
1

I am trying to create a chart and convert it to Image with below code.

public void BuildReproduceGraph()
{
    var data = new object[,]
    {
        { "Category", "55", "65", "70", "Percent" },
        { 0, 55, 65, 70, 0 },
        { 0.25, 55, 65, 70, 0.12 },
        { 0.50, 55, 65, 70, 0.45 },
        { 0.75, 70.00, 75, 77.5, 0.42 },
        { 1.00, 85, 85, 85, 0.01 }
    };

    // Create a new workbook
    Workbook workbook = new Workbook();
    // Access the first worksheet in the workbook
    Worksheet worksheet = workbook.Worksheets[0];

    // Get the range of cells where you want to set values
    int startRow = 0;
    int startColumn = 0;
    int numRows = data.GetLength(0);
    int numCols = data.GetLength(1);
    var range = worksheet.Cells.CreateRange(startRow, startColumn, numRows, numCols);

    // Set values from the array to the range
    range.Value = data;

    // Calculate Data
    // Calculate
    for (int i = 1; i < numRows; i++)
    {
        worksheet.Cells[i, 5].Formula = $"=C{i + 1} - B{i + 1}";
        worksheet.Cells[i, 6].Formula = $"=D{i + 1} - C{i + 1}";
        worksheet.Cells[i, 7].Formula = i == 1 ? "" : $"=CONCAT(C{i + 1},\"°\",\" Test \", CHAR(10), \"(\",TEXT(E{i + 1}, \"0%\"),\")\")";
    }

    // Format Data
    var temperature_Bins = worksheet.Cells.CreateRange("A2:A6");
    var temperatureBinsStyle = workbook.CreateStyle();
    temperatureBinsStyle.Custom = @"##0%;[Red](##0%)";
    temperature_Bins.SetStyle(temperatureBinsStyle);

    // Adding a chart to the worksheet
    int chartIndex = worksheet.Charts.Add(ChartType.AreaStacked, 8, 0, 30, 10);
    // Retrieve the Chart object
    Chart chart = worksheet.Charts[chartIndex];
    chart.ShowLegend = false;
    chart.ChartArea.Border.IsVisible = false;
    chart.PlotArea.Area.FillFormat.FillType = FillType.None;
    chart.PlotArea.Border.IsVisible = false;
    // Set Chart Details

    // Show the Second-Axis
    chart.SecondValueAxis.IsVisible = true;
    chart.CategoryAxis.Title.Text = "X Title";
    chart.ValueAxis.Title.Text = $"Y Title";
    // Set the series
    var ceft_bottom_Series = chart.NSeries[chart.NSeries.Add("Sheet1!B2:B6", true)];
    ceft_bottom_Series.Area.FillFormat.FillType = FillType.None;

    var ceft_IPLV_Series = chart.NSeries[chart.NSeries.Add("Sheet1!F2:F6", true)];
    ceft_IPLV_Series.Area.FillFormat.FillType = FillType.Pattern;
    ceft_IPLV_Series.Area.FillFormat.PatternFill.Pattern = FillPattern.WideDownwardDiagonal;
    ceft_IPLV_Series.Area.FillFormat.PatternFill.ForegroundColor = Color.Green;
    ceft_IPLV_Series.Area.FillFormat.PatternFill.BackgroundColor = Color.White;
    ceft_IPLV_Series.Border.Color = Color.Black;
    ceft_IPLV_Series.Border.WeightPt = 2;

    var ceft_top_Series = chart.NSeries[chart.NSeries.Add("Sheet1!G2:G6", true)];
    ceft_top_Series.Area.FillFormat.FillType = FillType.Pattern;
    ceft_top_Series.Area.FillFormat.PatternFill.Pattern = FillPattern.WideUpwardDiagonal;
    ceft_top_Series.Area.FillFormat.PatternFill.ForegroundColor = Color.Red;
    ceft_top_Series.Area.FillFormat.PatternFill.BackgroundColor = Color.White;
    ceft_top_Series.Border.Color = Color.Black;
    ceft_top_Series.Border.WeightPt = 2;

    // Set the Second-Axis 
    var iplv_Column_Series = chart.NSeries[chart.NSeries.Add("Sheet1!E2:E6", true)];
    // Set the second series ChartType to ColumnStacked
    iplv_Column_Series.Type = Aspose.Cells.Charts.ChartType.ColumnStacked;
    iplv_Column_Series.PlotOnSecondAxis = true;
    iplv_Column_Series.Area.ForegroundColor = Color.Black;
    iplv_Column_Series.Area.Transparency = 0.85;
    iplv_Column_Series.Border.Color = Color.FromArgb(118, 113, 113);
    iplv_Column_Series.Border.WeightPt = 0.75;
    iplv_Column_Series.GapWidth = 0;
    iplv_Column_Series.DataLabels.LinkedSource = "Sheet1!H2:H6";
    iplv_Column_Series.DataLabels.ShowCellRange = true;
    iplv_Column_Series.DataLabels.Position = LabelPositionType.InsideEnd;
    iplv_Column_Series.Points[1].DataLabels.LinkedSource = "Sheet1!H3";
    iplv_Column_Series.Points[1].DataLabels.Position = LabelPositionType.InsideBase;
    iplv_Column_Series.Points[4].DataLabels.LinkedSource = "Sheet1!H6";
    iplv_Column_Series.Points[4].DataLabels.Position = LabelPositionType.InsideBase;
    // Set the category data
    chart.NSeries.CategoryData = "A2:A6";
    chart.ChartObject.Height = 400;
    chart.ChartObject.Width = 600;
    chart.ValueAxis.MinValue = 45.0;
    chart.ValueAxis.MaxValue = 90;
    chart.ValueAxis.MajorUnit = 10;
    chart.ValueAxis.MinorUnit = 10;
    chart.SecondValueAxis.MinValue = 0;
    chart.SecondValueAxis.MaxValue = 0.45;
    chart.SecondValueAxis.MajorUnit = 0.1;
    chart.SecondValueAxis.MinorUnit = 0.1;
    chart.CategoryAxis.MajorTickMark = TickMarkType.Outside;
    chart.CategoryAxis.MinorTickMark = TickMarkType.None;
    chart.CategoryAxis.AxisBetweenCategories = false;
    chart.SecondValueAxis.TickLabelPosition = TickLabelPositionType.None;
    chart.SecondValueAxis.MajorTickMark = TickMarkType.None;
    chart.SecondValueAxis.MinorTickMark = TickMarkType.None;
    chart.SecondValueAxis.AxisLine.IsVisible = false;

    chart.ValueAxis.MajorGridLines.IsVisible = false;
    workbook.CalculateFormula();
    // Save the workbook
    chart.ToImage($"image.png");
    workbook.Save($"excel.xlsx");
}

But, it show different result that DataLable lost for 25% and 100% point.
chart _from_excel.png (26.6 KB)
chart_from_toImage.png (6.1 KB)
compare.png (23.3 KB)

2

@Meekou
By using sample code for testing on the latest version v24.4, we were able to reproduce the issue of data loss when converting a chart to an image. Please refer to the attachment.
dataloss.PNG (68.5 KB)

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-55533

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

Is there any workaround to convert chart to image to get same result?

4

@Meekou
As a temporary solution, you can set properties for all ChartPoints to solve the problem. Please refer to the attachment. result.zip (22.4 KB)

The sample code as follows:

var data = new object[,]
{
{ "Category", "55", "65", "70", "Percent" },
{ 0, 55, 65, 70, 0 },
{ 0.25, 55, 65, 70, 0.12 },
{ 0.50, 55, 65, 70, 0.45 },
{ 0.75, 70.00, 75, 77.5, 0.42 },
{ 1.00, 85, 85, 85, 0.01 }
};

// Create a new workbook
Workbook workbook = new Workbook();
// Access the first worksheet in the workbook
Worksheet worksheet = workbook.Worksheets[0];

// Get the range of cells where you want to set values
int startRow = 0;
int startColumn = 0;
int numRows = data.GetLength(0);
int numCols = data.GetLength(1);
var range = worksheet.Cells.CreateRange(startRow, startColumn, numRows, numCols);

// Set values from the array to the range
range.Value = data;

// Calculate Data
// Calculate
for (int i = 1; i < numRows; i++)
{
    worksheet.Cells[i, 5].Formula = $"=C{i + 1} - B{i + 1}";
    worksheet.Cells[i, 6].Formula = $"=D{i + 1} - C{i + 1}";
    worksheet.Cells[i, 7].Formula = i == 1 ? "" : $"=CONCAT(C{i + 1},\"°\",\" Test \", CHAR(10), \"(\",TEXT(E{i + 1}, \"0%\"),\")\")";
}

// Format Data
var temperature_Bins = worksheet.Cells.CreateRange("A2:A6");
var temperatureBinsStyle = workbook.CreateStyle();
temperatureBinsStyle.Custom = @"##0%;[Red](##0%)";
temperature_Bins.SetStyle(temperatureBinsStyle);

// Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(ChartType.AreaStacked, 8, 0, 30, 10);
// Retrieve the Chart object
Chart chart = worksheet.Charts[chartIndex];
chart.ShowLegend = false;
chart.ChartArea.Border.IsVisible = false;
chart.PlotArea.Area.FillFormat.FillType = FillType.None;
chart.PlotArea.Border.IsVisible = false;
// Set Chart Details

// Show the Second-Axis
chart.SecondValueAxis.IsVisible = true;
chart.CategoryAxis.Title.Text = "X Title";
chart.ValueAxis.Title.Text = $"Y Title";
// Set the series
var ceft_bottom_Series = chart.NSeries[chart.NSeries.Add("Sheet1!B2:B6", true)];
ceft_bottom_Series.Area.FillFormat.FillType = FillType.None;

var ceft_IPLV_Series = chart.NSeries[chart.NSeries.Add("Sheet1!F2:F6", true)];
ceft_IPLV_Series.Area.FillFormat.FillType = FillType.Pattern;
ceft_IPLV_Series.Area.FillFormat.PatternFill.Pattern = FillPattern.WideDownwardDiagonal;
ceft_IPLV_Series.Area.FillFormat.PatternFill.ForegroundColor = Color.Green;
ceft_IPLV_Series.Area.FillFormat.PatternFill.BackgroundColor = Color.White;
ceft_IPLV_Series.Border.Color = Color.Black;
ceft_IPLV_Series.Border.WeightPt = 2;

var ceft_top_Series = chart.NSeries[chart.NSeries.Add("Sheet1!G2:G6", true)];
ceft_top_Series.Area.FillFormat.FillType = FillType.Pattern;
ceft_top_Series.Area.FillFormat.PatternFill.Pattern = FillPattern.WideUpwardDiagonal;
ceft_top_Series.Area.FillFormat.PatternFill.ForegroundColor = Color.Red;
ceft_top_Series.Area.FillFormat.PatternFill.BackgroundColor = Color.White;
ceft_top_Series.Border.Color = Color.Black;
ceft_top_Series.Border.WeightPt = 2;

// Set the Second-Axis 
var iplv_Column_Series = chart.NSeries[chart.NSeries.Add("Sheet1!E2:E6", true)];
// Set the second series ChartType to ColumnStacked
iplv_Column_Series.Type = Aspose.Cells.Charts.ChartType.ColumnStacked;
iplv_Column_Series.PlotOnSecondAxis = true;
iplv_Column_Series.Area.ForegroundColor = Color.Black;
iplv_Column_Series.Area.Transparency = 0.85;
iplv_Column_Series.Border.Color = Color.FromArgb(118, 113, 113);
iplv_Column_Series.Border.WeightPt = 0.75;
iplv_Column_Series.GapWidth = 0;
iplv_Column_Series.DataLabels.LinkedSource = "Sheet1!H2:H6";
iplv_Column_Series.DataLabels.ShowCellRange = true;

//set all ChartPoints
iplv_Column_Series.DataLabels.Position = LabelPositionType.InsideEnd;
iplv_Column_Series.Points[1].DataLabels.LinkedSource = "Sheet1!H3";
iplv_Column_Series.Points[1].DataLabels.Position = LabelPositionType.InsideBase;
iplv_Column_Series.Points[2].DataLabels.LinkedSource = "Sheet1!H4";
iplv_Column_Series.Points[2].DataLabels.Position = LabelPositionType.InsideEnd;
iplv_Column_Series.Points[3].DataLabels.LinkedSource = "Sheet1!H5";
iplv_Column_Series.Points[3].DataLabels.Position = LabelPositionType.InsideEnd;
iplv_Column_Series.Points[4].DataLabels.LinkedSource = "Sheet1!H6";
iplv_Column_Series.Points[4].DataLabels.Position = LabelPositionType.InsideBase;
// Set the category data
chart.NSeries.CategoryData = "A2:A6";
chart.ChartObject.Height = 400;
chart.ChartObject.Width = 600;
chart.ValueAxis.MinValue = 45.0;
chart.ValueAxis.MaxValue = 90;
chart.ValueAxis.MajorUnit = 10;
chart.ValueAxis.MinorUnit = 10;
chart.SecondValueAxis.MinValue = 0;
chart.SecondValueAxis.MaxValue = 0.45;
chart.SecondValueAxis.MajorUnit = 0.1;
chart.SecondValueAxis.MinorUnit = 0.1;
chart.CategoryAxis.MajorTickMark = TickMarkType.Outside;
chart.CategoryAxis.MinorTickMark = TickMarkType.None;
chart.CategoryAxis.AxisBetweenCategories = false;
chart.SecondValueAxis.TickLabelPosition = TickLabelPositionType.None;
chart.SecondValueAxis.MajorTickMark = TickMarkType.None;
chart.SecondValueAxis.MinorTickMark = TickMarkType.None;
chart.SecondValueAxis.AxisLine.IsVisible = false;

chart.ValueAxis.MajorGridLines.IsVisible = false;
workbook.CalculateFormula();
// Save the workbook
chart.ToImage(filePath + $"out_image.png");
workbook.Save(filePath + $"out_excel.xlsx");

Hope helps a bit.

1 Like
5

Hi @Meekou
Aspose.Cells 24.5 has released:

In the new version, we have optimized this problem, please use the new version, and tell us your feedback, thank you!

6

The issues you have found earlier (filed as CELLSNET-55533) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi