java读取本地excel

six boy

于 2022-02-09 14:08:51 发布

阅读量3.7k

点赞数

文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/weixin_43829933/article/details/122840655

版权

读取本地

File f = new File("C:\\Users\\tscon\\Documents\\WeChat Files\\wxid_46q1tzjxubor12\\FileStorage\\File\\2022-01\\zl.xls");
	    	Workbook wb = readExcel(new FileInputStream(f),f.getName());
	    	Row row = null;
	    	if(wb != null){
	            //获取第一个sheet
	            Sheet sheet = wb.getSheetAt(0);
	            //获取最大行数
	            int rownum = sheet.getPhysicalNumberOfRows();
	            //获取第一行
	            row = sheet.getRow(0);
	            //获取最大列数
	            for (int i = 1; i<rownum; i++) {
	                row = sheet.getRow(i);

	                String line1 = row.getCell(0).toString();
	                Double x = Double.valueOf(row.getCell(1).toString());
	                Double y = Double.valueOf(row.getCell(2).toString());

	            }
	        }
//这个下面是单独函数
public static Workbook readExcel(InputStream is,String extString){
	        Workbook wb = null;
	        extString = extString.substring(extString.lastIndexOf("."));
	        try {
	            if(".xls".equals(extString)){
	                return wb = new HSSFWorkbook(is);
	            }else if(".xlsx".equals(extString)){
	                return wb = new XSSFWorkbook(is);
	            }else {
	                return wb = null;
	            }
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        return wb;
	    }

读取上传文件

 @RequestMapping(value = "/upload", method = RequestMethod.POST)
    public ResultMsg upload(@RequestParam("multipartfiles") MultipartFile[] multipartfiles) throws IOException {
        for(MultipartFile file : multipartfiles) {
            Row row = null;
            List<Map<String,String>> list = null;
            String cellData = null;
            String columns[] = {"pay_proof","c_name","party_b","beginTime","endTime","total_price","pay_total"};
            Workbook wb = readExcel(file.getInputStream(),file.getOriginalFilename());
            //Integer contract_id = 0;
            if(wb != null){
                //用来存放表中数据
                list = new ArrayList<Map<String,String>>();
                //获取第一个sheet
                Sheet sheet = wb.getSheetAt(0);
                //获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                //获取第一行
                row = sheet.getRow(0);
                //获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                for (int i = 2; i<rownum; i++) {
                    Map<String,String> map = new LinkedHashMap<String,String>();
                    row = sheet.getRow(i);
                    if(row !=null){
                        for (int j=0;j<colnum;j++){
                            cellData = (String) getCellFormatValue(row.getCell(j));
                            map.put(columns[j], cellData);
                        }
                    }else{
                        break;
                    }
                    list.add(map);
                }
            }
            //遍历解析出来的list
        }
        return ResultMsg.success(0);
    }

public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    String a = cellValue.toString();
                    int b = a.indexOf(".");
                    if(b>0){
                        if(a.substring(a.length()-1,a.length()).equals("0")){
                            cellValue = a.substring(0,b);
                        }
                    }
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }