加入jar包jxl.jar
===================services层掉用工具类====================================
// 导入
public List<TempXskh> inputExcel(Xskh xskhs, String url) {List<TempXskh> listadd = new ArrayList<TempXskh>();
TempXskh xskh = null; int rsRows = 0; String guidUser = xskhs.getId();// 当前用户登录的id Integer pubRowIndex=0; try { InputStream is = new FileInputStream(url); jxl.Workbook rwb = Workbook.getWorkbook(is); // 获取第一张Sheet表 Sheet rs = rwb.getSheet(0);// 获取Sheet表中所包含的总行数，
rsRows = rs.getRows();// 定位(简称)在excel中以简称为标题的位置
Map<Object, Object> map = Comm.getExcelIndex2("简称", url); Integer rowIndexAname = (Integer) map.get("rowIndex"); Integer columnIndexAname = (Integer) map.get("columnIndex"); pubRowIndex=rowIndexAname; // 定位(公司) Integer columnIndexComplay = Comm.getExcelIndex2("公司", url,pubRowIndex); Integer rowIndexComplay = pubRowIndex;if ("null".equals(rowIndexComplay) || rowIndexComplay == null) {
System.out.println("excel文件必须包含”公司“标题"); xskh = new TempXskh(); xskh.setAname("excel文件必须包含以'公司'为标题的记录！"); xskh.setStatus(-2); listadd.add(xskh); return listadd; }// 定位(固定电话)
Integer columnIndexPhone1 = Comm.getExcelIndex2("固定电话", url,pubRowIndex); Integer rowIndexPhone1 = pubRowIndex;// 定位(移动电话)
Integer columnIndexMobeliPhone = Comm.getExcelIndex2("移动电话", url,pubRowIndex); Integer rowIndexMobeliPhone =pubRowIndex;// 定位(传真)
Integer columnIndexFax = Comm.getExcelIndex2("传真", url,pubRowIndex); Integer rowIndexFax =pubRowIndex;// 定位(地址)
Integer columnIndexAddress = Comm.getExcelIndex2("地址", url,pubRowIndex); Integer rowIndexAddress = pubRowIndex;if ("null".equals(rowIndexAddress) || rowIndexAddress == null) {
System.out.println("excel没地址！"); xskh = new TempXskh(); xskh.setAname("excel文件必须包含以'地址'为标题的记录！"); xskh.setStatus(-2); listadd.add(xskh); return listadd; }// 定位(网站)
Integer columnIndexSite = Comm.getExcelIndex2("网址", url,pubRowIndex); Integer rowIndexSite = pubRowIndex;// 定位(email)
Integer columnIndexMail = Comm.getExcelIndex2("邮箱", url,pubRowIndex); Integer rowIndexMail =pubRowIndex;// 定位(qq)
Integer columnIndexqq = Comm.getExcelIndex2("qq", url,pubRowIndex); Integer rowIndexqq = pubRowIndex;// 定位(联系人)
Integer columnIndexPeple = Comm.getExcelIndex2("联系人", url,pubRowIndex); Integer rowIndexPeple = pubRowIndex;if ("null".equals(rowIndexPeple) || rowIndexPeple == null) {
System.out.println("excel没有联系人"); xskh = new TempXskh(); xskh.setAname("excel文件必须包含以'联系人'为标题的记录！"); xskh.setStatus(-2); listadd.add(xskh); return listadd; }// 定位(联系人部门)
Integer columnIndexDeparetment = Comm.getExcelIndex2("联系人部门", url,pubRowIndex); Integer rowIndexDeparetment =pubRowIndex;// 定位(联系人职务)
Integer columnIndexBusiness = Comm.getExcelIndex2("联系人职务", url,pubRowIndex); Integer rowIndexBusiness = pubRowIndex;// 定位(联系人电话)
Integer columnIndexphone2 = Comm.getExcelIndex2("联系人电话", url,pubRowIndex); Integer rowIndexphone2 =pubRowIndex;/**
* 标题不在同一行 */ if (rowIndexComplay != pubRowIndex || rowIndexComplay != rowIndexPeple) { System.out.println("excel格式不正确！！"); xskh = new TempXskh(); xskh.setAname("excel文件格式不对，必须标题在同一行！"); xskh.setStatus(-2); listadd.add(xskh);return listadd;
} // 从标题下一行开始取数据 System.out.println(rsRows); for (int j = pubRowIndex + 1; j < rsRows; j++) { xskh = new TempXskh();// excel不存在简称
if ("null".equals(columnIndexAname) || columnIndexAname == null) { xskh.setAname(null); } else { Cell aname = rs.getCell(columnIndexAname, j); // (简称)第几行第几列(前门是列，后面是行) xskh.setAname(aname.getContents().toString()); } // excel不存在固定电话 if ("null".equals(rowIndexPhone1) || rowIndexPhone1 == null) { xskh = new TempXskh(); xskh.setTel(null); } else { Cell tel = rs.getCell(columnIndexPhone1, j); xskh.setTel(tel.getContents().toString()); }// excel不存在移动电话
if ("null".equals(rowIndexMobeliPhone) || rowIndexMobeliPhone == null) { xskh = new TempXskh(); xskh.setMobile(null); } else { Cell mobele = rs.getCell(columnIndexMobeliPhone, j); xskh.setMobile(mobele.getContents().toString()); }// excel不存在传真
if ("null".equals(rowIndexFax) || rowIndexFax == null) { xskh = new TempXskh(); xskh.setFax(null); } else { Cell fax = rs.getCell(columnIndexFax, j); xskh.setFax(fax.getContents().toString()); } // excel不存在网站 if ("null".equals(rowIndexSite) || rowIndexSite == null) { xskh = new TempXskh(); xskh.setWww(null); } else { Cell site = rs.getCell(columnIndexSite, j); xskh.setWww(site.getContents().toString()); }// excel不存在email
if ("null".equals(rowIndexMail) || rowIndexMail == null) { xskh = new TempXskh(); xskh.setEmail(null);} else {
Cell mail = rs.getCell(columnIndexMail, j); xskh.setEmail(mail.getContents().toString()); }// excel不存在qq
if ("null".equals(rowIndexqq) || rowIndexqq == null) { xskh = new TempXskh(); xskh.setQq(null); } else { Cell qq = rs.getCell(columnIndexqq, j); xskh.setQq(qq.getContents().toString()); }// excel不存在联系人部门
if ("null".equals(rowIndexDeparetment) || rowIndexDeparetment == null) { xskh = new TempXskh(); xskh.setKf_peo_bm(null); } else { Cell bm = rs.getCell(columnIndexDeparetment, j); xskh.setKf_peo_bm(bm.getContents().toString()); }// excel不存在联系人电话
if ("null".equals(rowIndexphone2) || rowIndexphone2 == null) { xskh = new TempXskh(); xskh.setKf_peo_tel(null); } else { Cell ptel = rs.getCell(columnIndexphone2, j); xskh.setKf_peo_tel(ptel.getContents().toString()); }// excel不存在联系人职务
if ("null".equals(rowIndexBusiness) || rowIndexBusiness == null) { xskh = new TempXskh(); xskh.setKf_peo_zhiwei(null); } else { Cell Business = rs.getCell(columnIndexBusiness, j); xskh.setKf_peo_zhiwei(Business.getContents().toString()); }Cell complay = rs.getCell(columnIndexComplay, j); // (公司)第几行第几列(前门是列，后面是行)
Cell adress = rs.getCell(columnIndexAddress, j);// 地址 Cell peple = rs.getCell(columnIndexPeple, j);// 联系人 String com = complay.getContents(); String addr = adress.getContents(); String ple = peple.getContents(); if ("null".equals(com) || "".equals(com) || com == "" || com == null) { com = null; } if ("null".equals(addr) || "".equals(addr) || addr == "" || addr == null) { addr = null; } if ("null".equals(ple) || "".equals(ple) || ple == "" || ple == null) { ple = null; }xskh.setAname_long(com);
xskh.setAddr(addr); xskh.setKf_peo(ple);xskh.setAguid(Comm.getAguid());// 设置主键
// Integer pxxh=this.excelDao.getMaxPxxh();xskh.setGuid_user(guidUser);
xskh.setGuid_lastupd(guidUser);// xskh.setPxxh(pxxh+1);
// xskh.setGuid_lastupd(Comm.getDate());//导入日期 xskh.setString_create(Comm.getDate());// 导入日期 // xskh.setTimeOfLastUpdate(Comm.getDate());//最后更新 xskh.setRemark(SqliteDBServiceImpl.REMARK); xskh.setStatus(0); this.excelDao.inputTempExcel(xskh);// 添加到数据库方法（dao）return listadd;
}
===================自己写的工具类=====================================
// 定位excel标题位置（纵向定位）
@SuppressWarnings("unused") public static Map<Object, Object> getExcelIndex(String name, String url) { int rsRows = 0; Map<Object, Object> map = new HashMap<Object, Object>(); try { InputStream is = new FileInputStream(url);//excel文件路径 jxl.Workbook rwb = Workbook.getWorkbook(is); // 获取第一张Sheet表 Sheet rs = rwb.getSheet(0);// 获取Sheet表中所包含的总行数，
rsRows = rs.getRows(); for (int i = 0; i < rsRows; i++) { Cell[] cell = rs.getRow(i);// 获取某一行的所有单元格，返回的是单元格对象数组 // 获取某一行的所有单元格 for (int j = 0; j < cell.length; j++) {Cell[] cell2 = rs.getColumn(j);
// 获取某一行的具体列单元格 for (int k = 0; k < cell2.length; k++) { if (name.trim().equalsIgnoreCase( cell[j].getContents().trim())) { Cell c = rs.getCell(j, i); // 第几行第几列(前门是列，后面是行) int rowIndex = i;// 行 int columnIndex = j;// 列 map.put("rowIndex", rowIndex); map.put("columnIndex", columnIndex); break; } }break;
}
break;
}
} catch (Exception e) { e.printStackTrace(); System.out.println("定位excel位置出错！！"); } return map; }// 定位excel标题位置（横向定位）
@SuppressWarnings("unused") public static Map<Object, Object> getExcelIndex2(String name, String url) { int rsRows = 0; Map<Object, Object> map = new HashMap<Object, Object>();try {
InputStream is = new FileInputStream(url); jxl.Workbook rwb = Workbook.getWorkbook(is); // 获取第一张Sheet表 Sheet rs = rwb.getSheet(0);// 获取Sheet表中所包含的总行数，
rsRows = rs.getRows(); for (int i = 0; i < rsRows; i++) { Cell[] cell = rs.getRow(i);// 获取某一行的所有单元格，返回的是单元格对象数组 // 获取某一行的所有单元格 for (int j = 0; j < cell.length; j++) {Cell[] cell2 = rs.getRow(j);
// 获取某一行的具体列单元格 for (int k = 0; k < cell2.length; k++) { if (name.trim().equalsIgnoreCase( cell[k].getContents().trim())) { Cell c = rs.getCell(j, k); // 第几行第几列(前门是列，后面是行) int rowIndex = j;// 行 int columnIndex = k;// 列map.put("rowIndex", rowIndex);
map.put("columnIndex", columnIndex);break;
} }break;
}
break;
}
} catch (Exception e) { e.printStackTrace(); System.out.println("定位excel位置出错！！"); } return map; }// 定位excel标题位置（横向定位）
@SuppressWarnings("unused") public static Integer getExcelIndex2(String name, String url, Integer rowIndex) {Integer columnIndex = 0;
try {
InputStream is = new FileInputStream(url); jxl.Workbook rwb = Workbook.getWorkbook(is); // 获取第一张Sheet表 Sheet rs = rwb.getSheet(0);Cell[] cell = rs.getRow(rowIndex);// 获取某一行的所有单元格，返回的是单元格对象数组
// 获取某一行的所有单元格 for (int j = 0; j < cell.length; j++) {Cell[] cell2 = rs.getRow(j);
// 获取某一行的具体列单元格 for (int k = 0; k < cell2.length; k++) { if (name.trim().equalsIgnoreCase( cell[k].getContents().trim())) { Cell c = rs.getCell(j, k); // 第几行第几列(前门是列，后面是行)columnIndex = k;// 列
break; } } break;}
} catch (Exception e) {
e.printStackTrace(); System.out.println("定位excel位置出错！！"); } return columnIndex;}