使用JAVA在将页面数据导出成Excel表格
在工作的时候遇到一个需求,通过页面按钮将数据库的数据导出成excel表格,下面是实现的代码:
jar包依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
前台页面
<button id="outPutExcel" type="button" class="btn btn-primary" onclick="exportExcel()">导出</button>
js文件
function exportExcel() {
//这里获得一些要前台查询的数据
var productTitle = $('#productTitle').val();
var beginTime = $('#beginTime').val();
var endTime = $('#endTime').val();
var bonusMode = $('#bonusMode').val();
var productCategory = $('#productCategory').val();
//向后台发送请求
location.href=Feng.ctxPath+"/products/outPutExcel?productTitle="+productTitle+"&beginTime="+beginTime+"&endTime="+endTime+"&bonusMode="+bonusMode+"&productCategory="+productCategory;
}
后台代码
@RequestMapping(value = "/outPutExcel",method = RequestMethod.GET)
@ResponseBody
public void outPutExcel(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException {
//一、从数据库拿数据
if (null == request || null == response){
return;
}
//根据request获得前台传递过来的数据
String productTitle = request.getParameter("productTitle");
String beginTime = request.getParameter("beginTime");
String endTime = request.getParameter("endTime");
String bonusMode = request.getParameter("bonusMode");
String productCategory = request.getParameter("productCategory");
//根据前台的数据去数据库查出来自己要的数据
List<Map<String, Object>> list = (List<Map<String, Object>>) list(productTitle,beginTime,endTime,bonusMode,productCategory);
//二、 数据转成excel
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
//定义excel的文件的名字
String fileName="产品列表.xlsx";
fileName= URLEncoder.encode(fileName, "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
// 定义一个新的工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建一个Sheet页
XSSFSheet sheet = wb.createSheet("products");
sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
sheet.setColumnWidth(0, 4000);//设置列宽
sheet.setColumnWidth(1,4000);
sheet.setColumnWidth(2,4000);
sheet.setColumnWidth(3,4000);
sheet.setColumnWidth(11,4000);
sheet.setColumnWidth(12,4000);
sheet.setColumnWidth(13,4000);
XSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);
//获得表格第一行
XSSFRow row = sheet.createRow(0);
//根据需要给第一行每一列设置标题
XSSFCell cell = row.createCell(0);
cell.setCellValue("产品ID");
cell=row.createCell(1);
cell.setCellValue("产品标题");
cell=row.createCell(2);
cell.setCellValue("产品图标");
cell=row.createCell(3);
cell.setCellValue("产品广告语");
cell=row.createCell(4);
cell.setCellValue("产品种类");
cell=row.createCell(5);
cell.setCellValue("产品利率");
cell=row.createCell(6);
cell.setCellValue("最高额度");
cell=row.createCell(7);
cell.setCellValue("最低可贷");
cell=row.createCell(8);
cell.setCellValue("产品网址");
cell=row.createCell(9);
cell.setCellValue("热门flag");
cell=row.createCell(10);
cell.setCellValue("申请人数");
cell=row.createCell(11);
cell.setCellValue("结算模式");
cell=row.createCell(12);
cell.setCellValue("返佣模式");
cell=row.createCell(13);
cell.setCellValue("计拥形式");
cell=row.createCell(14);
cell.setCellValue("返佣金额");
cell=row.createCell(15);
cell.setCellValue("返佣比例");
cell=row.createCell(16);
cell.setCellValue("代理分享页面的网址");
cell=row.createCell(17);
cell.setCellValue("产品名称首字母");
cell=row.createCell(18);
cell.setCellValue("产品佣金");
cell=row.createCell(19);
cell.setCellValue("二级佣金");
cell=row.createCell(20);
cell.setCellValue("三级佣金");
cell=row.createCell(21);
cell.setCellValue("系统模块");
cell=row.createCell(22);
cell.setCellValue("创建时间");
cell=row.createCell(23);
cell.setCellValue("最长可贷天数");
cell=row.createCell(24);
cell.setCellValue("最短可贷天数");
cell=row.createCell(25);
cell.setCellValue("排序号");
XSSFRow rows;
XSSFCell cells;
//循环后台拿到的数据给所有行每一列设置对应的值
for (int i=0;i<list.size();i++){
// 在这个sheet页里创建一行
rows = sheet.createRow(i+1);
// 该行创建一个单元格,在该单元格里设置值
cells = rows.createCell(0);
cells.setCellValue(list.get(i).get("id").toString());
cells = rows.createCell(1);
cells.setCellValue((String) list.get(i).get("product_title"));
cells = rows.createCell(2);
cells.setCellValue((String) list.get(i).get("product_icon"));
cells = rows.createCell(3);
cells.setCellValue((String) list.get(i).get("product_slogen"));
cells = rows.createCell(4);
cells.setCellValue((String) list.get(i).get("productType"));
cells = rows.createCell(5);
cells.setCellValue(list.get(i).get("product_rate").toString());
cells = rows.createCell(6);
cells.setCellValue(list.get(i).get("product_maximum").toString());
cells = rows.createCell(7);
cells.setCellValue(list.get(i).get("product_minimum").toString());
cells = rows.createCell(8);
cells.setCellValue((String) list.get(i).get("product_url"));
cells = rows.createCell(9);
cells.setCellValue((String) list.get(i).get("hotFlag"));
cells = rows.createCell(10);
if (list.get(i).get("apply_count")!=null){
cells.setCellValue(list.get(i).get("apply_count").toString());
}else {
cells.setCellValue("0");
}
cells = rows.createCell(11);
cells.setCellValue((String) list.get(i).get("checkoutMode"));
cells = rows.createCell(12);
cells.setCellValue((String) list.get(i).get("bounsMode"));
cells = rows.createCell(13);
cells.setCellValue((String) list.get(i).get("bonusType"));
cells = rows.createCell(14);
if (list.get(i).get("bonus_value")!=null){
cells.setCellValue(list.get(i).get("bonus_value").toString());
}else {
cells.setCellValue("无");
}
cells = rows.createCell(15);
if (list.get(i).get("bonus_rate")!=null){
cells.setCellValue(list.get(i).get("bonus_rate").toString());
}else {
cells.setCellValue("无");
}
cells = rows.createCell(16);
cells.setCellValue((String) list.get(i).get("agen_share_url"));
cells = rows.createCell(17);
cells.setCellValue((String) list.get(i).get("product_first_name"));
cells = rows.createCell(18);
cells.setCellValue(list.get(i).get("product_commission").toString());
cells = rows.createCell(19);
cells.setCellValue(list.get(i).get("second_commission").toString());
cells = rows.createCell(20);
cells.setCellValue(list.get(i).get("third_commission").toString());
cells = rows.createCell(21);
cells.setCellValue((String) list.get(i).get("sys_model"));
cells = rows.createCell(22);
//如果拿到的是一个时间戳,根据时间戳转换成string格式存放
Date date= (Date) list.get(i).get("CreateTime");
String strDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
cells.setCellValue(strDate);
cells = rows.createCell(23);
cells.setCellValue(list.get(i).get("product_maximum_days").toString());
cells = rows.createCell(24);
cells.setCellValue(list.get(i).get("product_minimum_days").toString());
cells = rows.createCell(25);
cells.setCellValue(list.get(i).get("order_num").toString());
}
OutputStream out=null;
try {
out = response.getOutputStream();
wb.write(out);
}catch (IOException e){
e.printStackTrace();
}finally {
try {
out.close();
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
这样的话就可以通过点击页面上的按钮导出excel。