Java之POI操作Excel表-匯入匯出

不知春夏，不知秋冬發表於2020-09-23
JavaExcel

感謝：https://www.jianshu.com/p/4f08c27ca0c4的文章。
首先是兩個寫入資料和匯出資料的demo.

新建Excel表寫入資料的OutputExcelDemo類

package com.hjl.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * Created by huangMP on 2017/8/20.
 * decription :
 */
public class OutputExcelDemo {

    /**
     *  從 工作簿中寫入 資料 OutputExcelDemo
     * 07 版本及之前的版本寫法
     * @throws IOException
     */
    public void outputExcel() throws IOException {
        // 1. 建立工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 2. 建立工作類
        HSSFSheet sheet = workbook.createSheet("hello world");

        // 3. 建立行 , 第三行 注意:從0開始
        HSSFRow row = sheet.createRow(2);

        // 4. 建立單元格, 第三行第三列 注意:從0開始
        HSSFCell cell = row.createCell(2);
        cell.setCellValue("Hello World");

        String fileName = "D:\\huangMP\\Desktop\\OutputExcelDemo.xls";
        FileOutputStream fileOutputSteam = new FileOutputStream(fileName);

        workbook.write(fileOutputSteam);
        workbook.close();

        fileOutputSteam.close();
    }
}

讀取Excel表的ReadExcelDemo類

package com.hjl.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

/**
 * 從 工作簿中讀取 資料 ReadExcelDemo
 * @throws IOException
 */
public class ReadExcelDemo {

    public void readExel() throws IOException {

        String fileName = "D:\\huangMP\\Desktop\\OutputExcelDemo.xls";
        FileInputStream fileInputStream = new FileInputStream(fileName);

        // 1. 建立工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);

        // 2. 建立工作類
        HSSFSheet sheet = workbook.getSheetAt(0);

        // 3. 建立行 , 第三行 注意:從0開始
        HSSFRow row = sheet.getRow(2);

        // 4. 建立單元格, 第三行第三列 注意:從0開始
        HSSFCell cell = row.getCell(2);
        String cellString = cell.getStringCellValue();

        System.out.println("第三行第三列的值為 : " + cellString );

        workbook.close();
        fileInputStream.close();
    }
}

根據以上demo結合資料庫操作：
測試表userinfo

/*
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', '張三', '1234556', '男');
INSERT INTO `userinfo` VALUES ('2', '五萬', '321321', '女');

1.匯出資料庫表生成Excel。

package com.hjl.service;

import com.hjl.pojo.UserInfo;
import com.hjl.springbootmybatisdemo.SpringbootMybatisDemoApplication;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

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


@SpringBootTest(classes =SpringbootMybatisDemoApplication.class)
class OutputExcel {

    @Autowired
    private UserService userService;
    @Test
    void select() throws IOException {
        List<UserInfo> userInfoList=userService.select();
        outputExcel(userInfoList);
    }

    /**
     * 匯出excel
     *  從 工作簿中寫入 資料 OutputExcelDemo
     * 07 版本及之前的版本寫法
     * @throws IOException
     */
    public void outputExcel(List<UserInfo> userInfoList) throws IOException {
        // 1. 建立工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 2. 建立工作類
        HSSFSheet sheet = workbook.createSheet("hello world");

        // 3. 建立行  注意:從0開始
        HSSFRow hssfRow = sheet.createRow(0);//表的標題行
        hssfRow.createCell(0).setCellValue("UserId");//第一列 建立
        hssfRow.createCell(1).setCellValue("UserName");
        hssfRow.createCell(2).setCellValue("phone");
        hssfRow.createCell(3).setCellValue("sex");

        //從1開始
        for (int rowNume=1;rowNume<=userInfoList.size();rowNume++){
            HSSFRow row = sheet.createRow(rowNume);//假設有4個欄位
            for (int cellNume=0;cellNume<4;cellNume++){
            // 4. 建立單元格 注意:從0開始
            HSSFCell cell = row.createCell(cellNume);

            switch(cellNume){
                    case 0:
                        cell.setCellValue(userInfoList.get(rowNume-1).getUserId());break;
                    case 1:
                        cell.setCellValue(userInfoList.get(rowNume-1).getUserName());break;
                    case 2:
                        cell.setCellValue(userInfoList.get(rowNume-1).getPhone());break;
                    case 3:
                        cell.setCellValue(userInfoList.get(rowNume-1).getSex());break;
                }
            }
        }
        String fileName = "D:\\OutputExcelDemo.xls";
        FileOutputStream fileOutputSteam = new FileOutputStream(fileName);
        workbook.write(fileOutputSteam);
        workbook.close();
        fileOutputSteam.close();
        System.out.println("匯出成功！path:"+fileName);
    }
}

2.讀取Excel表的資料存入資料庫。

package com.hjl.service;


import com.hjl.dao.UserMapper;
import com.hjl.pojo.UserInfo;
import com.hjl.springbootmybatisdemo.SpringbootMybatisDemoApplication;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

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

@SpringBootTest(classes = SpringbootMybatisDemoApplication.class)
public class ReadExcel {
    @Autowired
    private UserMapper userMapper;

    @Test
    void select() throws IOException {
        String fileName="D:\\OutputExcelDemo.xls";
        List<UserInfo> list=readExels(fileName);//讀取excel表資料
        userMapper.insertUserInfoList(list);//批量插入資料
        System.out.println("成功！");
    }

    /**
     * 從 工作簿中讀取 資料 ReadExcelDemo
     * @throws IOException
     */
    public static List<UserInfo> readExels(String fileName) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<UserInfo> list= new ArrayList<>();
        HSSFRow row=null;
        HSSFCell cell=null;
        //3.遍歷行row 從第1行開始 0是表的標題行
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            //獲取每一行
            row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            UserInfo userInfo = new UserInfo();
            //4.遍歷列cell
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                //獲取每一列
                cell = row.getCell(cellNum);
                if (cell == null){
                    continue;
                }
                cell.setCellType(CellType.STRING);//設定單元格型別為String
                String str=cell.getStringCellValue();
                switch(cellNum){
                    case 0:
                        userInfo.setUserId(Integer.parseInt(str));break;
                    case 1:
                        userInfo.setUserName(str);break;
                    case 2:
                        userInfo.setPhone(str);break;
                    case 3:
                        userInfo.setSex(str);break;
                }
            }
            if (StringUtils.isBlank(userInfo.getUserName())) {
                continue;
            }
            list.add(userInfo);
        }
        workbook.close();
        fileInputStream.close();
        return  list;
    }
}

關於批量插入資料庫：https://www.cnblogs.com/quehai/p/10829360.html

userMapper.java

public interface UserMapper {
    List<UserInfo> select();
    void  insertUserInfoList(List<UserInfo> list);
}

userInfoMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hjl.dao.UserMapper">
    <select id="select" resultType="com.hjl.pojo.UserInfo">
    select * from userinfo
  </select>
    <insert id="insertUserInfoList"  parameterType="java.util.List">
    insert into userinfo(userName,phone,sex) values
      <foreach collection="list" item="userInfo" index="index" separator=",">
          (
          #{userInfo.userName},#{userInfo.phone},#{userInfo.sex}
          )
      </foreach>
    </insert>
</mapper>

相關文章