失眠网 > java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）

java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）

时间：2021-12-18 06:08:49

一个表格框可以添加多个图片

Excel表格导入数据的同时导入图片

@Transactional(rollbackFor = Exception.class)public String selectExcelName(MultipartFile multipartFile, String tableName,String token) {// String proFile = "D:/ruoyi/uploadPath";//文件存放的路径// String filePath = proFile + "/" + "pic/" ;//图片保存路径SysUser userInfo = tokenStoreService.getUserInfo(token, SysUser.class);//根据tableName获取tableIdString tableId = schemaMenuBaseInfoEntityMapper.selMessageByTableName(tableName);//获取当前的日期Date date = new Date();//设置要获取到什么样的时间SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//获取String类型的时间String createdate = sdf.format(date);//根据tableId去nodiot_schema_table_column_base_info中获取字段名称List<Map<String, Object>> mapList = schemaTableColumnBaseInfoEntityMapper.selectDemoById(tableId);List<Map<String, Object>> list = new ArrayList<>();InputStream inputStream = null; //文件流对象Workbook wb = null;try {inputStream = multipartFile.getInputStream();//创建文件流wb = new HSSFWorkbook(inputStream);//创建工作簿} catch (IOException e) {e.printStackTrace();}Map<Integer, String> map1 = new HashMap<>();//存放第几列和字段的关联关系Sheet sheetAt = wb.getSheetAt(0);Map<String, PictureData> sheetPictrues03Map = ExcelUtils.getSheetPictrues03( (HSSFSheet) sheetAt, (HSSFWorkbook) wb);// 多个图片的时候使用// Map<String, List<PictureData>> sheetPictrues03Map = ExcelUtils.getSheetPictrues03( (HSSFSheet) sheetAt, (HSSFWorkbook) wb);String rowAndCellkey = "";if(null != sheetPictrues03Map && sheetPictrues03Map.size() > 0){for (Map.Entry<String, PictureData> entry : sheetPictrues03Map.entrySet()) {rowAndCellkey = entry.getKey();}// 多个图片的时候使用/*for (Map.Entry<String, List<PictureData>> entry : sheetPictrues03Map.entrySet()) {rowAndCellkey = entry.getKey();}*/}// 获取图片所存取的列号String cellString = rowAndCellkey.substring(rowAndCellkey.indexOf("_")+1, rowAndCellkey.length());Map<String, String> pathMap = null;try {//写入图片，并返回图片路径，key：图片坐标，value：图片路径pathMap = printImg(sheetPictrues03Map);} catch (IOException e) {e.printStackTrace();}int firstRowNum = sheetAt.getFirstRowNum();int lastRowNum = sheetAt.getLastRowNum();for (int i = firstRowNum; i <= lastRowNum; i++) { //遍历行Map<String, Object> maps = new HashMap<>();Row row = sheetAt.getRow(i);int firstCellNum = row.getFirstCellNum();int lastCellNum = row.getLastCellNum();for (int i1 = firstCellNum; i1 < lastCellNum; i1++) { //遍历列if (i == 0) { //从第一行开始for (Map<String, Object> map : mapList) { // 遍历比对，put数据if (row.getCell(i1).toString().equals(map.get("name"))) {map1.put(i1, map.get("java_field").toString());break;}}} else {Cell cell = row.getCell(i1);if (cell == null) {maps.put(map1.get(i1), "");} else {maps.put(map1.get(i1), "'" + cell.toString() + "'");}}if (i > 0) {// 不是标头列时，添加图片路径String path = pathMap.get(i + "_" +cellString);maps.put(map1.get(Integer.parseInt(cellString)), "'" + path + "'");}}if (i != 0) {list.add(maps);}}StringBuilder sql = new StringBuilder();StringBuilder sqls = new StringBuilder();try {for (Map<String, Object> stringObjectMap : list) {Set<String> strings = stringObjectMap.keySet();sql.delete(0, sql.length());sqls.delete(0, sqls.length());sqls.append("('" + UUID.randomUUID().toString().replaceAll("-", "") + "', ' "+userInfo.getUserName()+" ' , ' "+createdate+" ' , ' "+userInfo.getDeptId()+" ' ,");sql.append("insert into " + tableName + "( id ,create_user,create_date,dept_id,");for (String string : strings) {if (!"".equals(stringObjectMap.get(string))) {sql.append(string + ",");sqls.append(stringObjectMap.get(string) + ",");}}String substring = sql.substring(0, sql.length() - 1) + ") values ";String result = substring + (sqls.substring(0, sqls.length() - 1) + ")");schemaTableColumnBaseInfoEntityMapper.inserta(result);}} catch (Exception e) {e.printStackTrace();TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();return "error";}return "true";}//写入图片，并返回图片路径，key：图片坐标，value：图片路径 ( 只能单个图片)private Map<String, String> printImg(Map<String, PictureData> sheetList) throws IOException {Map<String, String> pathMap = new HashMap();Object[] key = sheetList.keySet().toArray();/*File f = new File(path);if (!f.exists()) {f.mkdirs(); // 创建目录}*/for (int i = 0; i < sheetList.size(); i++) {// 获取图片流PictureData pic = sheetList.get(key[i]);// 获取图片索引String picName = key[i].toString();// 获取图片格式后缀String ext = pic.suggestFileExtension();String fileName = java.util.UUID.randomUUID().toString().replaceAll("-","");byte[] data = pic.getData();ResultBody<ResultFileModel> upload = remoteFileService.upload(data, 1, applicationName, fileName+"."+ext);System.out.println(upload);if(null != upload && null != upload.getData()){String imagePath = upload.getData().getPath();Map<String,Object> mapUrl = new HashMap<>();mapUrl.put("uid",fileName);mapUrl.put("url",imagePath);mapUrl.put("fileUrl",imagePath);mapUrl.put("downloadURL",imagePath);mapUrl.put("imgURL",imagePath);mapUrl.put("state","done");mapUrl.put("name",fileName+"."+ext);mapUrl.put("pdfFileUrl", "");mapUrl.put("fileType", "ext");pathMap.put(picName, JSONObject.toJSONString(mapUrl));}}return pathMap;}//写入图片，并返回图片路径，key：图片坐标，value：图片路径( 导入一条数据支持多个图片,之前只能单个图片)private Map<String, String> printImg(Map<String, List<PictureData>> sheetList) throws IOException {Map<String, String> pathMap = new HashMap();Object[] key = sheetList.keySet().toArray();for (int i = 0; i < sheetList.size(); i++) {// 获取图片索引String picName = key[i].toString();// 获取图片流List<PictureData> pics = sheetList.get(key[i]);if( null != pics && pics.size() > 0){List<Map<String,Object> > list = new ArrayList<>();for (PictureData pic : pics) {//PictureData pic1 = sheetList.get(key[i]);// 获取图片格式后缀String ext = pic.suggestFileExtension();String fileName = java.util.UUID.randomUUID().toString().replaceAll("-","");byte[] data = pic.getData();ResultBody<ResultFileModel> upload = remoteFileService.upload(data, 1, applicationName, fileName+"."+ext);if(null != upload && null != upload.getData()){String imagePath = upload.getData().getPath();Map<String,Object> mapUrl = new HashMap<>();mapUrl.put("uid",fileName);mapUrl.put("url",imagePath);mapUrl.put("fileUrl",imagePath);mapUrl.put("downloadURL",imagePath);mapUrl.put("imgURL",imagePath);mapUrl.put("state","done");mapUrl.put("name",fileName+"."+ext);mapUrl.put("pdfFileUrl", "");mapUrl.put("fileType", "ext");list.add(mapUrl);}}pathMap.put(picName, JSON.toJSON(list).toString() );}}return pathMap;}// 处理excel中的图片，分和两个版本package cn.isite90.form.server.form.service;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ooxml.POIXMLDocumentPart;import org.apache.poi.ss.usermodel.PictureData;import org.apache.poi.xssf.usermodel.*;import org.openxmlformats.schemas.drawingml.x.spreadsheetDrawing.CTMarker;import java.io.IOException;import java.util.HashMap;import java.util.List;import java.util.Map;public class ExcelUtils {/** 支持单个图片* 获取Excel图片* @param sheet 当前sheet对象* @param workbook 工作簿对象* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData* @throws IOException*/public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) {Map<String, PictureData> map = new HashMap<String, PictureData>();List<HSSFPictureData> pictures = workbook.getAllPictures();if (pictures.size() != 0) {for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();if (shape instanceof HSSFPicture) {HSSFPicture pic = (HSSFPicture) shape;int pictureIndex = pic.getPictureIndex() - 1;HSSFPictureData picData = pictures.get(pictureIndex);String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());map.put(picIndex, picData);}}return map;} else {return null;}}/*** 获取Excel图片* @param sheetNum 当前sheet编号* @param sheet 当前sheet对象* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData*/public static Map<String, PictureData> getSheetPictrues07(int sheetNum, XSSFSheet sheet) {Map<String, PictureData> map = new HashMap<String, PictureData>();for (POIXMLDocumentPart dr : sheet.getRelations()) {if (dr instanceof XSSFDrawing) {XSSFDrawing drawing = (XSSFDrawing) dr;List<XSSFShape> shapes = drawing.getShapes();for (XSSFShape shape : shapes) {XSSFPicture pic = (XSSFPicture) shape;XSSFClientAnchor anchor = pic.getPreferredSize();CTMarker ctMarker = anchor.getFrom();String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();map.put(picIndex, pic.getPictureData());}}}return map;}《 ========================= 下面的一个表格支持多个图片 ===========================》/** 支持多个图片* 获取Excel图片* @param sheet 当前sheet对象* @param workbook 工作簿对象* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData* @throws IOException*/public static Map<String, List<PictureData>> getSheetPictrues03s(HSSFSheet sheet, HSSFWorkbook workbook) {Map<String, PictureData> map = new HashMap<String, PictureData>();Map<String, List<PictureData>> maps = new HashMap<String, List<PictureData>>();List<HSSFPictureData> pictures = workbook.getAllPictures();List<PictureData> list = new ArrayList<>();if (pictures.size() != 0) {String picIndex = "";for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();if (shape instanceof HSSFPicture) {HSSFPicture pic = (HSSFPicture) shape;int pictureIndex = pic.getPictureIndex() - 1;HSSFPictureData picData = pictures.get(pictureIndex);picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());list.add(picData );}}maps.put(picIndex, list);return maps;} else {return null;}}}

测试demo地址：/download/qq_36961226/29500865

java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）可以导入多个图片）

如果觉得《java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。