ExcelUtil工具类
滑骞尧
maven依赖导入poi
<!-- excel工具类 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
导入jackson依赖包：
<!--jackson工具包-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.8.5</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.8.5</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.8.5</version>
</dependency>
ExcelUtil工具类：
// 日志工具
private static Logger log = Logger.getLogger(ExcelUtils.class);
// case案例id映射对应的行号
private static Map<String, Integer> caseIdRownumMapping = new LinkedHashMap<>();
// 列表头对应列数
private static Map<String, Integer> titleIdcolnumMapping = new LinkedHashMap<>();
/**
* 加载创建映射
*
* @Author 鹿少年 2020年9月8日 下午1:39:45
* @param filePath
* @param sheetName
*/
public static void loaderExcel(String filePath, String sheetName) {
String[] title = null;
try {
title = getTittle(filePath, sheetName);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
for (int i = 0; i < title.length; i++) {
titleIdcolnumMapping.put(title[i], i);
}
Sheet sheet = ExcelUtils.getSheet(filePath, sheetName);
int lastRownum = sheet.getLastRowNum();
for (int i = 1; i <= lastRownum; i++) {
Row row = sheet.getRow(i);
Cell cell = getCell(row, 0);
String caseid = cell.getStringCellValue();
int rownum = row.getRowNum();
caseIdRownumMapping.put(caseid, rownum);
}
}
/**
* 判断是否是Excel文件
* @Author 鹿少年 2020年9月16日 下午12:42:57
* @return
*/
public static boolean IsExcelFile(String filePath) {
if(filePath.substring(filePath.lastIndexOf('.')+1).equals("xls")||filePath.substring(filePath.lastIndexOf('.')+1).equals("xlsx")) {
return true;
}
return false;
}
/**
* 得到列号
*
* @Author 鹿少年 2020年9月8日 上午11:43:47
* @param filePath
* @param sheetName
* @param titleName
* @return
*/
public static int getcellNum(String filePath, String sheetName, String titleName) {
String[] title = null;
try {
title = getTittle(filePath, sheetName);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for (int i = 0; i < title.length; i++) {
if (StringUtil.equalsIgnoreCase(title[i], titleName)) {
return i;// 得到列号
}
}
// 没有查找到
return -1;
}
/**
* 行号唯一时获取行号
*
* @Author 鹿少年 2020年9月8日 上午11:58:03
* @param filePath
* @param sheetName
* @param caseId
* @return
*/
public static int getRowNum(String filePath, String sheetName, String caseId) {
Sheet sheet = getSheet(filePath, sheetName);
// 得到行数
int rownum = sheet.getLastRowNum();
for (int i = 1; i <= rownum; i++) {
Row row = sheet.getRow(i);
String value = getCellFormatValue(row, 0);
if (StringUtil.equalsIgnoreCase(value, caseId)) {
return i;// 得到行号
}
}
// 没有查找到
return -1;
}
/**
* 读取指定的数据 行列唯一指定定位唯一数据
*
* @Author 鹿少年 2020年9月8日 下午12:01:05
* @param filePath
* @param sheetName
* @param caseId
* @param titleName
*/
public static String ReadRowCellNum(String filePath, String sheetName, String caseId, String titleName) {
int cellnum = getcellNum(filePath, sheetName, titleName);
int rownum = getRowNum(filePath, sheetName, caseId);
Sheet sheet = getSheet(filePath, sheetName);
Row row = sheet.getRow(rownum - 1);
return getCellFormatValue(row, cellnum - 1);
}
/**
* 指定行号列号获取
*
* @Author 鹿少年 2020年9月7日 下午8:36:19
* @param filename
* @param sheeName
* @param row
* @param cell
*/
public static Object[][] readExcel(String filePath, String sheetName, int rowcolum[], int cellcolum[]) {
Sheet sheet = getSheet(filePath, sheetName);
Object[][] obj = new Object[rowcolum.length][cellcolum.length];
for (int i = 0; i < rowcolum.length; i++) {
Row row = sheet.getRow(rowcolum[i] - 1);
for (int j = 0; j < cellcolum.length; j++) {
obj[i][j] = getCellFormatValue(row, cellcolum[j] - 1);
}
}
return obj;
}
/**
* 读取到对应行的列信息
*
* @param cell
* @return
*/
public static String getCellFormatValue(Row row, int index) {
Cell cell = row.getCell(index, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
/**
* 得到cell列
*
* @Author 鹿少年 2020年9月8日 下午12:14:35
* @param row
* @param index
* @return
*/
@SuppressWarnings("deprecation")
public static Cell getCell(Row row, int index) {
Cell cell = row.getCell(index, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
return cell;
}
/**
* 打印输出Excel数据
*
* @param datas
*/
public static void printExcelData(List<Map<String, String>> datas) {
for (int i = 0; i < datas.size(); i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
map = datas.get(i);
Iterator<?> it = map.entrySet().iterator();
while (it.hasNext()) {
@SuppressWarnings("rawtypes")
Map.Entry entry = (Map.Entry) it.next();
String key = String.valueOf(entry.getKey());
String value = String.valueOf(entry.getValue());
}
}
}
/**
* 回写数据
*
* @Author 鹿少年 2020年9月8日 上午11:39:49
* @param filePath
* @param sheetName
* @param caseId 读取行
* @param titleName 根据title读取列
* @param result
*/
public static boolean writeBackData(String filePath, String sheetName, String caseId, String titleName,
String result) {
loaderExcel(filePath, sheetName);
// 下标为0开始
int cellnum = titleIdcolnumMapping.get(titleName);
int rownum = caseIdRownumMapping.get(caseId);
FileOutputStream out = null;
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
Sheet sheet = workbook.getSheet(sheetName);
Row row = sheet.getRow(rownum);
Cell cell = row.getCell(cellnum, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
cell.setCellValue(result);
out = new FileOutputStream(new File(filePath));
workbook.write(out);
return true;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
}
return false;
} /**
* Excel数据读取 获取指定区域的数据 存在空区域
*
* @param datas
* @return
*/
public static Object[][] getExcelData(List<Map<String, String>> datas) {
int length = datas.size();
Object[][] ob = new Object[length][];
for (int i = 0; i < length; i++) {
int j = 0;
Map<String, String> map = datas.get(i);
for (String key : map.keySet()) {
if (map.get(key) != null && map.get(key).length() >= 1) {
ob[i][j++] = map.get(key);
}
}
}
return ob;
}
/**
* 读取Excel文件
*
* @return
* @throws IOException
* @throws EncryptedDocumentException
*/
public static List<Map<String, String>> readExcel(String filePath, String sheetName) {
List<Map<String, String>> list = new LinkedList<Map<String, String>>();
List<String> listname = new LinkedList<String>();
Sheet sheet = getSheet(filePath, sheetName);
// 获取excel文件行数
int rownum = sheet.getLastRowNum();
// 得到row对象
Row row = sheet.getRow(0);
int colnum = row.getLastCellNum();
for (int i = 0; i < colnum; i++) {
listname.add(i, getCellFormatValue(row, i));
}
log.info("Excel列标题数据:" + listname.toString());
for (int i = 1; i <= rownum; i++) {
Row lrow = sheet.getRow(i);
Map<String, String> map = new LinkedHashMap<String, String>();
for (int j = 0; j < colnum; j++) {
map.put(listname.get(j), getCellFormatValue(lrow, j));
}
list.add(map);
}
log.info("获取到excel数据:" + list);
return list;
}
/**
* 得到列表头
*
* @Author 鹿少年 2020年9月8日 上午11:00:51
* @param filePath
* @param sheetName
* @return
* @throws IOException
* @throws EncryptedDocumentException
*/
public static String[] getTittle(String filePath, String sheetName) throws FileNotFoundException {
File file = new File(filePath);
if (!file.exists()) {
try {
throw new FileNotFoundException("Excel文件不存在");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
InputStream ins;
Sheet sheet = null;
try {
ins = new FileInputStream(new File(filePath));
Workbook workbook = WorkbookFactory.create(ins);
sheet = workbook.getSheet(sheetName);
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
Row row = sheet.getRow(0);
String[] title = null;
if (IsNotEmptyRow(row)) {
// 得到的列数
int colnum = row.getLastCellNum();
title = new String[colnum];
for (int i = 0; i < colnum; i++) {
Cell cell = row.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue().substring(0, cell.getStringCellValue().indexOf("("));
title[i] = value;
}
}
return title;
}
/**
* 判断row是否为空
*
* @Author 鹿少年 2020年9月8日 下午2:10:33
* @param row
* @return
*/
private static boolean IsNotEmptyRow(Row row) {
if (row != null) {
for (int i = 0; i < row.getLastCellNum(); i++) {
String value = getCellFormatValue(row, i);
if (value != null && value.trim().length() > 0) {
return true;
}
}
}
return false;
}
/**
* 得到Workbook
*
* @Author 鹿少年 2020年9月8日 下午12:16:29
* @param filePath
* @return
*/
public static Workbook getWorkbook(String filePath) {
File file = new File(filePath);
if (!file.exists()) {
try {
throw new FileNotFoundException("Excel文件不存在");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
InputStream ins = null;
Workbook workbook = null;
try {
ins = new FileInputStream(new File(filePath));
workbook = WorkbookFactory.create(ins);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (ins != null) {
try {
ins.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return workbook;
}
/**
* 得到sheet
*
* @Author 鹿少年 2020年9月8日 上午11:07:18
* @param filePath
* @param sheetName
* @return
*/
public static Sheet getSheet(String filePath, String sheetName) {
File file = new File(filePath);
if (!file.exists()) {
try {
throw new FileNotFoundException("Excel文件不存在");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
InputStream ins;
Sheet sheet = null;
try {
ins = new FileInputStream(new File(filePath));
Workbook workbook = WorkbookFactory.create(ins);
sheet = workbook.getSheet(sheetName);
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
return sheet;
}
类似资料：