1、配置springmvc
<!-- 需要文件上传功能时,启用以下配置 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxInMemorySize">
<value>1638400</value>
</property>
</bean>
2、页面
<div style="margin-bottom: 20px;height: 10px;font-size: 16px;font-weight: bold;"> 信息录入:<span class="d0_flow_lbt"></span></div>
<div align="center">
<form enctype="multipart/form-data" id="batchUpload4" method="post" class="form-horizontal">
<button class="btn btn-success btn-xs" id="uploadEventBtn4" style="height:26px;" type="button" >选择文件</button>
<input type="file" name="file" style="width:0px;height:0px;" id="uploadEventFile4">
<input id="uploadEventPath4" disabled="disabled" type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" >
</form>
<button type="button" class="btn btn-success btn-sm" οnclick="user4.uploadBtn(4)" >上传</button>
</div> <br/>
<script type="text/javascript">
var User = function(){
this.init = function(i){
//模拟上传excel
$("#uploadEventBtn"+i).unbind("click").bind("click",function(){
$("#uploadEventFile"+i).click();
});
$("#uploadEventFile"+i).bind("change",function(){
$("#uploadEventPath"+i).attr("value",$("#uploadEventFile"+i).val());
});
};
//点击上传按钮
this.uploadBtn = function(i){
var uploadEventFile = $("#uploadEventFile"+i).val();
if(uploadEventFile == ''){
layer.alert("请选择excel,再上传");
}else if(uploadEventFile.lastIndexOf(".xls")<0){//可判断以.xls和.xlsx结尾的excel
layer.alert("只能上传Excel文件");
}else{
var url = '${pageContext.request.contextPath}/load/upload.do?leadlx='+i;
alert(url);
var formData = new FormData($('form')[i]);
user.sendAjaxRequest(url,'POST',formData);
}
};
this.sendAjaxRequest = function(url,type,data){
$.ajax({
url : url,
type : type,
data : data,
success : function(map) {
layer.alert( map.result);
},
error : function() {
layer.alert( "excel上传失败");
},
cache : false,
contentType : false,
processData : false
});
};
}
//上传几个文件定义几个user对象
var user;
var user0;
var user1;
var user2;
var user3;
var user4;
$(function(){
user = new User();
user0 = new User();
user0.init(0);
user1 = new User();
user1.init(1);
user2 = new User();
user2.init(2);
user3 = new User();
user3.init(3);
user4 = new User();
user4.init(4);
});
</script>
3、controller层
@Controller
@RequestMapping("/load")
public class LoadController {
@Resource
private LoadService loadService;
@RequestMapping(value="/upload",method =RequestMethod.POST)
@ResponseBody
public Map upload(String leadlx,@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response){
String result = loadService.readExcelFile(file,leadlx);
Map map=new HashMap();
map.put("result", result);
return map;
}
}
4、service层
@Service
public class LoadServiceImpl implements LoadService {
@Resource
private LoadDao loadDao;
@Override
public String readExcelFile(MultipartFile file, String leadlx) {
// TODO Auto-generated method stub
String result ="";
boolean b=false;
ReadExcel readExcel=new ReadExcel();
List<StudentBean> useList = readExcel.getExcelInfo(file,leadlx);
if(useList!=null && useList.size()>0){
b=loadDao.leadUser(useList);
}
if(useList != null && !useList.isEmpty()){
result = "学生信息导入成功";
}else{
result = "信息导入为空";
}
return result;
}
}
5.工具类readExcel
public class ReadExcel {
//������
private int totalRows = 0;
//������
private int totalCells = 0;
//������Ϣ������
private String errorMsg;
//���췽��
public ReadExcel(){}
//��ȡ������
public int getTotalRows() { return totalRows;}
//��ȡ������
public int getTotalCells() { return totalCells;}
//��ȡ������Ϣ
public String getErrorInfo() { return errorMsg; }
/**
* ��EXCEL�ļ�����ȡ��Ϣ����
* @param fielName
* @return
*/
public List getExcelInfo(MultipartFile mFile,String leadlx) {
String fileName = mFile.getOriginalFilename();//��ȡ�ļ���
List userList=null;
try {
if (!validateExcel(fileName)) {// ��֤�ļ����Ƿ�ϸ�
return null;
}
boolean isExcel2003 = true;// �����ļ����ж��ļ���2003�汾����2007�汾
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
userList = createExcel(mFile.getInputStream(), isExcel2003,leadlx);
} catch (Exception e) {
e.printStackTrace();
}
return userList;
}
/**
* ����excel��������ݶ�ȡ�ͻ���Ϣ
* @param is ������
* @param isExcel2003 excel��2003����2007�汾
* @return
* @throws IOException
*/
public List createExcel(InputStream is, boolean isExcel2003,String leadlx) {
List userList=null;
try{
Workbook wb = null;
if (isExcel2003) {// excel版本2003,excel2003
wb = new HSSFWorkbook(is);
} else {// excel版本2007,excel2007
wb = new XSSFWorkbook(is);
}
userList = readExcelValue(wb,leadlx);// Excel
} catch (IOException e) {
e.printStackTrace();
}
return userList;
}
/**
* ��ȡExcel����ͻ�����Ϣ
* @param wb
* @return
*/
private List readExcelValue(Workbook wb,String leadlx) {
// �õ���һ��shell
Sheet sheet = wb.getSheetAt(0);
// �õ�Excel������
this.totalRows = sheet.getPhysicalNumberOfRows();
// �õ�Excel������(ǰ����������)
if (totalRows > 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List userList = new ArrayList();
// ѭ��Excel����
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null){
continue;
}
StudentBean user = new StudentBean();
ClassBean depart=new ClassBean();
MajorBean major=new MajorBean();
DormitoryBean dormitory=new DormitoryBean();
FeeBean tuition=new FeeBean();
List list=new ArrayList();
//读取excel
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
//����Ǵ�����,������д����25,cell.getNumericCellValue()�����25.0,ͨ����ȡ�ַ���ȥ��.0���25
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String name = String.valueOf(cell.getNumericCellValue());
list.add(name.substring(0, name.length()-2>0?name.length()-2:1));//����
}else{
list.add(cell.getStringCellValue());//����
}
} else if (c == 1) {
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue()+"");//�Ա�
}
} else if (c == 2){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue()+"");//�Ա�
}
} else if (c == 3){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
} else if (c == 4){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
} else if (c == 5){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
}else if (c == 6){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
}else if (c == 7){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
} else if (c == 8){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
} else if (c == 9){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
} else if (c == 10){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue()+"");//�Ա�
}
} else if (c == 11){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
String sex = String.valueOf(cell.getNumericCellValue());
list.add(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//�Ա�
}else{
list.add(cell.getStringCellValue());//�Ա�
}
}
}else{
list.add("");//�Ա�
}
}
//学生
int m=list.size();
if("0".equals(leadlx)){
for(int i=0;i<list.size();i++){
if(i==0){
user.setStu_no(list.get(i)+"");
}
if(i==1){
user.setStu_name(list.get(i)+"");
}
if(i==2){
if((list.get(i)+"").equals("男")){
user.setStu_sex("1");
}else if((list.get(i)+"").equals("女")){
user.setStu_sex("0");
}else{
user.setStu_sex(list.get(i)+"");
}
}
if(i==3){
user.setStu_num(list.get(i)+"");
}if(i==4){
user.setStu_id(list.get(i)+"");
}if(i==5){
user.setClass_id(list.get(i)+"");
}if(i==6){
user.setStu_phone(list.get(i)+"");
}if(i==7){
user.setStu_qq(list.get(i)+"");
}if(i==8){
user.setStu_bir(list.get(i)+"");
}if(i==9){
user.setStu_year(list.get(i)+"");
}if(i==10){
user.setStu_house(list.get(i)+"");
}if(i==11){
user.setHome_phone(list.get(i)+"");
}
}
userList.add(user);
}
//班级
if("1".equals(leadlx)){
for(int i=0;i<list.size();i++){
if(i==0){
depart.setClass_id(list.get(i)+"");
}
if(i==1){
depart.setClass_count(list.get(i)+"");
}
if(i==2){
depart.setMaj_id(list.get(i)+"");
}
if(i==3){
depart.setClass_c1(list.get(i)+"");
}
}
userList.add(depart);
}
//专业
if("2".equals(leadlx)){
for(int i=0;i<list.size();i++){
if(i==0){
major.setMaj_id(list.get(i)+"");
}
if(i==1){
major.setMaj_name(list.get(i)+"");
}
if(i==2){
major.setMaj_intro(list.get(i)+"");
}if(i==3){
major.setMaj_m1(list.get(i)+"");
}
}
userList.add(major);
}
//宿舍
if("3".equals(leadlx)){
for(int i=0;i<list.size();i++){
if(i==0){
dormitory.setDor_id(list.get(i)+"");
}
if(i==1){
dormitory.setDor_on(list.get(i)+"");
}
if(i==2){
String dor_max=list.get(i)+"";
if(dor_max.equals("4人间")){
dor_max="4";
}else if(dor_max.equals("6人间")){
dor_max="6";
}else if(dor_max.equals("8")){
dor_max="8";
}else{
dor_max="";
}
dormitory.setDor_max(dor_max);
dormitory.setDor_surplus(dor_max);
}
if(i==3){
String dor_sex=list.get(i)+"";
if(dor_sex.equals("男")){
dormitory.setDor_sex("1");
}else if(dor_sex.equals("女")){
dormitory.setDor_sex("1");
}else{
dormitory.setDor_sex("");
}
}
if(i==4){
dormitory.setDor_money(list.get(i)+"");
}
if(i==5){
dormitory.setClass_id(list.get(i)+"");
}if(i==6){
dormitory.setDor_d1(list.get(i)+"");
}
}
userList.add(dormitory);
}
//费用
if("4".equals(leadlx)){
for(int i=0;i<list.size();i++){
if(i==0){
tuition.setFee_id(list.get(i)+"");
}
if(i==1){
tuition.setFee_basic(list.get(i)+"");
}
if(i==2){
tuition.setFee_book(list.get(i)+"");
}
if(i==3){
tuition.setFee_safe(list.get(i)+"");
}
if(i==4){
tuition.setFee_others(list.get(i)+"");
}
if(i==5){
tuition.setFee_phone(list.get(i)+"");
}if(i==6){
tuition.setFee_on(list.get(i)+"");
}if(i==7){
tuition.setMaj_id(list.get(i)+"");
}if(i==8){
tuition.setFee_f1(list.get(i)+"");
}
}
userList.add(tuition);
}
}
return userList;
}
/**
* ��֤EXCEL�ļ�
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件格式不正确";
return false;
}
return true;
}
// @�������Ƿ���2003��excel������true��2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@�������Ƿ���2007��excel������true��2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
mapper示例
<insert id="leadUser" parameterType="java.util.List">
insert into student(stu_no,stu_name,stu_sex,stu_num,stu_id,class_id,stu_phone,stu_qq,stu_bir,stu_year,stu_house,home_phone,stu_psw,stu_state)
values
<foreach collection="list" item="student" index="index" separator=",">
(#{student.stu_no,jdbcType=VARCHAR},#{student.stu_name,jdbcType=VARCHAR},#{student.stu_sex,jdbcType=VARCHAR},
#{student.stu_num,jdbcType=VARCHAR},#{student.stu_id,jdbcType=VARCHAR},#{student.class_id,jdbcType=VARCHAR},
#{student.stu_phone,jdbcType=VARCHAR},#{student.stu_qq,jdbcType=VARCHAR},#{student.stu_bir,jdbcType=VARCHAR},
#{student.stu_year,jdbcType=VARCHAR},#{student.stu_house,jdbcType=VARCHAR},#{student.home_phone,jdbcType=VARCHAR},
'123','0'
)
</foreach>
ON DUPLICATE KEY UPDATE stu_name = VALUES(stu_name),stu_sex = VALUES(stu_sex),stu_sex = VALUES(stu_sex),
stu_id = VALUES(stu_id),class_id = VALUES(class_id),stu_phone = VALUES(stu_phone),stu_qq = VALUES(stu_qq),
stu_bir = VALUES(stu_bir),stu_house = VALUES(stu_house),stu_year = VALUES(stu_year),home_phone = VALUES(home_phone)
</insert>