将JSON数据转换成Excel文件在Javascript中下载

h43kikqp  于 2023-02-28  发布在  Java
关注(0)|答案(2)|浏览(164)

我有一个 AJAX POST请求取回JSON数组形式的数据。我想把这个接收到的JSON数据转换成Excel文件(不是CSV)下载(点击按钮),请帮助。JSON数据可能有空白值和每个JSON行丢失的字段。
我尝试在客户端使用Javascript,但不是在Java服务器端,在这种情况下,我将不得不使用@生产(媒体类型。MULTIPART_FORM_DATA)在 AJAX 端点方法,这是我可以尝试,但认为它的复杂。
a) AJAX 请求代码:

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)从 AJAX POST请求接收的JSON样本数据:

[
    {
    "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"
    }

]
z5btuh9x

z5btuh9x1#

这是一个从WebService(Resteasy实现)生成Excel的方法,经过测试并完全有效:

@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();

    }

这是管理Excel文件的maven依赖项:

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

您可以轻松地添加适当的注解和webparameter来管理服务启动后的文件输入:

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

如果有帮助就告诉我...

nnvyjq4y

nnvyjq4y2#

@21点,下面是代码......
1)单击按钮时调用的函数。此函数具有将Excel文件传递给REST端点函数 AJAX 调用:

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);
            }

        });//ajax ends

    }//if ends

}//Function ends

2)REST端点函数:

@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();

    //System.out.println("File name is: " + fileDetail.getFileName());
    //System.out.println("File size is : " + fileDetail.getSize());
    //System.out.println("File size is : " + fileDetail.getType());

    // 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(); //-----> no reference to excel cells here
        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();


}

相关问题