用Java实现将Excel中的记录入库
Excel
Oracle数据库
整个过程
前台form表单提交 -> url拦截 -> 传到后台的servlet -> 后台首先将Excel传到服务器(假设有)的某个路径 -> 然后将Excel分条解析到List里面 -> 将List记录放入Map ->批量方法传到数据库
废话不多说,上代码讲解:
1 上传Excel到服务器某个路径
这里注意一点:
前台form表单提交编码的格式必须是multipart/form-data方式,不能是application/x-www-form-urlencoded,原因和设置方法见getParameter取不到值
package com.trigl.impo;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Properties;
import java.util.Random;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;
/**
* 上传Excel并且将Excel信息存入数据库控制类
* @author 白鑫
* @date 2015年12月31日 上午12:50:06
*/
public class UploadExcel extends AbstractController {
private static final Logger log = Logger.getLogger(UploadExcel.class);
@Override
protected ModelAndView handleRequestInternal(HttpServletRequest arg0,
HttpServletResponse arg1) throws Exception {
return this.uploadFileAction(arg0, arg1);
}
/**
* 上传文件方法
* @param request
* @param response
* @return
* @throws Exception
*/
private ModelAndView uploadFileAction(HttpServletRequest request,
HttpServletResponse response) throws Exception {
Properties props = new Properties();
try {
//加载properties文件中的内容
props.load(UploadExcel.class.getClassLoader().getResourceAsStream("uploadpath.properties"));
} catch (IOException e1) {
log.error("获取路径失败!", e1);
}
String data = "";
log.info(request.getContentType());//form提交编码格式
// 设置返回类型, 字符集 ,防止乱码
response.setContentType("text/html; charset=utf-8");
// 参数,上传文件的子目录(以模块命名)
// String childPath = request.getParameter("path");
String childPath = "UploadExcel";
// request转换
// MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;
MultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());
MultipartHttpServletRequest mulRequest = resolver.resolveMultipart(request);
// 获取文件
MultipartFile file = mulRequest.getFile("fileField");
// 服务器存放路径 PATH为 C://dsmc
String dir = props.getProperty("PATH") + childPath;
File filePath = new File(dir);
// 如果文件夹不存在则创建
if (!filePath.exists()) {
filePath.mkdirs();
}
// 获取系统文件名,系统文件名由当前时间的值加上3位随机生成吗构成
String systemFileName = new Long(System.currentTimeMillis()).toString() + new Random().nextInt() % 1000;
// 获取后缀名
String[] s = file.getOriginalFilename().split("\\.");
String suffix = "";
if (s.length > 1)
suffix = s[s.length - 1];
FileOutputStream io = null;
try {
//separatorChar的值为“\\"
io = new FileOutputStream(dir + File.separatorChar + systemFileName + "." + suffix);
io.write(file.getBytes());
if (log.isDebugEnabled()) {
log.debug("文件保存为" + systemFi