Excel导出数据库字段,Excel中带图片

 /**
    * 导出excel
    *
    * @param request
    * @param hmInfo
    */
    @RequestMapping(value = "/exportXls")
    public void exportXls(HttpServletRequest request, HttpServletResponse response,
						  HmInfo hmInfo) throws IOException{

		try {
			int a = 0;
			int s = 1;

			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet();
			Date date = new Date();
			String dateString = DateUtils.formatDate(date);

			// 设置要导出的文件的名字
			String fileName = "电能表更换表单" + dateString + ".xls";

			// 新增数据行，并且设置单元格数据
			int rowNum = 1;
			//这里是你要导出表格第一行的标题，要几个自己加上去
			String[] headers = { "标题","旧表条码", "表计类型", "剩余余额照片", "剩余余额", "有功总照片", "有功总示数", "有功峰照片", "有功峰示数", "有功平照片",
					"有功平示数","有功谷照片","有功谷示数","新表条码","新表照片","提交人","提交时间"};
			/*String[] headers = { "旧表条码", "表计类型", "余额照片"};*/
			// headers表示excel表中第一行的表头
			HSSFRow row = sheet.createRow(0);

			// 在excel表中添加表头
			for (int i = 0; i < headers.length; i++) {
				HSSFCell cell = row.createCell(i);
				HSSFRichTextString text = new HSSFRichTextString(headers[i]);
				cell.setCellValue(text);
			}
			// 单独设置列宽，第一个参数代表列id(从0开始),第2个参数代表宽度值
			sheet.setColumnWidth(9, 4300);
			sheet.setColumnWidth(5, 7000);
			sheet.setColumnWidth(4, 4800);
			sheet.setColumnWidth(3, 4300);
			sheet.setColumnWidth(2, 4300);
			sheet.setColumnWidth(8, 5000);
			sheet.setColumnWidth(7, 7000);
			//数据库查询出数据
			LoginUser user = (LoginUser) SecurityUtils.getSubject().getPrincipal();
			if (!user.getUsername().equals("admin")){
				hmInfo.setUserid(user.getId());
			}
			List<HmInfo> list = hmInfoService.findList(hmInfo);
			List<HmInfo> exportList = null;


			// 过滤选中数据
			String selections = request.getParameter("selections");
			if (oConvertUtils.isNotEmpty(selections)) {
				List<String> selectionList = Arrays.asList(selections.split(","));
				exportList = list.stream().filter(item -> selectionList.contains(getId(item))).collect(Collectors.toList());
			} else {
				exportList = list;
			}

			// 在表中存放查询到的数据放入对应的列
			for (HmInfo hmInfo1 : exportList) {
				HSSFRow row1 = sheet.createRow(rowNum);
				// 设置行高
				row1.setHeight((short) 1500);
				//第1列
				row1.createCell(0).setCellValue(hmInfo1.getTitle());
				//第2列
				row1.createCell(1).setCellValue(hmInfo1.getOldBarcode());
				//第3列
				row1.createCell(2).setCellValue(hmInfo1.getType());
				//row1.createCell(2).setCellValue(hmInfo1.getPowerfile());

				/* 图片处理，数据库存图片路径，如果导出的时候在服务器没有该图片，
				则导出时候显示的我们自己默认的图片nullPhoto.jpg，
				如果默认图片也不存在，则导出文字。相应逻辑可以自己修改。
				如果不需要导出图片，删掉这部分即可*/
				//第4列(图片)
				File path = new File(ResourceUtils.getURL("classpath:").getPath());
				File file;
				file = new File("D://opt//upFiles//"+hmInfo1.getYuefile());
				setPhoto(workbook, sheet, a+3, file,s);
				//第5列
				row1.createCell(4).setCellValue(hmInfo1.getYue().toString());
				//第6列(图片)
				path = new File(ResourceUtils.getURL("classpath:").getPath());
				file = new File("D://opt//upFiles//"+hmInfo1.getPowerfile());
				setPhoto(workbook, sheet, a+5, file,s);
				//第7列
				row1.createCell(6).setCellValue(hmInfo1.getPower().toString());
				//第8列(图片)
				path = new File(ResourceUtils.getURL("classpath:").getPath());
				file = new File("D://opt//upFiles//"+hmInfo1.getPowerffile());
				setPhoto(workbook, sheet, a+7, file,s);
				//第9列
				row1.createCell(8).setCellValue(hmInfo1.getPowerf().toString());
				//第10列(图片)
				path = new File(ResourceUtils.getURL("classpath:").getPath());
				file = new File("D://opt//upFiles//"+hmInfo1.getPowerpfile());
				setPhoto(workbook, sheet, a+9, file,s);
				//第11列
				row1.createCell(10).setCellValue(hmInfo1.getPowerp().toString());
				//第12列(图片)
				path = new File(ResourceUtils.getURL("classpath:").getPath());
				file = new File("D://opt//upFiles//"+hmInfo1.getPowergfile());
				setPhoto(workbook, sheet, a+11, file,s);
				//第13列
				row1.createCell(12).setCellValue(hmInfo1.getPowerg().toString());
				//第14列
				row1.createCell(13).setCellValue(hmInfo1.getNewBarcode());
				//第15列(图片)
				path = new File(ResourceUtils.getURL("classpath:").getPath());
				file = new File("D://opt//upFiles//"+hmInfo1.getNewbfile());
				setPhoto(workbook, sheet, a+14, file,s);
				//第16列
				row1.createCell(15).setCellValue(hmInfo1.getCreateBy());
				//第17列
				Date time = hmInfo1.getCreateTime();
				SimpleDateFormat sdf = new SimpleDateFormat( " yyyy年MM月dd日 " );
				String str = sdf.format(time);
				row1.createCell(16).setCellValue(str);
				rowNum++;
				s++;

			}

			// 写入excel文件
			// 设置生成的Excel的文件名，并以中文进行编码
			/*response.reset();
			String codedFileName = new String(("哈密电能表更换表单" + dateString).getBytes("gbk"), "iso-8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
			// 响应类型,编码
			response.setContentType("application/json;charset=UTF-8");*/
			// 形成输出流
			OutputStream osOut = response.getOutputStream();
			// 将指定的字节写入此输出流
			workbook.write(osOut);
			// 刷新此输出流并强制将所有缓冲的输出字节被写出
			osOut.flush();
			// 关闭流
			osOut.close();
			/*
			 * dispose of temporary files backing this workbook on disk 处理在磁盘上备份此工作簿的临时文件
			 * SXSSF分配临时文件，您必须始终清除显式，通过调用dispose方法
			 */

		} catch (Exception e) {
			e.printStackTrace();
			response.sendRedirect("error.action");
		}

    }

	 /**
	  * @Title: @Description: excel生成图片工具 @param @return @throws
	  */
	 public void setPhoto(HSSFWorkbook workbook, HSSFSheet sheet, int a, File file,int s) {
		 HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		 BufferedImage bufferImg = null;
		 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

		 try {
			 bufferImg = ImageIO.read(file);
//				bufferImg = ImageIO.read(new File(ClassUtils.getDefaultClassLoader().getResource("static").getPath()
//				+ temperatureRecord.getPhotoUrl()));
			 ImageIO.write(bufferImg, "jpg", byteArrayOut);
			 /*关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
				dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
				dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
				dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
				dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
				col1：起始单元格列序号，从0开始计算；
				row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；
				col2：终止单元格列序号，从0开始计算；
				row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；*/
			 // anchor主要用于设置图片的属性
			 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) a, s, (short) a, s );
			 anchor.setAnchorType(3);
			 // 插入图片
			 patriarch.createPicture(anchor,
					 workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//				System.out.println("----Excle文件已生成------");
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
	 }