Apache POI EXCEL(XLSX)转成JSON

Excel(xlsx、xls)转成json。Excel表文件保存成txt，Excel中的每一行转成txt中标准JSON。

---

原始数据格式：

---

Maven依赖

---

用apache poi实现excel转成json，每一条json保存在txt中

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

/**
* Excel转成Json
*
* HSSFWorkbook:操作Excel2003以前（包括2003）的版本，扩展名是.xls
* XSSFWorkbook:操作Excel2007的版本，扩展名是.xlsx
*/

public class XLSXtoJSON {
   public static void main(String[] args) throws Exception{
       String excelPath1="C:\\Users\\lenovo\\Desktop\\nnn\\333.xlsx"; //Excel2007
       String excelPath2="C:\\Users\\lenovo\\Desktop\\nnn\\all.xls"; //Excel2003以前（包括2003）
       //excel转换成json
       ArrayList<String> jsonStirngArrayList = xlsx_to_json(excelPath2);
       String targetFile="F:\\IdeaTestData\\testData\\alljson4.txt";
       //结果写入文件
       writeTotxtFile(jsonStirngArrayList,targetFile);
   }

   /**
    * @param excelPath
    * @return ArrayList<String>
    * @throws FileNotFoundException,InvalidFormatException
    * @description: excel文件生成对应的json字符串
    */
   public static ArrayList<String> xlsx_to_json(String excelPath) throws InvalidFormatException, IOException {
       ArrayList<String> jsonString = new ArrayList<String>();
       //Excel
       Workbook wb = WorkbookFactory.create(new FileInputStream(excelPath));
       //sheet
       Sheet sheet =  wb.getSheetAt(0);
       //标题
       String [] headNames=null;
       //每行
       for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
           Row row = sheet.getRow(i);
           if(i==0){ //标题
               headNames=new String[row.getPhysicalNumberOfCells()];
               //每列
               for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                   Cell cell = row.getCell(j);
                   headNames[j]=cell.getStringCellValue();
               }
           }else { //构造json
               StringBuilder stringBuilder = new StringBuilder();
               stringBuilder.append("{");
               for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                   Cell cell = row.getCell(j);
                   String key = headNames[j];
                   String value=cell.getStringCellValue().replaceAll("[\\n\\r\"]"," "); //删除换行
                   if(j!=row.getPhysicalNumberOfCells()-1){
                       stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"").append(",");
                   }else{
                       stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"");
                   }
               }
               stringBuilder.append("}");
               jsonString.add(stringBuilder.toString());
           }
       }
       wb.close();
       return jsonString;
   }
   /**
    * @param jsonString
    * @param targetFile
    * @return ArrayList<String>
    * @throws FileNotFoundException,InvalidFormatException
    * @description: json写入文件
    */

   public static void writeTotxtFile(ArrayList<String> jsonString,String targetFile) throws IOException{
       PrintWriter printWriter = new PrintWriter(targetFile);
       for (String jsonStr : jsonString) {
           printWriter.println(jsonStr);
           printWriter.flush();
       }
       printWriter.close();
   }
}

---

结果：

{"title":"China-U.S. ties developing in positive direction: foreign minister","center":"Urgent Headline News+ NEWS ALERT: China-U.S. ties developing in positive direction: foreign minister 2017-03-08 11:26:55JST"}
{"title":"China-U.S. should overcome zero-sum mentality: Chinese foreign minister","center":"Urgent Headline News+ NEWS ALERT: China-U.S. should overcome zero-sum mentality: Chinese foreign minister 2017-03-08 11:27:51JST"}
{"title":"Chinese Foreign Minister calls on North Korea to cease nuclear and missile tests","center":"BEIJING, March 8 (Reuters) - Chinese Foreign Minister Wang Yi said on Wednesday North Korea must stop nuclear and missile tests and that the United States and South Korea must also stop joint military drills.      Wang made the comments at his annual news conference on the sidelines of the annual meeting of China's parliament in Beijing. (Reporting by Ben Blanchard; Editing by Paul Tait; Writing by Philip Wen; Editing by Paul Tait)              REUTERS "}
{"title":"China calls for halt to N.Korea nuclear and missile activities ","center":"GLGL o0123 ASI/AFP-CQ98----- b i ALERT- 03-08 0011 China calls for halt to N.Korea nuclear and missile activities  dma/sls    ALERT-¥  AFP 080243 GMT MAR 17"}
{"title":"China proposes new idea over N. Korea's suspension of nuclear program","center":"BREAKING NEWS: China proposes new idea over N. Korea's suspension of nuclear program 2017-03-08 11:49:42JST"}
{"title":"China calls for halt to N.Korea nuclear, missile activities","center":"BEIJING, March 8, 2017 (AFP) - China's foreign minister called Wednesday for North Korea to suspend its nuclear and missile activities, and for the US and South Korea to halt military exercises to cool what he called a looming security  crisis.    To defuse the looming crisis on the (Korean) peninsula, China proposes that as a first step, the DPRK may suspend its nuclear and missile activities in exchange for the halt of the large-scale US-ROK exercises,  Foreign Minister Wang Yi said, using the acronyms for the two Koreas.   This 'suspension for suspension' can help us break out of the security dilemma and bring the parties back to the negotiating table. "}
{"title":"China's foreign minister calls for halt of U.S.-S. Korea military drill ","center":"BREAKING NEWS: China's foreign minister calls for halt of U.S.-S. Korea military drill 2017-03-08 11:50:38JST"}