1. 引入poi的两个依赖
org.apache.poi poi 3.15 org.apache.poi poi-ooxml 3.15
2. controller层
package com.cdqd.app.controllers;import com.cdqd.app.common.JsonRet;import com.cdqd.app.exception.BizException;import com.cdqd.app.service.TranslateService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.MultipartHttpServletRequest;import org.springframework.web.multipart.MultipartRequest;import javax.servlet.http.HttpServletRequest;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.util.Objects;/** * @description: * @author: Rosa * @create: 2018-12-24 10:06 **/@RestController@RequestMapping("translate")public class TranslateController { @Autowired private TranslateService translateService; @PostMapping("source/name") public JsonRet translateSourceName(HttpServletRequest request) throws Exception{ MultipartRequest multipartHttpServletRequest = (MultipartRequest) request; MultipartFile multipartFile = multipartHttpServletRequest.getFile("file"); //如果为空，抛出异常，此处为自定义异常，如有需要请改写 if (Objects.requireNonNull(multipartFile).isEmpty()){ throw new BizException(4001); } //存储临时文件 String fileName = multipartFile.getOriginalFilename(); String prefix = fileName.substring(Objects.requireNonNull(fileName).lastIndexOf(".")); File file = File.createTempFile(System.currentTimeMillis() + "", prefix); multipartFile.transferTo(file); //调用service对流进行具体操作 translateService.translateSourceDatabase(new FileInputStream(file)); return JsonRet.buildRet(0);// }} 3. service层（给自己回忆用的，针对性强，读者没有必要往下看了）
接口： TranslateService.java
package com.cdqd.app.service;import java.io.InputStream;public interface TranslateService { void translateSourceDatabase(InputStream inputStream);} 实现文件： TranslateServiceImpl.java
package com.cdqd.app.service.impl;import com.cdqd.app.common.StringUtil;import com.cdqd.app.entity.DatabaseEntity;import com.cdqd.app.entity.TableEntity;import com.cdqd.app.exception.BizException;import com.cdqd.app.mapper.ColumnMapper;import com.cdqd.app.mapper.DatabaseMapper;import com.cdqd.app.mapper.TableMapper;import com.cdqd.app.model.Column;import com.cdqd.app.model.Database;import com.cdqd.app.model.Table;import com.cdqd.app.service.TableService;import com.cdqd.app.service.TranslateService;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/** * @description: * @author: Rosa * @create: 2018-12-24 10:04 **/@Servicepublic class TranslateServiceImpl implements TranslateService { @Autowired private DatabaseMapper databaseMapper; @Autowired private TableMapper tableMapper; @Autowired private ColumnMapper columnMapper; @Autowired private TableService tableService; @Override public void translateSourceDatabase(InputStream inputStream) { Map > databaseMap = getTransMap(inputStream); //遍历所有库 if (databaseMap != null) { for (String databaseEnName : databaseMap.keySet()) { Database database = new Database(); database.setDatabaseEnName(databaseEnName); //如果库存在则开始遍历表 if (databaseMapper.selectOne(database) != null) { Integer databaseId = databaseMapper.selectOne(database).getDatabaseId(); //MapKey("tableEnName") Map dbTableMap = tableService.getTableEntityMap(databaseId); // <表英文名， 表实体> Map excelTableMap = databaseMap.get(databaseEnName); //遍历数据库map的表实体 for (String dbTableEnName : dbTableMap.keySet()) { TableEntity dbTableEntity = dbTableMap.get(dbTableEnName); //如果excel数据中有这边的表 if (excelTableMap.containsKey(dbTableEnName)) { TableEntity excelTableEntity = excelTableMap.get(dbTableEnName); //源表表名翻译 if (!StringUtil.isEmpty(excelTableEntity.getTableCnName())) { Table table = new Table(); table.setTableId(dbTableEntity.getTableId()); table.setTableCnName(excelTableEntity.getTableCnName()); tableMapper.updateByPrimaryKeySelective(table); } //如果列不为空的话 if (dbTableEntity.getColumnList() != null && excelTableEntity.getColumnMap() != null) { // <列英文名， 列实体> Map excelColumnMap = excelTableEntity.getColumnMap(); for (Column dbColumn : dbTableEntity.getColumnList()) { if (excelColumnMap.containsKey(dbColumn.getColumnEnName())) { String columnCnName = excelColumnMap.get(dbColumn.getColumnEnName()).getColumnCnName(); if (!StringUtil.isEmpty(columnCnName)) { //如果列中文名不为空，翻译列名 Column column = new Column(); column.setColumnId(dbColumn.getColumnId()); column.setColumnCnName(columnCnName); columnMapper.updateByPrimaryKeySelective(column); } } } } } } } } }// return TRANS_MAP; } private Map > getTransMap(InputStream inputStream) { Map > databaseMap = new HashMap<>(); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); for (int row = 1; row < sheet.getLastRowNum(); row++) { XSSFRow xssfRow = sheet.getRow(row); if (xssfRow.getCell(0) == null) { continue; } String databaseName = String.valueOf(xssfRow.getCell(0)); String tableEnName = String.valueOf(xssfRow.getCell(1)); String tableCnName = String.valueOf(xssfRow.getCell(2)); String columnEnName = String.valueOf(xssfRow.getCell(3)); String columnCnName = String.valueOf(xssfRow.getCell(4)); Column column = new Column(); column.setColumnEnName(columnEnName); column.setColumnCnName(columnCnName); if (tableEnName == null) { continue; } if (databaseMap.containsKey(databaseName)) { // <表英文名， 表实体> Map excelTableMap = databaseMap.get(databaseName); //如果表存在，获取那个表实体 if (excelTableMap.containsKey(tableEnName)) { TableEntity excelTableEntity = excelTableMap.get(tableEnName); if (columnEnName != null && !columnEnName.equals("")) { if (excelTableEntity.getColumnMap() != null) { Map columnMap = excelTableEntity.getColumnMap(); columnMap.put(columnEnName, column); excelTableEntity.setColumnMap(columnMap); } else { Map columnMap = new HashMap<>(); columnMap.put(columnEnName, column); excelTableEntity.setColumnMap(columnMap); } } excelTableMap.put(tableEnName, excelTableEntity); } else { //表不存在时插入表 TableEntity excelTableEntity = new TableEntity(); excelTableEntity.setTableEnName(tableEnName); excelTableEntity.setTableCnName(tableCnName); if (columnEnName != null && !columnEnName.equals("")) { Map columnMap = new HashMap<>(); columnMap.put(columnEnName, column); excelTableEntity.setColumnMap(columnMap); } excelTableMap.put(tableEnName, excelTableEntity); } } else { Map excelTableMap = new HashMap<>(); TableEntity excelTableEntity = new TableEntity(); excelTableEntity.setTableEnName(tableEnName); excelTableEntity.setTableCnName(tableCnName); if (columnEnName != null && !columnEnName.equals("")) { Map columnMap = new HashMap<>(); columnMap.put(columnEnName, column); excelTableEntity.setColumnMap(columnMap); } excelTableMap.put(tableEnName, excelTableEntity); databaseMap.put(databaseName, excelTableMap); } } return databaseMap; } catch (Exception e) { e.printStackTrace(); } return null; }}