Spring MVC Web关于POI导出

来源：互联网发布：mac 安装mysql详解编辑：程序博客网时间：2024/06/01 15:20

<span style="font-family: Arial, Helvetica, sans-serif;">/**</span>

 * response * title   标题 * headers表头 * dataset数据 * pattern日期类型格式 * fileName文件名称 * @author Administrator * * @param <T> */public class ExportExcel<T>{@SuppressWarnings("unchecked")public void exportExcel(HttpServletResponse response,String title, String[] headers, Collection<T> dataset, String pattern,String fileName){// 声明一个工作薄          HSSFWorkbook workbook = new HSSFWorkbook();          // 生成一个表格          HSSFSheet sheet = workbook.createSheet(title);        // 设置表格默认列宽度为10个字节          sheet.setDefaultColumnWidth(20);        // 生成一个样式          HSSFCellStyle style = workbook.createCellStyle();         // 设置这些样式          //前景色彩//        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);          style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);          style.setBorderRight(HSSFCellStyle.BORDER_THIN);          style.setBorderTop(HSSFCellStyle.BORDER_THIN);          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 生成一个字体          HSSFFont font = workbook.createFont();          font.setColor(HSSFColor.VIOLET.index);          font.setFontHeightInPoints((short) 12);          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);                  // 把字体应用到当前的样式          style.setFont(font);                // 生成并设置另一个样式          HSSFCellStyle style2 = workbook.createCellStyle();          style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);          style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);          style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);          style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);          style2.setBorderRight(HSSFCellStyle.BORDER_THIN);          style2.setBorderTop(HSSFCellStyle.BORDER_THIN);          style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);          style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);          // 生成另一个字体          HSSFFont font2 = workbook.createFont();          font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);          // 把字体应用到当前的样式          style2.setFont(font2);                // 产生表格标题行          HSSFRow row = sheet.createRow(0);          for (int i = 0; i < headers.length; i++)          {              HSSFCell cell = row.createCell(i);              cell.setCellStyle(style);              HSSFRichTextString text = new HSSFRichTextString(headers[i]);              cell.setCellValue(text);        }                // 遍历集合数据，产生数据行          Iterator<T> it = dataset.iterator();        int index = 0;        while (it.hasNext()){        index++;        row = sheet.createRow(index);            T t = (T)it.next();            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值              Field[] fields = t.getClass().getDeclaredFields();            int j = 0;            for (int i = 0; i < fields.length; i++){                Field field = fields[i];                  String fieldName = field.getName();                  String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);                try{                @SuppressWarnings("rawtypes")Class tCls = t.getClass();                      Method getMethod = tCls.getMethod(getMethodName,new Class[]{});                      Object value = getMethod.invoke(t, new Object[]{});                      // 判断值的类型后进行强制类型转换                      String textValue = null;                    HSSFCell cell = row.createCell(i-j);                      cell.setCellStyle(style2);                    if(value != null) {                    if (value instanceof Date){                          Date date = (Date) value;                          SimpleDateFormat sdf = new SimpleDateFormat(pattern);                          textValue = sdf.format(date);                          cell.setCellStyle(style2);                        cell.setCellValue(textValue);                    }else if(value instanceof Boolean){                    textValue = "是";                        boolean bValue = (Boolean)value;                        if(!bValue){                        textValue = "否";                        }                        cell.setCellStyle(style2);                        cell.setCellValue(textValue);                    }else{                    // 其它数据类型都当作字符串简单处理                          textValue = value.toString();                         cell.setCellStyle(style2);                        cell.setCellValue(textValue);                    }                                       }else{                    j++;                    textValue = null;                    }                    //                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  //                    if (textValue != null){//                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");  //                        Matcher matcher = p.matcher(textValue);  //                        if (matcher.matches()){  //                            // 是数字当作double处理  //                            cell.setCellValue(Double.parseDouble(textValue));  //                        }else{  //                            HSSFRichTextString richString = new HSSFRichTextString(textValue);  //                            HSSFFont font3 = workbook.createFont();  //                            font3.setColor(HSSFColor.BLUE.index);  //                            richString.applyFont(font3);  //                            cell.setCellValue(richString);  //                        }  //                    }                  }catch (SecurityException e){                      e.printStackTrace();                  }catch(NoSuchMethodException e){                      e.printStackTrace();                  }catch (IllegalArgumentException e){                      e.printStackTrace();                  }catch (IllegalAccessException e){                      e.printStackTrace();                  }catch (InvocationTargetException e){                      e.printStackTrace();                  }finally{                      // 清理资源                  }              }        }        try{        OutputStream os = response.getOutputStream();         // 这个是弹出下载对话框的关键代码        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode((((null == fileName) || ("".equals(fileName.trim()))) ? ((new Date().getTime()) + "") : fileName.trim()) + ".xls", "utf-8"));            workbook.write(os);            os.flush();            os.close();          }catch (IOException e){              e.printStackTrace();          }}}以上为一个Class

此方法继承上面的类使用，用作Controller

<pre name="code" class="java">//菜品信息 导出功能@RequestMapping(value = "/dishes/exportExcel")public void exportExcel(@RequestParam(value = "ids", required = false) String[] ids,@RequestParam(value = "currentPage", required = false) Integer currentPage,@RequestParam(value = "mCode", required = false) String mCode, @RequestParam(value = "mName", required = false) String mName,@RequestParam(value = "mForShort", required = false) String mForShort,@RequestParam(value = "dishesName", required = false) String dishesName,String message2,HttpSession session,HttpServletResponse response) throws ParseException{//DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");///*当前登陆用户，先获取userId*///MyUserDetails curUserDetails=this.getCurrentUser(session.getId());//if(curUserDetails!=null){//userId=curUserDetails.getId();//}//user = userService.getUserById(userId);//String deptId = user.getBusinessDeptId();//List<Message> messageList = new ArrayList<Message>();////salesCondition.setShoppingGuide(shoppingGuide);//if(StringUtils.isNotBlank(startTime)){//salesCondition.setStartTime(format.parse(startTime));//}//if(StringUtils.isNotBlank(endTime)){//salesCondition.setEndTime(format.parse(endTime));//}//salesCondition.setDeptId(deptId);//messageList = messageService.findMessageForView(mName,mCode,mForShort,dishesName);if(messageList != null && messageList.size() > 0){//导出excel功能        ExportExcel<XlsDto> ex = new ExportExcel<XlsDto>();                //设置表头        String[] headers = {"编号","菜品名称","菜品分类","菜品单位","菜品价格","菜品优惠价","菜品会员价","菜品成本价","是否折扣","积分倍数","是否提成","提成金额","支持外卖","支持预订","排序号"};          //创建excel存储数据        List<XlsDto> dataset = new ArrayList<XlsDto>();                for(Message s : messageList){        XlsDto xl = new XlsDto();        xl.setMCode(s.getmCode());        xl.setMName(s.getmName());        xl.setDishesName(s.getDishesName());        xl.setUnitName(s.getUnitName());        xl.setMSale(s.getmSale());        xl.setMFavourable(s.getmFavourable());        xl.setMMember(s.getmMember());        xl.setMCost(s.getmCost());        xl.setMAgio(s.getmAgio());        xl.setMTimes(s.getmTimes());        xl.setMDeduct(s.getmDeduct());        xl.setMMoney(s.getmMoney());        xl.setMTakeOut(s.getmTakeOut());        xl.setMDestine(s.getmDestine());        xl.setMSort(s.getmSort());        dataset.add(xl);        }        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");        String fileName = "菜品信息单"+formatter.format(new Date());ex.exportExcel(response,"菜品信息单", headers, dataset, "yyyy-MM-dd",fileName);message2 = "exportExcelSuccess";//return messageList(currentPage,mCode, mName, mForShort, dishesName);}else{message2 = "exportExcelError";//return messageList(currentPage,mCode, mName, mForShort, dishesName);        }}

Dao层方法为

<pre name="code" class="java">@Transactional(propagation=Propagation.REQUIRED,readOnly=true)public List<Message> findMessageForView(String mName,String mCode,String mForShort,String dishesName) {StringBuffer hql=new StringBuffer();hql.append(" from Message where 1=1");Map<String,Object> params=new HashMap<String, Object>();if(StringUtils.isNotBlank(mName)){hql.append(" ");hql.append("and mName like :mName");params.put("mName", "%"+mName+"%");}if(StringUtils.isNotBlank(mCode)){hql.append(" ");hql.append("and mCode like :mCode");params.put("mCode", "%"+mCode+"%");}if(StringUtils.isNotBlank(mForShort)){hql.append(" ");hql.append("and mForShort like :mForShort");params.put("mForShort","%"+mForShort+"%");}if(StringUtils.isNotBlank(dishesName)){hql.append(" ");hql.append("and dishesName like :dishesName");params.put("dishesName", "%"+dishesName+"%");}List<Message> data=this.find(hql.toString(), params);return data;}

条件设置，返回List集合用作条件导出。

0 0