通过POI操作Excel的导入导出

项目地址https://github.com/zhuhaitao666/excel.git

这里罗列部分核心代码

1.引入依赖和配置

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.37</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--添加DBCP数据源-->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
</dependency>
<!--数据库操作采用 tk.mybatis 框架-->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<!-- Lombok 简化 set 和 get方法-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>
<!--thymeleaf-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>

 

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.excel.bean
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost/test
    username: root
    password: 123456
    type: org.apache.commons.dbcp2.BasicDataSource
    tomcat:
      max-idle: 10
      max-wait: 1000000
      max-active: 50
      initial-size: 5
  thymeleaf:
    suffix: .html
    cache: false
    mode: LEGACYHTML5

2.控制层

package com.example.excel.controller;

import com.example.excel.domain.S;
import com.example.excel.service.SService;
import org.apache.commons.io.FileUtils;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;


@Controller
public class SController {
    @Autowired
    private SService sService;

    @RequestMapping("/getAllFormExcel")
    public String getAllFormExcel(Model model){
        //需要解析的Excel文件
        File file=new File("D:/s.xls");
        List<S> list=new ArrayList<S>();//返回的集合
        try {
            //获取工作薄
            FileInputStream fileInputStream= FileUtils.openInputStream(file);
            HSSFWorkbook workbook=new HSSFWorkbook(fileInputStream);
            //获取第一个工作表
            HSSFSheet hs=workbook.getSheetAt(0);
            //获取该工作表的第一个行号和最后一个行号
            int last=hs.getLastRowNum();
            int first=hs.getFirstRowNum();
            //遍历获取单元格信息
            for(int i=first+1;i<=last;i++){//第一行是String类型
                HSSFRow row=hs.getRow(i);
                int firstCellNum=row.getFirstCellNum();
                int lastCellNum=row.getLastCellNum();
                S s=new S();
                for(int j=firstCellNum;j<lastCellNum;j++){

                    HSSFCell cell=row.getCell(j);
                    //读取数据前设置单元格类型
//                    cell.setCellType(CellType.STRING);
//                    String value=cell.getStringCellValue();
//                    System.out.print(value+" ");
                    if(j==firstCellNum){
                        s.setId((int) cell.getNumericCellValue());
                    }else if(j==firstCellNum+1){
                        s.setName(cell.getStringCellValue());
                    }else if(j==firstCellNum+2){
                        s.setCourse(cell.getStringCellValue());
                    }else if (j==firstCellNum+3){
                        s.setScore(cell.getNumericCellValue());
                    }
                }
                list.add(s);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        model.addAttribute("list",list);
        List<S> list1=sService.getAllStudent();
        model.addAttribute("list1",list1);
        return "index";
    }
    @RequestMapping("/export")
    @ResponseBody
    public String createExcel(HttpServletResponse response) throws IOException {
        //从数据库先获取集合信息
        List<S> sList=sService.getAllStudent();
        String title[]={"编号","姓名","课程","分数"};
        //1.创建Excel工作簿,不传入参数
        HSSFWorkbook workbook=new HSSFWorkbook();
        //2.创建一个工作表 名字为sheet2
        HSSFSheet sheet=workbook.createSheet("sheet2");
        //3.创建第一行
        HSSFRow row=sheet.createRow(0);
        HSSFCell cell=null;
        //4.插入第一行数据
        for (int i=0;i<title.length;i++){
            cell=row.createCell(i);
            cell.setCellValue(title[i]);
        }

        //5.插入其他数据
        for (int i=1;i<=sList.size();i++){
            sheet.autoSizeColumn(i);//单元格宽度自适应
            row=sheet.createRow(i);//创建行
            S s=sList.get(i-1);
            for(int j=0;j<title.length;j++){
                cell=row.createCell(j);
                if (j==0){
                    cell.setCellValue(s.getId());
                }else if (j==1){
                    cell.setCellValue(s.getName());
                }else if (j==2){
                    cell.setCellValue(s.getCourse());
                }else if(j==3){
                    cell.setCellValue(s.getScore());
                }else{
                    cell.setCellValue("字段列超过了设定的范围");
                }
            }
        }
        //保存到固定的位置
//        File file=new File("D://生成的Excel表.xls");
//        file.createNewFile();
//        FileOutputStream outputStream=FileUtils.openOutputStream(file);
//        workbook.write(outputStream);
//        outputStream.close();
        //输出Excel文件
        OutputStream outputStream=response.getOutputStream();
        response.reset();
        //文件名这里可以改
        response.setHeader("Content-disposition", "attachment; filename=report.xls");
        response.setContentType("application/msexcel");
        workbook.write(outputStream);
        outputStream.close();
        return "success";//实战中应该通过异步处理
    }
}

3.页面

<!DOCTYPE html SYSTEM "http://www.thymeleaf.org/dtd/xhtml1-strict-thymeleaf-4.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head>
    <title>Good Thymes Virtual Grocery</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <link th:href="@{/bootstrap.css}" type="text/css" rel="stylesheet">
</head>
<body>
<div class="container">
    <h1 style="text-align: center">从Excel导入的数据</h1>
    <table class="table table-hover">
        <caption>成绩表</caption>
        <thead>
                <th>序号</th>
                <th>姓名</th>
                <th>学科</th>
                <th>成绩</th>
        </thead>
        <tbody>
        <tr th:each="stu:${list}" class="success">
            <td th:text="${stu.getId()}" ></td>
            <td th:text="${stu.getName()}" ></td>
            <td th:text="${stu.getCourse()}"></td>
            <td th:text="${stu.getScore()}"></td>
        </tr>
        </tbody>
    </table>
    <h3 style="text-align: center">从数据库中导入数据,点击保存生成Excel表</h3>
    <table class="table table-hover">
        <caption>数据库信息</caption>
        <thead>
        <th>序号</th>
        <th>姓名</th>
        <th>学科</th>
        <th>成绩</th>
        </thead>
        <tbody>
        <tr th:each="stu:${list1}" class="warning">
            <td th:text="${stu.getId()}" ></td>
            <td th:text="${stu.getName()}" ></td>
            <td th:text="${stu.getCourse()}"></td>
            <td th:text="${stu.getScore()}"></td>
        </tr>
        </tbody>
    </table>
    <button type="button" class="btn btn-danger"><a th:href="@{/export}">保存到Excel</a></button>
</div>
</body>
</html>