package com.lxw.test3d.Service;
|
|
import com.lxw.test3d.pojo.NjHomeConfig;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
public class ExcelReader {
|
private String filePath;
|
private String sheetName;
|
private Workbook workBook;
|
private Sheet sheet;
|
private List<String> columnHeaderList;
|
private List<List<String>> listData;
|
private List<Map<String, String>> mapData;
|
private List<NjHomeConfig> listconfig;
|
private boolean flag;
|
|
public ExcelReader(String filePath, String sheetName) {
|
this.filePath = filePath;
|
this.sheetName = sheetName;
|
this.flag = false;
|
this.load();
|
}
|
|
private void load() {
|
FileInputStream inStream = null;
|
try {
|
inStream = new FileInputStream(new File(filePath));
|
workBook = WorkbookFactory.create(inStream);
|
sheet = workBook.getSheet(sheetName);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
try {
|
if (inStream != null) {
|
inStream.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
private String getCellValue(Cell cell) {
|
String cellValue = "";
|
DataFormatter formatter = new DataFormatter();
|
if (cell != null) {
|
/*switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_NUMERIC:
|
if (DateUtil.isCellDateFormatted(cell)) {
|
cellValue = formatter.formatCellValue(cell);
|
} else {
|
double value = cell.getNumericCellValue();
|
int intValue = (int) value;
|
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
|
}
|
break;
|
case Cell.CELL_TYPE_STRING:
|
cellValue = cell.getStringCellValue();
|
break;
|
case Cell.CELL_TYPE_BOOLEAN:
|
cellValue = String.valueOf(cell.getBooleanCellValue());
|
break;
|
case Cell.CELL_TYPE_FORMULA:
|
cellValue = String.valueOf(cell.getCellFormula());
|
break;
|
case Cell.CELL_TYPE_BLANK:
|
cellValue = "";
|
break;
|
case Cell.CELL_TYPE_ERROR:
|
cellValue = "";
|
break;
|
default:
|
cellValue = cell.toString().trim();
|
break;
|
}*/
|
cellValue = cell.getStringCellValue();
|
}
|
return cellValue.trim();
|
}
|
|
private void getSheetData() {
|
listconfig=new ArrayList<>();
|
listData = new ArrayList<List<String>>();
|
mapData = new ArrayList<Map<String, String>>();
|
columnHeaderList = new ArrayList<String>();
|
int numOfRows = sheet.getLastRowNum() + 1;
|
for (int i = 0; i < numOfRows; i++) {
|
Row row = sheet.getRow(i);
|
Map<String, String> map = new HashMap<String, String>();
|
List<String> list = new ArrayList<String>();
|
NjHomeConfig config=new NjHomeConfig();
|
if (row != null) {
|
for (int j = 0; j < row.getLastCellNum(); j++) {
|
Cell cell = row.getCell(j);
|
if (i == 0) {
|
columnHeaderList.add(getCellValue(cell));
|
} else {
|
map.put(columnHeaderList.get(j), this.getCellValue(cell));
|
}
|
list.add(this.getCellValue(cell));
|
config.setConfigName("属性"+sheetName+(i+1));
|
config.setConfigFlag(0);
|
config.setConfigClass(0);
|
config.setConfigValue(this.getCellValue(cell));
|
config.setConfigType(getType(sheetName));
|
}
|
}
|
if (i > 0) {
|
mapData.add(map);
|
}
|
listconfig.add(config);
|
listData.add(list);
|
}
|
flag = true;
|
}
|
|
private Integer getType(String sheetName) {
|
int type=0;
|
switch (sheetName){
|
case "ac":type=1;break;
|
case "acdc":type=2;break;
|
case "dc":type=3;break;
|
case "fbs9100state":type=4;break;
|
case "rtstate":type=5;break;
|
default:type=0;break;
|
}
|
return type;
|
}
|
|
public String getCellData(int row, int col) {
|
if (row <= 0 || col <= 0) {
|
return null;
|
}
|
if (!flag) {
|
this.getSheetData();
|
}
|
if (listData.size() >= row && listData.get(row - 1).size() >= col) {
|
return listData.get(row - 1).get(col - 1);
|
} else {
|
return null;
|
}
|
|
}
|
|
public String getCellData(int row, String headerName) {
|
if (row <= 0) {
|
return null;
|
}
|
if (!flag) {
|
this.getSheetData();
|
}
|
if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
|
return mapData.get(row - 1).get(headerName);
|
} else {
|
return null;
|
}
|
}
|
|
|
public List getSheet() {
|
this.getSheetData();
|
//System.out.println("listData.size():"+listData.size());
|
/*for (int i=0;i<listconfig.size();i++){
|
System.out.println(listconfig.get(i).toString());
|
}*/
|
return listconfig;
|
}
|
public static void main(String[] args) {
|
ExcelReader eh = new ExcelReader("E:\\ffmpeg\\4.xls", "ac");
|
//System.out.println(eh.getCellData(1, 1));
|
// System.out.println(eh.getCellData(1, "test1"));
|
//System.out.println(eh.getCellData(2, 1));
|
// eh.getSheetData();
|
eh.getSheet();
|
|
}
|
}
|
