之前自己写过一篇poi的excel文件的导入,但是大部分的列解析方法都已经废弃了,就又贴着头去看了下其他博客的写法,认识认识方法,然后自己写了一篇。
代码篇
pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
java
package com.seestech;
import com.seestech.exception.SeestechException;
import com.seestech.model.SysUser;
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.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
* Created by 神烦 on 2019/12/4.
*/
public class PoiReadTest extends AppTest {
private static Logger logger = LoggerFactory.getLogger(PoiReadTest.class);
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
@Test
public void readExcel() {
ExcelReader.readExcel("path");
}
static class ExcelReader {
static List<SysUser> readExcel(String fileName) {
Workbook workbook = null;
InputStream inputStream = null;
//获取后缀名
String fileType = fileName.substring(fileName.lastIndexOf(".")+1, fileName.length());
File file = new File(fileName);
if (!file.exists()) {
logger.error("文件不存在");
throw new SeestechException("文件不存在");
}
try {
// 获取文件
inputStream = new FileInputStream(file); //workbook = WorkbookFactory.create(file); 支持03 07 10
workbook = getWorkbook(inputStream, fileType);
return parseExcel(workbook);
} catch (FileNotFoundException e) {
logger.error("文件不存在");
return null;
} catch (IOException e) {
logger.error("文件流异常");
return null;
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
if (workbook != null) {
workbook = null;
}
} catch (Exception e) {
logger.error("流关闭异常");
} finally {
}
}
}
//读取
static Workbook getWorkbook(InputStream inputStream, String fileType)throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)){
//03版本
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
//07版本
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
//解析
static List<SysUser> parseExcel(Workbook workbook) {
List<SysUser> list = new ArrayList<>();
//解析
for (int i = 0; i <workbook.getNumberOfSheets() ; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
//获取起始行数
int firstNum = sheet.getFirstRowNum();
//再获取行的数据
Row row = sheet.getRow(firstNum);
if (row == null) {
logger.warn("没有校验的行参");
throw new SeestechException("没有校验的行参");
}
//防止中间空行 需校验
int startRowNum = firstNum+1;
//int endRowNum = sheet.getLastRowNum(); 返回最后一行的索引?
int endRowNum = sheet.getPhysicalNumberOfRows();
for (int j = startRowNum; j < endRowNum; j++) {
row = sheet.getRow(j);
if (row == null) {
continue;
}
//获取到合法的行 则转换参数
SysUser user = convertRowToData(row);
list.add(user);
}
}
return list;
}
static SysUser convertRowToData(Row row) {
SysUser user = new SysUser();
Cell cell;
int cellNum = 0;
cell = row.getCell(cellNum++); //1
//cell可获取
String sale = cell.getStringCellValue();
user.setSalt(sale);
//cell = row.getCell(cellNum++); //2
return user;
}
}
}
可以拿来即用。
小提示
项目中的MultipartFile对象转换file用常规的强转方法总会有遗留文件,我也是刚刚找到新的没有遗留的方法。
FileUtils.copyInputStreamToFile(multipartFile.getInputStream, new File());