I’m having problem with modifying a bar chart data
I have a workbook with two worksheets
one has the data, raw data
the other is the bar chart, cycle time.
i’m using aspose.cell to populate the raw data worsheet without issue.
however, I want my chart to adjust according to the data range of the data. Here’s a sample of what it can look like.
Hi Tan,
I tried that and the chart didn’t refresh with the data range of the table.
Hi Tan,
var book = new Workbook(myDir + “Median_CT.xlsx”);<o:p></o:p>
var sheet = book.Worksheets[1];<o:p></o:p>
var cells = sheet.Cells;<o:p></o:p>
for (int i = 13; i < 84; i++)<o:p></o:p>
{<o:p></o:p>
cells[i, 0].PutValue("Data " + i);<o:p></o:p>
cells[i, 1].PutValue(i + 100 % 13);<o:p></o:p>
}<o:p></o:p>
sheet = book.Worksheets[0];<o:p></o:p>
var chart = sheet.Charts[0];<o:p></o:p>
chart.Calculate();<o:p></o:p>
book.Save(myDir + “output.xlsx”);<o:p></o:p>
I’m currently using v 8.0.1.0.
This is what I have in a function to refresh the workbook.
if (ExcelWK != null)
{
foreach (Aspose.Cells.Worksheet worksheet in ExcelWK.Worksheets)
{
if (worksheet.PivotTables.Count > 0)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
try
{
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.RefreshDataFlag = false;
pivotTable.CalculateData();
pivotTable.RefreshDataOnOpeningFile = true;
}
catch (Exception ex)
{
ErrLog.LogError(new LogException("Report: RefreshPivots error on Worksheet: " + worksheet.Name + " pivot table: " + pivotTable.Name, ex), true, false);
}
}
}
if (worksheet.Charts.Count > 0)
{
foreach (Aspose.Cells.Charts.Chart chart in worksheet.Charts)
{
try
{
chart.RefreshPivotData();
chart.Calculate();
}
catch (Exception ex)
{
ErrLog.LogError(new logException("PMT Generate Report: Chart RefreshPivotData error on Worksheet: " + worksheet.Name + " chart: " + chart.Name, ex), true, false);
}
}
}
}
//ExcelWK.CalculateFormula();
}
}
Hi Tan,
var ExcelWK = new Workbook(myDir + “Median_CT.xlsx”);
var sheet = ExcelWK.Worksheets[1];
var cells = sheet.Cells;
for (int i = 13; i < 84; i++)
{
cells[i, 0].PutValue("Data " + i);
cells[i, 1].PutValue(i + 100 % 13);
}
if (ExcelWK != null)
{
foreach (Aspose.Cells.Worksheet worksheet in ExcelWK.Worksheets)
{
if (worksheet.PivotTables.Count > 0)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
try
{
pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.RefreshDataFlag = false;
pivotTable.CalculateData();
pivotTable.RefreshDataOnOpeningFile = true;
}
catch (Exception ex)
{
Console.WriteLine("Report: RefreshPivots error on Worksheet: " + worksheet.Name + " pivot table: " + pivotTable.Name);
}
}
}
if (worksheet.Charts.Count > 0)
{
foreach (Aspose.Cells.Charts.Chart chart in worksheet.Charts)
{
try
{
chart.RefreshPivotData();
chart.Calculate();
}
catch (Exception ex)
{
Console.WriteLine("PMT Generate Report: Chart RefreshPivotData error on Worksheet: " + worksheet.Name + " chart: " + chart.Name);
}
}
}
} // ExcelWK.CalculateFormula(); } }
ExcelWK.Save(myDir + “output.xlsx”);
I tried using the Aspose.Cell 8.1.0 and it just locks up when it tries to generate the data. Should there be any issue with license from 8.0.1 to 8.1.0?
Hi,
For the example you did above, you had the data populated for the same range as the chart selected data range. If you populate only half of the data (data 1- 30), and do the Chart.Calculate(), can you see if that updates the chart?
I’ve checked the license and it’s good till 2015.
How would I create the chart? When I tried, it created multiple series rather than a single series.
phamtq:
For the example you did above, you had the data populated for the same range as the chart selected data range. If you populate only half of the data (data 1- 30), and do the Chart.Calculate(), can you see if that updates the chart?
var book = new Workbook(myDir + "Median_CT.xlsx");
var chartWorksheet = book.Worksheets[0];
var chart = chartWorksheet.Charts[0];
try
{
var series = chart.NSeries[0];
series.Name = "new";
series.Values = "='raw data'!$B$2:$B$13";
series.XValues = "='raw data'!$A$2:$A$13";
chart.Calculate();
}
catch (Exception ex)
{
Console.WriteLine("chart: " + chart.Name + " Message: " + ex.Message);
}
book.Save(myDir + "output.xlsx");
Thanks. That works for me.
Hi Tan,