工作记录之POI的excel导出多张图片和数据

效果图

代码

public void exportHeadlessFastener(String header, String body,HttpServletResponse response) throws Exception  {
		// 将数据放入map类型的集合中
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

                //接收页面参数
		RequestModel<HeadlessFastenerQueryParam> requestModel = RequestUtil.getRequestModel(header, body, HeadlessFastenerQueryParam.class);
		requestModel.getBody().setPage(1);
		requestModel.getBody().setPageCount(100000);
		PageResult<HeadlessFastenerQueryResult> res = headlessFastenerService.queryHeadlessFastener(requestModel.getBody());
		List<HeadlessFastenerQueryResult> list = res.getList();

                //设置表头
		String[] assetHeadTemp = { 
				"图片1", 
				"图片2", 
				"图片3", 
				"图片4", 
				"图片5", 
				"图片6", 
				"体积(m³)", 
				"重量(KG)", 
				"包装", 
				"发现环节",
				"车牌号",
				"交接单号 ",
				"库区",
				"登记网点",
				"登记人",
				"登记时间",
				"修改人",
				"修改时间",
				"审核人",
				"审核时间",
				"认领网点",
				"认领人",
				"认领运单号",
				"认领时间",
				"状态"
				};

                //设置参数名
		String[] assetNameTemp = { 
				"imageUrl1", 
				"imageUrl2", 
				"imageUrl3", 
				"imageUrl4", 
				"imageUrl5", 
				"imageUrl6", 
				"volume", 
				"weight", 
				"packagetTypeName", 
				"findlinkName", 
				"vehicleNumber", 
				"trackingNumber", 
				"reservoirRegion", 
				"registerStatName", 
				"createUserName", 
				"createDatetime", 
				"modifyUserName", 
				"modifyDatetime", 
				"auditUserName", 
				"auditDatetime", 
				"claimStatName", 
				"claimUserName", 
				"transportNumber", 
				"claimDatetime",
				"statusName" 
				};

                将数据填充到map中
		for(HeadlessFastenerQueryResult result : list){
			Map<String,Object> hashMap = new LinkedHashMap<String, Object>();
			hashMap.put("imageUrl1", result.getImageUrl1());
			hashMap.put("imageUrl2", result.getImageUrl2());
			hashMap.put("imageUrl3", result.getImageUrl3());
			hashMap.put("imageUrl4", result.getImageUrl4());
			hashMap.put("imageUrl5", result.getImageUrl5());
			hashMap.put("imageUrl6", result.getImageUrl6());
			hashMap.put("volume", result.getVolume());
			hashMap.put("weight", result.getWeight());
			hashMap.put("packagetTypeName", result.getPackagetTypeName());
			hashMap.put("findlinkName", result.getFindlinkName());
			hashMap.put("vehicleNumber", result.getVehicleNumber());
			hashMap.put("trackingNumber", result.getTrackingNumber());
			hashMap.put("reservoirRegion", result.getReservoirRegion());
			hashMap.put("registerStatName", result.getRegisterStatName());
			hashMap.put("createUserName", result.getCreateUserName());
			hashMap.put("createDatetime", result.getCreateDatetime());
			hashMap.put("modifyUserName", result.getModifyUserName());
			hashMap.put("modifyDatetime", result.getModifyDatetime());
			hashMap.put("auditUserName", result.getAuditUserName());
			hashMap.put("auditDatetime", result.getAuditDatetime());
			hashMap.put("claimStatName", result.getClaimStatName());
			hashMap.put("claimUserName", result.getClaimUserName());
			hashMap.put("transportNumber", result.getTransportNumber());
			hashMap.put("claimDatetime", result.getClaimDatetime());
			hashMap.put("statusName", result.getStatusName());
			dataList.add(hashMap);
		}
		XSSFWorkbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet("Sheet1");
		Row row;
		Cell cell;
		// 图片字节数组
		byte[] imgByte = null;
 
		// excel样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
 
		// 输出表头
		row = sheet.createRow(0);
		for (int i = 0; i < assetHeadTemp.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(assetHeadTemp[i]);
			cell.setCellStyle(style);
		}
		// 输出内容
		int rowIndex = 1;
		for (Map<String, Object> map : dataList) {
			row = sheet.createRow(rowIndex++);
			// 设置行高
			row.setHeightInPoints((short) 70);
 
			for (int i = 0; i < assetNameTemp.length; i++) {
				cell = row.createCell(i);
				// 设置列宽
				sheet.setColumnWidth(i, 256 * 25);
				// 输出图片到第1列 到 第六列
				if (i >= 0 && i <= 5) {
					imgByte = null;
					if(map.get(assetNameTemp[i]) == null){
						continue;
					}
					// 输出图片
					URL url = new URL(map.get(assetNameTemp[i]).toString());  
					HttpURLConnection conn = (HttpURLConnection)url.openConnection();  
			                //设置超时间为3秒
					conn.setConnectTimeout(3*1000);
					//防止屏蔽程序抓取而返回403错误
					conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
			 
					//得到输入流
					InputStream inputStream = conn.getInputStream();
					// 图片转化为字节数组
					imgByte = IOUtils.toByteArray(inputStream);
					if (imgByte != null) {
						// 图片存在即输出图片
						int addPicture = wb.addPicture(imgByte, wb.PICTURE_TYPE_JPEG);
						Drawing drawing = sheet.createDrawingPatriarch();
						CreationHelper helper = wb.getCreationHelper();
						ClientAnchor anchor = helper.createClientAnchor();
 
						anchor.setRow1(rowIndex - 1);
						anchor.setCol1(i);
 
						// 指定我想要的长宽
						double standardWidth = 150;
						double standardHeight = 100;
 
						// 计算单元格的长宽
						double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex());
						double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;
 
						// 计算需要的长宽比例的系数
						double a = standardWidth / cellWidth;
						double b = standardHeight / cellHeight;
 
						Picture picture = drawing.createPicture(anchor, addPicture);
						picture.resize(a, b);
					}
				} else {
					// 输出文字
					Object object = map.get(assetNameTemp[i]);
					
					if(i == 15 || i == 17 || i == 19 || i == 23){
						String dateString = null;
						if(object != null){
							 SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
						      dateString = sdf.format((Date)object);
						}
						cell.setCellValue(
								dateString != null ? dateString : "");
						/*CellStyle style2 = wb.createCellStyle();
						XSSFDataFormat createDataFormat = wb.createDataFormat();
						style2.setDataFormat(createDataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
						cell.setCellStyle(style2);*/
					}else{
						cell.setCellValue(object != null ? object.toString() : "");
					}
					cell.setCellStyle(style);
				}
			}
		}
		excelUtil.exporExcel(wb, response, "无头件导出");