POI读取Excel生成JSON数据

 

背景：项目需要一个全国省份的国标码JSON数据，网上找了一圈，没有找到合适的，但是自己有Excel版本的数据，于是乎，不想copy数据，就写了一个小程序，用POI读取Excel，然后生成JSON数据

 

 

package com.besttone.util;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.nio.Buffer;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;


public class CreateProvinceJsonUtil {

    /**
     * @description: 根据excel文件生成对应的json字符串
     * @return
     * @throws FileNotFoundException
     */
    public static String createJson() throws FileNotFoundException {
        InputStream is = new FileInputStream("E:\\信令项目\\provinceCode.xls");
        StringBuffer buffer = new StringBuffer();
        try {
            Workbook workbook = new HSSFWorkbook(is);
            HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
            String key = "";
            String value = "";
            buffer.append("[");            
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                HSSFRow row = sheet.getRow(i);
                for(int j=0;j<row.getPhysicalNumberOfCells();j++){
                    HSSFCell cell = row.getCell(j);                    
                   if(i==0){
                       if(j==0){
                           key = cell.getStringCellValue();                           
                       }
                       if(j==1){
                           value = cell.getStringCellValue();                           
                       }
                   } else{
                       if(j==0){
                           buffer.append("{\"" + key + "\"" + ":" + "\"" +  getCellValue(cell) + "\"" + ",");
                       }
                       if(j==1){
                           buffer.append("\"" + value + "\"" + ":" + "\"" +  getCellValue(cell) + "\"}");
                       }

                   }
                }
                if(sheet.getPhysicalNumberOfRows()-1!=i && i!=0){
                    buffer.append(",");
                }
                buffer.append("\r");
            }
            buffer.append("]");
        } catch (IOException e) {
            System.out.println("出现异常");
            e.printStackTrace();
        }

        return buffer.toString();
    }

    /** 
     * 获取当前单元格内容 
     * */  
    private static String getCellValue(Cell cell){  
        String value = "";  
        if(cell!=null){  
            switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_NUMERIC:  
                if(HSSFDateUtil.isCellDateFormatted(cell)){ //日期类型  
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());  
                    value = sdf.format(date);  
                }else{  
                    Integer data = (int) cell.getNumericCellValue();  
                    value = data.toString();  
                }  
                break;  
            case Cell.CELL_TYPE_STRING:  
                value = cell.getStringCellValue();  
                break;  
            case Cell.CELL_TYPE_BOOLEAN:  
                Boolean data = cell.getBooleanCellValue();  
                value = data.toString();   
                break;  
            case Cell.CELL_TYPE_ERROR:  
                System.out.println("单元格内容出现错误");  
                break;  
            case Cell.CELL_TYPE_FORMULA:  
                value = String.valueOf(cell.getNumericCellValue());    
                if (value.equals("NaN")) {// 如果获取的数据值非法,就将其装换为对应的字符串  
                    value = cell.getStringCellValue().toString();    
                }             
                break;            
            case Cell.CELL_TYPE_BLANK:  
                System.out.println("单元格内容 为空值 ");  
                break;            
            default :  
                value = cell.getStringCellValue().toString();  
                break;  
            }  
        }  
        return value;  
    }  
    
    /**
     * @description: 将生成的字符串输出到一个文件中  
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws IOException {
        String str = createJson();
        OutputStream os = new FileOutputStream("E:\\信令项目\\provinceCode.json");
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(os, "UTF-8"));
        ObjectOutputStream oos = null;
        try {
            bw.write(str);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            if(bw!=null){
                bw.close();
            }
            if(os!=null){
                os.close();
            }
        }
    }

}

 

要读取的excel文件：

 

 

 

最后生成的JSON文件

 

评论

3 楼 lizepeng201021 2016-02-17  

mark.....

2 楼 henu_zhangyang 2016-02-15  

bjqincy 写道

既然有excel的文件，为什么不用excel的公式来生成的呢

受教 又多了一种方式

1 楼 bjqincy 2016-02-15  

既然有excel的文件，为什么不用excel的公式来生成的呢