POI的导入导出的使用

偏执的太偏执、 2023-09-29 18:18 55阅读 0赞

## POI的导入导出功能使用 ##

### poi是什么 ###

1.首先poi是一种导入导出技术，他是Apache提供给java程序对office文档进行读写功能。  
2.他内部提供了对文档进行读写的接口：常用的有HSSF，可以对2003及一下的版本的excel进行读写。  
XSSF可以对2007及以上的版本进行读写。

### poi在项目中如何用 ###

3.比如当时在做备案小类导入导出功能时，就用到了POI技术来做的。  
（1）首先，我们需要导入poi的jar包依赖在pom文件中，然后提供给用户响应的导入模板，用户只需要按照模板的格式输入即可，当然我们后端也会做一些验证，比如：版本是否正确，文本格式是否正确，模板是否正确，导入的字段是否为空，字段长度是否过长，数据是否合法，对数据做转换，数据库是否已经存在等等。验证通过后只需要批量入库即可。  
（2）在导出功能中就不需要逻辑判断了，我们只需要提供导出模板，可以去控制他的导出条数，从而去提升效率，最后对数据进行一个转换即可。这就是我对poi的理解。

### 实际用法 ###

1.首先在pom文件中导入poi的坐标

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

2.然后需要导入两个工具类，ExportUtil，和 POIClass

ExportUtil内部代码

package com.xyh.utils;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.core.io.ClassPathResource;
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipOutputStream;
    
    /**
     * excel导出数据
     * @author dk
     */
    @Slf4j
    public class ExportUtil {
        
    
    	private ExportUtil(){
        }
    	
    	/**
    	 * 封装返回的流信息
    	 * @param response
    	 * @param fileName
    	 * @throws Exception
    	 */
    	public static void toPackageOs(HttpServletResponse response , String fileName)throws Exception{
        
    		response.setContentType("application/octet-stream;charset=utf-8");  
    		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + ".xls");  
    	}
    
    	public static void toPackageOs(HttpServletResponse response , String fileName, String suffix)throws Exception{
        
    		response.setContentType("application/octet-stream;charset=utf-8");
    		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + suffix);
    	}
    
    	/**
    	 * 生成zip返回流头部信息
    	 * @param response
    	 * @param zipName
    	 * @throws Exception
    	 */
    	public static void toPackageZipOs(HttpServletResponse response , String zipName)throws Exception{
        
    		response.setContentType("APPLICATION/OCTET-STREAM");  
    	    response.setHeader("Content-Disposition","attachment; filename="+new String(zipName.getBytes(),"iso-8859-1"));
    	}
    	
    	/**
    	 * 生成模板输入流
    	 * @param temPath
    	 * @return
    	 * @throws Exception
    	 */
    	public static InputStream toPackageIn(String temPath)throws Exception{
        
    		//获取路径中的数据
    		return new ClassPathResource((temPath)).getInputStream();
    	}
    	
    	/**
    	 * 一次性导出全部数据
    	 * @param <T>
    	 * @param list
    	 * @param os
    	 */
    	/*public static <T> void exportExcel(List<T> list, OutputStream os ,
    			InputStream in)throws Exception{
    		long exportExcelBegin = System.currentTimeMillis();
    		log.warn("exportExcel begin: " + exportExcelBegin);
    		Context context = new Context();
    		context.putVar("list", list);
    		JxlsHelper.getInstance().processTemplateAtCell(in, os, context, "Result!A1");
            os.flush();
    		long exportExcelEnd = System.currentTimeMillis();
    		log.warn("exportExcel fininshed in: " + (exportExcelEnd - exportExcelBegin));
    	}*/
    	
    
    	
    	/**
    	 * 压缩制定目录下的文件, 生成并下载zip文件
    	 * @param srcFile 目标目录
    	 * @param zipPath 生成的zip文件的全路径
    	 * @param os 返回流  把zip流写到返回流中
    	 * @throws Exception
    	 */
    	public static void zipExcel(String srcFile , OutputStream os)throws Exception{
        
    		// 要被压缩的文件夹
            File file = new File(srcFile);
            InputStream input = null;
            ZipOutputStream zipOut = new ZipOutputStream(os);
            // zip的名称为
            zipOut.setComment(file.getName());
            if (file.isDirectory()) {
        
                File[] files = file.listFiles();
                for (int i = 0; i < files.length; ++i) {
        
                    input = new FileInputStream(files[i]);
                    zipOut.putNextEntry(new ZipEntry(file.getName() + File.separator + files[i].getName()));
                    int temp = 0;
                    while ((temp = input.read()) != -1) {
        
                        zipOut.write(temp);
                    }
                    input.close();
                } 
            }
            
            zipOut.close();
    	}
    	
    	/**
    	 * 删除文件夹下所有的文件
    	 * @param path
    	 * @return
    	 */
    	public static boolean delAllFile(String path) {
        
           boolean flag = false;
           File file = new File(path);
           if (!file.exists()) {
        
             return flag;
           }
           if (!file.isDirectory()) {
        
             return flag;
           }
           String[] tempList = file.list();
           File temp = null;
           for (int i = 0; i < tempList.length; i++) {
        
              if (path.endsWith(File.separator)) {
        
                 temp = new File(path + tempList[i]);
              } else {
        
                  temp = new File(path + File.separator + tempList[i]);
              }
              if (temp.isFile()) {
        
                 temp.delete();
              }
              if (temp.isDirectory()) {
        
                 delAllFile(path + "/" + tempList[i]);//先删除文件夹里面的文件
                 flag = true;
              }
           }
           return flag;
    	}
    	
    }

POIClass内部代码

package com.xyh.utils;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.springframework.core.io.ClassPathResource;
    import javax.servlet.http.HttpServletResponse;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class POIClass {
        
    	/**
    	 * 封装返回的流信息
    	 * @param response
    	 * @param fileName
    	 * @throws Exception
    	 */
    	public static void toPackageOs(HttpServletResponse response , String fileName)throws Exception{
        
    		//设置编码格式
    		response.setContentType("application/octet-stream;charset=utf-8");
    		//拼接名称加上日期 计划大类2019-09-26
    		String outFileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    		//给输出的文件设置名称
    		response.setHeader("Content-Disposition", "attachment;filename=" + new String(outFileName.getBytes(),"iso-8859-1") + ".xlsx");
    	}
    
    	/**
    	 * 生成模板输入流
    	 * @param temPath
    	 * @return
    	 * @throws Exception
    	 */
    	public static InputStream toPackageIn(String temPath)throws Exception{
        
    		return new ClassPathResource((temPath)).getInputStream();
    	}
    	// 给具体的某个行中的某个列赋值
    	public static void toCellValue(Row row, int cellColumn, String value) {
        
    		Cell cell = row.createCell(cellColumn);
    		cell.setCellValue(value);
    	}
    
    }

3.对于controller层-导入，这里接收要用MultipartFile 对文件进行接收

//  新增导入功能
        @PostMapping("/importFile")
        public ResultVo importFile(MultipartFile file)throws Exception{
        
            return partFzService.importFile(file);
        }

对于service层

//    导入
        @Override
        public ResultVo importFile(MultipartFile file) throws Exception {
        
            //        判断版本号是否正确
            InputStream is = file.getInputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            if(POIFSFileSystem.hasPOIFSHeader(bis)) {
        
                return ResultPlsVo.error("请使用2007及以上的excel版本！");
            }
    
            //使用poi读取里面的内容
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
    //获取单元格中的信息 at0是获取sheet1中的数据。
            XSSFSheet sheet = workbook.getSheetAt(0);
    //        创建集合存放数据
            List<SubClassAdd> subClassAddVoList = new ArrayList<>();
    
            //        判断模板是否正确
            String moban = sheet.getRow(0).getCell(0).getStringCellValue();
            if (!moban.equals("备件数量阀值表")){
        
                return ResultPlsVo.error("模板不是备件数量阀值表！请检查模板！");
            }
    
    //       保证内部的格式
            ResultVo<List<PartAdd>> resultVo = readData(sheet);
            if (!resultVo.getSuccess()){
        
                return resultVo;
            }
    
    //        5.字段长度的问题：内容，
    //        6.数值转换的问题：内容，
            List<PartAdd> partAddList = resultVo.getData();
            ResultVo<List<PartAdd>>  resultVo1 = validata1(partAddList);
            if (!resultVo1.getSuccess()){
        
                return resultVo1;
            }
    
    //        4.重复的问题：内容，
    //        7.是否数据库已经存在的问题：内容，
            List<PartAdd>  partAddList2= resultVo1.getData();
            ResultVo resultVo2 = validata2(partAddList2);
            if (resultVo2.getSuccess()==false){
        
                return ResultPlsVo.error(resultVo2);
            }
    
    //        7.是否数据库已经存在的问题：内容
            List<Part> partList=partFzDao.findAll();
            ResultVo resultVo3 = validata3(partList,partAddList2);
            if (resultVo3.getSuccess()==false){
        
                return ResultPlsVo.error("数据库存在重复数据,备件编码为："+resultVo3.getData());
            }else{
        
    //            批量入库
                partFzDao.inserts(partAddList2);
            }
            return resultVo;
        }

4.对于controller层–导出

//    导出功能--poi实现--没有数据形式
        @GetMapping("/exportFile")
        public ResultVo exportFile(String legalPlantName,String partCode,HttpServletResponse response)throws Exception{
        
    
            PartPage partPage=new PartPage();
            partPage.setLegalId(1);
            partPage.setPartCode("BHUA1");
            return  partFzService.exportFile(partPage,response);
        }

service层

//    导出功能
        @Override
        public ResultVo exportFile(PartPage partPage,HttpServletResponse response) throws Exception {
        
            //根据条件查询出数据
            List<Part> subClassVoList = partFzDao.pageQuery(partPage);
    //        for (Part ss:subClassVoList) {
        
    //            System.out.println(ss);
    //        }
            if (subClassVoList.size()>=1000){
        
                return ResultPlsVo.error("导出数据过大，请缩短时间范围，在进行导出！");
            }
    //
    //        //获取到excel模板
    //        //向模板中写数据
    //        //输出给浏览器
            for (Part part :subClassVoList){
        
                Boolean status = part.getPartStatus();//状态
                Boolean borrow= part.getBorrow();//是否可借用
                Boolean market= part.getMarket();//是否可销售
                String statusName = status?"起用":"禁用";
                String borrowName = borrow?"可借用":"不可借用";
                String marketName = market?"可销售":"不可销售";
                part.setPartStatusName(statusName);
                part.setBorrowName(borrowName);
                part.setMarketName(marketName);
            }
    //
    
    //        //响应给浏览器的箱子
            ServletOutputStream out = response.getOutputStream();//获取浏览器输出流
            //给输出文件设置名称
            com.jz.util.POIClass.toPackageOs(response, "备件数量阀值导出");
            //读取模板中的数据
            InputStream in = com.jz.util.ExportUtil.toPackageIn("templates/备件数量阀值.xlsx");
            //把数据写入到模板   in 写到哪个模板  subClassVoList写入的数据  out最终输出给浏览器的内容
            writeDataToExcel(in, "Sheet1", subClassVoList, out);
    //
            if (in != null) {
        
                in.close();
                out.close();
            }
            return ResultPlsVo.success();
        }

对于内部抽离的两个发放

// 由于此方法不能通用, 所以单独写在这里
        private void writeDataToExcel(InputStream in, String sheetName,
                                      List<Part> resultList, ServletOutputStream out) throws Exception {
        
            //POi读取模板
            XSSFWorkbook wb = new XSSFWorkbook(in);
            //读取sheet1中的数据
            Sheet sheet = wb.getSheet(sheetName);
            if (sheet != null) {
        
                //向sheet1中赋值，设置样式
                toResultListValueInfo(sheet, resultList);
            }
            //把数据写入到输出流中
            wb.write(out);
            //关闭poi方法
            wb.close();
        }
    
        private void toResultListValueInfo(Sheet sheet, List<Part> plantList) {
        
            //从第4行开始赋值
            int row_column = 4;
            int xuhao =1;
            //遍历数据集合
            for (Part obj : plantList) {
        
                //创建一行的方法
                Row row = sheet.createRow(row_column);
                // 给第一列序号赋值赋值
                POIClass.toCellValue(row,0, xuhao + "");
                // 给第二列备件编码赋值
                POIClass.toCellValue(row, 1, obj.getPartCode() + "");
                // 给第3列备件名称
                POIClass.toCellValue(row, 2, obj.getPartName() + "");
                // 给4列计量单位
                POIClass.toCellValue(row, 3, obj.getMeasure() + "");
                //给5列备件小类
                POIClass.toCellValue(row, 4, obj.getName() + "");
    //            给6列备件数量阀值
                POIClass.toCellValue(row, 5, obj.getPartFazhi() + "");
                //            给7列状态
                POIClass.toCellValue(row, 6, obj.getPartStatusName() + "");
                //            给8列状态
                POIClass.toCellValue(row, 7, obj.getBorrowName() + "");
                //            给9列状态
                POIClass.toCellValue(row, 8, obj.getMarketName() + "");
                //            给10列状态
                POIClass.toCellValue(row, 9, obj.getPartRemark() + "");
                //            给11列状态
                POIClass.toCellValue(row, 10, obj.getLegalPlantName() + "");
                //            给12列状态
                POIClass.toCellValue(row, 11, obj.getCreateName() + "");
                //            给13列状态
                POIClass.toCellValue(row, 12, obj.getCreateTime() + "");
                //            给14列状态
                POIClass.toCellValue(row, 13, obj.getUpdateName() + "");
                //            给15列状态
                POIClass.toCellValue(row, 13, obj.getUpdateTime() + "");
    
                row_column++;
                xuhao++;
            }
        }
    
    
        // 7.是否数据库已经存在的问题：内容
        private ResultVo validata3(List<Part> partList, List<PartAdd> partAddList2){
        
            //
            List<String> partList1 = new ArrayList<>();//数据库code
            List<String> partAddList1 = new ArrayList<>();//文件中的code
            for (Part pp1:partList) {
        
                partList1.add(pp1.getPartCode());
            }
            for (PartAdd pp2:partAddList2) {
        
                partAddList1.add(pp2.getPartCode());
            }
    
            //专门存放重复的数据
            List<String> repeat =new ArrayList<>();
            //存放全部的数据
            List<String> names = new ArrayList<>();
            //遍历全部的名称
            for (String name:partAddList1){
        
                if (partList1.contains(name)){
        
                    repeat.add(name);
                }else {
        
                    names.add(name);
                }
            }
    
            if (repeat.size()>0){
        
                return ResultPlsVo.error(repeat);
            }
            return ResultPlsVo.success(names);
        }
    
        private ResultVo validata2(List<PartAdd> partAddList){
        
            //        4.重复的问题：内容，
            List<String> codeList = new ArrayList<>();//存放备件编码
            for (PartAdd pp:partAddList){
        
                codeList.add(pp.getPartCode());
            }
    
            //专门存放重复的数据
            List<String> repeat =new ArrayList<>();
            //存放全部的数据
            List<String> names = new ArrayList<>();
            //遍历全部的名称
            for (String name:codeList){
        
                if (names.contains(name)){
        
                    repeat.add(name);
                }else {
        
                    names.add(name);
                }
            }
    
            if (!CollectionUtils.isEmpty(repeat)){
        
                return ResultPlsVo.error("存在重复的名称，重复的名称是："+repeat.toString());
            }
    
            return ResultPlsVo.success(partAddList);
        }
    
        private ResultVo validata1(List<PartAdd> partAddList){
        
            //        5.字段长度的问题：内容，
            //        6.数值转换的问题：内容，
            List<String> message2=new ArrayList<>();//存放错误信息
            for (PartAdd pas:partAddList){
        
    
                if (pas.getLegalName().length()>50){
        
                    message2.add("序号"+pas.getXuhao()+"行法人名称过长");
                }else {
        
    //                数值转换
                    Integer legalId=legalZH(pas.getLegalName());
                    if (legalId!=null){
        
                        pas.setLegalId(legalId);
                    }else{
        
                        message2.add("序号"+pas.getXuhao()+"行法人名称在法人表不存在");
                    }
                }
    
    
    //            备件编码
                if (pas.getPartCode().length()>4){
        
                    message2.add("序号"+pas.getXuhao()+"行备件编码过长");
                }
    
    //            备注
                if (pas.getPartRemark().length()>50){
        
                    message2.add("序号"+pas.getXuhao()+"行备注过长");
                }
            }
    
            if (message2.size()>0){
        
                return ResultPlsVo.error(message2);
            }
            return ResultPlsVo.success(partAddList);
        }
    
    
    
    
    
    //    法人名称转对应的id
        public Integer legalZH(String legalName){
        
    //        从法人表查询id
            Integer num=partFzDao.findById(legalName);
            return num;
        }
    
    
        //        保证内部格式方法
        //        3.某些字段必填：内容，是否必填
       private ResultVo readData(XSSFSheet sheet){
        
                List<PartAdd> partAdds = new ArrayList<>();
           List<String> message2=new ArrayList<>();//存放错误信息
                for(int i = 4;i<=sheet.getLastRowNum();i++){
        
                    try {
        
     //                判读xuhao是否为null
                        XSSFCell xuhao2 = sheet.getRow(i).getCell(0);
                        String xuhao =null;
                        if (xuhao2==null){
        
                            message2.add("序号"+xuhao2+"行序号为空");
                        }else{
        
                            xuhao = sheet.getRow(i).getCell(0).getStringCellValue();
                        }
    
    //                    法人名称判读
                        XSSFCell legalName2 = sheet.getRow(i).getCell(1);
                        String legalName =null;
                        if (legalName2==null){
        
                            message2.add("序号"+xuhao2+"行法人名称为空");
                        }else{
        
                            legalName = sheet.getRow(i).getCell(1).getStringCellValue();
                        }
    
    
    //                    备件编码
                        XSSFCell partCode2 = sheet.getRow(i).getCell(2);
                        String partCode =null;
                        if (partCode2==null){
        
                            message2.add("序号"+xuhao2+"行备件编码为空");
                        }else{
        
                            partCode = sheet.getRow(i).getCell(2).getStringCellValue();
                        }
    
    //                   备件名称
                        XSSFCell partName2 = sheet.getRow(i).getCell(3);
                        String partName =null;
                        if (partName2==null){
        
                            message2.add("序号"+xuhao2+"行备件名称为空");
                        }else{
        
                            partName = sheet.getRow(i).getCell(3).getStringCellValue();
                        }
    
    
    //                    阀值
                        XSSFCell partFazhi2 = sheet.getRow(i).getCell(4);
                        String partFazhi =null;
                        if (partFazhi2==null){
        
                            message2.add("序号"+xuhao2+"阀值为空");
                        }else{
        
                            partFazhi = sheet.getRow(i).getCell(4).getStringCellValue();
                        }
    
    //                    备注
                        XSSFCell remark2 = sheet.getRow(i).getCell(5);
                        String remark =null;
                        if (remark2!=null){
        
                            remark = sheet.getRow(i).getCell(5).getStringCellValue();
                        }
    
    //                    添加操作
                        PartAdd partAdd = new PartAdd();
                        Date date=new Date();//当前时间
                        partAdd.setCreateTime(date);
                        partAdd.setCreateName("天理");
                        partAdd.setUpdateTime(date);
                        partAdd.setUpdateName("天理");
    
                        partAdd.setLegalName(legalName);
                        partAdd.setPartCode(partCode);
                        partAdd.setPartFazhi(Integer.valueOf(partFazhi));
                        partAdd.setPartRemark(remark);
                        partAdd.setXuhao(xuhao);
                        partAdds.add(partAdd);
                    }catch (Exception e){
        
                        return ResultPlsVo.error("格式必须是文本格式！");
                    }
                }
                if (message2.size()>0){
        
                    return ResultPlsVo.success(message2);
                }
                return ResultPlsVo.success(partAdds);
        }