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); // 为用户提供提示信息对话框
}