spring boot如何使用POI讀取Excel文件

更新時(shí)間：2021年08月20日 10:13:43 作者：代碼貓

本文主要介紹使用POI進(jìn)行Excel文件的相關(guān)操作，涉及讀取文件，獲取sheet表格，對(duì)單元格內(nèi)容進(jìn)行讀寫操作，以及合并單元格的操作

spring boot 使用POI讀取Excel文件

Excel文件目錄

Excel模板文件存了resourse目錄下，如下圖：

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

重要說(shuō)明

如果是xls格式，使用HSSFWorkbook，HSSFSheet，HSSFRow來(lái)進(jìn)行相關(guān)操作

如果是xlsx格式，使用XSSFWorkbook，XSSFSheet，XSSFRow來(lái)進(jìn)行相關(guān)操作

讀取Excel文件

// 定義一個(gè)數(shù)據(jù)格式化對(duì)象
XSSFWorkbook wb = null;
try {
    //excel模板路徑
    File cfgFile = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "static/ExcelTemplate/ContradictionMatrix.xlsx");
    InputStream in = new FileInputStream(cfgFile);
    //讀取excel模板
    wb = new XSSFWorkbook(in);
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

獲取sheet表格及讀寫單元格內(nèi)容

//獲取sheet表格，及讀取單元格內(nèi)容
XSSFSheet sheet = null;
try{
    sheet = wb.getSheetAt(0);
    //先將獲取的單元格設(shè)置為String類型，下面使用getStringCellValue獲取單元格內(nèi)容
    //如果不設(shè)置為String類型，如果單元格是數(shù)字，則報(bào)如下異常
    //java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
    sheet.getRow(2).getCell(2).setCellType(CellType.STRING);
    //讀取單元格內(nèi)容
    String cellValue = sheet.getRow(2).getCell(2).getStringCellValue();
    
    //添加一行
    XSSFRow row = sheet.createRow(1); //第2行開(kāi)始寫數(shù)據(jù)
    row.setHeight((short)400); //設(shè)置行高
    //向單元格寫數(shù)據(jù)
    row.createCell(1).setCellValue("名稱");
}
catch (Exception e){
    e.printStackTrace();
}

合并單元格

使用下面的語(yǔ)句合并單元格：

sheet.addMergedRegion(new CellRangeAddress(0,2,15,18));

看一下CellRangeAddress的構(gòu)造函數(shù)：

/**
 * Creates new cell range. Indexes are zero-based.
 * 
 * @param firstRow Index of first row
 * @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
 * @param firstCol Index of first column
 * @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
 */
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
    super(firstRow, lastRow, firstCol, lastCol);
    
    if (lastRow < firstRow || lastCol < firstCol)
        throw new IllegalArgumentException("lastRow < firstRow || lastCol < firstCol");
}

SpringBoot解析Excel

現(xiàn)在很多web應(yīng)用中，導(dǎo)入excel導(dǎo)出excel很常見(jiàn)，這篇文章就講講導(dǎo)入excel文件。

以批量導(dǎo)入課程為例

首先加入需要的jar包

<!--解析excel-->
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>RELEASE</version>
  </dependency>
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>RELEASE</version>
  </dependency>

數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表course

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `course_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '課程id',
  `course_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '課程代碼',
  `course_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '課程名稱',
  `teacher_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '教師id',
  `course_time` date NOT NULL DEFAULT '1996-01-01' COMMENT '開(kāi)課時(shí)間',
  `class_room` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '開(kāi)課地點(diǎn)',
  `course_week` int(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '課程學(xué)時(shí)',
  `course_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '必修課' COMMENT '課程類型',
  `college_id` int(11) UNSIGNED NOT NULL COMMENT '所屬院系id',
  `score` int(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '學(xué)分',
  `is_on` tinyint(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否開(kāi)啟了選課，默認(rèn)0未開(kāi)啟',
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '課程表' ROW_FORMAT = Dynamic;

新建一個(gè)ExcelUtil.java

/**
 * excel工具類
 */
public class ExcelUtils {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    /**
     * 課程excel
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List getCourseListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList<>();
        // 創(chuàng)建excel工作簿
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("創(chuàng)建Excel工作薄為空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet == null) {
                continue;
            }
            // 濾過(guò)第一行標(biāo)題
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }
                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    // 日期類型轉(zhuǎn)換
                    if(y == 3) {
                        //cell.setCellType(CellType.STRING);
                        double s1 = cell.getNumericCellValue();
                        Date date = HSSFDateUtil.getJavaDate(s1);
                        li.add(date);
                        continue;
                    }
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }
    /**
     * 判斷文件格式
     * @param in
     * @param fileName
     * @return
     */
    private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook book = null;
        String filetype = fileName.substring(fileName.lastIndexOf("."));
        if(".xls".equals(filetype)) {
            book = new HSSFWorkbook(in);
        } else if (".xlsx".equals(filetype)) {
            book = new XSSFWorkbook(in);
        } else {
            throw new Exception("請(qǐng)上傳excel文件！");
        }
        return book;
    }
}

這里主要注意一下上面的日期轉(zhuǎn)換，在excel中的日期，通過(guò)Java讀出來(lái)之后，變成了26 四月 2019這樣的形式，而數(shù)據(jù)庫(kù)中我們的字段類型為date，所以總是插入失敗。

上面我的寫法直接是知道那個(gè)字段是Date類型，所以直接使用y==3，這樣寫可復(fù)用性很差。

接下來(lái)直接看和數(shù)據(jù)庫(kù)交互的邏輯代碼

   /**
     * 通過(guò)excel文件，批量增加課程
     * @param request
     * @return
     * @throws Exception
     */
    @PostMapping("/upload/course")
    public String uploadCourseExcel(HttpServletRequest request) {
        MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartHttpServletRequest.getFile("courseFile");
        if(file.isEmpty()) {
            return "redirect:/admin/course/list";
        }
        try {
            InputStream inputStream = file.getInputStream();
            List<List<Object>> list = ExcelUtils.getCourseListByExcel(inputStream, file.getOriginalFilename());
            inputStream.close();
            for (int i = 0; i < list.size(); i++) {
                List<Object> courseList = list.get(i);
                Course course = new Course();
                course.setCourseCode(courseList.get(0).toString());
                course.setCourseName(courseList.get(1).toString());
                // 通過(guò)教師姓名查教師id
                String teacherId = teacherService.getTeacByName(courseList.get(2).toString());
                // 教師信息錯(cuò)誤，直接跳過(guò)這條記錄
                if(teacherId == null) {
                    continue;
                }
                course.setTeacherId(teacherId);
                // 格式化時(shí)間
                Date date = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US).parse(courseList.get(3).toString());
                course.setCourseTime(new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(date)));
                course.setClassRoom(courseList.get(4).toString());
                course.setCourseWeek(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(5).toString()))));
                course.setCourseType(courseList.get(6).toString());
                // 通過(guò)院系名稱查詢?cè)合礽d
                Integer collegeId = collegeService.getCollegeByName(courseList.get(7).toString());
                // 院系有誤，直接跳過(guò)這條記錄
                if(collegeId == null || collegeId == 0) {
                    continue;
                }
                course.setCollegeId(collegeId);
                course.setScore(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(8).toString()))));
                // 默認(rèn)不開(kāi)啟選課
                course.setIsOn(0);
                logger.error("course = " + course);
                // 判斷課程是否重復(fù)(同一門課程可以有多個(gè)教師教師course_code, course_name, teacher_id聯(lián)合)
                Integer courseId = null;
                courseId = courseService.getCourseByThree(course);
                // 存在重復(fù)的
                if(courseId != null) {
                    // 跳過(guò)不添加
                    continue;
                }
                // 執(zhí)行插入操作
                courseService.addCourse(course);
            }
        } catch (Exception e) {
            return "redirect:/admin/course/list";
        }
        return "redirect:/admin/course/list";
    }

可以看到，我又對(duì)時(shí)間類型進(jìn)行了處理，才能最終插入數(shù)據(jù)庫(kù)

// 格式化時(shí)間
Date date = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US).parse(courseList.get(3).toString());
course.setCourseTime(new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(date)));

同時(shí)，在excel中的整數(shù)類型，取出來(lái)之后就會(huì)變成Double類型比如5變成5.0，所以我對(duì)此也進(jìn)行了處理

course.setCourseWeek(Integer.parseInt(new DecimalFormat("0").format(Double.parseDouble(courseList.get(5).toString()))));

最后調(diào)用代碼進(jìn)行插入操作。

看看前端代碼

<button class="btn btn-default col-md-2" style="margin-top: 20px">
<a data-toggle="modal" href="#uploadExcel" rel="external nofollow"  role="button" style="color: black; text-decoration: none">
批量添加<sapn class="glyphicon glyphicon-plus"/>
</a>
</button>
<!--批量添加模態(tài)框-->
<div class="modal fade" tabindex="-1" role="dialog" id="uploadExcel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<form class="form-horizontal" role="form" th:action="@{/admin/upload/course}"
enctype="multipart/form-data" method="post">
<div class="modal-body">
請(qǐng)選擇文件:<input type="file" name="courseFile">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">關(guān)閉</button>
<button type="submit" class="btn btn-success">添加</button>
</div>
</form>
</div>
</div>
</div>

這里我通過(guò)button喚醒一個(gè)模態(tài)框來(lái)添加

最后測(cè)試結(jié)果

excel記錄