Excel数据导入到数据库（工具类）

一、前台请求

<input class="xz" type="file" id="gzb" onchange="user.uploadBtn ('gzb')" />
<span class="xzwj_b" id="uploadEventPath">工资发放表</span>

二、js代码

<script type="text/javascript">
var user;
$(function(){
	user = new User();
	user.init();
})

var User = function(){
	//初始化
	this.init = function(){
		$("#gzb").val("");
		$("#gzb").bind("change",function(){
			$("#uploadEventPath").attr("value",$("#gzb").val());
		})
	}
	
	//点击上传按钮
	this.uploadBtn = function(id){
		var uploadEventFile = $("#gzb").val();
		if(uploadEventFile == ""){
			alert("请选择excel格式");
		}else if(uploadEventFile.lastIndexOf(".xls") <0){
			alert("只能上传excel格式");
		}else{
			var url = "/*/*";
			//var formData = new FormData($("form")[0]);  //有form表单时
			var formData = new FormData();
			formData.append("file", $('#' + id).prop('files')[0]);
			user.sendAjaxRequest(url,'POST',formData);
		}
		
		this.sendAjaxRequest = function(url,type,data){
			$.ajax({
				url:url,
				data:data,
				type:type,
				success:function(result){
					alert(result);
				},
				error:function(){
					alert("excel上传失败");
				},
				cache:false,
				contentType:false,
				processData:false
				
			})
		}
	}
}

</script>

 三、后台逻辑

import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

/**
 * 导入excel
 * 
 * @author Administrator
 *
 */
public class ExcelImport {

	/**
	 * 导入excel 说明 每行每列都是从0开始 返回值 isSuccess == true 时带回表格的所有数据、最大行数和最大列数
	 * 
	 * @param file
	 * @param minRow
	 * @param redirectAttributes
	 * @return
	 */
	public static Map<String, Object> importStuParentList(HttpServletRequest request, int minRow) {
		Map<String, Object> map = new HashMap<>();
		map.put("isSuccess", false);
		// 创建表格
		try {
			MultipartHttpServletRequest multipartFile = (MultipartHttpServletRequest) request;

			MultipartFile file = multipartFile.getFile("file");
			Workbook workbook = WorkbookFactory.create(file.getInputStream());
			Sheet sheet = workbook.getSheetAt(0);
			// 获取最大行数
			int totalRows = sheet.getLastRowNum();
			if (totalRows < minRow) {
				map.put("msg", "没有数据");
				return map;
			}
			// 获取最大列数
			int totalCells = sheet.getRow(minRow).getPhysicalNumberOfCells();
		
			// 读取数据放入集合
			for (int i = minRow; i <= totalRows; i++) {
				//获取每行
				Row row = sheet.getRow(i);
				for (int j = 0; j < totalCells; j++) {
					//获取每行中的每个单元格的数据
					Cell cell = row.getCell(j);
					
					//把数据存入到数据库
					
				}
			}
			map.put("isSuccess", true);
			map.put("sheet", sheet);
			map.put("totalRows", totalRows);
			map.put("totalCells", totalCells);
			return map;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			map.put("msg", "信息有误");
		}
		return map;
	}

}

四、注意事项

        如果获取手机号或者数值类型时，需要numberFormat转换

NumberFormat numberFormat = NumberFormat.getInstance();
numberFormat.setGroupingUsed(false);
numberFormat.format(cell.getNumericCellValue());