导入excle ,并处理数据实现文字和图片相对应
导入excel 处理文件，我使用的是poi 处理
引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>examples</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>excelant</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>excelant</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>scratchpad</artifactId>
<version>3.1.7</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
package com.zeus.utils.excel; import com.zeus.constant.AdminConstants; 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.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.*; /** * @Description excel处理工具类 * @Author latiao * @Date 2019/4/7 * @Version 1.0 */ public class ExcelItemUtil { /** * 判断文件是否是excle文件 * * @param file * @return boolean * @methodName checkFile * @author latiao * @date 2019/4/7 */ public static boolean checkFile(MultipartFile file) { //判断文件是否存在 if (null == file) { return false; } //获得文件名 String fileName = file.getOriginalFilename(); //判断文件是否是excel文件 if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) { return false; } return true; } /** * 获取所有excel文字数据 * * @param file * @return java.util.List<java.util.Map < java.lang.Integer , java.lang.String [ ]>> * @methodName readExcel * @author latiao * @date 2019/4/7 */ public static List<Map<Integer, String[]>> readExcel(MultipartFile file) { //获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 List<Map<Integer, String[]>> list = new ArrayList<>(); if (workbook != null) { //循环sheet for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { Map<Integer, String[]> map = new HashMap<>(10); //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if (sheet == null) { continue; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //循环当前sheet除了第一行的所有行 for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) { //获得当前行 Row row = sheet.getRow(rowNum); if (row == null) { continue; } //获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); String[] cells = new String[row.getPhysicalNumberOfCells()]; //循环当前行 for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) { Cell cell = row.getCell(cellNum); cells[cellNum] = getCellValue(cell); } map.put(rowNum, cells); } list.add(map); } try { if (workbook != null) { workbook.close(); } } catch (IOException e) { e.printStackTrace(); } } return list; } /** * 不同格式的excel 得到Workbook类型不同 * * @param file * @return org.apache.poi.ss.usermodel.Workbook * @methodName getWorkBook * @author latiao * @date 2019/4/7 */ public static Workbook getWorkBook(MultipartFile file) { //获得文件名 String fileName = file.getOriginalFilename(); //创建Workbook工作薄对象，表示整个excel Workbook workbook = null; try { //获取excel文件的io流 InputStream is = file.getInputStream(); //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith("xls")) { //2003 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith("xlsx")) { //2007 workbook = new XSSFWorkbook(is); } } catch (IOException e) { } return workbook; } /** * 得到string的数据，将所有文字都设置为string方式读取 * * @param cell * @return java.lang.String * @methodName getCellValue * @author latiao * @date 2019/4/7 */ public static String getCellValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } //把数字当成String来读，避免出现1读成1.0的情况 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cell.setCellType(Cell.CELL_TYPE_STRING); } //判断数据的类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //数字 cellValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: //字符串 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()); break; case Cell.CELL_TYPE_BLANK: //空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: //故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } /** * 03版获取图片 * * @param sheetNum * @param sheet * @param workbook * @return java.util.Map<java.lang.String , org.apache.poi.ss.usermodel.PictureData> * @methodName getSheetPictrues03 * @author latiao * @date 2019/4/7 */ public static Map<String, PictureData> getSheetPictrues03(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<>(10); 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); //设置图片的位置 sheet数_行数_列数 String picIndex = String.valueOf(sheetNum) + "_" + String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1()); sheetIndexPicMap.put(picIndex, picData); } } return sheetIndexPicMap; } else { return null; } } /** * 07格式excel获取图片。 * * @param sheetNum * @param sheet * @param workbook * @return java.util.Map<java.lang.String , org.apache.poi.ss.usermodel.PictureData> * @methodName getSheetPictrues07 * @author latiao * @date 2019/4/7 */ public static Map<String, PictureData> getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<>(10); 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 = String.valueOf(sheetNum) + "_" + ctMarker.getRow() + "_" + ctMarker.getCol(); sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } return sheetIndexPicMap; } /** * 得到所有图片 * * @param file * @return java.util.List<java.util.Map < java.lang.String , org.apache.poi.ss.usermodel.PictureData>> * @methodName getAllDate * @author latiao * @date 2019/4/7 */ // /图片及位置获取 public static List<Map<String, PictureData>> getAllDate(MultipartFile file) { // 创建文件 System.out.println("读取文件"); // 创建流 // 获取文件后缀名 String fileExt = file.getName().substring(file.getName().lastIndexOf(".") + 1); // 创建Workbook Workbook wb = getWorkBook(file); // 创建sheet Sheet sheet = null; //获取excel sheet总数 int sheetNumbers = wb.getNumberOfSheets(); // sheet list List<Map<String, PictureData>> sheetList = new ArrayList<>(); // 循环sheet for (int i = 0; i < sheetNumbers; i++) { sheet = wb.getSheetAt(i); // map等待存储excel图片 Map<String, PictureData> sheetIndexPicMap; // 判断用07还是03的方法获取图片 if (fileExt.equals("xls")) { //03版本excel sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb); } else { sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb); } // 将当前sheet图片map存入list sheetList.add(sheetIndexPicMap); } // Map map = getData(excelPath); return sheetList; } /** * 输出图片 * * @param pic * @return java.lang.String * @methodName printImg * @author latiao * @date 2019/4/7 */ public static String printImg(PictureData pic) { // 获取图片格式 String path = null; String url = null; if (pic != null) { String ext = pic.suggestFileExtension(); String format = System.currentTimeMillis() + ""; Random r = new Random(); for (int i = 0; i < 3; i++) { format += r.nextInt(10); } path = AdminConstants.IMG_PATH + format + "." + ext; url = AdminConstants.SERVICE_PATH + format + "." + ext; byte[] data = pic.getData(); FileOutputStream out = null; try { out = new FileOutputStream(path); out.write(data); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } } return url; } }
然后将数据插入对象，并存入数据库
@RequestMapping("excel")
public String insertProduct(MultipartFile file) {
JSONObject json = new JSONObject();
try {
if (ExcelItemUtil.checkFile(file)) {
//获得行数据列表,map中方单独sheet的每一行数据 String[]代表一行的数据，放每一cell数据
List<Map<Integer, String[]>> maps = ExcelItemUtil.readExcel(file);
//获得图片数据
List<Map<String, PictureData>> allDate = ExcelItemUtil.getAllDate(file);
//循环每一个sheet
for (int i = 0; i < maps.size(); i++) {
//当前sheet的文字信息
Map<Integer, String[]> strMap = maps.get(i);
//图片信息
Map<String, PictureData> PicDataMap = allDate.get(i);
//遍历map 获得信息
Object strkey[] = strMap.keySet().toArray();
//循环当前sheet
for (int j = 0; j < strMap.size(); j++) {
//信息cell数组
String[] strings = strMap.get(strkey[j]);
//GOODs对象
Goods goods = new Goods();
if (strings[0] != null) {
log.info(strings[0]);
goods.setGoodsId(strings[0]);
}
if (strings[1] != null) {
log.info(strings[1]);
goods.setGoodsName(strings[1]);
}
if (strings[2] != null) {
log.info(strings[2]);
goods.setTitle(strings[2]);
}
if (strings[3] != null) {
log.info(strings[3]);
goods.setModelnum(strings[3]);
}
if (strings[4] != null) {
log.info(strings[4]);
goods.setValueAdd(strings[4]);
}
if (strings[5] != null) {
log.info(strings[5]);
goods.setHotParts(strings[5]);
}
if (strings[6] != null) {
log.info(strings[6]);
goods.setPrice(new BigDecimal(strings[6]));
}
//小图
List<String> smallPic = getPicture(PicDataMap, i, strkey[j], 7, 7);
if(smallPic.size()>0){
goods.setSmallPic(smallPic.get(0));
}
//信息行数
List<Integer> imageIds = new ArrayList<>();
List<String> picturePath = getPicture(PicDataMap, i, strkey[j], 8, 13);
if(picturePath.size()>0){
for (String path : picturePath) {
Image image = new Image();
image.setImgUrl(path);
imageMapper.insert(image);
imageIds.add(image.getImgId());
}
}
goods.setImagId(JSONArray.toJSONString(imageIds));
//商品介绍
List<String> introduction = getPicture(PicDataMap, i, strkey[j], 14, 39);
goods.setIntroduction(JSONArray.toJSONString(introduction));
//规格
List<String> spec = getPicture(PicDataMap, i, strkey[j], 40, 43);
goods.setSpec(JSONArray.toJSONString(spec));
log.info("goods="+goods.toString());
goodsMapper.insertGoodsByExcel(goods);
}
}
json.put("msg", "上传成功");
json.put("code", 0);
} else {
json.put("msg", "文件不是excle");
json.put("code", 12003);
}
} catch (Exception e) {
json.put("msg", "上传失败");
json.put("code", 12002);
}
return json.toString();
}
private List<String> getPicture(Map<String, PictureData> picDataMap, Integer index, Object strkey, Integer start, Integer size) {
List<String> list = new ArrayList<>();
for (int i = start; i < size + 1; i++) {
PictureData pictureData = picDataMap.get(index + "_" + strkey + "_" + i);
if (pictureData != null) {
String path = ExcelItemUtil.printImg(pictureData);
list.add(path);
}
}
return list;
}
遇事犹豫不决时，须持虎狼之心，行仁义之事
