@Override
public String insertFinanceReport(HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 获得上传的附件
InputStream is = null;
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile multipartFile = multipartRequest.getFile("tableImportTemplate");
if (null == multipartFile) {
throw new BusinessException("没有上传财务报表");
}
// 上传的参数据
Map<String, String> model = QueryUtil.getRequestParameterMapByAjax(request);
// 列名对象解析
String financeparams = model.get("parames");
if (null == financeparams
|| "".equals(financeparams)
|| "null".equalsIgnoreCase(financeparams.trim())) {
throw new BusinessException("没有对Excel的配置");
}
try {
BaseFile bf = templateService.saveUpFiletoService(multipartFile, model);
String custid = "";
CustInfo custinfo = null;
if (model.containsKey("custid")) {
custid = model.get("custid");
custinfo = this.tableService.findEntityByID(CustInfo.class, custid);
if (null == custinfo) {
throw new BusinessException("给定的客户的ID没有找到客户");
}
} else {
throw new BusinessException("没有给定客户的ID");
}
Workbook wb = null;
is = multipartFile.getInputStream();
String importFileName = multipartFile.getOriginalFilename().toLowerCase();
if (importFileName.endsWith("xlsx")) {
wb = PoiExcelUtil.readWorkbook(is, ExcelVersionEnum.VERSION2007);
} else {
wb = PoiExcelUtil.readWorkbook(is, ExcelVersionEnum.VERSION2003);
}
List<FinanceReportParamBean> inintParam = this.getImportParams(financeparams);
if (inintParam.size() > 0) {
for (FinanceReportParamBean param : inintParam) {
Map<String, FinanceReportBean> Mapfield =
this.getClassFieldExcel(param.getSheeName(), param.getTargetClass());
if (Mapfield.keySet().size() > 0) {
try {
Sheet sheet = wb.getSheet(param.getSheeName());
if (null != sheet) {
List finance =
(List)
this.getFinanceReprotEntitysByConfig(sheet, Mapfield, param, bf, custinfo);
// 保证实体
if (null != finance) {
this.tableService.saveOrUpdateAllEntities(finance);
}
} else {
throw new BusinessException("上传的EXCEL中没有" + param.getSheeName());
}
} catch (Exception e) {
// TODO Auto-generated catch block
throw new BusinessException(
"上传的EXCEL中没有" + param.getSheeName() + ":" + e.getMessage());
}
} else {
throw new BusinessException("excel中" + param.getSheeName() + "没有配置相应的类");
}
}
} else {
throw new BusinessException("没有对Excel的配置");
}
} catch (BusinessException b) {
throw new BusinessException(b.getMessage());
} catch (Exception e) {
e.printStackTrace();
// TODO Auto-generated catch block
throw new BusinessException(e.getMessage());
}
return "成功";
}
/** 将 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;
}
