Chart do not show category, legend, x axis labels after export to excel file
Hi team,
I have issue when export this chart:
to excel file, the chart do not show category, legend, x axis labels after export to excel file, result detail in picture:
The chart export to excel not true:
- Do not show category, legend, x axis labels after export to excel file
- series data value not have value of cell B3:C10
Can you instruct me to fix this issue, this is my code:
void _addCharts(xlsio.Worksheet sheet, ExcelSheetState state, xlsio.Workbook workbook) {
final officechart.ChartCollection charts = officechart.ChartCollection(sheet);
state.objectManager.charts.forEach((id, sheetChartNotifier) {
final sheetChart = sheetChartNotifier.value;
print('--- [DEBUG] Starting to process chart ID: $id ---');
print(' - Title: ${sheetChart.chartTitle}');
print(' - DataRangeKey: "${sheetChart.dataRangeKey}"');
print(' - Is Series in Rows: ${sheetChart.dataIsRows}');
print(' - Categories from state: ${sheetChart.dataSource.categories}');
for (int i = 0; i < sheetChart.dataSource.series.length; i++) {
final series = sheetChart.dataSource.series[i];
print(' + Series ${i + 1} from state: ${series.name}');
print(' - Source Cells for values: ${series.sourceCells}');
final values = series.points.map((p) => p['y']).toList();
print(' - Actual values: $values');
}
print('--------------------------------------------------');
final String fullRangeAddress = sheetChart.dataRangeKey.replaceAll('"', '');
final xlsio.Range fullRange = sheet.getRangeByName(fullRangeAddress);
if (sheetChart.dataSource.categories.isNotEmpty) {
final int categoryCol = fullRange.column;
final int firstDataRow = fullRange.row + 1;
for (int i = 0; i < sheetChart.dataSource.categories.length; i++) {
final String categoryText = sheetChart.dataSource.categories[i];
final int targetRow = firstDataRow + i;
final String cellKey = ExcelSheetState.getCellKey(targetRow, categoryCol);
sheet.getRangeByName(cellKey).setText(categoryText);
}
}
for (final series in sheetChart.dataSource.series) {
for (final point in series.points) {
final String cellKey = point['sourceCellKey'] as String? ?? '';
if (cellKey.isEmpty) continue;
final dynamic yValue = point['y'];
if (yValue is num) {
sheet.getRangeByName(cellKey).setNumber(yValue.toDouble());
}
}
}
final officechart.Chart chart = charts.add();
final Offset topLeftPosition = sheetChart.position;
final Offset bottomRightPosition = Offset(sheetChart.position.dx + sheetChart.width, sheetChart.position.dy + sheetChart.height);
String? startCellKey = state.getCellFromOffset(topLeftPosition);
String? endCellKey = state.getCellFromOffset(bottomRightPosition);
if (startCellKey == null) return;
endCellKey ??= startCellKey;
chart.topRow = ExcelSheetState.getRowIndex(startCellKey);
chart.leftColumn = ExcelSheetState.getColumnIndex(startCellKey);
chart.bottomRow = ExcelSheetState.getRowIndex(endCellKey);
chart.rightColumn = ExcelSheetState.getColumnIndex(endCellKey);
chart.chartType = _mapChartType(sheetChart.type);
if (sheetChart.chartTitle.isNotEmpty) {
chart.chartTitle = sheetChart.chartTitle;
_applyTitleFormatting(chart.chartTitleArea, sheetChart.chartTitleStyle);
}
chart.hasLegend = sheetChart.legendIsVisible;
chart.dataRange = fullRange;
chart.isSeriesInRows = sheetChart.dataIsRows;
print('>>> [DEBUG] After setting dataRange, the library automatically created ${chart.series.count} series.');
final int categoryCol = fullRange.column;
final int firstDataRow = fullRange.row + 1;
final String categoryAddress = ExcelSheetState.getCellKey(firstDataRow, categoryCol) + ':' + ExcelSheetState.getCellKey(fullRange.lastRow, categoryCol);
print(' - [DEBUG] Calculated Category Address: $categoryAddress');
chart.primaryCategoryAxis.categoryLabels = sheet.getRangeByName(categoryAddress);
for (int i = 0; i < chart.series.count; i++) {
print(' --- [DEBUG] Processing library-generated series at index: $i ---');
if (i >= sheetChart.dataSource.series.length) break;
final officechart.ChartSerie officeSerie = chart.series[i];
final ChartSeriesData mySeriesData = sheetChart.dataSource.series[i];
final int valueCol = fullRange.column + 1 + i;
final String seriesNameCellKey = ExcelSheetState.getCellKey(fullRange.row, valueCol);
print(' - Assigning Series Name from cell: $seriesNameCellKey');
officeSerie.name = '=${sheet.name}!$seriesNameCellKey';
final String valuesRange = _getRangeFromCells(mySeriesData.sourceCells);
print(' - Assigning Series Values from range: $valuesRange');
officeSerie.values = sheet.getRangeByName(valuesRange);
print(' - Assigning Series CategoryLabels from range: $categoryAddress');
officeSerie.categoryLabels = sheet.getRangeByName(categoryAddress);
_applySeriesFormatting(officeSerie, mySeriesData, sheetChart);
}
_applyAxisSettings(chart.primaryCategoryAxis, sheetChart.xAxisSettings);
if(chart.hasLegend && chart.legend != null) {
_applyLegendSettings(chart.legend!, sheetChart);
}
});
sheet.charts = charts;
}
void _applyAxisSettings(officechart.ChartAxis axis, ChartAxisSettings settings) {
if (settings.title.isNotEmpty) {
axis.title = settings.title;
final titleArea = axis.titleArea;
titleArea.fontName = settings.titleStyle.fontFamily ?? 'Calibri';
titleArea.size = (settings.titleStyle.fontSize ?? 10).toDouble();
titleArea.color = _colorToExcelFormat(settings.titleStyle.color ?? Colors.black);
titleArea.bold = settings.titleStyle.fontWeight == FontWeight.bold;
titleArea.italic = settings.titleStyle.fontStyle == FontStyle.italic;
}
axis.numberFormat = _buildNumberFormatFromSettings(settings);
if (settings.minimum != null) axis.minimumValue = settings.minimum!;
if (settings.maximum != null) axis.maximumValue = settings.maximum!;
axis.hasMajorGridLines = settings.majorGridLineWidth > 0;
}
and here debug:
I/flutter (30475): --- [DEBUG] Starting to process chart ID: 62f1a0eb-3c9f-4ecd-bd94-29c14dfd0d5c ---
I/flutter (30475): - Title: Chart for A2:C10
I/flutter (30475): - DataRangeKey: "A2:C10"
I/flutter (30475): - Is Series in Rows: false
I/flutter (30475): - Categories from state: [Caption, Footnote, Formula, List-item, Page-footer, Page-header, Picture, Section-header]
I/flutter (30475): + Series 1 from state: Doc
I/flutter (30475): - Source Cells for values: [B3, B4, B5, B6, B7, B8, B9, B10]
I/flutter (30475): - Actual values: [68.0, 71.0, 60.0, 81.0, 62.0, 72.0, 72.0, 68.0]
I/flutter (30475): + Series 2 from state: Page
I/flutter (30475): - Source Cells for values: [C3, C4, C5, C6, C7, C8, C9, C10]
I/flutter (30475): - Actual values: [83.0, 84.0, 66.0, 88.0, 89.0, 90.0, 82.0, 83.0]
I/flutter (30475): --------------------------------------------------
I/flutter (30475): >>> [DEBUG] After setting dataRange, the library automatically created 2 series.
I/flutter (30475): - [DEBUG] Calculated Category Address: A3:A10
I/flutter (30475): --- [DEBUG] Processing library-generated series at index: 0 ---
I/flutter (30475): - Assigning Series Name from cell: B2
I/flutter (30475): - Assigning Series Values from range: B3:B10
I/flutter (30475): - Assigning Series CategoryLabels from range: A3:A10
I/flutter (30475): --- [DEBUG] Processing library-generated series at index: 1 ---
I/flutter (30475): - Assigning Series Name from cell: C2
I/flutter (30475): - Assigning Series Values from range: C3:C10
I/flutter (30475): - Assigning Series CategoryLabels from range: A3:A10
Thank you so much
Thank for yours response
I already flowed your instruct:
void _addCharts(xlsio.Worksheet sheet, ExcelSheetState state, xlsio.Workbook workbook) {
sheet.getRangeByName('A1').setText('Split');
sheet.getRangeByName('B1').setText('Doc');
sheet.getRangeByName('C1').setText('Page');
// Data rows
final List<List<dynamic>> data = [
['Caption', 68, 83],
['Footnote', 71, 84],
['Formula', 60, 66],
['List-item', 81, 88],
['Page-footer', 62, 89],
['Page-header', 72, 90],
['Picture', 72, 82],
['Section-header', 68, 83],
];
for (int i = 0; i < data.length; i++) {
sheet.getRangeByIndex(i + 2, 1).setText(data[i][0]); // Column A
sheet.getRangeByIndex(i + 2, 2).setNumber((data[i][1] as num).toDouble()); // Column B
sheet.getRangeByIndex(i + 2, 3).setNumber((data[i][2] as num).toDouble()); // Column C
}
final officechart.ChartCollection charts = officechart.ChartCollection(sheet);
// Add chart
final officechart.Chart chart = charts.add();
chart.chartType = officechart.ExcelChartType.line;
chart.dataRange = sheet.getRangeByName('A1:C9');
chart.isSeriesInRows = false;
chart.chartTitle = 'Chart for A2:C10';
final officechart.ChartSerie series1 = chart.series[0];
final officechart.ChartSerie series2 = chart.series[1];
chart.topRow = 11;
chart.bottomRow = 25;
chart.leftColumn = 1;
chart.rightColumn = 7;
chart.legend!.position = officechart.ExcelLegendPosition.top;
sheet.charts = charts;
}
but the result not change:
I think this is limit of syncfusion chart office in flutter, this can not build real chart, with true value from cell
Thank for yours response
I already flowed your instruct:
void _addCharts(xlsio.Worksheet sheet, ExcelSheetState state, xlsio.Workbook workbook) {
sheet.getRangeByName('A1').setText('Split');
sheet.getRangeByName('B1').setText('Doc');
sheet.getRangeByName('C1').setText('Page');
// Data rows
final List<List<dynamic>> data = [
['Caption', 68, 83],
['Footnote', 71, 84],
['Formula', 60, 66],
['List-item', 81, 88],
['Page-footer', 62, 89],
['Page-header', 72, 90],
['Picture', 72, 82],
['Section-header', 68, 83],
];
for (int i = 0; i < data.length; i++) {
sheet.getRangeByIndex(i + 2, 1).setText(data[i][0]); // Column A
sheet.getRangeByIndex(i + 2, 2).setNumber((data[i][1] as num).toDouble()); // Column B
sheet.getRangeByIndex(i + 2, 3).setNumber((data[i][2] as num).toDouble()); // Column C
}
final officechart.ChartCollection charts = officechart.ChartCollection(sheet);
// Add chart
final officechart.Chart chart = charts.add();
chart.chartType = officechart.ExcelChartType.line;
chart.dataRange = sheet.getRangeByName('A1:C9');
chart.isSeriesInRows = false;
chart.chartTitle = 'Chart for A2:C10';
final officechart.ChartSerie series1 = chart.series[0];
final officechart.ChartSerie series2 = chart.series[1];
chart.topRow = 11;
chart.bottomRow = 25;
chart.leftColumn = 1;
chart.rightColumn = 7;
chart.legend!.position = officechart.ExcelLegendPosition.top;
sheet.charts = charts;
}
but the result not change:
I think this is limit of syncfusion chart office in flutter, this can not build real chart, with true value from cell
Thank you very much !
My project process is user will input data, after that I use syncfusion flutter chart to draw chart base on user data, and user can save those include input data and chart to excel file.
data input as picture:
But , seem syncfusion office chart and xlsio can only set data, and can not get data as I think ?
It is my lucky, if you can review my code in attachment, and give me the advice, what should I do ?
Thank you very much !
Attachment: ExcelExporter_2606230b.zip
