Convert Excel To JSON In Java Example

A reader of article Read / Write Excel Data Using Apache POI ask 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 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 require below jar files, you can go to maven repository to search and download them if you add the jars by hand. You can read 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-jdk15.jar
  6. poi-bin-3.16-20170419.zip

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

2. Convert Excel To JSON Example Source Code.

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 data from 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 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 value is counted use 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 current 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());
        }
    }
}
(Visited 110 times, 1 visits today)
READ :   Sort Java Objects Using Comparable And Comparator

11 Comments


  1. 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. 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

      1. I think you need to add some break point in your java source code to debug the error.

        Reply

    2. You need add commons-collections-3.2.1.jar into your java project. You can find the download link in the article.

      Reply

  2. 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. one more thing can we implement the same code by using jackson??

      Reply

      1. 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. 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

  3. 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

  4. Can we use threads as i need to read multiple excel files and convert it into json? how i can do this?

    Reply

    1. 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

Leave a Reply

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.