POI实现excel的导入导出

一。导入到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中

3.测试

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

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

 

二。从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,数据如下

6.测试

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

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

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