java获取excel中数据，并转化为JSON格式

java通过poi读取execl文件，并且将其中的map格式和数组格式的字符串，转化为相应的数据类型。

• 引入poi库
• 读取execl数据，转化为json字符串
• 将其中的map格式和数组格式的字符串，转化为相应的数据类型

---

引入poi库：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>com.google.code.gson</groupId>
    <artifactId>gson</artifactId>
    <version>2.8.2</version>
</dependency>
<dependency>
    <groupId>net.sf.json-lib</groupId>
    <artifactId>json-lib</artifactId>
    <version>2.4</version>
    <classifier>jdk15</classifier>
</dependency>

execl中的数据：

代码：

import cn.com.qiyitech.qiyibiz.controller.ControllerConstant;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import lombok.Getter;
import lombok.Setter;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Map;

public class EexelToJson {
    /**
     * 读取xlsx文件
     *
     * @param path
     * @throws IOException
     * @throws ParseException
     */
    public static String readXlsx(String path) throws IOException {
        JSONArray dataArray = new JSONArray();

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(path));
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            String sheetName = xssfSheet.getSheetName();
            if (xssfSheet == null) {
                continue;
            }
            //当前sheet的json文件
            JSONObject sheetJson = new JSONObject();
            //当前sheet的array，作为sheetJson 的value值
            JSONArray sheetArr = new JSONArray();
            //sheet的第一行，获取作为json的key值
            JSONArray key = new JSONArray();
            int xssfLastRowNum = xssfSheet.getLastRowNum();
            // 循环行Row
            for (int rowNum = 0; rowNum <= xssfLastRowNum; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null) {
                    continue;
                }

                // 循环列Cell，在这里组合json文件
                int firstCellNum = xssfRow.getFirstCellNum();
                int lastCellNum = xssfRow.getLastCellNum();
                JSONObject rowJson = new JSONObject();
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    XSSFCell cell = null;
                    try {
                        cell = xssfRow.getCell(cellNum);
                        if (cell == null) {
                            rowJson.put(key.getString(cellNum), "");
                            continue;
                        }
                        if (rowNum == 0)
                            key.add(toString(cell));
                        else {
                            //若是列号超过了key的大小，则跳过
                            if (cellNum >= key.size()) continue;
                            rowJson.put(key.getString(cellNum), toString(cell));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (rowJson.keySet().size() > 0)
                    sheetArr.add(rowJson);
            }
            sheetJson.put(sheetName, shuffleData(sheetArr));
            dataArray.add(sheetJson);
        }
        return dataArray.toString();
    }

    public static Object toString(XSSFCell cell) {
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                cell.setCellType(CellType.STRING);
                return "";
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                    return sdf.format(cell.getDateCellValue());
                }
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue();
            case STRING:
                String val = cell.getStringCellValue();
                if ("无".equalsIgnoreCase(val)) return "";
                //将其中的map格式和数组格式的字符串，转化为相应的数据类型
                if (val.indexOf("{") > -1) {
                    JSONObject jsonObject = JSONObject.fromObject(val);
                    Map<String, Integer> mapJson = JSONObject.fromObject(jsonObject);
                    return mapJson;
                }
                if (val.indexOf("[") > -1) {
                    val = val.substring(1, val.length() - 1);
                    String[] array = val.split(",");
                    return array;
                }
                return val;
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case ERROR:
                return "非法字符";
            default:
                return "未知字符";
        }
    }

    public static JSONArray shuffleData(JSONArray sheetArr) {
        JSONArray array = new JSONArray();
        for (int i = 0; i < sheetArr.size(); i++) {
            JSONObject object = sheetArr.getJSONObject(i);
            int count = 0;
            int length = 0;
            for (Object key : object.keySet()) {
                Object o = object.get((String) key);
                length++;
                boolean b = StringUtils.isEmpty(o.toString());
                if (b) {
                    count++;
                }
            }
            if (count != length) {
                array.add(object);
            }
        }
        return array;
    }

    public static void main(String[] args) {
        try {
            String s = readXlsx("d:/image/123.xlsx");
            JSONArray jsonArray = JSONArray.fromObject(s);
            JSONObject jsonObject = jsonArray.getJSONObject(0);
            String s1 = jsonObject.get("Sheet1").toString();
            jsonArray = JSONArray.fromObject(s1);
            s1 = jsonArray.getJSONObject(0).toString();
            GsonBuilder gsonBuilder = new GsonBuilder();
            Gson gson = gsonBuilder.create();
            test i = gson.fromJson(s1, test.class);
            System.out.println(i);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Getter
    @Setter
    class test {
        private String[] test;
        private Map<String, Integer> prices;
    }
}