页面jsp
//点击按钮导出跳到controller层
$('.daochu').click(function(){
var index = layer.load(0, {time: 5000});
window.location = "/addproduct/export;
})
controller
@Autowired
public HttpServletRequest request;
@Autowired
public HttpServletResponse response;
@RequestMapping(value = "/export", produces = "application/json; charset=utf-8")
@ResponseBody
public String export() {
try {
// 要导出的查询语句
List<OrderIdList> list = addProductService.selectCids();// 获取的list列表
// excel标题
List<String> headers = new ArrayList<String>();
headers.add("isbn");
// 和上面标题对应
List<String> titles = new ArrayList<String>();
titles.add("isbn");
// 生成excel文件名
String fileName = "isbn_" + System.currentTimeMillis() + ".xls";
fileName = new String(fileName.getBytes(), "ISO8859-1");
response.setContentType("application/vnd.ms-excel;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream os = response.getOutputStream();
ExportExcelUtil<OrderIdList> exportExcel = new ExportExcelUtil<OrderIdList>();
exportExcel.getHSSFWorkbook("sheet1", headers, titles, list, os, null);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
return "导出失败";
}
return "导出成功";
}
untils工具类
package com.mingwen.common.utils;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExportExcelUtil<T> {
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
/**
* 导出Excel
*
* @param <E>
* @param sheetName sheet名称
* @param title 标题
* @param list 内容
* @param wb HSSFWorkbook对象
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public void getHSSFWorkbook(String sheetName, List<String> headers, List<String> titles, List<T> list,
OutputStream out, HSSFWorkbook wb) {
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 声明列对象
HSSFCell cell = null;
// 创建标题
Iterator<String> iterator = headers.iterator();
int n = 0;
while (iterator.hasNext()) {
cell = row.createCell(n);
cell.setCellValue(iterator.next());
cell.setCellStyle(style);
n++;
}
//标题
Iterator<T> it = list.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = it.next();
Class tCls = t.getClass();
Method[] methods = tCls.getMethods();
List<String> methodList = new ArrayList<String>();
for (Method method : methods) {
if (method.getName().startsWith("get")) {
methodList.add(method.getName());
}
}
for (short i = 0; i < titles.size(); i = (short) (i + 1)) {
cell = row.createCell(i);
String fieldName = titles.get(i);
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
if (!methodList.contains(getMethodName)) {
getMethodName = "get" + fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);
}
Method getMethod = tCls.getMethod(getMethodName, new Class[0]);
Object value = getMethod.invoke(t, new Object[0]);
if (null != value) {
if (value instanceof Date) {
cell.setCellValue(sdf.format(value));
} else {
cell.setCellValue(value.toString());
}
}
} catch (SecurityException | IllegalAccessException | IllegalArgumentException
| InvocationTargetException | NoSuchMethodException e) {
e.printStackTrace();
}
}
}
try {
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
}
pom.xml
<!-- excel表格处理 -->
<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>javax.activation</groupId>
<artifactId>activation</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>javax.mail</groupId>
<artifactId>mail</artifactId>
<version>1.4.7</version>
</dependency>
<dependency>
<groupId>com.sun.mail</groupId>
<artifactId>smtp</artifactId>
<version>1.6.0</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
效果: