java 读取excel内容转为JSONArray

需要引入的JAR

    <!--*.xls-->

        <dependency>

            <groupId>net.sourceforge.jexcelapi</groupId>

            <artifactId>jxl</artifactId>

            <version>2.6.8</version>

        </dependency>

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>fastjson</artifactId>

            <version>1.2.7</version>

        </dependency>

测试类

package com.taobao.tair;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import java.io.File;

public class Excel {

    public static void main(String[] args) {

        Sheet sheet;

        Workbook book;

        Cell cell1, cell2, cell3, cell4, cell5;

        JSONArray array = new JSONArray();

        try {

            //为要读取的excel文件名

            book = Workbook.getWorkbook(new File("D://b.xls"));

            //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)

            sheet = book.getSheet(0);

            for (int i = 1; i < sheet.getRows(); i++) {

                //获取每一行的单元格

                cell1 = sheet.getCell(0, i);//（列，行）

                cell2 = sheet.getCell(1, i);

                cell3 = sheet.getCell(2, i);

                cell4 = sheet.getCell(3, i);

                cell5 = sheet.getCell(4, i);

                if ("".equals(cell1.getContents())) {//如果读取的数据为空

                    break;

                }

                JSONObject object = new JSONObject();

                object.put("ID",cell1.getContents());

                object.put("编号",cell2.getContents());

                object.put("姓名",cell3.getContents());

                object.put("数量",cell4.getContents());

                object.put("住址",cell5.getContents());

                array.add(object);

            }

            System.out.println(array.toString());

            book.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}

b.xls文件：

使用 java 代码读取 excel 文件代码时报错如下：

jxl.read.biff.BiffException: Unable to recognize OLE stream
    at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
    at jxl.read.biff.File.<init>(File.java:127)
    at jxl.Workbook.getWorkbook(Workbook.java:268)
    at jxl.Workbook.getWorkbook(Workbook.java:253)

原因：不支出读取 excel 2007 文件(*.xlsx)。只支持 excel 2003 (*.xls)。

将b.xls转换为2003版就可以了。

[{"姓名":"张三","数量":"100","住址":"浙江省杭州市","ID":"1","编号":"1001"},{"姓名":"李四","数量":"200","住址":"湖北省武汉市","ID":"2","编号":"1002"},{"姓名":"王五","数量":"300","住址":"广东省深圳市","ID":"3","编号":"1003"},{"姓名":"赵六","数量":"400","住址":"湖南省长沙市","ID":"4","编号":"1004"}]