excel导入
控制器：
/**
* @Description: 处理导入excel空单元格数据
* @param @param list
* @param @param index
* @param @return
* @return Object
* @throws
* @author hougm
* @date 2017-9-18
*/
@SuppressWarnings("rawtypes")
private Object getListItem(List list,int index){
if(list.size()>index){
return list.get(index);
}else{
return null;
}
}
/**
* @Description: 上传excel(批量处理还款计划)
* @param @param file
* @param @param request
* @param @param response
* @param @param isValidate
* @return void
* @throws
* @author hougm
* @date 2017-8-16
*/
@SuppressWarnings("unchecked")
@RequestMapping("/toImportBatchLoanRepayPlan")
@ResponseBody
public Object toImportBatchLoanRepayPlan(@RequestParam(required=false) MultipartFile[] file,HttpServletRequest request,String isValidate,String channel){
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
MultipartFile resFile = null;
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
resFile = entity.getValue();
List<List<Object>> list=new ArrayList<List<Object>>();
try {
list = ExcelUtils.read2007Excel(resFile.getInputStream(),1,false);
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
JSONObject json=new JSONObject();
json.put("message", "您上上传的excel文件不对，请核实你上传的文件为excel2007及以上");
json.put("succ",true);
return json;
}
List<FinancialBatchLoanPlanRecordError> errorList=new ArrayList<FinancialBatchLoanPlanRecordError>();
Map<String,FinancialBatchLoanPlanRecordError> errorMap=new HashMap<String,FinancialBatchLoanPlanRecordError>();
List<FinancialBatchLoanPlanRecordSucc> succList=new ArrayList<FinancialBatchLoanPlanRecordSucc>();
SimpleDateFormat format_1=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String pcNo=this.getBatchNo();//生成批次号
for(int i=0;i<list.size();i++){
if(list.get(i)==null||list.get(i).size()<3){
continue;
}
System.out.println("列表第"+(i+1)+"行数据："+list.get(i));
Map<String, Object> params=new HashMap<String,Object>();
params.put("channel", channel);
boolean isVali=true;
StringBuffer errorStr=new StringBuffer();
FinancialBatchLoanPlanRecordError errorRecord=new FinancialBatchLoanPlanRecordError();
FinancialBatchLoanPlanRecordSucc succRecord=new FinancialBatchLoanPlanRecordSucc();
errorRecord.setChannel(channel);
succRecord.setChannel(channel);
errorRecord.setBathcNo(pcNo);
succRecord.setBathcNo(pcNo);
/**借款编号**/
String loanNo=(String) getListItem(list.get(i),0);
if(StringUtils.isEmpty(loanNo)){
isVali=false;
errorStr.append("借款编号不能为空;");
// continue;
}else {
params.put("loanNo", loanNo);
errorRecord.setLoanNo(loanNo);
succRecord.setLoanNo(loanNo);
}
/**客户姓名**/
String realName=(String) getListItem(list.get(i),1);
if(StringUtils.isEmpty(realName)){
errorStr.append("客户姓名不能为空;");
}else {
// params.put("realName", realName);
errorRecord.setRealName(realName);
succRecord.setRealName(realName);
}
/**借款人ID**/
String userId=(String) getListItem(list.get(i),2);
if(StringUtils.isEmpty(userId)){
errorStr.append("借款人ID不能为空;");
}else {
// params.put("userId", userId);
try {
errorRecord.setUserId(Long.parseLong(userId));
succRecord.setUserId(Long.parseLong(userId));
} catch (NumberFormatException e) {
e.printStackTrace();
errorStr.append("借款人ID必须是数字;");
}
}
/**分期数**/
String stepNo=(String) getListItem(list.get(i),3);
if(StringUtils.isEmpty(stepNo)){
isVali=false;
errorStr.append("分期数不能为空;");
}else {
params.put("stepNo", stepNo);
try {
String stepNos[]=stepNo.split("/");
if(stepNos.length==2){
errorRecord.setStepNo(Integer.parseInt(stepNos[0]));
succRecord.setStepNo(Integer.parseInt(stepNos[0]));
}else {
errorStr.append("分期数格式不对，必须是如：1/3;");
isVali=false;
}
} catch (Exception e) {
e.printStackTrace();
isVali=false;
errorStr.append("分期数必须是数字;");
}
}
/**减免金额**/
String REDUCE_AMOUNT=(String) getListItem(list.get(i),4);
if(!StringUtils.isEmpty(REDUCE_AMOUNT)){
try {
BigDecimal BIG_REDUCE_AMOUNT=new BigDecimal(REDUCE_AMOUNT);
if(BIG_REDUCE_AMOUNT.compareTo(new BigDecimal(0))<0){
errorStr.append("减免滞纳金错误;");
}else {
errorRecord.setReduceAmount(BIG_REDUCE_AMOUNT);
succRecord.setReduceAmount(BIG_REDUCE_AMOUNT);
}
} catch (Exception e) {
e.printStackTrace();
errorStr.append("减免滞纳金格式错误;");
}
}
/**实收合计**/
String ACT_TOTAL_AMOUNT=(String) getListItem(list.get(i),5);
if(!StringUtils.isEmpty(ACT_TOTAL_AMOUNT)){
try {
BigDecimal BIG_ACT_TOTAL_AMOUNT=new BigDecimal(ACT_TOTAL_AMOUNT);
if(BIG_ACT_TOTAL_AMOUNT.compareTo(new BigDecimal(0))<0){
errorStr.append("实收合计错误;");
}else {
errorRecord.setActTotalAmount(BIG_ACT_TOTAL_AMOUNT);
succRecord.setActTotalAmount(BIG_ACT_TOTAL_AMOUNT);
}
} catch (Exception e) {
e.printStackTrace();
errorStr.append("实收合计格式错误;");
}
}else{
errorStr.append("实收合计不能为空;");
}
/**实还日期**/
String ACT_TIME=(String) getListItem(list.get(i),6);
if(!StringUtils.isEmpty(ACT_TIME)){
try {
errorRecord.setActTime(format_1.parse(ACT_TIME));
succRecord.setActTime(format_1.parse(ACT_TIME));
} catch (ParseException e) {
e.printStackTrace();
errorStr.append("实还日期格式错误;");
}
}else{
errorStr.append("实换日期不能为空;");
}
/**备注**/
String REMARK=(String) getListItem(list.get(i),7);
if(!StringUtils.isEmpty(REMARK)){
if(REMARK.length()>150){
errorStr.append("备注最多150个字;");
}else {
errorRecord.setRemark(REMARK);
succRecord.setRemark(REMARK);
}
}else {
errorStr.append("备注不能为空;");
}
/******用借款编号，分期数，channel匹配对应还款计划******/
//假如参数本身没有问题（excel有值），具备数据库匹配条件那么匹配数据库还款计划数据
if(isVali){
List<Map<String, Object>> lrpList=this.loanRepayPlanService.queryLoanRepayPlanList(params,errorRecord.getActTime());
if(lrpList!=null&&lrpList.size()>0){
Map<String, Object> loanInfoMap=lrpList.get(0);
if(loanInfoMap.get("ID")==null||!((Long)loanInfoMap.get("ID")).equals(errorRecord.getUserId())){
errorStr.append("借款人ID和借款编号对应的还款计划的借款人ID不匹配;");
}
if(loanInfoMap.get("REAL_NAME")==null||!errorRecord.getRealName().equals(((String)loanInfoMap.get("REAL_NAME")))){
errorStr.append("客户姓名和借款编号对应的还款计划的客户姓名不匹配;");
}
if("002005002".equals((String)loanInfoMap.get("REPAY_STATUS"))){
errorStr.append("匹配的还款计划已经还款;");
}
if(loanInfoMap.get("LOAN_TIME")!=null){
// String loanTimeStr=DateUtils.formatDate("yyyy-MM-dd", (Date)loanInfoMap.get("LOAN_TIME"));
// Date loanTime=DateUtils.stringToDate(loanTimeStr+" 00:00:00", "yyyy-MM-dd HH:mm:ss");
Date loanTime=(Date)loanInfoMap.get("LOAN_TIME");
if(errorRecord.getActTime().getTime()<loanTime.getTime()){
errorStr.append("实还日期有误;");
}
}else{
errorStr.append("匹配的还款计划没有放款时间;");
}
errorRecord.setPlanId(Long.parseLong(loanInfoMap.get("PLAN_ID")+""));
succRecord.setPlanId(Long.parseLong(loanInfoMap.get("PLAN_ID")+""));
errorRecord.setMobile((String) loanInfoMap.get("MOBILE"));
errorRecord.setLoanDay((Long) loanInfoMap.get("LOAN_DAY")+"");
errorRecord.setLoanAmount((BigDecimal) loanInfoMap.get("LOAN_AMOUNT"));
errorRecord.setDueDay(DateUtils.stringToDate((String) loanInfoMap.get("DUE_DAY"), "yyyy-MM-dd"));
try {
errorRecord.setNomTotalAmount(new BigDecimal(loanInfoMap.get("NOM_TOTAL_AMOUNT")+""));
} catch (ClassCastException e) {
// TODO Auto-generated catch block
errorRecord.setNomTotalAmount((BigDecimal)loanInfoMap.get("NOM_TOTAL_AMOUNT"));
}
/**
* 实还服务费+实还滞纳金+实还利息+实还本金+活动减免+减免利息+减免服务费+减免滞纳金≥应还服务费+应还滞纳金+应还利息+应还本金 若不≥，
* 不予上传，告知减免金额有误
*/
BigDecimal actTotalAmount=new BigDecimal(0);
if(errorRecord.getActTotalAmount()!=null){
actTotalAmount=errorRecord.getActTotalAmount();
}
// if(errorRecord.getDiffDueInterest()!=null){
// actTotalAmount=actTotalAmount.add(errorRecord.getDiffDueInterest());
// }
// if(errorRecord.getDiffDueServiceInterest()!=null){
// actTotalAmount=actTotalAmount.add(errorRecord.getDiffDueServiceInterest());
// }
if(errorRecord.getReduceAmount()!=null){
actTotalAmount=actTotalAmount.add(errorRecord.getReduceAmount());
/*计算减免总额*/
BigDecimal reduceAmount=(BigDecimal)loanInfoMap.get("toalReduceAmount");
reduceAmount=reduceAmount.add(errorRecord.getReduceAmount());
/**减免总金额不得大于应还利息、应还服务费、应还滞纳金之和**/
BigDecimal toalReduceAmount=loanInfoMap.get("DUE_INTEREST")==null?new BigDecimal(0):(BigDecimal)loanInfoMap.get("DUE_INTEREST");
toalReduceAmount=toalReduceAmount.add(loanInfoMap.get("DUE_SERVICE_INTEREST")==null?new BigDecimal(0):(BigDecimal)loanInfoMap.get("DUE_SERVICE_INTEREST"));
toalReduceAmount=toalReduceAmount.add(loanInfoMap.get("overEueAmount")==null?new BigDecimal(0):(BigDecimal)loanInfoMap.get("overEueAmount"));
// if(toalReduceAmount.compareTo(reduceAmount)<0){
// errorStr.append("减免总金额不得大于应还利息、应还服务费、应还滞纳金之和;");
// }
}else {
actTotalAmount.add((BigDecimal)loanInfoMap.get("toalReduceAmount"));
}
// if(actTotalAmount.compareTo(errorRecord.getNomTotalAmount())<0){
// errorStr.append("减免金额有误;");
// }
errorRecord.setRepayStatusName((String) loanInfoMap.get("REPAY_STATUS_NAME"));
errorRecord.setEarlyFlag(loanInfoMap.get("EARLY_FLAG")+"");
errorRecord.setOverdueDay((long) loanInfoMap.get("OVERDUE_DAY")+"");
}else {
errorStr.append("根据借款编号和分期数未找到匹配还款计划;");
}
}
if(errorStr.length()>0){
errorRecord.setErrorStr(errorStr.toString());
errorList.add(errorRecord);
}
succList.add(succRecord);
errorMap.put(errorRecord.getPlanId()+"BAK",errorRecord);
}
Map<String, Object> jsonMap = new HashMap<String, Object>();// 定义map
if(errorList.size()>0||"0".equals(isValidate)){
this.financialBatchLoanPlanRecordService.insertFinancialBatchLoanPlanRecordErrorList(errorList);
jsonMap.put("message","已经完成数据验证，其中"+errorList.size()+"条数据验证失败！");
jsonMap.put("batchNo", pcNo);
return JSONObject.fromObject(jsonMap);
}else if("1".equals(isValidate)){
/**保存验证成功的记录信息**/
this.financialBatchLoanPlanRecordService.insertFinancialBatchLoanPlanRecordSuccList(succList);
AdminInfo adminInfo = (AdminInfo) request.getSession().getAttribute(
DtConstant.CurrentUserH);
Map<String, Object> params=new HashMap<String,Object>();
List<FinancialModifyLoanPlanRecordInfo> logInfoList=new ArrayList<FinancialModifyLoanPlanRecordInfo>();
/**处理批量修改还款计划记录信息**/
for (FinancialBatchLoanPlanRecordSucc recordSucc : succList) {
params.clear();
params.put("planId", recordSucc.getPlanId());
params.put("DIFF_DUE_INTEREST", recordSucc.getDiffDueInterest()!=null?recordSucc.getDiffDueInterest().toString():null);
params.put("DIFF_DUE_SERVICE_INTEREST", recordSucc.getDiffDueServiceInterest()!=null?recordSucc.getDiffDueServiceInterest().toString():null);
params.put("DIFF_OVERDUE_AMOUNT", recordSucc.getReduceAmount()!=null?recordSucc.getReduceAmount().toString():null);
params.put("REMARK", recordSucc.getRemark());
/**获取修改还款计划以前的还款计划数据**/
FinancialModifyLoanPlanRecordInfo record=this.financialModifyLoanPlanRecordServiceImpl.processFinancialModifyLoanPlanRecordInfoByOldInfo(adminInfo,params);
record.setOperateType("1");
record.setBatchNo(pcNo);
record.setActTime(recordSucc.getActTime());
logInfoList.add(record);
}
/**更新还款计划信息**/
// this.loanRepayPlanService.updateModifyLoanRepayPlan(params,record);
String responseContent=this.loanRepayPlanService.callBatchModifyLoanRepayPlan(succList,logInfoList);
if(responseContent.indexOf("[")!=-1){
JSONArray resJosnList=JSONArray.fromObject(responseContent);
Iterator<JSONObject> jsonList=resJosnList.iterator();
List<FinancialBatchLoanPlanRecordError> processErrorList=new ArrayList<FinancialBatchLoanPlanRecordError>();
while (jsonList.hasNext()) {
JSONObject resJson= (JSONObject) jsonList.next();
if("succ".equals(resJson.get("code"))){
for (FinancialModifyLoanPlanRecordInfo record : logInfoList) {
/**插入还款计划日志信息**/
this.financialModifyLoanPlanRecordServiceImpl.insertFinancialModifyLoanPlanRecordInfo(record);
}
}else{
FinancialBatchLoanPlanRecordError errorRecord=errorMap.get(((JSONObject)resJson.get("reqData")).get("id")+"BAK");
if(errorRecord==null){
errorRecord=new FinancialBatchLoanPlanRecordError();
errorRecord.setErrorStr((String) resJson.get("msg"));
errorRecord.setLoanNo((String) resJson.get("loanNo"));
errorRecord.setBathcNo(pcNo);
}else{
errorRecord.setErrorStr((String) resJson.get("msg"));
processErrorList.add(errorRecord);
}
}
}
if(processErrorList.size()>0){
this.financialBatchLoanPlanRecordService.insertFinancialBatchLoanPlanRecordErrorList(processErrorList);
jsonMap.put("message", "共操作了"+logInfoList.size()+"条数据,其中"+resJosnList.size()+"条数据失败。");
}else {
jsonMap.put("message","已经完成数据验证并提交处理成功,处理"+logInfoList.size()+"条数据！");
jsonMap.put("succ",true);
}
}else {
JSONObject resJson=JSONObject.fromObject(responseContent);
jsonMap.put("message", resJson.get("msg"));
}
jsonMap.put("batchNo", pcNo);
return JSONObject.fromObject(jsonMap);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
JSONObject jsonMap=new JSONObject();
logger.error(e.getMessage());
jsonMap.put("message","系统错误,请联系管理员:"+e.getMessage());
jsonMap.put("succ",true);
return jsonMap;
}
return null;
}
工具类：
package com.ydly.utils; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; public class ExcelUtils { public static List<String[]> readExcel(InputStream stream) throws BiffException, IOException { // 创建一个list 用来存储读取的内容 List<String[]> list = new ArrayList<String[]>(); Workbook rwb = null; Cell cell = null; // 获取Excel文件对象 rwb = Workbook.getWorkbook(stream); // 获取文件的指定工作表 默认的第一个 Sheet sheet = rwb.getSheet(0); // 行数(表头的目录不需要，从1开始) for (int i = 0; i < sheet.getRows(); i++) { // 创建一个数组 用来存储每一列的值 String[] str = new String[sheet.getColumns()]; // 列数 for (int j = 0; j < sheet.getColumns(); j++) { // 获取第i行，第j列的值 cell = sheet.getCell(j, i); str[j] = cell.getContents(); } // 把刚获取的列存入list list.add(str); } return list; } /** * @Description: 读取2003excel * @param @param is * @param @param startIndex * @param @param processMergedRegion * @param @return * @param @throws IOException * @return List<List<Object>> * @throws * @author hougm * @date 2017-8-16 */ public static List<List<Object>> read2003Excel(InputStream is,int startIndex,boolean processMergedRegion) throws IOException { List<List<Object>> list = new LinkedList<List<Object>>(); HSSFWorkbook hwb = new HSSFWorkbook(is); HSSFSheet sheet = hwb.getSheetAt(0); Object value = null; HSSFRow row = null; HSSFCell cell = null; int counter = 0; // for (int i = sheet.getFirstRowNum(); counter < sheet // .getPhysicalNumberOfRows(); i++) { for (int i = startIndex==0?sheet.getFirstRowNum():startIndex; counter < sheet .getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { if(i<(50*sheet.getPhysicalNumberOfRows())){ continue; }else{ break; } } else { counter++; } List<Object> linked = new LinkedList<Object>(); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); Map<String, Object> cellMap=isMergedRegion2003(sheet,i,j); if((boolean) cellMap.get("isMergedRegion")&&processMergedRegion){ CellRangeAddress cellRangeAddress=(CellRangeAddress) cellMap.get("cellRangeAddress"); cell=sheet.getRow(cellRangeAddress.getFirstRow()).getCell(cellRangeAddress.getFirstColumn()); int lastColumn = cellRangeAddress.getLastColumn(); j=lastColumn; } if (cell == null) { linked.add(null); continue; } DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符 SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字 switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString()); if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle() .getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: System.out.println(i + "行" + j + " 列 is Boolean type"); value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; default: System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); } if (value == null || "".equals(value)) { continue; } linked.add(value); } list.add(linked); } return list; } /** * @Description: 读取read2007Excel * @param @param is * @param @param startIndex * @param @param processMergedRegion * @param @return * @param @throws IOException * @return List<List<Object>> * @throws * @author hougm * @date 2017-8-16 */ public static List<List<Object>> read2007Excel(InputStream is,int startIndex,boolean processMergedRegion) throws IOException { List<List<Object>> list = new LinkedList<List<Object>>(); // 构造 XSSFWorkbook 对象，strPath 传入文件路径 XSSFWorkbook xwb = new XSSFWorkbook(is); // 读取第一章表格内容 XSSFSheet sheet = xwb.getSheetAt(0); Object value = null; XSSFRow row = null; XSSFCell cell = null; int counter = 0; // for (int i = sheet.getFirstRowNum(); counter < sheet // .getPhysicalNumberOfRows(); i++) { for (int i = startIndex==0?sheet.getFirstRowNum():startIndex; counter < sheet .getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { if(i<(50*sheet.getPhysicalNumberOfRows())){ continue; }else{ break; } } else { counter++; } List<Object> linked = new LinkedList<Object>(); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); Map<String, Object> cellMap=isMergedRegion2007(sheet,i,j); if((boolean) cellMap.get("isMergedRegion")&&processMergedRegion){ CellRangeAddress cellRangeAddress=(CellRangeAddress) cellMap.get("cellRangeAddress"); cell=sheet.getRow(cellRangeAddress.getFirstRow()).getCell(cellRangeAddress.getFirstColumn()); int lastColumn = cellRangeAddress.getLastColumn(); j=lastColumn; } if (cell == null) { linked.add(null); continue; } // DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符 // SimpleDateFormat sdf = new SimpleDateFormat( // "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 // DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字 System.out.println(i + "行" + j + "列cell："+cell); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: System.out.println(i + "行" + j + "列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString()); if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = String.valueOf(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle() .getDataFormatString())) { value = String.valueOf(cell.getNumericCellValue()); } else { value = String.valueOf(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: System.out.println(i + "行" + j + " 列 is Boolean type"); value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; default: System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); } System.out.println(i + "行" + j + "列value："+value); System.out.println(); if (value == null || "".equals(value)) { continue; } linked.add(value); } list.add(linked); } return list; } /** * @Description: 判断是否是合并单元格 * @param @param sheet * @param @param row 第几行 * @param @param column 第几列 * @param @return * @return Map<String, Object> * @throws * @author hougm * @date 2017-7-31 */ private static Map<String, Object> isMergedRegion2003(HSSFSheet sheet,int row ,int column) { int sheetMergeCount = sheet.getNumMergedRegions(); Map<String, Object> cellMap=new HashMap<String, Object>(); cellMap.put("isMergedRegion", false); 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){ cellMap.put("isMergedRegion", true); cellMap.put("cellRangeAddress", range); return cellMap; } } } return cellMap; } /** * @Description: 判断是否是合并单元格 * @param @param sheet * @param @param row 第几行 * @param @param column 第几列 * @param @return * @return Map<String, Object> * @throws * @author hougm * @date 2017-7-31 */ private static Map<String, Object> isMergedRegion2007(XSSFSheet sheet,int row ,int column) { int sheetMergeCount = sheet.getNumMergedRegions(); Map<String, Object> cellMap=new HashMap<String, Object>(); cellMap.put("isMergedRegion", false); 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){ cellMap.put("isMergedRegion", true); cellMap.put("cellRangeAddress", range); return cellMap; } } } return cellMap; } }
