import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
/**
* @author Lius
* @date 2018/10/26 17:37
* @description excel 表格工具
*/
public class ExcelUtils {
/**
* 构造方法
*/
public ExcelUtils() {
}
/**
* 判断excel格式是否正确
*
* @author Lius
* @date 2018/10/26 17:37
*/
private static boolean validateExcel(String filePath) {
// XLS
boolean flag = true;
String fileType = FileUtils.getFileTypeByPath(filePath);
boolean isExcel = false;
if (FileType.XLS.equals(fileType) || FileType.XLSX.equals(fileType)) {
isExcel = true;
}
if (null == filePath || !isExcel) {
flag = false;
throw new RuntimeException("文件不是excel类型");
}
// 判断文件是否存在
if (!FileUtils.isExistFile(filePath)) {
flag = false;
throw new RuntimeException("文件不存在");
}
return flag;
}
/**
* 根据文件类型读取文件
*
* @author Lius
* @date 2018/10/27 10:56
*/
public static List> readExcel(String filePath) {
List> dataList = new ArrayList>();
InputStream is = null;
Workbook wb = null;
try {
// 验证文件是否合法
if (!validateExcel(filePath)) {
return null;
}
File file = new File(filePath);
is = new FileInputStream(file);
String fileType = FileUtils.getFileTypeByPath(filePath);
if (FileType.XLS.equals(fileType)) {
is.close();
return getSheet(new HSSFWorkbook(is));
}
if (FileType.XLSX.equals(fileType)) {
is.close();
return getSheet(new XSSFWorkbook(is));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException ex) {
is = null;
ex.printStackTrace();
}
}
}
return dataList;
}
/**
* 获得每一个工作表格内容
*
* @author Lius
* @date 2018/10/27 11:14
*/
private static List> getSheet(Workbook workbook) {
List> dataList = new ArrayList>();
// 得到第一个shell
Sheet sheet = workbook.getSheetAt(0);
// 得到Excel的行数
int totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数
int totalCells = 0;
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
// 循环excel的行
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
List rowLst = new ArrayList();
// 循环Excel的列
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
CellType type = cell.getCellTypeEnum();
switch (type) {
// 数字
case NUMERIC:
// 科学计数法处理
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);
cellValue = nf.format(cell.getNumericCellValue()) + "";
break;
// 字符串
case STRING:
cellValue = cell.getStringCellValue();
break;
// Boolean
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
// 公式
case FORMULA:
cellValue = cell.getCellFormula() + "";
break;
// 空值
case _NONE:
cellValue = "";
break;
// 故障
case ERROR:
cellValue = "error";
break;
default:
cellValue = "null";
break;
}
}
rowLst.add(cellValue);
}
// 保存第r行的第c列
dataList.add(rowLst);
}
return dataList;
}
特别说明: