excel文件导入demo

前端html代码

	<!-- 导入 -->
	<shiro:hasPermission name="stuManage:stuManage:import">
		<!-- 下载模版 -->
		<input type="button" id="btnDown" style="width: 100px; margin-left: -340px" class="ui-btn ui-btn ui-btn-download" onclick="downTemplate('import_student_file_name')" value="下载模板" />
		<!-- 导入 -->
		<input type="button" id="btnImport" class="ui-btn ui-btn ui-btn-import" value="导入" />
		<input type="file" name="upfile" id="upfile" style="display: none;" multiple="multiple" onchange="importData()" />
	</shiro:hasPermission>

前端js代码

<script>
	/* 初始化方法 */
    $(document).ready(function () {
		/* 导入 */
		$("#btnImport").click(function() {
			document.getElementById("upfile").click();
		});
 	});
	/*导入*/
	function importData() {
		// 异步上传文件
		// 通过FormData构造函数创建一个空对象
		var formData=new FormData();
		// 通过append()方法来追加数据
		formData.append('file', $("#upfile")[0].files[0]);
		alert(formData);
		$.ajax({
            type:"POST",
            contentType:false,
            processData: false,
            cache: false,
            data:formData,
            url:"student/importStudentInfo",
            dataType:"json",
            success: function(data){
                if (data.flag) {
                    layer.confirm(data.message, {
                        title:'提示:',
                        closeBtn:0,
                        btn: ['确定'], //按钮
                        area: ['300px', '200px'] //宽高
                    }, function(index){
                        location.reload(true);
                        layer.close(index);
                    }, function(){
                    });
                } else {
                    layer.confirm(data.message, {
                        title:'提示:',
                        closeBtn:0,
                        btn: ['确定'], //按钮
                        area: ['300px', '200px'] //宽高
                    }, function(index){
                    	location.reload(true);
                        layer.close(index);
                    }, function(){
                    	 location.reload(true);
                    });
                }
            }
        });
	}

后端java代码

@Controller
@RequestMapping(value = "student")
public class CCStudentController extends BaseController {
	@Autowired
	private StudentService studentService;
	@RequestMapping(value = { "importStudentInfo" })
    public String importStudentInfo(HttpServletRequest request, Model model, HttpServletResponse response) throws ResourceException, IOException {
        // 返回值
        Map<String, Object> map = new HashMap<String, Object>();
        // 检测是否为上传请求
        String contentType = request.getContentType();
        if (contentType == null || !contentType.toLowerCase().startsWith("multipart/")) {
            // 提示信息:系统错误，请联系管理员！
            return renderString(response, AjaxDone.error(getText("System_error_please_contact_your_administrator"));
        }

        MultipartHttpServletRequest multipartRequest =  WebUtils.getNativeRequest(request, MultipartHttpServletRequest.class);
        // 返回值
        String result = this.studentService.importStudentInfo(multipartRequest, multipartRequest);
        // 有错误信息
        if (result!= null && result.trim().length() > 0) {
            map.put("flag", false);
            map.put("message", "导入失败：" + result);
        }
        // 导入成功
        else {
            map.put("flag", true);
            map.put("message", getText("import_excel_success"));
        }
        return renderString(response, JsonMapper.toJsonString(map),"application/json");
    }
    /**
	 * 客户端返回JSON字符串
	 * @param response
	 * @param object
	 * @return
	 */
	protected String renderString(HttpServletResponse response, Object object) {
		return renderString(response, JsonMapper.toJsonString(object), "application/json");
	}
	/**
	 * 客户端返回字符串
	 * @param response
	 * @param string
	 * @return
	 */
	protected String renderString(HttpServletResponse response, String string, String type) {
		try {
			response.reset();
	        response.setContentType(type);
	        response.setCharacterEncoding("utf-8");
			response.getWriter().print(string);
			return null;
		} catch (IOException e) {
			return null;
		}
	}
	 /**
     * 国际化
     * @param message
     * @return
     */
    protected String  getText(String message){
        return  SpringContextHolder.getApplicationContext().getMessage(message, null, null);
    }
}
    
@Service
public class StudentService {
	@Autowired
	private StudenDao studentDao;
	
	@Transactional(readOnly = false)
	public String importStudentInfo(MultipartHttpServletRequest multipartRequest,HttpServletRequest request) {
		String result = StringUtils.EMPTY;
		InputStream myxls;
		XSSFWorkbook wb;
		XSSFSheet sheet;
		XSSFRow row;

		MultipartFile file = multipartRequest.getFile("file");
		// 循环行
		int rowNum = 0;

		// 提示：请导入有效的excel文件
		if (!file.getOriginalFilename().toLowerCase().contains(".xlsx")) {
			result = SpringContextHolder.getApplicationContext().getMessage("import_excel_type_alert", null, null);
			return result;
		}

		try {
			File f = null;
			if (file.equals("") || file.getSize() <= 0) {
				file = null;
			} else {
				InputStream ins = file.getInputStream();
				f = new File(file.getOriginalFilename());
				inputStreamToFile(ins, f);
			}

			myxls = new FileInputStream(f);
			wb = new XSSFWorkbook(myxls);
			sheet = wb.getSheetAt(0);

			// 循环行
			rowNum = 0;

			// 结果记录List
			List<Student> insertList = new ArrayList<Student>();
			// 临时变量
			Student entity = new Student(); // 实体类
			// 遍历信息
			do {
				// 初始化临时变量
				entity = new Student(); // 实体类
				entity.preInsert();

				// 获取当前行 （第一行开始）
				rowNum++;
				row = sheet.getRow(rowNum);
				if (row == null) {
					break;
				}
				// 获取信息
				// 学生姓名
				studentName = UploadUtils.getCellFormatValue(row.getCell(0));
				entity.setStudentName(studentName);

				// 放入集合
				entity.setIndex(rowNum);
				insertList.add(entity);
			} while (true);
			// 数据库执行
			this.studentDao.insertList(insertList);
		} catch (Exception e) {
			// 提示信息:系统错误，请联系管理员！
			result = SpringContextHolder.getApplicationContext().getMessage("System_error_please_contact_your_administrator", null, null);
			return result;
		}
		return result;
	}
	/**
     * 获取当前cell内容
     * @param xssfCell
     * @return cell String
     */
    public static String getCellFormatValue(XSSFCell xssfCell) {
        String cellvalue = "";

        if (xssfCell != null) {
            // 判断当前Cell的Type
            switch (xssfCell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
                    // 如果是Date类型则，转化为Data格式
                    // 方法1：这样子的data格式是带时分秒的：2011-10-120:00:00
                    // cellvalue=cell.getDateCellValue().toLocaleString();
                    // 方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = xssfCell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                }
                // 如果是纯数字
                else {
                    CellStyle cs = xssfCell.getCellStyle();
                    double d = xssfCell.getNumericCellValue();
                    if (cs.getDataFormat() == 58) {
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(d);
                        SimpleDateFormat sdf = new SimpleDateFormat(
                                "yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    } else {
                        // 取得当前Cell的数值
                        cellvalue = BigDecimal.valueOf(
                                xssfCell.getNumericCellValue()).toPlainString();
                    }
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = xssfCell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
	/**
     * 流转换
     * @param ins
     * @param file
     */
    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

// 返回类
public class AjaxDone {
    public final static String STATUSCODEOK = "200";
    public final static String STATUSCODEERROR = "300";
    /**
     * 操作结果状态
     */
    private  String statusCode = STATUSCODEOK;
    /**
     * 返回的消息
     */
    private String message;
    public String getStatusCode() {
        return statusCode;
    }
    public void setStatusCode(String statusCode) {
        this.statusCode = statusCode;
    }
    public String getMessage() {
        return message;
    }
    public void setMessage(String message) {
        this.message = message;
    }
    public static AjaxDone ok(String message){
        AjaxDone ajaxDone = new AjaxDone();
        ajaxDone.setStatusCode(STATUSCODEOK);
        ajaxDone.setMessage(message);
        return ajaxDone;
    }
    public static AjaxDone error(String message){
        AjaxDone ajaxDone = new AjaxDone();
        ajaxDone.setStatusCode(STATUSCODEERROR);
        ajaxDone.setMessage(message);
        return ajaxDone;
    }

}