java（SSM框架）导出数据到Excel

为什么80%的码农都做不了架构师？>>>   

 

Action.java

 //导出数据到excel
				 @RequestMapping(value = "exportExcel")
				 public String export(HttpServletResponse response) throws Exception{
					  response.setHeader("Content-Disposition","attachment;filename=stuInfo.xls");
				      response.setContentType("application/x-download; charset=utf-8");
				      
				      StudentExample studentExample = null;
					  List<Student> list = studentService.getStudents(studentExample);

			          
			        HSSFWorkbook workBook = new HSSFWorkbook();		//创建 一个excel文档对象
			      	HSSFSheet sheet = workBook.createSheet("aaa");			//创建一个工作薄对象
			      	
			      	sheet.setColumnWidth(3, 20 * 350);    ///设置第四列的高度与宽度
			      	
			      	//设置样式
			      	HSSFCellStyle titleStyle = workBook.createCellStyle();	//创建样式对象
			      	
			      	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);	//水平居中
			      	titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);	//垂直居中
			      	// 设置字体
			      	HSSFFont titleFont = workBook.createFont();			//创建字体对象
			      	titleFont.setFontHeightInPoints((short) 15);			//设置字体大小
			      	titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);	//设置粗体
			      	titleFont.setFontName("黑体");	//设置为黑体字
			      	
			      	titleFont.setColor(HSSFColor.BLUE.index);//HSSFColor.BLUE.index //字体颜色
			      	
			      	titleStyle.setFont(titleFont);
			      	
			      	// 合并单元格操作
			      	sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 4));
			      	
			      	HSSFRow row = null;
			      	HSSFCell cell = null;
			      	row = sheet.createRow(0);
			      	cell = row.createCell(0);
			      	
			      	row.setHeightInPoints(60);设置单元格的高度               设置的值永远是height属性值的60倍
			      	
			      	titleStyle.setWrapText(true);  设置自动换行   还有综合\n\r
			      	
			        设置背景颜色
			      	titleStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
			        solid 填充  foreground  前景色
			      	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			      	
			      	cell.setCellStyle(titleStyle);
			       
			      	做批注
			       ///创建绘图对象
			        HSSFPatriarch p=sheet.createDrawingPatriarch();
			       前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
			        HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
			        ///输入批注信息
			        comment.setString(new HSSFRichTextString("作者pkd:\r\n插件批注成功!插件批注成功!"));
			        添加作者,选中单元格,看状态栏
			        comment.setAuthor("pkd");
			        将批注添加到单元格对象中
			        cell.setCellComment(comment);
			   
			      	cell.setCellValue(new HSSFRichTextString("学生\r\n信息表"));// \r\n换行
			      
			      	// 设置表文样式
			      	HSSFCellStyle tableStyle = workBook.createCellStyle();
			      	
			      	tableStyle.setBorderBottom((short)1);
			      	tableStyle.setBorderTop((short)1);
			      	tableStyle.setBorderLeft((short)1);
			      	tableStyle.setBorderRight((short)1);
			      	tableStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			      	// 设置表文字体
			      	HSSFFont tableFont = workBook.createFont();
			      	tableFont.setFontHeightInPoints((short) 12); 		//设置字体大小
			      	tableFont.setFontName("宋体"); 				//设置为黑体字
			      	tableStyle.setFont(tableFont);
			      	
			      	String[] title = {"id","姓名","年龄","日期","sid"};
			      	
			      	row = sheet.createRow(2);
			      	
			    	for (int i = 0; i < title.length; i++) {
			    		cell = row.createCell(i);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString(title[i]));
			    	}
			    	
			    	for (int i = 0; i < list.size(); i++) {
			    		row = sheet.createRow(i+3);
			    		Student stu = list.get(i);
			    		
			    		cell = row.createCell(0);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString("\n"+stu.gettId().toString()));
			    		
			    		cell = row.createCell(1);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString(stu.gettName()));
			    		
			    		cell = row.createCell(2);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString("\n"+stu.gettAge().toString()));
			    		
			    		cell = row.createCell(3);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString(stu.gettEnterdate().toLocaleString()));
			    		
			    		cell = row.createCell(4);
			    		cell.setCellStyle(tableStyle);
			    		cell.setCellValue(new HSSFRichTextString("\n"+stu.gettSid().toString()));  ///  去掉单元格左上角有一个绿色三角形 "\n"+ 
		

			    	}
			    	
			    	HSSFSheet sheet1 = workBook.createSheet("bbb");			//创建一个工作薄对象
			    	
			    	// 文件输出流
			    	workBook.write(response.getOutputStream()); 		//将文档对象写入文件输出流
			      	
					return null;
				  }
				 

 

法2：

 /**
     * 导出报表
     * @return
     */
    @RequestMapping(value = "/export")
    @ResponseBody
    public void export(Worker worker,HttpServletRequest request,HttpServletResponse response) throws Exception {
    	
    }
           //获取数据
           List<PageData> list = workerService.findPageData(worker);

           //excel标题
        　　String[] title = {"名称","性别","年龄","学校","班级"};

         　//excel文件名
        　 String fileName = "学生信息表"+System.currentTimeMillis()+".xls";

  　　　　　//sheet名
        　 String sheetName = "学生信息表";

　　　　　　for (int i = 0; i < list.size(); i++) {
            content[i] = new String[title.length];
            PageData obj = list.get(i);
            content[i][0] = obj.get("stuName").tostring();
            content[i][1] = obj.get("stuSex").tostring();
            content[i][2] = obj.get("stuAge").tostring();
            content[i][3] = obj.get("stuSchoolName").tostring();
            content[i][4] = obj.get("stuClassName").tostring();
　　　　　　}

　　　　　　//创建HSSFWorkbook 
　　　　　　HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);

　　　　　　//响应到客户端
　　　　　　try {
　　　　　　　　this.setResponseHeader(response, fileName);
       　　　　OutputStream os = response.getOutputStream();
       　　　　wb.write(os);
       　　　　os.flush();
       　　　　os.close();
 　　　　　　} catch (Exception e) {
       　　　　e.printStackTrace();
 　　　　　　}
　　}

    //发送响应流方法
    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();
        }
    }

 

转载于:https://my.oschina.net/8824/blog/2875965