package zj.excel.util;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;
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 zj.check.util.CheckUtil;
import zj.excel.bean.DatasKeySheets;
import zj.excel.bean.Excel;
import zj.excel.bean.ExcelI;
import zj.excel.bean.ExcelTemplate;
import zj.excel.bean.ReadExcelI;
import zj.excel.bean.SheetDatas;
import zj.java.util.JavaUtil;
import zj.type.TypeUtil;
/**
* 概況 ：Excel/xls/xlsx<br>
*
* @version 1.00 （2011.12.02）
* @author SHNKCS 張軍 {@link <a target=_blank href="http://www.shanghaijiadun.com">上海加盾信息科技有限公司</a> <a target=_blank href="http://www.dlhighland.cn">張軍個人網站</a> <a target=_blank href="http://user.qzone.qq.com/360901061/">張軍QQ空間</a>} <br>
* * <br>
* --------------------------讀取excel數據------------------------------<br>
*
* <pre>
* try {
* Excel readExcel = new Excel();
* readExcel.setFilePath("E:/document/zj-utils/excel/寫入excel" + "1" + ".xls");
* // readExcel.setSheetValue(new String[] { "Sheet1" });
* DatasKeySheets datasKeySheets = null;
* // RE re = new RE();
* // datasKeySheets = ExcelUtil.readExcel(readExcel,re);
* datasKeySheets = ExcelUtil.readExcel(readExcel);
* List<SheetDatas> sheetDatas = datasKeySheets.getSheetDatas();
* for (SheetDatas datas : sheetDatas) {
* // =======================
* System.out.println("sheet:" + datas.getSheetIndex() + "\t\t" + datas.getSheetName() + "\t\t" + datas.getSheet());
* // 循環sheet
* // 獲取某個sheet的行列數據
* List<List<SheetData>> datasLst = datas.getRowsDataLst();
* if (datasLst != null) {
* // 循環某個sheet的行數據
* for (List<SheetData> dataLst : datasLst) {
* // 循環某個sheet的列數據
* for (SheetData data : dataLst) {
* if (data != null) {
* System.out.print(data.getValue() + "\t\t");
* }
* }
* System.out.println();
* }
* }
* }
* } catch (Exception e) {
* e.printStackTrace();
* }
* </pre>
*
* <pre>
* <br>------------------------------導出excel數據------------------------------<br>
* try {
* // 設置數據
* for (int ii = 0; ii < 10; ii++) {
* List<SheetDatas> sheetDatas = new ArrayList<SheetDatas>();
* for (int k = 0; k < 10; k++) {
* // 所有行列數據對象
* SheetDatas sheetData = new SheetDatas();
* sheetData.setSheetName("sheet名." + k);
* sheetDatas.add(sheetData);
* // 所有行數據
* List<List<SheetData>> rowsDataLst = new ArrayList<List<SheetData>>();
* // 設置所有行數據
* sheetData.setRowsDataLst(rowsDataLst);
* // 所有列數據
* List<SheetData> columnsDataLst = null;
* // 所有單元格數據
* SheetData data = null;
* // 實例化所有行列數據
* for (int i = 0; i < 10000; i++) {
* // 設置第i行數據
* columnsDataLst = new ArrayList<SheetData>();
* rowsDataLst.add(columnsDataLst);
* // 添加第j行數據
* for (int j = 0; j < 10; j++) {
* data = new SheetData();
* if (j == 5) {
* data.setValue(i * j);
* } else {
* data.setValue("行" + i + "列" + j);
* }
* columnsDataLst.add(data);
* }
* }
* }
* // 導出excel設置
* Excel excel = new Excel();
* excel.setFilePath("E:/document/zj-utils/excel/寫入excel" + ii + ".xls");
* ExcelUtil.writeExcel(sheetDatas, excel);
* }
* } catch (Exception e) {
* e.printStackTrace();
* }
* </pre>
*/
public final class ExcelReadUtil implements Serializable {
private static final long serialVersionUID = 1L;
private static final Logger logger = Logger.getLogger(ExcelReadUtil.class.getName());
private ExcelReadUtil() {
}
/**
* 讀取excel
*
* @param excel
* excel對象
* @return 所有行列數據對象
* @throws Exception
*/
public static final List<String> readExcelSheetNames(final Excel excel) throws Exception {
List<String> sheetNames = new ArrayList<String>();
excel.initWorkbook();
Workbook wb = excel.getWb();
int sheetCount = wb.getNumberOfSheets();
// 循環所有sheet
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
// 獲得sheet工作簿Sheet
Sheet sheet = wb.getSheetAt(sheetIndex);
sheetNames.add(sheet.getSheetName());
}
return sheetNames;
}
/**
* 根據標題獲取excel數據
*
* @param excel
* 數據對象
* @param rowsValues
* 數據
* @return
*/
public static final void readTitleRowValues(Excel excel, SheetDatas datas) {
Object objTitles = excel.getObjTitles();
String titleSplit = excel.getTitleSplit();
int titleRowIndex = excel.getTitleRow();
List<List<Object>> rowsValues = datas.getRowValues();
datas.setTitleRowValues(readTitleRowValues(objTitles, titleSplit, titleRowIndex, rowsValues));
}
/**
* 根據標題獲取excel數據
*
* @param objTitles
* 標題對象
* @param titleSplit
* 標題分割符
* @param titleRowIndex
* 標題行索引
* @param rowsValues
* 值
* @return
*/
@SuppressWarnings("unchecked")
public static final List<Map<String, Object>> readTitleRowValues(Object objTitles, String titleSplit, int titleRowIndex, List<List<Object>> rowsValues) {
// 行值->{標題:值}
List<Map<String, Object>> titleRowValues = new ArrayList<Map<String, Object>>();
if (rowsValues != null) {
// 標題對應的數據索引
Map<String, Integer> titlesIndex = new HashMap<String, Integer>();
if (objTitles instanceof String[]) {
String[] titleKeys = (String[]) objTitles;
for (String title : titleKeys) {
titlesIndex.put(title, -1);
}
} else if (objTitles instanceof Collection) {
Collection<String> titleKeys = (Collection<String>) objTitles;
for (String title : titleKeys) {
titlesIndex.put(title, -1);
}
} else {
return titleRowValues;
}
// System.out.println("總行數:" + datasLst.size());
if (rowsValues.size() > 0) {
// 設置標題項
List<Object> titleDatas = rowsValues.get(titleRowIndex);
for (int i = 0; i < titleDatas.size(); i++) {
String value = JavaUtil.objToStr(titleDatas.get(i));
for (String title : titlesIndex.keySet()) {
String[] titles = JavaUtil.split(title, titleSplit);
String thisTitle = titles[0];
if (thisTitle.equals(value)) {
// 如果標題相同,設置列索引
titlesIndex.put(title, i);
break;
}
}
// System.out.print(value + "\t\t");
}
// System.out.println();
// System.out.println(titlesIndex);
for (int i = 0; i < rowsValues.size(); i++) {
if (i == titleRowIndex) {
// 去除標題行
continue;
}
List<Object> rowDatas = rowsValues.get(i);
Map<String, Object> rowValue = new HashMap<String, Object>();
// 添加數據
titleRowValues.add(rowValue);
for (String title : titlesIndex.keySet()) {
// 獲取列索引
Integer colIndex = titlesIndex.get(title);
// 根據列索引獲取數據
Object value = null;
if (colIndex != -1) {
if (rowDatas.size()>colIndex){
value = rowDatas.get(colIndex);
}
}
String[] titles = JavaUtil.split(title, titleSplit);
String thisTitle = titles[0];
if (titles.length > 1) {
// 別名
thisTitle = titles[1];
}
// 設置行數據{title,value}
rowValue.put(thisTitle, value);
}
}
// System.out.println(rowValues);
}
}
return titleRowValues;
}
/**
* 讀取excel
*
* @param excel
* excel對象
* @return 所有行列數據對象
* @throws Exception
*/
public static final DatasKeySheets readExcel(final Excel excel) throws Exception {
return readExcel(excel, null);
}
/**
* 讀取excel
*
* @param excel
* excel對象
* @param excelI
* excelI接口
* @return 所有行列數據對象
* @throws Exception
*/
@SuppressWarnings("rawtypes")
public static final DatasKeySheets readExcel(final Excel excel, final ExcelI excelI) throws Exception {
excel.initWorkbook();
Workbook wb = excel.getWb();
// key:index/sheet/sheetName
DatasKeySheets datasKeySheets = new DatasKeySheets();
List<SheetDatas> sheetDatas = new ArrayList<SheetDatas>();
Sheet sheet = null;
int sheetCount = wb.getNumberOfSheets();
// 獲得sheet工作簿Sheet
Object sheetValue = excel.getSheetValue();
boolean isUseTitleData = excel.getObjTitles() != null;
if (sheetValue == null) {
// 循環所有sheet
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
// 獲得sheet工作簿Sheet
sheet = wb.getSheetAt(sheetIndex);
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(sheetIndex);
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
} else {
if (sheetValue instanceof String) {
String sheetName = String.valueOf(sheetValue);
sheet = wb.getSheet(sheetName);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(wb.getSheetIndex(sheet));
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
} else if (sheetValue instanceof Integer) {
Integer sheetIndex = Integer.parseInt(String.valueOf(sheetValue));
sheet = wb.getSheetAt(sheetIndex);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(sheetIndex);
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
} else if (sheetValue instanceof String[]) {
String[] sheetNames = (String[]) sheetValue;
for (String sheetName : sheetNames) {
sheet = wb.getSheet(sheetName);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(wb.getSheetIndex(sheet));
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
}
} else if (sheetValue instanceof int[]) {
int[] sheetIndexs = (int[]) sheetValue;
for (int a = 0; a < sheetIndexs.length; a++) {
sheet = wb.getSheetAt(a);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(a);
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
}
} else if (sheetValue instanceof Integer[]) {
Integer[] sheetIndexs = (Integer[]) sheetValue;
for (int a = 0; a < sheetIndexs.length; a++) {
sheet = wb.getSheetAt(a);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(a);
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
}
} else if (sheetValue instanceof Collection) {
Collection sheetCollection = (Collection) sheetValue;
Iterator it = sheetCollection.iterator();
while (it.hasNext()) {
Object ito = it.next();
if (ito instanceof Integer) {
int sheetIndex = Integer.parseInt(String.valueOf(ito));
sheet = wb.getSheetAt(sheetIndex);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(sheetIndex);
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
} else if (ito instanceof String) {
String sheetName = String.valueOf(ito);
sheet = wb.getSheet(sheetName);
if (sheet != null) {
SheetDatas datas = new SheetDatas();
datas.setSheetIndex(wb.getSheetIndex(sheet));
datas.setSheetName(sheet.getSheetName());
datas.setSheet(sheet);
sheetDatas.add(datas);
readRowValue(excel, sheet, datas, excelI);
if (isUseTitleData) {
readTitleRowValues(excel, datas);
}
}
}
}
} else {
throw new Exception("不支持類型:" + sheetValue);
}
}
datasKeySheets.setSheetDatas(sheetDatas);
datasKeySheets.setSheetCount(sheetCount);
return datasKeySheets;
}
/**
* 讀取sheet行列集合(正常)
*
* @param sheet
* @param datas
* @param excelI
* @throws Exception
*/
public static final void readRowValue(final Excel excel, final Sheet sheet, final SheetDatas datas, final ExcelI excelI) throws Exception {
ReadExcelI rexcelI = null;
if (excelI != null) {
rexcelI = (ReadExcelI) excelI;
}
// 行列數據
List<List<Object>> rowValues = new ArrayList<List<Object>>();
int endRowIndex = rexcelI == null ? ExcelI.END_ROW_INDEX : rexcelI.getEndRowIndex();
int endColumnIndex = rexcelI == null ? ExcelI.END_COLUMN_INDEX : rexcelI.getEndColumnIndex();
// 循環sheet中的所有行數據
for (int rowIndex = rexcelI == null ? ExcelI.START_ROW_INDEX : rexcelI.getStartRowIndex(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
// 是否結束
if (endRowIndex != -1 && rowIndex > endRowIndex)
break;
if (rexcelI != null && rexcelI.filterRow(sheet, rowIndex)) {
// ("sheetName:" + sheet.getSheetName() + ",第" + rowIndex + "行數據已被過慮");
continue;
}
// 獲得行對象
Row row = sheet.getRow(rowIndex);
// 判斷是否一行全部為""或null
boolean notNull = excel.isAddRowNull();
if (null != row) {
// 行數據
List<Object> rowValue = new ArrayList<Object>();
// // 添加標題索引值
// if (excel.getTitleKeys() != null && rowIndex == ExcelI.START_ROW_INDEX) {
// for (int ci = 0; ci < rowTitle.getLastCellNum(); ci++) {
// String titleKey = null;
// if (excel.getTitleKeys().size() > ci) {
// titleKey = excel.getTitleKeys().get(ci);
// } else {
// // 默認當前單元格式值
// Cell cell = row.getCell(ci);
// Object value = readCellString(cell);
// value = rexcelI == null ? value : rexcelI.readValue(sheet, rowIndex, ci, value);
// titleKey = JavaUtil.objToStr(value);
// }
// rowsDataMap.put(titleKey, new ArrayList<Map<String,SheetData>>());
// }
// }
// 獲得本行中單元格的個數
// 遍歷列cell
for (int columnIndex = rexcelI == null ? 0 : rexcelI.getStartColumnIndex(); columnIndex < row.getLastCellNum(); columnIndex++) {
// 是否結束
if (endColumnIndex != -1 && columnIndex > endColumnIndex)
break;
Cell cell = row.getCell(columnIndex);
// 獲得指定單元格中的數據
Object value = readCellString(cell);
value = rexcelI == null ? value : rexcelI.readValue(sheet, rowIndex, columnIndex, value);
if (!notNull && CheckUtil.isNotNull(JavaUtil.objToStr(value))) {
notNull = true;
}
rowValue.add(value);
}
if (notNull) {
rowValues.add(rowValue);
}
} else {
List<Object> rowValue = new ArrayList<Object>();
if (notNull) {
rowValues.add(rowValue);
}
}
}
// 刪除判斷最后行是否一行全部為""或null
List<List<Object>> addRowsNull = new ArrayList<List<Object>>();
boolean notNull = false;
for (int b = rowValues.size() - 1; b >= 0; b--) {
List<Object> datasNull = rowValues.get(b);
for (Object value : datasNull) {
if (!notNull && CheckUtil.isNotNull(JavaUtil.objToStr(value))) {
notNull = true;
break;
}
}
if (notNull) {
break;
}
addRowsNull.add(datasNull);
}
for (List<Object> datasNull : addRowsNull) {
rowValues.remove(datasNull);
}
datas.setRowValues(rowValues);
}
/**
* 獲取導出的excel的sheet,復制新模板sheet
*
* @param excel
* @return
* @throws Exception
*/
public static Set<Sheet> readSheets(final ExcelTemplate excel) throws Exception {
Set<Sheet> sheets = new HashSet<Sheet>();
excel.initWorkbook();
Workbook wb = excel.getWb();
// key:index/sheet/sheetName
Sheet sheet = null;
int sheetCount = wb.getNumberOfSheets();
// 獲得sheet工作簿Sheet
Object sheetValue = excel.getSheetValue();
// if (sheetValue == null) {
// // 循環所有sheet
// for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
// // 獲得sheet工作簿Sheet
// sheet = wb.getSheetAt(sheetIndex);
// sheets.add(sheet);
// }
// } else {
// if (sheetValue instanceof String) {
// String sheetName = String.valueOf(sheetValue);
// // 取得
// sheet = wb.getSheet(sheetName);
// sheets.add(sheet);
// } else if (sheetValue instanceof Integer) {
// Integer sheetIndex = Integer.parseInt(String.valueOf(sheetValue));
// sheet = wb.getSheetAt(sheetIndex);
// sheets.add(sheet);
// } else if (sheetValue instanceof String[]) {
// String[] sheetNames = (String[]) sheetValue;
// for (String sheetName : sheetNames) {
// sheet = wb.getSheet(sheetName);
// //wb.setSheetName(wb.getSheetIndex(sheet), sheetName + "--zhangjun");
// sheets.add(sheet);
// }
// } else if (sheetValue instanceof int[]) {
// int[] sheetIndexs = (int[]) sheetValue;
// for (int a = 0; a < sheetIndexs.length; a++) {
// sheet = wb.getSheetAt(a);
// sheets.add(sheet);
// }
// } else if (sheetValue instanceof Integer[]) {
// Integer[] sheetIndexs = (Integer[]) sheetValue;
// for (int a = 0; a < sheetIndexs.length; a++) {
// sheet = wb.getSheetAt(a);
// sheets.add(sheet);
// }
// } else if (sheetValue instanceof Collection) {
// Collection sheetCollection = (Collection) sheetValue;
// Iterator it = sheetCollection.iterator();
// while (it.hasNext()) {
// Object ito = it.next();
// if (ito instanceof Integer) {
// int sheetIndex = Integer.parseInt(String.valueOf(ito));
// sheet = wb.getSheetAt(sheetIndex);
// sheets.add(sheet);
// } else if (ito instanceof String) {
// String sheetName = String.valueOf(ito);
// sheet = wb.getSheet(sheetName);
// sheets.add(sheet);
// }
// }
// } else {
// throw new Exception("不支持類型:" + sheetValue);
// }
// }
if (sheetValue == null) {
// 循環所有sheet
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
// 獲得sheet工作簿Sheet
sheet = wb.getSheetAt(sheetIndex);
addWriteSheetVariable(excel, sheets, sheet);
}
} else {
if (sheetValue instanceof String) {
String sheetNames = String.valueOf(sheetValue);
sheet = readCloneSheet(wb, excel, sheetNames);
addWriteSheetVariable(excel, sheets, sheet);
} else if (sheetValue instanceof Integer) {
Integer sheetIndex = Integer.parseInt(String.valueOf(sheetValue));
sheet = wb.getSheetAt(sheetIndex);
addWriteSheetVariable(excel, sheets, sheet);
} else if (sheetValue instanceof String[]) {
String[] sheetNameAry = (String[]) sheetValue;
for (String sheetNames : sheetNameAry) {
sheet = readCloneSheet(wb, excel, sheetNames);
addWriteSheetVariable(excel, sheets, sheet);
}
} else if (sheetValue instanceof int[]) {
int[] sheetIndexs = (int[]) sheetValue;
for (int a = 0; a < sheetIndexs.length; a++) {
sheet = wb.getSheetAt(a);
addWriteSheetVariable(excel, sheets, sheet);
}
} else if (sheetValue instanceof Integer[]) {
Integer[] sheetIndexs = (Integer[]) sheetValue;
for (int a = 0; a < sheetIndexs.length; a++) {
sheet = wb.getSheetAt(a);
addWriteSheetVariable(excel, sheets, sheet);
}
} else if (sheetValue instanceof Collection) {
Collection<?> sheetCollection = (Collection<?>) sheetValue;
Iterator<?> it = sheetCollection.iterator();
while (it.hasNext()) {
Object ito = it.next();
if (ito instanceof Integer) {
int sheetIndex = Integer.parseInt(String.valueOf(ito));
sheet = wb.getSheetAt(sheetIndex);
addWriteSheetVariable(excel, sheets, sheet);
} else if (ito instanceof String) {
String sheetNames = String.valueOf(ito);
sheet = readCloneSheet(wb, excel, sheetNames);
addWriteSheetVariable(excel, sheets, sheet);
}
}
} else {
throw new Exception("不支持類型:" + sheetValue);
}
}
return sheets;
}
private static void addWriteSheetVariable(final ExcelTemplate excel, Set<Sheet> sheets, Sheet sheet) {
if (sheet != null) {
sheets.add(sheet);
excel.addedSheets.add(sheet.getSheetName());
}
}
/**
* 獲取sheet對象
*
* @param wb
* @param sheetNames
* @return
*/
private static Sheet readCloneSheet(final Workbook wb, final ExcelTemplate excel, final String sheetNames) {
String sheetNamesAry[] = JavaUtil.split(sheetNames, "*");
String sheetName = "";
String newSheetName = "";
if (sheetNamesAry.length > 0) {
sheetName = sheetNamesAry[0];
}
if (sheetNamesAry.length > 1) {
newSheetName = sheetNamesAry[1];
}
Sheet sheet = wb.getSheet(sheetName);
if (sheet != null) {
// 重點：針對輸出多個模板sheet時，模板sheet名稱取值一定要唯一，由于使用過sheet之后會重新重命名原來的模板名，否則添加多個sheet無法進行判斷
if (CheckUtil.isNull(newSheetName)) {
// 沒有重命名，克隆后，此時無法進行一個sheet模板輸出多個sheet,因為在輸出時已經把模板sheet重寫了
} else {
if (sheetName.equals(newSheetName)) {
} else {
// 重命名
int index = wb.getSheetIndex(sheet);
// 被克隆對象
Sheet cloneSheet = wb.cloneSheet(index);
// 重命名（克隆后，還原以前的模板名）
wb.setSheetName(index, newSheetName);
// 重命名復制模板sheet的名字
int cloneIndex = wb.getSheetIndex(cloneSheet);
wb.setSheetName(cloneIndex, sheetName);
excel.templateSheets.add(cloneSheet.getSheetName());
}
}
}
return sheet;
}
/**
* 讀取excel某個單元格值的索引號
*
* @param excel
* excel對象
* @param params
* 參數
* @return 單元格值
* @author 張軍
* @date 2015-11-03 21:59:00
* @modifiyNote
* @version 1.0
* @throws Exception
*/
public static final Map<ConstantForEnum.CellValueKey, Integer> readCellValueIndex(final Excel excel, Map<ConstantForEnum.CellValueKey, Object> params) throws Exception {
Map<ConstantForEnum.CellValueKey, Integer> returnMap = new HashMap<ConstantForEnum.CellValueKey, Integer>();
returnMap.put(ConstantForEnum.CellValueKey.ROW_INDEX, -1);
returnMap.put(ConstantForEnum.CellValueKey.COLUMN_INDEX, -1);
String value = JavaUtil.trim(JavaUtil.objToStr(params.get(ConstantForEnum.CellValueKey.VALUE)));
Object valueCountObj = params.get(ConstantForEnum.CellValueKey.VALUE_COUNT);
boolean valueCountMatch = TypeUtil.Primitive.booleanValue(params.get(ConstantForEnum.CellValueKey.VALUE_COUNT_MATCH));
Object valueRowObj = params.get(ConstantForEnum.CellValueKey.VALUE_ROW);
Object valueColumnObj = params.get(ConstantForEnum.CellValueKey.VALUE_COLUMN);
int valueRow = -1;
int valueColumn = -1;
if (valueRowObj != null) {
valueRow = TypeUtil.Primitive.intValue(valueRowObj);
}
if (valueColumnObj != null) {
valueColumn = TypeUtil.Primitive.intValue(valueColumnObj);
}
int valueCount = 1;
if (valueCountObj != null) {
valueCount = TypeUtil.Primitive.intValue(valueCountObj);
}
if (CheckUtil.isNull(value)) {
// logger.debug("查詢值不能為空");
return returnMap;
}
int valueCountDefault = 0;
int tempRowCount = -1;
int tempColumnCount = -1;
Sheet sheet = readSheet(excel);
// 如果未指定查詢行,則查詢所有行，否則只查詢一行,從valueRow開始到valueRow+1結束
int startRows = -1;
int rows = -1;
if (valueRow == -1) {
startRows = 0;
rows = sheet.getLastRowNum();
} else {
startRows = valueRow;
rows = valueRow + 1;
}
for (int i = startRows; i < rows; i++) {
Row row = sheet.getRow(i);
if (row == null) {
// logger.debug("第[" + i + "]行對象為null,繼續下個行對象查詢");
continue;
}
int startColumns = -1;
int columns = -1;
if (valueColumn == -1) {
startColumns = 0;
columns = row.getLastCellNum();
} else {
startColumns = valueColumn;
columns = valueColumn + 1;
}
breakFor: for (int j = startColumns; j < columns; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
// logger.debug("第[" + i + "]行第[" + j + "]列單元格對象為null,繼續下個單元格查詢");
continue;
}
Object obj = readCellString(cell);
String cellValue = JavaUtil.trim(JavaUtil.objToStr(obj));
if (valueCountDefault != valueCount) {
// 如果未找到指定的匹配索引，則繼續查找
if (value.equals(cellValue)) {
tempRowCount = i;
tempColumnCount = j;
valueCountDefault++;
}
}
if (tempRowCount != -1 && tempColumnCount != -1) {
// 獲取行列索引，判斷行列第幾個
if (valueCountDefault == valueCount) {
// logger.debug("找到位置了行[" + tempRowCount + "]列[" + tempColumnCount + "]第[" + valueCount + "]個值");
break breakFor;
}
}
}
}
if (tempRowCount == -1 || tempColumnCount == -1) {
// logger.debug("沒有找到位置了行[" + tempRowCount + "]列[" + tempColumnCount + "]第[" + valueCount + "]個值");
return returnMap;
} else {
if (valueCountMatch && valueCountDefault != valueCount) {
// logger.debug("行必須匹配,沒有找到位置了行[" + tempRowCount + "]列[" + tempColumnCount + "][最多第(" + valueCountDefault + ")個與配置第(" + valueCount + ")個值不同]");
return returnMap;
}
returnMap.put(ConstantForEnum.CellValueKey.ROW_INDEX, tempRowCount);
returnMap.put(ConstantForEnum.CellValueKey.COLUMN_INDEX, tempColumnCount);
}
return returnMap;
}
/**
* 讀取excel某個單元格值
*
* @param excel
* excel對象
* @param params
* 參數
* @return 單元格值
* @author 張軍
* @date 2015-11-03 21:59:00
* @modifiyNote
* @version 1.0
* @throws Exception
*/
public static final Object readCellValue(final Excel excel, Map<ConstantForEnum.CellValueKey, Object> params) throws Exception {
Object rowIndexObj = params.get(ConstantForEnum.CellValueKey.ROW_INDEX);
Object columnIndexObj = params.get(ConstantForEnum.CellValueKey.COLUMN_INDEX);
if (rowIndexObj == null || columnIndexObj == null) {
Sheet sheet = readSheet(excel);
Map<ConstantForEnum.CellValueKey, Object> paramsRow = new HashMap<ConstantForEnum.CellValueKey, Object>();
paramsRow.put(ConstantForEnum.CellValueKey.VALUE, params.get(ConstantForEnum.CellValueKey.ROW_VALUE));
paramsRow.put(ConstantForEnum.CellValueKey.VALUE_COUNT, params.get(ConstantForEnum.CellValueKey.ROW_COUNT));
paramsRow.put(ConstantForEnum.CellValueKey.VALUE_COUNT_MATCH, params.get(ConstantForEnum.CellValueKey.ROW_COUNT_MATCH));
Map<ConstantForEnum.CellValueKey, Integer> returnRowIndex = ExcelReadUtil.readCellValueIndex(excel, paramsRow);
int tempRowCount = returnRowIndex.get(ConstantForEnum.CellValueKey.ROW_INDEX);
if (tempRowCount == -1) {
return null;
}
Map<ConstantForEnum.CellValueKey, Object> paramsColumn = new HashMap<ConstantForEnum.CellValueKey, Object>();
paramsColumn.put(ConstantForEnum.CellValueKey.VALUE, params.get(ConstantForEnum.CellValueKey.COLUMN_VALUE));
paramsColumn.put(ConstantForEnum.CellValueKey.VALUE_COUNT, params.get(ConstantForEnum.CellValueKey.COLUMN_COUNT));
paramsColumn.put(ConstantForEnum.CellValueKey.VALUE_COUNT_MATCH, params.get(ConstantForEnum.CellValueKey.COLUMN_COUNT_MATCH));
Map<ConstantForEnum.CellValueKey, Integer> returnColumnIndex = ExcelReadUtil.readCellValueIndex(excel, paramsColumn);
int tempColumnCount = returnColumnIndex.get(ConstantForEnum.CellValueKey.COLUMN_INDEX);
if (tempColumnCount == -1) {
return null;
}
return readCellString(sheet.getRow(tempRowCount).getCell(tempColumnCount));
// String rowValue = JavaUtil.trim(JavaUtil.objToStr(params.get(ConstantForEnum.CellValueKey.ROW_VALUE)));
// String columnValue = JavaUtil.trim(JavaUtil.objToStr(params.get(ConstantForEnum.CellValueKey.COLUMN_VALUE)));
// Object rowCountObj = params.get(ConstantForEnum.CellValueKey.ROW_COUNT);
// Object columnCountObj = params.get(ConstantForEnum.CellValueKey.COLUMN_COUNT);
// boolean rowCountMatch = TypeUtil.Primitive.booleanValue(params.get(ConstantForEnum.CellValueKey.ROW_COUNT_MATCH));
// boolean columnCountMatch = TypeUtil.Primitive.booleanValue(params.get(ConstantForEnum.CellValueKey.COLUMN_COUNT_MATCH));
// int rowCount = 1;
// int columnCount = 1;
// if (rowCountObj != null) {
// rowCount = TypeUtil.Primitive.intValue(rowCountObj);
// }
// if (columnCountObj != null) {
// columnCount = TypeUtil.Primitive.intValue(columnCountObj);
// }
// if (CheckUtil.isNull(rowValue) || CheckUtil.isNull(columnValue)) {
// logger.debug("行[" + rowValue + "]或列[" + columnValue + "]值不能為空");
// return null;
// }
// int rowCountDefault = 0;
// int columnCountDefault = 0;
// int tempRowCount = -1;
// int tempColumnCount = -1;
// Sheet sheet = getSheet(excel);
// for (int i = 0; i < sheet.getLastRowNum(); i++) {
// Row row = sheet.getRow(i);
// if (row == null) {
// logger.debug("第[" + i + "]行對象為null,繼續下個行對象查詢");
// continue;
// }
// breakFor: for (int j = 0; j < row.getLastCellNum(); j++) {
// Cell cell = row.getCell(j);
// if (cell == null) {
// logger.debug("第[" + i + "]行第[" + j + "]列單元格對象為null,繼續下個單元格查詢");
// continue;
// }
// Object obj = readCellString(cell);
// String value = JavaUtil.trim(JavaUtil.objToStr(obj));
// if (rowCountDefault != rowCount) {
// // 如果未找到指定的匹配行索引，則繼續查找
// if (rowValue.equals(value)) {
// tempRowCount = i;
// rowCountDefault++;
// }
// }
// if (columnCountDefault != columnCount) {
// // 如果未找到指定的匹配列索引，則繼續查找
// if (columnValue.equals(value)) {
// tempColumnCount = j;
// columnCountDefault++;
// }
// }
// if (tempRowCount != -1 && tempColumnCount != -1) {
// // 獲取行列索引，判斷行列第幾個
// if (rowCountDefault == rowCount && columnCountDefault == columnCount) {
// logger.debug("找到位置了行[" + tempRowCount + "]第[" + rowCount + "]個值，列[" + tempColumnCount + "]第[" + columnCount + "]個值");
// break breakFor;
// }
// }
// }
// }
// if (tempRowCount == -1 || tempColumnCount == -1) {
// logger.debug("沒有找到位置了行[" + tempRowCount + "]第[" + rowCount + "]個值，列[" + tempColumnCount + "]第[" + columnCount + "]個值");
// return null;
// } else {
// if (rowCountMatch && rowCountDefault != rowCount) {
// logger.debug("行必須匹配,沒有找到位置了行[" + tempRowCount + "][最多第(" + rowCountDefault + ")個與配置第(" + rowCount + ")個值不同]，列[" + tempColumnCount + "]第[" + columnCount + "]個值");
// return null;
// }
// if (columnCountMatch && columnCountDefault != columnCount) {
// logger.debug("列必須匹配,沒有找到位置了行[" + tempRowCount + "]第[" + rowCount + "]個值，列[" + tempColumnCount + "][最多第(" + columnCountDefault + ")個與配置第(" + columnCount + ")個值不同]");
// return null;
// }
// return readCellString(sheet.getRow(tempRowCount).getCell(tempColumnCount));
// }
} else {
int rowIndex = TypeUtil.Primitive.intValue(rowIndexObj);
int columnIndex = TypeUtil.Primitive.intValue(columnIndexObj);
if (rowIndex == -1 || columnIndex == -1) {
// logger.debug("行[" + rowIndex + "]或列[" + columnIndex + "]索引值不能小于0");
return null;
}
Sheet sheet = readSheet(excel);
Row row = sheet.getRow(TypeUtil.Primitive.intValue(rowIndexObj));
if (row == null) {
// logger.debug("第[" + rowIndex + "]行對象為null");
return null;
}
Cell cell = row.getCell(columnIndex);
if (cell == null) {
// logger.debug("第[" + rowIndex + "]行第[" + columnIndex + "]列單元格對象為null");
return null;
}
return readCellString(cell);
}
}
/**
* 獲取sheet對象
*
* @return sheet對象
* @author 張軍
* @date 2015-11-03 21:59:00
* @modifiyNote
* @version 1.0
* @throws Exception
*/
public static Sheet readSheet(final Excel excel) throws Exception {
excel.initWorkbook();
Workbook wb = excel.getWb();
// key:index/sheet/sheetName
Sheet sheet = null;
// 獲得sheet工作簿Sheet
Object sheetValue = excel.getSheetValue();
if (sheetValue == null) {
sheet = wb.getSheetAt(0);
} else {
if (sheetValue instanceof String) {
String sheetName = String.valueOf(sheetValue);
sheet = wb.getSheet(sheetName);
} else if (sheetValue instanceof Integer) {
Integer sheetCount = Integer.parseInt(String.valueOf(sheetValue));
sheet = wb.getSheetAt(sheetCount);
}
}
return sheet;
}
/**
* 判斷指定的單元格是否是合并單元格
*
* @param sheet
* @param row
* 行下標
* @param column
* 列下標
* @return null:沒有合并,非null,有合并
*/
public static final CellRangeAddress readMergedRegion(final Sheet sheet, final int row, final int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return range;
}
}
}
return null;
}
/**
* 獲取一個cell的數據類型
*
* @param cell
* @return
*/
public static final Object readCell(final Cell cell) {
Object result = null;
try {
result = cell.getRichStringCellValue();
} catch (Exception e) {
try {
result = cell.getNumericCellValue();
} catch (Exception e2) {
try {
result = cell.getDateCellValue();
} catch (Exception e3) {
try {
result = cell.getBooleanCellValue();
} catch (Exception e4) {
logger.error("獲取excel公式結果出錯:[" + cell.getRowIndex() + "][" + cell.getColumnIndex() + "]", e4);
// 返回公式
result = "=" + cell.getCellFormula();
}
}
}
}
return result;
}
/**
* 獲取一個cell的數據類型
*
* @param cell
* @return
*/
public static final Object readCellString(final Cell cell) {
Object result = null;
if (cell != null) {
// 單元格類型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = cell.getCellType();
try {
switch (cellType) {
case Cell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
result = cell.getDateCellValue();
} else {
result = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_FORMULA:
result = readCell(cell);
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
result = null;
break;
case Cell.CELL_TYPE_ERROR:
result = null;
break;
default:
break;
}
} catch (Exception e) {
// 讀取文本
logger.error("讀取格式行列[" + cell.getRowIndex() + "," + cell.getColumnIndex() + "]出錯，改成讀取文本：" + e.getMessage());
try {
result = cell.getRichStringCellValue().getString();
} catch (Exception e1) {
logger.error("改成讀取文本出錯，返回null：" + e1.getMessage());
return null;
}
}
}
return JavaUtil.getIsNumValue(result);
}
}
本文為張軍原創文章,轉載無需和我聯系,但請注明來自張軍的軍軍小站,個人博客http://www.dlhighland.cn
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
