本文档详细介绍了在一个基于SSM（Spring、SpringMVC、MyBatis）的环境中，如何实现类目数据的导入功能。从jsp页面设计、JavaScript处理、实体类和控制器的编写等方面进行了阐述，包括文件上传、数据校验、模板下载以及导入后的结果显示。此外，还涉及到了Excel文件的导入和解析，以及如何处理导入数据的校验和存储。
导入文件详细步骤
一、搭建是ssm环境（略）
二、jsp内容（表格模板的存放位置就是下载模板的位置）
定义一个名为groupCateList.jsp的文件
<div class="addpop_cont pdlr-0">
<div class="page" style="padding-top:30px;">
<div class="fixed-bar">
<div class="item-title">
<div class="subject">
<h3>
选择导入文件:<a href="${ctxStatic}/plat2016/templete/groupCategoryDome.xlsx"
style="color: #3b639f">【类目导入模板下载.xlsx】</a>
</h3>
</div>
</div>
</div>
<form id="importDataList" class="form-inline"
action="" method="post"
modelAttribute="entityBusi" enctype="multipart/form-data">
<div class="row">
<div class="form-group col-lg-6">
<div class="col-sm-9">
<input type="file" id="file" name="file" class="input-n4 fl w-input262"/>
</div>
</div>
<div class="form-group col-lg-12">
<div class="col-sm-10" id="showErrorMessage"></div>
</div>
</div>
</form>
<div class="modal-footer" style="padding-top:20px;">
<input type="button" value="取消" class="btn-gray-outline " onclick="closeImportPop()">
<input type="button" value="确认" class="btn-yellow " onclick="excelFileImport('file');">
</div>
</div>
</div>
//导入数据
function importExcel() {
$("#importPop").removeClass("dis-none");
$("#importPop").show();
}
function checkExcelFile(fileId) {
var filepath = $("#" + fileId).attr("value");
filepath = filepath.substring(filepath.lastIndexOf('.') + 1, filepath.length);
filepath = filepath.toLowerCase();
if (filepath == '' || filepath == null) {
$("#showErrorMessage").text("请选择导入数据文件");
return false;
}
if (filepath != 'xlsx' && filepath != 'xls') {
$("#showErrorMessage").text("请下载对应模板填写数据");
return false;
}
return true;
}
function closeImportPop() {
$("#importPop").hide();
$("#importPop").addClass("dis-none");
}
function excelFileImport(fileId) {
if (checkExcelFile(fileId)) {
$("#importDataList").attr("action", "${ctx}/group/cate/importData");
loading('正在提交，请稍等...');
$("#importDataList").submit();
}
}
四、实体类内容
import java.util.Date;
import java.util.LinkedHashMap;
import com.ai.ecs.common.utils.excel.annotation.ExcelField;
import com.ai.iis.domain.pojo.base.DataEntity;
public class GroupCategory {
private static final long serialVersionUID = -8991882881883173222L;
@ExcelField(title="类目Id",sort=1)
private String cateId;//类目id
@ExcelField(title="父级类目id",sort=3)
private String parentId;//父级id
private String parentIds;
@ExcelField(title="层级",sort=4)
private String cateLevel;//层级
@ExcelField(title="类目名称",sort=2)
private String cateName;//类目名称
}
然后给实体类添加get和set方法，再添加tostring和hashcode方法，直接鼠标右键然后选择generate,然后选择添加这些方法就可以，后者使用快捷键alt+insert，快速选择就OK
@ExcelField(title=“类目名称”,sort=2)这个注解必须要添加，
五、控制器controller
1、控制器第一步必须要有个ModelAndView返回到前面写的jsp中，
@Controller
@RequestMapping(value = "${adminPath}/group/cate/")
public class GroupCategoryController {
@Autowired
private IGroupCategoryService groupCategoryService;
@Autowired
private IGroupCategoryGoodsService groupCategoryGoodsService;
/*
* 初始化类目列表页面，递归查询
*/
@RequestMapping(value = "initPage")
public ModelAndView initPage(GroupCategory groupCategory, HttpServletRequest request, HttpServletResponse response) {
ModelAndView mav = new ModelAndView("modules/group/cate/groupCateList");
//查询类目树形结构
List<GroupCategory> groupCategoryList = groupCategoryService.queryAllGroupCategoryList();
for (int i = 0; i < groupCategoryList.size(); i++) {//剔除状态为0或者删除过的数据
if (groupCategoryList.get(i).getCateStatus().equals("0")
|| groupCategoryList.get(i).getDelFlag().equals("1")) {
groupCategoryList.remove(i);
i--;
}
}
/* GroupCategory info = new GroupCategory();
info.setCateId("0");
info.setCateLevel("0");//层级
info.setCateName("类目");//类目名称
info.setCateSort("0");
groupCategoryList.add(info);*/
mav.addObject("groupCategoryList", groupCategoryList);
return mav;
}
第二步，写导入类目的控制器，在这里需要返回到导入之后的结果，而且需要一个校验表格的类，首先，先写导入之后的页面groupCateImport.jsp
<%@ page contentType="text/html;charset=UTF-8"%>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<html>
<head>
<title>类目导入结果</title>
<%@ include file="/WEB-INF/views/mod2016/include/head.jsp"%>
</head>
<body class="bg-gray">
<div id="tabchunk">
<div class="recommend-s1 bg-white mgt-10">
<div class="recom-top recom-tops1 clearfix">
<div class="floor-tabs">
<ul class="clearfix">
<li class="active"><a href="javascript:void(0)">导入详情</a></li>
</ul>
<span style="left: 0px;" class="tabs-guide"></span>
<a href="${ctx}/group/cate/initPage" class=" fr mgr-10 edited-back-btn">返回</a>
</div>
</div>
</div>
<div class="list-top pdlr-20 clear pdt-5">
<div class="list-title fl fs14">类目列表<span class="fs12 font-gray">（成功${successNum}条，失败${failureNum}条）</span>
</div>
</div>
<div class="list-con pdlr-20 clear js_pans">
<table id="dataTable" border="0" width="100%" class=" table-style4 js_table_style4">
<thead>
<tr class="erow">
<td>类目ID </td>
<td>类目名</td>
<td>父级类目id</td>
<td>层级</td>
<td>导入结果</td>
<td>失败原因</td>
</tr>
</thead>
<tbody>
<c:forEach items="${addList}" var="item">
<tr class="erow">
<td>${item.cateId}</td>
<td>${item.cateName}</td>
<td>${item.parentId}</td>
<td>${item.cateLevel}</td>
<td>${item.importResult=='0'?'成功':'失败'}</td>
<td>${item.failReason}</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</body>
</html>
校验表格的类
/**
* 导入Excel文件（支持“XLS”和“XLSX”格式）
* @author ThinkGem
* @version 2013-03-10
*/
public class ImportExcel {
private static final int FIVE_EIGHT = 58;
private static final int TWO = 2;
private static final int THREE = 3;
private static final int NUM_58 = 58;
private static final Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
* @param path 导入文件，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
* @param path 导入文件对象，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
* @param path 导入文件
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
* @param file 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)){
throw new RuntimeException("导入文档为空!");
}else if(fileName.toLowerCase().endsWith("xls")){
this.wb = new HSSFWorkbook(is);
}else if(fileName.toLowerCase().endsWith("xlsx")){
this.wb = new XSSFWorkbook(is);
}else{
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets()<sheetIndex){
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
log.debug("Initialize success.");
}
/**
* 获取行对象
* @param rownum
* @return
*/
public Row getRow(int rownum){
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
* @return
*/
public int getDataRowNum(){
return headerNum+1;
}
/**
* 获取最后一个数据行号
* @return
*/
public int getLastDataRowNum(){
return this.sheet.getLastRowNum()+headerNum;
}
/**
* 获取最后一个列号
* @return
*/
public int getLastCellNum(){
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column){
Object val = "";
try{
Cell cell = row.getCell(column);
if (cell != null){
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
//防止长数字变成科学计数法显示
DecimalFormat df = new DecimalFormat("0");
val = df.format(cell.getNumericCellValue());
}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
val = cell.getStringCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
val = cell.getCellFormula();
}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
val = cell.getBooleanCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
val = cell.getErrorCellValue();
}
}
}catch (Exception e) {
return val;
}
return val;
}
public static Object getCellString(Cell cell) {
Object result = null;
if (cell != null) {
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == NUM_58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是FIVE_EIGHT)
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
// if (HSSFDateUtil.isCellDateFormatted(cell)) {
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd
// HH:mm:ss");
// double value = cell.getNumericCellValue();
// Date date =
// org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
// result = sdf.format(date);
// }else{
// result = cell.getNumericCellValue();
// }
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
default:
break;
}
}
return result;
}
/**
* 获取导入数据列表
* @param cls 导入对象类型
* @param groups 导入分组
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
List<Object[]> annotationList = Lists.newArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs){
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==TWO)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms){
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==TWO)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
@Override
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
//log.debug("Import column count:"+annotationList.size());
// Get excel data
List<E> dataList = Lists.newArrayList();
for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
E e = (E)cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
Object val = this.getCellValue(row, column++);
if (val != null){
ExcelField ef = (ExcelField)os[0];
// If is dict type, get dict value
/*if (StringUtils.isNotBlank(ef.dictType())){
val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
}*/
// Get param type and type cast
Class<?> valType = Class.class;
if (os[1] instanceof Field){
valType = ((Field)os[1]).getType();
}else if (os[1] instanceof Method){
Method method = ((Method)os[1]);
if ("get".equals(method.getName().substring(0, THREE))){
valType = method.getReturnType();
}else if("set".equals(method.getName().substring(0, THREE))){
valType = ((Method)os[1]).getParameterTypes()[0];
}
}
//log.debug("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == String.class){
String s = String.valueOf(val.toString());
if(StringUtils.endsWith(s, ".0")){
val = StringUtils.substringBefore(s, ".0");
}else{
val = String.valueOf(val.toString());
}
}else if (valType == Integer.class){
val = Double.valueOf(val.toString()).intValue();
}else if (valType == Long.class){
val = Double.valueOf(val.toString()).longValue();
}else if (valType == Double.class){
val = Double.valueOf(val.toString());
}else if (valType == Float.class){
val = Float.valueOf(val.toString());
}else if (valType == Date.class){
val = DateUtil.getJavaDate((Double)val);
}else{
if (ef.fieldType() != Class.class){
val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
}else{
val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
}
}
} catch (Exception ex) {
log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field){
Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
}else if (os[1] instanceof Method){
String mthodName = ((Method)os[1]).getName();
if ("get".equals(mthodName.substring(0, THREE))){
mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
}
}
sb.append(val+", ");
}
dataList.add(e);
log.debug("Read success: ["+i+"] "+sb.toString());
}
return dataList;
}
// /**
// * 导入测试
// */
/*public static void main(String[] args) throws Throwable {
ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
Row row = ei.getRow(i);
for (int j = 0; j < ei.getLastCellNum(); j++) {
Object val = ei.getCellValue(row, j);
}
}
}*/
}
然后是导入数据的控制器
/*
* 导入
* */
@RequestMapping(value = "importData", method = RequestMethod.POST)
public String importData(MultipartFile file, Model model) {
int successNum = 0;
int failureNum = 0;
try {
ImportExcel ei = new ImportExcel(file.getOriginalFilename(),
file.getInputStream(), 0, 0);
List<GroupCategory> importInfos = ei.getDataList(GroupCategory.class);//获取表格数据转换成实体类数据
List<GroupCategory> addList = new ArrayList<>();
for (GroupCategory groupCategory : importInfos) {//遍历数据
if (!groupCategory.getParentId().isEmpty() && !groupCategory.getCateName().isEmpty()) {//父级id级类目名称为必填项
GroupCategory category = groupCategoryService.queryGroupCategoryByName(groupCategory.getCateName());//判断数据库中是否已经存在
if (category == null) {
groupCategoryService.insertGroupCategoryInfo(groupCategory);
groupCategory.setImportResult("0");
addList.add(groupCategory);
successNum++;
} else {
groupCategory.setImportResult("1");
groupCategory.setFailReason("类目已经存在，请修改类目名");
addList.add(groupCategory);
failureNum++;
}
} else {
groupCategory.setImportResult("1");
groupCategory.setFailReason("父级id和类目名不能为空");
addList.add(groupCategory);
failureNum++;
}
}
model.addAttribute("successNum", successNum);
model.addAttribute("failureNum", failureNum);
model.addAttribute("addList", addList);
} catch (Exception e) {
}
return "modules/group/cate/groupCateImport";
}
控制器完成，之后得完成相对应Service接口和数据库查询的dao
首先是service接口（名字根据controller里面的bean命名）：
/*根据类目名称查询类目信息*/
GroupCategory queryGroupCategoryByName(String cateName);
/* *
* 新增类目信息
* @param groupCategory
* @return
*/
public int insertGroupCategoryInfo(GroupCategory groupCategory);
然后是实现类，注意添加@Service注解和注入dao
@Override
public GroupCategory queryGroupCategoryByName(String cateName) {
return GroupCategoryDao.queryGroupCategoryByName(cateName);
}
/*
* 新增类目
*/
@Override
public int insertGroupCategoryInfo(GroupCategory groupCategory) {
return GroupCategoryDao.insertGroupCategoryInfo(groupCategory);
}
接下来到对应的dao以及xml文件
dao：
/*根据名字获取类目*/
GroupCategory queryGroupCategoryByName(String cateName);
//新增类目
int insertGroupCategoryInfo(GroupCategory groupCategory);
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ai.iis.newuop.group.dao.IGroupCategoryDao">
<!--根据名称获取类目-->
<select id="queryGroupCategoryByName" resultType="com.ai.iis.domain.pojo.group.GroupCategory"
parameterType="java.lang.String">
select * from new_uop.TF_GROUP_CATEGORY T WHERE T.CATE_NAME=#{cateName} AND DEL_FLAG='0'
</select>
<!--新增类目信息-->
<insert id="insertGroupCategoryInfo" parameterType="com.ai.iis.domain.pojo.group.GroupCategory">
INSERT INTO TF_GROUP_CATEGORY (PARENT_ID,CATE_NAME,CATE_SORT,CREATE_DATE,CATE_LEVEL)
VALUES(#{parentId},#{cateName},#{cateSort},sysdate,#{cateLevel})
</insert>
</mapper>
扫一扫
392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
