1.pom.xml
<!--poi.start --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${org-apache-poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>${org-apache-poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <!--poi.end -->2.读取工具类
package com.common.utils; import lombok.Data; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 读取excel工具类 */ public class ExcelReadUtils { private XSSFWorkbook workbook; private XSSFSheet sheet; /** * 工作表下标 */ private int sheetIndex = 0; public ExcelReadUtils(){} public ExcelReadUtils(File file) throws IOException { FileInputStream inputStream = new FileInputStream(file); this.workbook = new XSSFWorkbook(inputStream); inputStream.close(); this.changeSheet(); } public ExcelReadUtils(File file, int sheetIndex) throws IOException { FileInputStream inputStream = new FileInputStream(file); this.workbook = new XSSFWorkbook(inputStream); inputStream.close(); this.sheetIndex = sheetIndex; this.changeSheet(); } /** * 获取某行的单元格的值-字符串数组 * @param rowIndex * @return */ public List<String> getRowStrArr(int rowIndex){ List<String> res = new ArrayList<>(); XSSFRow row = this.sheet.getRow(rowIndex); for (int j = 0; j < row.getLastCellNum(); j++) { XSSFCell dataCell = row.getCell(j); String cellValue = this.convertCellValueToString(dataCell); res.add(cellValue); } return res; } /** * 获取某行的单元格的值-根据keyArr列顺序拼接成map * @param rowIndex * @param keyArr * @return */ public Map<String, String> getRowStrMap(int rowIndex, List<String> keyArr){ List<String> list = this.getRowStrArr(rowIndex); Map<String, String> res = new HashMap<>(); if( CollectionUtils.isNotEmpty(list) ){ for(int i=0; i<list.size(); i++){ String key = "column_"+i; if(CollectionUtils.isNotEmpty(keyArr) && i<keyArr.size()) { key = keyArr.get(i); } res.put(key, list.get(i)); } } return res; } /** * 获取所有行的数据-根据keyArr列顺序拼接成map * @param keyArr * @return */ public List<Map<String, String>> getAllDataMap(List<String> keyArr){ List<Map<String, String>> res = new ArrayList<>(); for (int i = 0; i <= this.sheet.getLastRowNum(); i++) { Map<String, String> item = this.getRowStrMap(i, keyArr); res.add(item); } return res; } /** * 更换工作表 * @return */ private void changeSheet(){ this.sheet = this.workbook.getSheetAt(this.sheetIndex); } /** * 将单元格内容转化为字符串 */ private String convertCellValueToString(Cell cell) { if (null == cell) { return null; } String returnValue = null; switch (cell.getCellTypeEnum()) { //字符串 case STRING: returnValue = cell.getStringCellValue(); break; //数字 case NUMERIC: double numericCellValue = cell.getNumericCellValue(); boolean isInteger = isIntegerForDouble(numericCellValue); if (isInteger) { DecimalFormat df = new DecimalFormat("0"); returnValue = df.format(numericCellValue); } else { returnValue = Double.toString(numericCellValue); } break; //布尔 case BOOLEAN: boolean booleanCellValue = cell.getBooleanCellValue(); returnValue = Boolean.toString(booleanCellValue); break; //空值 case BLANK: break; //公式 case FORMULA: cell.getCellFormula(); break; //故障 case ERROR: break; default: break; } return returnValue; } /** * 判断是否为整数，是返回true，否则返回false. */ public static boolean isIntegerForDouble(Double num) { double eqs = 1e-10; //精度范围 return num - Math.floor(num) < eqs; } /** * 使用完要关闭流 */ public void close() throws IOException { this.workbook.close(); } public int getSheetIndex() { return sheetIndex; } public ExcelReadUtils setSheetIndex(int sheetIndex) { this.sheetIndex = sheetIndex; this.changeSheet(); return this; } } 3.写入工具类
package com.common.utils; import com.common.exceptions.BaseException; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.*; /** * 写excel工具类 */ public class ExcelWriteUtils { private XSSFWorkbook workbook; private List<XSSFSheet> sheetArr; private List<String> sheetNameArr; private int currSheetIndex = 0; private int currRowIndex = 0; public ExcelWriteUtils(){ this.sheetNameArr = Arrays.asList("sheet1"); this.initSheetArr(); } public ExcelWriteUtils(String sheetName) { this.sheetNameArr = Arrays.asList(sheetName); this.initSheetArr(); } public ExcelWriteUtils(List<String> sheetNameArr) { this.sheetNameArr = sheetNameArr; this.initSheetArr(); } /** * 初始化sheet * @throws Exception */ private void initSheetArr() { if( CollectionUtils.isEmpty(this.sheetNameArr) ){ throw BaseException.logicException("sheet没有设置名称"); } //XSSFWorkbook:07版本的xlsx this.workbook = new XSSFWorkbook(); //构造sheet this.sheetArr = new ArrayList<>(); this.sheetNameArr.forEach(sheetName->{ XSSFSheet sheet = this.workbook.createSheet(sheetName); this.sheetArr.add(sheet); }); } /** * 获取当前sheet * @return */ private XSSFSheet getCurrSheet(){ return this.sheetArr.get(this.currSheetIndex); } /** * 写入数据到某行 * @param valueArr */ public void writeRow(int rowIndex, List<String> valueArr){ XSSFRow row = this.getCurrSheet().getRow(rowIndex); if( row==null ){ row = this.getCurrSheet().createRow(rowIndex); } if( CollectionUtils.isNotEmpty(valueArr) ){ for(int i=0; i<valueArr.size(); i++){ XSSFCell cell = row.createCell(i); cell.setCellValue(valueArr.get(i)); } } } /** * 写入数据到当前行 * @param valueArr */ public void writeCurrRow(List<String> valueArr){ this.writeRow(this.currRowIndex, valueArr); } /** * 批量写入数据到多行 * @param valueArrArr */ public void writeBatchRow(List<List<String>> valueArrArr){ if( CollectionUtils.isNotEmpty(valueArrArr) ){ valueArrArr.forEach(valueArr->{ if( CollectionUtils.isNotEmpty(valueArr) ){ this.writeCurrRow(valueArr); } this.currRowIndex++; }); } } public int getCurrSheetIndex() { return currSheetIndex; } public void setCurrSheetIndex(int currSheetIndex) { this.currSheetIndex = currSheetIndex; } public int getCurrRowIndex() { return currRowIndex; } public void setCurrRowIndex(int currRowIndex) { this.currRowIndex = currRowIndex; } public XSSFWorkbook getWorkbook() { return workbook; } } 4.测试
上传文件，读取excel内容，log打印，然后生成新的excel，最后下载这个excel文件
4.1 控制器
package com.demo.mydemo.controller; import com.common.utils.AjaxResult; import com.common.utils.StringUtils; import com.demo.mydemo.entity.dto.ExcelParamDTO; import com.demo.mydemo.service.IExcelService; import io.swagger.annotations.ApiOperation; import io.swagger.annotations.ApiParam; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.net.URL; import java.net.URLConnection; import java.net.URLEncoder; /** * <p> * excel * </p> * * @author szy * @since 2022-05-06 */ @Controller @RequestMapping("/excel") public class ExcelController { @Autowired private IExcelService excelService; /** * http://localhost:20001/szy-tools/excel/index * @return */ @RequestMapping("/index") public String index() { return "excel_index"; } /** * http://localhost:20001/szy-tools/excel/upload */ //限制只能post访问 @RequestMapping(value="/upload", method= RequestMethod.POST) @ResponseBody public AjaxResult uploadData( @ApiParam(value = "参数") ExcelParamDTO param) { String filePath = excelService.upload(param); return AjaxResult.success("成功", filePath); } @RequestMapping("/download") @ApiOperation(value = "下载文件") public void download( HttpServletResponse response, @ApiParam(value = "文件路径") @RequestParam(value="filePath", required = true) String filePath ) throws IOException { File fileUpload = new File(filePath); String fileName = fileUpload.getName(); //encode后替换 解决空格问题 fileName = URLEncoder.encode(fileName, "UTF-8"); fileName = fileName.replace("+", "%20"); // 读到流中 // URL url = new URL(fileUpload.getFileUrl()); // URLConnection conn = url.openConnection(); // InputStream inputStream = conn.getInputStream(); InputStream inputStream = new FileInputStream(fileUpload); response.reset(); response.setContentType("application/octet-stream"); response.addHeader("Content-Disposition", "attachment; filename=" + fileName); ServletOutputStream outputStream = response.getOutputStream(); byte[] b = new byte[1024]; int len; //从输入流中读取一定数量的字节，并将其存储在缓冲区字节数组中，读到末尾返回-1 while ((len = inputStream.read(b)) > 0) { outputStream.write(b, 0, len); } inputStream.close(); } } 4.2 服务类/业务类
接口类
package com.demo.mydemo.service; import com.demo.mydemo.entity.dto.ExcelParamDTO; import com.demo.mydemo.entity.dto.SmbParamDTO; import java.io.File; public interface IExcelService { String upload(ExcelParamDTO param); } 实现类
package com.demo.mydemo.service.impl; import com.common.exceptions.BaseException; import com.common.utils.*; import com.demo.mydemo.entity.dto.*; import com.demo.mydemo.service.IExcelService; import lombok.extern.slf4j.Slf4j; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import org.springframework.util.ResourceUtils; import org.springframework.web.multipart.MultipartFile; import java.io.*; import java.util.*; import static java.util.regex.Pattern.compile; @Service @Slf4j public class ExcelServiceImpl implements IExcelService { @Override public String upload(ExcelParamDTO param){ File uploadFile = null; try { //上传文件 uploadFile = this._uploadFile(param.getFiles(), ""); //读取excel ExcelReadUtils readUtils = new ExcelReadUtils(uploadFile); List<String> keyArr = Arrays.asList("10-19", "20-29", "30-39", "40-49", "50-59", "60-69"); List<Map<String, String>> data = readUtils.getAllDataMap(keyArr); if( CollectionUtils.isNotEmpty(data) ){ final int[] i = {1}; data.forEach(item->{ log.info("第"+ i[0] +"行：{}", JSONUtils.toJsonString(item)); i[0]++; }); } readUtils.close(); //写入excel ExcelWriteUtils writeUtils = new ExcelWriteUtils("测试1"); if( CollectionUtils.isNotEmpty(data) ){ final int[] rowIndex = {0}; data.forEach(item->{ List<String> valueArr = new ArrayList<>(); keyArr.forEach(key->{ valueArr.add( item.get(key) ); }); writeUtils.writeRow(rowIndex[0], valueArr); rowIndex[0]++; }); } File targetFile = this._buildExcelFile(writeUtils.getWorkbook(), ""); return targetFile.getAbsolutePath(); } catch (Exception e) { e.printStackTrace(); throw BaseException.logicException(e.getMessage()); } finally { //删除上传的文件 if( uploadFile!=null && uploadFile.exists() ){ // uploadFile.delete(); } } } private File _uploadFile(MultipartFile srcFile, String dir) throws Exception{ // 根路径，在 resources/static/upload String folderPath = ResourceUtils.getURL("classpath:").getPath() + "upload/" + (StringUtils.isNotBlank(dir) ? (dir + "/") : ""); File folder = new File(folderPath); if( !folder.exists() ){ folder.mkdirs(); } UUID uuid = UUID.randomUUID(); File targetFile = new File(folder.getAbsolutePath(), uuid+"_"+srcFile.getOriginalFilename()); srcFile.transferTo(targetFile); return targetFile; } private File _buildExcelFile(XSSFWorkbook workbook, String dir) throws IOException { // 根路径，在 resources/static/upload String folderPath = ResourceUtils.getURL("classpath:").getPath() + "upload/" + (StringUtils.isNotBlank(dir) ? (dir + "/") : ""); File folder = new File(folderPath); if( !folder.exists() ){ folder.mkdirs(); } UUID uuid = UUID.randomUUID(); File targetFile = new File(folder.getAbsolutePath(), uuid+"_build_.xlsx"); OutputStream out = new FileOutputStream(targetFile); workbook.write(out); workbook.close(); out.close(); return targetFile; } } 4.3 页面
<!DOCTYPE html> <html> <head> <meta charset="UTF-8" /> <title>excel测试</title> <script src="./../scripts/jquery-3.3.1.min.js"></script> <script src="./../scripts/jquery.form.js"></script> </head> <body> <form id="form" method="post" enctype="multipart/form-data"> <table> <tr> <td>文件</td> <td> <input type="file" name="files"/> </td> </tr> </table> <input type="submit" value="上传" id="btnUpload"/> </form> <div id="result"></div> <script> $(function(){ var url = window.location.origin+"/szy-tools/excel/upload"; var download_url = window.location.origin+"/szy-tools/excel/download"; $("#form").ajaxForm({ url : url, type : "POST", dataType : "json", beforeSubmit: function(){ return true; }, success: function(res){ if( res.state=="success" ){ $("#result").text("成功："+res.data); var t_url = download_url+"?filePath="+urlencode(res.data); window.open(t_url); }else{ $("#result").text("失败："+res.message); } }, fail: function(res){ $("#result").text("失败："+res); } }); }); function urlencode (str) { str = (str + '').toString(); return encodeURIComponent(str).replace(/!/g, '%21').replace(/'/g, '%27').replace(/\(/g, '%28').replace(/\)/g, '%29').replace(/\*/g, '%2A').replace(/%20/g, '+'); } </script> </body> </html>5.补充所有pom.xml
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.6.7</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>szy-tools</name> <description>My tools project for Spring Boot</description> <!-- 打包配置 --> <packaging>jar</packaging> <properties> <java.version>1.8</java.version> <!-- 分布式RPC框架Motan--> <motan.version>1.1.6</motan.version> <org-apache-poi.version>3.17</org-apache-poi.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 热部署 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- thymeleaf,for html,jsp... --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- json.start --> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mapper-asl</artifactId> <version>1.9.13</version> </dependency> <!-- jackson默认不支持java8的时间类型，需要添加一个时间模块 --> <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-jsr310</artifactId> </dependency> <!-- json.end --> <!--lombok用来简化实体类--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.8.1</version> </dependency> <!-- Knife4j在线API文档的依赖 --> <dependency> <groupId>com.github.xiaoymin</groupId> <artifactId>knife4j-spring-boot-starter</artifactId> <version>3.0.3</version> </dependency> <!-- httpclient.start --> <dependency> <groupId>org.apache.httpcomponents</groupId> <artifactId>httpclient</artifactId> </dependency> <!-- httpclient.end --> <!-- smb.start --> <!--支持文件共享SMB1协议--> <dependency> <groupId>org.samba.jcifs</groupId> <artifactId>jcifs</artifactId> <version>1.3.3</version> </dependency> <!--支持文件共享SMB2/3协议--> <dependency> <groupId>com.hierynomus</groupId> <artifactId>smbj</artifactId> <version>0.10.0</version> </dependency> <!-- smb.end --> <!-- user-agent工具类.start --> <dependency> <groupId>eu.bitwalker</groupId> <artifactId>UserAgentUtils</artifactId> <version>1.21</version> </dependency> <!-- user-agent工具类.end --> <!--poi.start --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${org-apache-poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>${org-apache-poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <!--poi.end --> </dependencies> <build> <!-- 指定生成的jar包名称 --> <finalName>szy-tools</finalName> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>