poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
controller
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
public String exportExcel(HttpServletRequest request, Model model) {
try {
//查询导出数据
List<Order> orderList = orderService.findListByParam(map);
//设置名称、标题、数据一一对应
DtExcelMap excel = DtExcelMap.getExcelMap("订单列表",
new String[] { "ID","编号","序列", "匿名", "状态", "姓名","手机号"},
orderList,
new String[] { "id","orderNo","serialNumber", "anonymous", "statusView", "realName", "mobile"});
model.addAttribute("map", excel);
//返回视图(xml中配置)
return "excelView";
} catch (Exception e) {
log.error("导出excel失败:" + e);
}
return null;
}
ExcelView
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
public class ExcelView extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook,
HttpServletRequest request, HttpServletResponse response) throws Exception {
// 从model对象中获取excel所需数据
DtExcelMap map = (DtExcelMap) model.get("map");
// 创建Excel的sheet
String name = map.getExcelName();
HSSFSheet sheet = workbook.createSheet(name);
// 创建标题行
HSSFRow header = sheet.createRow(0);
String[] titles = map.getTitles();
int cell = 0;
for (String title : titles) {
header.createCell(cell).setCellValue(title);
cell++;
}
// 填充数据
int rowNum = 1;
List<?> models = map.getModels();
String[] fileds = map.getFields();
for (Object model2 : models) {
HSSFRow row = sheet.createRow(rowNum);
String cellValue = null;
for (int i = 0; i < fileds.length; i++) {
String fieldName = fileds[i];
Object result = ReflectUtil.invokeGetter(model2, fieldName);
cellValue = ReflectUtil.toString(result);
// 如果是日期类型则进行格式化处理
if (ReflectUtil.isDateType(model2.getClass(), fieldName)) {
cellValue = DateUtil.Date2Stirng2Second((Date) result);
}
row.createCell(i).setCellValue(cellValue);
}
rowNum++;
}
response.setHeader("Content-Disposition",
"attachment;filename=" + new String((name + ".xls").getBytes(), "ISO-8859-1"));
}
}
spring xml
controller里面return的就是该视图,也就是ExcelView
<bean name="excelView" class="com.yzx.view.ExcelView"/>
ExcelView会使用到以下几个工具类
ReflectUtil
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;
//反射工具类
public class ReflectUtil {
public static <T> Object invokeGetter(T target, String fieldName) throws NoSuchMethodException,
SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
// 如果属性名为xxx,则方法名为getXxx
String methodName = "get" + StringUtil.firstCharUpperCase(fieldName);
Method method = target.getClass().getMethod(methodName);
return method.invoke(target);
}
@SuppressWarnings("unchecked")
public static <T> boolean isDateType(Class<T> clazz, String fieldName) {
boolean flag = false;
Field field = null;
boolean noSuchFiled = true;
do {
try {
field = clazz.getDeclaredField(fieldName);
Object typeObj = field.getType().newInstance();
flag = typeObj instanceof Date;
noSuchFiled = false;
} catch (NoSuchFieldException e) {
clazz = (Class<T>) clazz.getSuperclass();
} catch (Exception e) {
// 除了NoSuchFieldException这个异常,其他直接跳出循环
noSuchFiled = false;
}
} while (noSuchFiled && clazz != Object.class);
return flag;
}
public static String toString(Object object) {
StringBuffer buffer = new StringBuffer();
if (object != null) {
buffer.append(object);
}
return buffer.toString();
}
}
DateUtil
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtil {
public static final String DEFAULT_DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
public static String toString(Date date, String format) {
SimpleDateFormat formatter;
if ((date == null) || (format == null) || (format.length() == 0)) {
return null;
}
formatter = new SimpleDateFormat(format);
return formatter.format(date);
}
/**
* 日期字符串转换,页面显示格式为:yyyy-MM-dd HH:mm:dd
* @param 参数为Date类型
*/
public static String Date2Stirng2Second(Date date) {
return toString(date, DEFAULT_DATETIME_FORMAT);
}
}
DtExcelMap
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DtExcelMap {
private final static String EXCELNAME = "name";
private final static String MODELS = "models";
private final static String FILEDS = "fileds";
private final static String TITLES = "titles";
private Map<String, Object> map;
private DtExcelMap() {
super();
}
private DtExcelMap(Map<String, Object> map) {
super();
this.map = map;
}
private Map<String, Object> getMap() {
return map;
}
/**
*
* @param name
* 导出excel表名字
* @param titles
* excel表标题
* @param models
* 表内容代表的实体类
* @param fields
* 每列标题对应实体类的属性,titles和fields长度必须相等,且顺序一致
* @return
* @throws Exception
*/
public static <T> DtExcelMap getExcelMap(String name, String[] titles, List<T> models, String[] fields)
throws Exception {
if (titles == null || fields == null) {
return null;
}
if (titles.length != fields.length) {
throw new Exception("标题对应的实体属性长度不一致");
}
Map<String, Object> map = new HashMap<>();
map.put(EXCELNAME, name);
map.put(TITLES, titles);
map.put(MODELS, models);
map.put(FILEDS, fields);
return new DtExcelMap(map);
}
public String getExcelName() {
return (String) this.getMap().get(EXCELNAME);
}
public String[] getTitles() {
return (String[]) this.getMap().get(TITLES);
}
public String[] getFields() {
return (String[]) this.getMap().get(FILEDS);
}
@SuppressWarnings("unchecked")
public <T> List<T> getModels() {
return (List<T>) this.getMap().get(MODELS);
}
}
以上代码开箱即用。