springboot的poi导入和导出

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>