package com.xinhua.xpm.execute.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.druid.util.StringUtils;
/**
* @author minqiang
* @version 创建时间 2018年8月8日 下午1:24:54
*/
public class ReadExcelUtil {
@SuppressWarnings("resource")
public static Map<Integer, List<List<String>>> readExcel(File excelFile) throws IOException {
Map<Integer, List<List<String>>> excelDatas = new HashMap<Integer, List<List<String>>>();
InputStream in = new FileInputStream(excelFile);
Workbook workbook = null;
if (excelFile.getName().endsWith("xlsx")) {
workbook = new XSSFWorkbook(in);
} else {
workbook = new HSSFWorkbook(in);
}
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {// 遍历excel中sheet
List<List<String>> currentSheetDatas = new ArrayList<List<String>>();
Sheet currentSheet = workbook.getSheetAt(sheetNum);
// 读取sheet后缀名称为-MX的文件数据
if (currentSheet == null || !currentSheet.getSheetName().endsWith("-MX")) {
continue;
}
here: for (int rowNum = 5; rowNum <= currentSheet.getLastRowNum(); rowNum += 2) { // 第5行开始读取
List<String> rowDatas = new ArrayList<String>();
if (rowNum > 6 && rowNum < 11) { // 7到10行之间跳过读取
continue;
}
for (int columnNum = 9; columnNum <= 50; columnNum++) { // 第9列开始读取
// 获取表格中合并单元格的数量
int sheetMergeCount = currentSheet.getNumMergedRegions();
// 获取合并单元格中的数据
for (int sheetMergeNum = 0; sheetMergeNum < sheetMergeCount; sheetMergeNum++) {
CellRangeAddress cellRangeAddress = currentSheet.getMergedRegion(sheetMergeNum);
int firstColumn = cellRangeAddress.getFirstColumn(); // 合并单元格的开始列
int lastColumn = cellRangeAddress.getLastColumn(); // 合并单元格的结束列
int firstRow = cellRangeAddress.getFirstRow(); // 合并单元格的开始行
int lastRow = cellRangeAddress.getLastRow(); // 合并单元格的结束行
// 判断读取的单元格是否为合并单元格,并且获取合并单元格的数据
if (rowNum >= firstRow && rowNum <= lastRow) {
if (columnNum >= firstColumn && columnNum <= lastColumn) {
Row row = currentSheet.getRow(firstRow);
Cell cell = row.getCell(firstColumn);
String cellData = getCellData(cell);
// 如果读取到下一行第一个单元格数据为空,当前单元格的解析结束,跳出进行下一次遍历
if (columnNum == 9 && StringUtils.isEmpty(cellData)) {
break here;
}
rowDatas.add(trimStr(cellData));
// 行和列赋值为合并单元格的最后一行和最后一列,开启下一个单元格的数据读取
rowNum = lastRow;
columnNum = lastColumn;
break;
}
}
}
}
currentSheetDatas.add(rowDatas);
}
excelDatas.put(sheetNum + 1, currentSheetDatas);
}
return excelDatas;
}
/**
* 获取单元格的数据
*
* @param cell
* @return
*/
private static String getCellData(Cell cell) {
String cellData = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
cellData = new DecimalFormat("0").format(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellData = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellData = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellData = cell.getCellFormula().toString();
break;
default:
break;
}
return cellData;
}
/**
* 字符串修剪 去除所有空白符号 , 问号 , 中文空格
*
* @param str
* @return
*/
private static String trimStr(String cellData) {
return cellData.replaceAll("[\\s\\?]", "").replace(" ", "");
}
}