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;
}}
