SpringMVC列表数据导出

最新推荐文章于 2024-03-08 18:18:36 发布

Allean_hello

最新推荐文章于 2024-03-08 18:18:36 发布

阅读量785

点赞数 2

分类专栏： WEB开发

本文链接：https://blog.youkuaiyun.com/sinat_35598260/article/details/78455157

版权

WEB开发专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何在SpringMVC中实现列表数据的导出，包括页面上添加行内点击事件的处理和Java后台的相关代码实现，以及 Maven 中相关依赖的配置步骤。

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

页面上怎么写?-----加上行内点击事件

<a href="javascript:;" οnclick="exportRegInfo()">导出数据</a>

Js函数:

	function exportRegInfo(){
		var id = getUrlParams("id");
		var _url='/thoughtism/activityReg/getRegListExcel.json?id='+id+"&name="+name;
    	window.open(_url);
	}

Java类怎么写?

1.首先要在maven里配置相关jar包的引用，编译去下载

  <dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
  </dependency>

2.创建一个Util类

public class ExcelUtil {
	/**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);
      

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) i);
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
}

3.Controller部分

**
	 * @param request
	 * @param response
	 * @return 导出活动的报名列表，格式为Excel
	 * @throws IOException
	 */
	@ResponseBody
	@RequestMapping(value = "/getRegListExcel.json")
	public String download(HttpServletRequest request, HttpServletResponse response) throws IOException {
		
		String id = StringUtil.null2Str(getRequest().getParameter("id"));
		String name = StringUtil.null2Str(getRequest().getParameter("name"));
		String fileName = name+"__报名用户列表";
		// 填充projects数据
		List<Map<String, Object>> activityReg = createData(StringUtil.nullToInteger(id));
		List<Map<String, Object>> list = createExcelRecord(activityReg);
		// 列名
		String columnNames[] = { "姓名", "手机号", "报名时间", "是否报名成功", "发送短信状态" };
		// map中的key
		String keys[] = { "name", "mobile", "signUpTime", "status", "smsStatus" };
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			ExcelUtil.createWorkBook(list, keys, columnNames).write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);
		// 设置response参数，可以打开下载页面
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
		ServletOutputStream out = response.getOutputStream();
		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		try {
			bis = new BufferedInputStream(is);
			bos = new BufferedOutputStream(out);
			byte[] buff = new byte[2048];
			int bytesRead;
			// Simple read/write loop.
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}
		} catch (final IOException e) {
			throw e;
		} finally {
			if (bis != null) {
				bis.close();
			}
			if (bos != null) {
				bos.close();
			}
		}
		return null;
	}

/**
	 * @return
	 */
	private List<Map<String, Object>> createData(Integer id) {
		List<Map<String, Object>> data = activityRegService.getRegList(StringUtil.nullToInteger(id), null, null);
		return data;
	}

/**
	 * @param activityReg
	 * @return
	 */
	private List<Map<String, Object>> createExcelRecord(List<Map<String, Object>> activityReg) {
		List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = new HashMap<String, Object>(50);
		map.put("sheetName", "sheet1");
		listmap.add(map);
		// Activity activityRecord=null;
		for (int j = 0; j < activityReg.size(); j++) {
			Map<String, Object> mapValue = new HashMap<String, Object>(50);
			// "name", "phoneNum", "signUpTime","status","smsStatus"
			mapValue.put("name", activityReg.get(j).get("name"));
			mapValue.put("mobile", (activityReg.get(j).get("mobile")));
			Object regTimeObj = activityReg.get(j).get("reg_time");
			if (regTimeObj != null) {
				mapValue.put("signUpTime", (sdf.format(regTimeObj)));
			}else{
				mapValue.put("signUpTime", (null));
			}
			Integer status = StringUtil.nullToInteger(activityReg.get(j).get("status"));
			if (status != null && status.equals(1)) {
				mapValue.put("status", "成功");
			} else if (status != null && status.equals(2)) {
				mapValue.put("status", "失败");
			}
			Integer smsStatus = StringUtil.nullToInteger(activityReg.get(j).get("sms_status"));
			if (smsStatus != null && smsStatus.equals(1)) {
				mapValue.put("smsStatus", "发送成功");
			} else if (smsStatus != null && smsStatus.equals(2)) {
				mapValue.put("smsStatus", "发送失败");
			} else {
				mapValue.put("smsStatus", "未发送");
			}
			listmap.add(mapValue);
		}
		return listmap;
	}