poi 实现excel小例子

最新推荐文章于 2024-10-04 09:25:15 发布

风灵寒

最新推荐文章于 2024-10-04 09:25:15 发布

阅读量679

点赞数

分类专栏：框架技术的应用文章标签： spring mvc poi mybatis excel

本文链接：https://blog.csdn.net/u013766398/article/details/51452114

版权

框架技术的应用专栏收录该内容

32 篇文章 0 订阅

订阅专栏

在文件中需要引入的包有org.apache.poi 、poi-ooxml、poi-ooxml-schemas

jsp：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
       <script type="text/javascript" src="../resource/js/jquery-1.8.2.min.js"></script>   
       <script type="text/javascript" src="../resource/js/jquery-form.js"></script>   
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>上传Excel</title>
    </head>
<body>
<script type="text/javascript">
function checkFile(file){
	var filePath = file.value;
	var suffix = filePath.substring(filePath.lastIndexOf(".")+1);
	if("xlsx" == suffix || "xls" == suffix){
	}else{
		alert("请上传Excel文件");
	}
}
function importExcel(){
	 $("#excelForm").ajaxSubmit({  
         type:"post",  //提交方式  
         url:"${pageContext.request.contextPath}/student/filesUpload", //请求url  
         success:function(data){ //提交成功的回调函数  
         	alert(data);
         }  
     }); 
}
</script>
</head>
<body>
<form id="excelForm" method="post" enctype="multipart/form-data">
	<table width="100%" class="basic">
	    <tr>
		 	<th width="30%">Excel文件：</th>
			<td>
				<input id="file" type="file" name="file" οnchange="checkFile(this);"/>
			</td>
	  	</tr>
	</table>
	<br/>
	<p style="text-align: center;">
		<input type="button" οnclick="importExcel();" value="上传excel" class="search"/>
	</p>
</form>
</body>
</html>

controller层：

  @RequestMapping(value={"/filesUpload"},method={RequestMethod.POST})
	@ResponseBody
	public String filesUpload(String examFlow , MultipartFile file) throws Exception{
		if(file.getSize() > 0){
			try{
				service.importExcel(examFlow , file);
				return OperatorEnum.OperatorSuccess.getName();
			}catch(RuntimeException re){
				return re.getMessage();
			}
			
		}
		return OperatorEnum.OperatorFailure.getName();
	}

service层：

public void importExcel(String examFlow, MultipartFile file) throws Exception {
		InputStream is = file.getInputStream();
		byte[] fileData = new byte[(int)file.getSize()];  
		is.read(fileData); 
		Workbook wb =  createCommonWorkbook(new ByteInputStream(fileData, (int)file.getSize() ));
	    parseExcel(examFlow,wb);
	}
	public static Workbook createCommonWorkbook(InputStream inS) throws IOException, InvalidFormatException { 
		if (!inS.markSupported()) { 
			inS = new PushbackInputStream(inS); 
		} 
		// EXCEL2003
		if (POIFSFileSystem.hasPOIFSHeader(inS)) { 
			return new HSSFWorkbook(inS); 
		} 
		// EXCEL2007
		if (POIXMLDocument.hasOOXMLHeader(inS)) { 
			return new XSSFWorkbook(OPCPackage.open(inS)); 
		} 
		throw new IOException("excel格式不支持"); 
	} 
	private void parseExcel(String examFlow,Workbook wb)  {
		int sheetNum = wb.getNumberOfSheets();
		if(sheetNum>0){
			List<String> colnames = new ArrayList<String>();
			Sheet sheet;
			try{
				sheet = (HSSFSheet)wb.getSheetAt(0);
			}catch(Exception e){
				sheet = (XSSFSheet)wb.getSheetAt(0);
			}
			int row_num = sheet.getLastRowNum(); 
            for(int i = 0; i <= row_num; i++){
                Row r =  sheet.getRow(i);  
                System.err.println(i);
                int cell_num = r.getLastCellNum();  
                if(i == 0){
                	for (int j = 0; j < cell_num; j++) {  
               		 String value = "";
						 if(r.getCell((short)j).getCellType() == 1){  
							 value = r.getCell((short) j).getStringCellValue();  
						 }else{  
							 value = _doubleTrans(r.getCell((short) j).getNumericCellValue());  
						 }  
						 System.err.println(value+"==="); 
						colnames.add(value);
					}
                }else {
                	Student student = new Student();
					boolean flag = false;
					for(int j = 0; j < cell_num; j++){  
						String value = "";
						 if(r.getCell((short)j).getCellType() == 1){  
							 value = r.getCell((short) j).getStringCellValue();  
						 }else{  
							 value = _doubleTrans(r.getCell((short) j).getNumericCellValue());  
						 }  
						 System.err.println(value+"==="); 
					
						if("学号".equals(colnames.get(j))) {
							if (value == null|| value.trim().length() == 0) {
								flag = true;
								break;
							}
							student.setId(Integer.parseInt(value));
						}else if("姓名".equals(colnames.get(j))){
							if (value == null|| value.trim().length() == 0) {
								flag = true;
								break;
							}
							student.setName(value);
						}else if("生日".equals(colnames.get(j))){
							student.setBirthday(value);
						}else if("性别".equals(colnames.get(j))){
							if("男".equals(value.trim())){
								student.setSex((short)1);
							}else{
								student.setSex((short)2);
							}
					    }else if("头像".equals(colnames.get(j))){
							student.setPhotoUrl(value);
					    }else if("年级".equals(colnames.get(j))){
							student.setClassId(Integer.parseInt(value));
					    }
				    	if (flag) {
							break; 
						}
					}
					this.studentMapper.insert(student);
                }	  
            }
		}
	}
	public static String _doubleTrans(double d){
        if((double)Math.round(d) - d == 0.0D)
            return String.valueOf((long)d);
        else
            return String.valueOf(d);
    }

spring xml：

  <!-- 配置文件上传，如果没有使用文件上传可以不用配置，当然如果不配，那么配置文件中也不必引入上传组件包 -->  
        <bean id="multipartResolver"    
            class="org.springframework.web.multipart.commons.CommonsMultipartResolver">    
            <!-- 默认编码 -->  
            <property name="defaultEncoding" value="utf-8" />    
            <!-- 文件大小最大值 -->  
            <property name="maxUploadSize" value="10485760000" />    
            <!-- 内存中的最大值 -->  
            <property name="maxInMemorySize" value="40960" />    
        </bean> 
        <!-- 上传文件时候遇见的exception 这时候并没有进入controller 层 -->
        <bean id="exceptionResolver" class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver"> 
		    <property name="exceptionMappings"> 
		        <props> 
		            <!-- 遇到MaxUploadSizeExceededException异常时，自动跳转到/WEB-INF/jsp/error_fileupload.jsp页面 --> 
		            <prop key="org.springframework.web.multipart.MaxUploadSizeExceededException">uploadFile/uploadError</prop> 
		        </props> 
		    </property> 
		</bean>

student 表结构截图：