package com.sinosoft.em.rushRepairTask;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Poi导入excel工具类
*
* @author Layduo
*
*/
public class ImportExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelUtil.class);
private static final boolean STATUS_TRUE = true;
private static final boolean STATUS_FALSE = false;
private static final String RESPONSE_STATUS = "status";
private static final String RESPONSE_MSG = "msg";
private static final Object RESPONSE_DATA = "data";
/**
* 读取出filePath中的所有数据信息
*
* @param filePath
* excel文件的绝对路径
*
*/
@SuppressWarnings("resource")
public static JSONObject getDataFromExcel(String filePath, String[] header) {
JSONObject resultJson = new JSONObject();
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
// 判断是否为excel类型文件
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
LOGGER.error("<------------------文件不是excel类型!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "文件不是excel类型!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
FileInputStream inputStream = null;
Workbook wookbook = null;
int flag = 0;
// 获取一个绝对地址的流
try {
inputStream = new FileInputStream(filePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
LOGGER.error("<------------------找不到该路径下的文件!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "找不到该路径下的文件!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
try {
// 2003版本的excel,用.xls结尾
wookbook = new HSSFWorkbook(inputStream);// 得到工作簿
} catch (Exception e) {
try {
// 这里需要重新获取流对象,因为前面的异常导致了流的关闭
inputStream = new FileInputStream(filePath);
// 2007版本的excel,用.xlsx结尾
wookbook = new XSSFWorkbook(inputStream);// 得到工作簿
} catch (IOException e1) {
e1.printStackTrace();
LOGGER.error("<------------------创建excel工作簿出错!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "创建excel工作簿出错!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
}
// 得到一个工作表
Sheet sheet = wookbook.getSheetAt(0);
// 获得表头
Row rowHead = sheet.getRow(1);
// 根据不同的data放置不同的表头
Map<Object, Integer> headMap = new HashMap<Object, Integer>();
// 判断表头是否正确
if (rowHead.getPhysicalNumberOfCells() != header.length) {
LOGGER.error("<------------------表头列数与要导入的数据库不对应!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "表头列数与要导入的数据库不对应!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
try {
// ----------------这里根据你的表格有多少列
while (flag < header.length) {
Cell cell = rowHead.getCell(flag);
//if (getRightTypeCell(cell).toString().equals(titleName[flag])) {
headMap.put(header[flag], flag);
//}
flag++;
}
} catch (Exception e) {
e.printStackTrace();
LOGGER.error("<------------------表头不合规范,请修改后重新导入!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "表头不合规范,请修改后重新导入!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
// 获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
if (0 == totalRowNum) {
LOGGER.error("<------------------Excel内没有数据!------------------>");
resultJson.put(RESPONSE_STATUS, STATUS_FALSE);
resultJson.put(RESPONSE_MSG, "Excel内没有数据!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
// 获得所有数据
for (int i = 2; i <= totalRowNum; i++) {
// 获得第i行对象
Row row = sheet.getRow(i);
Map<String, Object> map = new HashMap<String, Object>();
for (int j = 0; j < header.length; j++) {
Cell cell = row.getCell(headMap.get(header[j]));
String data = StringUtils.EMPTY;
if (getRightTypeCell(cell) instanceof String) {
data = (String) getRightTypeCell(cell);
}
map.put(header[j], data.split(":")[0].trim());
}
resultList.add(map);
}
resultJson.put(RESPONSE_STATUS, STATUS_TRUE);
resultJson.put(RESPONSE_MSG, "导入成功!");
resultJson.put(RESPONSE_DATA, resultList);
return resultJson;
}
/**
*
* @param cell
* 一个单元格的对象
* @return 返回该单元格相应的类型的值
*/
public static Object getRightTypeCell(Cell cell) {
Object object = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: {
object = cell.getStringCellValue();
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
object = cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_FORMULA: {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
object = cell.getNumericCellValue();
break;
}
case Cell.CELL_TYPE_BLANK: {
cell.setCellType(Cell.CELL_TYPE_BLANK);
object = cell.getStringCellValue();
break;
}
}
return object;
}
public static void main(String[] args) {
String filePath = "D://layduo/开发文件/rushcar.xlsx";
String header [] = {"CAR_NAME", "COMP_ID", "CAR_STATE"};
//String titleName [] = {"车牌号码", "区局单位", "车辆状态"};
JSONObject resultList = getDataFromExcel(filePath, header);
System.out.println(resultList.toString());
}
}