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

Question

我想读取 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();
    }
}

}

原文由 Prathik 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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

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

原文由 Zacharia Musa Manyoni 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

使用 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;

}

原文由 Jones Michael 发布，翻译遵循 CC BY-SA 4.0 许可协议