springboot实现导入excel

springboot实现excel数据导入

controller

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.xxx.common.annotation.ValidationParam;
import com.xxx.common.base.ResponseHelper;
import com.xxx.common.base.ResponseModel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
@Api(tags={"xx接口"})
@RestController
@RequestMapping("/Xx")
public class XxController {

	@ApiOperation(value = "",notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id", value = "id", paramType = "body", dataType = "Integer"),
            @ApiImplicitParam(name = "file", value = "导入文件", paramType = "body", dataType = "File")
    })
    @PostMapping("/importFile")
    public ResponseModel importFile(@RequestParam(value = "id")Integer robotId, @RequestParam(value = "file") MultipartFile file)throws Exception{
        File file1 = null;
        file1 = File.createTempFile("temp", null);
        file.transferTo(file1);
        InputStream ips = new FileInputStream(file1);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(ips);
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        // 获得总行数
        int rowNum = sheet.getLastRowNum();
        // 获取sheet的总行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        // 获取该行的总列数
        int RowCells=sheet.getRow(0).getPhysicalNumberOfCells();
        // 读取单元格内容
        for (int i = 0; i <= rowNum; i++) {
        	// 读取行
            XSSFRow row = sheet.getRow(i);
            // 设置单元格格式
            row.getCell(0).setCellType(CellType.STRING);
            row.getCell(1).setCellType(CellType.NUMERIC);
            // 读取单元格内容
            String s = row.getCell(0).getStringCellValue();
            double d = row.getCell(1).getNumericCellValue();
        }
        //具体处理过程省略
    	return ResponseHelper.succeed(true);
    }
}

pom.xml文件

<properties>
	<poi.version>3.15</poi.version>
</properties>
<dependencyManagement>
	<dependencies>
	<dependency>
		<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
		<version>${poi.version}</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>${poi.version}</version>
	</dependency>
</dependencyManagement>