背景:项目需要一个全国省份的国标码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文件