JAVA导入导出Excel(poi示例)

最新推荐文章于 2025-05-18 16:20:42 发布

原创最新推荐文章于 2025-05-18 16:20:42 发布 · 950 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#JAVA导出Excel #poi示例 #JAVA导入Excel

Java 最佳实践手册专栏收录该内容

27 篇文章

订阅专栏

本文介绍如何使用Java的Apache POI库实现Excel文件的数据导出与导入功能。文章详细展示了创建Excel表格、填充数据及从Excel读取数据的具体步骤，并提供了完整的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pom.xml

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6.1</version>
        </dependency>

导出Excel

public void export(List<Demo> query,String sheetName,HttpServletResponse response){
        //导出数据为空,直接返回
        if (query.size() == 0) {
            return;
        }
        //获取导出数据的总条数
        int countColumnNum = query.size();
        //创建XSSFWorkbook文件对象
        XSSFWorkbook book = null;

        book = new XSSFWorkbook();
        //创建一个Name的新
        XSSFSheet sheet = book.createSheet(sheetName);
        // 获取表的第一行
        XSSFRow firstRow = sheet.createRow(0);
        //创建表的第一行的每列的说明
  	String[] options = {"编号","姓名"};
        XSSFCell[] firstCells = new XSSFCell[options.length];
        //给表的第一行的每一列赋值
        for (int j = 0; j < options.length; j++) {
            firstCells[j] = firstRow.createCell(j);
            firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
        }
        //把表的第一列写好后,接下来从表的第二列开始把对应的值写入到文件里
        for (int i = 0; i < countColumnNum; i++) {
            //给execl创建一行
            XSSFRow row = sheet.createRow(i + 1);
            //获取集合对象
            Demo result = query.get(i);
            String depart = "";
            //循环给列赋值
            for (int column = 0; column < options.length; column++) {
                //确认每一列对应的表的列
                XSSFCell userId = row.createCell(0);
                XSSFCell zhName = row.createCell(1);
                //给对应列赋值
                userId.setCellValue(result.getUserId());
                zhName.setCellValue(result.getZhName());
            }
        }
        //写一个try catch捕捉异常(response获取输出流)
        OutputStream os = null;
        try {
	    //处理下载文件名乱码	
            String filename= new String("人员信息".getBytes("utf-8"), "ISO_8859_1");
            response.setHeader("Content-Disposition", "attachment;filename="+filename+".xlsx");
            response.setContentType("application");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            os = response.getOutputStream();
            book.write(os);
        } catch (IOException e) {
            log.info("IO流异常");
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                log.info("关闭IO流异常");
            }
        }
    }

poi导入

@PostMapping("/excel/read")
    public ResultVO<List<ExcelVO>> readExcel(MultipartFile file) {
        List<ExcelVO> list = new ArrayList<ExcelVO>();
        try {
            //开始读取excel
            InputStream is = file.getInputStream();
            Workbook hssfWorkbook = null;
            if (file.getOriginalFilename().endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
            } else if (file.getOriginalFilename().endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
            }
            ExcelVO patch = null;
            PatchExcelResultVO patchExcelResult = null;
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                // 循环行Row
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        Cell userId = hssfRow.getCell(0);
                        Cell zhName = hssfRow.getCell(1);  

                        String staffIdNoValue = formatStaffIdCell(staffIdNo);
                        swipeDate = formatCell(swipeDate,"yyyy-MM-dd");
                        list.add(new ExcelVO(userId,zhName));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ResultVO(GlobalReturnCode.SUCCESS_CODE,"SUCCESS",list);
    }