springMvc下导出Excel功能
2017-11-17 11:48 sihao560 阅读(5963) 评论(0) 编辑 收藏 举报1、ExcleUtils.java代码:
public class ExcelUtils { //excel默认宽度; private static int width = 256*14; //默认字体 private static String excelfont = "微软雅黑"; /** * * @param excelName 导出的EXCEL名字 * @param sheetName 导出的SHEET名字 当前sheet数目只为1 * @param headers 导出的表格的表头 * @param ds_titles 导出的数据 map.get(key) 对应的 key * @param ds_format 导出数据的样式 * 1:String left; * 2:String center * 3:String right * 4 int right * 5:float ###,###.## right * 6:number: #.00% 百分比 right * @param widths 表格的列宽度 默认为 256*14 * @param data 数据集 List<Map> * @param response * @throws IOException */ public static void export(String excelName, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data ,HttpServletRequest request, HttpServletResponse response) throws IOException { HttpSession session = request.getSession(); session.setAttribute("state", null); if(widths==null){ widths = new int[ds_titles.length]; for(int i=0;i<ds_titles.length;i++){ widths[i]=width; } } if(ds_format==null){ ds_format = new int[ds_titles.length]; for(int i=0;i<ds_titles.length;i++){ ds_format[i]=1; } } //设置文件名 String fileName = ""; if(StringUtils.isNotEmpty(excelName)){ fileName = excelName; } //创建一个工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //创建一个sheet HSSFSheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?sheetName:"excel"); //创建表头,如果没有跳过 int headerrow = 0; if(headers!=null){ HSSFRow row = sheet.createRow(headerrow); //表头样式 HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName(excelfont); font.setFontHeightInPoints((short) 11); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < headers.length; i++) { sheet.setColumnWidth((short)i,(short)widths[i]); HSSFCell cell = row.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(style); } headerrow++; } //表格主体 解析list if(data != null){ List styleList = new ArrayList(); for (int i = 0; i <ds_titles.length; i++) { //列数 HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontName(excelfont); font.setFontHeightInPoints((short) 10); style.setFont(font); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); if(ds_format[i]==1){ style.setAlignment(HSSFCellStyle.ALIGN_LEFT); }else if(ds_format[i]==2){ style.setAlignment(HSSFCellStyle.ALIGN_CENTER); }else if(ds_format[i]==3){ style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //int类型 }else if(ds_format[i]==4){ style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //int类型 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0")); }else if(ds_format[i]==5){ //float类型 style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); }else if(ds_format[i]==6){ //百分比类型 style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); } styleList.add(style); } for (int i = 0; i < data.size() ; i++) { //行数 HSSFRow row = sheet.createRow(headerrow); Map map = data.get(i); for (int j = 0; j <ds_titles.length; j++) { //列数 HSSFCell cell = row.createCell(j); Object o = map.get(ds_titles[j]); if(o==null||"".equals(o)){ cell.setCellValue(""); }else if(ds_format[j]==4){ //int cell.setCellValue((Long.valueOf((map.get(ds_titles[j]))+"")).longValue()); }else if(ds_format[j]==5|| ds_format[j]==6){ //float cell.setCellValue((Double.valueOf((map.get(ds_titles[j]))+"")).doubleValue()); }else { cell.setCellValue(map.get(ds_titles[j])+""); } cell.setCellStyle((HSSFCellStyle)styleList.get(j)); } headerrow++; } } fileName=fileName+".xls"; String filename = ""; try{ filename =encodeChineseDownloadFileName(request,fileName); }catch(Exception e){ e.printStackTrace(); } // final String userAgent = request.getHeader("USER-AGENT"); // if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器 // filename = URLEncoder.encode(fileName,"UTF8"); // }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器 // filename = new String(fileName.getBytes(), "ISO8859-1"); // }else{ // filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器 // } response.setHeader("Content-disposition", filename); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename="+filename); response.setHeader("Pragma", "No-cache"); OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); session.setAttribute("state", "open"); } /** * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性 * @throws UnsupportedEncodingException */ public static String encodeChineseDownloadFileName( HttpServletRequest request, String pFileName) throws Exception { String filename = null; String agent = request.getHeader("USER-AGENT"); if (null != agent){ if (-1 != agent.indexOf("Firefox")) {//Firefox filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?="; }else if (-1 != agent.indexOf("Chrome")) {//Chrome filename = new String(pFileName.getBytes(), "ISO8859-1"); } else {//IE7+ filename = java.net.URLEncoder.encode(pFileName, "UTF-8"); filename = filename.replace("+", "%20"); } } else { filename = pFileName; } return filename; } }
2、前端调用代码:
$("#exportExcel").click(function(){ var url = "exprotProductList"; $('#listForm').attr('action', url); $('#listForm').submit(); $('#listForm').attr('action', "list"); });
3、controller中代码(springMVC):
/** * 测试下载 */ @GetMapping("/exprotProductList") public void exportExcel(Product.Type type,Long countryId, Long productCategoryId, Long brandId, Long promotionId, Long productTagId, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, ModelMap model,HttpSession session,HttpServletRequest request,HttpServletResponse response){ String[] excelHeader = { "ID","国家", "商品编号", "名称","分类","创建日期"}; String[] ds_titles = { "id","country", "sn", "name","productCategory","createdDate"}; int[] ds_format = { 1,1, 1, 1,1,1}; try{ Country country = null; if (countryId != null) { country = countryService.find(countryId); } ProductCategory productCategory = productCategoryService.find(productCategoryId); Brand brand = brandService.find(brandId); Promotion promotion = promotionService.find(promotionId); ProductTag productTag = productTagService.find(productTagId); List<Product> products = productService.findList(type, productCategory, country, brand, promotion, productTag, isMarketable, isList, isTop, isOutOfStock, isStockAlert); List<Map<String,Object>> data = new ArrayList<Map<String,Object>>(); if(CollectionUtils.isNotEmpty(products)){ for(Product product : products){ Map<String,Object> map = new HashMap<String,Object>(); map.put("id", product.getId()); map.put("country", product.getProductCategory().getCountry().getName()); map.put("sn", product.getSn()); map.put("name", product.getName()); map.put("productCategory", product.getProductCategory().getName()); map.put("createdDate", product.getCreatedDate()); data.add(map); } } ExcelUtils.export("商品表", "商品", excelHeader, ds_titles, ds_format, null, data, request, response); }catch(Exception e){ e.printStackTrace(); } }
4、servicer、servicerimpl、dao、daoimpl中的代码省略