Java通过jxl解析Excel文件入库，及日期格式处理方式 （附源代码）

时间：2023-03-08 16:47:32

JAVA可以利用jxl简单快速的读取文件的内容，但是由于版本限制，只能读取97-03  xls格式的Excel。

本文是项目中用到的一个实例，先通过上传xls文件（包含日期），再通过jxl进行读取上传的xls文件（文件格式见下user.xls），解析不为空的行与列，写入数据库。

文件user.xls格式为：

Java通过jxl解析Excel文件入库，及日期格式处理方式 （附源代码）

下面来看代码实例演示：

一、前端jsp页面（本来内容很多，这里精简了）

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html lang="en">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>用户列表</title>

</head>

<body>

<form:form action="${path}/user/batchadduser.do" method="post" modelAttribute="iptvuser" cssClass="form-inline">

  <label class="control-label" for="excelFile">批量导入：</label>

  <input name="excelFile" id="fileToUpload" type="file" />

  <input name="sbmt" id="sbmt" type="submit" />

</form>

  <br />

 </body>

<script type="text/javascript">

$('#sbmt').click(function chcekfile()

{

    var file = $("#fileToUpload").val();

     $.ajaxFileUpload

        (

            {

                url: '${path}/user/batchadduser.do',

                secureuri: false,

                fileElementId: 'fileToUpload',

                dataType: 'json',

                success: function(data, status) {

                    var result = data.result;

                    if(0 == result)

                    {

                        alert('*导入成功')

                    }else if(1 == result){

                        alert('*导入的用户账户包含不符合格式的数据，请先修正这些数据再导入。');

                    }else if(2 == result){

                        alert('*导入的用户账户包含已经存在的用户账户，请先删除这些数据再导入。');

                    }

                },

                error: function(data, status, e) {

                }

            }

        )

});

</script>

</html>

二、实体类及sql脚本

1、sql脚本为

CREATE TABLE `user` (

  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `user_code` varchar(32) NOT NULL COMMENT '用户编号',

  `districtId` varchar(32) DEFAULT NULL COMMENT '地区',

  `businessId` varchar(32) DEFAULT NULL COMMENT '业务分组',

  `access_time` date DEFAULT NULL COMMENT '访问时间',

  `cancel_account_status` varchar(10) DEFAULT NULL COMMENT '状态',

  PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户表';

2、实体类

package com.zealer.cps.base.model.entity.customer;

import java.util.Date;

import org.joda.time.DateTime;

import org.springframework.format.annotation.DateTimeFormat;

import com.zealer.cps.base.model.entity.BaseValue;

/**

 * 用户实体类

 */

public class UserValue

{

    private static final long serialVersionUID = 1L;

    private Integer userId;

    private String userCode;

    private String districtId;

    private String businessId;

    //访问时间

    @DateTimeFormat( pattern = "yyyy-MM-dd HH:mm:ss" )

    private Date accessTime;

    //状态

    private String cancelAccountStatus;

//set与get以及toString方法省略，大家用eclipse可以自己生成

   ...

}

三、控制类

package com.zealer.cps.customer.controller;

import java.io.File;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Locale;

import java.util.Map;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.MessageSource;

import org.springframework.http.HttpHeaders;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

import org.springframework.web.multipart.MultipartFile;

import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.zealer.cps.base.annotation.Log;

import com.zealer.cps.base.annotation.MarkRequest;

import com.zealer.cps.base.constant.AppConstant;

import com.zealer.cps.base.constant.ParamConstants;

import com.zealer.cps.base.controller.BaseController;

import com.zealer.cps.base.message.ErrorActionResult;

import com.zealer.cps.base.message.SuccessActionResult;

import com.zealer.cps.base.model.entity.customer.UserTagValue;

import com.zealer.cps.base.model.entity.customer.UserValue;

import com.zealer.cps.base.model.entity.customer.LabelManagerValue;

import com.zealer.cps.base.util.AssertHelper;

import com.zealer.cps.base.util.ExcelUtils;

import com.zealer.cps.base.util.FileUploadUtil;

import com.zealer.cps.base.util.HttpRequestUtils;

import com.zealer.cps.base.util.HttpUtils;

import com.zealer.cps.base.util.JSONHelper;

import com.zealer.cps.customer.service.ArrearageUserService;

import com.zealer.cps.customer.service.IptvGroupService;

import com.zealer.cps.customer.service.UserService;

import com.zealer.cps.customer.service.LabelManagerService;

import com.zealer.cps.operation.impl.OperationInterfaceImp;

@Controller

@RequestMapping( "/user" )

public class UserController extends BaseController

{

    @Resource( name = "userService" )

    private UserService userService;

    /**

     * 往某一分组里批量添加用户记录信息

     * @param id 用户id

     * @return

     *

     */

    @ResponseBody

    @RequestMapping( value = "/batchadduser", produces = "application/json" )

    @Log( "批量添加用户信息" )

    public ResponseEntity<String> batchAddUser( @RequestParam ("excelFile") MultipartFile excelFile, Model model,

                         HttpServletRequest request, Locale locale )

    {

        Map<String, Object>  jsonMap = new HashMap<String, Object>();

        String dir = FileUploadUtil.getFileRealPath( request, excelFile.getName(), "Excel" );

        String ctxPath = FileUploadUtil.getTomcatPath( request );

        String localPath = HttpRequestUtils.getContextPath( request ) + "/";

        try

        {

            Map<String, Object> resutlMap = ExcelUtils.excelImportUser( ctxPath + dir, request );

            List<UserValue> errorList = (List<UserValue>)resutlMap.get( "error" );

            /* 如果Excel中存在不符合格式的数据则返回不符合格式的数据 */

            if ( AssertHelper.isNotEmptyCollection( errorList ) )

            {

                jsonMap.put( "result", 1 );

                String jsonStr = JSONHelper.toJson( jsonMap );

                return(new ResponseEntity<String> ( jsonStr, HttpStatus.OK ) );

            }

            List<UserValue> successList  = (List<UserValue>)resutlMap.get( "success" );

            Map<String, List<UserValue> >  map  = checkUserCodeIsExist( successList );

            /* 如果导入的用户账号中存在系统中没有的账号则进行插入操作 */

            List<UserValue> notExistList = map.get( "noExistList" );

            if ( AssertHelper.isNotEmptyCollection( notExistList ) )

            {

                userService.batchInsertUser( notExistList );

            }

        }

        catch ( Exception e )

        {

            log.error( "batchadd user to group error::", e );

            jsonMap.put( "result", -1 );

            String jsonStr = JSONHelper.toJson( jsonMap );

            return(new ResponseEntity<String> ( jsonStr, HttpStatus.BAD_REQUEST ) );

        }

        jsonMap.put( "result", 0 );

        String jsonStr = JSONHelper.toJson( jsonMap );

        return(new ResponseEntity<String> ( jsonStr, HttpStatus.OK ) );

    }

    /**

     * 校验导入的用户账号是否已经存在系统中了

     * @param list 待导入的用户账号

     * @return 存在则返回存在的用户账号list，不存在则返回空的list

     */

    private Map<String, List<UserValue>> checkUserCodeIsExist(List<UserValue> list)

    {

        Map<String, List<UserValue>> map = new HashMap<String, List<UserValue>>();

        List<String> allList = UserService.getAllUsersCode();

        List<UserValue> existList = new ArrayList<UserValue>();

        List<UserValue> noExistList = new ArrayList<UserValue>();

        for (UserValue UserValue : list)

        {

            if (allList.contains(UserValue.getUserCode()))

            {

                existList.add(UserValue);

            }

            else

            {

                noExistList.add(UserValue);

            }

        }

        map.put("existList", existList);

        map.put("noExistList", noExistList);

        return map;

    }

}

三、工具类

控制器中用到的工具类的方法有分别有解析excel文件的、JSON工具类等，详细见下面代码。

1、读取excel的工具类ExcelUtils

ExcelUtils.java

package com.zealer.cps.base.util;

import java.io.File;

import java.io.IOException;

import java.io.OutputStream;

import java.io.UnsupportedEncodingException;

import java.lang.reflect.Field;

import java.net.URLEncoder;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import org.joda.time.DateTime;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.stereotype.Component;

import org.springframework.stereotype.Controller;

import com.zealer.cps.base.constant.ParamConstants;

import com.zealer.cps.base.dao.BaseDaoInterface;

import com.zealer.cps.base.model.entity.customer.UserTagValue;

import com.zealer.cps.base.model.entity.customer.UserValue;

import com.zealer.cps.base.model.entity.customer.LabelManagerValue;

import com.zealer.cps.base.model.request.customer.LabelManagerReq;

import com.zealer.cps.customer.service.LabelManagerService;

import jxl.CellType;

import jxl.DateCell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.NumberFormats;

import jxl.write.WritableCell;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class ExcelUtils

{

    private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

/**

     * 根据给定的文件路径读取iptv用户的业务账号信息存放到list中并返回

     * 目前只支持Excel2003以前的版本，暂不支持2007及以后的版本

     * @param fileDir

     *            上传后的Excel文件路径

     * @return map对象,包含两个结果list和操作信息，一个是符合要求的结果list，另一个是不符合要求的list。

     *         分别是[success:s_list,error:e_list,message:异常信息]

     */

    public static Map<String, Object> excelImportUser( String fileDir, HttpServletRequest request)

    {

        Workbook book = null;

        Map<String, Object> resultMap = new HashMap<String, Object>();

        List<UserValue> s_list = new ArrayList<UserValue>();

        List<UserValue> e_list = new ArrayList<UserValue>();

        boolean flag = false;

        try {

            book = Workbook.getWorkbook(new File(fileDir.replace("\\", "/")));

            for (int i = 0; i < book.getNumberOfSheets(); i++)

            {

                Sheet sheet = book.getSheet(i);

                // 有多少行

                int rowSize = sheet.getRows();

                for (int j = 1; j < rowSize; j++)

                {

                    // 有多少列

                    int columns = sheet.getColumns();

                    UserValue User = new UserValue();

                    //正则表达式，中文、字母或数字

                    String regex = "^[A-Za-z\\d\\u4E00-\\u9FA5]+$";

                    /**

                     * 区域，第一列

                     * */

                    String districtId = sheet.getCell(0, j).getContents();

                    /**

                     * 用户编号，第二列

                     * */

                    String userCode = sheet.getCell(1, j).getContents();

                    if (AssertHelper.isEmptyString(userCode))

                    {

                        continue;

                    }

                    else

                    {

                        userCode = userCode.trim();

                    }

                    //用户账号长度不能大于20位

                    if (userCode.length()>20 || !userCode.matches(regex) )

                    {

                        e_UserTag.setUserCode(userCode);

                        flag = true;

                    }

                    /**

                     * 访问时间,第三列

                     * */

                    DateTime accessTime = null;

                    //日期格式处理方式：

                    if(sheet.getCell(2, j).getType() == CellType.DATE){

                         DateCell dc = (DateCell)sheet.getCell(2, j);

                         Date date = dc.getDate();    //获取单元格的date类型

                         accessTime =  new DateTime(date);

                    }

                    /**

                     * 销户状态，第四列

                     * */

                    String cancelAccountStatus = sheet.getCell(3, j).getContents();

                    /**

                     * 用户分组，第五列

                     * */

                    String businessId = sheet.getCell(4, j).getContents();

                    }

                    User.setDistrictId(districtId);

                    User.setUserCode(userCode);

                    User.setAccessTime(new Date());

                    User.setCancelAccountStatus(cancelAccountStatus);

                    User.setBusinessId(businessId);

                    User.setCreateTime(new Date());

                    if (flag )

                    {

                        e_list.add(User);

                        flag = false;

                    }

                    else

                    {

                        s_list.add(User);

                    }

                }

            }

        }

        catch (BiffException e)

        {

            log.error("inport occur error::",e);

            e.printStackTrace();

            resultMap.put("message", "无法读取Excel文件！");

        }

        catch (IOException e)

        {

            log.error("inport occur error::",e);

            resultMap.put("message", "读取文件时IO发生错误！");

        }

        finally

        {

            if (book != null)

            {

                book.close();

                book = null;

            }

        }

        resultMap.put("success", s_list);

        resultMap.put("error", e_list);

      return resultMap;

    }

}

2、文件上传工具类

package com.zealer.cps.base.util;

import java.io.File;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.util.FileCopyUtils;

import org.springframework.web.multipart.MultipartHttpServletRequest;

import org.springframework.web.multipart.commons.CommonsMultipartFile;

/**

 *

 * 文件上传工具类

 * 文件上传到tomcat 下的 webapps\resources 文件夹下*/

public class FileUploadUtil

{

    public static Logger log = LoggerFactory.getLogger(FileUploadUtil.class);

    public final static String ROOTPATH = File.separator;

    /**

     *

     * 将上传的文件保存在服务器 fileupload/yyyy/MM/dd 文件夹下 返回文件保存后的相对路径

     *

     * @param request

     * @param inputname

     *            对应文件上传表单中input的name 例如 'input type="file" name="file"'

     * @param FilePath 需要保存的路径

     * @return 返回文件存储的相对路径

     */

    public static String getFileRealPath(HttpServletRequest request,

        String inputName,String FilePath)

    {

        log.debug("-----------------------rootPath="+ROOTPATH);

        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

        CommonsMultipartFile file = (CommonsMultipartFile) multipartRequest.getFile(inputName);

        // 获得文件名：

        String realFileName = file.getOriginalFilename();

        if (AssertHelper.isNotEmptyString(realFileName))

        {

            // 获取路径

            String tomcatPath = getTomcatPath(request);

            String ctxPath = tomcatPath + "resources" + ROOTPATH  + "fileupload" + ROOTPATH + FilePath + ROOTPATH;

            // 创建文件

            String randomPath = getDateDir();

            String fileSuffix = getFileSuffix(realFileName);

            File dirPath = new File(ctxPath + ROOTPATH + randomPath);

            if (!dirPath.exists())

            {

                dirPath.mkdirs();

            }

            File uploadFile = new File(ctxPath + ROOTPATH + randomPath + ROOTPATH

                    + UUID.randomUUID().toString() + fileSuffix);

            try

            {

                FileCopyUtils.copy(file.getBytes(), uploadFile);

            }

            catch (IOException e)

            {

                log.error(e.getMessage());

            }

            String result = uploadFile.getAbsolutePath();

            String pathName = result.substring(result.lastIndexOf("resources"));

            pathName = pathName.replace("\\", "/");

            return pathName;

        }

        else

        {

            log.debug("file is not found !");

        }

        return "";

    }

    public static String getDateDir()

    {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy" + ROOTPATH + "MM" + ROOTPATH + "dd");

        String dir = sdf.format(new Date());

        return dir;

    }

    public static String getFileSuffix(String filename)

    {

        return filename.substring(filename.lastIndexOf("."));

    }

    public static String getTomcatPath(HttpServletRequest request){

        String realPath = request.getSession().getServletContext().getRealPath(ROOTPATH);

        String contextPath = request.getContextPath( );

        String endStr = contextPath.substring(1);

        String result = realPath.substring(0,realPath.lastIndexOf(endStr));

        return result;

    }

}

3、请求、路径工具类

package com.zealer.cps.base.util;

import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletRequest;

import org.springframework.web.context.WebApplicationContext;

import org.springframework.web.context.support.WebApplicationContextUtils;

public class HttpRequestUtils {

    /**

     * 获取请求的URI,不包含ip、端口和项目名称 eg：in >

     * http://127.0.0.1:8080/project/user/login.do out > user/login.do

     * @param request

     * @return

     */

    public static String getRequestUri(HttpServletRequest request) {

        String contextPath = getContextPath(request);

        String requestUri = request.getRequestURI().substring(

                contextPath.length() + 1); // 去掉上下文路径和"/"

        return requestUri;

    }

    /**

     * 获取项目的URI eg: in > http://127.0.0.1:8080/project/user/login.do out >

     * /project

     *

     * @param request

     * @return

     */

    public static String getContextPath(HttpServletRequest request) {

        String contextPath = request.getSession().getServletContext()

                .getContextPath();

        return contextPath;

    }

    /**

     * 获取项目的URL eg：in > http://127.0.0.1:8080/project/user/login.do out >

     * http://127.0.0.1:8080/project/

     *

     * @param request

     * @return

     */

    public static String getProjectUrl(HttpServletRequest request) {

        String url = request.getRequestURL().toString();

        int endIndex = StringUtils.getPosition(url, "/", 4);

        String hostProject = url.substring(0, endIndex);

        return hostProject;

    }

    /**

     * eg：in > http://127.0.0.1:8080/project/user/login.do out >

     * http://127.0.0.1:8080/

     *

     * @param request

     * @return

     */

    public static String getProjectDomain(HttpServletRequest request) {

        String projectDomain = request.getScheme() + "://"

                + request.getServerName() + ":" + request.getServerPort() + "/";

        return projectDomain;

    }

    /**

     * 获取项目的绝对路径 eg: D:/server/tomcat6/webapps/ROOT/

     *

     * @param request

     * @return

     */

    public static String getProjectAbsoultPath(HttpServletRequest request) {

        return request.getSession().getServletContext().getRealPath("/");

    }

    /**

     * 获取项目Class文件的绝对路径 eg: D:/server/tomcat6/webapps/ROOT/WEB-INF/classes/

     *

     * @param request

     * @return

     */

    public static String getProjectClassAbsoultPath() {

        return HttpRequestUtils.class.getResource("/").getPath().substring(1);

    }

    /**

     * 判断请求内容是否为JSON格式

     *

     * @param request

     * @return true 表示为JSON格式

     */

    public static boolean isJsonContent(HttpServletRequest request) {

        String contentType = request.getHeader("Content-Type");

        if (contentType == null

                || contentType.indexOf("application/json") == -1) {

            return false;

        }

        return true;

    }

    /**

     * 判断是否是AJAX请求

     *

     * @param request

     * @return true 表示是AJAX请求

     */

    public static boolean isAjaxRequest(HttpServletRequest request) {

        boolean isAjaxRequest = "XMLHttpRequest".equals(request

                .getHeader("X-Requested-With"))

                || request.getParameter("ajax") != null;

        return isAjaxRequest;

    }

    /**

     * 根据传入的bean id的名称获取该bean的实例对象

     * @param servletContext 上下文对象

     * @param beanName bean id的名称

     * @return 实例对象

     */

    public static Object getBeanByName(ServletContext servletContext,String beanName)

    {

        WebApplicationContext applicationContext = WebApplicationContextUtils.getWebApplicationContext(servletContext);

        return applicationContext.getBean(beanName);

    }

}

4、JSON工具类

package com.zealer.cps.base.util;

import java.util.ArrayList;

import java.util.List;

import org.codehaus.jackson.map.ObjectMapper;

import org.codehaus.jackson.map.type.TypeFactory;

/**

 * json 帮助类 对象和JSON互相转换

 */

public class JSONHelper {

    private static final ObjectMapper OBJECT_MAPPER    = new ObjectMapper();

    /**

     * 将Object对象转为JSON字符串

     * @param object

     * @return

     */

    public static String toJson(Object object) {

        String json = null;

        try {

            json = OBJECT_MAPPER.writeValueAsString(object);

        } catch (Exception e) {

            throw new RuntimeException("To json error, object is "+object+";exception:"+e);

        }

        return json;

    }

    /**

     * 将一个JSON字符串转换为Object对象

     * @param <T>

     * @param json

     * @param clazz

     * @return

     */

    public static <T> T toObject(String json, Class<T> clazz) {

        T o = null;

        if (json != null) {

            try {

                o = OBJECT_MAPPER.readValue(json, clazz);

            } catch (Exception e) {

                throw new RuntimeException("Json string To object error, json is "+json+";exception:"+e);

            }

        }

        return o;

    }

    /**

     * 将一个JSON字符串转换为List<T>对象

     * @param <T>

     * @param json

     * @param clazz

     * @return

     */

    @SuppressWarnings("deprecation")

    public static <T> List<T> toList(String json, Class<T> clazz) {

        List<T> o = null;

        if (json != null) {

            try {

                o = OBJECT_MAPPER.readValue(json, TypeFactory.collectionType(ArrayList.class, clazz));

            } catch (Exception e) {

                throw new RuntimeException("Json string To List<object> error, json is "+json+";exception:"+e);

            }

        }

        return o;

    }

}