Create two Charts in the same Excel file : One displaying the highest and the other the lowest values.
Thanks in advance for some help.
From a local Json file i am trying to create a Excel file with two Charts.
One Chart would need to display the all highest values of the
"open_force_array"
for each count
and the second would need to display the lowest values of the
"open_force_array" .
The Json looks for example as follows:
```
{
"measure": [
{
"count": 9449,
"open_force_array": [
19.7,
20.6,
10.4,
0.9
],
"close_force_array": [
5.8,
272,
624.6,
949
]
},
{
"count": 9450,
"open_force_array": [
29.8,
20.5,
10.9,
1
],
"close_force_array": [
2,
266.8,
589,
948.1
]
},
{
"count": 9451,
"open_force_array": [
9.5,
20.3,
10.7,
0.9
],
"close_force_array": [
5.7,
272.7,
609.5,
950.8
]
},
{
"count": 9452,
"open_force_array": [
12.8,
20.5,
10.7,
0.8
],
"close_force_array": [
1.9,
270.9,
608.2,
958
]
}
]
}
```
In my code if i use the following function:
```
void importJsonData(String fileName) async {
// Create a new Workbook
final Workbook workbook = Workbook();
final Worksheet sheet = workbook.worksheets[0];
sheet.showGridlines = true; // Enable-Disable Gridlines
// Initialize the list
List measure = data!['measure'];
List<Object> listCount = [], listMaxOpenForce = [], listMinOpenForce = [];
sheet.getRangeByName('A1').setText('Cycle');
sheet.getRangeByName('B1').setText('Max Open Force');
sheet.getRangeByName('C1').setText('Min Open Force');
for (var i = 0; i < measure.length; i++) {
listCount.add(measure[i]['count'].toString());
double maxOpenForce = measure[i]['open_force_array'].reduce((max, e) => max = max > e ? max : e);
double minOpenForce = measure[i]['open_force_array'].reduce((min, e) => min = min > e ? min : e);
listMaxOpenForce.add(maxOpenForce);
listMinOpenForce.add(minOpenForce);
}
sheet.importList(listCount, 2, 1, true);
sheet.importList(listMaxOpenForce, 2, 2, true);
sheet.importList(listMinOpenForce, 2, 3, true);
// Create an instance of chart collection.
final ChartCollection charts = ChartCollection(sheet);
// Add the chart for the highest value in OPEN FORCE.
final Chart maxopenForceChart = charts.add();
maxopenForceChart.chartType = ExcelChartType.lineMarkers;
// Set data range for the chart.
maxopenForceChart.dataRange = sheet.getRangeByIndex(1, 1, measure.length + 1, 2);
maxopenForceChart.isSeriesInRows = false;
// Set the X-axis values (count values).
maxopenForceChart.primaryCategoryAxis.numberFormat = '0'; // Display integers on the X-axis.
// Find the maximum value from all "open_force_array" values.
double maxOpenForce = measure
.map((item) => item['open_force_array'].reduce((max, e) => max = max > e ? max : e))
.reduce((max, e) => max = max > e ? max : e);
// Set the maximum value for the Y-axis.
maxopenForceChart.primaryValueAxis.maximumValue = maxOpenForce;
// Add the chart for the lowest value in OPEN FORCE.
final Chart minopenForceChart = charts.add();
minopenForceChart.chartType = ExcelChartType.lineMarkers;
// Set data range for the chart.
minopenForceChart.dataRange = sheet.getRangeByIndex(1, 1, measure.length + 1, 3);
minopenForceChart.isSeriesInRows = false;
// Set the X-axis values (count values).
minopenForceChart.primaryCategoryAxis.numberFormat = '0'; // Display integers on the X-axis.
// Find the minimum value from all "open_force_array" values.
double minOpenForce = measure
.map((item) => item['open_force_array'].reduce((min, e) => min = min > e ? min : e))
.reduce((min, e) => min = min > e ? min : e);
// Set the minimum value for the Y-axis.
minopenForceChart.primaryValueAxis.minimumValue = minOpenForce;
// Set charts to the worksheet.
sheet.charts = charts;
// Save the workbook to a file
final List<int> bytes = workbook.saveAsStream();
await saveAndLaunchFile(bytes, '$fileName.xlsx');
}
```
It creates this excel:
But i would need to create a excel like this:
Follows the complete code and the desired Excel file:
import 'package:flutter/material.dart';
import 'package:syncfusion_flutter_xlsio/xlsio.dart' hide Column, Row;
import 'package:syncfusion_officechart/officechart.dart';
import 'package:path_provider/path_provider.dart';
import 'dart:io';
import 'dart:convert';
import 'package:file_picker/file_picker.dart';
//***********************************************************************************************
//** class JsonExcelView
//***********************************************************************************************
class JsonExcelView extends StatefulWidget {
const JsonExcelView({super.key});
@override
State<JsonExcelView> createState() => JsonExcelViewState();
}
//***********************************************************************************************
//** class JsonExcelViewState
//***********************************************************************************************
class JsonExcelViewState extends State<JsonExcelView> {
Map<String, dynamic>? data;
var pickjson = "";
TextEditingController fileNameController = TextEditingController();
var fileName = "";
late String? _selectedFolderPath = "";
@override
void dispose() {
fileNameController.dispose();
super.dispose();
}
@override
void initState() {
super.initState();
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
actions: <Widget>[
ElevatedButton(
child: const Text('Pick JSON', textAlign: TextAlign.center),
onPressed: () async {
FilePickerResult? result = await FilePicker.platform.pickFiles(
type: FileType.custom,
allowedExtensions: ['json'],
);
if (result == null) {
WidgetsBinding.instance.addPostFrameCallback((_) {
showDialog(
context: context,
builder: (context) {
return AlertDialog(
content: const Text("You did not select any File."),
actions: <Widget>[
ElevatedButton(
child: const Text("OK"),
onPressed: () {
Navigator.of(context).pop();
},
),
],
);
},
);
});
} else {
// Use the complete file path instead of just the filename
pickjson = result.files.single.path!;
_loadJsonData();
}
},
),
ElevatedButton(
child: const Text('Create EXCEL', textAlign: TextAlign.center),
onPressed: () {
showDialog(
context: context,
builder: (context) {
return AlertDialog(
title: const Text("Enter file name"),
content: TextField(
controller: fileNameController,
),
actions: <Widget>[
ElevatedButton(
child: const Text("Cancel"),
onPressed: () {
Navigator.of(context).pop();
},
),
ElevatedButton(
child: const Text("OK"),
onPressed: () async {
Navigator.of(context).pop();
_selectedFolderPath = (await FilePicker.platform.getDirectoryPath());
if (_selectedFolderPath != null) importJsonData(fileNameController.text);
},
),
],
);
},
);
},
),
],
),
body: CustomScrollView(
slivers: [
SliverGrid.count(
crossAxisCount: 1,
mainAxisSpacing: 10.0,
crossAxisSpacing: 10.0,
childAspectRatio: 1,
children: [
Card(
child: data == null
? Column(
mainAxisAlignment: MainAxisAlignment.start,
children: <Widget>[
const SizedBox(
height: 160,
),
Text(
'Pick a JSON File',
style: Theme.of(context).textTheme.titleLarge,
),
],
)
: (data!['measure'] == null)
? Center(
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
children: <Widget>[
const SizedBox(
height: 20,
),
Text(
'The JSON file is empty or has an incorrect structure!',
style: Theme.of(context).textTheme.titleLarge,
),
],
),
)
: ListView.builder(
/* One ListViewhas two properties ( addRepaintBoundaryand addAutomaticKeepAlives) that can lead to high memory
consumption in certain cases. They tell ListViewto not discard items that are not currently visible on the screen.
In terms of performance, this is good because scrolling is fast. But invisible items still take up memory space.
If you have a long list of images this can cause problems.
The solution is to set the mentioned properties to false (they are set to true by default). */
addAutomaticKeepAlives: false,
addRepaintBoundaries: false,
itemCount: data!['measure'].length,
itemBuilder: (context, index) {
return Card(
child: Padding(
padding: const EdgeInsets.all(4.0),
child: Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: <Widget>[
Text(
'COUNT: ${data!['measure'][index]['count']}',
style: const TextStyle(fontSize: 12.0, letterSpacing: 1.5),
),
const SizedBox(height: 2.0),
Text(
'OPEN FORCE: ${data!['measure'][index]['open_force_array'].join(',')}',
style: const TextStyle(fontSize: 12.0, letterSpacing: 1.5),
),
const SizedBox(height: 2.0),
Text(
'CLOSE FORCE: ${data!['measure'][index]['close_force_array'].join(',')}',
style: const TextStyle(fontSize: 12.0, letterSpacing: 1.5),
),
],
),
),
);
},
),
)
],
),
],
),
);
}
//***********************************************************************************************
//** void _loadJsonData()
//***********************************************************************************************
void _loadJsonData() async {
try {
final File file = File(pickjson); // Use pickjson directly to access the file path
String jsonString = await file.readAsString();
dynamic decoded = jsonDecode(jsonString);
if (decoded == null || !decoded.containsKey("measure")) {
WidgetsBinding.instance.addPostFrameCallback((_) {
showDialog(
context: context,
builder: (context) {
return AlertDialog(
title: const Text("Error"),
content: const Text("The JSON file is empty or has an incorrect structure."),
actions: <Widget>[
ElevatedButton(
child: const Text("OK"),
onPressed: () {
Navigator.of(context).pop();
},
),
],
);
},
);
});
} else {
data = decoded;
setState(() {});
}
} catch (e) {
print("An error occurred while trying to retrieve data from the JSON file: $e");
}
}
//***********************************************************************************************
//** void importJsonData
//***********************************************************************************************
void importJsonData(String fileName) async {
// Create a new Workbook
final Workbook workbook = Workbook();
final Worksheet sheet = workbook.worksheets[0];
sheet.showGridlines = true; // Enable-Disable Gridlines
// Initialize the list
List measure = data!['measure'];
List<Object> listCount = [], listMaxOpenForce = [], listMinOpenForce = [];
sheet.getRangeByName('A1').setText('Cycle');
sheet.getRangeByName('B1').setText('Max Open Force');
sheet.getRangeByName('C1').setText('Min Open Force');
for (var i = 0; i < measure.length; i++) {
listCount.add(measure[i]['count'].toString());
double maxOpenForce = measure[i]['open_force_array'].reduce((max, e) => max = max > e ? max : e);
double minOpenForce = measure[i]['open_force_array'].reduce((min, e) => min = min < e ? min : e);
listMaxOpenForce.add(maxOpenForce);
listMinOpenForce.add(minOpenForce);
}
sheet.importList(listCount, 2, 1, true);
sheet.importList(listMaxOpenForce, 2, 2, true);
sheet.importList(listMinOpenForce, 2, 3, true);
// Create an instance of chart collection.
final ChartCollection charts = ChartCollection(sheet);
// Add the chart for the highest value in OPEN FORCE.
final Chart maxopenForceChart = charts.add();
maxopenForceChart.chartType = ExcelChartType.lineMarkers;
// Set data range for the chart.
maxopenForceChart.dataRange = sheet.getRangeByIndex(1, 1, measure.length + 1, 2);
maxopenForceChart.isSeriesInRows = false;
// Set the X-axis values (count values).
maxopenForceChart.primaryCategoryAxis.numberFormat = '0'; // Display integers on the X-axis.
// Find the maximum value from all "open_force_array" values.
double maxOpenForce = measure
.map((item) => item['open_force_array'].reduce((max, e) => max = max > e ? max : e))
.reduce((max, e) => max = max > e ? max : e);
// Set the maximum value for the Y-axis.
maxopenForceChart.primaryValueAxis.maximumValue = maxOpenForce;
// Add the chart for the lowest value in OPEN FORCE.
final Chart minopenForceChart = charts.add();
minopenForceChart.chartType = ExcelChartType.lineMarkers;
// Set data range for the chart.
minopenForceChart.dataRange = sheet.getRangeByIndex(1, 1, measure.length + 1, 3);
minopenForceChart.isSeriesInRows = false;
// Set the X-axis values (count values).
minopenForceChart.primaryCategoryAxis.numberFormat = '0'; // Display integers on the X-axis.
// Find the minimum value from all "open_force_array" values.
double minOpenForce = measure
.map((item) => item['open_force_array'].reduce((min, e) => min = min < e ? min : e))
.reduce((min, e) => min = min < e ? min : e);
// Set the minimum value for the Y-axis.
minopenForceChart.primaryValueAxis.minimumValue = minOpenForce;
// Set charts to the worksheet.
sheet.charts = charts;
// Save the workbook to a file
final List<int> bytes = workbook.saveAsStream();
await saveAndLaunchFile(bytes, '$fileName.xlsx');
}
}
//***********************************************************************************************
//** void saveAndLaunchFile
//***********************************************************************************************
Future<void> saveAndLaunchFile(List<int> bytes, String fileName) async {
String? path;
final Directory directory = await getApplicationDocumentsDirectory();
path = directory.path;
final File file = File(Platform.isWindows ? '$path\\$fileName' : '$path/$fileName');
await file.writeAsBytes(bytes, flush: true);
await Process.run('start', <String>['$path\\$fileName'], runInShell: true);
}
Attachment: excel_47a905c1.zip
SIGN IN To post a reply.
1 Reply
SIGN IN To post a reply.
