java导出excel带图片-封装，vo转map

最新推荐文章于 2024-05-29 15:29:20 发布

早起傻一天～G

最新推荐文章于 2024-05-29 15:29:20 发布

阅读量143

点赞数

文章标签： java javascript 开发语言

本文链接：https://blog.csdn.net/sinat_24328329/article/details/126277820

版权

	/**
	 *  导出
	 *
	 * @param list
	 * @return
	 */
	public void excelDisposalCompanyStaffImg(List<DisposalCompanyStaffExcel> list, HttpServletResponse response){
		// 获取数据列表
		// 创建一个工作簿，对应文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 创建一个sheet工作表
		XSSFSheet sheet = workBook.createSheet(ExcelConstant.EXPORT_DISPOSAL_COMPANY_STAFF_NAME + DateUtil.time());
		// 设置表头单元格样式
		XSSFCellStyle headstyle = workBook.createCellStyle();
		// 设置居中
		headstyle.setAlignment(HorizontalAlignment.CENTER);
		headstyle.setVerticalAlignment(VerticalAlignment.CENTER);
		XSSFFont headFont = workBook.createFont();
		headFont.setFontHeight(14);
		headFont.setBold(true);
		headstyle.setFont(headFont);

		// 创建一般单元格样式
		XSSFCellStyle cellstyle = workBook.createCellStyle();
		cellstyle.setAlignment(HorizontalAlignment.CENTER);
		cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellstyle.setWrapText(true);
		XSSFFont cellFont = workBook.createFont();
		cellFont.setFontHeight(11);
		cellstyle.setFont(cellFont);
		// 创建表头
		String[] excelHeaders = {"公司", "名称", "性别", "年龄", "联系电话", "二维码", "二维码值", "职位", "备注"};
		String[] keys = {"companyName", "name", "sex", "age", "phone", "qrcodeUrl", "qrcodeValue", "position", "remarks"};
		XSSFRow headRow = sheet.createRow(0);
		for (int i = 0; i < excelHeaders.length; i++) {
			XSSFCell cell = headRow.createCell(i);
			cell.setCellValue(excelHeaders[i]);
			cell.setCellStyle(headstyle);
			sheet.setColumnWidth(i, (20 * 256));
		}
		// 创建内容
		XSSFRow row = null;
		DisposalCompanyStaffExcel item = null;
		for (int rowIndex = 0; rowIndex < list.size(); rowIndex++) {
			row = sheet.createRow(rowIndex + 1);
			row.setHeight((short) (40 * 20));
			// 单元格
			XSSFCell cell = null;
			String s = JSONObject.toJSONString(list.get(rowIndex));
			// 转成map
			Map<String,Object> map = JSONObject.parseObject(s, HashMap.class);
			for (int j = 0; j < keys.length; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(cellstyle);
				if(j == 5){
					cell.setCellValue("");
					try{
						URL photoFile = new URL( (String) map.get(keys[j]));
						// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
						ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
						//将图片读入BufferedImage对象
						BufferedImage bufferImg = ImageIO.read(photoFile);
						// 将图片写入流中
						ImageIO.write(bufferImg, "jpg", byteArrayOut);
						// 利用HSSFPatriarch将图片写入EXCEL
						XSSFDrawing patriarch = sheet.createDrawingPatriarch();
						// 图片一导出到单元格I3-5中 列开始：8 行开始：2 列结束：9 行结束：5
						XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, rowIndex + 1, j, rowIndex + 1);
						anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
						// 插入图片内容
						Picture picture =  patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut
							.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
						picture.resize(1.05,1.10);
						//将图片插入工作表
					}catch (Exception e) {
						cell.setCellValue("暂无图片");
					}
				}else{
					cell.setCellValue((String) map.get(keys[j]));
				}
			}
		}
		try {
			//最终已流的形式返回
			OutputStream out = null;
			out = response.getOutputStream();
			response.setHeader("content-type", "application/octet-stream");
			response.setContentType("application/octet-stream");
			response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(ExcelConstant.EXPORT_DISPOSAL_COMPANY_STAFF_NAME + DateUtil.time() + ".xlsx", "UTF-8"));
			workBook.write(out);
			out.flush();
			out.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}