背景：最近需要做一个excel模板导入的功能，以便用户可以自己增删改查数据，当然，只有特别的用户才能有此权限，捋了捋思路，还是从前端写起
实现：
页面最后的效果如下，可以自己修改，删除，导入导出数据，为了统一规范，防止数据不规范解析不了，模板由我们提供下载，用户填充数据统一导入，
涉及到机密，打码请见谅。
页面主要我分为三大块功能，
一是分权分域，只有特定的用户才能导入修改，删除，添加数据，这个看你怎么保存当前用户，验证下用户权限等，这里不做过多阐述：
二是数据的查询，导出，因为之前做的几乎都有这两个功能，所以集成过来，需要看导出代码的可以看我之前的博客参考；
三是数据导入，修改，删除，添加和模板的下载，这里主要说模板下载和导入数据的解析
模板下载这块，前端用的按钮点击事件，window.location.href指定到后台路径，后台模板下载方法代码如下：
public static void getTemplate(HttpServletRequest request, HttpServletResponse response,String filename) throws IOException {
String path = TEMPLATE_PATH + filename;
File file = new File(path);
if(!file.exists()){
return;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename="+ new String(filename.getBytes("utf-8"), "ISO-8859-1"));
BufferedInputStream bi = new BufferedInputStream(new FileInputStream(file));
OutputStream os = response.getOutputStream();
byte[] bytes = new byte[1024];
int i = bi.read(bytes);
while (i != -1){
os.write(bytes, 0, i);
i = bi.read(bytes);
}
if (bi != null) {
try {
bi.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
这里path我指定了一个位置来放模板下载的文件，因为模块较多，所以按照名称下载，
导入模板，前端代码如下：
<li class='item'>
<input type="button" class="button" id="uploading" value="导入">
</li>
<li class='item'>
<input type="file" id="file1">
</li>
两个按钮，导入的按钮绑定click事件，里面只验证了文档的格式是不是excel，使用FormData对象作为data上传，FormData对象用以将数据编译成键值对，以便用XMLHttpRequest来发送数据，后台使用MultipartFile对象接收：
click事件如下：
ExportIn: function () {
if($("#file1").val().length <=0){
alert("请选择文件");
return false;
}else{
var filepath = $("#file1").val();
var extStart = filepath.lastIndexOf(".");
var ext = filepath.substring(extStart,filepath.length).toUpperCase();
if (ext != ".XLSX" && ext != ".XLS" && ext != ".XLSM") {
alert("请上传excel格式文档");
return false;
}
}
//获取到上传的文件信息
var data =document.getElementById("file1").files[0];
var fromData = new FormData();
if(data != null){
fromData.append("file",data);
$.ajax({
type: "post",
url: dss.rootPath + "plugin/labourCompetition/NpsCompetitionScore_mobile_uploading",
data: fromData,
dataType: "json",
contentType: false,
processData: false,
beforeSend: function () {
dss.load(true);
},
complete: function () {
dss.load(false);
},
success: function (data) {
if (data.status != "OK") {
alert(data.status);
}
else if (data.status == "OK") {
alert("导入成功！");
}
window.location.reload();
}
});
}
},
后台使用MultipartFile 接收，绑定了前端的file参数，验证文件是否为空，然后判断文件格式，是xls还是xlsx格式，创建对应的对象解析，然后把数据存到list<String[]>中，
一行为一个string数组，因为解析模板要复用，还有验证不为空，日期格式等所以有些地方写的很麻烦，可以用简单的办法解决，但是又不能影响复用的模块，所以我的处理可
能麻烦了点，各位可以按照自己的需求来。
后台接收的方法如下：
@RequestMapping("plugin/labourCompetition/NpsCompetitionScore_mobile_uploading")
@ResponseBody
public JsonData importTemplate(@RequestParam(value = "file")MultipartFile file) throws Exception {
JsonData jsonData = new JsonData();
Boolean bool = ImportExcel.checkFile(file);
if(!bool){
jsonData.setStatus("文件类型不正确或为空");
return jsonData;
}
//工具类在下面
HashMap<String, ArrayList<String[]>> hashMap = ImportExcel.analysisFile(file);
ArrayList<String[]> arrayList = hashMap.get("OK");
if(arrayList == null){
Set<String> strings = hashMap.keySet();
String next = strings.iterator().next();
jsonData.setStatus(next);
return jsonData;
}
//数据都在arrayList中，循环入库，记得最后一个字段加上loadTime
ConnectSettings dwConnect = commUtil.getDwConnect();
String sql = " INSERT INTO 表名 values ";
String nowDate = ImportExcel.getNowDate();
for(int n = 0;n < arrayList.size();n++){
String[] str = arrayList.get(n);
sql += " ( ";
for(int num =0;num<str.length;num++){
sql += "'" +str[num]+"',";
if(num == str.length-1){
sql += "to_date('"+nowDate+"','yyyy-mm-dd hh24:mi:ss')";
}
}
if( n != arrayList.size()-1 ){
sql += " ),";
}else{
sql += " )";
}
}
System.err.println(sql);
int i = DbHelper.executeNonQuery(sql, dwConnect);
System.err.println("成功执行行数"+i);
if(i < 1){
jsonData.setStatus("导入失败");
return jsonData;
}
jsonData.setStatus("导入成功");
return jsonData;
}
@Component
public class ImportExcel { private static Calendar calendar = Calendar.getInstance(); private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月");
private static SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
private static SimpleDateFormat simpleDateFormat3 = new SimpleDateFormat("yyyy/MM/dd"); //解析excel文件
public static HashMap<String, ArrayList<String[]>> analysisFile(MultipartFile file) throws IOException {
HashMap<String, ArrayList<String[]>> hashMap = new HashMap<>();
//获取workbook对象
Workbook workbook = null;
String filename = file.getOriginalFilename();
InputStream inputStream = file.getInputStream();
//根据后缀名是否excel文件
if(filename.endsWith("xls")){
//
workbook = new HSSFWorkbook(inputStream);
}else if(filename.endsWith("xlsx")){
//
workbook = new XSSFWorkbook(inputStream);
} //创建对象，把每一行作为一个String数组，所以数组存到集合中
ArrayList<String[]> arrayList = new ArrayList<>();
if(workbook != null){
//循环sheet,现在是单sheet
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获取第一个sheet
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
hashMap.put("文件sheet为空!",arrayList);
return hashMap;
}
//获取当前sheet开始行和结束行
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
//循环开始，除了前两行
for(int rowNum = firstRowNum + 2;rowNum <= lastRowNum;rowNum++){
//获取当前行
Row row = sheet.getRow(rowNum);
//获取当前行的开始列和结束列
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum(); //获取总行数
int lastCellNum2 = row.getPhysicalNumberOfCells();
String[] strings = new String[lastCellNum2];
//循环当前行
for(int cellNum = firstCellNum;cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
if( cell == null || "".equals(cell) || cell.getCellType()== Cell.CELL_TYPE_BLANK ){
hashMap.put("第"+(rowNum+1)+"行,第"+(cellNum+1)+"列为空",arrayList);
return hashMap;
}
String cellValue = "";
cellValue = getCellValue(cell);
strings[cellNum] = cellValue;
}
arrayList.add(strings); }
}
}
inputStream.close();
hashMap.put("OK",arrayList);
return hashMap;
} //把每一个cell转换为string
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字转换成string，防止12.0这种情况
if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
cell.setCellType(cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: //数字0
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串1
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
//cellValue = String.valueOf(cell.getCellFormula());
try {
cellValue = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
} //判断row是否为空
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
} //检查文件类型
public static Boolean checkFile(MultipartFile file){
//检查文件是否为空
boolean empty = file.isEmpty();
if(empty || file == null){
return false;
}
//检查文件是否是excel类型文件
String filename = file.getOriginalFilename();
if(!filename.endsWith("xls") && !filename.endsWith("xlsx")){
return false;
}
return true;
} //转换excel导入之后时间变为数字,月时间
public static String getCorrectMonth(int i){
calendar.set(1900,0,1);
calendar.add(calendar.DATE,i);
Date time = calendar.getTime();
String s = simpleDateFormat.format(time);
return s;
} //转换excel导入之后时间变为数字,年月日时间
public static String getCorrectDay(int i){
calendar.set(1900,0,-1,0,0,0);
calendar.add(calendar.DATE,i);
Date time = calendar.getTime();
String s = simpleDateFormat3.format(time);
return s;
} //获取当前时间的字符串
public static String getNowDate(){
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = simpleDateFormat.format(date);
return format;
} //文件读取到指定的位置
public String saveFile(MultipartFile file) throws IOException {
MultipartFile update = file;
//文件中参数名字
String name = update.getName();
//文件名字
String originalFilename = update.getOriginalFilename();
//是否为空
boolean empty = update.isEmpty();
//传输文件到指定路径中
String path = "F://LDJS/boco/uploading/"+originalFilename;
update.transferTo(new File(path));
//文件类型
String contentType = update.getContentType();
InputStream inputStream = update.getInputStream();
inputStream.close();
//是否存在此路径
boolean path1 = new File(path).exists();
if(path1){
return "OK";
}else{
return "导入文件失败";
} } //显示时间，把数字转换成时间类型的
public static String getExcelDate(Cell cell){
Date dateCellValue = cell.getDateCellValue();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String format = simpleDateFormat.format(dateCellValue);
return format;
} public static String getDetailDate(String date){
int dayNum = (int)Double.parseDouble(date); String s1 = "0."+ date.split("\\.")[1];
String hour = Double.parseDouble(s1)*24 +"";
int hourNum = Integer.parseInt(hour.split("\\.")[0]); String s2 = "0."+ hour.split("\\.")[1];
String minte = Double.parseDouble(s2)*60 +"";
int minteNum = Integer.parseInt(minte.split("\\.")[0]); String s3 = "0."+ minte.split("\\.")[1];
String second = Double.parseDouble(s3)*60 +"";
int secondNum = Integer.parseInt(second.split("\\.")[0]);
calendar.set(1900,0,-1,0,0,0);
calendar.add(calendar.DATE,dayNum);
calendar.add(calendar.HOUR,hourNum);
calendar.add(calendar.MINUTE,minteNum);
calendar.add(calendar.SECOND,secondNum);
Date time = calendar.getTime();
String s = simpleDateFormat2.format(time);
return s;
} //检查是否是数字
public static Boolean checkWhetherNumber(String str){
try {
BigDecimal bigDecimal = new BigDecimal(str);
}catch (Exception e){
return false;
}
return true;
} //检查是不是时间类型
public static Boolean checkWhetherDate(String str){
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
// 设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01
simpleDateFormat.setLenient(false);
simpleDateFormat.parse(str);
}catch (Exception e){
return false;
}
return true;
} //检查是不是时间类型
public static Boolean checkWhetherDate2(String str){
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
simpleDateFormat.setLenient(false);
simpleDateFormat.parse(str);
}catch (Exception e){
return false;
}
return true;
} //检查是不是月的时间类型
public static Boolean checkWhetherMonth(String str){
try {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月");
simpleDateFormat.setLenient(false);
simpleDateFormat.parse(str);
}catch (Exception e){
return false;
}
return true;
} }