java解析excel

public class POIUtil {

/**

* 读取excel

* @param file file对象

* @return

* @throws IOException

*/

public static HashMap<String, List<Map<String, String>>> readExcelByFile(File file) throws IOException{

return readExcel(file,file.getName());

}

 

/**

* 读取excel springmvc解析上传的文件

* @param multipartFile springmvc封装参数

* @return

* @throws IOException

*/

public static HashMap<String, List<Map<String, String>>> readExcelByMultipartFile(MultipartFile multipartFile) throws IOException{

CommonsMultipartFile cf = (CommonsMultipartFile)multipartFile;

DiskFileItem fi = (DiskFileItem) cf.getFileItem();

File file = fi.getStoreLocation();

return readExcel(file,multipartFile.getOriginalFilename());

}

 

/**

* 读取excel

* @param file file对象

* @param fileName 带后缀文件名

* @return

* @throws IOException

*/

public static HashMap<String, List<Map<String, String>>> readExcel(File file, String fileName) throws IOException {

Workbook workbok = getWorkbok(file,fileName);

int numberOfSheets = workbok.getNumberOfSheets();

HashMap<String, List<Map<String, String>>> sheetMap = new LinkedHashMap<>();

for (int i = 0;i<numberOfSheets;i++) {

Sheet sheet = workbok.getSheetAt(i);

int rowNum = sheet.getLastRowNum();

 

//获取标题行列数

Row row = sheet.getRow(0);

if (row == null){

continue;

}

int colNum = row.getPhysicalNumberOfCells();

 

List<Map<String, String>> rowsList = new ArrayList<>();

 

boolean flag = false;

for (int j = 1; j <= rowNum; j++) {

 

Map<String, String> rowMap = new LinkedHashMap<>();

 

row = sheet.getRow(j);

int k = 0;

char columnName = 'A';

while (k < colNum) {

Cell cell = row.getCell(k);

if (cell != null && !"".equals(cell.toString())) {

String cellValue = getCellValue(cell,workbok);

rowMap.put("cell" + (char)(columnName + (k)), cellValue);

}

k++;

}

if (rowMap.size() > 0){

flag = true;

rowMap.put("rowNumber",String.valueOf(j+1));

rowsList.add(rowMap);

}

}

if (flag) {

sheetMap.put("sheet" + (i + 1), rowsList);

}

}

return sheetMap;

}

 

/**

* 获取单元格值

* @param cell

* @param workbok

* @return

*/

public static String getCellValue(Cell cell,Workbook workbok){

Object value = null;

CellType cellTypeEnum = cell.getCellTypeEnum();

if (cellTypeEnum == CellType.STRING){

value = cell.getStringCellValue();

}else if(cellTypeEnum == CellType.NUMERIC){

//如果为时间格式的内容

if (HSSFDateUtil.isCellDateFormatted(cell)) {

//注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

value=sdf.format(HSSFDateUtil.getJavaDate(cell.

getNumericCellValue())).toString();

} else {

value = new DecimalFormat("0").format(cell.getNumericCellValue());

}

}else if(cellTypeEnum == CellType.BOOLEAN){

value = cell.getBooleanCellValue() + "";

}else if(cellTypeEnum == CellType.FORMULA){

//获取函数

// value = cell.getCellFormula() + "";

 

//获取函数结果

if (workbok instanceof HSSFWorkbook) {

HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator((HSSFWorkbook) workbok);

value = eva.evaluate(cell).getNumberValue();

}else if (workbok instanceof XSSFWorkbook){

XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator((XSSFWorkbook) workbok);

value = eva.evaluate(cell).getNumberValue();

}

}else if(cellTypeEnum == CellType.BLANK){

value = null;

}else if(cellTypeEnum == CellType.ERROR){

value = "非法字符";

}else{

value = "未知类型";

}

 

//过时方法

/*switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_NUMERIC: // 数字

//如果为时间格式的内容

if (HSSFDateUtil.isCellDateFormatted(cell)) {

//注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

value=sdf.format(HSSFDateUtil.getJavaDate(cell.

getNumericCellValue())).toString();

break;

} else {

value = new DecimalFormat("0").format(cell.getNumericCellValue());

}

break;

case HSSFCell.CELL_TYPE_STRING: // 字符串

value = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean

value = cell.getBooleanCellValue() + "";

break;

case HSSFCell.CELL_TYPE_FORMULA: // 公式

value = cell.getCellFormula() + "";

break;

case HSSFCell.CELL_TYPE_BLANK: // 空值

value = "";

break;

case HSSFCell.CELL_TYPE_ERROR: // 故障

value = "非法字符";

 

break;

default:

value = "未知类型";

break;

}*/

 

return value == null ? null : String.valueOf(value);

}

 

/**

* 根据后缀获取不同版本解析对象

* @param file file对象

* @param fileName 带后缀文件名

* @return

* @throws IOException

*/

public static Workbook getWorkbok(File file,String fileName) throws IOException{

Workbook wb = null;

FileInputStream in = new FileInputStream(file);

if(fileName.endsWith("xls")){ //Excel&nbsp;2003

wb = new HSSFWorkbook(in);

}else if(fileName.endsWith("xlsx")){ // Excel 2007/2010

wb = new XSSFWorkbook(in);

}

return wb;

}

}