导出excel

来源：互联网发布：网络交易管理办法2017 编辑：程序博客网时间：2024/06/05 15:41

jsp：<td style="vertical-align:top;"><a class="btn btn-mini btn-light" style="margin-bottom: 5px;" onclick="toExcel();" title="导出到EXCEL">

java：

            /*/
       * 导出excel
       */
       @RequestMapping(value="/doexcel")
       public ModelAndView doexcel(){
           ModelAndView mv=new ModelAndView();
           PageData pd=new PageData();
           pd=this.getPageData();
           try {
               Map< String , Object > map=new HashMap<>();
               List<String > titles=new ArrayList<String>();

//获得表的标题

             titles.add("姓名");
               titles.add("学号");
               titles.add("入学年份");
               titles.add("毕业年份");
               titles.add("性别");
               titles.add("院");
               titles.add("系（部）");
               titles.add("专业");
               titles.add("班级");
               map.put("titles", titles);

//获得学生数据

                List<PageData> student=studentService.getStudentAll(pd);
               List<PageData > varList=new ArrayList<PageData>();
               for(PageData list : student){
                   PageData var=new PageData();

//把相应的数据放入相应的标题里

                    var.put("var1", list.getString("name"));
                   var.put("var2", list.getString("studentId"));
               /*   var.put("var3", list.getString("name"));
                   var.put("var4", list.getString("name"));*/
                   var.put("var5", list.getString("sex"));
                   /*var.put("var6", list.getString("collegeId"));*/
                   var.put("var7", list.getString("collegeName"));
                   var.put("var8", list.getString("majorName"));
                   var.put("var9", list.getString("className"));
                   varList.add(var);
               }
               map.put("varList", varList);

//ObjectsExcelView是写好的工具类

                ObjectsExcelView erv = new ObjectsExcelView();
               mv = new ModelAndView(erv,map);
           } catch (Exception e) {
               // TODO: handle exception
               logger.error(e.toString(), e);
           }
           return mv;
       }

//工具类

public class ObjectsExcelView extends AbstractExcelView{
   private String[] sheetNames;
   public String[] getSheetNames() {
       return sheetNames;
   }
   public void setSheetNames(String[] sheetNames) {
       this.sheetNames = sheetNames;
   }
   @Override
   protected void buildExcelDocument(Map<String, Object> model,
           HSSFWorkbook workbook, HttpServletRequest request,
           HttpServletResponse response) throws Exception {
       // TODO Auto-generated method stub
       Date date = new Date();
       String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
       response.setContentType("application/octet-stream");
       response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");

           HSSFSheet sheet;
           HSSFCell cell;
           sheet = workbook.createSheet("sheet1");
           List<String> titles = (List<String>)model.get("titles");

           int len = titles.size();
           HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
           headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
           headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
           HSSFFont headerFont = workbook.createFont();   //标题字体
           headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
           headerFont.setFontHeightInPoints((short)11);
           headerStyle.setFont(headerFont);
           short width = 20,height=25*20;
           sheet.setDefaultColumnWidth(width);
           for(int i=0; i<len; i++){ //设置标题
               String title = titles.get(i);
               cell = getCell(sheet, 0, i);
               cell.setCellStyle(headerStyle);
               setText(cell,title);
           }
           sheet.getRow(0).setHeight(height);

           HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
           contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
           List<PageData> varList = (List<PageData>) model.get("varList");
           int varCount = varList.size();
           for(int i=0; i<varCount; i++){
               PageData vpd = varList.get(i);
               for(int j=0;j<len;j++){
                   String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
                   cell = getCell(sheet, i+1, j);
                   cell.setCellStyle(contentStyle);
                   setText(cell,varstr);
               }

           }


   }

}

阅读全文

0 0