Java导出Excel和图片

版权声明:本文为博主原创文章,如需转载请指明出处。 https://blog.csdn.net/xujiangdong1992/article/details/78068760

由于项目需要,需要导出数据和二维码到Excel,其实二维码就是图片

我这里excel的布局要求是四列,每个单元由数据+二维码组成

 public String exportCode(String id,EquEquipmentDetails equ,HttpServletResponse response,HttpServletRequest request) {
    	String path;
    	if(id!=null) {//这里是根据ID获取数据来源,ID不存在   数据就是equ 
    		equ = equEquipmentDetailsService.selectByPrimaryKey(id);
    		equ.setPurchaseDate(convert(equ.getPurchaseDate()));
    		//预览二维码存放路径
    		path = request.getServletContext().getRealPath("/")+"static\\qrCode\\";
    	}
    	//自定义二维码存放路径
    	path = request.getServletContext().getRealPath("/")+"static\\customCode\\";
    	//装备数量
    	int  equipmentNum = Integer.valueOf(equ.getEquipmentNum()).intValue();

    	String beginName=school.getOrganizationno()+equ.getBatchNumber()+equ.getEquipmentCode();
    	//excel名称
    	String sheetName=equ.getEquipmentName()+"-标签二维码";
    	try {
			//创建工作薄
			/*Workbook wb = new SXSSFWorkbook(500);
			//创建工作表
			Sheet sheet = wb.createSheet(sheetName);
			Drawing patriarch = sheet.createDrawingPatriarch();*/
			
            // 创建一个工作薄  
            HSSFWorkbook wb = new HSSFWorkbook();  
            //创建一个sheet  
            HSSFSheet sheet = wb.createSheet();  
            
			CellStyle style = wb.createCellStyle();
	        style.setAlignment(CellStyle.ALIGN_CENTER);
	        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	        Font titleFont = wb.createFont();
	        titleFont.setFontName("Arial");
	        titleFont.setFontHeightInPoints((short) 16);
	        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	        style.setFont(titleFont);
	        //创建第一行
	        Row titleRow=sheet.createRow(0);
	        titleRow.setHeightInPoints(40);//设置行高
	        //创建单元格
	        Cell titleCell=titleRow.createCell(0);
	        titleCell.setCellValue(sheetName);
	        titleCell.setCellStyle(style);
	        //设置合并单元格
	        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,3));
	        int rowNum;//计算行数,规定一行4列
	        if(equipmentNum%4==0) {
	        	rowNum = equipmentNum/4;
	        }else {
	        	rowNum = equipmentNum/4+1;
	        }
	        int lastRowNum = equipmentNum%4;
	        //设置4列宽度
	        sheet.setColumnWidth(0, 42*256);
	        sheet.setColumnWidth(1, 42*256);
	        sheet.setColumnWidth(2, 42*256);
	        sheet.setColumnWidth(3, 42*256);
	        for(int i=0;i<rowNum;i++) {
	        	Row row = sheet.createRow(i+1);//创建行
	        	row.setHeightInPoints(100);//设置行高
	        	if(i==rowNum-1) {//最后一行时根据具体数量生成
		        		for(int j=0;j<lastRowNum;j++) {
		        			BufferedImage bufferImg=null;//图片
		        			HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
		                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 
		        			//创建单元格
		        			Cell cell=row.createCell(j);
		        			style = wb.createCellStyle();
		        			style.setWrapText(true);//设置自动换行
		        			//垂直居中
		        			style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		        			//设置边线
		        			style.setBorderBottom(CellStyle.BORDER_THIN);
		        			style.setBorderTop(CellStyle.BORDER_THIN);
		        			style.setBorderRight(CellStyle.BORDER_THIN);
		        			//设置单元边框颜色
		        			style.setTopBorderColor(HSSFColor.RED.index);
		        			style.setRightBorderColor(HSSFColor.RED.index);
		        			style.setBottomBorderColor(HSSFColor.RED.index);
		        			StringBuilder sb=new StringBuilder();
		        			sb.append("物品编号:"+equ.getEquipmentCode()+"-"+(4*i+j+1)+"\n");
		    	        	sb.append("物品名称:"+equ.getEquipmentName()+"\n");
		    	        	sb.append("购入日期:"+equ.getPurchaseDate());
		    	        	cell.setCellValue(sb.toString());
		    	        	cell.setCellStyle(style);
			    	        //获取二维码
			    	        bufferImg = ImageIO.read(new File(path+beginName+(4*i+j+1)+".jpg"));
			    	        System.out.println(path+beginName+(4*i+j+1)+".jpg");
			    	        ImageIO.write(bufferImg, "jpg", byteArrayOut);  
			    	        //插入Excel表格
			    	        HSSFClientAnchor anchor = new HSSFClientAnchor(500, 10,0, 0,  
			    	        		 (short) j, (i+1), (short)(j+1) , (i+2)); 
			                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut  
			                        .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
		        	 }
	        	  }else {
		        		for(int j=0;j<4;j++) {
		        			HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
		                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 
		        			//创建单元格
		        			Cell cell=row.createCell(j);
		        			style = wb.createCellStyle();
		        			style.setWrapText(true);//设置自动换行
		        			//垂直居中
		        			style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		        			//设置边线
		        			style.setBorderBottom(CellStyle.BORDER_THIN);
		        			style.setBorderTop(CellStyle.BORDER_THIN);
		        			style.setBorderRight(CellStyle.BORDER_THIN);
		        			//设置单元边框颜色
		        			style.setTopBorderColor(HSSFColor.RED.index);
		        			style.setRightBorderColor(HSSFColor.RED.index);
		        			style.setBottomBorderColor(HSSFColor.RED.index);
		        			StringBuilder sb=new StringBuilder();
		        			sb.append("物品编号:"+equ.getEquipmentCode()+"-"+(4*i+j+1)+"\n");
		    	        	sb.append("物品名称:"+equ.getEquipmentName()+"\n");
		    	        	sb.append("购入日期:"+equ.getPurchaseDate());
		    	        	cell.setCellValue(sb.toString());
		    	        	cell.setCellStyle(style);
		    	        	BufferedImage bufferImg=null;//图片
		    	        	 System.out.println(path+beginName+(4*i+j+1)+".jpg");
			    	        //获取二维码
			    	        bufferImg = ImageIO.read(new File(path+beginName+(4*i+j+1)+".jpg"));
			    	        ImageIO.write(bufferImg, "jpg", byteArrayOut); 
			    	         /** 
			                 * 该构造函数有8个参数 
			                 * 前四个参数是控制图片在单元格的位置,分别是图片距离单元格left,top,right,bottom的像素距离 
			                 * 后四个参数,前连个表示图片左上角所在的cellNum和 rowNum,后天个参数对应的表示图片右下角所在的cellNum和 rowNum, 
			                 * excel中的cellNum和rowNum的index都是从0开始的 
			                 *  
			                 */ 
			    	        //插入Excel表格
			    	        HSSFClientAnchor anchor = new HSSFClientAnchor(500, 10,0,0,  
			                        (short) j, (i+1), (short)(j+1) , (i+2)); 
			                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut  
			                        .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
		        	 }
	        	  }
	        }
			response.reset();
	        response.setContentType("application/octet-stream; charset=utf-8");
	        response.setHeader("Content-Disposition", "attachment; filename=" + new String(
	                (sheetName+LocalDate.now()+".xls").getBytes("utf-8"), "iso-8859-1"));
	        wb.write(response.getOutputStream()); 
		} catch (Exception e) {
			logger.debug(e.getMessage());
			e.printStackTrace();
		}
		return null;
    }

下篇文章将介绍二维码生成

阅读更多

没有更多推荐了,返回首页