在项目的resource目录下新建template文件夹用于存放Excel模板

image.png

获取Excel文件并添加数据等操作

ClassPathResource cpr = new ClassPathResource("template/文件名.xls");
        InputStream is = cpr.getInputStream();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        for (int i = 0; i < 10; i++) {
              HSSFRow nextRow = sheet.createRow(row);
              nextRow.createCell(0).setCellValue("姓名");
              nextRow.createCell(1).setCellValue("年龄");
              nextRow.createCell(2).setCellValue("性别");
        }
        String fileName = "文件名";
        response.reset();
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        response.setContentType("application/xls;charset=UTF-8");
        OutputStream os = new BufferedOutputStream(response.getOutputStream());
        hssfWorkbook.write(os);
        os.flush();
        if (os != null) {
            os.close();
        }
        if (hssfWorkbook != null) {
            hssfWorkbook.close();
        }

如果报获取不到文件异常，pom.xml文件中的bulid标签下添加插件解决读取Excel文件异常问题

<build>
        <plugins>
            <!--解决读取EXCEL失败问题-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                        <nonFilteredFileExtension>xls</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>
        </plugins>
    </build>