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

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

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

文件user.xls格式为：

下面来看代码实例演示：

一、前端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;
    }

}

posted @ 2017-06-15 22:59 骑白马的菜鸟阅读(5868) 评论(0) 编辑收藏举报

刷新页面返回顶部

zishengY

每一个你不满意的当下，都有一个你不曾努力的曾经

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

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

二、实体类及sql脚本

1、sql脚本为

2、实体类

三、控制类

三、工具类

1、读取excel的工具类ExcelUtils

2、文件上传工具类

3、请求、路径工具类

4、JSON工具类

公告

zishengY

每一个你不满意的当下，都有一个你不曾努力的曾经

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

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

二、实体类及sql脚本

1、sql脚本为

2、实体类

三、控制类

三、工具类

1、读取excel的工具类ExcelUtils

2、文件上传工具类

3、请求、路径工具类

4、JSON工具类

公告

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