excel导入数据库

需求

使用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版的）
到数据库去查看：

至此功能算是完成。