Java操作Excel实现批量添加

1、引入依赖包

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

2、Java语句

		try {
			File file = new File("D:\\zhiwu.xlsx");
			FileInputStream fileInput = null;//创建文件输入流
			fileInput = new FileInputStream(file);
			XSSFWorkbook wb = new XSSFWorkbook(fileInput);//由输入流文件得到工作簿对象
			XSSFSheet sheet = wb.getSheetAt(0);//获取第一个sheet
			int lastRowNum = sheet.getLastRowNum(); //获取表格内容的最后一行的行数
			String family = null;
			String muId = null;
			String gangid = null;
			String menid = null;
			String jieid = null;
			for (int i = 0; i < lastRowNum; i++) {
				XSSFRow row = sheet.getRow(i);//获取每一行
				String cell = row.getCell(0).getStringCellValue();
				String cell1 = row.getCell(1).getStringCellValue();
				String cell2 = row.getCell(2).getStringCellValue();
				if(cell.substring(cell.length()-1).equals("科")){
					QueryWrapper<Taxonomy> queryWrapper = new QueryWrapper<>();
					queryWrapper.like("name", cell);
					List<Taxonomy> taxonomies = taxonomyService.list(queryWrapper);
					if (!taxonomies.isEmpty()) {
						//科
						family = taxonomies.get(0).getId();
						//目
						Taxonomy taxonomy = taxonomyService.queryTaxonomyById(family);
						muId = taxonomy.getParentId();
						//纲
						Taxonomy taxonomy1 = taxonomyService.queryTaxonomyById(muId);
						gangid = taxonomy1.getParentId();
						//门
						Taxonomy taxonomy2 = taxonomyService.queryTaxonomyById(gangid);
						menid = taxonomy2.getParentId();
						//界
						Taxonomy taxonomy3 = taxonomyService.queryTaxonomyById(menid);
						jieid = taxonomy3.getParentId();
					}else{
						Taxonomy taxonomy = new Taxonomy();
						taxonomy.setId(UUID.randomUUID().toString());
						taxonomy.setName(cell);
						taxonomy.setRank("科");
						taxonomy.setParentId("999");
						taxonomy.setIsParent(Boolean.valueOf("1"));
						taxonomy.setDeleteFlag("N");
						service.save(model);
					}
				}else{
					for(int j = 0;j < cell.length();j++){
						QueryWrapper<Taxonomy> queryWrapper1 = new QueryWrapper<>();
						queryWrapper1.like("name", cell);
						List<Taxonomy> taxonomies1 = taxonomyService.list(queryWrapper1);
						if (taxonomies1.isEmpty()) {
							model.setViewer((long) 2);
							model.setName(cell);
							model.setLatinName(cell1);
							if (cell2.contains("省级")) {
								model.setShanxiConservation("1");
							} else if (cell2.contains("国家II级")) {
								model.setNationConservation("Ⅱ级");
							}
							model.setKingdom(jieid);
							model.setPhylum(menid);
							model.setClasss(gangid);
							model.setOrders(muId);
							model.setFamily(family);
							model.setGuid(UUID.randomUUID().toString());
							addSpecies(model);
						}
					}
				}
			}
			wb.close();
			fileInput.close();
		} catch (Exception e) {
			e.printStackTrace();
		}