前言:
excel的操作还是比较难的!可能对很多初学者来说,都不太理解怎么操作。当然是直接操作excel难,但是springmvc提供了一个抽象类(一个视图——AbstractExcelView),我们仅仅如要继承一个那个类,然后写一些操作逻辑,既可以轻松实现excel的导出!简单化了我们的开发!
在此小编提供一个工具类,可以很方便的对excel进行操作!轻松将数据库数据导出到excel!
工具类:
package com.wen.util;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
/**
* 通用的excelUtils类。可以适用大部分简单excel
* 前提是用springmvc的View
* @author wen
*
*/
public class ObjectExcelView extends AbstractExcelView {
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest arg2,
HttpServletResponse response) throws Exception {
// 这个是下载后的excel的文件名
Date date = new Date();
String filename = date + "";
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
// 不用质疑,这玩意就是excel的第几页页
HSSFSheet sheet;
HSSFCell cell;
// 设置一下第一页
sheet = workbook.createSheet("sheet1");
// 从springmvc中获取出来放入View的信息,就是Value
List<String> titles = (List<String>) model.get("titles");
// 标题样式,就是titles的样式,包括字体,大小
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 标题字体
HSSFFont headerFont = workbook.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 11);
headerStyle.setFont(headerFont);
short width = 20, height = 25 * 20;
int len = titles.size();
sheet.setDefaultColumnWidth(width);
// 设置标题,将从title的东西获取出来~
for (int i = 0; i < len; i++) {
//获取titles的值!
String title = titles.get(i);
//第一页,第一行,第几列的将数title插进去
cell = getCell(sheet, 0, i);
//设置风格
cell.setCellStyle(headerStyle);
//这个不用说了吧
setText(cell, title);
}
//这个要改改高度~~
sheet.getRow(0).setHeight(height);
// 以下就是内容样式了。都差不多,不写注释了。。吐血
HSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<Map<String, Object>> varList = (List<Map<String, Object>>) model.get("userData");
System.out.println(varList);
int varCount = varList.size();
for (int i = 0; i < varCount; i++) {
Map<String, Object> vpd = varList.get(i);
for (int j = 0; j < len; j++) {
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
String varstr = String.valueOf(vpd.get("var" + (j + 1))) != null
? String.valueOf(vpd.get("var" + (j + 1))) : "";
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cell = getCell(sheet, i + 1, j);
cell.setCellStyle(contentStyle);
setText(cell, varstr);
}
}
}
}
上面的var 的意思是为了操作方便,为什么?请看下面的代码。
package com.wen.controller.admin;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.websocket.server.PathParam;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import com.wen.util.FileDownload;
import com.wen.util.ObjectExcelView;
@Controller
public class AdminController {
@Resource
AdminServices adminService;
@Resource
UserServices userServices;
/**
* 导出Excel
*
* @return
*/
@RequestMapping(value = "excel")
public ModelAndView exportExcel() {
ModelAndView mv = new ModelAndView();
// 设置标题
try {
Map<String, Object> dataMap = new HashMap<String, Object>();
List<String> titleList = new ArrayList<String>();
titleList.add("学号");
titleList.add("姓名");
titleList.add("学分");
titleList.add("行为表现分");
titleList.add("创造能力");
titleList.add("专业能力");
titleList.add("组织管理");
titleList.add("文体能力");
titleList.add("社会实践");
titleList.add("总分");
dataMap.put("titles", titleList);
// 查询数据,将放入excel中
List<ExtendFreaction> listAllScore = adminService.findAllScoreDiscount();
List<Map<String, Object>> userData = new ArrayList<Map<String, Object>>();
for (int i = 0; i < listAllScore.size(); i++) {
Map<String, Object> tempMap = new HashMap<String, Object>();
tempMap.put("var1", listAllScore.get(i).getStudent_id());
tempMap.put("var2", listAllScore.get(i).getUsername());
tempMap.put("var3", listAllScore.get(i).getCredit());
tempMap.put("var4", listAllScore.get(i).getBehavior());
tempMap.put("var5", listAllScore.get(i).getCreative());
tempMap.put("var6", listAllScore.get(i).getMajor());
tempMap.put("var7", listAllScore.get(i).getOrganization());
tempMap.put("var8", listAllScore.get(i).getStyle());
tempMap.put("var9", listAllScore.get(i).getPractice());
tempMap.put("var10", listAllScore.get(i).getSum());
userData.add(tempMap);
}
dataMap.put("userData", userData);
// 新建新的Excel模板
ObjectExcelView erv = new ObjectExcelView();
// 提供下载
mv = new ModelAndView(erv, dataMap);
} catch (RuntimeException e) {
e.printStackTrace();
}
return mv;
}
}
这些功能都是之前写的demo都爬下来,然后写成博客。代码不难理解,操作可能比较繁琐。
提供一个bean类,当时bean的话是继承自上一个的。但是mybatis对象映射将数据存进去了!
父类:
package com.wen.bean;
public class Freaction {
private int id;
private double credit;
private double behavior;
private int student_id;
private double creative;
private double major;
private double organization;
private double style;
private double practice;
private double sum;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public double getCredit() {
return credit;
}
public void setCredit(double credit) {
this.credit = credit;
}
public double getBehavior() {
return behavior;
}
public void setBehavior(double behavior) {
this.behavior = behavior;
}
public double getMajor() {
return major;
}
public void setMajor(double major) {
this.major = major;
}
public int getStudent_id() {
return student_id;
}
public void setStudent_id(int student_id) {
this.student_id = student_id;
}
public double getCreative() {
return creative;
}
public void setCreative(double creative) {
this.creative = creative;
}
public double getOrganization() {
return organization;
}
public void setOrganization(double organization) {
this.organization = organization;
}
public double getStyle() {
return style;
}
public void setStyle(double style) {
this.style = style;
}
public double getPractice() {
return practice;
}
public void setPractice(double practice) {
this.practice = practice;
}
public double getSum() {
return sum;
}
public void setSum(double sum) {
this.sum = sum;
}
}
子类:
package com.wen.bean;
/**
* 这个类是继承的。方便扩展维护的一个类~~
* @author wen
*
*/
public class ExtendFreaction extends Freaction {
private String username;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "ExtendFreaction [username=" + username + ", getId()=" + getId() + ", getCredit()=" + getCredit()
+ ", getBehavior()=" + getBehavior() + ", getMajor()=" + getMajor() + ", getStudent_id()="
+ getStudent_id() + ", getCreative()=" + getCreative() + ", getOrganization()=" + getOrganization()
+ ", getStyle()=" + getStyle() + ", getPractice()=" + getPractice() + ", getSum()=" + getSum()
+ ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + ", toString()=" + super.toString()
+ "]";
}
}
这些都是没什么技术难度的。重点只要看上面前2个的代码,bean的话自定义就可以了。
小编提供的工具类也是有一定的局限性的。当然,如果觉得不够用,AbstractExcelView这个还支持自定义excel模板。下面先介绍excel的自定义模板!
注意:
1. 自定义模板需放在 WEB-INF 目录下2. 需要指定模板路径时不需要添加扩展名, Spring将自动添加 .xls 到URL 属性中.
3. 在指定URL前需先设置 view 的 ApplicationContext
自定义模板相对来说麻烦了一些!需要做挺多的配置,推荐使用代码来创建模板!
总结:
这几天小编实在是有点失了志~吃鸡玩一下,又玩王者,最后都不了了之!又不想学习,郁闷的想死!!!最后逼迫一下自己将以前做的东西总结一些写成博客!唉~真心不知道怎么缓解这个症状,如果大家有什么好的建议,欢迎大家给小编留言,解决这个“失了志”的症状!
回归正题,springmvc的还提供了很多很多的强大的功能,利于猿们发掘与学习,除了这个导出excel之外,还有文件上传也提供了相关的操作类。如果上面有不懂的,或者需要一份代码的同学欢迎私信小编!
程序人生,与君共勉