前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站点击跳转浏览。
1准备pom文件
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.miyo</groupId> <artifactId>miyo-file-server</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--热部署配置--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> </dependency> <!-- <!– mybatis-spring-boot-starter –>--> <!-- <dependency>--> <!-- <groupId>org.mybatis.spring.boot</groupId>--> <!-- <artifactId>mybatis-spring-boot-starter</artifactId>--> <!-- <version>2.1.1</version>--> <!-- </dependency>--> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.4</version> </dependency> <!-- <dependency>--> <!-- <groupId>mysql</groupId>--> <!-- <artifactId>mysql-connector-java</artifactId>--> <!-- <scope>runtime</scope>--> <!-- </dependency>--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
2 在controller里面有生成Excel，解析Excel方法
首先先用
这个方法，导出一个Excel，然后将文件放到
这个位置之后就可以根据前端传来的数值进行修改模板中字段了。
package com.miyo.controller; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Font; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.*; /** * @author xiaoli.he * @date 2022/5/20 */ @Controller public class TemplateController { /** * 根据模板修改下载对应的Excel * * @param response excel * @param templateCode code * @param includeFields 选中的字段 * @throws Exception null */ @SuppressWarnings("resource") @RequestMapping("/download") @ResponseBody public void download( HttpServletResponse response, String templateCode, @RequestBody List<String> includeFields) throws Exception { // 测试 int length = templateCode.length(); System.out.println(length); // 判断得到那些值 // 模板文件放在resources中的download包中 String filePath = Objects.requireNonNull(TemplateController.class.getClassLoader().getResource("download")) .getPath() + "/template.xls"; FileInputStream fileInputStream = new FileInputStream(filePath); BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream); POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream); HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workbook.getSheet("Sheet1"); // 得到第一行 HSSFRow row = sheet.getRow(0); // 得到最后一列 short lastCellNum = row.getLastCellNum(); // 判断那些列需要采用 for (int j = 0; j < lastCellNum; j++) { String cellValue = row.getCell(j).getStringCellValue(); HSSFCell cell = row.getCell(j); // 没有则从模板中移除 if (!includeFields.contains(cellValue)) { row.removeCell(cell); } } bufferedInputStream.close(); // 输出Excel文件 OutputStream outputStream = response.getOutputStream(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=template.xls"); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } /** * 解析Excel * * @param file file * @throws Exception null */ @SuppressWarnings("resource") @RequestMapping("/parse") @ResponseBody public void parse(@RequestParam("file") MultipartFile file) throws Exception { InputStream inputStream = file.getInputStream(); BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream); POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream); HSSFWorkbook workbook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workbook.getSheetAt(0); // 得到行数 int lastRowNum = sheet.getLastRowNum(); // 解析并且封装到一个list List<Map<String, String>> listMaps = new ArrayList<>(); // 得到第一行作为表头 HSSFRow row1 = sheet.getRow(0); int lastCellNum = row1.getLastCellNum(); String[] arr = new String[lastCellNum]; // 存入数组中 for (int t = 0; t < lastCellNum; t++) { HSSFCell cell = row1.getCell(t); String cellValue = cell.getStringCellValue(); arr[t] = cellValue; } for (int i = 1; i <= lastRowNum; i++) { // 每一行数据对应一个map Map<String, String> map = new HashMap<>(16); HSSFRow row = sheet.getRow(i); // 得到有几列 int num = row.getLastCellNum(); // 遍历每一行的单元格 for (int j = 0; j < num; j++) { HSSFCell cell = row.getCell(j); // 设置类型为String cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING); String cellValue = cell.getStringCellValue(); // 把表头和里面的值对应 map.put(arr[j], cellValue); } listMaps.add(map); } System.out.println(listMaps); } /** * 生成Excel * * @param response Excel * @throws Exception null */ @SuppressWarnings("resource") @RequestMapping("/exportExcel") public void exportExcel(HttpServletResponse response) throws Exception { // 创建一个excel的文档对象 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建excel的表单 HSSFSheet sheet = workbook.createSheet("Sheet1"); // cell样式 HSSFCellStyle cellStyle = workbook.createCellStyle(); // 设置水平和垂直居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成一个字体 Font font = workbook.createFont(); font.setFontHeightInPoints((short) 9); // 设置字体的颜色 font.setColor(HSSFColor.BLUE.index); // 设置字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); // 创建一个行 HSSFRow row = sheet.createRow(0); // 设计表头 String[] tableHeaders = { "公司", "毛利率", "净现比", "管理费用率", "销售费用率", "预付账款周转率", "应收账款周转率", "应付账款周转率", "其他应收款占总资产比", "其他应付应收比" }; // 创建表头 for (int i = 0; i < tableHeaders.length; i++) { // 如果用了添加表头 // 创建单元格并设置单元格内容 HSSFCell cell = row.createCell(i); // 表头数组 cell.setCellValue(tableHeaders[i]); // 赋予格式 cell.setCellStyle(cellStyle); } // 输出Excel文件 OutputStream outputStream = response.getOutputStream(); response.reset(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=template.xls"); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } }
3，之后用postman测试成功
