java遍历excel 表格

package 工具类;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * 导入excel帮助类
 */
public class ExcelUtil {

    /**
     * 导入excel表格 返回表格数据
     * @param file
     * @return 表格数据
     */
    public static ArrayList<ArrayList<Object>> ImportExcel(MultipartFile file){
        ObjectMapper objectMapper =new ObjectMapper();
        String array = "[\"0\",\"1\"]";

        try {

            InputStream inputStream =file.getInputStream();//获取前端传递过来的文件对象，存储在“inputStream”中
            String fileName = file.getOriginalFilename();//获取文件名

            Workbook workbook =null; //用于存储解析后的Excel文件

            //判断文件扩展名为“.xls还是xlsx的Excel文件”,因为不同扩展名的Excel所用到的解析方法不同
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if(".xls".equals(fileType)){
                workbook= new HSSFWorkbook(inputStream);//HSSFWorkbook专门解析.xls文件
            }else if(".xlsx".equals(fileType)){
                workbook = new XSSFWorkbook(inputStream);//XSSFWorkbook专门解析.xlsx文件
            }

            ArrayList<ArrayList<Object>> list =new ArrayList<>();

            Sheet sheet ; //工作表
            Row row;      //行
            Cell cell;    //单元格

            //循环遍历，获取数据
            for(int i=0;i<workbook.getNumberOfSheets();i++){
                sheet=workbook.getSheetAt(i);//获取sheet
                for(int j=sheet.getFirstRowNum();j<=sheet.getLastRowNum();j++){//从有数据的第行开始遍历
                    row=sheet.getRow(j);
                    if(row!=null&&row.getFirstCellNum()!=j){ //row.getFirstCellNum()!=j的作用是去除首行，即标题行，如果无标题行可将该条件去掉
                        ArrayList tempList =new ArrayList();
                        for(int k=row.getFirstCellNum();k<row.getLastCellNum();k++){//这里需要注意的是getLastCellNum()的返回值为“下标+1”
                            cell =row.getCell(k);
                            tempList.add(cell);
                        }
                        list.add(tempList);
                    }
                }
            }

//            System.out.println("我是读取的字符串："+nativeStr);
//            System.out.println("我是读取的数组："+arrayList.toString());
            System.out.println("我是解析的Excel："+list.toString());

            return list;
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }


}

遍历

@ResponseBody
    @RequestMapping(value = "/importExcel.do",method = {RequestMethod.POST})
    public ActionResult importData(MultipartFile file, String activeId, String deptId, HttpServletRequest request) throws Exception
    {
        //解析表格数据
        ArrayList<ArrayList<Object>> excelList = ExcelUtil.ImportExcel(file);
        if (excelList == null || excelList.isEmpty()){
            return new ActionResult("Excel表格数据为空",false);
        }

        List<ZSzpbGrCpcj> cpcjList = new ArrayList<>();
        for ( ArrayList<Object> obj:excelList){
            ZSzpbGrCpcj grCpcj = new ZSzpbGrCpcj();

            XSSFCell id = (XSSFCell)obj.get(0);
            DataFormatter formatter = new DataFormatter();
            String valueOfCell = formatter.formatCellValue(id);
            grCpcj.setId(valueOfCell);

            XSSFCell userName = (XSSFCell)obj.get(1);
            grCpcj.setUserName( userName.getStringCellValue());

            XSSFCell dw = (XSSFCell)obj.get(2);
            grCpcj.setDw(dw.getStringCellValue());


            XSSFCell dxpgcj = (XSSFCell)obj.get(3);//定性评估
            grCpcj.setDxpgcj(dxpgcj.getNumericCellValue());

            XSSFCell mzcp = (XSSFCell)obj.get(4);//民主测评成绩
            grCpcj.setMzcpcj(mzcp.getNumericCellValue());


            XSSFCell zhcj = (XSSFCell)obj.get(5);//综合成绩
            grCpcj.setZhpjcj(zhcj.getNumericCellValue());


            XSSFCell tjcj = (XSSFCell)obj.get(6);//推荐结果
            grCpcj.setTjjg(tjcj.getStringCellValue());

            cpcjList.add(grCpcj);

        }

        List<ZSzpbGrCpcj> ActiveScoreList = getDao().getCpcjByActiveId(activeId);
        //定义一个list，错误数据集合，最后统一写入excel中，
        List<ZSzpbGrCpcj> errorList = new ArrayList();
        //判断是否存在错误数据的flag
        boolean errorFlag = false;
        boolean tempFlag = false;
        //判断是否重复的map
        HashMap<String,Object> mapTemp = new HashMap<String,Object>();
        //计划评比部门人员集合
        HashMap<String,String> userTemp = new HashMap<String,String>();
        //excel表格解析数据
        Iterator<ZSzpbGrCpcj> iterator = cpcjList.iterator();
        //user 对象数据
        List<CpcjUserVo> userList = getDao().getUserByDeptName(deptId);

        for (ZSzpbGrCpcj bean : ActiveScoreList) {
            mapTemp.put(bean.getUserName()+bean.getDw(),bean);
        }
        for(CpcjUserVo user:userList)
        {
            userTemp.put(user.getUserName()+user.getDeptName(),"1");
        }
        while (iterator.hasNext()) {
            String errorMsg = "";
            ZSzpbGrCpcj next = iterator.next();
            next.setHdId(activeId);
            next.setDw(deptId);
            String nextId = next.getId();
            if(!userTemp.containsKey(next.getUserName()+next.getDw()))
            {
                tempFlag = true;
                errorFlag = true;
                errorMsg += "该部门人员不在此次计划评比中,";
                next.setErrorMsg(errorMsg);
                System.out.println(next.getUserName() + errorMsg);
                errorList.add(next);
                continue;
            }
            if(mapTemp.containsKey(next.getUserName()+next.getDw())){
                ZSzpbGrCpcj zz = (ZSzpbGrCpcj) mapTemp.get(next.getUserName()+next.getDw());
                next.setId(zz.getId());
                next.setUserId(zz.getUserId());
                //数据重复，更新操作
                //update
                Map<String,Object> map = verifyWay(next,errorMsg,tempFlag);
                errorMsg =  map.get("errorMsg").toString();
                tempFlag = (boolean) map.get("tempFlag");
                if(tempFlag)
                {
                    errorFlag = true;
                    tempFlag = false;
                    //errorlist 添加操作
                    next.setId(nextId);
                    System.out.println("update_nextId:"+nextId);
                    next.setErrorMsg(errorMsg);
                    errorList.add(next);
                    errorMsg="";
                    continue;
                }else{
                    //验证通过
                    //save方法存库，当前一条数据
                    System.out.println("update:"+next.toString());
                    getDao().update(next);
                }
            }else{
                mapTemp.put(next.getUserName()+next.getDw(),next);
                //数据不重复，新增操作
                //验证开始
                //判断人员及所在部门是否属于此次评比部门中的人，不是，错误信息记录

                //验证得分是否正常数值，错误的都记录指errorList中
                //验证方法 ：民主测评成绩，定性评估成绩，综合评价成绩，推荐结果  验证
                Map<String,Object> map = verifyWay(next,errorMsg,tempFlag);
                errorMsg = (String) map.get("errorMsg");
                tempFlag = (boolean) map.get("tempFlag");
                if(tempFlag)
                {
                    errorFlag = true;
                    tempFlag = false;
                    //errorlist 添加操作
                    next.setErrorMsg(errorMsg);
                    System.out.println("add_nextId::"+nextId);
                    next.setId(nextId);
                    errorList.add(next);
                    errorMsg="";
                    continue;
                }else{
                    //验证通过
                    //save方法存库，当前一条数据
                    System.out.println("save:"+next.toString());
                    next.setId(null);
                    getDao().save(next);
                }
            }
        }

        List<CpcjExcelEntity> excelEntityList = new ArrayList<>();
        for (ZSzpbGrCpcj grCpcj : errorList){
            //excel生成实体
            CpcjExcelEntity excelEntity = new CpcjExcelEntity();
            excelEntity.setId(grCpcj.getId());
            excelEntity.setMzcpcj(grCpcj.getMzcpcj());
            excelEntity.setDw(grCpcj.getDw());
            excelEntity.setErrorMsg(grCpcj.getErrorMsg());
            excelEntity.setDxpgcj(grCpcj.getDxpgcj());
            excelEntity.setZhpjcj(grCpcj.getZhpjcj());
            excelEntity.setTjjg(grCpcj.getTjjg());
            excelEntity.setUserName(grCpcj.getUserName());
            excelEntityList.add(excelEntity);
        }

        String url = request.getSession().getServletContext().getRealPath("/download/szpb/"+IDUtil.randomUUID()+".xlsx");
        Map<String,Object> map = new HashMap<>();
        //将错误errorList数据放入excel的DOM返回
        //返回值，提示及EXCEL，errorFlag有错误时，提示导入成功，但又错误，下载excel。errorFlag无错误时，提示导入成功
        if(errorList!=null && errorList.size()>0){
            //excel对象操作
            String[] headers = {"序号", "用户名称","单位","定性评估成绩","民主测评成绩","综合评估成绩","推荐结果"};
            ImportExcelUtil.export(headers,excelEntityList,url);
            System.out.println("excel表格生成"+errorList.size()+"条数据!");
            String ExcelUrl = url.substring(url.indexOf("download"));
            System.out.println(ExcelUrl);
            map.put("url",ExcelUrl);
            return new ActionResult("加载成功", map);
        }

        map.put("scoer",excelEntityList);//成功导入失败excel数据
        return new ActionResult("加载成功", map);
    }