java使用poi读取excel表格

前言

用户上传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;
    private String department;


    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.x2006.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;
                }
                String department = "";
                if (isMergedRegion(sheet,i,6)) {
                    department = getMergedRegionValue(sheet,i,6);
                }else {
                    department = row.getCell(6).getStringCellValue();
                }

                userList.add(new User(id, username, age, sex, savePath, date, department));
                
            }

        } 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 "";
    }

    /**
     * 获取合并单元格的值
     * @param sheet 表格
     * @param row 行
     * @param column 列
     */
    private static String getMergedRegionValue(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++){
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row>=firstRow&&row<=lastRow) {
                if (column>=firstColumn&&column<=lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return fCell.getStringCellValue();
                }
            }
        }
        return "";
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet 表格
     * @param row 行下标
     * @param column 列下标
     */
    private static boolean isMergedRegion(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row>=firstRow&&row<=lastRow) {
                if (column>=firstColumn&&column<=lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
}

控制层接口

@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": "2022-02-18",
	"department": "3601"
}, {
	"id": 2,
	"name": "莫妮卡",
	"age": 26,
	"sex": "女",
	"picPath": "D:\\JavaCode\\testmaven14excel\\image\\2-照片.png",
	"date": "2022-02-19",
	"department": "3601"
}, {
	"id": 3,
	"name": "乔伊",
	"age": 24,
	"sex": "男",
	"picPath": "",
	"date": "2022-02-20",
	"department": "3602"
}, {
	"id": 4,
	"name": "钱德勒",
	"age": 26,
	"sex": "男",
	"picPath": "",
	"date": "2022/2/21",
	"department": "3602"
}, {
	"id": 5,
	"name": "罗斯",
	"age": 27,
	"sex": "男",
	"picPath": "",
	"date": "2022/2/22",
	"department": ""
}, {
	"id": 6,
	"name": "菲比",
	"age": 25,
	"sex": "女",
	"picPath": "",
	"date": "2022-02-23",
	"department": ""
}]