package cc.mrbird.febs.server.hr.util;
|
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import cn.hutool.core.util.StrUtil;
|
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
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;
|
|
public class PoiImportExcel {
|
|
private final static String excel2003L = ".xls"; // 2003- 版本的excel
|
private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
|
|
/**
|
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
|
*
|
* @param in,fileName
|
* @return
|
* @throws IOException
|
*/
|
public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
|
List<List<Object>> list = null;
|
|
// 创建Excel工作薄
|
Workbook work = getWorkbook(in, fileName);
|
if (null == work) {
|
throw new Exception("创建Excel工作薄为空！");
|
}
|
Sheet sheet = null;
|
Row row = null;
|
Cell cell = null;
|
|
list = new ArrayList<List<Object>>();
|
// 直接获取第一个sheet
|
sheet = work.getSheetAt(0);
|
if (sheet == null) {
|
return list;
|
}
|
// 取得Excel的总列数,总行数
|
int columns = 0;
|
Row firstRow = sheet.getRow((short) 0);
|
if (firstRow != null) {
|
columns = firstRow.getPhysicalNumberOfCells();
|
}
|
|
int rows = sheet.getPhysicalNumberOfRows();
|
List<Object> dataRow = null;
|
|
// 首行为定义标题的行，数据从第2行开始
|
for (int i = 1; i < rows; i++) {
|
dataRow = new ArrayList<Object>();
|
// 获取行
|
row = sheet.getRow(i);
|
if (row != null) {
|
// columns=row.getPhysicalNumberOfCells();//不在这里设置，通过firstRow来获取列数信息。
|
for (int j = 0; j < columns; j++) {
|
// 获取某行某列的某一个单元格
|
cell = row.getCell(j);
|
// 往dataRow存值
|
dataRow.add(getCellValue(cell));
|
}
|
list.add(dataRow);
|
} else {
|
list.add(new ArrayList<>());
|
}
|
}
|
|
// 遍历当前sheet中的所有行
|
/*
|
* int countCellNum = 0; for (int j = sheet.getFirstRowNum(); j <=
|
* sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null ||
|
* row.getFirstCellNum() == j) { if (row != null) countCellNum =
|
* row.getLastCellNum(); continue; }
|
*
|
* // 遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row
|
* .getFirstCellNum(); y <= countCellNum; y++) { cell = row.getCell(y);
|
* li.add(this.getCellValue(cell));
|
*
|
* } list.add(li); }
|
*/
|
|
work.close();
|
return list;
|
}
|
|
/**
|
* 描述：获取IO流中的数据，组装成List<List<List<Object>>>对象,多个sheet
|
*
|
* @param in,fileName
|
* @return
|
* @throws IOException
|
*/
|
public static List<List<List<Object>>> getMulitListByExcel(InputStream in, String fileName) throws Exception {
|
List<List<Object>> list = null;
|
|
// 创建Excel工作薄
|
Workbook work = getWorkbook(in, fileName);
|
if (null == work) {
|
throw new Exception("创建Excel工作薄为空！");
|
}
|
List<List<List<Object>>> returnList = new ArrayList<List<List<Object>>>();
|
for (Sheet sheet : work) {
|
Row row = null;
|
Cell cell = null;
|
|
list = new ArrayList<List<Object>>();
|
if (sheet == null) {
|
continue;
|
}
|
// 取得Excel的总列数,总行数
|
int columns = 0;
|
Row firstRow = sheet.getRow((short) 0);
|
if (firstRow != null) {
|
columns = firstRow.getPhysicalNumberOfCells();
|
}
|
|
int rows = sheet.getPhysicalNumberOfRows();
|
List<Object> dataRow = null;
|
|
// 首行为定义标题的行，数据从第2行开始
|
for (int i = 1; i < rows; i++) {
|
dataRow = new ArrayList<Object>();
|
// 获取行
|
row = sheet.getRow(i);
|
|
cell = row.getCell(0);
|
if ( StrUtil.isBlank(getCellValue(cell).toString())){
|
break;
|
}
|
|
if (row != null) {
|
// columns=row.getPhysicalNumberOfCells();//不在这里设置，通过firstRow来获取列数信息。
|
for (int j = 0; j < columns; j++) {
|
// 获取某行某列的某一个单元格
|
cell = row.getCell(j);
|
|
// 往dataRow存值
|
dataRow.add(getCellValue(cell));
|
}
|
list.add(dataRow);
|
} else {
|
list.add(new ArrayList<>());
|
}
|
}
|
|
work.close();
|
returnList.add(list);
|
}
|
|
return returnList;
|
}
|
|
/**
|
* 描述：根据文件后缀，自适应上传文件的版本
|
*
|
* @param inStr,fileName
|
* @return
|
* @throws Exception
|
*/
|
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
|
Workbook wb = null;
|
String fileType = fileName.substring(fileName.lastIndexOf("."));
|
if (excel2003L.equals(fileType)) {
|
wb = new HSSFWorkbook(inStr); // 2003-
|
} else if (excel2007U.equals(fileType)) {
|
wb = new XSSFWorkbook(inStr); // 2007+
|
} else {
|
throw new Exception("解析的文件格式有误！");
|
}
|
return wb;
|
}
|
|
/**
|
* 描述：对表格中数值进行格式化
|
*
|
* @param cell
|
* @return
|
*/
|
@SuppressWarnings("deprecation")
|
public static Object getCellValue(Cell cell) {
|
try {
|
Object value = null;
|
|
if (cell == null) {
|
value = "";
|
return value;
|
}
|
|
DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化
|
DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
|
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_STRING:
|
value = cell.getRichStringCellValue().getString();
|
break;
|
case Cell.CELL_TYPE_NUMERIC:
|
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
|
|
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")
|
|| cell.getCellStyle().getDataFormat() == 176) {
|
sdf = new SimpleDateFormat("HH:mm");
|
}
|
Date date = cell.getDateCellValue();
|
value = sdf.format(date);
|
|
} else if (cell.getCellStyle().getDataFormat() == 58) {
|
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
|
SimpleDateFormat sdf2 = new SimpleDateFormat("MM-dd");
|
double cellValue = cell.getNumericCellValue();
|
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cellValue);
|
value = sdf2.format(date);
|
} else if (cell.getCellStyle().getDataFormat() == 183 || cell.getCellStyle().getDataFormat() == 177
|
|| cell.getCellStyle().getDataFormat() == 181) {
|
// 处理自定义日期格式：y年m月d日(通过判断单元格的格式id解决，id的值是183)
|
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
|
Date date = cell.getDateCellValue();
|
value = sdf2.format(date);
|
} else {
|
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
|
value = df.format(cell.getNumericCellValue());
|
} else {
|
value = df2.format(cell.getNumericCellValue());
|
}
|
}
|
|
break;
|
case Cell.CELL_TYPE_BOOLEAN:
|
value = cell.getBooleanCellValue();
|
break;
|
case Cell.CELL_TYPE_BLANK:
|
value = "";
|
break;
|
default:
|
value = cell.getStringCellValue();
|
break;
|
}
|
return value;
|
|
} catch (Exception e) {
|
return null;
|
}
|
|
}
|
|
}
|
