java按坐标读取数据excel的数据

临时有个项目要求按excel的坐标从exce对应的单元格中抓取相应的数据 

使用的是poi第三方包，贴代码（后台是SpringBoot框架）：

后台代码：

package com.qm.jczx.common.validated;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;


import com.mongodb.gridfs.GridFSDBFile;
import com.qm.jczx.common.mongodb.MongoKit;
import com.qm.yqwl.core.Result;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * @version : 1.0.0
 * @Author : Byr
 * @Date : 2019/3/20 8:30
 * @Description :更具坐标读取excel单元格的数据
 */
@Slf4j
@RestController
@Api(value = "excel提取数据", description = "excel提取数据")
@RequestMapping("/getExcelDate")
public class ReadExcel {
    @Autowired
    CacheManager cacheManager;
    @Autowired
    MongoKit mongoKit; 
        public  List readExcel(Map map) throws IOException, InvalidFormatException {
            //从mongodb获得输入流
            GridFSDBFile f = mongoKit.findFile(map.get("fileId").toString());
            //存储页的list
            List sheetList= new ArrayList();
            try {
                // 获得工作簿
                Workbook workbook = WorkbookFactory.create(f.getInputStream());
                //本地文件
                //File file=new File("");
                // Workbook workbook = WorkbookFactory.create(file);
                // 获得工作表个数
                int sheetCount = workbook.getNumberOfSheets();
                // 遍历工作表
                for (int i = 0; i < sheetCount; i++) {
                    //储存行的List
                    List rowList=new ArrayList();
                    Sheet sheet = workbook.getSheetAt(i);
                    // 获得行数
                    int rows = sheet.getLastRowNum() + 1;
                    // 获得列数，先获得一行，在得到该行列数
                    Row tmp = sheet.getRow(i);
                    if (tmp == null) {
                        sheetList.add(i,null);
                        continue;
                    }
                    int cols = tmp.getPhysicalNumberOfCells();
                    // 读取数据
                    for (int row = 0; row < rows; row++) {
                        //储存列的数据
                        List coluList=new ArrayList();
                        Row r = sheet.getRow(row);
                        for (int col = 0; col < cols; col++) {
                            if(r!=null){
                                if(r.getCell(col)!=null){
                                    Object  value=null;
                                    if(r.getCell(col).getCellType() == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(r.getCell(col))){
                                        Date date = r.getCell(col).getDateCellValue();
                                        //装换为时间格式
                                        String formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                                        value=formatDate;
                                    }else {
                                        r.getCell(col).setCellType(CellType.STRING);
                                        value=r.getCell(col).getStringCellValue();
                                    }
                                    coluList.add(col,value);
                                }else {
                                    coluList.add(col,null);
                                }
                            }else {
                                coluList.add(col,null);
                            }
                        }
                        rowList.add(row,coluList);
                    }
                    sheetList.add(i,rowList);
                }
                System.out.println(sheetList);
                return sheetList;
            }catch (IOException e){
                e.printStackTrace();
            }
            return null;
        }
    /**
     * 将InputStream写入本地文件
     * @param input 输入流
     * @throws IOException IOException
     */
    public static String writeToLocal(InputStream input) throws IOException {
        int index;
        byte[] bytes = new byte[1024];
        File file=new File("");
        FileOutputStream outputStream=null;
        String filePath=file.getCanonicalPath()+"/src/main/webapp/template/temp.xls";
       try{
           outputStream= new FileOutputStream(filePath);
           while ((index = input.read(bytes)) != -1) {
               outputStream.write(bytes, 0, index);
               outputStream.flush();
           }
           return filePath;
       }catch (IOException e){
           e.getMessage();
           e.printStackTrace();
       }finally {
           input.close();
           outputStream.close();
       }
     return null;
    }

    /**
     *
     * @param map fileId，excel坐标
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
   public synchronized String excelValue(Map map) throws Exception {
       List list=null;
       String mapValue=map.get("coordinate").toString();
       //sheet页码
       Integer sheetNumber=Integer.valueOf(mapValue.substring(0,2));
       //列数
       Integer column=Integer.valueOf(stringToAscii(mapValue.substring(2,3).toUpperCase()))-65;
       //行数
       Integer row=Integer.valueOf(mapValue.substring(3,mapValue.length()))-1;
       //缓存key值
       String key= map.get("fileId").toString();
        try {
            //将值存入缓存中，根据自己情况来写
            Cache cache=cacheManager.getCache("myCache");
            if(cache.get(key)!=null){
                list=(List) cache.get(key).get();
            }else {
                list=this.readExcel(map);
                cache.put(key,list);
            }
            //根据坐标取出数据（根据excel的不同sheet的值也不一样，可根据自己的情况调试除sheet的页数）
            List sheetList=(List)list.get(sheetNumber);
            List rowList= (List) sheetList.get(row);
            String excelValue=rowList.get(column).toString();
            System.out.println(excelValue);
            return excelValue;
        }catch (Exception e){
            e.printStackTrace();
        }
         return null;
    }

    /**
     * 将字符转换为AsCll
     * @param value
     * @return
     */
    public static String stringToAscii(String value) {
        StringBuffer sbu = new StringBuffer();
        char[] chars = value.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            if(i != chars.length - 1)
            {
                sbu.append((int)chars[i]).append(",");
            }
            else {
                sbu.append((int)chars[i]);
            }
        }
        return sbu.toString();
    }
    /**接收前台传过来的值
     * 根据excel坐标读取excel单元格的数据
     * 注！工作表数量暂时为1~3页为准，后面的数据可能会乱 待后期优化
     * @Author：Byr
     * @param fileId excel的fileId
     * @param coordinateArr 单元格坐标 （格式为：工作表的第几页+坐标：例如：取出第一个工作表第A1的数据的坐标为：01A01）
     * @throws Exception
     */
    @PostMapping(value = "/readExcelDate", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public synchronized Result<Map> readExcelDate(@RequestParam(value = "fileId") String fileId,
                                     @RequestParam(value = "coordinateArr") String coordinateArr) throws Exception {
        //将字符串转换为数组型
      com.alibaba.fastjson.JSONArray array= com.alibaba.fastjson.JSONArray.parseArray(coordinateArr);
        Map<Integer,String> mapValue=new HashMap();
        for(int i=0;i<array.size();i++){
            Map map=new HashMap();
            map.put("fileId",fileId);
            map.put("coordinate",array.get(i));
            String excelValue=this.excelValue(map);
            mapValue.put(i,excelValue);
        }
        return Result.success(mapValue);
    }
}

前台代码：

 toReadExcel(){
                let that=this;     
                let coordinateArr1=["01C3","01I33","01U16","03I33","01I34","03I34","01U16"]//单元格坐标 （格式为：工作表的第几页+坐标：例如：取出第一个工作表第A1的数据的坐标为：01A01）

                let mapValue=[];
                const p1={}
                const param1 = {
                    params:{
                        fileId: this.fileList[0].fileId,
                        coordinateArr: JSON.stringify(coordinateArr1)
                    }
                };
                that.$http.post('/getExcelDate/readExcelDate',p1,param1).then((result) => {
                    console.log("excel数据", result);
                    if (result.data.isSuccess) {
                        mapValue=result.data.data;
                        var date=new Date(mapValue[0]);
                        var timestamp=date.getTime();
                        var D=(parseFloat(mapValue[2])*28.836)/(8.314*(22+273.15));
                        var ROUND=(1-D/7900)/(1-D/280.8989).toFixed(5);
                        var pAveQ=parseFloat(mapValue[3])/ROUND;
                        var pAveH=parseFloat(mapValue[5])/ROUND;
                          
                    }
                });
            },