1.导入jar包
<!-- poi:07版之前的二进制格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<!-- poi-ooxml:07版之后的OpenXML格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
2.添加PoiReadExl工具类
package com.nari.iot.tools;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class PoiReadExl {
//POI解析EXL内容
public static Map toReadExl(String path) {
// TODO Auto-generated method stub
//需要解析的EXCEL文件
File file = new File(path);
// File file = new File("D:/javahuanjin/.metadata/.plugins/org.eclipse.wst.server.core/tmp1/wtpwebapps/SSH/Html/upload/2.xls");
Map map=new HashMap();
try {
// System.out.println(file.exists());
//创建EXCEL,读取文件内容
InputStream is = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(is);
// HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
//获取一个工作表workbook.getSheet("sheet1");
// HSSFSheet sheet = workbook.getSheet("sheet1");
//默认读取第一个工作表sheet
HSSFSheet sheet = workbook.getSheetAt(0);
int firstRowNum = 0;
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
//循环读取每一行
for(int i = firstRowNum; i <= lastRowNum; i++){
List<String> cx = new ArrayList<String>();
HSSFRow row = sheet.getRow(i);
// System.out.println("第"+i+"行:"+row);
//获取当前行最后单元格列号
int lastCellNum = row.getLastCellNum();
for(int j = 0;j < lastCellNum;j++){
HSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING);
String value = null;
if(cell != null){
if(cell.getCellType().ordinal() == 0){
value = String.valueOf(cell.getNumericCellValue());
}else{
value = cell.getStringCellValue();
}
}else{
value =" ";
}
cx.add(value);
// System.out.print(value + " ");
}
System.out.println();
map.put(String.valueOf(i+1),cx);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return map;
}
}
3.java代码
@RequestMapping("/getRegulatoryPotential")
public String getRegulatoryPotential(int airFloor,int outTemp,int onOff,int setTemp){
Map map= PoiReadExl.toReadExl("src/main/resources/P矩阵数据.xls");
List list=new ArrayList();
for(int i=0;i<5;i++){
if(outTemp==(i+1)){
if (onOff==1){
list=(List)map.get(String.valueOf(i*6+1));
}else if (onOff==0){
if (setTemp==2){
list=(List)map.get(String.valueOf(i*6+2));
}else if (setTemp==3){
list=(List)map.get(String.valueOf(i*6+3));
}else if (setTemp==4){
list=(List)map.get(String.valueOf(i*6+4));
}else if (setTemp==5){
list=(List)map.get(String.valueOf(i*6+5));
}else if (setTemp==6){
list=(List)map.get(String.valueOf(i*6+6));
}
}
}
}
return JsonUtils.toString(list);
}
}