JAVA开发利用POI实现Excel与word文档的读写操作（excel篇）

SpringBoot整合POI实现Excel表格的读写操作（1）

1. 导入Pom依赖

<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>org.apache.poi</groupId> 
	<artifactId>ooxml-schemas</artifactId> 
	<version>1.1</version>
</dependency>

2. POI写操作

package com.ss.poi.excel;


import com.ss.poi.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

@Service
public class WriteXLS {
    public void exportExcel(List<User> list) {
        /*创建HSSFWorkBook对象*/
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook ();
        /*创建excel表*/
        HSSFSheet sheet = hssfWorkbook.createSheet ();
        /*在当前表中创建行，第一行也就是rowNum为0的行一般为表头行*/
        HSSFRow titleRow = sheet.createRow (0);
        /*给当前行titleRow创建单元格，并使用setCellValue方法赋值。根据实际需求创建。与数据库对应*/
        titleRow.createCell (0).setCellValue ("用户编号");
        titleRow.createCell (1).setCellValue ("用户头像");
        titleRow.createCell (2).setCellValue ("用户姓名");
        titleRow.createCell (3).setCellValue ("用户密码");
        titleRow.createCell (4).setCellValue ("用户手机");
        titleRow.createCell (5).setCellValue ("用户地址");
        titleRow.createCell (6).setCellValue ("用户邮箱");
        titleRow.createCell (7).setCellValue ("用户余额");
        titleRow.createCell (8).setCellValue ("用户支付密码");
        for (User user : list) {
            //获取当前最大的行
            int lastRowNum = sheet.getLastRowNum ();
            //新创建一行操作
            HSSFRow dataRow = sheet.createRow (lastRowNum + 1);
            dataRow.createCell (0).setCellValue (user.getUserId ());
            dataRow.createCell (1).setCellValue (user.getVia ()==null?"":user.getVia ());
            dataRow.createCell (2).setCellValue (user.getUserName ()==null?"":user.getUserName ());
            dataRow.createCell (3).setCellValue (user.getUserPassword ()==null?"":user.getUserPassword ());
            dataRow.createCell (4).setCellValue (user.getPhone ()==null?"":user.getPhone ());
            dataRow.createCell (5).setCellValue (user.getAddress ()==null?"":user.getAddress ());
            dataRow.createCell (6).setCellValue (user.getUserEmail ()==null?"":user.getUserEmail ());
            dataRow.createCell (7).setCellValue (user.getBalance ()==null?"":user.getBalance ());
            dataRow.createCell (8).setCellValue (user.getPayPwd ()==null?"":user.getPayPwd ());
        }
        /*创建输出流及本地存储地址*/
        String fileName = "用户.xls";
        try {
            FileOutputStream fileOut = new FileOutputStream ("D:\\L\\" + fileName);
            /*写出到本地*/
            hssfWorkbook.write (fileOut);
            /*关闭流*/
            hssfWorkbook.close ();
        } catch (FileNotFoundException e) {
            e.printStackTrace ();
        } catch (IOException e) {
            e.printStackTrace ();
        }
    }
}

3. POI读操作

package com.ss.poi.excel;


import com.ss.poi.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class ReadXLS {
    public List<User> importXLS() {
        ArrayList<User> list = new ArrayList<>();
        try {
            String fileName = "用户.xls";
            /*输入流*/
            InputStream inputStream = new FileInputStream("D:\\L\\" + fileName);
            /*提供读写Microsoft Excel格式档案的功能。创建HSSFWorkBook对象*/
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            /*获取HSSFWorkBook对象第一张excel表*/
            HSSFSheet sheetAt = workbook.getSheetAt(0);
            /*遍历每行*/
            for (Row row : sheetAt) {
                /*判断行数是否大于0.第一行表头，不读取第一行的表头内容，也即rowNum=0的行*/
                if (row.getRowNum() == 0) {
                    continue;
                }
                Integer userId = (int) row.getCell(0).getNumericCellValue();
                String via = row.getCell(1).getStringCellValue();
                String userName = row.getCell(2).getStringCellValue();
                String userPassword = row.getCell(3).getStringCellValue();
                String phone = row.getCell(4).getStringCellValue();
                String address = row.getCell(5).getStringCellValue();
                String userEmail = row.getCell(6).getStringCellValue();
                String balance = row.getCell(7).getStringCellValue();
                String payPwd = row.getCell(8).getStringCellValue();
                User user = new User();
                user.setUserId(userId);
                user.setVia(via);
                user.setUserName(userName);
                user.setUserPassword(userPassword);
                user.setPhone(phone);
                user.setAddress(address);
                user.setUserEmail(userEmail);
                user.setBalance(balance);
                user.setPayPwd(payPwd);
                list.add(user);
            }
            /*关闭流*/
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
}

4. 实体

package com.ss.poi.pojo;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Data
@Entity
@Table(name = "user")
public class User {
    @Id
    @Column(name = "user_id")
    private Integer userId;
    private String via;
    @Column(name = "user_name")
    private String userName;
    @Column(name = "user_password")
    private String userPassword;
    private String phone;
    private String address;
    @Column(name = "user_email")
    private String userEmail;
    private String balance;
    @Column(name = "pay_pwd")
    private String payPwd;
}

5. 数据库表
   
6. Controller层接口

package com.ss.poi.controller;


import com.ss.poi.excel.ReadXLS;
import com.ss.poi.excel.WriteXLS;
import com.ss.poi.pojo.User;
import com.ss.poi.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.Mapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class ExcelController {
    @Autowired
    private ReadXLS readXLS;
    @Autowired
    private WriteXLS writeXLS;
    @Autowired
    private UserService userService;

    @GetMapping("/writeExcel")
    public String WriteExcel(){
        List<User> userList = userService.selectAllUser ();
        System.out.println (userList);
        writeXLS.exportExcel (userList);
        return "成功";
    }

    @GetMapping("/readExcel")
    public String ReadExcel(){
        List<User> userList = readXLS.importXLS ();
        System.out.println (userList);
        return "成功";
    }
}

7. 测试及结果
   
   
   8.可能会出现的异常
   IOException、FileNotFoundException。
   注意文件保存路径的位置。关于java利用poi操作Word文档的案例请看下一篇文章。