基于springboot架构的读取excel 图片并自动上传
- 控制类CommentController.java
- 接口类ICommentService.java
- 接口实现类CommentServiceImpl.java
- 读取表格内容工具类UploadUtil.java
- 腾讯云上传工具类CosClientUtil.java
- 腾讯云上传参数CosConfig.java
- 配置文件application.properties
excel 图片上传
页面准备 comment.html
代码如下：
<a class="btn btn-info" onclick="$.table.importExcel()"> <i class="fa fa-upload"></i> 导入 </a> <!-- 导入区域 --> <form id="importForm" enctype="multipart/form-data" class="mt20 mb10" style="display: none;"> <div class="col-xs-offset-1"> <input type="file" id="file" name="file"/> <!--<div class="mt10 pt5"> <input type="checkbox" id="updateSupport" name="updateSupport" title="如果登录账户已经存在，更新这条数据。"> 是否更新已经存在的用户数据 <a onclick="$.table.importTemplate()" class="btn btn-default btn-xs"><i class="fa fa-file-excel-o"></i> 下载模板</a> </div>--> <font color="red" class="pull-left mt10"> 提示：仅允许导入“xls”或“xlsx”格式文件！ </font> </div> </form>
js代码如下:
// 导入数据 importExcel: function(formId) { var currentId = $.common.isEmpty(formId) ? 'importForm' : formId; $.form.reset(currentId); layer.open({ type: 1, area: ['400px', '230px'], fix: false, //不固定 maxmin: true, shade: 0.3, title: '导入' + $.table._option.modalName + '数据', content: $('#' + currentId), btn: ['<i class="fa fa-check"></i> 导入', '<i class="fa fa-remove"></i> 取消'], // 弹层外区域关闭 shadeClose: true, btn1: function(index, layero){ var file = layero.find('#file').val(); if (file == '' || (!$.common.endWith(file, '.xls') && !$.common.endWith(file, '.xlsx'))){ $.modal.msgWarning("请选择后缀为 “xls”或“xlsx”的文件。"); return false; } var index = layer.load(2, {shade: false}); $.modal.disable(); var formData = new FormData(); formData.append("file", $('#file')[0].files[0]); formData.append("updateSupport", $("input[name='updateSupport']").is(':checked')); $.ajax({ url: $.table._option.importUrl, data: formData, cache: false, contentType: false, processData: false, type: 'POST', success: function (result) { if (result.code == web_status.SUCCESS) { $.modal.closeAll(); $.modal.alertSuccess(result.msg); $.table.refresh(); } else if (result.code == web_status.WARNING) { layer.close(index); $.modal.enable(); $.modal.alertWarning(result.msg) } else { layer.close(index); $.modal.enable(); $.modal.alertError(result.msg); } } }); } }); }
逻辑处理准备
控制类CommentController.java
@PostMapping("/importData") @ResponseBody public AjaxResult importData(MultipartFile file) { //导入数据 AjaxResult ajaxResult = commentService.importData(file); return ajaxResult; }
接口类ICommentService.java
/** * 导入数据 * @param file * @return */ AjaxResult importData(MultipartFile file);
接口实现类CommentServiceImpl.java
/** * 导入数据 * @param file * @return */ @Override @Transactional(rollbackFor = Exception.class) public AjaxResult importData(MultipartFile file) { //获取所有的内容 try { //System.out.println("================"+file.getOriginalFilename()); Map<String, Map<Integer, Map<Integer, String>>> sheets = UploadUtil.readExcelToMap(file); if (sheets != null) { Set<String> keys = sheets.keySet(); List<String> steetsNames = new ArrayList<>(); //获取所有的页名称 for (String steetsName : keys) { steetsNames.add(steetsName); } //遍历所有的sheet //for (int i = 0; i < sheets.size(); i++) { //根据页名称获取页 这里我只获取第一个sheet表格内容 Map<Integer, Map<Integer, String>> rows = sheets.get(steetsNames.get(0)); //System.out.println("页名称:"+steetsNames.get(0)); //根据所有的行 遍历 for (int j = 1; j < rows.size(); j++) { Map<Integer, String> columns = rows.get(j); //System.out.println("columns:"+columns.toString()); //========以下是业务处理逻辑，取出数据并保存========== //设置参数并保存 Comment comment = new Comment(); //考种 //图书系列 String examName = columns.get(0); String typeName = columns.get(1); if (StringUtils.isNotEmpty(examName) && StringUtils.isNotEmpty(typeName)) { Exam exam = examMapper.selectExamByName(examName); if (exam != null) { Type type = typeMapper.selectTypeByParam(typeName,exam.getId()); if (type != null) { comment.setTypeCode(type.getTypeCode()); } } } //用户头像 if (StringUtils.isNotEmpty(columns.get(2))) { comment.setHeadImageUrl(columns.get(2)); }else { comment.setHeadImageUrl(Constants.OFFICAL_ICON); } //用户昵称 comment.setNickName(columns.get(3)); //评价内容 comment.setContent(columns.get(4)); //图片 if (StringUtils.isEmpty(columns.get(5)) && StringUtils.isEmpty(columns.get(6)) && StringUtils.isEmpty(columns.get(7)) ) { //无图 comment.setCommentType(Constants.COMMENT_TYPE.NO.getValue()); }else { //有图 comment.setCommentType(Constants.COMMENT_TYPE.YES.getValue()); String originalUrls = ""; if (StringUtils.isNotEmpty(columns.get(5)) ) { originalUrls = originalUrls + columns.get(5) + ","; } if (StringUtils.isNotEmpty(columns.get(6)) ) { originalUrls = originalUrls + columns.get(6) + ","; } if (StringUtils.isNotEmpty(columns.get(7)) ) { originalUrls = originalUrls + columns.get(7); } if(StringUtils.isNotEmpty(originalUrls) && originalUrls.endsWith(",")){ originalUrls = originalUrls.substring(0,originalUrls.length()-1); } String smallUrls = ThumbnailGenUtil.getThumbnailUrlByMulti(originalUrls); if(StringUtils.isNotEmpty(smallUrls) && smallUrls.endsWith(",")){ smallUrls = smallUrls.substring(0,smallUrls.length()-1); } comment.setOriginalUrls(originalUrls); comment.setSmallUrls(smallUrls); } comment.setStatus(Constants.STATUS.ACTIVE.getValue()); insertComment(comment); //标签 String col8 = columns.get(8); if (col8 != null ) { if (col8.contains("，") || col8.contains(",")) { col8 = col8.replace("，", ","); //多个标签 String[] split = col8.split(","); for (String s : split) { Mark mark = markMapper.selectMarkByName(s); if (mark != null) { CommentMark commentMark = new CommentMark(); commentMark.setCommentId(comment.getId()); commentMark.setMarkId(mark.getId()); commentMark.setIsValid(Constants.IS_VALID.YES.getValue()); commentMark.setCreateUser(ShiroUtils.getUserId()); commentMark.setCreateDate(new Date()); commentMarkMapper.insertCommentMark(commentMark); } } }else { //一个标签 Mark mark = markMapper.selectMarkByName(col8); if (mark != null) { CommentMark commentMark = new CommentMark(); commentMark.setCommentId(comment.getId()); commentMark.setMarkId(mark.getId()); commentMark.setIsValid(Constants.IS_VALID.YES.getValue()); commentMark.setCreateUser(ShiroUtils.getUserId()); commentMark.setCreateDate(new Date()); commentMarkMapper.insertCommentMark(commentMark); } } } //===============业务数据处理结束===================== } //} return AjaxResult.success(); } }catch (Exception e) { //异常回滚 TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(); e.printStackTrace(); } return AjaxResult.error(); }
读取表格内容工具类UploadUtil.java
package com.dongao.project.utils; import com.dongao.project.config.ConfigConstant; import com.qcloud.cos.model.ObjectMetadata; import org.apache.poi.POIXMLDocumentPart; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; import org.springframework.web.multipart.MultipartFile; import java.io.*; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; /** * Created by nao'nao on 2020/4/17. */ public class UploadUtil { /** * 读取文件内容到map * @param file * @return * @throws IOException */ public static Map<String, Map<Integer, Map<Integer, String>>> readExcelToMap(MultipartFile file) throws IOException { Workbook wb = null; Sheet sheet = null; Row row = null; // 声明所有页的集合 Map<String, Map<Integer, Map<Integer, String>>> mapSheet = new LinkedHashMap<>(); try { //读取文档 wb = readExcel(file); if (wb != null) { // 获取总页数 int pageSize = wb.getNumberOfSheets(); //System.out.println("总页数："+pageSize); for (int i = 0; i < pageSize; i++) { // 声明当前页的行和列 Map<Integer, Map<Integer, String>> map = new HashMap<>(); // 获取当前页 sheet = wb.getSheetAt(i); //声明当前页图片的集合 Map<String, PictureData> pMap = null; //获取当前页的所有图片 if(file.getOriginalFilename().endsWith(".xls")){ //用于区分 没写方法重载 pMap = getPictures1((HSSFSheet) sheet); }else{ //用于区分 没写方法重载 pMap = getPictures2((XSSFSheet) sheet); } //获取页名称 String sheetName = sheet.getSheetName(); //获取当前页的最大行数 int rowSize = sheet.getPhysicalNumberOfRows(); //System.out.println("总行数:"+rowSize); //System.out.println("遍历所有行"); for (int j = 0; j < rowSize; j++) { //System.out.println("获取第"+j+"行"); row = sheet.getRow(j); //获取当前页的最大列数; int columnSize = row.getPhysicalNumberOfCells(); // 声明当前列 Map<Integer, String> columnMap = new HashMap<>(); //System.out.println("列大小:"+columnSize); for (int j2 = 0; j2 < columnSize; j2++) { // System.out.println("获取第"+j2+"列的内容"); String value = (String)getCellFormatValue(row.getCell(j2)); // 添加当前列的内容 j2代表第几列 value是内容 columnMap.put(j2, value); } // 添加当前行的内容 j代表第几行 value是列的内容 意思是第几行第几列的内容 map.put(j, columnMap); } //解析图片并上传到服务器 进行文件上传后 返回上传地址 并根据图片所在的表格位置 赋值表格位置为 上传后的返回地址 Object key[] = pMap.keySet().toArray(); //上传图片 腾讯云上传图片 CosClientUtil cosClientUtil = new CosClientUtil(); for (int v = 0; v < pMap.size(); v++) { //获取图像数据对象 PictureData pic = pMap.get(key[v]); //获取文件名称 String picName = key[v].toString(); //获取文件后缀名 String ext = pic.suggestFileExtension(); //=============本地测试图片上传开始============== FileOutputStream out = new FileOutputStream("D:\\" + picName + "." + ext); out.write(data); out.flush(); out.close(); //=============本地测试图片上传结束============== //===============腾讯云上传图片上传开始============== String imgToCos = cosClientUtil.simpleUploadImgToCos(pic, ConfigConstant.comment); //String imgToCos = "22222222"; //===============腾讯云上传图片上传结束============== //解析key 并根据key 设置 某一行的某一列的 图片链接 String[] split = picName.split("-"); Integer rowIndex = Integer.parseInt(split[0].toString()),columnIndex = Integer.parseInt(split[1].toString()); //根据行下标 获取所有的列 Map<Integer, String> columns = map.get(rowIndex); //根据列下标 设置图片链接值 //参数2为图片上传后的路径 columns.put(columnIndex, imgToCos); } // 添加当前页的所有内容 mapSheet.put(sheetName, map); } } return mapSheet; } catch (Exception e) { e.printStackTrace(); return null; } } // 读取excel private static Workbook readExcel(MultipartFile file) { Workbook wb = null; if (file == null) { return null; } String originalFilename = file.getOriginalFilename(); String extString = originalFilename.substring(originalFilename.lastIndexOf(".")); InputStream is = null; try { is = file.getInputStream(); if (".xls".equals(extString)) { return wb = new HSSFWorkbook(is); } else if (".xlsx".equals(extString)) { return wb = new XSSFWorkbook(is); } else { return wb = null; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return wb; } // 读取图片 public static Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException { Map<String, PictureData> map = new HashMap<String, PictureData>(); List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren(); for (HSSFShape shape : list) { if (shape instanceof HSSFPicture) { HSSFPicture picture = (HSSFPicture) shape; HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor(); PictureData pdata = picture.getPictureData(); String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号 map.put(key, pdata); } } return map; } //读取图片 public static Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException { Map<String, PictureData> map = new HashMap<String, PictureData>(); List<POIXMLDocumentPart> list = sheet.getRelations(); for (POIXMLDocumentPart part : list) { if (part instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) part; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { XSSFPicture picture = (XSSFPicture) shape; XSSFClientAnchor anchor = picture.getPreferredSize(); CTMarker marker = anchor.getFrom(); String key = marker.getRow() + "-" + marker.getCol(); map.put(key, picture.getPictureData()); } } } return map; } //获取表格字段属性 private static Object getCellFormatValue(Cell cell) { Object cellValue = null; if (cell != null) { // 判断cell类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA: { // 判断cell是否为日期格式 if (DateUtil.isCellDateFormatted(cell)) { // 转换为日期格式YYYY-mm-dd cellValue = cell.getDateCellValue(); } else { // 数字 cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING: { cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } } else { cellValue = ""; } return cellValue; } public static void printImg(Map<String, PictureData> sheetList) throws IOException { Object key[] = sheetList.keySet().toArray(); for (int i = 0; i < sheetList.size(); i++) { PictureData pic = sheetList.get(key[i]); String picName = key[i].toString(); String ext = pic.suggestFileExtension(); byte[] data = pic.getData(); FileOutputStream out = new FileOutputStream("D:\\" + picName + "." + ext); out.write(data); out.flush(); out.close(); } } //根据路径去腾讯云判断当前图片是否存在 public static boolean hasImage(String key){ CosClientUtil cosClientUtil = new CosClientUtil(); ObjectMetadata objectMetadata = cosClientUtil.getObjectMetadata(key); if (objectMetadata != null) { System.out.println("objectMetadata存在！"); return true; } return false; } }
腾讯云上传工具类CosClientUtil.java
package com.dongao.project.utils; import com.dongao.project.config.CosConfig; import com.qcloud.cos.COSClient; import com.qcloud.cos.ClientConfig; import com.qcloud.cos.auth.BasicCOSCredentials; import com.qcloud.cos.auth.COSCredentials; import com.qcloud.cos.model.ObjectMetadata; import com.qcloud.cos.model.PutObjectResult; import com.qcloud.cos.region.Region; import com.ruoyi.common.utils.StringUtils; import com.ruoyi.common.utils.file.FileUploadUtils; import com.ruoyi.common.utils.spring.SpringUtils; import org.apache.poi.ss.usermodel.PictureData; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.Random; /** * Created by nao'nao on 2020/4/17. */ public class CosClientUtil { private static Logger logger = LoggerFactory.getLogger(CosClientUtil.class); private static CosConfig cosConfig = SpringUtils.getBean(CosConfig.class); /**初始化密钥信息*/ private COSCredentials cred = new BasicCOSCredentials(cosConfig.getSecretId(), cosConfig.getSecretKey()); /**初始化客户端配置,设置bucket所在的区域*/ private ClientConfig clientConfig = new ClientConfig(new Region(cosConfig.getRegion())); /**初始化cOSClient*/ private COSClient cOSClient = new COSClient(cred, clientConfig); /** * 上传图片 * @param file * @param businessName * @return * @throws Exception */ public String uploadImgToCos(MultipartFile file, String businessName) throws Exception { int imageSize = Integer.parseInt(cosConfig.getImageSize()); int maxSize = imageSize << 20; if (file.getSize() > maxSize) { throw new Exception("上传图片大小不能超过"+imageSize+"M！"); } if (StringUtils.isEmpty(businessName)) { businessName = cosConfig.getCommon(); } //生成文件夹层级 Calendar cale = Calendar.getInstance(); int year = cale.get(Calendar.YEAR); SimpleDateFormat sdf = new SimpleDateFormat("MM"); Date dd = cale.getTime(); String month = sdf.format(dd); String folderName = cosConfig.getProjectName()+"/image/"+businessName+"/"+year+"/"+month+"/"; //图片名称 String originalFilename = file.getOriginalFilename(); Random random = new Random(); //生成新的图片名称(随机数0-9999+系统当前时间+上传图片名) String name; if (originalFilename.lastIndexOf(".") != -1) { name = random.nextInt(10000) + System.currentTimeMillis() + originalFilename.substring(originalFilename.lastIndexOf(".")); }else { String extension = FileUploadUtils.getExtension(file); name = random.nextInt(10000) + System.currentTimeMillis() + "." + extension; } //生成对象键 String key = folderName+name; try { InputStream inputStream = file.getInputStream(); this.uploadFileToCos(inputStream, key); //return "http://" + cosConfig.getBucketName() + ".cos."+cosConfig.getRegion()+".myqcloud.com/" + key; return key; } catch (Exception e) { throw new Exception("图片上传失败"); } } /** * 上传图片 简单上传 * @param pic * @param businessName * @return * @throws Exception */ public String simpleUploadImgToCos(PictureData pic, String businessName) throws Exception { if (StringUtils.isEmpty(businessName)) { businessName = cosConfig.getCommon(); } //生成文件夹层级 Calendar cale = Calendar.getInstance(); int year = cale.get(Calendar.YEAR); SimpleDateFormat sdf = new SimpleDateFormat("MM"); Date dd = cale.getTime(); String month = sdf.format(dd); String folderName = cosConfig.getProjectName()+"/image/"+businessName+"/"+year+"/"+month+"/"; //获取图片后缀名 String ext = pic.suggestFileExtension(); Random random = new Random(); //生成新的图片名称(随机数0-9999+系统当前时间+上传图片名) //图片名称 String name = random.nextInt(10000) + System.currentTimeMillis() + "." + ext; //生成对象键 String key = folderName+name; //图片数据 byte[] data = pic.getData(); //System.out.println("开始上传图片"); try { //System.out.println("====================="); InputStream inputStream = new ByteArrayInputStream(data); //System.out.println("===========inputStream"); this.uploadFileToCos(inputStream, key); //return "http://" + cosConfig.getBucketName() + ".cos."+cosConfig.getRegion()+".myqcloud.com/" + key; return key; } catch (Exception e) { throw new Exception("图片上传失败"); } } /** * 上传到COS服务器 如果同名文件会覆盖服务器上的 * @param instream * @param key * @return 出错返回"" ,唯一MD5数字签名 */ public String uploadFileToCos(InputStream instream, String key) { String etag = ""; try { // 创建上传Object的Metadata ObjectMetadata objectMetadata = new ObjectMetadata(); // 设置输入流长度为500 objectMetadata.setContentLength(instream.available()); // 设置 Content type objectMetadata.setContentType(getcontentType(key.substring(key.lastIndexOf(".")))); // 上传文件 //System.out.println("===============开始上传图片key："+key); logger.info("开始上传图片key:【{}】",key); PutObjectResult putResult = cOSClient.putObject(cosConfig.getBucketName(), key, instream, objectMetadata); //System.out.println("===============上传成功！"); etag = putResult.getETag(); logger.info("图片上传成功etag:【{}】",etag); } catch (IOException e) { e.printStackTrace(); } finally { try { if (instream != null) { //关闭输入流 instream.close(); } // 关闭客户端(关闭后台线程) cOSClient.shutdown(); } catch (IOException e) { e.printStackTrace(); } } return etag; } /** * Description: 判断Cos服务文件上传时文件的contentType * @param filenameExtension 文件后缀 * @return String */ public String getcontentType(String filenameExtension) { String bmp = "bmp"; if (bmp.equalsIgnoreCase(filenameExtension)) { return "image/bmp"; } String gif = "gif"; if (gif.equalsIgnoreCase(filenameExtension)) { return "image/gif"; } String jpeg = "jpeg"; String jpg = "jpg"; String png = "png"; if (jpeg.equalsIgnoreCase(filenameExtension) || jpg.equalsIgnoreCase(filenameExtension) || png.equalsIgnoreCase(filenameExtension)) { return "image/jpeg"; } String html = "html"; if (html.equalsIgnoreCase(filenameExtension)) { return "text/html"; } String txt = "txt"; if (txt.equalsIgnoreCase(filenameExtension)) { return "text/plain"; } String vsd = "vsd"; if (vsd.equalsIgnoreCase(filenameExtension)) { return "application/vnd.visio"; } String pptx = "pptx"; String ppt = "ppt"; if (pptx.equalsIgnoreCase(filenameExtension) || ppt.equalsIgnoreCase(filenameExtension)) { return "application/vnd.ms-powerpoint"; } String docx = "docx"; String doc = "doc"; if (docx.equalsIgnoreCase(filenameExtension) || doc.equalsIgnoreCase(filenameExtension)) { return "application/msword"; } String xml = "xml"; if (xml.equalsIgnoreCase(filenameExtension)) { return "text/xml"; } String mp4 = ".mp4"; if (mp4.equalsIgnoreCase(filenameExtension)) { return "application/octet-stream"; } return "image/jpeg"; } //根据图片路径获取图片 public ObjectMetadata getObjectMetadata(String key) { // Bucket的命名格式为 BucketName-APPID ，此处填写的存储桶名称必须为此格式 ObjectMetadata objectMetadata = null; try { objectMetadata = cOSClient.getObjectMetadata(cosConfig.getBucketName(), key); }catch (Exception e) { //e.printStackTrace(); } finally { try { // 关闭客户端(关闭后台线程) cOSClient.shutdown(); } catch (Exception e) { //e.printStackTrace(); } } return objectMetadata; } }
腾讯云上传参数CosConfig.java
package com.dongao.project.config; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; /** * 腾讯云上传参数 * @author： * @create: 2020/04/17 */ @Component @ConfigurationProperties(prefix = "cos") public class CosConfig { private String secretId = "123456qwertty"; private String secretKey = "123sdfghghjjj"; private String region = "ap-beijing"; private String bucketName = "sdffgddg"; private String projectName = "aaaaaaa"; private String common = "common"; private String imageSize = "2"; private String prefixDomain = "http://aa.bb.com/"; public String getSecretId() { return secretId; } public void setSecretId(String secretId) { this.secretId = secretId; } public String getSecretKey() { return secretKey; } public void setSecretKey(String secretKey) { this.secretKey = secretKey; } public String getRegion() { return region; } public void setRegion(String region) { this.region = region; } public String getBucketName() { return bucketName; } public void setBucketName(String bucketName) { this.bucketName = bucketName; } public String getProjectName() { return projectName; } public void setProjectName(String projectName) { this.projectName = projectName; } public String getCommon() { return common; } public void setCommon(String common) { this.common = common; } public String getImageSize() { return imageSize; } public void setImageSize(String imageSize) { this.imageSize = imageSize; } public String getPrefixDomain() { return prefixDomain; } public void setPrefixDomain(String prefixDomain) { this.prefixDomain = prefixDomain; } }
配置文件application.properties
如此具备以上即可，仅供参考
