1、自制工具（创建一个工具类）
package com.jeecg.limitprice.controller; import org.apache.poi.hssf.usermodel.*; import java.util.List; public class ExcelUtil { /** * 导出Excel * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */ public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,List<List> arrayList, 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]); } }*/ if(arrayList!=null && arrayList.size()>0) { for(int i=0;i<arrayList.size();i++) { row = sheet.createRow(i + 1); List list = arrayList.get(i); if(list !=null && list.size()>0) { for(int j=0;j<list.size();j++){ String a = (String) list.get(j); row.createCell(j).setCellValue(a); } } } } return wb; } }
2、前台
<t:dgToolBar title="导出" icon="icon-putout" funname="exportres"></t:dgToolBar>
//导出 function exportres(title,url,gname) { gridname=gname; JeecgExcelExport("limitPriceController.do?exportres", "limitPriceList"); }
3、后台方法
@RequestMapping(params = "exportres") public void exportres(LimitPriceEntity dyieat, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid, ModelMap map) throws IOException { CriteriaQuery cq = new CriteriaQuery(LimitPriceEntity.class, dataGrid); org.jeecgframework.core.extend.hqlsearch.HqlGenerateUtil.installHql(cq, dyieat, request.getParameterMap()); String ieflag = request.getParameter("ieflag"); String wheresql = ""; try { // 自定义追加查询条件 //自定义追加查询条件 String sql = " select a.cus_Company cusCompany, b.origin,b.destination,b.car_Type carType, " + " b.priceform,b.priceto,b.isbaoguan,b.type,b.create_Name createName, " + " b.create_By createBy,b.create_Date createDate,b.bpm_Status bpmStatus " + " from ld_customer a " + " right join limit_price b on(a.id = b.fidts) "+wheresql; List<Map<String,Object>> ruleList= jdbcTemplate.queryForList(sql); dataGrid.setTotal(ruleList.size()); dataGrid.setResults(ruleList); } catch (Exception e) { throw new BusinessException(e.getMessage()); } List<Map<String,Object>> dylist = dataGrid.getResults(); Session session = sessionFactory.getCurrentSession(); // excel标题 String[] title = { "客户名称","起始地","目的地","车型", "价格低","价格高", "是否报关","类型" }; // excel文件名 String fileName = "限价" + System.currentTimeMillis() + ".xls"; // sheet名 String sheetName = "限价"; List<List> contentList = new ArrayList<>(); if (dylist != null && dylist.size() > 0) { for (Map<String,Object> dyat : dylist) { List<String> list1 = new ArrayList<>(); Integer i1 = 0; //客户名称 i1 = toSet(list1,String.valueOf(dyat.get("cusCompany")), i1); // 起始地 i1 = toSet(list1,String.valueOf(dyat.get("origin")), i1); // 目的地 i1 = toSet(list1, String.valueOf(dyat.get("destination")), i1); //车型 i1 = toSet(list1, String.valueOf(dyat.get("carType")), i1); // 价格区间 i1 = toSet(list1, String.valueOf(dyat.get("priceform")), i1); // 价格区间 i1 = toSet(list1, String.valueOf(dyat.get("priceto")), i1); // 是否报关 i1 = toSet(list1, String.valueOf(dyat.get("isbaoguan")), i1); // 类型 i1 = toSet(list1, String.valueOf(dyat.get("type")), i1); contentList.add(list1); } } session.close(); // 创建HSSFWorkbook HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, contentList, null); // 响应到客户端 try { this.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { } } public Integer toSet(List list, String s, Integer i) { if (s != null && !s.equals("")&& !s.equals("null") && !s.equals("\"\"")) { if (s.contains("\"")) { s = s.replace("\"", ""); } list.add(i, s); } else { list.add(i, null); } i = i + 1; return i; } // 发送响应流方法 public void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(), "ISO8859-1"); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } }