Java 如何获取excel数据并将其转换为JSON

How can i get excel data and convert it to JSON

提问by Prathik

I want to read the excel files(.xls,.xlsx) and convert it into JSON format and save it. Here is a code which enable me to read data from the excel file but im not able to getting how to put data in JSON format. Can anyone help me out.

我想读取excel文件（.xls，.xlsx）并将其转换为JSON格式并保存。这是一个代码，它使我能够从 excel 文件中读取数据，但我无法获得如何将数据放入 JSON 格式。谁能帮我吗。

public class ReadExcelFile {
private static XSSFWorkbook mybook;
static String fileLocation = "D://Traniee-SPG//Book1.xlsx";

public static void main(String[] args){
    try{
        File newFile = new File(fileLocation);
        FileInputStream fIO = new FileInputStream(newFile);
        mybook = new XSSFWorkbook(fIO);         //finds the Excelfile
        XSSFSheet mySheet = mybook.getSheetAt(0);// Return first sheet from the XLSX workbook
        Iterator<Row> rowIterator = mySheet.iterator(); //create a cursor called iterator to all rows in sheet
        Row r;
        Cell c;
        //to travel into the Excel spreadsheet
        while(rowIterator.hasNext())    {
             r = rowIterator.next();
            //Cursor points to row
            Iterator<Cell> cell_Iterator = r.cellIterator();
            while(cell_Iterator.hasNext())  {
                 c = cell_Iterator.next();
                //Cursor points to cell
                switch (c.getCellType())    {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(c.getStringCellValue()+"\t");
                    //System.out.println("Case String");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(c.getNumericCellValue()+"\t");
                    //System.out.println("Case number");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(c.getBooleanCellValue()+"\t");
                    System.out.println("Case boolean");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.print(c.getCellFormula()+"\t");
                    //System.out.println("Case formula");
                    break;
                default:
                }                   
            }
            System.out.println(" ");//next to display in table format
       }            
        mybook.close();
        fIO.close();
    }
    catch(FileNotFoundException ef){
        ef.printStackTrace();
    }
    catch(IOException ei){
        ei.printStackTrace();
    }
}

}

}

采纳答案by Zach

If looking for a simple online conversion tool, use this: http://www.convertcsv.com/csv-to-json.htm

如果正在寻找一个简单的在线转换工具，请使用：http: //www.convertcsv.com/csv-to-json.htm

Or do it programmatically as this guy does: https://github.com/nullpunkt/excel-to-json/blob/master/src/main/java/net/nullpunkt/exceljson/convert/ExcelToJsonConverter.java

或者像这个人那样以编程方式进行：https: //github.com/nullpunkt/excel-to-json/blob/master/src/main/java/net/nullpunkt/exceljson/convert/ExcelToJsonConverter.java

回答by Akki

package com.core.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.sling.commons.json.JSONException;
import org.apache.sling.commons.json.JSONcellValuesect;
import org.json.simple.JSONArray;

import com.PojoClassName;

public class ReadExcelUtil {
//create pojo class based on excel sheet columns 

    public static List<PojoClassName> readAssetExcel(
            final InputStream inputStream) {
        List<PojoClassName> cellValues = new ArrayList<PojoClassName>();

        XSSFWorkbook workbook;
        try {

            workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            cellValues = damcellValues(sheet, cellValues);
            return cellValues;

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return cellValues;

    }

    private static List<PojoClassName> damcellValues(XSSFSheet sheet,
            List<PojoClassName> cellValues) {

        int num = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i <= num; i++) {

            Row column = sheet.getRow(i);

            if (column.getCell(0) == null) {
                break;
            }

            PojoClassName object = new PojoClassName();

            String str1 = column.getCell(0).getStringCellValue();
            object.setDamPath(str1);

            String str2 = column.getCell(1).getStringCellValue();
            object.setServerUrl(str2);

            String str3 = column.getCell(2).getStringCellValue();
            object.setAssetTitle(str3);

            String str4 = column.getCell(3).getStringCellValue();
            object.setAssetDescription(str4);

            String str5 = column.getCell(4).getStringCellValue();
            object.setSourceId(str5);

            cellValues.add(object);
        }

        return cellValues;

    }

    //you can use below method to convert the List into Json

    public static String getJsonFromMyFormObject(List<DamAssetDetails> obj) throws JSONException{

        JSONArray jsonArray = new JSONArray();
        for (int i = 0; i < obj.size(); i++)`enter code here`
        {
          JSONObject formDetailsJson = new JSONObject();
          formDetailsJson.put("key1", obj.get(i).getDamPath());
          formDetailsJson.put("key2", obj.get(i).getServerUrl());
          formDetailsJson.put("key3", obj.get(i).getAssetTitle());
          formDetailsJson.put("key4", obj.get(i).getAssetDescription());
          formDetailsJson.put("key5", obj.get(i).getSourceId());
          jsonArray.add(formDetailsJson);
        }
        //responseDetailsJson.put("obj", jsonArray);
        return jsonArray.toJSONString();
      }

}

回答by Jones Michael

Using Gson given the fact the first row in each sheet are the column names:

鉴于每个工作表中的第一行是列名，因此使用 Gson：

public static JsonObject getExcelDataAsJsonObject(File excelFile) {

    JsonObject sheetsJsonObject = new JsonObject();
    Workbook workbook = null;

    try {
        workbook = new XSSFWorkbook(excelFile);
    } catch (InvalidFormatException | IOException e) {
        TestLogUtils.logErrorMessage(
                "ExcelUtils -> getExcelDataAsJsonObject() :: Exception thrown constructing XSSFWorkbook from provided excel file.  InvalidFormatException | IOException => "
                        + TestLogUtils.convertStackTraceToString(e));
    }

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {

        JsonArray sheetArray = new JsonArray();
        ArrayList<String> columnNames = new ArrayList<String>();
        Sheet sheet = workbook.getSheetAt(i);
        Iterator<Row> sheetIterator = sheet.iterator();

        while (sheetIterator.hasNext()) {

            Row currentRow = sheetIterator.next();
            JsonObject jsonObject = new JsonObject();

            if (currentRow.getRowNum() != 0) {

                for (int j = 0; j < columnNames.size(); j++) {

                    if (currentRow.getCell(j) != null) {
                        if (currentRow.getCell(j).getCellTypeEnum() == CellType.STRING) {
                            jsonObject.addProperty(columnNames.get(j), currentRow.getCell(j).getStringCellValue());
                        } else if (currentRow.getCell(j).getCellTypeEnum() == CellType.NUMERIC) {
                            jsonObject.addProperty(columnNames.get(j), currentRow.getCell(j).getNumericCellValue());
                        } else if (currentRow.getCell(j).getCellTypeEnum() == CellType.BOOLEAN) {
                            jsonObject.addProperty(columnNames.get(j), currentRow.getCell(j).getBooleanCellValue());
                        } else if (currentRow.getCell(j).getCellTypeEnum() == CellType.BLANK) {
                            jsonObject.addProperty(columnNames.get(j), "");
                        }
                    } else {
                        jsonObject.addProperty(columnNames.get(j), "");
                    }

                }

                sheetArray.add(jsonObject);

            } else {
                // store column names
                for (int k = 0; k < currentRow.getPhysicalNumberOfCells(); k++) {
                    columnNames.add(currentRow.getCell(k).getStringCellValue());
                }
            }

        }

        sheetsJsonObject.add(workbook.getSheetName(i), sheetArray);

    }

    return sheetsJsonObject;

}

回答by Jaimin Mehta

public static String ExcelReader(String filePath, int index) throws IOException {
    List<String> list = new ArrayList<String>();

    try {

        FileInputStream excelFile = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(excelFile);
        Sheet datatypeSheet = workbook.getSheetAt(index);
        XSSFRow row = (XSSFRow) datatypeSheet.getRow(0);
        String TC_ID = String.valueOf(row.getCell(0));
        String TS_ID = String.valueOf(row.getCell(1));
        String Test_Steps = String.valueOf(row.getCell(2));
        String Execution_Flag = String.valueOf(row.getCell(3));
        String IdentifierType = String.valueOf(row.getCell(4));
        String IdentifierValue = String.valueOf(row.getCell(5));
        String Action_Keyword = String.valueOf(row.getCell(6));
        String Dataset = String.valueOf(row.getCell(7));


        for (int rowNumber = 1; rowNumber <= datatypeSheet.getLastRowNum(); rowNumber++) {

            list.add(jsonObject.toString());
            XSSFRow row1 = (XSSFRow) datatypeSheet.getRow(rowNumber);

            for (int columnNumber = 0; columnNumber < row.getLastCellNum(); columnNumber++) {
                String cell = String.valueOf(row1.getCell(columnNumber));
                if (cell != null) {
                    switch (columnNumber) {
                        case 0:
                            jsonObject.addProperty(*** cell);
                            break;

                        case 1:
                            jsonObject.addProperty(**, cell);
                            break;

                        case 2:
                            jsonObject.addProperty(**, cell);
                            break;






                }
            }
            Gson gson = new GsonBuilder().setPrettyPrinting().create();
          json = gson.toJson(jsonObject);
          System.out.print(json);




        }


    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return jsonObject.toString();
}

}

}