前言
用户上传excel表格，后端读取excel中的数据并存入数据库。
这里只演示如何读取到excel表格中的内容，excel中包括图片、日期、字符串格式。
导入依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.6</version></dependency><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3.3</version></dependency>
excel表格样式
实体类
public class User {private Integer id;private String name;private Integer age;private String sex;private String picPath;private String date;public User() {}}
读取表格工具类
import com.csv.pojo.User;import org.apache.poi.POIXMLDocumentPart;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.PictureData;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.*;import org.openxmlformats.schemas.drawingml.x.spreadsheetDrawing.CTMarker;import java.io.*;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** Excel工具类*/public class ExcelUtil {/*** 判断文件是否存在** @param fileName* @return*/public static boolean fileExist(String fileName) {boolean flag = false;File file = new File(fileName);flag = file.exists();return flag;}/*** 新文件写入数据** @return*/public static List<User> writeNewFile() {List<User> userList = new ArrayList<>();User user;for (int i = 0; i < 10; i++) {user = new User();user.setName("u" + i);user.setAge(i);user.setSex("男");userList.add(user);}return userList;}/*** 从excel中读取数据** @param xls true xls文件，false xlsx文件* @param inputStream 文件输入流* @return 数据封装到对象*/public static List<User> getDataFromExcel(boolean xls, InputStream inputStream) {SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");Workbook workbook = null;List<User> userList = new ArrayList<>();try {if (xls) {workbook = new HSSFWorkbook(inputStream);} else {workbook = new XSSFWorkbook(inputStream);}// 得到一个工作表Sheet sheet = workbook.getSheetAt(0);// 得到表头Row rowHead = sheet.getRow(0);// 判断表头是否正确if (rowHead.getPhysicalNumberOfCells() < 1) {throw new Exception("表头错误");}// 获取照片Map<String, PictureData> pictures;if (xls) {pictures = getPictures((HSSFSheet) sheet);} else {pictures = getPictures((XSSFSheet) sheet);}// 获取数据for (int i = 2; i <= sheet.getLastRowNum(); i++) {// 获取第i行Row row = sheet.getRow(i);// 获取第i行各个列的数据Integer id = (int) row.getCell(0).getNumericCellValue();String username = row.getCell(1).getStringCellValue();Integer age = (int) row.getCell(2).getNumericCellValue();String sex = row.getCell(3).getStringCellValue();String savePath = savePicture(id, pictures.get(i + "-4"));String date;// 判断该单元格的数据类型switch (row.getCell(5).getCellTypeEnum()) {case STRING: // 字符串类型date = row.getCell(5).getStringCellValue();break;case NUMERIC: // 日期类型date = sf.format(row.getCell(5).getDateCellValue());break;default:date = "";break;}userList.add(new User(id, username, age, sex, savePath, date));}} catch (Exception e) {e.printStackTrace();}return userList;}/*** xls获取照片*/private static Map<String, PictureData> getPictures(HSSFSheet sheet) {Map<String, PictureData> map = new HashMap<>();List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();for (HSSFShape shape : list) {if (shape instanceof HSSFPicture) {HSSFPicture picture = (HSSFPicture) shape;HSSFClientAnchor clientAnchor = picture.getClientAnchor();HSSFPictureData pictureData = picture.getPictureData();String key = clientAnchor.getRow1() + "-" + clientAnchor.getCol1();map.put(key, pictureData);}}return map;}/*** xlsx获取照片*/private static Map<String, PictureData> getPictures(XSSFSheet sheet) {Map<String, PictureData> map = new HashMap<>();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 String savePicture(int id, PictureData picData) throws IOException {if (picData != null) {byte[] data = picData.getData();String fileName = id + "-" + "照片";String filePath = "D:\\JavaCode\\testmaven14excel\\image\\";FileOutputStream out = new FileOutputStream(filePath + fileName + ".png");out.write(data);out.close();return filePath + fileName + ".png";}return "";}}
控制层接口
@RestController@RequestMapping("/excel")@CrossOrigin(maxAge = 3600)public class ExcelController {@RequestMapping(value = "/upload",method = RequestMethod.POST)public ResultMessage readFile(@RequestParam(value = "excelFile") MultipartFile excelFile, HttpServletRequest request) throws IOException {// 文件名String name = excelFile.getOriginalFilename();boolean b;if (name.endsWith(".xls")) {b=true;}else if(name.endsWith(".xlsx")){b=false;}else if(name.endsWith(".jpg")||name.endsWith(".png")){System.out.println("收到图片文件"+name);return new ResultMessage(true,"这是图片",null);}else {return new ResultMessage(false,"不是excel文件",null);}List<User> dataFromExcel = ExcelUtil.getDataFromExcel(b,excelFile.getInputStream());for (User user : dataFromExcel) {System.out.println(user.toString());}return new ResultMessage(true,"解析文件",dataFromExcel);}}
效果演示
{"id": 1,"name": "瑞秋","age": 25,"sex": "女","picPath": "D:\\JavaCode\\testmaven14excel\\image\\1-照片.png","date": "-02-18"},{"id": 2,"name": "莫妮卡","age": 26,"sex": "女","picPath": "D:\\JavaCode\\testmaven14excel\\image\\2-照片.png","date": "-02-19"},{"id": 3,"name": "乔伊","age": 24,"sex": "男","picPath": "","date": "-02-20"},{"id": 4,"name": "钱德勒","age": 26,"sex": "男","picPath": "","date": "/2/21"},{"id": 5,"name": "罗斯","age": 27,"sex": "男","picPath": "","date": "/2/22"},{"id": 6,"name": "菲比","age": 25,"sex": "女","picPath": "","date": "-02-23"}
