0

When I try to save excel data in JSON file under resource folder. I am getting Null pointer exception when I hit send in postman. Not sure if the code is okay or anyone can refer any code for saving excel file in json format.

import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.simple.JSONObject;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.google.gson.Gson;

@RestController
public class ExcelController {

    @PostMapping("excel")
    public String getExcel(@RequestParam("file") MultipartFile file) {
        excel2Json(file);
        return "Success";
    }

    public void excel2Json(MultipartFile file) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(file.getInputStream());
            XSSFSheet workSheet = workBook.getSheetAt(0);
            List<JSONObject> dataList = new ArrayList<>();
            XSSFRow header = workSheet.getRow(0);
            for(int i=1;i<workSheet.getPhysicalNumberOfRows();i++) {
                XSSFRow row = workSheet.getRow(i);
                JSONObject rowJsonObject = new JSONObject();
                for(int j=0; j<row.getPhysicalNumberOfCells();j++) {
                    String columnName = header.getCell(j).toString();
                    String columnValue = row.getCell(j).toString();
                    rowJsonObject.put(columnName, columnValue);
                }
                dataList.add(rowJsonObject);
            }
            System.out.println(dataList);
            writeData2JsonFile(dataList);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void writeData2JsonFile(List<JSONObject> dataList) {
        Gson gson = new Gson();
        try {
            FileWriter file = new FileWriter("D:\\work\\blog-api\\src\\main\\resources\\data.json");
            file.write(gson.toJson(dataList));
            file.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

Please let me know why I am getting null pointer exception when I hit the excel file in postman.

  • what's coming back as null? file name? JSONObject? – Dut A. Jul 25 '22 at 07:19
  • In postman I am getting this error - "timestamp": "2022-07-25T06:41:29.856+00:00", "status": 500, "error": "Internal Server Error", "trace": "java.lang.NullPointerException\r\n\tat com.blog.api.controllers.ExcelController.excel2Json(ExcelController.java:36)\r\n\tat com.blog.api.controllers.ExcelController.getExcel(ExcelController.java:23)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect. – Apratim Dutta Choudhury Jul 25 '22 at 09:20
  • Does this answer your question? [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) – vanje Jul 25 '22 at 12:21
  • This is hard to replicate as no one has exact replica of your local setup of that project. It's clearly this line not finding data: `String columnValue = row.getCell(j).toString();`. It will help finding out more how Apache POI handles data in a spreadsheet... Something is wrong with the loop counters. – Dut A. Jul 25 '22 at 15:08

0 Answers0