项目地址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>