We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Creating Excel who displayed values and linear chart from selected data inside a local Json

I am able to load data from a local JSON file and displays it in a dropdown menu.  Then select a value from the dropdown and the application will display the corresponding "Range Array" and "Force Array" from the JSON data. I want to generate a Excel sheet with the data that was selected from the dropdown. The generated Excel would need to look like this:

Untitled1.png

At this moment i am able to generate a Excel that displays the values but without Chart. 

The Excel looks like this:

Untitled.png

But at this point i think how i am generating the Excel is not the right way.

Because the Excel would need to look like this, so that i could generate a Chart .

Untitled.png


Follows the code that i have to read from the local Json and generating the Excel:

```

import 'package:flutter/material.dart';
import 'package:flutter/services.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';

void main() => runApp(const MyApp());

class MyApp extends StatelessWidget {
  const MyApp({super.key});

  @override
  Widget build(BuildContext context) {
    return const MaterialApp(
      home: JsonExcelView(),
    );
  }
}

//***********************************************************************************************
//**                               class JsonExcelView
//***********************************************************************************************
class JsonExcelView extends StatefulWidget {
  const JsonExcelView({super.key});

  @override
  State<JsonExcelView> createState() => JsonExcelViewState();
}

class JsonExcelViewState extends State<JsonExcelView> {
  var data;
  late Map<String, dynamic> measure;
  var selectedCount;

  @override
  void initState() {
    super.initState();
    _loadJsonData();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      body: data != null
          ? Column(
              children: <Widget>[
                Container(
                  padding: const EdgeInsets.only(left: 20, right: 20),
                  child: ElevatedButton(
                    child: const Text("Create Excel with Chart"),
                    onPressed: () async {
                      generateExcellOfficeChart("test");
                    },
                  ),
                ),
                DropdownButton(
                  items: (data["measure"] as List<dynamic>)
                      .map((item) => DropdownMenuItem(
                            value: item["count"],
                            child: Text(item["count"].toString()),
                          ))
                      .toList(),
                  onChanged: (value) {
                    setState(() {
                      selectedCount = value;
                    });
                  },
                  value: selectedCount,
                ),
                Row(
                  mainAxisAlignment: MainAxisAlignment.center,
                  children: [
                    Column(
                      children: [
                        const Text("Range Array:"),
                        Text(data["measure"].firstWhere((item) => item["count"] == selectedCount)["range_array"].map((e) => e.toString()).toList().join('\n'))
                      ],
                    ),
                    const SizedBox(
                      width: 30,
                    ),
                    Column(
                      children: [
                        const Text("Force Array:"),
                        Text(data["measure"].firstWhere((item) => item["count"] == selectedCount)["force_array"].map((e) => e.toString()).toList().join('\n'))
                      ],
                    ),
                  ],
                ),
              ],
            )
          : const Center(
              child: CircularProgressIndicator(),
            ),
    );
  }

  void _loadJsonData() async {
    String jsonString = await rootBundle.loadString('assets/data.json');
    data = jsonDecode(jsonString);
    selectedCount = data["measure"][0]["count"];
    setState(() {});
  }

  void generateExcellOfficeChart(String fileName) async {
    // Create a new Workbook
    final Workbook workbook = Workbook();
    final Worksheet sheet = workbook.worksheets[0];
    sheet.showGridlines = true; // Enable-Disable Gridlines

// Setting value in the cell.
    sheet.getRangeByName('A1').setText('Range');
    sheet.getRangeByName('B1').setText('Force');

//Create an instances of chart collection.
    final ChartCollection charts = ChartCollection(sheet);

// Add the chart.
    final Chart chart1 = charts.add();

    var rangeArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["range_array"];
    var forceArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["force_array"];

    for (var i = 0; i < rangeArray.length; i++) {
      sheet.getRangeByName('A${i + 2}').setText(rangeArray[i].toString());
      sheet.getRangeByName('B${i + 2}').setText(forceArray[i].toString());
    }

    // Set Chart Type.
    chart1.chartType = ExcelChartType.line;

    chart1.dataRange = sheet.getRangeByName('A1:B1');

// set charts to worksheet.
    sheet.charts = charts;

    // Save the workbook to a file
    final List<int> bytes = workbook.saveAsStream();
    workbook.dispose();
    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);
}


```


The Json looks like this:

```

{
    "measure": [
        {
            "count": 1,
            "range_array": [
                12,
                13,
                14,
                15
            ],
            "force_array": [
                23,
                21,
                45,
                34
            ]
        },
        {
            "count": 2,
            "range_array": [
                16,
                23,
                36,
                49
            ],
            "force_array": [
                21,
                36,
                44,
                71
            ]
        }
    ]
}

```


Thanks in advance for some help


10 Replies 1 reply marked as answer

CC Carlos Costa February 6, 2023 06:53 AM UTC

I changed my code to this:

To get a XY Scatter Chart.

```

void generateExcellOfficeChart(String fileName) async {
// Create a new Workbook
final Workbook workbook = Workbook();
final Worksheet sheet = workbook.worksheets[0];
sheet.showGridlines = true; // Enable-Disable Gridlines

// Setting value in the cell.
sheet.getRangeByName('A1').setText('Range');
sheet.getRangeByName('B1').setText('Force');

var rangeArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["range_array"];
var forceArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["force_array"];

for (var i = 0; i < rangeArray.length; i++) {
sheet.getRangeByName('A${i + 2}').setText(rangeArray[i].toString());
sheet.getRangeByName('B${i + 2}').setValue(forceArray[i]);
}

//Create an instances of chart collection.
final ChartCollection charts = ChartCollection(sheet);

// Add the chart.
final Chart chart = charts.add();

chart.chartType = ExcelChartType.line;
//Set data range in the worksheet.

chart.dataRange = sheet.getRangeByName('A1:B5');
chart.isSeriesInRows = false;
// set charts to worksheet.
sheet.charts = charts;

// Set Chart Type.

// Save the workbook to a file
final List<int> bytes = workbook.saveAsStream();
workbook.dispose();
await saveAndLaunchFile(bytes, '$fileName.xlsx');
}

```

I am getting the Excel like this:

Untitled.png


Now i have two situation that i do not understand.

To get the Excel result shown in the picture, on this line.

1:

```

sheet.getRangeByName('A${i + 2}').setText(rangeArray[i].toString());

```

I am declaring it to setText (String). If i declare it to setValue i do not get the result shown in the picture.


2:

```

chart.dataRange = sheet.getRangeByName('A1:B5');

```

I am declaring sheet.getRangeByName('A1:B5') as hardcoded. At the Json file exampel that i gave it is ok like this. In the example there are 4 values. But it can be 4 or more or less values. I need a way to get all values depending on the quantity



RB Rahul Balasundaram Syncfusion Team February 6, 2023 01:28 PM UTC

Hi Carlos,

To get the cell values as per the Excel image from your initial update, we suggest using SetValue method to set cell values. Now, you have used SetValue method for force array values whereas SetText method is for range array values. So, the range array values are treated as text instead of numbers. This is the expected behavior of Excel.


To set both values as numbers and find the dynamic array length, please refer to the code snippet below.


void generateExcellOfficeChart(String fileName) async {

    // Create a new Workbook

    final Workbook workbook = Workbook();

    final Worksheet sheet = workbook.worksheets[0];

 

    sheet.showGridlines = true; // Enable-Disable Gridlines

 

    // Setting value in the cell.

    sheet.getRangeByName('A1').setText('Range');

    sheet.getRangeByName('B1').setText('Force');

 

    //Create an instances of chart collection.

    final ChartCollection charts = ChartCollection(sheet);

 

    // Add the chart.

    final Chart chart1 = charts.add();

    var rangeArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["range_array"];

    var forceArray = data["measure"].firstWhere((item) => item["count"] == selectedCount)["force_array"];

 

    for (var i = 0; i < rangeArray.length; i++) {

      sheet.getRangeByName('A${i + 2}').setValue(rangeArray[i]);

      sheet.getRangeByName('B${i + 2}').setValue(forceArray[i]);

      }

 

    // Set Chart Type.

    chart1.chartType = ExcelChartType.line;

    chart1.dataRange = sheet.getRangeByName('A1:B${forceArray.Length}');

 

    // set charts to worksheet.

    sheet.charts = charts;

 

    // Save the workbook to a file

    final List<int> bytes = workbook.saveAsStream();

    workbook.dispose();

    await saveAndLaunchFile(bytes, '$fileName.xlsx');

  }


Please share the required sample Excel document with us. It will help us to create a sample from our end to achieve your requirement.


Thanks,
Rahul.



CC Carlos Costa February 6, 2023 04:09 PM UTC

Hi Rahul,


Thanks a lot for your reply. 


I follow your suggestions.

This:

chart1.dataRange = sheet.getRangeByName('A1:B${forceArray.Length}');

I changed to this:

chart1.dataRange = sheet.getRangeByName('A1:B${forceArray.length}');

To make it work.


Follows the Excel and Json file.


Attachment: Measure_98c41add.zip


RB Rahul Balasundaram Syncfusion Team February 7, 2023 12:31 PM UTC

Hi Carlos,

We are glad that the provided suggestion helped you. Please get back to us if you need any further assistance.

Note: If that post is helpful, please consider Accepting it as the solution so that other members can locate it more quickly.

 





CC Carlos Costa replied to Rahul Balasundaram February 7, 2023 02:17 PM UTC

Hi Rahul,

Thanks for the help.

It was my fault.

To set both values as numbers and find the dynamic array length, please refer to the code snippet below.

chart1.dataRange = sheet.getRangeByName('A1:B${forceArray.length}');

This is working.


But i continuo not be able to create a Excel like i provided, if i use this:

sheet.getRangeByName('A${i + 2}').setValue(rangeArray[i]);

sheet.getRangeByName('B${i + 2}').setValue(forceArray[i]);


Only if i define like this: But setText is not what i want.

sheet.getRangeByName('A${i + 2}').setValue(rangeArray[i]);

sheet.getRangeByName('B${i + 2}').setText(forceArray[i]);








RB Rahul Balasundaram Syncfusion Team February 8, 2023 01:55 PM UTC

Hi Carlos,


Please share the current Excel document in which the problem is occurring, as well as the expected output Excel file, so that we can analyze the issue and provide a solution as soon as possible.



CC Carlos Costa February 8, 2023 04:39 PM UTC

Hi Rahul,

Thanks for the reply.


I am sharing the Measure Zip File.

There are 4 files.

  1. The Flutter Code File
  2. The Json Data File
  3. The Current Excel File that are generated with the Flutter Code
  4. The Expected Excel


Attachment: Measure_84f390c4.zip


RB Rahul Balasundaram Syncfusion Team February 10, 2023 12:15 PM UTC

Hi Carlos,


The expected Excel document you shared contains a scatter with a line and marker chart created for the data provided, but you used Flutter XlsIO to create a line chart, which is the expected output according to Microsoft Excel behavior. 


Currently, we do not have support for creating scatter charts in Flutter XlsIO, and we have logged a feature request for this support. However, we do not have any immediate plans to implement this feature in the near future.


You can track the status of this feature report through the below feedback link.
https://www.syncfusion.com/feedback/41039/support-for-creating-scatter-charts-in-flutter-xlsio


Note: To get the expected result, Should set "Range" column values to text using the setText method.


Regards,
Rahul


Marked as answer

CC Carlos Costa February 10, 2023 01:12 PM UTC

Hi Rahul, 


Thanks a lot for the reply. 

To get the expected result, i set "Range" column values to text using the setText method.





RB Rahul Balasundaram Syncfusion Team February 13, 2023 02:19 PM UTC

Hi Carlos,

You’re welcome. Kindly let us know if you need any further assistance.


Loader.
Live Chat Icon For mobile
Up arrow icon