一、总体思路将excel文件上传到服务器端然后使用服务器的路径获取文件资源实现文件的读写。注意需要导入相关的poi jar包
二、表
三 、实体类
package com.ssm.bean;
import java.util.Date;
public class UserInfo {
private Integer userId;
private String userName;
private String email;
private String mobile;
private String password;
private Date createTime;
private Date updateTime;
private String delFlg;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public String getDelFlg() {
return delFlg;
}
public void setDelFlg(String delFlg) {
this.delFlg = delFlg;
}
}
四、jsp页面用于上传Excel文件
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>导入excel页面</title>
<script type="text/javascript" src="<%=path %>/skin/jquery.js"></script>
<script type="text/javascript" src="<%=path %>/skin/layer/layer.js"></script>
<link rel="stylesheet" href="<%=path %>/skin/js/bootstrap.min.css" type="text/css">
<script type="text/javascript" src="<%=path %>/skin/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="<%=path %>/skin/layui/css/layui.css" media="all">
<script src="<%=path %>/skin/layui/layui.js"></script>
<script>
layui.use('upload', function(){
var upload = layui.upload , $ = layui.jquery;
//上传图片
var uploadInst = upload.render({
elem: '#uploadPic' //绑定元素
,url: 'xunpan/impExcel' //上传接口 [[@{/upload/img}]]
,auto: false
// ,exts: 'doc|docx|pdf|jpg|jpeg|png|zip|'
,exts: 'doc|docx|pdf|zip|xls|xlsx'
,bindAction: '#uploadPicBtn'
,before: function(obj){
//预读本地文件示例，不支持ie8
obj.preview(function(index, file, result){
console.log(index);
console.log(file);
console.log(result);
debugger
//$('#preShow').attr('src', result); //图片链接（base64）
});
}
,done: function(res){
//上传失败
if(res.code > 0){
return layer.msg('上传失败');
}
//上传成功
if(res.code == 0){
$('#aftershow').attr('href', "/upload/"+res.data);
document.getElementById("file_url").value = "/upload/"+res.data;
$('#aftershow').html("文件下载");
return layer.msg('上传成功');
}
}
,error: function(re){
var demoText = $('#demoText');
demoText.html('<span style="color: #FF5722;">上传失败</span> <a class="layui-btn layui-btn-mini demo-reload">重试</a>');
demoText.find('.demo-reload').on('click', function(){
uploadInst.upload();
});
}
});
} );
</script>
</head>
<body>
<div class="layui-input-block">
<!-- 上传按钮 -->
<button type="button" class="layui-btn" id="uploadPic"><i class="layui-icon"></i>选择文件</button>
<button type="button" class="layui-btn layui-btn-warm" id="uploadPicBtn">开始上传</button>
<!-- 隐藏的input,一个隐藏的input（用于保存文件url） -->
<input type="hidden" id="file_url" name="fujian" value=""/>
<!-- <img class="layui-upload-img" width="100px" height="80px" id="aftershow"/> -->
<a id="aftershow" href="#" download style="color:#32a5e6"></a>
</div>
</body>
</html>
五、controller后台
/**
* 导入excel
* @param file
* @param servletRequest
* @return
* @throws IOException
* @throws ParseException
*/
@RequestMapping(value = "/impExcel" , method = RequestMethod.POST)
@ResponseBody
public Map impExcel(@RequestParam("file")MultipartFile file,HttpServletRequest servletRequest)
throws IOException, ParseException {
Map res = new HashMap();
//上传文件路径
String path = servletRequest.getServletContext().getRealPath("/upload");
//上传文件名
String name = file.getOriginalFilename();//上传文件的真实名称
String suffixName = name.substring(name.lastIndexOf("."));//获取后缀名
String hash = UUID.randomUUID().toString().replaceAll("-","");
String fileName = hash + suffixName;
File filepath = new File(path, fileName);
//判断路径是否存在，没有就创建一个
if (!filepath.getParentFile().exists()) {
filepath.getParentFile().mkdirs();
}
//将上传文件保存到一个目标文档中
File tempFile = new File(path + File.separator + fileName);
file.transferTo(tempFile);
List<Map<String, String>> data = new ReadExcel().readExcel(path, fileName, 0, 0, 0);
Date date= new Date();//创建时间
SimpleDateFormat sdfs = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr= sdfs.format(date);
Date createtime=sdfs.parse(dateStr);
for (int i = 0; i < data.size(); i++) {
UserInfo userInfo = new UserInfo();
userInfo.setUserId(Integer.parseInt(data.get(i).get("userId")));
userInfo.setUserName(data.get(i).get("userName"));
userInfo.setEmail(data.get(i).get("email"));
userInfo.setPassword(data.get(i).get("password"));
userInfo.setMobile(data.get(i).get("mobile"));
userInfo.setCreateTime(createtime);
userInfo.setUpdateTime(createtime);
userInfo.setDelFlg("1");
xunpanService.addUserInfo(userInfo);
}
res.put("code", "0");
res.put("msg", "");
res.put("data", tempFile.getName());
return res;
}
六、通用读取excel工具类
package com.ssm;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
public List<Map<String,String>> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
String suffix = filename.substring(filename.lastIndexOf(".") + 1);
if ("xls".equals(suffix)) {
varList = readExcel2003(filepath, filename, startrow, startcol, sheetnum);
} else if ("xlsx".equals(suffix)) {
varList = readExcel2007(filepath, filename, startrow, startcol, sheetnum);
} else {
System.out.println("Only excel files with XLS or XLSX suffixes are allowed to be read!");
return null;
}
return varList;
}
/**
* 读取2003Excel
*
* @param filepath 文件路径
* @param filename 文件名，包括扩展名
* @param startrow 开始行号，索引从0开始
* @param startcol 开始列号，索引从0开始
* @param sheetnum 工作簿，索引从0开始
* @return
*/
public static List<Map<String,String>> readExcel2003(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
try {
File target = new File(filepath, filename);
FileInputStream fis = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
// sheet 从0开始
HSSFSheet sheet = wb.getSheetAt(sheetnum);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
HSSFRow rowTitle = sheet.getRow(0);
// 标题行的最后一个单元格位置
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
for (int i = startcol; i < cellTitleNum; i++) {
HSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
if (cell != null) {
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "";
}
}
// 行循环开始
for (int i = startrow + 1; i < rowNum; i++) {
Map<String, String> varpd = new HashMap<String, String>();
// 行
HSSFRow row = sheet.getRow(i);
// 列循环开始
for (int j = startcol; j < cellTitleNum; j++) {
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = "";
if (cell != null) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
}
varpd.put(title[j], cellValue);
}
varList.add(varpd);
}
wb.close();
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
/**
* 读取2007Excel
*
* @param filepath 文件路径
* @param filename 文件名，包括扩展名
* @param startrow 开始行号，索引从0开始
* @param startcol 开始列号，索引从0开始
* @param sheetnum 工作簿，索引从0开始
* @return
*/
public List<Map<String,String>> readExcel2007(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
try {
File target = new File(filepath, filename);
InputStream ins = new FileInputStream(target);
XSSFWorkbook wb = new XSSFWorkbook(ins);
ins.close();
// 得到Excel工作表对象
XSSFSheet sheet = wb.getSheetAt(sheetnum);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
XSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
for (int i = startcol; i < cellTitleNum; i++) {
XSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
if (cell != null) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "";
}
}
// 行循环开始
for (int i = startrow + 1; i < rowNum; i++) {
Map<String, String> varpd = new HashMap<String, String>();
// 得到Excel工作表的行
XSSFRow row = sheet.getRow(i);
// 列循环开始
for (int j = startcol; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
XSSFCell cell = row.getCell(j);
String cellValue = "";
if (cell != null) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
}
varpd.put(title[j], cellValue);
}
varList.add(varpd);
}
wb.close();
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
}
还有些将数据入库的代码 就不贴了。
最后看一下入库的效果吧：
147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
