Java把Excel表格转换成json的所有代码

Java把Excel表格转换成json

**pom文件

<dependencies>

        <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>

        <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</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>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.56</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.example</groupId>
            <artifactId>WebMavwe</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.7.5</version>
        </dependency>

        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.4</version>
        </dependency>

        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-core-asl</artifactId>
            <version>1.9.4</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.21</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.21</version>
        </dependency>

    </dependencies>`**

***导包

`package com.json;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;

main方法*

public class JsonExcel {

    private final static Logger logger = LoggerFactory.getLogger(JsonExcel.class);
    //定义一个全局的ObjectMapper
    private final static ObjectMapper mapper = new ObjectMapper();

    public static void main(String[] args) {
        try {
            //获取Excel表格的路径
            FileInputStream fis = new FileInputStream("D:\\WangZY\\Excel\\test.xls");
            //获取Workbook
            Workbook workbook = WorkbookFactory.create(fis);
            //获取sheet数
            int sheetNum = workbook.getNumberOfSheets();
            //获取JSONObject 注意导fastjson包
            JSONObject jsonObject = new JSONObject();
            //循环
            for (int i = 0; i < sheetNum; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                //获取最大行数
                int rowNum = sheet.getPhysicalNumberOfRows();
                //简单判断一下
                if(rowNum<=1){
                    continue;
                }
                //获取第一行
                Row row = sheet.getRow(0);
                //获取最大列数
                int colnum = row.getPhysicalNumberOfCells();

                // System.out.println("列：："+colnum);

                JSONArray jsonArray = new JSONArray();
                //循环读取行
                for (int i1 = 1; i1 < rowNum; i1++) {
                    Row row1 = sheet.getRow(i1);
                    //判断行是否为空
                    if(row1 != null){
                        JSONObject rowObj = new JSONObject();
                        //循环列
                        for (int i2 = 0; i2 < colnum; i2++) {

                            //System.out.println("列数：："+colnum);

                            Cell row1Cell = row1.getCell(i2);

                            //判断列是否为空
                            if(row1Cell != null){

                               //System.out.println("问题？"+row1Cell);

                                //判断row1Cell的类型
                                switch (row1Cell.getCellType()){
                                    case Cell.CELL_TYPE_NUMERIC:{
                                        //数字
                                        rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getNumericCellValue());
                                        break;
                                    }
                                    case Cell.CELL_TYPE_FORMULA:{
                                        //判断rowCell是否是日期格式
                                        if(DateUtil.isCellDateFormatted(row1Cell)){
                                            rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getDateCellValue());
                                        }else {
                                            //数字
                                            rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getNumericCellValue());
                                        }
                                        break;
                                    }
                                    case Cell.CELL_TYPE_STRING:{
                                        rowObj.put(row.getCell(i2).getStringCellValue(),row1Cell.getStringCellValue());
                                        break;
                                    }
                                    default:
                                        rowObj.put(row.getCell(i2).getStringCellValue(),"");
                                }

                            }else {
                                rowObj.put(row.getCell(i2).getStringCellValue(),"");
                            }

                        }


//
                        jsonArray.add(rowObj);
                    }

                }
                logger.info(jsonArray.toJSONString());
                jsonObject.put(sheet.getSheetName(),jsonArray);
                System.out.println(jsonObject);
            }
            logger.info(jsonObject.toJSONString());
           // System.out.println(jsonObject);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}