java导出数据到excel模板_springboot+jxls 根据Excel模板 填写数据并导出

项目结构

pom.xml

                net.sf.jxls            jxls-core            1.0.6compile

学生信息表模板：

ExcelUtiles

package cn.bdqn.utils;import net.sf.jxls.transformer.XLSTransformer;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.util.ResourceUtils;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.util.Map;/** * @ProjectName: Student * @Author: huat * @Date: 2020/5/7 8:53 * @Version: 1.0 */public class ExcelUtiles {    /**     * 输出表格     * @param map 表格中数据     * @param response 响应     * @param excelName 表格名称     * @param excelPath 表格模板保存的路径     */    public static void outExcel(Map map,HttpServletResponse response,String excelName,String excelPath){        File file=null;        try {            file= ResourceUtils.getFile(excelPath);        } catch (FileNotFoundException e) {            e.printStackTrace();        }        //配置下载路径        String path = "/download/";        createDir(new File(path));        //根据模板生成新的excel        File excelFile = createNewFile(map, file, path,excelName);        //浏览器端下载文件        try {            downloadFile(response, excelFile,excelName);        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }        //删除服务器生成文件        deleteFile(excelFile);    }    /**     * 根据excel模板生成新的excel     * @param beans 表格中的数据     * @param file 文件     * @param path 生成文件的位置     * @param excelName 文件名称     * @return     */    private static File createNewFile(Map beans, File file, String path,String excelName) {        XLSTransformer transformer = new XLSTransformer();        File newFile = new File(path + excelName+".xlsx");        try (InputStream in = new BufferedInputStream(new FileInputStream(file));             OutputStream out = new FileOutputStream(newFile)) {            Workbook workbook = transformer.transformXLS(in, beans);            workbook.write(out);            out.flush();            return newFile;        } catch (Exception e) {            System.out.println(e.getMessage());        }        return newFile;    }    /**     * 将服务器新生成的excel从浏览器下载     * @param response 响应     * @param excelFile 表格文件     * @param excelName 表格名称     * @throws UnsupportedEncodingException     */    private static void downloadFile(HttpServletResponse response, File excelFile,String excelName) throws UnsupportedEncodingException {        /* 设置文件头：最后一个参数是设置下载文件名 */        response.setHeader("Content-type","application/vnd.ms-excel");        // 解决导出文件名中文乱码        response.setCharacterEncoding("UTF-8");        response.setHeader("Content-Disposition","attachment;filename="+new String(excelName.getBytes("UTF-8"),"ISO-8859-1")+".xlsx");        try (                InputStream ins = new FileInputStream(excelFile);                OutputStream os = response.getOutputStream()        ) {            byte[] b = new byte[1024];            int len;            while ((len = ins.read(b)) > 0) {                os.write(b, 0, len);            }        } catch (IOException ioe) {            ioe.printStackTrace();        }    }    /**     * 浏览器下载完成之后删除服务器生成的文件     * 也可以设置定时任务去删除服务器文件     *     * @param excelFile     */    private static void deleteFile(File excelFile) {        excelFile.delete();    }    //如果目录不存在创建目录 存在则不创建    private static void createDir(File file) {        if (!file.exists()) {            file.mkdirs();        }    }}

Controller

 @RequestMapping("downExcel")    public void downExecl(HttpServletResponse response){        Map map=new HashMap();        map.put("name","学生信息表");       //获取学生信息        map.put("studentList",studentService.getStudent());       //"classpath:static/excel/学生表格.xlsx"   表格模板保存路径，classpath:代表resources路径        ExcelUtiles.outExcel(map,response,"学生信息表","classpath:static/excel/学生表格.xlsx");    }

原文：https://my.oschina.net/u/3535099/blog/4268971

作者：冥焱