SpringBoot+Mybatis+Vue ElementUI+POI 实现Excel数据的导入导出

2、JS代码：

二、后台代码

  1、目录结构：

  2、依赖jar包：

    org.apache.poi
    poi
    3.16


    org.apache.poi
    poi-ooxml
    3.14



    commons-fileupload
    commons-fileupload
    1.3.1


    commons-io
    commons-io
    2.4

3、配置文件：

server:
  port: 8080
  servlet:
    context-path: /elementui
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mvc?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
    maxActive: 20
    initialSize: 5
    minIdle: 3
    maxWait: 10000

mybatis:
  type-aliases-package: com.vue.elementui.entity

 4、实体类：

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable {
    private Integer bookId;
    private String bookName;
    private String bookAuthor;
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
    private Date bookDate;
    private Double bookPrice;
}

5、SQL语句生成器类：

public class BookSQLProvider {
    public String selectSQL(){ //查询所有
        return new SQL() {
            {
                SELECT("*");
                FROM("ssm_book");
            }
        }.toString();
    }

    public String findByIdSQL(Integer id){ //按Id查询
        return new SQL(){
            {
                SELECT("*");
                FROM("ssm_book");
                WHERE("book_id=#{id}");
            }
        }.toString();
    }

    public String insertSQL(Book book){ //插入
        return new SQL(){
            {
                INSERT_INTO("ssm_book");
                VALUES("book_name","#{bookName}");
                VALUES("book_author","#{bookAuthor}");
                VALUES("book_date","#{bookDate}");
                VALUES("book_price","#{bookPrice}");
            }
        }.toString();
    }

    public String deleteSQL(Integer id){ //删除
        return new SQL(){
            {
                DELETE_FROM("ssm_book");
                WHERE("book_id=#{id}");
            }
        }.toString();
    }

    public String updateSQL(Book book){ //更新
        return new SQL(){
            {
                UPDATE("ssm_book");
                SET("book_name=#{bookName}");
                SET("book_author=#{bookAuthor}");
                SET("book_date=#{bookDate}");
                SET("book_price=#{bookPrice}");
                WHERE("book_id=#{bookId}");
            }
        }.toString();
    }

    public String pageListSQL(Integer start,Integer count){ //分页查询
        return new SQL(){
            {
                SELECT("*");
                FROM("ssm_book");
                LIMIT("#{start},#{count}");
            }
        }.toString();
    }
}

6、代理(Mapper):

@Mapper
@Repository
public interface IBookMapper {

    @SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "selectSQL")
    @Results(id = "bookMap",value = {
            @Result(id = true,property = "bookId",column = "book_id"),
            @Result(property = "bookName",column = "book_name"),
            @Result(property = "bookAuthor",column = "book_author"),
            @Result(property = "bookDate",column = "book_date"),
            @Result(property = "bookPrice",column = "book_price")
    })
    public List findAll();

    @SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "findByIdSQL")
    @ResultMap(value = "bookMap")
    public Book findById(Integer id);

    @SelectProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "pageListSQL")
    @ResultMap(value = "bookMap")
    public List findPageBook(Integer start,Integer count);

    @InsertProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method ="insertSQL" )
    public int insert(Book book);

    @DeleteProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method ="deleteSQL")
    public int delete(Integer id);

    @UpdateProvider(type = com.vue.elementui.provider.BookSQLProvider.class,method = "updateSQL")
    public int update(Book book);
}

7、服务层接口：

public interface IBookService {
    public int addBook(Book book); //插入
    public int removeBook(Integer id);//删除
    public int modifyBook(Book book);//更新
    public List getBooks();//查询所有
    public Book findBookById(Integer id);//按Id查询
    public List getPageBook(Integer start,Integer count);//分页查询
    public boolean batchImport(String fileName, MultipartFile file) throws Exception;//导入Excel
    public HSSFWorkbook exportToExcel(List books);//导出到Excel
}

8、服务层实现类：

@Service
@Transactional
public class BookService implements IBookService {
    @Autowired
    private IBookMapper bookMapper;
    @Override
    public int addBook(Book book) {
        return bookMapper.insert(book);
    }
    @Override
    public int removeBook(Integer id) {
        return bookMapper.delete(id);
    }
    @Override
    public int modifyBook(Book book) {
        return bookMapper.update(book);
    }
    @Override
    public List getBooks() {
        return bookMapper.findAll();
    }
    @Override
    public Book findBookById(Integer id) {
        return bookMapper.findById(id);
    }
    @Override
    public List getPageBook(Integer start, Integer count) {
        return bookMapper.findPageBook(start,count);
    }
    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        boolean notnull = false;
        List bookList = new ArrayList<>();
        if(!fileName.matches("^.+\\.(?i)(xlsx)$")){ //
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2007 = true;
        if(fileName.matches("^.+\\.(?i)(xls)$")){
            isExcel2007 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2007) {
            wb = new HSSFWorkbook(is);
        }else{
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet != null){
            notnull = true;
        }
        Book book = null;
        for(int r=2;r<=sheet.getLastRowNum();r++){ //r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            Row row = sheet.getRow(r);//通过sheet表单对象得到行对象
            if(row == null){
                continue;
            }
        //sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException
        book = new Book();
             row.getCell(0).setCellType(CellType.STRING);//将每一行第一个单元格设置为字符串类型
             String bId =row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
            if(bId == null || bId.isEmpty()){
                throw new Exception("导入失败(第\"+(r+1)+\"行，图书编号未填写)");
            }
            Integer bookId = Integer.parseInt(bId);

            String bookName = row.getCell(1).getStringCellValue();//得到每一行的第二个单元格的值
            if(bookName == null || bookName.isEmpty()){
                throw new Exception("导入失败(第\"+(r+1)+\"行，图书名称未填写)");
            }
            String bookAuthor = row.getCell(2).getStringCellValue();//得到每一行的第二个单元格的值
            if(bookAuthor == null || bookAuthor.isEmpty()){
                throw new Exception("导入失败(第\"+(r+1)+\"行，图书作者未填写)");
            }
             row.getCell(3).setCellType(CellType.STRING);
             Date bookDate =DateConvert.StringToDate(row.getCell(3).getStringCellValue());//得到每一行的第三个单元格的值(日期型)
            if(bookDate == null){
                throw new Exception("导入失败(第\"+(r+1)+\"行，图书发行日期未填写)");
            }
            row.getCell(4).setCellType(CellType.STRING);//将每一行第四个单元格设置为字符串类型
            String bPrice =row.getCell(4).getStringCellValue();//得到每一行的第四个单元格的值(日期型)
            if(bPrice == null || bPrice.isEmpty()){
                throw new Exception("导入失败(第\"+(r+1)+\"行，图书价格未填写)");
            }
            Double bookPrice = Double.parseDouble(bPrice);

            //完整的循环一次 就组成了一个对象
            book.setBookId(bookId);
            book.setBookName(bookName);
            book.setBookAuthor(bookAuthor);
            book.setBookDate(bookDate);
            book.setBookPrice(bookPrice);

            bookList.add(book);
        }
        for(Book bookResord : bookList){
            int id = bookResord.getBookId();
            Book b1 = bookMapper.findById(id);
            if(b1 == null){
                bookMapper.insert(bookResord);
                System.out.println("==>插入："+bookResord);
            }else{
                bookMapper.update(bookResord);
                System.out.println("==>更新："+bookResord);
            }
        }
        return notnull;
    }

    @Override
    public HSSFWorkbook exportToExcel(List books) { //将集合中的数据存储到Execl工作簿中
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("book_info");

        HSSFRow row = null;
        row = sheet.createRow(0);//创建第一行
        row.setHeight((short) 800);// 设置行高
        HSSFCell c00 = row.createCell(0); //创建第一个单元格
        c00.setCellValue("图书列表");//设置单元格内容

//设置标题样式
        c00.setCellStyle(ExcelImportUtils.createTitleCellStyle(wb));

//合并单元格(firstRow:起始行,lastRow:结束行,firstCol:起始列,lastCol:结束列)
        CellRangeAddress rowRegion = new CellRangeAddress(0,0,0,4);
        sheet.addMergedRegion(rowRegion);

        //创建表头行，并设置样式
        row = sheet.createRow(1); //创建第二行
        row.setHeight((short)500);//设置行高
        String[] row_head = {"图书编号","图书名称","图书作者","发行时间","图书单价"};
        for(int i=0;i创建表头
            HSSFCell tempCell = row.createCell(i);
            tempCell.setCellValue(row_head[i]); //设置单元格内容

//设置表头样式
           tempCell.setCellStyle(ExcelImportUtils.createHeadCellStyle(wb));
        }

        //定义表格内容(每行数据)

//集合(books)中有多少个元素就生成多少行
        for(int i=0;i

row = sheet.createRow(i + 2);
            Book book = books.get(i);
            for (int j = 0; j < 5; j++) { //每行有5列
                HSSFCell tempCell = row.createCell(j); //设置单元格内容
                //设置内容样式
tempCell.setCellStyle(ExcelImportUtils.createContentCellStyle(wb)); 

 if (j == 0) {
                    tempCell.setCellValue(book.getBookId());
                } else if (j == 1) {
                    tempCell.setCellValue(book.getBookName());
                } else if (j == 2) {
                    tempCell.setCellValue(book.getBookAuthor());
                } else if (j == 3) {
                    tempCell.setCellValue(DateConvert.DateToString(book.getBookDate()));
                } else if (j == 4) {
                    tempCell.setCellValue(book.getBookPrice());
                }
            }
        }
        // sheet.setDefaultRowHeight((short)(16.5*20)); 设置默认行高
        //列宽自适应
        for(int i=0;i<5;i++){
            sheet.autoSizeColumn(i);
        }
        return wb;
    }
}

9、工具类：

  (1)日期转换：

public class DateConvert {
    public static Date StringToDate(String str){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = sdf.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

    public static String DateToString(Date date){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.format(date);
    }
}

   (2)分页类：

public class Pagination {
    public Integer currentPage; //当前页
    public Integer pageSize; //每页显示的记录数
    public Integer recordTotal;//记录总数
    public Integer pageCount;//总页数
    public List pageList;//分页数据

    public Pagination() {
        this.currentPage = 1;
        this.pageSize = 5;
    }

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getRecordTotal() {
        return recordTotal;
    }

    public void setRecordTotal(Integer recordTotal) {
        this.recordTotal = recordTotal;
        if(this.recordTotal % this.pageSize ==0){
            this.pageCount = this.recordTotal/this.pageSize;
        }else{
            this.pageCount = this.recordTotal/this.pageSize + 1;
        }
    }
    public List getPageList() {
        return pageList;
    }

    public void setPageList(List pageList) {
        this.pageList = pageList;
    }
}

(3)Excel导入导出工具类：

public class ExcelImportUtils {
    //@描述：判断是否是2003版的excel,返回true是2003
    public static boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    //@描述：判断是否是2007版的Excel，返回true是2007
    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
    /*
      @描述：验证excel文件
      @param:filePath
      @return
     */
    public static boolean validateExcel(String filePath){
        if(filePath == null ||!(isExcel2003(filePath))||!(isExcel2007(filePath))){
            return false;
        }else{
            return true;
        }
    }
    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
     //背景颜色   cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

        HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
      //背景颜色  cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        HSSFFont font = wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }
}

10、控制器类：

@CrossOrigin
@RestController
public class BookController {
    @Autowired
    private IBookService bookService;
    private Pagination pagination = new Pagination<>();
    //private Integer records = bookService.getBooks().size();

    @GetMapping("/books")
    public String getBooks(String CurrentPage,String PageSize){
        pagination.setRecordTotal(bookService.getBooks().size());
        int current_page = Integer.parseInt(CurrentPage);
        int page_size = Integer.parseInt(PageSize);
        pagination.setCurrentPage(current_page);
        pagination.setPageSize(page_size);

        int start = pagination.getCurrentPage()*pagination.getPageSize()-pagination.getPageSize();
        pagination.setPageList(bookService.getPageBook(start,pagination.getPageSize()));
        ObjectMapper objectMapper = new ObjectMapper();
        String pagebooks = null;

        try {
            pagebooks = objectMapper.writeValueAsString(pagination);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
         return pagebooks;
    }
    @GetMapping("/insert")
    public boolean insertBook(HttpServletRequest request){
        String name = request.getParameter("bookName");
        String author = request.getParameter("bookAuthor");
        Date date = DateConvert.StringToDate(request.getParameter("bookDate"));
        Double price = Double.parseDouble(request.getParameter("bookPrice"));
        Book book = new Book(null,name,author,date,price);
        int flag = bookService.addBook(book);
        return flag>0;
    }
    @GetMapping("/delete")
    public boolean deleteBook(Integer bookId){
        int flag = bookService.removeBook(bookId);
        return  flag>0;
    }
    @GetMapping("/batchDelete")
    public boolean batchDeleteBook(String ids){
        String[] str = ids.split(",");
        int flag = 0;
        for(int i=0;i            int id = Integer.parseInt(str[i]);
            flag = bookService.removeBook(id);
        }
        return flag>0;
    }
    @GetMapping("/update")
    public boolean updateBook(HttpServletRequest request){
        Integer id = Integer.parseInt(request.getParameter("bookId"));
        String name = request.getParameter("bookName");
        String author = request.getParameter("bookAuthor");
        Date date = DateConvert.StringToDate(request.getParameter("bookDate"));
        Double price = Double.parseDouble(request.getParameter("bookPrice"));

        Book book = new Book(id,name,author,date,price);
        int flag = bookService.modifyBook(book);
        return  flag>0;
    }
    @PostMapping("/import")
    public boolean exImport(@RequestParam("file") MultipartFile file){
        boolean a = false;
        String fileName = file.getOriginalFilename();
        System.out.println(fileName);
        try {
            a = bookService.batchImport(fileName,file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(a);
        return a;
    }
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        List books = bookService.getBooks();
        HSSFWorkbook wb = bookService.exportToExcel(books);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream os = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=book.xlsx"); //默认Excel名称
        wb.write(os);
        os.flush();
        os.close();
    }
}