这是我做的一个项目中用到的一个功能：把Excel中的数据导入到数据库中并显示在页面；我上网查了很久，也有一些例子是导入的，但是添加到我的项目中都不能用，有用插件的（jquery.uploadify.js）但是这个插件不好用，在这里我想把原因解释一下：
首先这个插件的兼容性不是很好，有的浏览器不能用；其次在项目中导入2007+版本的Excel会报错，java流转换的时候报错，可能还有解决办法，但是这里我没有找到。下面我就把我的代码写出来，但是不一定适合大家，希望可以帮助一些人。
1.通过form表单提交
<form method="POST" enctype="multipart/form-data" id="form1" action="" target="rfFrame">
<input type="hidden" name="loginid" value="{{zh}}">
<input type="hidden" name="nc" value="{{nc}}">
<input type="hidden" name="app" value="{{apptenant}}">
<input type="hidden" name="bus" value="{{bustenant}}">
<table>
<tr>
<td>上传文件: </td>
<td> <input id="upfile" type="file" name="upfile"></td>
<td><input type="submit" value="提交" on-click="ajaxSubmitForm"></td>
</tr>
</table>
</form>
<iframe id="rfFrame" name="rfFrame" src="about:blank" style="display:none;"></iframe>代码input框中的数据是隐藏的，因为业务需要，后台需要这些参数完成调用接口，所以这里可以根据自己的需要选择，也可以不要。
2.前台js层
function ajaxSubmitForm （）{
var result = "";
var option = {
url : Constants.path+'/tmgl/upload',
type : 'POST',
dataType : 'json',
beforeSend:function(xhr){//请求之前
// var index = layer.load(1, {
// shade: [0.5,'#000'] //0.5透明度的黑色背景
// });
},
success:function(data)
{
result = data.result;
},
complete:function(responseText,xhr){//请求完成
console.log(responseText);
//layer.closeAll('loading');
//询问框
// layer.confirm('Excel导入成功！', {
// btn: ['确定'] //按钮
// }, function(){
// location.href = "tmgl.html";//location.href实现客户端页面的跳转
// });
function tmFunc(){
location.href = "tmgl.html";
}
var sucObj = {
txt: $.i18n.prop('i18n_tmk_drcg'),
callBackFun: tmFunc
}
success(sucObj);
},
error: function(xhr,status,msg){
//alert("状态码"+status+"; "+msg);
//layer.msg('玩命加载中..');
}
};
if(checkData()){
$("#form1").ajaxSubmit(option);
}
}
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
alert("选择Excel格式的文件导入！");
return false;
}
return true;
} 3.后台Controller层
@RequestMapping(value = "/upload", method = { RequestMethod.GET, RequestMethod.POST })
public ResultJson uploadExcel(HttpServletRequest request, HttpServletResponse response) {
//response.setContentType("text/html; charset=utf-8");
ResultJson resultJson = new ResultJson();
String loginid = request.getParameter("loginid");
String nc = request.getParameter("nc");
String apptenant = request.getParameter("app");
String bustenant = request.getParameter("bus");
System.out.println("登录账号" + loginid);
logger.info("登录账号" + loginid);
List<Object> listob = null;
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
System.out.println("通过传统方式form表单提交方式导入excel文件！");
logger.info("通过传统方式form表单提交方式导入excel文件！");
InputStream in = null;
MultipartFile file = multipartRequest.getFile("upfile");
if (file.isEmpty()) {
try {
throw new Exception("文件不存在！");
} catch (Exception e) {
e.printStackTrace();
}
}
try {
in = file.getInputStream();
listob = new ImportExcelUtil().getObjectByExcel(in, file.getOriginalFilename(), ParamTmkExcel.class);
in.close();
} catch (Exception e) {
e.printStackTrace();
}
//listob = new ImportExcelUtil().importExceList(request);
// 封装Excel解析后的数据
List<ParamTmkExcel> paramTmkExcelList = new ArrayList<ParamTmkExcel>();
for (int i = 0; i < listob.size(); i++) {
ParamTmkExcel vo = new ParamTmkExcel();
vo = (ParamTmkExcel) listob.get(i);
paramTmkExcelList.add(vo);
}
// 封装页面传入的参数
ParamTmkAppAndBus paramTmkAppAndBus = new ParamTmkAppAndBus();
paramTmkAppAndBus.setLoginid(loginid);
paramTmkAppAndBus.setCrjmc(nc);
paramTmkAppAndBus.setApptenant(apptenant);
paramTmkAppAndBus.setBustenant(bustenant);
// 该处可调用service相应方法进行数据保存到数据库中
resultJson = tmglService.importExcelInsertTmk(paramTmkExcelList, paramTmkAppAndBus);
return resultJson;
}4.Excel导入工具类
public class ImportExcelUtil {
private final static String excel2003L = ".xls"; //2003- 版本的excel
private final static String excel2007U = ".xlsx"; //2007+ 版本的excel
@SuppressWarnings("unused")
public List<Object> getObjectByExcel(InputStream in, String fileName, Class<?> clazz) throws Exception {
List<Object> list = null;
//创建Excel工作薄
HSSFWorkbook hwb = null;
XSSFWorkbook xwb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
hwb = new HSSFWorkbook(in); //2003-
if (hwb == null) {
throw new Exception("创建Excel工作薄为空！");
}
list = read2003Excel(hwb, clazz);
} else if (excel2007U.equals(fileType)) {
xwb = new XSSFWorkbook(in); //2007+
if (xwb == null) {
throw new Exception("创建Excel工作薄为空！");
}
list = read2007Excel(xwb, clazz);
} else {
throw new Exception("解析的文件格式有误！");
}
/*for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
int sheetcnt = sheet.getFirstRowNum();
int sheet2 = sheet.getLastRowNum();
for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
//遍历所有的列
int rowscnt = row.getFirstCellNum();
int rows2 = row.getLastCellNum();
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellValue(cell));
}
list.add(li);
}
}*/
return list;
}
public List<Object> read2003Excel(HSSFWorkbook wb, Class<?> clazz) throws NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException {
//List<List<Object>> list = new LinkedList<List<Object>>();
StringBuilder sb = new StringBuilder();
// === 提取导入数据模板中的列头信息，即第三列的数据
HSSFSheet sheet = wb.getSheetAt(0);
// 读取第三行header部
HSSFRow headerCellRow = sheet.getRow(0);
Integer cellHeaderNum = Integer.valueOf(headerCellRow.getLastCellNum());
HSSFCell dataCell = null;
HSSFRow dataRow = null;
List<Object> rowList = new ArrayList<Object>();
Map<String, String> columnMap = new HashMap<String, String>();
dataRow = sheet.getRow(0);
for (int m = 0; m < cellHeaderNum; m++) {
dataRow.getCell(m).setCellType(Cell.CELL_TYPE_STRING);
String columnNameE = String.valueOf(dataRow.getCell(m).getRichStringCellValue().toString()).trim();
// === 循环遍历字节码注解 获取属性名称
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
String fieldName = field.getName();
if (excelColumn.columnName().trim().equals(columnNameE)) {
columnMap.put(columnNameE, fieldName);
}
}
}
}
// === 循环遍历数据
Integer rowNum = sheet.getLastRowNum();
for (int i = 1; i <= rowNum; i++) {
sb.delete(0, sb.length());
sb.append(String.valueOf(i));
dataRow = sheet.getRow(i);
if (dataRow != null) {
Object obj = clazz.newInstance();
for (int j = 0; j < cellHeaderNum; j++) {
dataCell = dataRow.getCell(j);
// =================================== 读取Excel文件中的数据
// 文本，数值或日期类型的条件判断 开始 =============================
if (dataCell != null) {
Object value = "";
switch (dataCell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(dataCell)) {
// === 如果是date类型则 ，获取该cell的date值
// value =
HSSFDateUtil.getJavaDate(dataCell.getNumericCellValue()).toString();
Date date = dataCell.getDateCellValue();
// SimpleDateFormat sdf = new
// SimpleDateFormat("yyyy-MM-dd") ;
// value = sdf.format(date) ;
value = date;
} else { // === 纯数字
dataCell.setCellType(Cell.CELL_TYPE_STRING);
value = String.valueOf(dataCell.getRichStringCellValue().toString());
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = dataCell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_FORMULA:
// === 读公式计算值
value = String.valueOf(dataCell.getNumericCellValue());
// === 如果获取的数据值为非法值,则转换为获取字符串
if (value.equals("NaN")) {
value = dataCell.getRichStringCellValue().toString();
}
// cell.getCellFormula() ;//读公式
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = dataCell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
default:
value = dataCell.getRichStringCellValue().toString();
break;
}
sb.append(value);
// === 每一行数据的列头是否匹配，决定如何反射设置属性的值
String columnNameE = String.valueOf(sheet.getRow(0).getCell(j).getRichStringCellValue().toString()).trim();
String fieldName = columnMap.get(columnNameE);
if (StringUtils.isNotBlank(fieldName)) {
Field f = obj.getClass().getDeclaredField(fieldName);
f.setAccessible(true);
f.set(obj, value);
}
}
// =================================== 读取Excel文件中的数据
// 文本，数值或日期类型的条件判断 结束 =============================
}
if (sb.toString().equals(String.valueOf(i))) {
Collections.emptyList();
} else {
rowList.add(obj);
}
}
}
return rowList;
}
public List<Object> read2007Excel(XSSFWorkbook xwb, Class<?> clazz) throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
StringBuilder sb = new StringBuilder();
// === 提取导入数据模板中的列头信息，即第三列的数据
XSSFSheet sheet = xwb.getSheetAt(0);
// 读取第三行header部
XSSFRow headerCellRow = sheet.getRow(0);
Integer cellHeaderNum = Integer.valueOf(headerCellRow.getLastCellNum());
XSSFCell dataCell = null;
XSSFRow dataRow = null;
List<Object> rowList = new ArrayList<Object>();
Map<String, String> columnMap = new HashMap<String, String>();
dataRow = sheet.getRow(0);
for (int m = 0; m < cellHeaderNum; m++) {
dataRow.getCell(m).setCellType(Cell.CELL_TYPE_STRING);
String columnNameE = String.valueOf(dataRow.getCell(m).getRichStringCellValue().toString()).trim();
// === 循环遍历字节码注解 获取属性名称
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
String fieldName = field.getName();
if (excelColumn.columnName().trim().equals(columnNameE)) {
columnMap.put(columnNameE, fieldName);
}
}
}
}
// === 循环遍历数据
Integer rowNum = sheet.getLastRowNum();
for (int i = 1; i <= rowNum; i++) {
sb.delete(0, sb.length());
sb.append(String.valueOf(i));
dataRow = sheet.getRow(i);
if (dataRow != null) {
Object obj = clazz.newInstance();
for (int j = 0; j < cellHeaderNum; j++) {
dataCell = dataRow.getCell(j);
// =================================== 读取Excel文件中的数据
// 文本，数值或日期类型的条件判断 开始 =============================
if (dataCell != null) {
Object value = "";
switch (dataCell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(dataCell)) {
// === 如果是date类型则 ，获取该cell的date值
// value =
HSSFDateUtil.getJavaDate(dataCell.getNumericCellValue()).toString();
Date date = dataCell.getDateCellValue();
// SimpleDateFormat sdf = new
// SimpleDateFormat("yyyy-MM-dd") ;
// value = sdf.format(date) ;
value = date;
} else { // === 纯数字
dataCell.setCellType(Cell.CELL_TYPE_STRING);
value = String.valueOf(dataCell.getRichStringCellValue().toString());
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = dataCell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_FORMULA:
// === 读公式计算值
value = String.valueOf(dataCell.getNumericCellValue());
// === 如果获取的数据值为非法值,则转换为获取字符串
if (value.equals("NaN")) {
value = dataCell.getRichStringCellValue().toString();
}
// cell.getCellFormula() ;//读公式
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = dataCell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
default:
value = dataCell.getRichStringCellValue().toString();
break;
}
sb.append(value);
// === 每一行数据的列头是否匹配，决定如何反射设置属性的值
String columnNameE = String.valueOf(sheet.getRow(0).getCell(j).getRichStringCellValue().toString()).trim();
String fieldName = columnMap.get(columnNameE);
if (StringUtils.isNotBlank(fieldName)) {
Field f = obj.getClass().getDeclaredField(fieldName);
f.setAccessible(true);
f.set(obj, value);
}
}
// =================================== 读取Excel文件中的数据
// 文本，数值或日期类型的条件判断 结束 =============================
}
if (sb.toString().equals(String.valueOf(i))) {
Collections.emptyList();
} else {
rowList.add(obj);
}
}
}
return rowList;
}
}
以上是经过测试的代码！希望可以帮助到大家，谢谢！
7061
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
