Collect from 企业网站模板

poi导入图片

第一次登录送5个金币,绑定QQ送5个金币。金币可以用来观看视频和下载资源哦!

前台读取excel,通过工具类存入数据到数据库,主要是读取图片保存到数据库

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
	public String importExcel(
			@RequestParam(value = "file", required = true) MultipartFile file,
			HttpServletRequest request, HttpServletResponse response, Model m,
			HttpSession session, String state) {
		m.addAttribute("state", state);

		String role = (String) session.getAttribute("role");

		DyBranch branch = null;

		Map<String, DyCollege> college_maps = new HashMap<String, DyCollege>();
		Map<String, DyMajor> major_maps = new HashMap<String, DyMajor>();
		Map<String, DyClass> class_maps = new HashMap<String, DyClass>();
		Map<String, DyUsers> ur_maps = new HashMap<String, DyUsers>();
		Map<String, DyUsers> dy_num_maps = new HashMap<String, DyUsers>();

		if (role.equals("maner")) {
			DyUsers lu = (DyUsers) session.getAttribute("adminU");
			branch = branchService.getById(lu.getBranch_id());

			List<DyCollege> collegeli = collegeService.list(null);
			for (DyCollege mm : collegeli) {
				college_maps.put(mm.getCname(), mm);
			}

			List<DyMajor> majorli = majorService.list(null);
			for (DyMajor mm : majorli) {
				major_maps.put(mm.getMname(), mm);
			}

			List<DyClass> classli = classService.list(null);
			for (DyClass mm : classli) {
				class_maps.put(mm.getClname(), mm);
			}
		} else {
			m.addAttribute("msg", "你暂无该权限");
			return "forward:/admin/users/toimportExcel";
		}

		String msg = "";

		// 创建处理EXCEL的类
		ReadUsersExcel readExcel = new ReadUsersExcel();
		// 解析excel,获取上传的事件单
		List<DyUsers> sli = readExcel.getExcelInfo(file, state);

		if (sli == null || sli.size() == 0) {
			m.addAttribute("msg", msg + "<br>数据导入失败,请检查你的excel是否符合要求");
			return "forward:/admin/users/toimportExcel";

		}
		System.out.println("====================>>>" + sli.size());

		List<DyUsers> estuli = usersService.list(null);
		Map<String, DyUsers> eusers_maps = new HashMap<String, DyUsers>();
		for (DyUsers mm : estuli) {
			eusers_maps.put(mm.getStu_num(), mm);
			ur_maps.put(mm.getRealname(), mm);
			if(state.equals("1")&&mm.getDy_num()!=null)dy_num_maps.put(mm.getDy_num(), mm);
		}

		List<DyUsers> succli = new ArrayList<DyUsers>();
		// 至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,

		for (DyUsers stu : sli) {

			System.out.println("-------------");
			System.out.println(stu.toString());
			
			if (state.equals("1")) {
				int notcand=0;
				String dy_num=stu.getDy_num() == null ? "" : stu.getDy_num();
				String stu_num = stu.getStu_num() == null ? "" : stu.getStu_num();
				
				if (dy_num != null && dy_num.length() > 0) {
					DyUsers th2 = dy_num_maps.get(stu.getDy_num());
					 if(th2!=null){
						 msg += "导入失败:" + stu.getDy_num()
									+ stu.getRealname() + "党员编号重复,请手动添加或者删除后重新导入<br>";
						 notcand++;
					 }else{
						 stu.setUname(stu.getDy_num());
						 
						 if (stu_num != null && stu_num.length() > 0) {
								if(!stu_num.equals("000000")){
									DyUsers th = eusers_maps.get(stu_num);
									if (th != null) {
										msg += "导入失败:" + stu.getStu_num()
												+ stu.getRealname() + "学号重复,请手动添加或者删除后重新导入<br>";
										notcand++;
									}else{
										stu.setUname(stu_num);
									} 
								}  
							}
						 
					 }
				} else{
					notcand++;
				}
				
				if(notcand==0){
					stu.setBranch_id(branch.getId());
					stu.setBname(branch.getBname());
					
					if(college_maps!=null&&!college_maps.isEmpty()){
						DyCollege c = college_maps.get(stu.getCollege());
						if (c != null)
							stu.setCollege_id(c.getId());
					}
				
					if(major_maps!=null&&!major_maps.isEmpty()){
						DyMajor mj = major_maps.get(stu.getMajor());
						if (mj != null)
							stu.setMajor_id(mj.getId());
					}
					
					if(class_maps!=null&&!class_maps.isEmpty()){
						DyClass cl = class_maps.get(stu.getClass_name());
						if (cl != null)
							stu.setClass_id(cl.getId());
					}
					

					DyUsers ur1 = ur_maps.get(stu.getDy_references_realname1()
							);
					DyUsers ur2 = ur_maps.get(stu.getDy_references_realname2()
							);
					if (ur1 == null && ur2 == null) {
						stu.setForm_("1");
					} else {
						stu.setForm_("0");
						if (ur1 != null) {
							stu.setReferences_uid1(ur1.getId());
							stu.setReferences_realname1(ur1.getRealname());
						}
						if (ur2 != null) {
							stu.setReferences_uid2(ur2.getId());
							stu.setReferences_realname2(ur2.getRealname());
						}
						stu.setDy_references_realname1("");
						stu.setDy_references_realname2("");

					}

					stu.setUname(stu_num);
					stu.setUpass(MD5.get(Sys.init_pwd));
					stu.setIs_branch_loader("0");
					stu.setState(state);

					if (stu.getByte_() != null) {

						String img = UploadFile.upimgFromExcel(request,
								stu.getByte_(), stu.getFormat(),
								Sys.Upimg.user_photo);
						stu.setPhoto(img);
					}
					
					usersService.add(stu);
					succli.add(stu);
				}
				
			} 

		}// for

		if (msg.trim().length() > 0) {
			m.addAttribute("msg", msg);
		}
		if (succli != null && succli.size() > 0) {
			m.addAttribute("succli", succli);

		} else {
			if (msg.trim().length() > 0)
				m.addAttribute("msg", msg + "<br>数据导入失败");
			else
				m.addAttribute("msg", msg + "<br>数据导入失败,请检查你的excel是否符合要求");
			return "forward:/admin/users/toimportExcel";
		}
		return "forward:/admin/users/toimportExcel";
	}

工具

 package s.jf3q.com.untils;

 

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;

import s.jf3q.com.entity.DyUsers;



/**
 *
 * @author hewangtong
 *
 */
public class ReadUsersExcel {
    // 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    // 构造方法
    public ReadUsersExcel() {
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 读EXCEL文件,获取信息集合
     *
     * @param fielName
     * @return
     */
    public List<DyUsers> getExcelInfo(MultipartFile mFile,String state) {
        String fileName = mFile.getOriginalFilename();// 获取文件名
 
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            return createExcel(mFile.getInputStream(), isExcel2003,state);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public List<DyUsers> createExcel(InputStream is, boolean isExcel2003,String state) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb,state,isExcel2003);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb
     * @return
     */
    private List<DyUsers> readExcelValue(Workbook wb,String state,boolean isExcel2003) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        
        List<DyUsers> tli = new ArrayList<DyUsers>();
        // 循环Excel行数
        boolean flag=true;
        
        List<Map<String, Object>> list = new ArrayList();
        int firstRowIndex = sheet.getFirstRowNum();
        int lastRowIndex = sheet.getLastRowNum();
    	
        for (int r = 1; r < totalRows && flag; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Map<String, Object> map = new HashMap(); 
            
            int firstCellIndex = row.getFirstCellNum();
            int lastCellIndex = row.getLastCellNum();
            int cIndex = firstCellIndex;
             Map<String, PictureData> sheetIndexPicMap;
            // 判断用07还是03的方法获取图片
            if (isExcel2003 ) {
                sheetIndexPicMap = getSheetPictrues03(r, (HSSFSheet) sheet, (HSSFWorkbook) wb);

            }else{
                sheetIndexPicMap = getSheetPictrues07(r, (XSSFSheet) sheet, (XSSFWorkbook) wb);
            }
            
            // 循环Excel的列
            DyUsers t = new DyUsers();
            
            if(sheetIndexPicMap.get(r+"_0")!=null&&!"".equals(sheetIndexPicMap.get(r+"_0"))){
            	PictureData pictureData=sheetIndexPicMap.get(r+"_0");
                map.put("photo",pictureData.getData());
                map.put("mimeType",pictureData.getMimeType().replace("image/",""));
                map.put("p_row",r);
                
                t.setByte_(pictureData.getData());
                t.setFormat(pictureData.getMimeType().replace("image/",""));
               
            }
       
                 
            
            
          
            for (int c = 0; c < this.totalCells && flag ; c++) {
                Cell cell = row.getCell(c);
                
                 
               
                
                if (null != cell) {
                	if(state.equals("1")){
                		cell.setCellType(Cell.CELL_TYPE_STRING);
                    	String lm=sheet.getRow(0).getCell(c).getStringCellValue(); 
                    	System.out.println(lm); 
                       
                        if (lm.indexOf("党员编号")>-1){
                        	if( cell.getStringCellValue()==null || cell.getStringCellValue().isEmpty() ) {
                        		flag=false;
                        		break;
                        	}
                        	t.setDy_num( cell.getStringCellValue()==null?"":cell.getStringCellValue().trim());
                        } 
                        if (lm.indexOf("学号")>-1){
                        	t.setStu_num( cell.getStringCellValue()==null?"":cell.getStringCellValue().trim()); 
                        }  
                        
                        
                	}else{
                		cell.setCellType(Cell.CELL_TYPE_STRING);
                    	String lm=sheet.getRow(0).getCell(c).getStringCellValue(); 
                        if (lm.indexOf("学号")>-1){
                        	if( cell.getStringCellValue()==null || cell.getStringCellValue().isEmpty() ){
                        		flag=false;
                        		break;
                        	}
                        	t.setStu_num( cell.getStringCellValue()==null?"":cell.getStringCellValue().trim()); 
                        }
                        if (lm.indexOf("姓名")>-1) {
                        	t.setRealname( cell.getStringCellValue()==null?"":cell.getStringCellValue().trim());// 名称
                        }
                        
                    		 
   
                }
            }
            
            tli.add(t);
        }
        return tli;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述:是否是2003的excel,返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述:是否是2007的excel,返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
    
    
    /**
     * 获取Excel2003图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引(0_1)String,value:图片流PictureData
     * @throws IOException
     */
    public static  Map<String, PictureData> getSheetPictrues03(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    if(sheetNum==anchor.getRow1()){
                        sheetIndexPicMap.put(picIndex, picData);
                        
                        System.out.println(picIndex);
                        System.out.println(pic.getPictureData());
                        return sheetIndexPicMap;
                    }
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2007图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引(0_1)String,value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
                    if(sheetNum==ctMarker.getRow() ){
                        sheetIndexPicMap.put(picIndex,pic.getPictureData());
                        System.out.println(picIndex);
                        System.out.println(pic.getPictureData());
 
                        return sheetIndexPicMap;
                    }
                }
            }
        }
        return sheetIndexPicMap;
    }
    
    

}

image.png

上传保存图片到磁盘的工具类

  public static String upimgFromExcel(HttpServletRequest request,byte[] byte_,String format, String package1 ) {
    	 String fileName = DateUtils.DateToString(new Date(), "yyyyMMddHHmmss")+UUID.randomUUID().toString().replace("-", "").substring(1, 11)+"."+format;  
    	 String abPath= request.getSession().getServletContext().getRealPath("")+Sys.Upimg.absolute_path+package1;
    	 abPath=abPath.replace("/", "\\");
    	 File pf=new File(abPath);
         if(!pf.exists()){  
        	 pf.mkdirs();  
         }
         
    	 FileImageOutputStream imgout;
		try {
			imgout = new FileImageOutputStream(new File(abPath+fileName));
			 imgout.write(byte_, 0, byte_.length);
			 imgout.flush();
	         imgout.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
    	 
        return   Sys.Upimg.absolute_path+package1+fileName  ;
     }
在此处输入评论

允许提问者通过qq联系我

    现在绑定QQ

点击加载更多...