一、前台请求
<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());