Language: java

Frame: SSM

Project: maven

Tool class: ExcelUtils.java

Download address of tool class: https://download.csdn.net/download/ledzcl/10234291

Note: This download address is from the blog of dedzcl of CSDN (connection: https://blog.csdn.net/ledzcl/article/details/79222737)

Only this memo

1. After downloading the tool class, directly put it into maven project, and I put it under util package

2. Add jar package dependency in pom

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

Download address of jar package: https://mvnrepository.com/artifact/org.apache.poi/poi

3. Add requestMapper to controller layer

@ResponseBody
	@RequestMapping("/allExcel")
	public void AllExcel(HttpServletResponse res) {
		try {
			ServletOutputStream out = res.getOutputStream();
			res.setContentType("application/vnd.ms-excel");
			res.setHeader("Content-disposition", "attachment;filename=" + new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+URLEncoder.encode("All", "UTF-8") + ".xls");
			Collection<Object[]> collection=new ArrayList<>();
			//
			List<Mess> allList = mService.getDownloadAll();
//			System.out.println(allList);
			for(Mess mess:allList) {
				Object[] os=new Object[20];
				os[0]=mess.getNumber();
				os[1]=mess.getSname();
				os[2]=mess.getIdcard();
				os[3]=mess.getSex();
				os[4]=mess.getBirthdate();
				os[5]=mess.getNation();
				os[6]=mess.getNativeplace();
				os[7]=mess.getHomeadress();
				os[8]=mess.getSgs();
				os[9]=mess.getSgc();
				os[10]=mess.getScadre();
				os[11]=mess.getDsfirst();
				os[12]=mess.getDssecound();
				os[13]=mess.getDsthird();
				os[14]=mess.getPname();
				os[15]=mess.getRelationship();
				os[16]=mess.getUnit();
				os[17]=mess.getDuty();
				os[18]=mess.getPhonenumber();
				os[19]=mess.getPaiwei();
				collection.add(os);
			}
			//
			String[] columnNames=new String[] {
				"number","Full name","ID number","Gender","Date of birth","Nation","registered residence","Home address","Graduate School","Graduating class",
				"Former class representative","First love school","Second favorite school","Third favorite school","Name of parents","Relationship with students","Company","post","Contact information","Whether to participate in the assignment"
			};
			int[] columnIndexs=new int[] {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19};
			ExcelUtils.export(collection, "All students", columnNames, columnIndexs, 100, out);
			out.flush();
			out.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

Interpretation:

(1) call ExcelUtils.export() in the tool class to generate and download Excel

(2) HttpServletResponse needs to set content type to res.setcontenttype ("application / vnd. MS Excel");

(3) prevent Chinese random code URLEncoder.encode("all", "UTF-8")

(4) for collection < object [] > it is necessary to convert the list returned from the service layer to object and put it into collection < object [] >

(5) columnNames, columnindexes and Object [] in collection < Object [] > are one-to-one correspondence

(6) finally, don't forget out.flush(); and out.close();

End...