java通过apache POI读取Excel文件生成JSON数据
java通过apache POI读取Excel文件生成JSON数据
动听的椰子 发表于2年前
java通过apache POI读取Excel文件生成JSON数据
  • 发表于 2年前
  • 阅读 76
  • 收藏 3
  • 点赞 1
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

摘要: java通过apache POI读取Excel文件生成JSON数据
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
            <scope>test</scope>
        </dependency>
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.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 {

  /**
   * @return
   * @throws FileNotFoundException
   * @description: 根据excel文件生成对应的json字符串
   */
  public static String createJson() throws FileNotFoundException {
    InputStream is = new FileInputStream("D:\\a.xls");
    StringBuilder buffer = new StringBuilder();
    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("{\"").append(key).append("\"").append(":").append("\"").append(getCellValue(cell)).append("\"").append(",");
            }
            if (j == 1) {
              buffer.append("\"").append(value).append("\"").append(":").append("\"").append(getCellValue(cell)).append("\"}");
            }

          }
        }
        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();
          }
          break;
        case Cell.CELL_TYPE_BLANK:
          System.out.println("单元格内容 为空值 ");
          break;
        default:
          value = cell.getStringCellValue();
          break;
      }
    }
    return value;
  }

  /**
   * @param args
   * @throws IOException
   * @description: 将生成的字符串输出到一个文件中
   */
  public static void main(String[] args) throws IOException {
    String str = createJson();
    OutputStream os = new FileOutputStream("D:\\provinceCode.json");
    BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(os, "UTF-8"));
    ObjectOutputStream oos = null;
    try {
      bw.write(str);
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      bw.close();
      os.close();
    }
  }

}





共有 人打赏支持
粉丝 3
博文 108
码字总数 17603
×
动听的椰子
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: