Excel转Json

1、任务需求

转换前

转换后

2、关键逻辑代码

a、获取excel内容

 //要读取的excel文件        book = Workbook.getWorkbook(new File(frontFile));        //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)        sheet = book.getSheet(0);        int position = 0;        for (int i = 1; i < sheet.getRows(); i++) {            //获取每一行的单元格            cell1 = sheet.getCell(0, i);//（列，行）            cell2 = sheet.getCell(1, i);            cell3 = sheet.getCell(2, i);            cell4 = sheet.getCell(3, i);            cell5 = sheet.getCell(4, i);            JSONObject object = new JSONObject();                        String kpPid = "0";                        if(!"".equals(cell1.getContents())){            kpPid = "0";            }else if(i>1){                        cell6 = sheet.getCell(3, i-1);                cell7= sheet.getCell(4, i-1);                String kpCode1 = null;                if(cell7.getContents().length()==3){                kpCode1 = "00"+cell7.getContents();               }else if(cell7.getContents().length()==4){               kpCode1 = "0"+cell7.getContents();               }else if(cell7.getContents().length()==5){                kpCode1 = cell7.getContents();               }                                kpPid = cell6.getContents()+""+kpCode1;            }                        object.put("kpPid",kpPid);                                                            if(!"".equals(cell1.getContents())){            object.put("kpName",cell1.getContents());            }else if(!"".equals(cell2.getContents())){            object.put("kpName",cell2.getContents());            }else if(!"".equals(cell3.getContents())){            object.put("kpName",cell3.getContents());            }                        object.put("kpSetId",cell4.getContents());            object.put("kpCode",cell5.getContents());                                    String kpCode = null;                        if(cell5.getContents().length()==3){             kpCode = "00"+cell5.getContents();            }else if(cell5.getContents().length()==4){            kpCode = "0"+cell5.getContents();            }else if(cell5.getContents().length()==5){             kpCode = cell5.getContents();            }                                    object.put("kpId", cell4.getContents() +""+ kpCode);                 if (!"".equals(cell1.getContents())) {            position++;           }                        object.put("position",position);                        String s = cell5.getContents();                        object.put("xkId",s.subSequence(0, 1));                                    array.add(object);        }

b、输出格式换

String jsonStr = array.toString();                int level = 0;        StringBuffer jsonForMatStr = new StringBuffer();        for(int i=0;i<jsonStr.length();i++){            char c = jsonStr.charAt(i);            if(level>0&&'\n'==jsonForMatStr.charAt(jsonForMatStr.length()-1)){                jsonForMatStr.append(getLevelStr(level));            }            switch (c) {            case '{':            case '[':                jsonForMatStr.append(c+"\n");                level++;                break;            case ',':                jsonForMatStr.append(c+"\n");                break;            case '}':            case ']':                jsonForMatStr.append("\n");                level--;                jsonForMatStr.append(getLevelStr(level));                jsonForMatStr.append(c);                break;            default:                jsonForMatStr.append(c);                break;            }        }                            System.out.println(jsonForMatStr.toString());                        FileWriter writer;        writer = new FileWriter(backFile);        writer.write(jsonForMatStr.toString());        writer.flush();        writer.close();                book.close();    } catch (Exception e) {        e.printStackTrace();    }}private static String getLevelStr(int level){        StringBuffer levelStr = new StringBuffer();        for(int levelI = 0;levelI<level ; levelI++){            levelStr.append("\t");        }        return levelStr.toString();    }}

3、界面设计及代码

代码

    // 选择要转换文件按钮的单击事件    protected void do_entryButton_actionPerformed(ActionEvent arg0) {        java.awt.FileDialog fd = new FileDialog(this);        fd.setVisible(true);        String filePath = fd.getDirectory() + fd.getFile();        if (!filePath.equals("") && !(filePath == null)) {            entryTextField.setText(filePath);        }            }        // 选择转换后文件的保存地址按钮的单击事件    protected void do_saveButton_actionPerformed(ActionEvent arg0) {        FileDialog saveDialog = new FileDialog(this, "保存文件对话框", FileDialog.SAVE);        saveDialog.setVisible(true);        String filePath = saveDialog.getDirectory() + saveDialog.getFile();        if (!filePath.equals("") && !(filePath == null)) {            saveTextField.setText(filePath);        }    }        // 确定加密按钮单击事件    protected void do_confirmButton_actionPerformed(ActionEvent arg0) {Excel2Json_logic Excel2Json = new Excel2Json_logic();  //创建转换json的类对象Excel2Json.excel2json(entryTextField.getText(), saveTextField.getText()); //调用对文件进行转换json的方法    JOptionPane.showMessageDialog(getContentPane(), "Excel2Json成功！", "信息提示框",            JOptionPane.WARNING_MESSAGE); // 为用户提供提示信息对话框}