导入文件这里,分为了几个部分:
第一步是读取文件,若未读取到文件,直接返回读取失败信息;
第二步是读取到文件后,进行校验,若不是excel文件,返回文件格式错误,读取失败信息;
第三步是读取sheet中信息并检验,若有错误信息则保存入errorList中,全部读取完,返回错误信息列表,若没有错误信息,才能将读取到的表保存到数据库中
其中.xsl和.xslx文件兼容问题,是用以下方法解决:
try {
wb = WorkbookFactory.create(inputStream);
} catch (EncryptedDocumentException e) {
importError.add("此文件不能解析,请传入正确格式的文件!");
} catch (InvalidFormatException e) {
importError.add("此文件不能解析,请传入正确格式的文件!");
}
package com.mo.main; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.ws.rs.Consumes; import javax.ws.rs.POST; import javax.ws.rs.Path; import javax.ws.rs.PathParam; import javax.ws.rs.Produces; import javax.ws.rs.core.Context; import javax.ws.rs.core.MediaType; import javax.ws.rs.core.Response; import javax.ws.rs.core.Response.ResponseBuilder; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileItemFactory; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.springframework.transaction.annotation.Transactional; public class ImportService { @Transactional public List<String> importTable(FileItem fileItem, HttpServletRequest request){ InputStream inputStream = null; Workbook wb = null; List<String> importError = new ArrayList<String>(); try { inputStream = fileItem.getInputStream(); String fileName = fileItem.getName(); // 文件的全路径,绝对路径名加文件名 String endName = fileName.substring(fileName.lastIndexOf(".") + 1);// 取得文件的后缀名 List<TableInfo> tableInfoList = new ArrayList<TableInfo>(); if(!endName.endsWith("xlsx") && !endName.endsWith("xls")){ importError.add("请传入Excel格式文件!"); return importError; } if(null != inputStream){ try { wb = WorkbookFactory.create(inputStream); } catch (EncryptedDocumentException e) { importError.add("此文件不能解析,请传入正确格式的文件!"); } catch (InvalidFormatException e) { importError.add("此文件不能解析,请传入正确格式的文件!"); } } if(null == wb){ importError.add("workbook为空"); return importError; } verifyWorkBook(request, wb, importError, tableInfoList); } catch (IOException e) { OperLog.logOperation(logger, userId, request, "import", "table", null, "failed"); //e.printStackTrace(); }finally{ try { if(null != wb){ wb.close(); } if(null != inputStream){ inputStream.close(); } } catch (IOException e) { // TODO Auto-generated catch block OperLog.logOperation(logger, userId, request, "import", "table", null, "failed"); } } return importError; } /** * 导入表校验 * @param request * @param wb * @param importError * @param dmDsmInfoList * @return */ public List<String> verifyWorkBook(HttpServletRequest request,Workbook wb,List<String> importError,List<TableInfo> tableInfoList){ boolean flag = true;//是否要新建表对象 List<String> tableNameList = new ArrayList<String>(); List<String> ChineseNameList = new ArrayList<String>(); List<String> dbTypes = new ArrayList<String>(); String chineseName = ""; String tableName = ""; for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) { Sheet sheet = wb.getSheetAt(numSheet); for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) { List<String> dataTypes = new ArrayList<String>(); chineseName = ""; tableName = ""; flag = true; if (sheet.getRow(rowNum) != null) { //验证表名、别名 if(null != sheet.getRow(rowNum).getCell(0) && null != sheet.getRow(rowNum).getCell(1)){ chineseName = sheet.getRow(rowNum).getCell(0).getStringCellValue(); tableName = sheet.getRow(rowNum).getCell(1).getStringCellValue(); // 检查表名、别名是否为空 if (isNullOrBlank(chineseName) && isNullOrBlank(tableName)) { // 检查中文名是否重复 if (ChineseNameList.contains(chineseName)) { flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" + "Repeated alias"); } else { ChineseNameList.add(chineseName); } // 检查表名是否重复 if (tableNameList.contains(tableName)) { flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" + "Repeated table name"); } else { tableNameList.add(tableName); } // 检查字段名是否字母开头 String regex = "^[a-zA-Z].*"; if (!tableName.matches(regex)) { flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" + "table name must start with letters"); } // 检查字段名、中文名长度是否至少一位以及不大于三十位 if (getStringLength(tableName) < 1 || getStringLength(chineseName) < 1 || getStringLength(tableName) > 30 || getStringLength(chineseName) > 30) { flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" + "Length of table name or alias must between 1-30"); } //查询数据库中表,查询是否已经有此表名 if (isTableExist(chineseName, tableName)) { flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" + "table already exist"); } }else{ flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" +"table name or alias must not be null"); } }else{ flag = false; importError.add("sheet"+ sheet.getSheetName() + "第" + (rowNum+1) +"行" +"table name or alias must not be null"); } //如果未报错,新建表 if(flag){ TableInfo tableInfo = new TableInfo(); tableInfo.setChineseName(chineseName); tableInfo.setFieldName(tableName); tableInfoList.add(tableInfo); } } } } //若无错误信息,将表保存于数据库中 if(null == importError || importError.size() == 0){ TableInfoDao.save(tableInfoList); OperLog.logOperation(logger, userId, request, "import", "standard", null, "success"); } return importError; } // 标准导入 @POST @Path("/{userId}/table/import") @Consumes({ MediaType.APPLICATION_XML, MediaType.MULTIPART_FORM_DATA }) @Produces(MediaType.APPLICATION_JSON) public Response importTable(@PathParam("userId") int userId, @Context HttpServletRequest request) throws Exception { ResponseBuilder rb = null; Operlog log = new Operlog(); List<String> importError = new ArrayList<String>(); //记录导入信息,一个属性是boolean类型,记录是否导入成功,另一个属性是string类型的list,保存导入错误具体信息 ImportInfoBean ImportInfoBean = new ImportInfoBean(); // 解决文件名中文乱码 request.setCharacterEncoding("utf-8"); if (ServletFileUpload.isMultipartContent(request)) { FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List<FileItem> items = upload.parseRequest(request); for (FileItem fileItem : items) { // 判断是否是文件或是表单内容 if (!fileItem.isFormField()) { importError = importService.importTable(userId, fileItem, request); if(null == importError || importError.size() == 0){ ImportInfoBean.setResult(true); }else{ ImportInfoBean.setResult(false); } }else{ importError.add("请选择Excel文件"); ImportInfoBean.setResult(false); } } } ImportInfoBean.setImportError(importError); rb = Response.ok(importError); return rb.build(); } /** * 判断字符串是否为空 * @param str * @return */ public boolean isNullOrBlank(String str){ if(null != str && !str.equals("")){ return true; }else{ return false; } } }