IT序号网

spring boot读取Excel知识解答

qq123 2021年05月25日 编程语言 144 0

首先引入相关依赖

 <!--解析office相关文件--> 
        <dependency> 
            <groupId>org.apache.poi</groupId> 
            <artifactId>poi</artifactId> 
            <version>3.17</version> 
        </dependency> 
        <dependency> 
            <groupId>org.apache.poi</groupId> 
            <artifactId>poi-ooxml</artifactId> 
            <version>3.17</version> 
        </dependency> 
 <!--解析office相关文件-->

工具类

 
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class OfficeUtils {

protected static final Logger logger = LoggerFactory.getLogger(OfficeUtils.class);

public static Map<Integer, Map<Integer, Object>> readExcelContentz(MultipartFile file) throws Exception {
Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
// 上传文件名
Workbook wb = getWb(file);
if (wb == null) {
throw new BusinessException(ErrorType.WORK_BOOK_EMPTY);
}
Sheet sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
while (j < colNum) {
Object obj = getCellFormatValue(row.getCell(j));
cellValue.put(j, obj);
j++;
}
content.put(i, cellValue);

}
return content;
}

//根据Cell类型设置数据
private static Object getCellFormatValue(Cell cell) {
Object cellvalue = "";
if (cell != null) {
switch (cell.getCellTypeEnum()) {
case NUMERIC:
cellvalue = String.valueOf(cell.getNumericCellValue());
break;
case FORMULA: {
cellvalue = cell.getDateCellValue();
break;
}
case STRING:
cellvalue = cell.getRichStringCellValue().getString();
break;
default:
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}

private static Workbook getWb(MultipartFile mf) {
String filepath = mf.getOriginalFilename();
String ext = filepath.substring(filepath.lastIndexOf("."));
Workbook wb = null;
try {
InputStream is = mf.getInputStream();
if (".xls".equals(ext)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(ext)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
} catch (FileNotFoundException e) {
logger.error("FileNotFoundException", e);
} catch (IOException e) {
logger.error("IOException", e);
}
return wb;
}
}
 

service层

public Map<Integer, Map<Integer,Object>> addCustomerInfo(MultipartFile file) { 
    Map<Integer, Map<Integer,Object>> map = new HashMap<>(); 
    try { 
        map = ReadExcelUtil.readExcelContentz(file); 
    } catch (Exception e) { 
        e.printStackTrace(); 
    } 
    //excel数据存在map里,map.get(0).get(0)为excel第1行第1列的值,此处可对数据进行处理 
}

controller层

@PostMapping 
public String add(@RequestParam("file")MultipartFile file){ 
    Map<Integer, Map<Integer,Object>> map = customerService.addCustomerInfo(file); 
    return "success"; 
}

至此,基本完成Excel的解析。


发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

JAVA泛型知识--> < extends T>和< super T>知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。