java如何进行excel的导入导出

java如何进行excel的导入导出

       java开发中，经常需要将数据库中查询的数据放在Excel中进行，导出到本地。

       实现思路：

      1、由前端重定向访问后端的导出Excel接口

      2、后端根据前端传的参数到后端查询数据库

      3、后端获取到数据库数据后将数据放到Excel中

      4、后端将生成的Excel以文件流的方式返回给前端。

      5、前端接收到Excel后保存到本地。

      实现思路图：

     

     首先需要导入  pol的jar包

 

      实现代码：

     1.前端js:

  

                window.location.href="http://"+window.location.host+"/chenchi/chenchiWebVersion/exportExcel.do";

     2.后端

     controller

//导出Excel
	@RequestMapping("exportExcel")
	@ResponseBody
	public AjaxJson exportExcel(String id,HttpServletRequest req,HttpServletResponse rsp)
	{
		Map<String,Object> map = new HashMap();
		
		
		AjaxJson j = new AjaxJson();
		j.setMsg("Ok");
		j.setAttributes(map);
		
		j.setSuccess(true); 
		
		
		List<Map<String, Object>> versionVerifyList=chenchiWebService.getversionVerifyList();

		System.out.println(versionVerifyList);
		
		String[] title={"id","用户id","版本号"};
		String[] key={"id","user_id","version_code"};
		
		
		WriteExcel we=new WriteExcel();
		
		
		
		we.improment(rsp,"test",title,versionVerifyList,key);
		
		return j;

		 
	}

    

       WriteExcel.java:

public class WriteExcel {
//导出
public void improment(HttpServletResponse response,String name,String[] title,List<Map<String, Object>> list,String[] key){
//创建HSSFWorkbook对象(excel的文档对象)  
HSSFWorkbook wb = new HSSFWorkbook();  
//建立新的sheet对象（excel的表单）  
HSSFSheet sheet=wb.createSheet(name);  
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个  
HSSFRow row1=sheet.createRow(0);  
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个  
HSSFCell cell=row1.createCell(0);  
    //设置单元格内容  
cell.setCellValue("网吧信息版本号信息一览表");  
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列  
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));  
//在sheet里创建第二行  
HSSFRow row2=sheet.createRow(1);      
	for (int i = 0; i < title.length; i++) {
		row2.createCell(i).setCellValue(title[i]);
	}
	for (int i = 0; i < list.size(); i++) {
	    HSSFRow row3=sheet.createRow(2+i);  
	    for (int j = 0; j < key.length; j++) {
	    	try {
	    		row3.createCell(j).setCellValue(list.get(i).get(key[j]).toString()); 
			} catch (Exception e) {
				// TODO: handle exception
				System.out.println(key[j]);
			}
	    	
		}
	    
	}      
	try {
		  OutputStream output=response.getOutputStream();  
		  response.reset();  
		  response.setHeader("Content-disposition", "attachment; filename=versionInfo.xls");  
		  response.setContentType("application/msexcel");          
		  wb.write(output);  
		  output.close();  
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}
}

public void uploads(HttpServletRequest request){
	ArrayList<String> list=new ArrayList<String>();
	//实例化通用多文件解析器
	CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());
	
	//判断是否有文件上传（即多文件请求）
	if (multipartResolver.isMultipart(request)) {
		System.out.println("有文件");
		
		//转换多文件上传请求
		MultipartHttpServletRequest multiRquest = (MultipartHttpServletRequest) request;
		//获取上传文件名
		Iterator<String> iterator = multiRquest.getFileNames();
		
		while (iterator.hasNext()) {
			System.out.println("开始读取");
			//获取上传文件
			MultipartFile file = multiRquest.getFile(iterator.next());
			
			FileInputStream fileInputStream;
			try {
				exprot(file.getInputStream());
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
}
public void exprot(InputStream fileIn) {
		List temp = new ArrayList();
		Workbook wb0 = null;
		try {
			wb0 = new HSSFWorkbook(fileIn);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Sheet sht0 = wb0.getSheetAt(0);
		for (Row r : sht0) {
			if (r.getRowNum() < 1) {
				continue;
			}
			// 创建实体类

			// 取出当前行第1个单元格数据，并封装在info实体stuName属性上
			
			for (int i = 0; i < r.getLastCellNum(); i++) {
				System.out.print(r.getCell(i).getStringCellValue()+"        ");
			}
			System.out.println();
		}
		try {
			fileIn.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}