Excel导入保存附件和解析数据
一，前端上传附件的组件
1、先给一个下载模板的按钮
// 下载Excel模板
downLoadExcel: function () {
window.open(GLOBAL_CONFIG.webSiteRoot + "/main/common/files/xxx.xls");
},
2、Element上传控件
<el-upload ref="upload" :action="importFileUrl" :before-upload="beforeUpload" :multiple="false" :on-success="uploadSuccess" :on-error="uploadFail" :show-file-list="false" style="display: inline-block;"> <el-button type="primary" icon="el-icon-plus" size="medium">导入Excel</el-button> </el-upload>
3、js中上传有关
var vue = new Vue({ el: '#app', data: { // 导入的后台接口地址 importFileUrl : '/excellImport/importFile', methods：{ // 上传前对文件的类型和大小判断 beforeUpload : function (file) { var self = this; const extension = file.name.split('.')[1] === 'xls' const extension2 = file.name.split('.')[1] === 'xlsx' const isLt2M = file.size / 1024 / 1024 < 50 if (!extension && !extension2 ) { alert('上传文件只能是 xls、xlsx 格式!') } if (!isLt2M) { alert('上传文件大小不能超过 50MB!') } console.log(file); return extension || extension2 && isLt2M }, // 文件上传成功 uploadSuccess: function (response, file, flieList) { this.initTable(); console.log(response); alert(response.msg); }, // 文件上传失败 uploadFail: function (err, file, fileList) { alert('上传失败，请重试！'); }, }
其中，data 中的 importFileUrl : '/excellImport/importFile', 就是后台的接口地址；
二、java代码
@SuppressWarnings({"unchecked", "finally" })
@ResponseBody
@RequestMapping(value="/importFile",method= RequestMethod.POST)
@ApiOperation("导入excel文件操作接口")
public JsonResult<XfImportexcelModel> importFile(HttpServletRequest request, HttpServletResponse response,
@RequestParam("file") MultipartFile[] excelFile, XfUser user) throws AccessExpiredException, DaoAccessException, Exception {
System.out.println("==========================================-进入导入excel文件操作接口==========================");
JsonResult<XfImportexcelModel> jsonResult = JsonResult.getDefaultResult();
try {
response.setCharacterEncoding("utf-8");
// 手动调用PrintWriter向客户端输入返回值，若本方法是有返回值的，则不需要
// PrintWriter out = response.getWriter();
//文件保存本地目录路径
String savePath = request.getSession().getServletContext().getRealPath(PATH_LINE) + "main"+PATH_LINE+"xxx"+PATH_LINE+"xxx"+PATH_LINE+"xxx"+PATH_LINE+"upload";
//文件保存目录URL
String saveUrl = request.getContextPath() + PATH_LINE +"upload"+PATH_LINE+"file"+PATH_LINE;
if(!ServletFileUpload.isMultipartContent(request)){
// out.print(getError("请选择文件。"));
// out.close();
jsonResult.setMsg("请选择文件。");
return jsonResult;
}
//检查目录
File uploadDir = new File(savePath);
if(!uploadDir.isDirectory()){
uploadDir.mkdirs(); //目录不存在就创建目录
}
//检查目录写权限
if(!uploadDir.canWrite()){
// out.print(getError("上传目录没有写权限。"));
// out.close();
jsonResult.setMsg("上传目录没有写权限。");
return jsonResult;
}
String dirName = request.getParameter("dir");
if (dirName == null) {
dirName = "file";
}
//定义允许上传的文件扩展名
Map<String, String> extMap = new HashMap<String, String>();
extMap.put("image", "gif,jpg,jpeg,png,bmp");
extMap.put("flash", "swf,flv");
extMap.put("media", "swf,flv,mp3,wav,wma,wmv,mid,avi,mpg,asf,rm,rmvb");
extMap.put("file", "doc,docx,xls,xlsx,ppt,htm,html,xml,txt,zip,rar,gz,bz2");
if(!extMap.containsKey(dirName)){
// out.print(getError("目录名不正确。"));
// out.close();
jsonResult.setMsg("目录名不正确。");
return jsonResult;
}
//创建文件夹
savePath += PATH_LINE+dirName + PATH_LINE;
saveUrl += PATH_LINE;
File saveDirFile = new File(savePath);
if (!saveDirFile.exists()) {
saveDirFile.mkdirs(); //保存到项目工程文件夹指定目录中
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String ymd = sdf.format(new Date());
savePath += ymd + PATH_LINE;
saveUrl += ymd + PATH_LINE;
File dirFile = new File(savePath); //存到本地之后在获取解析
if (!dirFile.exists()) {
dirFile.mkdirs();
}
//最大文件大小
long maxSize = 10000000;
// 保存文件
for(MultipartFile iFile : excelFile){
String fileName = iFile.getOriginalFilename();
//检查文件大小
if(iFile.getSize() > maxSize){
// out.print(getError("上传文件大小超过限制。"));
// out.close();
jsonResult.setMsg("上传文件大小超过限制。");
return jsonResult;
}
//检查扩展名
String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
if(!Arrays.<String>asList(extMap.get(dirName).split(",")).contains(fileExt)){
//return getError("上传文件扩展名是不允许的扩展名。
只允许" + extMap.get(dirName) + "格式。");
// out.print(getError("上传文件扩展名是不允许的扩展名。
只允许" + extMap.get(dirName) + "格式。"));
// out.close();
jsonResult.setMsg("上传文件扩展名是不允许的扩展名。
只允许" + extMap.get(dirName) + "格式。");
return jsonResult;
}
// 解析Excel数据存入数据库
JsonResult<Object> jsonResultHandle = handleExcelData(iFile, user); //调用另外一个方法解析excel中的数据交互项目生产库
// 解析或者入库有问题则反馈到前端
if(!jsonResultHandle.getSuccess()){
// 返回导入信息到前端页面
jsonResult.setSuccess(false);
jsonResult.setMsg(jsonResultHandle.getMsg());
return jsonResult;
}
// 返回导入信息到前端页面
jsonResult.setMsg(jsonResultHandle.getMsg());
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
String newFileName = df.format(new Date()) + "_" + iFile.getName() + "." + fileExt;
try{
File uploadedFile = new File(savePath, newFileName);
// 写入文件
FileUtils.copyInputStreamToFile(iFile.getInputStream(), uploadedFile);
}catch(Exception e){
// out.print(getError("上传文件失败。"));
// out.close();
jsonResult.setMsg("上传文件失败。");
return jsonResult;
}
JSONObject obj = new JSONObject();
obj.put("error", 0);
obj.put("url", saveUrl + newFileName);
// out.print(obj.toJSONString());
// out.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("", e);
System.out.println(e);
} finally {
System.out.println("===========================================结束导入excel文件操作接口==");
return jsonResult;
}
}
/**
* 解析Excel数据存入数据库
* @param file
* @param user
* @return
*/
@SuppressWarnings("unchecked")
public JsonResult<Object> handleExcelData(MultipartFile file, XfUser user) throws Exception{
System.out.println("==================================================开始解析Excel数据存入数据库==");
// 返回导入的结果信息
String resultString = "";
JsonResult<Object> jsonResult = JsonResult.getDefaultResult();
if (file != null) {
try {
List<ImportexcelParam> ImportexcelParamList = null;
// 获取解析的excel数据
JsonResult<List<List<String>>> JsonResultList = ExcelUtil.parseExcel(file.getInputStream(),
file.getOriginalFilename());
// 解析有问题反馈到调用地方
if(!JsonResultList.getSuccess()){
jsonResult.setSuccess(false);
jsonResult.setMsg(JsonResultList.getMsg());
return jsonResult;
}
// excel数据解析成功，进行获取和入库处理
List<List<List<String>>> resultList = JsonResultList.getData();
// 目前只检查第一个sheet
if (resultList != null && !resultList.isEmpty()) {
for (List<List<String>> sheetList : resultList) {
ImportexcelParamList = new ArrayList<>();
if (sheetList != null && !sheetList.isEmpty()) {
if (sheetList.size() > 200) {
jsonResult.setSuccess(false);
jsonResult.setMsg("单次上传文件记录条数不能超过200条！");
return jsonResult;
}
if(sheetList.get(0).size() != 20){
jsonResult.setSuccess(false);
jsonResult.setMsg("请使用正确的导入模板！");
return jsonResult;
}
// 导入开始时间，毫秒
long startTime = System.currentTimeMillis();
// 记录导入成功的数据条数
int successImportNum = 0;
// 重复的编号记录
String repeatInfo="";
for (List<String> rowList : sheetList){
if (!rowList.isEmpty()) {
// 投诉人和投诉内容筛选有效行
if(StringUtils.isNullOrEmpty(rowList.get(13))||StringUtils.isNullOrEmpty(rowList.get(14))){
continue;
}
// 导入数据举报编号去重
XfImportexcel xfImportexcel = iXfImportexcelService.getXfImportexcelByBussinesNo(rowList.get(2));
if (xfImportexcel != null) {
repeatInfo += "【";
repeatInfo += rowList.get(2);
repeatInfo += "】";
continue;
}
ImportexcelParam ImportexcelParam = new ImportexcelParam();
ImportexcelParam.setStatus(rowList.get(0));//状态
ImportexcelParam.setEmergencyLevel(rowList.get(1));//紧急程度
ImportexcelParam.setBussinesNo(rowList.get(2));//业务编号
ImportexcelParam.setCallSysNo(rowList.get(3));//来电编号
ImportexcelParam.setRepeatFlag("初件".equals(rowList.get(4))?false:true);//重复标志
ImportexcelParam.setReplyFlag("未答复".equals(rowList.get(5))?false:true);//答复标志
ImportexcelParam.setProcessUnitName(rowList.get(6));//处理单位
ImportexcelParam.setOperator(rowList.get(7));//经办人
ImportexcelParam.setProcessamento(rowList.get(8));//处理方式
ImportexcelParam.setProcessStatus(rowList.get(9));//处理状态
ImportexcelParam.setCallPhoneNum(rowList.get(10));//来电号码
ImportexcelParam.setLinkTelphone(rowList.get(11));//联系电话
ImportexcelParam.setCreateBy(rowList.get(12));//创建人
ImportexcelParam.setCallPerson(rowList.get(13));//来电人
ImportexcelParam.setComplainContent(rowList.get(14));//投诉内容
ImportexcelParam.setCallDate(StringUtils.isNullOrEmpty(rowList.get(15))?null:TimeUtils.string2Timestamp(DateUtil.YYYY_MM_DD_HH_MM_SS, rowList.get(15)));//来电日期
ImportexcelParam.setCallPhoneNum(rowList.get(16));//来电人数
ImportexcelParam.setItemType(rowList.get(17));//事项分类
ImportexcelParam.setCallPurpose(rowList.get(18));//来电目的
ImportexcelParam.setProcessTimeLimit(StringUtils.isNullOrEmpty(rowList.get(19))?null:TimeUtils.string2Timestamp(DateUtil.YYYY_MM_DD_HH_MM_SS, rowList.get(19)));//处理时限
ImportexcelParamList.add(ImportexcelParam);
}
}
// 调用excel数据导入保存业务实现方法
successImportNum = iXfImportexcelService.save(ImportexcelParamList, user);
// 导入失败条数
int failImportNum = ImportexcelParamList.size()-successImportNum;
// 导入结束时间，毫秒
long endTime = System.currentTimeMillis();
if(repeatInfo!=""){
resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒;"+repeatInfo+"编号数据已存在";
}else{
resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒。";
}
// 解析和入库都无问题，设置sucess和信息反馈
jsonResult.setSuccess(true, resultString);
System.out.println("================================================="+resultString+"=========");
}else{
jsonResult.setSuccess(false, "文件没有数据记录");
}
}
// List<List<String>> sheetList = resultList.get(0);
}else{
jsonResult.setSuccess(false, "文件没有数据记录");
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// String path = "E:/springUpload" + file.getOriginalFilename();
// 上传
// file.transferTo(new File(path));
}else{
jsonResult.setSuccess(false, "无法获取文件");
}
System.out.println("==================================================结束解析Excel数据存入数据库======");
return jsonResult;
}
2、解析Excel封装工具类
package com.xxx.xx.utils; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.mlsc.fw.common.exception.ParamDataException; import com.mlsc.fw.common.utils.DateUtil; import com.xxx.fw.web.JsonResult; public class ExcelUtil { private static Logger logger = Logger.getLogger(ExcelUtil.class); public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"; public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; public static final String CELL_TYPE_STRING="String"; /** * 适用于第一行是标题行的excel， * * resultList --> sheetList --> rowList * * @throws Exception */ @SuppressWarnings("unchecked") public static JsonResult<List<List<String>>> parseExcel(InputStream in, String suffixName) throws ParamDataException { JsonResult<List<List<String>>> jsonResult = JsonResult.getDefaultResult(); List<List<List<String>>> resultList = new ArrayList<>(); List<List<String>> sheetList = null; List<String> rowList = null; String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length()); Workbook wb = null; try { if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2003_POSTFIX)) { wb = new HSSFWorkbook(in); } else if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2010_POSTFIX)) { wb = new XSSFWorkbook(in); } else { jsonResult.setSuccess(false); jsonResult.setMsg("请使用正确的导入模板"); return jsonResult; } int sheetSize = wb.getNumberOfSheets(); // 文件中不止一张工作表 if(sheetSize>1){ jsonResult.setSuccess(false); jsonResult.setMsg("请使用正确的导入模板"); return jsonResult; } for (int i = 0; i < sheetSize; i++) {// 遍历sheet页 Sheet sheet = wb.getSheetAt(i); int rowSize = sheet.getLastRowNum() + 1; if(rowSize<=1){ jsonResult.setSuccess(false); jsonResult.setMsg("导入模板没有数据记录"); return jsonResult; } sheetList = new ArrayList<>(); int columnSize = 0; // 从第一行标题行开始向下遍历行，包括第一行 for (int j = 0; j < rowSize; j++) { Row row = sheet.getRow(j); // 遍历第一行作用是为了获取每一行的列数 if (j == 0) { // bypass the 1st row columnSize = row.getLastCellNum(); continue; } // 略过空行 if (row == null) { continue; } rowList = new ArrayList<>(); for (int m = 0; m < columnSize; m++) { // if (m == 0 && (row.getCell(3)==null||row.getCell(3).equals("")||row.getCell(3).getCellType() ==HSSFCell.CELL_TYPE_BLANK) ) { // break; // } if (row.getCell(m) != null){ rowList.add(getValue(row.getCell(m))); } else { rowList.add(""); } } if (rowList.size() == columnSize) sheetList.add(rowList); } resultList.add(sheetList); } } catch (Exception e) { logger.error("", e); throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！"); } finally { if (wb != null) { try { wb.close(); } catch (Exception e) { logger.error("关闭Workbook出现异常！", e); } } if (in != null) { try { in.close(); } catch (Exception e) { logger.error("关闭输入流出现异常！", e); } } } jsonResult.setSuccess(true); jsonResult.setData(resultList); return jsonResult; } public static void exportByTemplate(FileInfo fileInfo, List<DataInfo> dataInfoList, HttpServletResponse response) { try { setResponseHeader(fileInfo.getGenerateFileName(), response); XSSFWorkbook workbook = createWorkBook(fileInfo, dataInfoList, response); outputFile(fileInfo, workbook, response); } catch (UnsupportedEncodingException e) { logger.error("", e); } } private static void outputFile(FileInfo fileInfo, XSSFWorkbook workbook, HttpServletResponse response) { BufferedOutputStream bufferedOutPut = null; try { OutputStream output = response.getOutputStream(); bufferedOutPut = new BufferedOutputStream(output); bufferedOutPut.flush(); workbook.write(bufferedOutPut); } catch (IOException e) { logger.error("", e); } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { } } if (bufferedOutPut != null) { try { bufferedOutPut.close(); } catch (IOException e) { } } } } @SuppressWarnings("deprecation") private static XSSFWorkbook createWorkBook(FileInfo fileInfo, List<DataInfo> dataInfoList, HttpServletResponse response) { XSSFWorkbook workbook = null; try { File fi = new File(fileInfo.getTemplatePath() + fileInfo.getTemplateName()); FileInputStream fs = new FileInputStream(fi); // 读取excel模板 workbook = new XSSFWorkbook(fs); if (dataInfoList == null || dataInfoList.size() == 0) { return workbook; } int rowIndex = 0; int columnIndex = 0; // sheet for (DataInfo dataInfo : dataInfoList) { if(dataInfo.getSheetIndex()==null){ continue; } XSSFSheet sheet = workbook.getSheetAt(dataInfo.getSheetIndex()); rowIndex = dataInfo.getRowStart(); if(StringUtils.isNotEmpty(dataInfo.getModifiedTitle())){ CellStyle cs=sheet.getRow(0).getCell(0).getCellStyle(); sheet.getRow(0).getCell(0).setCellValue(dataInfo.getModifiedTitle()); sheet.getRow(0).getCell(0).setCellStyle(cs); } if (dataInfo.getData() == null || dataInfo.getData().isEmpty()) { continue; } XSSFRow styleRow = null; CellStyle style = null; CellStyle style0 = null; // row for (int rIndex = 0; rIndex < dataInfo.getData().size(); rIndex++) { if (dataInfo.getData().get(rIndex) == null || dataInfo.getData().get(rIndex).length == 0) { continue; } columnIndex = dataInfo.getColumnStart(); XSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); } if(rIndex==0){ styleRow = sheet.getRow(rowIndex); } // cell for (int cIndex = 0; cIndex < dataInfo.getColumnLength(); cIndex++) { if(styleRow.getCell(columnIndex)==null){ System.out.println(222); } style = styleRow.getCell(columnIndex).getCellStyle(); if(dataInfo.isGenIndex()){ XSSFCell cell0 = row.getCell(0); if(cell0==null){ cell0=row.createCell(0); } cell0.setCellValue(rIndex+1); style0 = styleRow.getCell(0).getCellStyle(); cell0.setCellStyle(style0); } XSSFCell cell = row.getCell(columnIndex); if (cell == null) { cell = row.createCell(columnIndex); } // if(cIndex==17){ // System.out.println(333); // } // System.out.println("sheet:"+dataInfo.getSheetIndex()+"/rIndex:"+rIndex+"/cIndex:"+cIndex); // if(null == dataInfo.getData().get(rIndex)[cIndex]){ // System.out.println(111); // } if(dataInfo.getTypeMap()!=null && dataInfo.getTypeMap().containsKey(cIndex)){ if(CELL_TYPE_STRING.equals(dataInfo.getTypeMap().get(cIndex))){ cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]); } }else if(null != dataInfo.getData().get(rIndex)[cIndex] && dataInfo.getData().get(rIndex)[cIndex].matches("^(-?\d+)(\.\d+)?$")){ cell.setCellValue(Double.parseDouble(dataInfo.getData().get(rIndex)[cIndex])); }else{ cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]); } cell.setCellStyle(style); columnIndex++; } rowIndex++; } } List<Integer> hideSheetList=new ArrayList<>(); for(DataInfo dataInfo : dataInfoList){ hideSheetList.add(dataInfo.getSheetIndex()); } for(int i=0;i<workbook.getNumberOfSheets();i++){ if(!hideSheetList.contains(i)){ workbook.setSheetHidden(i, 2); } } } catch (IOException e) { logger.error("", e); } return workbook; } @SuppressWarnings("deprecation") private static String getValue(Cell cell) { DecimalFormat df = new DecimalFormat("#.###"); if (cell == null) { return ""; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat(DateUtil.YYYY_MM_DD); return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } return df.format(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); case HSSFCell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case HSSFCell.CELL_TYPE_BLANK: return ""; } return ""; } @SuppressWarnings("unused") public static String getExcelAcceptDepartment(InputStream in, String suffixName) throws ParamDataException { String header = ""; String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length()); Workbook wb = null; try { if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2003_POSTFIX)) { wb = new HSSFWorkbook(in); } else if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2010_POSTFIX)) { wb = new XSSFWorkbook(in); } else { throw new ParamDataException("读取的不是excel文件"); } int sheetSize = wb.getNumberOfSheets(); for (int i = 0; i < sheetSize; i++) {// 遍历sheet页 Sheet sheet = wb.getSheetAt(i); header = getValue(sheet.getRow(2).getCell(1)).trim(); break; } } catch (Exception e) { logger.error("", e); throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！"); } finally { if (wb != null) { try { wb.close(); } catch (Exception e) { logger.error("关闭Workbook出现异常！", e); } } if (in != null) { try { in.close(); } catch (Exception e) { logger.error("关闭输入流出现异常！", e); } } } return header; } private static void setResponseHeader(String fileName, HttpServletResponse response) throws UnsupportedEncodingException { response.reset(); // SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); // String date = sdf.format(new Date()); // String newFileName=fileName+date; // 指定下载的文件名 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Pragma", "no-cache"); response.setHeader("Cache-Control", "no-cache"); response.setDateHeader("Expires", 0); } }