步骤:
导入jar包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
前端:
1: 添加button控件:
<button id="js-export" type="button" >导出数据Excel</button>
2: Js触发事件,选择进入的控制器和传入参数:
<script>
$('#js-export').click(function(){
window.location.href="<%=path%>/mine/myGift/export.do?giftid=${requestScope.page.params.giftid}";
});
</script>
后台:
1.控制器:
/**
* 导出报表
* @return
*/
@RequestMapping("export")
@Privilege(source = "mine/myGift/export.do")
@ResponseBody
public void export(HttpServletRequest request,HttpServletResponse response,String giftid, Page page) throws Exception {
log.info("*********************【接口请求】:导出报表**mine/myGift/export.do******************************************");
log.info("**【请求数据】:" +giftid );
try {
Assert.notNull(giftid);
//获取数据
List<MyGift> list = myGiftService.selectListByUserId(giftid);//业务层的方法每个人都不同,查到想要的数据就行
//excel标题
String[] title = {"领取用户","礼品","收件人","联系电话","身份证号码","省","市","区","详细地址","领取时间"};
//excel文件名
String fileName = "礼品信息表"+System.currentTimeMillis()+".xls";
//sheet名
String sheetName = "礼品信息表";
String[][] temp = new String[title.length][title.length];
for (int i = 0; i < list.size(); i++) {
MyGift obj = list.get(i);
temp[i][0] = obj.getUsername();
temp[i][1] = obj.getGifttitle();
temp[i][2] = obj.getRecipients();
temp[i][3] = obj.getContactnumber();
temp[i][4] = obj.getItentity();
temp[i][5] = obj.getProvince();
temp[i][6] = obj.getCity();
temp[i][7] = obj.getArea();
temp[i][8] = obj.getIndetail();
temp[i][9] = DateUtil.format_YMDHM(obj.getDrawtime());
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, temp, null);
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
log.error("**【响应到客户端异常】:" + e.getMessage());
}
} catch (Exception e) {
log.error("**【控制器异常】:" + e.getMessage());
} finally {
log.info("**********************************************************");
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"utf-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
2. 工具类 ExcelUtil.java:
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 ExcelUtil {
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, 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();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
}
效果:
点击“导出Excel”
下载数据,并且看后台日志:
2019-05-05 22:21:17,801 INFO [MyGiftController.java:237] : *********************【接口请求】:导出报表**mine/myGift/export.do******************************************
2019-05-05 22:21:17,802 INFO [MyGiftController.java:239] : **【请求数据】:A3420A69778F416FA8D1C72158C98F84:Page [pageNo=1, pageSize=10, results=null, totalPage=0, totalRecord=0]:{}
2019-05-05 22:21:18,082 INFO [MyGiftController.java:289] : **********************************************************