SSM框架 Excel导出(Spring+SpringMVC+Mybatis)

心已赠人 2022-01-29 15:53 347阅读 0赞

# SSM框架 (Spring+SpringMVC+Mybatis) Excel导出 #

### 实现从数据库(MySql)查寻数据，写入excle中并导出 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd4dTk3_size_16_color_FFFFFF_t_70]

#### 第一种方式（推荐） ####

<不会再项目下生成多于文件夹，代码更简便>

// 导出图书  
    	@RequestMapping(value = "/outPutBook/{ids}", method = RequestMethod.GET)
    	public void
    	outPutBook(@PathVariable(value = "ids") String ids1,HttpServletResponse response) throws IOException {
        
    		 
    		List<BookAndFenlei> list = null;
    		String key = "";
    		if (ids1.equals("a")) {
        //传入a 表示导出全部
    			
    			list = this.bookService.outPutBookAll();
    			key = "全部";
    
    		}else{
         
    			//System.out.println(ids1);
    			list = this.bookService.outPutBookIds(ids1);
    			key = "勾选";
    
    		}
    		//创件一个工作蒲
    		HSSFWorkbook Workbook = new HSSFWorkbook();
    		//创建一个工作表
    		HSSFSheet sheet = Workbook.createSheet(key + "图书信息表");
              
    		sheet.setColumnWidth(7, 15 * 256); //设定列宽度
    		//设置样式
    		HSSFCellStyle style = Workbook.createCellStyle();
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		HSSFFont font = Workbook.createFont();
    		font.setBold(true);
    		font.setColor(HSSFColor.DARK_RED.index);
    		style.setFont(font);
    		String[] title = {
         "编号", "分类编号", "图书名", "图书价格", "出版社", "作者", "库存" };
    		HSSFRow row = sheet.createRow(0);//从0开始
    		for (int i = 0; i < title.length; i++) {
        
    			HSSFCell cell = row.createCell(i);
    			cell.setCellStyle(style);
    			cell.setCellValue(title[i]);
    		}
    		HSSFCellStyle style1 = Workbook.createCellStyle();
    		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
    		// 设置字体样式
    		for (int i = 0; i < list.size(); i++) {
         
    
    			HSSFRow row1 = sheet.createRow(i + 1);
    			BookAndFenlei book = list.get(i);
    
    			HSSFCell cell1 = row1.createCell(0);
    			cell1.setCellValue(book.getBid());
    
    			HSSFCell cell2 = row1.createCell(1);
    			cell2.setCellValue(book.getFenlei().getFname());
    
    			HSSFCell cell3 = row1.createCell(2);
    			cell3.setCellValue(book.getBname());
    
    			HSSFCell cell4 = row1.createCell(3);
    			cell4.setCellValue(book.getMoney());
    
    			HSSFCell cell5 = row1.createCell(4);
    			cell5.setCellValue(book.getPress());
    
    			HSSFCell cell6 = row1.createCell(5);
    			cell6.setCellValue(book.getAuthor());
    
    			HSSFCell cell7 = row1.createCell(6);
    			cell7.setCellValue(book.getStock());
    
    			cell1.setCellStyle(style1);
    			cell2.setCellStyle(style1);
    			cell3.setCellStyle(style1);
    			cell4.setCellStyle(style1);
    			cell5.setCellStyle(style1);
    			cell6.setCellStyle(style1);
    			cell7.setCellStyle(style1);
    
    		}
    		 
    		 String fname = key +"图书信息表.xls"; 
    		response.setContentType("application/octet-stream");
    		response.setHeader("Content-disposition", "attachment;filename="+new String(fname.getBytes("UTF-8"), "iso-8859-1"));
    		// new String(fname.getBytes("UTF-8"), "iso-8859-1") 解决中文名称乱码问题
    		response.flushBuffer();
    		 Workbook.write(response.getOutputStream());
    		 
    	}

#### 第二种方式 ####

＜可能会在项目下生成多的文件夹＞

// 导出excle   Handler里代码
    	@RequestMapping(value = "/outPutBook/{ids}", method = RequestMethod.GET)
    	public  ResponseEntity<byte[]> 
    	outPutBook(@PathVariable(value = "ids") String ids1, HttpServletRequest request,
    			HttpSession session) throws IOException  {
        
    		 
    		List<BookAndFenlei> list = null;
    		String key = "";
    		if (ids1.equals("a")) {
        //传入a 表示导出全部
    			
    			list = this.bookService.outPutBookAll();
    			key = "全部";
    
    		}else{
         
    			//System.out.println(ids1);
    			list = this.bookService.outPutBookIds(ids1);
    			key = "勾选";
    
    		}
    		//创件一个工作蒲
    		HSSFWorkbook Workbook = new HSSFWorkbook();
    		//创建一个工作表
    		HSSFSheet sheet = Workbook.createSheet(key + "图书信息表");
              
    		sheet.setColumnWidth(7, 15 * 256); //设定列宽度
    		//设置样式
    		HSSFCellStyle style = Workbook.createCellStyle();
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		HSSFFont font = Workbook.createFont();
    		font.setBold(true);
    		font.setColor(HSSFColor.DARK_RED.index);
    		style.setFont(font);
    		String[] title = {
         "编号", "分类编号", "图书名", "图书价格", "出版社", "作者", "库存" };
    		HSSFRow row = sheet.createRow(0);//从0开始
    		for (int i = 0; i < title.length; i++) {
        
    			HSSFCell cell = row.createCell(i);
    			cell.setCellStyle(style);
    			cell.setCellValue(title[i]);
    		}
    		HSSFCellStyle style1 = Workbook.createCellStyle();
    		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
    		// 设置字体样式
    		for (int i = 0; i < list.size(); i++) {
         //把list数据放入
    
    			HSSFRow row1 = sheet.createRow(i + 1);
    			BookAndFenlei book = list.get(i);
    
    			HSSFCell cell1 = row1.createCell(0);
    			cell1.setCellValue(book.getBid());
    
    			HSSFCell cell2 = row1.createCell(1);
    			cell2.setCellValue(book.getFenlei().getFname());
    
    			HSSFCell cell3 = row1.createCell(2);
    			cell3.setCellValue(book.getBname());
    
    			HSSFCell cell4 = row1.createCell(3);
    			cell4.setCellValue(book.getMoney());
    
    			HSSFCell cell5 = row1.createCell(4);
    			cell5.setCellValue(book.getPress());
    
    			HSSFCell cell6 = row1.createCell(5);
    			cell6.setCellValue(book.getAuthor());
    
    			HSSFCell cell7 = row1.createCell(6);
    			cell7.setCellValue(book.getStock());
    
    			cell1.setCellStyle(style1);
    			cell2.setCellStyle(style1);
    			cell3.setCellStyle(style1);
    			cell4.setCellStyle(style1);
    			cell5.setCellStyle(style1);
    			cell6.setCellStyle(style1);
    			cell7.setCellStyle(style1);
    
    		}
    		File f = new File(key +"图书信息表.xls");
    		 
    		OutputStream outputStream = new FileOutputStream(f);
    		// 把工作蒲的内容写入文件
    		Workbook.write(outputStream);
          
    		 
    		// 响应浏览器，
    	        HttpHeaders headers = new HttpHeaders();
    	        //下载显示的文件名，解决中文名称乱码问题
    	        String downloadFilelName = new String(f.getName().getBytes("UTF-8"), "iso-8859-1");
    	        //通知浏览器以attachment下载
    	        headers.setContentDispositionFormData("attachment", downloadFilelName);
    	        //application/octet-stream ： 二进制流数据 ,最常见的文件下载 
    	        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    	        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(f),
    	                headers, HttpStatus.CREATED);
          
    		 
    	}

//serviceImpl  @Service里代码
    @Override
    	@Transactional
    	public List<BookAndFenlei> outPutBookIds(String ids1) {
        
    		// TODO Auto-generated method stub
    		String[] a = ids1.split(","); List<Integer> list =new ArrayList<Integer>();
    	 for (int i = 0; i < a.length; i++) {
        
               
    			list.add(Integer.parseInt(a[i]));
    			 
    		}
    	return this.bookMapper.selectOutPutIds(list);
    }

//xxx.xml 里代码
      <select id="selectOutPutIds"  parameterType="list" resultMap="BookAndFenlei">	
    		select * from book,fenlei 
        
         <where>
    		book.flid=fenlei.fid and bid in
    		<foreach collection="list" item="id" open="(" separator="," close=")" >
    		     #{id}
    	 </foreach>
    	 
       </where> 
    		 order by book.bid desc
    	</select>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd4dTk3_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd4dTk3_size_16_color_FFFFFF_t_70]: /images/20220129/9f90eaff9a0d4c3c9431a9a83ff25b7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmd4dTk3_size_16_color_FFFFFF_t_70 1]: /images/20220129/6e4ec8da313341e58780ce051c3e6ecc.png