springcloud读取Excel数据

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);
    }
}