/** 将 excel中的sheet解析成对应的实体 */
@Override
public <T> Collection<T> getFinanceReprotEntitysByConfig(
Sheet sheet,
Map<String, FinanceReportBean> financebean,
FinanceReportParamBean financeParam,
BaseFile bf,
CustInfo ci)
throws Exception {
// TODO Auto-generated method stub
String targetClass = financeParam.getTargetClass();
Class<?> targetObject = Class.forName(targetClass);
String sheaderCell = financeParam.getHeaderCell();
String sdataCell = financeParam.getDataCell();
String vheaderCell[] = sheaderCell.split(",");
String vdataCell[] = sdataCell.split(",");
String headerCell = "";
String dataCell = "";
int headerCellRowIndex = 0;
int headerCellCellIndex = 0;
int dataCellRowIndex = 0;
int dataCellCellIndex = 0;
int sheetRowNum = sheet.getLastRowNum();
int datalength = 0;
String cellvalue = "";
String headerValue = "";
String field = "";
Boolean flag = true;
Boolean empty = false;
Map<String, JSONObject> FinanceEntiies = new HashMap<String, JSONObject>();
List finance = new ArrayList();
String year = "";
try {
for (int i = 0; i < vheaderCell.length; i++) {
datalength = 0;
headerCellRowIndex = 0;
headerCellCellIndex = 0;
dataCellRowIndex = 0;
dataCellCellIndex = 0;
headerCell = vheaderCell[i];
dataCell = vdataCell[i];
ExeclBean headerInfo = PoiExcelUtil.getExcelBean(headerCell);
ExeclBean dataInfo = PoiExcelUtil.getExcelBean(dataCell);
headerCellRowIndex = headerInfo.getRow_num() + 1;
headerCellCellIndex = headerInfo.getCol_num();
dataCellRowIndex = dataInfo.getRow_num();
dataCellCellIndex = dataInfo.getCol_num();
flag = true;
if (headerCellRowIndex > sheetRowNum || dataCellRowIndex > sheetRowNum) {
throw new BusinessException(
"解析excel出错:配置的标题cell("
+ headerCellRowIndex
+ ")或数据cell("
+ dataCellRowIndex
+ ")大于实际 sheet("
+ sheet.getSheetName()
+ ")的row长度");
} else {
Row datarow = sheet.getRow(dataCellRowIndex);
datalength = 0;
// 获得数据列的长度
for (int j = dataCellCellIndex; j < datarow.getLastCellNum(); j++) {
Cell datacell = datarow.getCell(j);
cellvalue = PoiExcelUtil.getCellValue(datacell);
if (flag == true) {
if (null != cellvalue && (!"".equals(cellvalue))) {
datalength = datalength + 1;
} else {
flag = false;
}
}
}
List<JSONObject> entity = new ArrayList<JSONObject>();
for (int j = 0; j < datalength; j++) {
cellvalue = "";
empty = true;
JSONObject financeEntityJson = new JSONObject();
datarow = sheet.getRow(dataCellRowIndex);
Cell cell = datarow.getCell(dataCellCellIndex + j);
year = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
// 隐藏列则不写入数据库
if (!sheet.isColumnHidden(dataCellCellIndex + j)) {
if (!"".equals(year.trim())) {
for (int h = dataCellRowIndex; h < sheetRowNum; h++) {
datarow = sheet.getRow(h);
if (null != datarow) {
if (headerCellCellIndex < datarow.getLastCellNum()) {
if (null != datarow.getCell(headerCellCellIndex)) {
headerValue =
PoiExcelUtil.getCellValue(datarow.getCell(headerCellCellIndex));
}
if (dataCellCellIndex + j < datarow.getLastCellNum()) {
if (null != datarow.getCell(dataCellCellIndex + j)) {
cell = datarow.getCell(dataCellCellIndex + j);
cellvalue =
PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
if (null != cellvalue && (!"".equals(cellvalue))) {
field = "";
flag = true;
if (h == dataCellRowIndex) {
flag = false;
}
field =
this.getExcelTableField(
headerValue,
(cell.getRowIndex() + 1) + "_" + i,
financebean,
flag);
System.out.println(
headerValue
+ ":"
+ ((cell.getRowIndex() + 1) + "_" + i)
+ ":"
+ cellvalue);
if (!"".equals(field)) {
financeEntityJson.put(field, cellvalue);
empty = false;
}
}
}
}
}
}
}
if (empty == false) {
// 根据年进行合并
if (FinanceEntiies.containsKey(year)) {
JSONObject temp = FinanceEntiies.get(year);
Iterator it = financeEntityJson.keys();
while (it.hasNext()) {
String key = (String) it.next();
temp.put(key, financeEntityJson.get(key).toString());
}
FinanceEntiies.put(year, temp);
} else {
FinanceEntiies.put(year, financeEntityJson);
}
}
}
}
}
}
}
System.out.println(FinanceEntiies);
if (!FinanceEntiies.isEmpty()) {
Set<String> key = FinanceEntiies.keySet();
for (Iterator it = key.iterator(); it.hasNext(); ) {
year = (String) it.next();
JSONObject jsonObj = FinanceEntiies.get(year);
Object sourectObject = targetObject.newInstance();
this.tableService.copyAndOverrideExistedValueFromJSONObject(jsonObj, sourectObject, null);
try {
BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "upLoadId")
.getWriteMethod()
.invoke(sourectObject, bf);
BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "custId")
.getWriteMethod()
.invoke(sourectObject, ci);
} catch (Exception e) {
// TODO Auto-generated catch block
throw new BusinessException(
"在记录上传文件的ID和客户时出请检查实体类中有没有upLoadId和custId字段" + e.getMessage());
}
finance.add(sourectObject);
}
return finance;
}
} catch (BusinessException b) {
b.printStackTrace();
throw new BusinessException(b.getMessage());
} catch (Exception e) {
e.printStackTrace();
// TODO Auto-generated catch block
throw new BusinessException(e.getMessage());
}
return null;
}
