excel导出

目录

一、准备

1.Maven项目先引进poi依赖，然后进行代码编写

2.模板

二、导出步骤

1、获取当地模板

2、Map格式导出

 3、List方式导出数据

3、通过poi提供的工具类ExcelExportUtil注入模板和数据

三、下载工具类DownloadExcel

 四、方法汇总

---

本文介绍按照excel模板导出数据，当然excel导出还有很多种办法，但这是最容易的一种，难的就是要自己画模板，然后找到单元格进行填充数据，今天就介绍按照模板导出。

一、准备

1.Maven项目先引进poi依赖，然后进行代码编写

            <!--excel导入导出-->
            <dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi</artifactId>
				<version>3.9</version>
			</dependency>
			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi-ooxml</artifactId>
				<version>3.9</version>
			</dependency>
			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi-ooxml-schemas</artifactId>
				<version>3.9</version>
			</dependency>

2.模板

链接：https://pan.baidu.com/s/11JqbtIvPc8xkrit67cEoCg?pwd=nz58 
提取码：nz58

 {{map的key}}:当你使用map来注入数据的时候就需要使用{{}}来标记map的key，通过key来注入值

{{!fe: list t.number  t.receivesNum}}:通过list注入值，list里面嵌套一个小map然后进行遍历，遍历的主要作用就是进行导出多条数据

二、导出步骤

这里介绍的是map和list两种方式导出

1、获取当地模板

 //获取模板信息   "D:\\xiangmu\\force/force/excel/调拨单.xlsx"
        TemplateExportParams params = new TemplateExportParams(
                System.getProperty("user.dir") + "/force/excel/调拨作业信息模板.xlsx");

2、Map格式导出

//时间格式化
 SimpleDateFormat sm=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

//用一个大map装调单表的数据和list，list里面装详情表的数据
        Map<String, Object> map = new HashMap<String, Object>();
        //判断前端传过来的id是否为空
        if (!EmptyUtil.isNull(ids)) {
            //调拨单号表数据
            Allot allot = allotService.getById(ids);
            if (allot != null) {
                //调拨单号
                map.put("allotWarehouseNumber", allot.getAllotWarehouseNumber());
                //创建时间
                map.put("createDate",sm.format(allot.getCreateDate()));

                //供应单位，该字段引用外表数据
                Dept dept = deptService.getById(allot.getAllotDept());
                if (dept !=null){
                    allot.setAllotDept(dept.getSimpleName());
                }
                map.put("allotDept",allot.getAllotDept());
            }

        }

 3、List方式导出数据

 List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        //序列号
        int number = 1;
        //明细表数据
        List<AllotDetail> allotDetailList = allotDetailService.list(new QueryWrapper<AllotDetail>().eq("allot_warehouse_number", ids));
        if (allotDetailList.size() != 0) {
            for (AllotDetail detail : allotDetailList) {
                //小map装详情表的数据
                Map<String, Object> maplist = new HashMap<String, Object>();
                //序列号
                maplist.put("number", number);
                //  //装备名称
                Article article = articleService.getById(detail.getArticleid());
                if (article != null) {
                    detail.setArticleid(article.getArticlename());
                }else{
                    continue;
                }
                maplist.put("articleid", detail.getArticleid());


                Measureunit unit = measureunitService.getById(detail.getUnit());
                if (unit != null) {
                    detail.setUnit(unit.getMeasureunitName());
                }else{
                    continue;
                }
                //单位
                maplist.put("unit", detail.getUnit());

                //规格类型
                maplist.put("articlespec", detail.getArticlespec());

                //单价
                maplist.put("price","");
                //等级
                maplist.put("grade","");

                //数量
                maplist.put("num", detail.getNum());
                //将小map装进list
                lists.add(maplist);
                number++;
            }
        }
        //将list放入大Map中
        map.put("list", lists);

3、通过poi提供的工具类ExcelExportUtil注入模板和数据

//注入数据以及模板
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        File savefile = new File(System.getProperty("user.dir") + "/force/excel/tempfile/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        String fileurl = System.getProperty("user.dir") + "/force/excel/tempfile/" + RandomFileName.getRandomFileName() + ".xlsx";
        FileOutputStream fos = new FileOutputStream(fileurl);
        workbook.write(fos);
        fos.close();
        String state = DownloadExcel.downloadfile(response, fileurl, "调拨作业信息模板.xlsx");
        if (state == "0") {
            System.out.println("文件不存在");
        }

三、下载工具类DownloadExcel

public class DownloadExcel {
	public static String downloadfile(HttpServletResponse response, String url, String newFileName) {
		ServletOutputStream out = null;
		FileInputStream ips = null;
		
		try { // 如果是测试可以指定路径
			// 获取文件存放的路径
			File file = new File(url);
			//String fileName = file.getName();
			// 获取到文字 数据库里对应的附件名字加上老的文件名字：filename 截取到后面的文件类型 例：txt 组成一个新的文件名字：newFileName
			//SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			//String newFileName = String.valueOf(sdf.format(new Date())+"."+fileName.substring(fileName.lastIndexOf(".")+1));
			if (!file.exists()) {
				// 如果文件不存在就跳出
				return "0";
			}
			ips = new FileInputStream(file);
			response.setContentType("multipart/form-data");
			// 为文件重新设置名字，采用数据库内存储的文件名称
			response.addHeader("Content-Disposition",
					"attachment; filename=\"" + new String(newFileName.getBytes("UTF-8"), "ISO8859-1") + "\"");
			out = response.getOutputStream();
			// 读取文件流
			int len = 0;
			byte[] buffer = new byte[1024 * 10];
			while ((len = ips.read(buffer)) != -1) {
				out.write(buffer, 0, len);
			}
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
				ips.close();
			} catch (IOException e) {
				System.out.println("关闭流出现异常");
				e.printStackTrace();
			}
		}

		return "1";
	}
}

 四、方法汇总

    /**
     * 导出接口
     *
     * @author dyc
     * @Date 2022/08/10
     */
    @RequestMapping("/export")
    @ResponseBody
    public void export(TaskAllotParam taskAllotParam, String ids, HttpServletResponse response) throws Exception {

        SimpleDateFormat sm=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //获取模板信息   "D:\\xiangmu\\force/force/excel/调拨单.xlsx"
        TemplateExportParams params = new TemplateExportParams(
                System.getProperty("user.dir") + "/force/excel/调拨作业信息模板.xlsx");

        //用一个大map装调单表的数据和list，list里面装详情表的数据
        Map<String, Object> map = new HashMap<String, Object>();

        BigDecimal number_sum = new BigDecimal(0);
        //判断前端传过来的id是否为空
        if (!EmptyUtil.isNull(ids)) {
            //调拨单号表数据
            Allot allot = allotService.getById(ids);
            if (allot != null) {
                //调拨单号
                map.put("allotWarehouseNumber", allot.getAllotWarehouseNumber());
                //创建时间
                map.put("createDate",sm.format(allot.getCreateDate()));

                //供应单位，该字段引用外表数据
                Dept dept = deptService.getById(allot.getAllotDept());
                if (dept !=null){
                    allot.setAllotDept(dept.getSimpleName());
                }
                map.put("allotDept",allot.getAllotDept());
            }

        }

        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
        //序列号
        int number = 1;
        //明细表数据
        List<AllotDetail> allotDetailList = allotDetailService.list(new QueryWrapper<AllotDetail>().eq("allot_warehouse_number", ids));
        if (allotDetailList.size() != 0) {
            for (AllotDetail detail : allotDetailList) {
                //小map装详情表的数据
                Map<String, Object> maplist = new HashMap<String, Object>();
                //序列号
                maplist.put("number", number);
                //  //装备名称
                Article article = articleService.getById(detail.getArticleid());
                if (article != null) {
                    detail.setArticleid(article.getArticlename());
                }else{
                    continue;
                }
                maplist.put("articleid", detail.getArticleid());


                Measureunit unit = measureunitService.getById(detail.getUnit());
                if (unit != null) {
                    detail.setUnit(unit.getMeasureunitName());
                }else{
                    continue;
                }
                //单位
                maplist.put("unit", detail.getUnit());

                //规格类型
                maplist.put("articlespec", detail.getArticlespec());

                //单价
                maplist.put("price","");
                //等级
                maplist.put("grade","");

                //数量
                maplist.put("num", detail.getNum());
                //将小map装进list
                lists.add(maplist);
                number++;
            }
        }
        //将list放入大Map中
        map.put("list", lists);
        //注入数据以及模板
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        File savefile = new File(System.getProperty("user.dir") + "/force/excel/tempfile/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        String fileurl = System.getProperty("user.dir") + "/force/excel/tempfile/" + RandomFileName.getRandomFileName() + ".xlsx";
        FileOutputStream fos = new FileOutputStream(fileurl);
        workbook.write(fos);
        fos.close();
        String state = DownloadExcel.downloadfile(response, fileurl, "调拨作业信息模板.xlsx");
        if (state == "0") {
            System.out.println("文件不存在");
        }


    }