一、实体类
二、控制层Controller
需要导的包，都是POI里的，我使用的是maven，
pom.xml中的配置
<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>
控制层Controller需要导的包
/**
*
* 导出用户数据
*
*/
@RequestMapping(value = "export", method=RequestMethod.GET)
public String exportFile(HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName="预报名学生数据"+DateUtils.getDate("yyyyMMdd");
//填充ToSignUpStudent数据
List<ToSignUpStudent> student=createData(); //调用createData()方法，获得要导出的list对象集合
List<Map<String,Object>> list=createExcelRecord(student); //调用createExcelRecord(student);方法，将list对象集合进行转化为Excel中的每行数据
String columnNames[]={"预报名学生id","编号","姓名","年龄","身高","体重","脚码","备注"};//列名
String keys[] = {"ts_id","ts_code","ts_name","ts_age","ts_height","ts_weight","ts_feet","remark"};//map中的key
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;
} catch (Exception e) {
addMessage(redirectAttributes, "导出用户失败！失败信息："+e.getMessage());
}
return null;
}
//进行数据库中list对象集合查询
private List<ToSignUpStudent> createData() {
ToSignUpStudent student = new ToSignUpStudent();
return service.list(student);
}
//对象集合解析为Excel中的数据
private List<Map<String, Object>> createExcelRecord(List<ToSignUpStudent> projects) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listmap.add(map);
ToSignUpStudent student=null;
for (int j = 0; j < projects.size(); j++) {
student=projects.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
String height = student.getTs_height() + "m";
String age = student.getTs_age() + "岁";
String weight = student.getTs_weight() + "kg";
String feet = student.getTs_feet() + "码";
mapValue.put("ts_id", student.getTs_id());
mapValue.put("ts_code", student.getTs_code());
mapValue.put("ts_name", student.getTs_name());
mapValue.put("ts_age", age);
mapValue.put("ts_height", height);
mapValue.put("ts_weight", weight);
mapValue.put("ts_feet", feet);
mapValue.put("remark", student.getRemark());
listmap.add(mapValue);
}
return listmap;
}
三、ExcelUtil工具类
需要导的包：
工具类：
public class ExcelUtil {
//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.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_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;
}
}
四、JSP页面：
<input id="btnExport" class="layui-btn layui-btn-normal layui-btn-radius" type="button" onclick="btnExport()" value="导出"/>
五、js：
function btnExport(){
var msg = "确认要导出预报名学生数据吗？";
confirm(msg,function(){
window.open(ctx+"/tosignup/export","_self");
})
}