poi实现导入和导出功能

忘是亡心i 2022-06-02 05:57 308阅读 0赞

利用poi可以实现excel文件导入和导出功能   本例子结合了springboot做demo

pom文件

<properties>
        <java.version>1.8</java.version>
    
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>1.5.4.RELEASE</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
            <version>1.5.4.RELEASE</version>
        </dependency>
        
        <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>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.16</version>
        </dependency>
    
    
    
    </dependencies>

一个demo的controller类

@RestController
    public class DemoController {
        private final Logger LOGGER = LoggerFactory.getLogger(DemoController.class);
    
        @RequestMapping("upload")
        public Object upload(@PathVariable("file") MultipartFile file) throws Exception {
            if (file == null || file.getInputStream() == null) {
                LOGGER.error("文件上传失败");
                return "文件上传失败";
            }
    
            if (file.getSize() > 5 * 1024 * 1024) {
                LOGGER.error("文件太大，上传失败");
                return "文件太大，上传失败";
            }
            FileTypeUtil.FileType fileType = FileTypeUtil.getFileType(file.getInputStream());
            //XLS_DOC --》2007之前的excel类型  XLSX_DOCX--》  2007之后的excel类型
            if (fileType == FileTypeUtil.FileType.XLS_DOC) {
                LOGGER.info("开始解析XLS_DOC文件");
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(file.getInputStream());
                List<User> getData = readOldExcel(hssfWorkbook);
                if (getData == null) {
                    LOGGER.error("解析失败...");
                    return "文件数据解析失败";
                }
                LOGGER.info("数据解析成功");
                file.getInputStream().close();
                return getData;
            } else if (fileType == FileTypeUtil.FileType.XLSX_DOCX) {
                LOGGER.info("开始解析XLSX_DOCX文件");
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
                List<User> getData = readExcel(xssfWorkbook);
                if (getData == null) {
                    LOGGER.error("解析失败...");
                    return "文件数据解析失败";
                }
                LOGGER.info("数据解析成功");
                file.getInputStream().close();
                return getData;
            } else {
                LOGGER.error("上传文件类型不正确");
                return "文件类型不正确";
            }
    
        }
    
        @RequestMapping("download")
        public void  download(HttpServletResponse response) throws Exception {
            //获取模板的输入流
            InputStream inputStream = UploadController.class.getClassLoader().getResourceAsStream("templates" + File.separator + "xx.xlsx");
            if (inputStream == null) {
                LOGGER.error("模板文件的路径不正确");
    
            }
            //测试数据
            List<User> users = new ArrayList<User>();
            for (int i = 0; i < 10; i++) {
                User user = new User();
                user.setId(i + 1);
                user.setUsername("张三" + i + 1);
                user.setPassword("123" + i + 1);
                users.add(user);
            }
            //获取模板的工作薄
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheetAt = workbook.getSheetAt(0);
            XSSFRow row = sheetAt.getRow(0);
            short rowHeight = row.getHeight();
            XSSFRow row1 = null;
            XSSFCell cell = null;
            for (int j = 0; j < users.size(); j++) {
                User user = users.get(j);
                row1 = sheetAt.createRow(j + 1);
                row1.setHeight(rowHeight);
                cell = row1.createCell(0);
                cell.setCellValue(user.getId());
    
                cell = row1.createCell(1);
                cell.setCellValue(user.getUsername());
    
                cell = row1.createCell(2);
                cell.setCellValue(user.getPassword());
            }
            String filename = new String("文件".getBytes("UTF-8"),"ISO8859-1");
            LOGGER.info("文件名"+filename);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", "attachment;filename="+filename+".xlsx");
            workbook.write(response.getOutputStream());
            LOGGER.info("文件下载成功！！");
            response.getOutputStream().flush();
            response.getOutputStream().close();
            workbook.close();
    
    
        }
    
        //处理2007之前的excel
        private List<User> readOldExcel(HSSFWorkbook hssfWorkbook) {
            List<User> users = new ArrayList<User>();
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
            HSSFCell cell = null;
            HSSFRow row = null;
            for (int i = sheetAt.getFirstRowNum(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
                row = sheetAt.getRow(i);
                if (row == null) {
                    LOGGER.warn("获取到一个空行-------》》》》》》" + i + "行");
                    continue;
                }
                Object[] objects = new Object[row.getLastCellNum()];
                for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            objects[j] = cell.getStringCellValue();
                            System.out.println(cell.getStringCellValue());
                            break;
                        case _NONE:
                            objects[j] = "";
                            break;
                        case BOOLEAN:
                            objects[j] = cell.getBooleanCellValue();
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case NUMERIC:
                            //处理double类型的  1.0===》1
                            DecimalFormat df = new DecimalFormat("0");
                            String s = df.format(cell.getNumericCellValue());
                            objects[j] = s;
                            System.out.println(s);
                            break;
                        default:
                            objects[j] = cell.toString();
                    }
                }
                //处理数据
                if (objects != null) {
                    User user = new User();
                    user.setId(Integer.parseInt(objects[0].toString()));
                    user.setUsername((String) objects[1]);
                    user.setPassword(objects[2].toString());
                    users.add(user);
                }
            }
            return users;
        }
    
    
        //处理2007之后的excel
        private List<User> readExcel(XSSFWorkbook xssfWorkbook) {
            List<User> users = new ArrayList<User>();
            //获得excel第一个工作薄
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            //行
            XSSFRow row = null;
            //列
            XSSFCell cell = null;
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行
                row = sheet.getRow(i);
                //判断是否出现空行
                if (row == null) {
                    LOGGER.warn("获取到一个空行-------》》》》》》" + i + "行");
                    continue;
                }
                Object[] objects = new Object[row.getLastCellNum()];
                for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
    
                    if (cell == null) {
                        LOGGER.warn("获取到一个空列------------》》》》》》" + j + "列");
                        continue;
                    }
                    //第一行数据
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            objects[j] = cell.getStringCellValue();
                            System.out.println(cell.getStringCellValue());
                            break;
                        case _NONE:
                            objects[j] = "";
                            break;
                        case BOOLEAN:
                            objects[j] = cell.getBooleanCellValue();
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case NUMERIC:
                            //处理double类型的  1.0===》1
                            DecimalFormat df = new DecimalFormat("0");
                            String s = df.format(cell.getNumericCellValue());
                            objects[j] = s;
                            System.out.println(s);
                            break;
                        default:
                            objects[j] = cell.toString();
                    }
    
                }
                //处理数据
                if (objects != null) {
                    User user = new User();
                    user.setId(Integer.parseInt(objects[0].toString()));
                    user.setUsername((String) objects[1]);
                    user.setPassword(objects[2].toString());
                    users.add(user);
                }
    
            }
            return users;
        }
    
        //处理excel中的图片 2007之后  ---》获取   String里面保存着  这个图片的坐标
        private Map<String, PictureData> getExcelPic(XSSFWorkbook xssfWorkbook, XSSFSheet xssfSheet) {
            Map<String, PictureData> map = new HashMap<String, PictureData>();
            for (POIXMLDocumentPart poixmlDocument : xssfWorkbook.getRelations()) {
                if (poixmlDocument instanceof XSSFDrawing) {
                    //拿到 drawing
                    XSSFDrawing xssfDrawing = (XSSFDrawing) poixmlDocument;
                    //拿到所有的图形
                    List<XSSFShape> shapes = xssfDrawing.getShapes();
                    for (XSSFShape xssfShape : shapes) {
                        if (xssfShape instanceof XSSFPicture) {
                            //拿到picture
                            XSSFPicture xssfPicture = (XSSFPicture) xssfShape;
                            //拿到anchor  这里存在 图片的坐标
                            XSSFClientAnchor size = xssfPicture.getPreferredSize();
                            int row1 = size.getRow1();
                            short col1 = size.getCol1();
                            //自定义一个坐标
                            String xy = String.valueOf(row1) + "_" + String.valueOf(col1);
                            map.put(xy, (PictureData) xssfPicture.getPictureData());
                        }
                    }
                }
            }
            return map;
        }
    
        //处理excel中的图片 2007之前
        private Map<String, PictureData> getOldExcelPic(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet) {
            Map<String, PictureData> map = new HashMap<String, PictureData>();
            //需要先获取 所有的图片  通过 workbook
            List<HSSFPictureData> pictures = hssfWorkbook.getAllPictures();
            if (pictures.size() > 0) {
                //通过sheet 获得sheet里面所有的 shape
                List<HSSFShape> children = hssfSheet.getDrawingPatriarch().getChildren();
                for (HSSFShape hssfShape : children) {
                    //通过shape 获得anchor  来获取图片坐标
                    HSSFAnchor anchor = hssfShape.getAnchor();
                    if (hssfShape instanceof HSSFPicture) {
                        //获得picture
                        HSSFPicture hssfPicture = (HSSFPicture) hssfShape;
                        //2007之前的需要通过   picture的索引 ----》  在全局的图片信息里面获取图片数据
                        int i = hssfPicture.getPictureIndex() - 1;
                        //获得图片数据
                        HSSFPictureData hssfPictureData = pictures.get(i);
                        //拿到图片的坐标
                        String xy = anchor.getDx1() + "_" + anchor.getDy1();
                        map.put(xy, (PictureData) hssfPictureData);
                    }
                }
                return map;
            } else {
                return null;
            }
        }
    }