springboot项目使用jxls导出Excel报表

jxls导出Excel

---

设置Excel模板

• jx:area(lastCell=“B3”) : 模板作用区域 为A1到B3 (需在第一单元格内指出作用区域)

• jx:each(items=“user”,var=“u”,lastCell=“B3”) : user 是 Map 集合或 List 集合的键名或集合名

Maven依赖

 <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.8.1</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>2.8.1</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-jexcel</artifactId>
            <version>1.0.9</version>
        </dependency>

源代码 (省去实体类)

@Controller
@RequestMapping("/test")
@Slf4j
public class ExcelTest {

    public static String template = "/test1.xlsx" ;
    @RequestMapping("/export")
    public void execute() throws IOException {
        log.info("Opening input stream");
        OutputStream os = new FileOutputStream("E://Desktop/out.xlsx");//换成指定文件 出现错误    不换 下载下来是压缩包
        List<User> users = new ArrayList<>();
        User user1 = new User(1,"1","1");
        User user2 = new User(2,"1","1");
        User user3 = new User(3,"1","1");
        users.add(user1);
        users.add(user2);
        users.add(user3);
        template = ExcelTest.class.getResource(template).getPath();
        HashMap<String, Object> model = new HashMap<>();
        model.put("user", users);
        //template 为模板路径：eg:leadshow.xls，
        // JxlsExporter.class.getResourceAsStream(template)—> leadshow.xls模板路径需放置在该类包路径下
        String path = new File("").getAbsolutePath()+"/src/main/resources/test1.xlsx";
        try (InputStream is = new FileInputStream(path)) {
            //output 为导出的Excel路径，有一个坑需要注意的是，在springboot项目打包成jar包之后，
            //导出文件路径可这样表示： System.getProperty("user.dir") + "/export_leadshow.xls"，这种"static/export_leadshow.xls"路径会失效
                Context context = new PoiContext();
                context.putVar("user", users);
                JxlsHelper.getInstance().processTemplate(is, os, context);
        }
    }
}