Java excel數(shù)據(jù)導(dǎo)入mysql的實現(xiàn)示例詳解

更新時間：2022年08月12日 09:44:24 作者：赫赫有安

今天教大家如何使用Java將excel數(shù)據(jù)導(dǎo)入MySQL,文中有非常詳細(xì)的代碼示例,對正在學(xué)習(xí)java的小伙伴呢很有幫助,需要的朋友可以參考下

一.新建Excel表并插入測試所需數(shù)據(jù)

二.新建springBoot工程

修改pom.xml文件以及application.properties。

pom.xml

<!--缺少此jar包，導(dǎo)致@Mapper注解無效-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<!--java對象狀態(tài)自動映射到關(guān)系數(shù)據(jù)庫中數(shù)據(jù)上-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.0.2.RELEASE</version>
</dependency>
<!--實現(xiàn)類與xml之間的相互轉(zhuǎn)換-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-oxm</artifactId>
    <version>5.0.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-jpamodelgen</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>2.2.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>
<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>

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=12345678
spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
#配置通過jpa自動創(chuàng)建表
spring.jpa.hibernate.ddl-auto=create
#打印SQL
spring.jpa.show-sql=true

三.創(chuàng)建實體類進(jìn)行關(guān)系的映射

啟動項目就會在數(shù)據(jù)庫自動創(chuàng)建實體類的表，創(chuàng)建完之后會仔細(xì)發(fā)現(xiàn)數(shù)據(jù)庫里的字段和實體類里的字段順序是不一樣，會出現(xiàn)亂序狀態(tài)，這是因為hibernate源碼中用的是TreeMap存儲實體類字段，TreeMap屬性是無序的。具體的解決辦法如下：

1.找到源碼文件

2.在當(dāng)前項目中創(chuàng)建一個和源碼類一樣的包結(jié)構(gòu)和一樣名字的類，直接復(fù)制源碼文件所有代碼到新建的類中。

3.將上圖標(biāo)識的TreeMap 修改為LinkedHashMap修改好之后重新啟動項目，會發(fā)現(xiàn)實體類和數(shù)據(jù)庫表中順序?qū)R了。

4.Excel的DAO類接口，與Excel有關(guān)的持久化操作方法。

5.創(chuàng)建service層接口，與Excel有關(guān)的業(yè)務(wù)邏輯方法。

6.service層方法的實現(xiàn)，與用戶信息有關(guān)的業(yè)務(wù)邏輯方法。詳細(xì)代碼如下：

package com.wxy.excel.service;
import com.wxy.excel.mapper.ExcelRepository;
import com.wxy.excel.entity.Excel;
import com.wxy.excel.mapper.UserMapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Service
public class ExcelServiceImpl implements ExcelService{
    @Autowired
    private ExcelRepository excelRepository;
    @Autowired
    private UserMapper userMapper;
    @Override
    public boolean getExcel(MultipartFile file) throws Exception {
        List<Excel> list = new ArrayList<Excel>();
        //1.得到上傳的表
        Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
        //2.獲取test工作表 注意test就是excel下面的sheet名稱
        Sheet sheet2 = workbook2.getSheet("test");
        //3.獲取表的總行數(shù)
        int num = sheet2.getLastRowNum();
        //4.獲取表總列數(shù)
        int col = sheet2.getRow(0).getLastCellNum();
        //5.遍歷excel每一行
        for (int j = 0; j <num; j++) {
            Row row1 = sheet2.getRow(j);
            // 如果單元格中有數(shù)字或者其他格式的數(shù)據(jù)，則調(diào)用setCellType()轉(zhuǎn)換為string類型
            Cell cell1 = row1.getCell(0);
            cell1.setCellType(CellType.STRING);
            //獲取表中第i行，第2列的單元格
            Cell cell2 = row1.getCell(1);
            cell2.setCellType(CellType.STRING);
            //獲取excel表的第i行，第3列的單元格
            Cell cell3 = row1.getCell(2);
            cell3.setCellType(CellType.STRING);
            Cell cell4 = row1.getCell(3);
            cell4.setCellType(CellType.STRING);
            Cell cell5 = row1.getCell(4);
            cell5.setCellType(CellType.STRING);
            //這里new 一個對象，用來裝填從頁面上傳的Excel數(shù)據(jù)，字段根據(jù)上傳的excel決定
            Excel excel= new Excel();
            excel.setId(cell1.getStringCellValue());
            excel.setUsername(cell2.getStringCellValue());
            excel.setEmail(cell3.getStringCellValue());
            excel.setPassword(cell4.getStringCellValue());
            excel.setRole(cell5.getStringCellValue());
            list.add(excel);
            System.out.println("excel"+excel);
        }
        excelRepository.saveAll(list);
        return true;
    }
    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        // 第一步，創(chuàng)建一個webbook，對應(yīng)一個Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一個sheet,對應(yīng)Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("test");
        // 第三步，在sheet中添加表頭第0行,注意老版本poi對Excel的行數(shù)列數(shù)有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，創(chuàng)建單元格，并設(shè)置值表頭 設(shè)置表頭居中
        HSSFCellStyle style = wb.createCellStyle();
        // 創(chuàng)建一個居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        /*此處根據(jù)情況自己自定義樣式*/
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("ID");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("郵箱");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("密碼");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("角色");
        cell.setCellStyle(style);
        // 第五步，寫入實體數(shù)據(jù) 實際應(yīng)用中這些數(shù)據(jù)從數(shù)據(jù)庫得到，
        List<Excel> list = userMapper.getAllUser();
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Excel excel = list.get(i);
            // 創(chuàng)建單元格，并設(shè)置值
            row.createCell(0).setCellValue(excel.getId());
            row.createCell(1).setCellValue(excel.getUsername());
            row.createCell(2).setCellValue(excel.getEmail());
            row.createCell(3).setCellValue(excel.getPassword());
            row.createCell(4).setCellValue(excel.getRole());
        }
        //第六步,輸出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        //設(shè)置日期格式
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        // 獲取當(dāng)前系統(tǒng)時間
        String fileName = df.format(new Date());
        //設(shè)置導(dǎo)出文件表頭（即文件名）
        response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
        //設(shè)置返回內(nèi)容類型
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }
}

7.Controller層實現(xiàn)。