util包
public class ExcelHelp {
public static ArrayList<ArrayList<String>> readExcel(InputStream in) {
ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(in);
} catch (Exception ex) {
workBook = new HSSFWorkbook(in);
}
for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 循环列Cell
ArrayList<String> arrCell = new ArrayList<String>();
for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
arrCell.add(getValue(cell));
}
Row.add(arrCell);
}
}
} catch (IOException e) {
return null;
}
return Row;
}
/**
* 读取excel文件
*
* @param path 文件路径
* @return
*/
public static ArrayList<ArrayList<String>> readExcel(String path) {
ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(path);
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(path));
}
for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 循环列Cell
ArrayList<String> arrCell = new ArrayList<String>();
for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
arrCell.add(getValue(cell));
}
Row.add(arrCell);
}
}
} catch (IOException e) {
return null;
}
return Row;
}
// 读取excel文件辅助
private static String getValue(Cell cell) {
String value = null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "true" : "false");
break;
default:
value = "";
}
return value;
}
}
jsp页面
function uploadExecl(obj) {
//var id=$(obj).attr("id");
$.ajaxFileUpload({
url : domain+'driving/test/importExcel', //用于文件上传的服务器端请求地址
secureuri : false, //是否需要安全协议,一般设置为false
fileElementId : obj, //文件上传域的ID
dataType : 'json', //返回值类型 一般设置为json
data:{"teacherId":"${teacherId}"},
success : function(data,status) //服务器成功响应处理函数
{
data = $.parseJSON(data);
if(data.status == "0"){
swal({"title":"","text":"导入成功"});
searchCurrentPage();
}else{
swal({"title":"","text":data.msg,"type":"error"});
}
},
})
}
controller层
@RequestMapping("/importExcel")
@ResponseBody
public MapDto importExcel(@RequestParam("importExcel") MultipartFile importExcel,
@RequestParam("teacherId") Long teacherId) throws IllegalStateException, IOException, ParseException {
MapDto dto = new MapDto();
// SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
if (importExcel != null && !importExcel.isEmpty()) {
String fileName = importExcel.getOriginalFilename().toLowerCase();
String type = fileName.substring(fileName.lastIndexOf("."));
if (!excel.contains(type)) {
dto.setStatus(1);
dto.errorMsg("请上传以.xlsx或.xls格式的excel文档!");
return dto;
}
ArrayList<ArrayList<String>> arrayList = ExcelHelp.readExcel(importExcel.getInputStream());
Map<String, Object> map = new HashMap<String, Object>();
map.put("testId", testId);
for (ArrayList<String> item : arrayList) {
if (item.get(0).equals("测试二")) {
map.put("appointSubject", 0);
} else {
map.put("appointSubject", 1);
}
map.put("schoolGradeId", teacherScheduleService.selectSchoolGradeIdByName(item.get(1)));
String scheduleDate = teacherScheduleService.selectIdByDate(item.get(2));
if (scheduleDate != null && scheduleDate.length() > 0) {
dto.setStatus(1);
dto.setMsg("日期已存在");
return dto;
}
String scheduleTime = teacherScheduleService.selectIdByTime(item.get(3));
if (scheduleTime != null && scheduleTime.length() > 0) {
dto.setStatus(1);
dto.setMsg("时间区间已存在");
return dto;
}
map.put("scheduleDate", item.get(2));
map.put("scheduleTime", item.get(3));
map.put("appointQuota", item.get(4));
teacherScheduleService.addExcel(map);
}
dto.setStatus(0);
} else {
dto.setStatus(1);
dto.errorMsg("请上传以.xlsx或.xls格式的excel文档!");
}
return dto;
}
private static Set<String> excel = new HashSet<String>();
static {
excel.addAll(Arrays.asList(PlatformConstants.EXCEL_FORMAT_ARRAY));
}