java 对excel操作导入excel数据到数据库
加入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;
}
