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

Free Support Forum - aspose.com

Reflect the changes in worksheet on charts

Hi,

I have made changes in the worksheet of the charts in my slides but the changes are not reflected on the chart. these changes are visible only when we click on ‘Edit Data’.
Is there any way to refresh the data so that the changes are reflected on the charts.Also, I have to change the font color to black which is by default light grey in color.
Following is the code snippet that I have used to change data in the worksheet.

public void FillDataToChartWorkbookDirectly(Chart chart, DataTable dt)
{
MemoryStream mem = chart.ChartData.ReadWorkbookStream();
mem.Position = 0;

        Workbook wb = new Workbook(mem);
        var sheet = wb.Worksheets[0];            
        sheet.Cells.DeleteRows(1, sheet.Cells.MaxRow + 1);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                sheet.Cells[i + 1, j].Value = dt.Rows[i][j];
                Style style = sheet.Cells[i + 1, j].GetStyle();
                style.Font.Color = Color.Black;
                sheet.Cells[i + 1, j].SetStyle(style);
            }
        }

        MemoryStream outstream = new MemoryStream();
        wb.Save(outstream, Aspose.Cells.SaveFormat.Xlsx);
        outstream.Position = 0;
        chart.ChartData.WriteWorkbookStream(outstream);
    }

@shubhamkataria143,

Can you please share source file along with generated results so that we may further investigate to help you out. Also the code snippet you have shared includes some undeclared variables so would you please share your SSCCE code reproducing the issue so that we may try to reproduce and investigate it in our environment.

here is the code snippet with all the declared variables

public void FillDataToChartWorkbookDirectly()
{
Presentation TemplatePpt = new Presentation(@“E:\excavate\Sachin sir\Tabs2PPT.V2\Tabs2PPT.V2\Data\Copy.pptx”);
var slide = TemplatePpt.Slides[10];
var shape = (Shape)slide.Shapes.FirstOrDefault(x => x is Chart);
DataTable dt = new DataTable();
Workbook workb = new Workbook(@“E:\excavate\Sachin sir\Tabs2PPT.V2\Tabs2PPT.V2\Data\UK.xlsx”);
var sheets = workb.Worksheets[0];
dt = sheets.Cells.ExportDataTable(0, 0, sheets.Cells.MaxDataRow + 1, sheets.Cells.MaxDataColumn + 1, new ExportTableOptions() { CheckMixedValueType = true, ExportColumnName = true });
var chart = (Chart)shape;
MemoryStream mem = chart.ChartData.ReadWorkbookStream();
mem.Position = 0;

        Workbook wb = new Workbook(mem);
        var sheet = wb.Worksheets[0];
        if(dt.Rows.Count >2 && sheet.Cells.Rows.Count > dt.Rows.Count)
        {
            sheet.Cells.DeleteRows(dt.Rows.Count, sheet.Cells.MaxRow + 1);
        }
        //if (!yes)
        //    sheet.Cells.DeleteRows(1, sheet.Cells.MaxRow + 1);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                float val = 0;
                string value = Convert.ToString(dt.Rows[i][j]);
                Style style = sheet.Cells[i + 1, j].GetStyle();
                if (float.TryParse(value, out val))
                {
                    if (val >= 1)
                        sheet.Cells[i + 1, j].Value = val;
                    else
                    {
                        sheet.Cells[i + 1, j].Value = val;
                        style.CultureCustom = "0%";
                    }
                }
                else
                    sheet.Cells[i + 1, j].Value = dt.Rows[i][j];
                style.Font.Color = Color.Black;
                sheet.Cells[i + 1, j].SetStyle(style);
            }
        }

        MemoryStream outstream = new MemoryStream();
        wb.Save(outstream, Aspose.Cells.SaveFormat.Xlsx);
        outstream.Position = 0;
        chart.ChartData.WriteWorkbookStream(outstream);
    }

@shubhamkataria143,

Can you please share source file and generated results so that i may test sample code with your source file and investigate this issue further.