JAVA Excel 简单导出导入

JAVA Excel 导出导入的简单实现

在很多web应用中需要用到导入或导出excel、word等。Java中操作Microsoft的office文件比较常用的就是 Apache的poi。

POI简介

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式文档读和写的功能
Jar包：poi.jar

导出excel

    public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    
    		
    		UserService service = new UserServiceImpl();
    		
    		List<User> list = service.getAllUser();
    		
    		HSSFWorkbook workBook = new HSSFWorkbook();
    		
    		HSSFSheet sheet = workBook.createSheet("users");
    		
    		HSSFRow row0 = sheet.createRow(0);
    		
    		row0.createCell((short)0).setCellValue(new HSSFRichTextString("ID"));
    		row0.createCell((short)1).setCellValue(new HSSFRichTextString("姓名"));
    		row0.createCell((short)2).setCellValue(new HSSFRichTextString("年龄"));
    		row0.createCell((short)3).setCellValue(new HSSFRichTextString("生日"));
    		
    		HSSFCellStyle cellStyle = workBook.createCellStyle();
    		
    		HSSFDataFormat dataFormat = workBook.createDataFormat();
    		cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));
    		
    		for(int i=0;i<list.size();i++){
    			
    			HSSFRow row = sheet.createRow(i+1);
    			
    			row.createCell((short)0).setCellValue(list.get(i).getId());
    			row.createCell((short)1).setCellValue(new HSSFRichTextString(list.get(i).getName()));
    			row.createCell((short)2).setCellValue(list.get(i).getAge());
    			
    			HSSFCell c = row.createCell((short)3);
    			
    			c.setCellValue(list.get(i).getBirthday());
    			//System.out.println(list.get(i).getBirthday());
    			c.setCellStyle(cellStyle);
    
    		}
    		response.setContentType("bin");
    		response.addHeader("Content-Disposition", "attachment;filename=\"user.xls"+"\"");
    		OutputStream os = response.getOutputStream();		
    		workBook.write(os);
    		
    		os.close();
    	}

导入excel

public void importExcel(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	     DiskFileItemFactory factory = new  DiskFileItemFactory();
	     ServletFileUpload sfu = new ServletFileUpload(factory);
	     try {
			List<FileItem> list = sfu.parseRequest(request);
			for(FileItem item:list){
				if(!item.isFormField()){
					InputStream is = item.getInputStream();
					HSSFWorkbook book = new HSSFWorkbook(is);
					HSSFSheet sheet = book.getSheetAt(0);
					// sheet.getPhysicalNumberOfRows() 获得这个sheet中一共多少行
					
					List<Student> studentList = new ArrayList<Student>();
					for(int i=0;i<sheet.getPhysicalNumberOfRows()-1;i++){
						
						Student s = new Student();
						s.setId((int)(sheet.getRow(i+1).getCell(0).getNumericCellValue()));
						s.setName(sheet.getRow(i+1).getCell(1).getRichStringCellValue().toString());
						s.setAge((int)(sheet.getRow(i+1).getCell(2).getNumericCellValue()));
						s.setBirthday(sheet.getRow(i+1).getCell(3).getDateCellValue());
						
						studentList.add(s);
					}
					
					
					// 调用service中添加方法把list存入数据库...
					
					for(Student s:studentList){
						
						System.out.println(s.getId()+"\t"+s.getName()+"\t"+s.getAge()+"\t"+s.getBirthday());
					}
					
					
				}
				
			}
			
		} catch (FileUploadException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}