需要引入的JAR
net.sourceforge.jexcelapi
jxl
2.6.8
com.alibaba
fastjson
1.2.7
测试类
packagecom.taobao.tair;importcom.alibaba.fastjson.JSONArray;importcom.alibaba.fastjson.JSONObject;importjxl.Cell;importjxl.Sheet;importjxl.Workbook;importjava.io.File;public classExcel {public static voidmain(String[] args) {
Sheet sheet;
Workbook book;
Cell cell1, cell2, cell3, cell4, cell5;
JSONArray array= newJSONArray();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= newJSONObject();
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.(CompoundFile.java:116)
at jxl.read.biff.File.(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"}]