POI实现Excel批量导入数据（SpringMVC）

最新推荐文章于 2024-04-24 13:17:14 发布

playboy307

最新推荐文章于 2024-04-24 13:17:14 发布

阅读量3.4k

点赞数

分类专栏： JAVA

本文链接：https://blog.csdn.net/qq_29963431/article/details/72624870

版权

JAVA 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

最近再做个东西，需要Excel批量导入数据，以前没有做过这类东西，写篇博客做个记录

首先是前期准备，在SpringMVC配置文件中开启文件上传

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>

导入相关的Jar包

xbean.jar

jsr173_1.0_api.jar

dom4j-1.6.1.jar
poi-3.8-20120326.jar
poi-ooxml-3.5-FINAL.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
xmlbeans-2.3.0.jar

前台代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<head>
<script type="text/javascript">
function closeDialog() {
	$.pdialog.closeCurrent();
	navTab.reload("basinfo/sourceRiskList", {navTabId : "sourceRiskList"});
}
</script>
</head>
<div class="pageContent">
	<form method="post" action="basinfo/importExcel" class="pageForm required-validate" 
		οnsubmit="return iframeCallback(this, dialogAjaxDone);" enctype="multipart/form-data" >
			<table width="590" style="border-collapse:separate; border-spacing:0px 10px;">
				<tr height="90">
					<td>
						<lable>导入文件</lable>
					</td>
					<td> <input type="file" name="sourceRiskFile"/> </td>
					<td> <button type="submit">上传</button> </td>
					<td> <button οnclick="closeDialog()">查看上传结果</button> </td>
				</tr>
				<tr height="50">
				</tr>
			</table>
	</form>
</div>

Controller代码

@RequestMapping(value="importExcel", method=RequestMethod.POST)
	@ResponseBody
	public PageResult importExcel(@RequestParam(value = "sourceRiskFile", required = false) MultipartFile sourceRiskFile,
				HttpServletRequest request, Model model){
		String fileName = sourceRiskFile.getOriginalFilename();
		System.out.println(fileName);
		PageResult result = new PageResult();
		if(sourceRiskFile!=null){
			//判断该文件是否为Excel文件
			if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
				try {
					int res = sourceRiskService.importExcel(sourceRiskFile, sourceRiskFile.getOriginalFilename());
					result.setMessage("成功添加" + res + "条记录");
					result.setStatusCode(200);
				} catch (Exception e) {
					e.printStackTrace();
					result.setStatusCode(500);
					result.setMessage("添加失败，请检查文件是否符合要求");
				}
			}else{
				result.setStatusCode(500);
				result.setMessage("添加失败，请检查文件是否为Excel文件");
			}
		}
		return result;
	}

Service代码

@Transactional(readOnly=false)
	public int importExcel(MultipartFile file, String fileName){
		//记录成功添加的记录数
		int res = 0;
		System.out.println(1111);
		try {
			InputStream fileInputStream = file.getInputStream();
	        //判断是否是03版本的Excel(还是07的)  
	        boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");  
	        //1、读取工作簿  
	        Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream)  
	                : new XSSFWorkbook(fileInputStream);  
	        //2、读取工作表  
	        Sheet sheet=workbook.getSheetAt(0);  
	        //3、读取行  
	        System.out.println(sheet.getPhysicalNumberOfRows());
	        if(sheet.getPhysicalNumberOfRows()>1){  
	            SourceRisk sourceRisk = null;  
	            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {  
	                //4、读取单元格  
	                Row row=sheet.getRow(i);  
	                sourceRisk = new SourceRisk();  
	                if(row==null || row.getCell(0) == null){
	                	continue;
	                }
	                //Id
	                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);  
	                sourceRisk.setId(row.getCell(0).getStringCellValue());  
	                //危险源名称  
	                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
	                sourceRisk.setName(row.getCell(1).getStringCellValue());      
	                //风险源  
	                try {
	                	//先查询是否存在，然后再添加
	                	SourceRisk temp = sourceRiskDao.findEntityById(sourceRisk.getId());
	                	if(temp == null){
	                		sourceRiskDao.add(sourceRisk);
	                		res++;	                		
	                	}
					} catch (Exception e) {
						
					}
	            } 
	        }
	        fileInputStream.close(); 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}