NdJson to Excel in reactive progreamming

Aspose.Cells Product Family
1

hi Team, we have a use case where we have NdJson stream coming over http and want to convert the json data into excel file . The json object will vary for different request. Is aspose going to use memory operation to parse and create the file ? how can we make it process efficent ?

2

@sr2989

To convert an NdJson stream to an Excel file in a reactive programming context, you can utilize Aspose.Cells, which efficiently handles memory operations for parsing and creating Excel files. Here’s a step-by-step approach to achieve this:

  1. Cause: You need to convert NdJson data into an Excel format for better data manipulation and analysis.

  2. Stream the NdJson Data: Use a reactive stream to read the NdJson data from the HTTP source. Libraries like Reactor or RxJava can facilitate this.

  3. Parse the NdJson: As you read each line of the NdJson, parse the JSON objects using a library like Jackson or Gson to convert each line into a Java object.

  4. Create an Excel Workbook: Initialize an Aspose.Cells.Workbook object to create a new Excel file.

  5. Add Data to the Workbook: For each parsed JSON object, add the data to the workbook. Access the cells of the worksheet and use the PutValue method to insert data.

  6. Save the Workbook: Once all data is added, save the workbook to a file.

Here’s a minimal code example to illustrate this:

import com.aspose.cells.*;
import com.fasterxml.jackson.databind.ObjectMapper;
import reactor.core.publisher.Flux;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;

public class NdJsonToExcel {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        ObjectMapper objectMapper = new ObjectMapper();

        // Simulating a reactive stream of NdJson data
        Flux<String> ndJsonStream = Flux.just(
            "{\"name\":\"John\", \"age\":30}",
            "{\"name\":\"Jane\", \"age\":25}"
        );

        ndJsonStream.subscribe(jsonLine -> {
            try {
                // Parse JSON line to a Map or a custom object
                Map<String, Object> data = objectMapper.readValue(jsonLine, Map.class);
                int row = worksheet.getCells().getMaxDataRow() + 1; // Get next empty row

                // Add data to the worksheet
                worksheet.getCells().get(row, 0).putValue(data.get("name"));
                worksheet.getCells().get(row, 1).putValue(data.get("age"));
            } catch (IOException e) {
                e.printStackTrace();
            }
        }, 
        error -> System.err.println("Error: " + error),
        () -> {
            try (FileOutputStream fileOutputStream = new FileOutputStream("output.xlsx")) {
                workbook.save(fileOutputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        });
    }
}

Next Steps:

  • Batch Processing: Instead of processing each JSON object individually, consider batching them to reduce the number of write operations to the Excel file.
  • Optimize Memory Usage: Use streaming APIs to handle large datasets without loading everything into memory at once.

This approach will efficiently convert NdJson to Excel while being mindful of memory and energy usage. If you have any further questions or need additional assistance, feel free to ask!

3

@sr2989,

Aspose.Cells for Java does use in-memory operations by default when working with data transformations such as JSON to Excel. Aspose.Cells provides support for reading and loading files from streams, as well as writing and saving files to streams. Aspose.Cells may not natively support streaming NDJSON directly, it expects a full JSON string or file to be parsed or imported. It can import the entire JSON input into memory via Workbook object or using JsonUtility.importData() or similar methods. See the document with example code to demonstrate the use of the JsonUtility and JsonLayoutOptions classes to import JSON data.

Hope, this helps a bit.