JavaScript Function to Convert JSON Data into an Excel File Download

I am looking for help with converting JSON data received from an AJAX POST Request into an Excel file (not CSV) for download on a button click. The JSON Data may contain blank values and missing fields for each JSON row.

I have attempted to achieve this on the client side using Javascript, but I am now considering exploring server-side Java implementation where I would need to use @Produces(MediaType.MULTIPART_FORM_DATA) in the AJAX Endpoint method. This approach seems complex, but it's something worth trying.

a) Here is the code snippet for the AJAX request:

function fileUploadFunction() {

    var file = $('input[name="file"]').get(0).files[0];
    var formData = new FormData();

    if(file.name != null) {
        document.getElementById("btnUpload").disabled = false;

        formData.append('file', file);
        $.ajax({
            url : "<%=request.getContextPath()%>/rest/upload/upload",
            type : "POST",
            data : formData,
            cache : false,
            contentType : false,
            processData : false,
            success : function(response) {
                //Store result in Session and Enable Download button
                var cacheString = JSON.stringify(response, null, 2);
                console.log("-----------------> cacheString is: " + cacheString);
                if(cacheString != null && cacheString != "[]") {
                    document.getElementById("download").disabled = false;
                }
                var sessionresponse = sessionStorage.setItem("i98779", cacheString); 

                console.log("response is: " + response);
                console.log("cacheString is: " + cacheString);
                excelDownload(cacheString);
                //createTable(response);
                //https://stackoverflow.com/questions/47330520/how-to-export-json-object-into-excel-using-javascript-or-jquery

            },

            error : function(jqXHR, textStatus, errorThrown) {
                console.log(errorThrown);
                alert("Error: " + errorThrown);
            }

        });//ajax ends

    }//if ends

}//Function ends

b) Below is a sample of JSON data received from the AJAX POST Request:

[
    {
    "entityid":2,
    "firstname":"George",
    "lastname":"Bush",
    "ssn":"",
    "city":"Houston",
    "state":"TX",
    "country":"USA",
    "zipcode":""
    },
    {
    "entityid": 8,
    "firstname": "Jim",
    "lastname": "Macron",
    "ssn": "888-88-8888",
    "city": "Paris",
    "state": "NY",
    "country": "France",
    "zipcode": "T789J"
    },
    {
    "entityid": 11,
    "firstname": "Angela",
    "lastname": "Merkel",
    "city": "Saxony",
    "zipcode": ""
    },
    {
    "entityid": 7,
    "firstname": "Donald",
    "lastname": "Trump",
    "ssn": "777-77-7777",
    "city": "Washington D.C.",
    "state": "DC",
    "country": "USA",
    "zipcode": "70000"
    }

]

Answer №1

Here is a reliable method for creating an Excel spreadsheet using the WebService (Resteasy Implementation) that has been thoroughly tested and proven to work:

@POST
    @Path("/excelpost")
    @Produces(MediaType.APPLICATION_OCTET_STREAM)
    public Response downloadFilePost( ) throws IOException {

         XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
            Object[][] datatypes = {
                    {"Datatype", "Type", "Size(in bytes)"},
                    {"int", "Primitive", 2},
                    {"float", "Primitive", 4},
                    {"double", "Primitive", 8},
                    {"char", "Primitive", 1},
                    {"String", "Non-Primitive", "No fixed size"}
            };

            int rowNum = 0;
            System.out.println("Creating excel");

            for (Object[] datatype : datatypes) {
                Row row = sheet.createRow(rowNum++);
                int colNum = 0;
                for (Object field : datatype) {
                    Cell cell = row.createCell(colNum++);
                    if (field instanceof String) {
                        cell.setCellValue((String) field);
                    } else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    }
                }
            }

                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                workbook.write(outputStream);
                workbook.close();


            return Response.status(200).header("Access-Control-Allow-Origin", "*")
                    .header("Content-Disposition", "attachment;filename='fileName.xlsx'")
                    .header("Access-Control-Allow-Headers", "origin, content-type, accept, authorization")
                    .header("Access-Control-Allow-Credentials", "true")
                    .header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS, HEAD")
                    .header("Access-Control-Max-Age", "1209600").entity( new ByteArrayInputStream(outputStream.toByteArray() )).build();


    }

Here is the maven dependency needed to manage Excel files:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

If you want to handle file input from the initial post of your service, you can easily add annotations and webparameters like this:

@POST
@Path("/excelpost")
@Consumes(MediaType.MULTIPART_FORM_DATA)
@Produces(MediaType.APPLICATION_OCTET_STREAM)
public Response downloadFilePost(MultipartFormDataInput input)
   .......

Hope this information proves helpful to you...

Answer №2

@Blackjack, here is the script for handling file upload and calling a REST Endpoint function:

1) The following function is triggered on click of a button. It involves an AJAX call to send an Excel file to a REST Endpoint:

function fileUploadFunction() {

    var file = $('input[name="file"').get(0).files[0];
    var formData = new FormData();

    if(file.name != null) {
        document.getElementById("btnUpload").disabled = false;

        formData.append('file', file);
        $.ajax({
            url : "<%=request.getContextPath()%>/rest/upload/bulkSearch",
            type : "POST",
            data : formData,
            cache : false,
            contentType : false,
            processData : false,
            success : function(response) {
                //Store result in Session and Enable Download button
                var cacheString = JSON.stringify(response, null, 2);
                console.log("-----------------> cacheString is: " + cacheString);
                if(cacheString != null && cacheString != "[]") {
                    document.getElementById("download").disabled = false;
                }
                var sessionresponse = sessionStorage.setItem("i98779", cacheString); 

                console.log("response is: " + response);
                console.log("cacheString is: " + cacheString);
                //excelDownload(cacheString);
                //createTable(response);
                //https://stackoverflow.com/questions/47330520/how-to-export-json-object-into-excel-using-javascript-or-jquery

            },

            error : function(jqXHR, textStatus, errorThrown) {
                console.log(errorThrown);
                alert("Error: " + errorThrown);
            }

        });
    }

}

2) Here's the code for the REST End-Point function:

@POST 
@Consumes(MediaType.MULTIPART_FORM_DATA) 
@Produces(MediaType.APPLICATION_OCTET_STREAM)
@Path("/bulkSearch") 
public Response bulkSearch( 
    @FormDataParam("file") InputStream uploadedInputStream, 
    @FormDataParam("file") FormDataContentDisposition fileDetail) throws IOException { 

    System.out.println("Entered uploadFile method");
    System.out.println("uploadedInputStream is: " + uploadedInputStream);
    System.out.println("fileDetail is: " + fileDetail.toString());

    String returnJSON = null;
    List<User> usersList = null;
    ObjectMapper uploadMapper = new ObjectMapper();

    // Check if all form parameters are provided 
    if (uploadedInputStream == null || fileDetail == null) 
        return Response.status(400).entity("Invalid form data").build();

    System.out.println("Checked Input file is ok");

    System.out.println("----------------------------------------------------------------");
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
    Object[][] datatypes = {
            {"Datatype", "Type", "Size(in bytes)"},
            {"int", "Primitive", 2},
            {"float", "Primitive", 4},
            {"double", "Primitive", 8},
            {"char", "Primitive", 1},
            {"String", "Non-Primitive", "No fixed size"}
    };

    int rowNum = 0;
    System.out.println("Creating excel");

    for (Object[] datatype : datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field : datatype) {
            org.apache.poi.ss.usermodel.Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    System.out.println("For loop done");

    ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); 
    System.out.println("outputstream done");
    workbook.write(outputStream);
    System.out.println("workbook.write done");
    workbook.close();
    System.out.println("workbook closed");


    return Response.status(200).header("Access-Control-Allow-Origin", "*")
            .header("Content-Disposition", "attachment;filename='fileName.xlsx'")
            .header("Access-Control-Allow-Headers", "origin, content-type, accept, authorization")
            .header("Access-Control-Allow-Credentials", "true")
            .header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS, HEAD")
            .header("Access-Control-Max-Age", "1209600").entity(new ByteArrayInputStream(outputStream.toByteArray())).build();



} 

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Despite initializing session_start(), the session variable remains unchanged and fails to update

I have a page called train_detail.php. The table on this page was created using SQL and includes the column attribute train_id. Each row in the table has a button named check_availability. When this button is clicked, it opens a new modal that passes the v ...

Display all dependents associated with a dependency in the lock file

Is there a way to identify all dependents of a specific dependency from a lock file (npm/pnpm/yarn)? This is the opposite of using yarn ls I would rather not parse the lock file if there is an existing command in npm/pnpm/yarn for this I am aware that I ...

Refresh dynamic JSON data for an interactive force-directed graph with updated links

As a newcomer to D3, I am currently working on a force directed graph that utilizes dynamic JSON data. My challenge lies in updating the force graph with new data without causing a jarring springing effect. Below is the code snippet responsible for creatin ...

The elegance of a JSON datetime in the world of ballerinas

My task involves indexing documents to Elasticsearch on an index with a date field mapping. I've been attempting to construct a JSON object with the date value, but Ballerina seems to indicate that it's not possible. I considered storing the ...

Implement dropdown menus for category selection in Magento CE version 1.8.1.0

A previous option causing confusion has been removed * UPDATE * I am trying to set up a dropdown list for easy navigation through a specific category hierarchy. If you need a visual example, check out this image: * UPDATE * I'm still on the hunt ...

Developing an Android application that utilizes PHP to return JSON encoded data

I have a situation where I am outputting JSON encoded data at the end of a PHP file on my server using the code "echo json_encode($response);" When this PHP file is accessed directly from a browser, the encoded data appears in the browser instead of being ...

Response from successful AJAX call to retrieve data

Here's the code snippet I'm currently working with: var array = []; $('#next1').on('click', function(){ var dataString='company= '+$(this).closest('.inside').find('select').val(); $.ajax( ...

Is there a possibility of encountering an endless update loop within a component's render function?

Hello! I am a beginner in Vue.js and I have encountered an issue with my function. Instead of increasing my variable to 1 as expected, it is increasing it to a random number every time. The console is displaying the following error message: "You may hav ...

What method can I use to modify the object's keys based on certain conditions in ES6/React?

How can I dynamically change the value of an object's keys based on specific conditions? What is the most effective way to structure and implement this logic? Data const newData = { id: 111, name: "ewfwef", description: "Hello&qu ...

The Vue DevTools are functioning as expected, but there seems to be an issue

Encountering a peculiar issue where the value displayed in Vue DevTools is accurate, matching what is expected in my data. When I first click on the "Edit" button for an item, the correct value appears in the browser window as intended. However, upon clic ...

Manipulate the presence of THREE.Points in a three.r84 scene by adding or removing them

I recently upgraded from three.js version 71 to version 84 and encountered a problem with updating points in the scene. Previously, with THREE.PointCloud, it was simple to add and remove points as needed like so: function updatePoints(newData) { geom ...

Creating dynamic charts using Angular 7 with Chartjs and mapping JSON data

Struggling to map JSON Data for a Bar-Chart display. The desired final Array should be: [883, 5925, 17119, 27114, 2758]. Seems like the Array I'm trying to use for barChartData (dringlichkeitenValues[]) is empty. Apologies for my coding skills. Can so ...

Encountering a problem when trying to reference socket.io

I've been working on building an express app with chat functionality, but I've run into an issue with socket.io. It's not working properly and is throwing an "uncaught reference" error when I try to run the server. You can see a screenshot o ...

The problem of double encoding in jQuery Ajax issues

Currently, I am attempting to make an ajax call to the server in order to read user input text (which is in Hebrew and utf-8 charset). This is how I have been trying to achieve it: my_url = some_url + textinput my_url = encodeURI(my_url) The issue I am e ...

The app.get() method in Node JS and Express requires three parameters, and I am seeking clarification on how these parameters work

Hey there, I'm new to this and have a question regarding my code using passport-google-oauth20. app.get('/auth/google/secrets', passport.authenticate('google',{failureRedirect: '/login'}), function(req,res){ res.redirec ...

How can I filter an array to retain only the initial 5 characters of every element?

I need help using the .map function to transform this array so that only the first 5 characters are displayed, like "01:00", "02:00"? This is the array: ["01:00:00 AM", "02:00:00 AM", "03:00:00 AM", "04:00:00 AM", "05:00:00 AM", "06:00:00 AM", "07:00:00 ...

Transition within Vuejs moves forwards and backwards, with a unique feature that allows it to skip directly to

I am in the process of developing a slider element that consists of only 2 items. My goal is to ensure that these items smoothly slide back and forth to the left and right when I click on the back or next button. While everything functions correctly when I ...

What is the best way to resize a PDF to match the width and height of a canvas using

I need help with a project involving rendering previews of PDF documents in a UI similar to Google Docs. {documents.map((doc) => { return ( <div key={doc.id} className=" ...

Programmatically adding route resolve in Angular using $routeProvider

Is it possible to dynamically add Angular's resolve after it has been initially defined in the app? Let's say we have a setup with routes like this: app.config(['$routeProvider', function ($routeProvider) { $routeProvider ...

'AngularJS' filtering feature

I am dealing with an array of objects and I need to extract a specific value when a key is passed in the 'filter' function. Despite my best efforts, the controller code snippet provided below returns an undefined response. Can someone please assi ...