需求
使用easyUi上传excel文件,并将excel里的数据存入数据库。
工具
Spring+SpringMVC+MyBatis,easyUi
(实验是在taotao商城的后台管理项目(源码即百度即可)的基础上实现的)
实现
easyUi实现界面
<form id="upExcelForm" method="post" enctype="multipart/form-data">
选择文件: <input id="uploadExcel" name="uploadExcel" class="easyui-filebox" style="width:200px" data-options="prompt:'请选择文件...'">
<a href="#" class="easyui-linkbutton" style="width:122px" onclick="uploadExcel()" >导入</a>
</form>
<script type="text/javascript">
function uploadExcel(){
//得到上传文件的全路径
var fileName= $('#uploadExcel').filebox('getValue');
var d1=/\.[^\.]+$/.exec(fileName);
if(d1==".xls") {
document.getElementById("upExcelForm").action="/item/upexcel";
document.getElementById("upExcelForm").submit();
$.messager.alert('提示','我已经上传了','info');
}
}
</script>
ExcelUtil.java
public static <T> List<T> excelToList(InputStream in,
Class<T> entityClass, LinkedHashMap<String, String> fieldMap,
String[] uniqueFields) throws ExcelException {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据Excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(in);
// 获取工作表,
//如果输入sheetName,则是根据sheet的名字来读取,我们的输入中直接使用第一个sheet表的数据,减少函数的变量
Sheet sheet = wb.getSheet(0);
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null
|| "".equals(currentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel中的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString()
.trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在,则抛出异常,提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段,或字段名称有误");
}
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
for (int j = 0; j < uniqueFields.length; j++) {
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet.findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1].getRow(), true);
if (sameCell != null) {
nullCols++;
}
}
if (nullCols == uniqueFields.length) {
throw new ExcelException("Excel中有重复行,请检查");
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fieldMap.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getKey();
// 获取英文字段名
String enNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents()
.toString().trim();
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
// 如果是ExcelException,则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e;
// 否则将其它异常包装成ExcelException再抛出
} else {
e.printStackTrace();
throw new ExcelException("导入Excel失败");
}
}
return resultList;
}
ExcelUtil代码是在jxl.jar包的基础上实现的。在github上也有很多这样的工具函数,这也是我在网上找的,去掉了一个函数参数,(源代码来自:http://blog.csdn.net/zwk626542417/article/details/43153005)
代码里面需要的ExcelException.java
public class ExcelException extends Exception {
private static final long serialVersionUID = 7582457389069996252L;
public ExcelException() {
}
public ExcelException(String message) {
super(message);
}
public ExcelException(String message, Throwable cause) {
super(message, cause);
}
public ExcelException(Throwable cause) {
super(cause);
}
public ExcelException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
super(message, cause, enableSuppression, writableStackTrace);
}
}
ssm部分代码实现
- TbUserMapper.xml编写
<insert id="insertList" parameterType="java.util.List">
insert into tb_user(username, password, phone, email, created, updated) values
<foreach collection="list" item="user" index="index" separator=",">
<trim prefix=" (" suffix=")" suffixOverrides="," >
<if test="user.id != null" >
#{user.id,jdbcType=BIGINT},
</if>
<if test="user.username != null" >
#{user.username,jdbcType=VARCHAR},
</if>
<if test="user.password != null" >
#{user.password,jdbcType=VARCHAR},
</if>
<if test="user.phone != null" >
#{user.phone,jdbcType=VARCHAR},
</if>
<if test="user.email != null" >
#{user.email,jdbcType=VARCHAR},
</if>
<if test="user.created != null" >
#{user.created,jdbcType=TIMESTAMP},
</if>
<if test="user.updated != null" >
#{user.updated,jdbcType=TIMESTAMP},
</if>
</trim>
</foreach>
</insert>
- TbUserMapper.java实现
int insertList(List<TbUser> record);
- 接口ItemService.java
void insertUserList(List<TbUser> users);
- 在ItemServiceImpl.java中实现该接口
public void insertUserList(List<TbUser> users) {
// TODO Auto-generated method stub
userMapper.insertList(users);
}
- controller中调用
@RequestMapping(value="/item/upexcel", method=RequestMethod.POST)
@ResponseBody
private void uploadExcel(@RequestParam("uploadExcel")CommonsMultipartFile uploadExcel, HttpServletRequest request, HttpServletResponse response) throws IOException {
InputStream in = uploadExcel.getInputStream();
LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();
// fieldMap.put("id", "id");
fieldMap.put("username", "username");
fieldMap.put("password", "password");
fieldMap.put("phone", "phone");
fieldMap.put("email", "email");
fieldMap.put("created", "created");
fieldMap.put("updated", "updated");
String[] uniqueNames = new String[1];
uniqueNames[0] = "username";
List<TbUser> users = new ArrayList<TbUser>();
try {
users = ExcelUtil2.excelToList(in, TbUser.class, fieldMap, uniqueNames);
} catch (ExcelException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
itemService.insertUserList(users);
// itemService.insertUsers(users);
// return users;
}
功能演示
管理界面导入111.xls,(jxl.jar貌似不支持.xlsx格式的表格,测试的时候没成功。只支持03版的)
到数据库去查看:
至此功能算是完成。