一、依赖引入
<dependency><groupId>net.sourceforge.jexcelapi</groupId><artifactId>jxl</artifactId><version>2.6.12</version></dependency>
二、表格操作
1、读取xls文件
测试文件为:
代码:
publicvoidtest()throws IOException, BiffException{// 1、获取文件,创建workbook
File file=newFile("D:/test/自动化监测数据上传模板20210525.xls");
Workbook workbook= Workbook.getWorkbook(file);// 2.获取第一个工作表
Sheet sheet= workbook.getSheet(0);// 3.获取表中数据
Range[] rangecell= sheet.getMergedCells();
System.out.println("行:"+ sheet.getRows());
System.out.println("列:"+ sheet.getColumns());for(int i=0; i< sheet.getRows(); i++){for(int j=0; j< sheet.getColumns(); j++){
Cell cell= sheet.getCell(j, i);
String contents= cell.getContents();
System.out.print(contents+" ");}
System.out.println();}
workbook.close();}
输出结果(注意合并单元格处,需要特殊处理):
改造代码如下:
publicvoidtest()throws IOException, BiffException{// 1、获取文件,创建workbook
File file=newFile("D:/test/自动化监测数据上传模板20210525.xls");
Workbook workbook= Workbook.getWorkbook(file);// 2.获取第一个工作表
Sheet sheet= workbook.getSheet(0);// 3.获取表中数据// 返回合并单元格数据
Range[] rangecell= sheet.getMergedCells();
System.out.println("行:"+ sheet.getRows());
System.out.println("列:"+ sheet.getColumns());for(int i=0; i< sheet.getRows(); i++){for(int j=0; j< sheet.getColumns(); j++){
Cell cell= sheet.getCell(j, i);
String contents= cell.getContents();// 判断当前单元格,是否为合并单元格for(Range r: rangecell){if(i> r.getTopLeft().getRow()&&
i<= r.getBottomRight().getRow()&&
j>= r.getTopLeft().getColumn()&&
j<= r.getBottomRight().getColumn()){
contents= sheet.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow()).getContents();}}
System.out.print(contents+" ");}
System.out.println();}
workbook.close();}
结果: