杨校老师课堂之POI导入数据

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

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

package com.example.springbootmybatis;

import com.example.springbootmybatis.entity.Book;
import com.example.springbootmybatis.entity.User;
import com.example.springbootmybatis.service.BookService;
import com.example.springbootmybatis.service.UserService;
import org.apache.poi.ss.usermodel.*;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@SpringBootTest
@RunWith(SpringRunner.class)
public class SpringbootMybatisApplicationTests {

    @Resource
    private BookService bookService;
    // 实例化集合，用于存储读取的excel数据
    List<Book> bookList = new ArrayList<>();

    @Test
    public void contextLoads() throws IOException {
        //  1. 手写创建 File  ||  稍后记得导入  commons-io 、commons-fileupload
        File file = new File("C:\\Users\\Mryang\\Desktop\\book.xlsx");
        //  2.创建工作簿
        Workbook workbook = WorkbookFactory.create(file);

        int sheets = workbook.getNumberOfSheets();

        System.out.println("该文件有"+sheets+"表");

        Sheet sheet = workbook.getSheetAt(0);

        System.out.println(sheet.getSheetName());

        int rows = sheet.getPhysicalNumberOfRows();//getPhysicalNumberOfRows 获取所有行数
        System.out.println("行数是" + rows);

        //  循环所有的行数
        for (int j = 0; j < rows; j++) {

            // 判断是不是第一行标题 是不是表头？
            if (sheet.getFirstRowNum() == j){
                //  第一行不要了
                continue;
            }
            Row row = sheet.getRow(j);
            int cells = row.getPhysicalNumberOfCells();//getPhysicalNumberOfCells 获取所有列数

            Book book = new Book();// 创建10个呀

            for (int i = 0; i < cells; i++) {
                switch (i){
                    case 0 ://第1列
                        row.getCell(i).setCellType(CellType.STRING);//  设置单元格的类型字符串类型
                        int bookId  = Integer.parseInt(row.getCell(i).getStringCellValue());//转换为整数
                        book.setBookId(bookId);
                        break;
                    case 1 ://第2列
                        row.getCell(i).setCellType(CellType.STRING);
                        book.setBookName(row.getCell(i).getStringCellValue());
                        break;
                    case 2 ://第3列
                        row.getCell(i).setCellType(CellType.STRING);
                        book.setBookPrice(Double.parseDouble(row.getCell(i).getStringCellValue()));
                        break;
                    case 3 ://第4列
                        row.getCell(i).setCellType(CellType.STRING);
                        book.setBookPublish(row.getCell(i).getStringCellValue());
                        break;
                }
            }
            bookList.add(book);
        }
        //System.out.println("bookList = " + bookList);//检验是否将配置文件内的书信息存储到集合中

        for (Book book : bookList) {
          //System.out.println(book);
            bookService.addBook(book);
          
        }
    }
}