- 导入所需依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
- 前端代码
<div class="title">
<h3>代理列表</h3><a onclick="exOut()">导出表格</a>
</div>
/*导出数据*/
function exOut() {
var schoolName = $("#sch_fx").val();
var city = $("#input_city").val();
var school = $("#input_school").val();
var subject = $("#input_subject").val();
var name = $("#input_name").val();
var telNumber = $("#input_tel").val();
var grade = $("#grade").val();
var onTime = $("#onTime").val();
var offTime = $("#offTime").val();
window.location = "../sys/exportOut?schoolName="+schoolName+"&city="+city+"&school="+school+"&subject="+subject+"&name="+name+"&telNumber="+telNumber+"&grade="+grade+"&onTime="+onTime+"&offTime="+offTime;
}
- 后台代码
@RequestMapping(value = "/exportOut")
public void exportOut(Model model, String schoolName, String name, String city, String school, String subject, String grade, String telNumber, String onTime, String offTime, HttpServletRequest request, HttpServletResponse response){
List<Map<String,Object>> list = new ArrayList<>();
StuData stuData = new StuData(name,city,school,subject,grade,telNumber,null,schoolName,onTime,offTime);//封装查询条件
//调用service查询所有数据,传的是一个对象,需要封装一个实体类.,返回一个list
List<StuData> listAll = schoolZPService.findAll(stuData);
//遍历集合,拿到集合中的每一个数据,封装到map中
for (StuData data : listAll) {
Map<String,Object> map = new HashMap<>();
map.put("分校名称",data.getSchoolName());
map.put("代理姓名",data.getName());
map.put("所在城市",data.getCity());
map.put("就读高校",data.getSchool());
map.put("就读专业",data.getSubject());
map.put("年级",data.getGrade());
map.put("电话号码",data.getTelNumber());
map.put("申请时间",data.getTime());
list.add(map);
}
//把表头封装到list集合中
List<String> headList = new ArrayList<>();
headList.add("分校名称");
headList.add("代理姓名");
headList.add("所在城市");
headList.add("就读高校");
headList.add("就读专业");
headList.add("年级");
headList.add("电话号码");
headList.add("申请时间");
//调用工具类,传入文件名,表头集合,数据集合,request和response
ExportExcel.exportExcel("代理资料",headList,list,request,response);
}
- 工具类
/**
*
* @param sheetName 工作表的名字
* @param column 列名
* @param data 需要导出的数据 ( map的键定义为列的名字 一定要和column中的列明保持一致 )
* @param response
*/
public static void exportExcel(String sheetName, List<String> column, List<Map<String,Object>> data, HttpServletRequest request, HttpServletResponse response){
//创建工作薄
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
// 表头
Row headRow = sheet.createRow(0);
for (int i = 0; i < column.size(); i++){
headRow.createCell(i).setCellValue(column.get(i));
}
for (int i = 0; i < data.size(); i++) {
Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
for (int x = 0; x < column.size(); x++) {
dataRow.createCell(x).setCellValue(data.get(i).get(column.get(x))==null?"":data.get(i).get(column.get(x)).toString());
}
}
response.setContentType("application/vnd.ms-excel");
try {
//获取浏览器名称
String agent=request.getHeader("user-agent");
String filename=sheetName+".xls";
//不同浏览器需要对文件名做特殊处理
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ new BASE64Encoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
//推送浏览器
response.setHeader("Content-Disposition","attachment;filename="+filename);
hssfWorkbook.write(response.getOutputStream());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
注意:如果从前端传输数据到后台或者请求后台的时候,尽量不要使用ajax提交,容易出问题,使用window.location提交应该就不会出现各种问题.