Convert Excel To JSON In Java Example

33 Comments / Java Tutorial / Excel

A reader of the article Read / Write Excel Data Using Apache POI asks me a question about how to read excel sheet data and write those data to a JSON file. This question lead to this example. So after reading this article you can know how to convert excel sheet data to a JSON file, and how to create a text file that contains the excel sheet data.

1. Convert Excel To JSON Example Required Library.

This example requires the below jar files, you can go to the maven repository to search and download them if you add the jars by hand. You can read the article How To Download Jars From Maven Repository to learn more.

  1. commons-beanutils-1.8.3.jar
  2. ezmorph-1.0.6.jar
  3. commons-collections-3.2.1.jar
  4. commons-lang-2.6.jar
  5. json-lib-2.4.jar
  6. apache-poi-maven-lib
  7. poi-bin-3.16-20170419.zip

Because this example uses apache poi to parse excel files, so all the poi-related jar files are included in the poi-bin zip file. Just download it and unzip it to get those jars.

2. Convert Excel To JSON Example Source Code.

  1. Below is the example source code that can convert an excel file to a JSON file. 
    package com.dev2qa.java.basic.excel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.json.Json;

import com.google.gson.Gson;
import com.google.gson.stream.JsonReader;

import net.sf.json.JSONObject;

public class ReadExcelDataWithDynamicColumn {

    public static void main(String[] args)
    {
        // You can specify your excel file path.
        String excelFilePath = "/Users/zhaosong/Documents/WorkSpace/EmployeeInfo.xls";

        // This method will read each sheet of data from the above excel file and create a JSON and a text file to save the sheet data.
        creteJSONAndTextFileFromExcel(excelFilePath);
    }


    /* Read data from an excel file and output each sheet of data to a JSON file and a text file. 
     * filePath:  The excel file store path.
     * */
    private static void creteJSONAndTextFileFromExcel(String filePath)
    {
        try{
         /* First need to open the file. */
            FileInputStream fInputStream = new FileInputStream(filePath.trim());
   
         /* Create the workbook object to access excel file. */
            //Workbook excelWookBook = new XSSFWorkbook(fInputStream)
         /* Because this example use .xls excel file format, so it should use HSSFWorkbook class. For .xlsx format excel file use XSSFWorkbook class.*/;
            Workbook excelWorkBook = new HSSFWorkbook(fInputStream);

            // Get all excel sheet count.
            int totalSheetNumber = excelWorkBook.getNumberOfSheets();

            // Loop in all excel sheet.
            for(int i=0;i<totalSheetNumber;i++)
            {
                // Get current sheet.
                Sheet sheet = excelWorkBook.getSheetAt(i);

                // Get sheet name.
                String sheetName = sheet.getSheetName();

                if(sheetName != null && sheetName.length() > 0)
                {
                    // Get current sheet data in a list table.
                    List<List<String>> sheetDataTable = getSheetDataList(sheet);

                    // Generate JSON format of above sheet data and write to a JSON file.
                    String jsonString = getJSONStringFromList(sheetDataTable);
                    String jsonFileName = sheet.getSheetName() + ".json";
                    writeStringToFile(jsonString, jsonFileName);

                    // Generate text table format of above sheet data and write to a text file.
                    String textTableString = getTextTableStringFromList(sheetDataTable);
                    String textTableFileName = sheet.getSheetName() + ".txt";
                    writeStringToFile(textTableString, textTableFileName);

                }
            }
            // Close excel work book object. 
            excelWorkBook.close();
        }catch(Exception ex){
            System.err.println(ex.getMessage());
        }
    }


    /* Return sheet data in a two-dimensional list. 
     * Each element in the outer list is represent a row, 
     * each element in the inner list represent a column.
     * The first row is the column name row.*/
    private static List<List<String>> getSheetDataList(Sheet sheet)
    {
        List<List<String>> ret = new ArrayList<List<String>>();

        // Get the first and last sheet row number.
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();

        if(lastRowNum > 0)
        {
            // Loop in sheet rows.
            for(int i=firstRowNum; i<lastRowNum + 1; i++)
            {
                // Get current row object.
                Row row = sheet.getRow(i);

                // Get first and last cell number.
                int firstCellNum = row.getFirstCellNum();
                int lastCellNum = row.getLastCellNum();

                // Create a String list to save column data in a row.
                List<String> rowDataList = new ArrayList<String>();

                // Loop in the row cells.
                for(int j = firstCellNum; j < lastCellNum; j++)
                {
                    // Get current cell.
                    Cell cell = row.getCell(j);

                    // Get cell type.
                    int cellType = cell.getCellType();

                    if(cellType == CellType.NUMERIC.getCode())
                    {
                        double numberValue = cell.getNumericCellValue();

                        // BigDecimal is used to avoid double values is counted using the Scientific counting method.
                        // For example the original double variable value is 12345678, but JDK translated the value to 1.2345678E7.
                        String stringCellValue = BigDecimal.valueOf(numberValue).toPlainString();

                        rowDataList.add(stringCellValue);

                    }else if(cellType == CellType.STRING.getCode())
                    {
                        String cellValue = cell.getStringCellValue();
                        rowDataList.add(cellValue);
                    }else if(cellType == CellType.BOOLEAN.getCode())
                    {
                        boolean numberValue = cell.getBooleanCellValue();

                        String stringCellValue = String.valueOf(numberValue);

                        rowDataList.add(stringCellValue);

                    }else if(cellType == CellType.BLANK.getCode())
                    {
                        rowDataList.add("");
                    }
                }

                // Add current row data list in the return list.
                ret.add(rowDataList);
            }
        }
        return ret;
    }

    /* Return a JSON string from the string list. */
    private static String getJSONStringFromList(List<List<String>> dataTable)
    {
        String ret = "";

        if(dataTable != null)
        {
            int rowCount = dataTable.size();

            if(rowCount > 1)
            {
                // Create a JSONObject to store table data.
                JSONObject tableJsonObject = new JSONObject();

                // The first row is the header row, store each column name.
                List<String> headerRow = dataTable.get(0);

                int columnCount = headerRow.size();

                // Loop in the row data list.
                for(int i=1; i<rowCount; i++)
                {
                    // Get current row data.
                    List<String> dataRow = dataTable.get(i);

                    // Create a JSONObject object to store row data.
                    JSONObject rowJsonObject = new JSONObject();

                    for(int j=0;j<columnCount;j++)
                    {
                        String columnName = headerRow.get(j);
                        String columnValue = dataRow.get(j);

                        rowJsonObject.put(columnName, columnValue);
                    }

                    tableJsonObject.put("Row " + i, rowJsonObject);
                }

                // Return string format data of JSONObject object.
                ret = tableJsonObject.toString();

            }
        }
        return ret;
    }


    /* Return a text table string from the string list. */
    private static String getTextTableStringFromList(List<List<String>> dataTable)
    {
        StringBuffer strBuf = new StringBuffer();

        if(dataTable != null)
        {
            // Get all row count.
            int rowCount = dataTable.size();

            // Loop in the all rows.
            for(int i=0;i<rowCount;i++)
            {
                // Get each row.
                List<String> row = dataTable.get(i);

                // Get one row column count.
                int columnCount = row.size();

                // Loop in the row columns.
                for(int j=0;j<columnCount;j++)
                {
                    // Get column value.
                    String column = row.get(j);

                    // Append column value and a white space to separate value.
                    strBuf.append(column);
                    strBuf.append("    ");
                }

                // Add a return character at the end of the row. 
                strBuf.append("\r\n");
            }

        }
        return strBuf.toString();
    }

    /* Write string data to a file.*/
    private static void writeStringToFile(String data, String fileName)
    {
        try
        {
            // Get the currently executing class working directory.
            String currentWorkingFolder = System.getProperty("user.dir");

            // Get file path separator.
            String filePathSeperator = System.getProperty("file.separator");

            // Get the output file absolute path.
            String filePath = currentWorkingFolder + filePathSeperator + fileName;

            // Create File, FileWriter, and BufferedWriter object.
            File file = new File(filePath);

            FileWriter fw = new FileWriter(file);

            BufferedWriter buffWriter = new BufferedWriter(fw);

            // Write string data to the output file, flush and close the buffered writer object.
            buffWriter.write(data);

            buffWriter.flush();

            buffWriter.close();

            System.out.println(filePath + " has been created.");

        }catch(IOException ex)
        {
            System.err.println(ex.getMessage());
        }
    }
}

33 thoughts on “Convert Excel To JSON In Java Example”

    1. Jerry Zhao

      The convert excel to JSON example required jat library download link has been updated, it can work now.

      Reply
  5. Kannan S

    Hi Thank you Very much for this info.

    Its really helping me lot to solve my problem.

    But still i’m worrying to differentiate the data from excel file. I do have json object and Json Array also some time array inside array. Could some one please help me to handle this problem.

    I need a json request as like below.

    {
    “USerName”: {
    “names”: [
    {
    “firstName”: “Kannan”,
    “midName”: “M”,
    “lastName”: “Subramaiam”,
    “EmailAddress”: [
    “EmailAddress1” : “address@Ce.com”,
    “EmailAddresse” : “address22@Ce.com”,
    ],
    “title”: “MR”
    }
    ]
    }

    It really help full if some one look into this and solve this problem.

    Please share me sample xls file to handle the above problem.

    Thanks in Advance.

    Regards,
    KAnnan.S

    Reply
  6. Kannan S

    Hi ,

    Can any one please share the sample xls file template . also i’m not sure how this code will differentiate/identify normal json object and json array/ array of array.

    Suppose, I need a json object as below.

    {
    “UserName”: {
    “names”: [
    {
    “firstName”: “Kannan”,
    “midName”: “S”,
    “lastName”: “Subramaniam”,
    “suffixes”: [
    “M”
    ],
    “title”: “Mr”
    }
    ]
    }

    Can any one help me to specify the columns in the Excel to generate json request as like above ?

    Thanks in Advance..

    Reply
  7. Swetabh Mukherjee

    Hey there, this code worked perfectly for me. The only problem is that I want the JSON to be generated in the same order as it is present in the excel file. The output JSON gives a randomly generated file(though the row numbers are correct!). Is there any way to resolve this? Thanks.

    Reply
  8. DarkSlayer

    Hi,
    I followed your all steps but getting this exceptions. Can you help me to resolve this, please. Thank you very much.

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
    at exceltojson.ReadExcelDataWithDynamicColumn.creteJSONAndTextFileFromExcel(ReadExcelDataWithDynamicColumn.java:54)
    at exceltojson.ReadExcelDataWithDynamicColumn.main(ReadExcelDataWithDynamicColumn.java:43)
    Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    … 2 more

    Reply
  10. MSK

    Hi,

    Can you please give a sample code to convert Json data to Excel?
    I get my Json data from the client side onto server and need code in Java to insert the Json(dynamic data) to Excel.

    Reply
  11. Vasu

    hi, Do you have any example to convert json to excel , and then use the same excel to json…. I am planning to use your above solution from excel to json for second part. The reason I need to convert json to excel is
    (1) Raw josn is big and needs to be spit to smaller Jsons
    (2) Needs to update smaller json with few fields
    (3) send these smaller json for my validation

    Reply
  13. Gaurav

    List<List> sheetDataTable = getSheetDataList(sheet);

    I put breakpoint and this line threw an exception, showing output as null in console window.

    My excel file is test.xlsx having 1 sheet named sheet1 having 3 rows of data.

    Please help with what can be the possible solutions.

    Reply
      1. Gaurav Singh

        int cellType = cell.getCellType();

        is throwing a nullpointer exception in case of cells that are null. Pls help me in this case as the document which I have is having many null values at randomn postions. Thanks in Advance.

        Reply
    1. happyzhaosong

      You can create a thread pool ( for example store thread objects in an array ), then assign each thread in the array a excel file to convert. Wish this can help you.

      Reply
  15. @jasmeen

    I don’t want to create separate files for Y and N. I want to create files for each group of similar reg_no.
    Hence the first 3 records in one JSON, second 2records in another one and last one in different JSON and so on. I want test it with about thousand records and want to generate JSON accordingly.

    Reply
  16. @jasmeen

    hi, Jerry Zhao
    thank you for sharing this useful article.
    in my excel i set up one field “Status” which takes two values Y or N. as you see below is grouped data. so i need to generate separate json file for each group. how I can do it?

    status reg_num name
    Y T001 ABC
    N T001 XYZ
    N T001 EFG

    Y D002 Bob
    N D002 Jazz

    Y A004 Tom

    Thank you in Advance.

    Reply
      1. Jerry Zhao

        Maybe but i am not sure, i do not use jackson. But if you want to use jackson, you just need to replace the code in method getJSONStringFromList(List<List> dataTable).

        Reply
    2. Jerry Zhao

      You can sort the data in the getSheetDataList(Sheet sheet) method returned data list, and separate to two data list one for status column value Y and the other for status column value N. Then you can generate different JSON files.

      Reply
  17. @Di

    hi,
    I followed your all steps but getting this exceptions. please help to resolve. thanks

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/commons/collections/map/ListOrderedMap
    at net.sf.json.JSONObject.(JSONObject.java:1450)
    at merchant_Profile_Subcription_Details.MerchantInfoIntoJson.getJSONStringFromList(MerchantInfoIntoJson.java:162)
    at merchant_Profile_Subcription_Details.MerchantInfoIntoJson.getMerchantInfoInJson(MerchantInfoIntoJson.java:70)
    at merchant_Profile_Subcription_Details.MerchantInfoIntoJson.main(MerchantInfoIntoJson.java:37)
    Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections.map.ListOrderedMap
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
    … 4 more

    Reply
    1. @Di

      able to resolve above exception problem but since when I’m passing Employee.xlsx its working fine but when I’m trying to pass some different xlsx file. On the console its showing null.

      Reply
      2. luis Rozas

        it is showing null because there are some field in excel file in blank, that case has not been contemplated..
        i`m debugging to propose a solution

        Reply
      3. Akashdeep Singh

        How did you resolve this issue. I am not able to it.

        Getting following error.

        Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/commons/lang/exception/NestableRuntimeException
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClass(Unknown Source)
        at java.security.SecureClassLoader.defineClass(Unknown Source)
        at java.net.URLClassLoader.defineClass(Unknown Source)
        at java.net.URLClassLoader.access$100(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at com.kfpr.qa.util.ConvertExcelToJson.getJSONStringFromList(ConvertExcelToJson.java:175)
        at com.kfpr.qa.util.ConvertExcelToJson.creteJSONAndTextFileFromExcel(ConvertExcelToJson.java:74)
        at com.kfpr.qa.util.ConvertExcelToJson.main(ConvertExcelToJson.java:38)
        Caused by: java.lang.ClassNotFoundException: org.apache.commons.lang.exception.NestableRuntimeException
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        … 15 more

        Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.