POI实现excel的导入导出

爱被打了一巴掌 2022-01-07 03:47 404阅读 0赞

一。导入到excel

注：本文是在之前ssm框架基础上引入的POI

1.引入依赖

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

2.编写POIUtil类

package com.exp.utils;
    
    import com.exp.model.User;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.util.List;
    
    
    public class POIUtil {
        
    
        public static void export(List<User> userList) throws Exception{
        
            //指定数据存放的位置
            OutputStream outputStream = new FileOutputStream("D:\\test.xls");
            //1.创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建一个工作表sheet
            HSSFSheet sheet = workbook.createSheet("test");
            //List<User> userList = userService.selectAll();
            //构造参数依次表示起始行，截至行，起始列， 截至列
            CellRangeAddress region=new CellRangeAddress(0, 0, 0, 3);
            sheet.addMergedRegion(region);
    
            HSSFCellStyle style=workbook.createCellStyle();
            //水平居中
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cell = row1.createCell(0);
            //设置值，这里合并单元格后相当于标题
            cell.setCellValue("人员信息表");
            //将样式添加生效
            cell.setCellStyle(style);
    
            for(int i = 0;i<userList.size();i++){
        
                //行
                HSSFRow row = sheet.createRow(i+1);
                //对列赋值
                row.createCell(0).setCellValue(userList.get(i).getId());
                row.createCell(1).setCellValue(userList.get(i).getName());
                row.createCell(2).setCellValue(userList.get(i).getPassword());
                row.createCell(3).setCellValue(userList.get(i).getRemark());
            }
            workbook.write(outputStream);
            outputStream.close();
        }
    
    
    }

在TestController中

![1681041-20190603195112271-747474274.png][]

3.测试

访问：http://localhost:8080/ssm2/test

在D盘会生成一个test.xls文件打开后如下

![1681041-20190612202027376-1844486386.png][]

二。从excel导出到数据库

1.编写POIUtil工具类

public static List<User> importExcel() throws Exception{
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("D:\\t1.xls")));
            HSSFSheet sheet = null;
    
            for(int i = 0;i < workbook.getNumberOfSheets();i++){
                //获取每个sheet
                sheet = workbook.getSheetAt(i);
                List<User> list = new ArrayList<User>();
                //getPhysicalNumberOfRows获取有记录的行数
                for(int j = 0;j < sheet.getPhysicalNumberOfRows();j++){
                    Row row = sheet.getRow(j);
                    if(null!=row){
                        //getLastCellNum获取最后一列
                        User user = new User();
                        for(int k = 0;k < row.getLastCellNum();k++){
                            if(null!=row.getCell(k)){
                                if(k==0){
                                    Cell cell = row.getCell(0);
                                    //cell->double
                                    Double d = cell.getNumericCellValue();
                                    //double->int
                                    int id = new Double(d).intValue();
                                    user.setId(id);
                                }
                                if(k==1){
                                    Cell cell = row.getCell(1);
                                    //cell->string
                                    user.setName(cell.getStringCellValue().toString());
                                }
                                if(k==2){
                                    Cell cell = row.getCell(2);
                                    user.setPassword(cell.getStringCellValue().toString());
                                }
                                if(k==3){
                                    Cell cell = row.getCell(3);
                                    user.setRemark(cell.getStringCellValue().toString());
                                }
                            }
    
                        }
                        list.add(user);
    
    
                    }
    
                }
                System.out.println("读取sheet表："+ workbook.getSheetName(i) + "完成");
                return list;
            }
    
            return null;
        }

在编写工具类的时候遇到的问题总结：

(1)getLastRowNum

如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1；

getPhysicalNumberOfRows

获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；

之前写成getLastRowNum()执行到row.getLastCellNum()=-1，获取不到excel中的值

(2)如何在循环里将值赋值到一个对象中，我就写了个if判断

(3)cell->int和string

2.UserMapper.xml

<insert id="insertData" parameterType="list">
        insert into user(id,name,password,remark) VALUES
        <foreach collection="list" index="index" item="user" separator=",">
          (#{user.id},#{user.name},#{user.password},#{user.remark})
        </foreach>
      </insert>

3.UserMapper和UserService接口一样

int insertData(List<User> users);

4.UserServiceImpl

@Override
        public int insertData(List<User> users) {
            int num = userMapper.insertData(users);
            return num;
        }

5.TestController

@RequestMapping("/insertData")
        public void insertData() throws Exception{
            List<User> users = POIUtil.importExcel();
            int num = userService.insertData(users);
            if(num > 0){
                System.out.println("插入数据成功");
            }
    
        }

excel数据在D:/t1.xls,数据如下

![1681041-20190613215025754-2693711.png][]

6.测试

访问：http://localhost:8080/ssm2/insertData

在数据库中查看如下即成功：

![1681041-20190613215141790-111771338.png][]

注：本文只是简单写了下POI的导入导出功能，当然POI中还有很多用法有待学习。

转载于:https://www.cnblogs.com/xinyijiu/p/10969690.html

[1681041-20190603195112271-747474274.png]: /images/20211227/9e80c2fa63c641b991daa461a3853174.png
[1681041-20190612202027376-1844486386.png]: /images/20211227/1b1f3a59598f4e1db4fa031e28bdddd6.png
[1681041-20190613215025754-2693711.png]: /images/20211227/fe3a80a569574fe8bc667809f02e298b.png
[1681041-20190613215141790-111771338.png]: /images/20211227/7cfd7b9fd83944a08619f9a67df1d047.png