package function;
import java.io.IOException;
import java.util.*;
import java.util.Map.Entry;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class AnalyExcel {
public static void main(String[] args) throws IOException {
AnalyExcel function = new AnalyExcel();
Map<String, Map<String, Map<String, String>>> sheetMap =
function.readXlsx();
System.out.println(sheetMap.toString());
}
@SuppressWarnings({ "deprecation" })
public Map<String, Map<String, Map<String, String>>> readXlsx() throws IOException {
String path = "F:\\file.xlsx";
// 指定解析的excel文件地址
XSSFWorkbook wb = new XSSFWorkbook(path);
// 创建sheetMap用来存放每一个sheet表中的所有数据
Map<String, Map<String, Map<String, String>>> sheetMap = new LinkedHashMap<>();
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
// 遍历每个sheet表
XSSFSheet sheet = wb.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
Map<String, Map<String, String>> dataMap = new LinkedHashMap<String, Map<String, String>>();
// 将sheetName和dataMap放进sheetMap中
sheetMap.put(sheet.getSheetName(), dataMap);
// 遍历行
Map<String, String> valueMap = new LinkedHashMap<String, String>();
dataMap.put(sheet.getRow(0).getCell(sheet.getRow(0).getLastCellNum() - 1).toString(), valueMap);
for (int rowNum = sheet.getFirstRowNum() + 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
XSSFRow row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
List<String> cellList = new ArrayList<String>();
// 遍历列
for (int cellNum = row.getFirstCellNum() + 1; cellNum <= row.getLastCellNum(); cellNum++) {
XSSFCell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
if (cell != null) {
cellList.add(getValuexlsx(cell));
}
}
valueMap.put(cellList.get(0), cellList.get(cellList.size() - 1));
}
}
return sheetMap;
}
// 设置表格里面内容,全部转化为string类型
public String getValuexlsx(XSSFCell cell) {
String returnValue = "";
if (null != cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC: // 数字
Double doubleValue = cell.getNumericCellValue();
String str = doubleValue.toString();
if (str.contains(".0")) {
str = str.replace(".0", "");
}
Integer intValue = Integer.parseInt(str);
returnValue = intValue.toString();
break;
case XSSFCell.CELL_TYPE_STRING: // 字符串
returnValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN: // 布尔
Boolean booleanValue = cell.getBooleanCellValue();
returnValue = booleanValue.toString();
break;
case XSSFCell.CELL_TYPE_BLANK: // 空值
returnValue = "";
break;
case XSSFCell.CELL_TYPE_FORMULA: // 公式
returnValue = cell.getCellFormula();
break;
case XSSFCell.CELL_TYPE_ERROR: // 故障
returnValue = "";
break;
default:
System.out.println("未知类型");
break;
}
}
return returnValue;
}
/**
* @throws IOException
*
*/
// 遍历Map
public Map<String, String> getData(String sheetName) throws IOException {
Map<String, Map<String, Map<String, String>>> sheetMap = readXlsx();
Map<String, String> dataMap = null;
Iterator<Entry<String, Map<String, Map<String, String>>>> iter = sheetMap.entrySet().iterator();
while (iter.hasNext()) {
Entry<String, Map<String, Map<String, String>>> entry = iter.next();
if (entry.getKey().equals(sheetName)) {
Map<String, Map<String, String>> groupMap = entry.getValue();
Iterator<Entry<String, Map<String, String>>> iterGroup = groupMap.entrySet().iterator();
while (iterGroup.hasNext()) {
Entry<String, Map<String, String>> data = iterGroup.next();
dataMap = data.getValue();
return dataMap;
}
}
}
return dataMap;
}
}