Jxl Excel导入

来源：互联网发布：淘宝店铺联盟和淘客编辑：程序博客网时间：2024/05/22 13:52

说明：首先把excel上传到服务器。然后是针对空行的判断，导入成功，导入失败和重复导入的计数。最后是判断有合并单元格的处理情况

public void readExcel() throws ParseException{

StringBuffer sbf = new StringBuffer();
//首先把文件上传到服务器
UploadFile uf = getFile();
//获取文件对象
File f = uf.getFile();
//获取文件在服务器中的路径
String filePath = f.getPath();
System.out.println(filePath);
//String filePath = getAttrForStr("file");
InputStream fs = null;
Workbook workBook = null;
try {
// 加载excel文件
fs = new FileInputStream(filePath);
// 得到 workbook
workBook = Workbook.getWorkbook(fs);
// 取得sheet，如果你的workbook里有多个sheet 可以利用 wb.getSheets()方法来得到所有的。
int sheet_size = workBook.getNumberOfSheets();
System.out.println("sheet个数："+sheet_size);
Cell cell = null;
for(int i = 0;i < sheet_size;i++) {
Sheet sheet = workBook.getSheet(i);
int column = sheet.getColumns();
int row = sheet.getRows();
System.out.println("列数："+sheet.getColumns());
System.out.println("行数"+sheet.getRows());
//以Excel表的列数为基准
if(i == 0) {
int count = 0;
int rCount = 0;
int repeatCount = 0;
Map map = new HashMap();
Range[] rang = sheet.getMergedCells();
int nullCellNum;
for(int j = 1;j < row;j++) {
//把该行各列的值存入map中,首先判断是否有空行
nullCellNum = 0;
for(int c = 0; c < column; c++) {//循环列数
String val = sheet.getCell(c, j).getContents();
val = StringUtils.trimToEmpty(val);
if(StringUtils.isBlank(val)){
nullCellNum++;
}
}
if(nullCellNum >= column) {
//如果nullCellNum大于或等于总的列数
continue;
}else {
rCount++;
for(int k = 0; k < column; k++) {
cell = sheet.getCell(k, j);
for (Range r : rang) {
if (j > r.getTopLeft().getRow() && j <= r.getBottomRight().getRow() && k >= r.getTopLeft().getColumn() && k <= r.getBottomRight().getColumn()) {
cell = sheet.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow());
}
}
map.put(k, cell.getContents());
}
}
Map<String,String> attrs = new HashMap<String,String>();
attrs.put("fundid", map.get(sheet.getColumns()-1).toString());
attrs.put("level", map.get(5).toString());
attrs.put("ptype", 1+"");
attrs.put("code", map.get(1).toString());
attrs.put("startpoint", map.get(6).toString());
attrs.put("time", map.get(8).toString());
String boo = HttpParams("/products/findId", attrs);
if(!boo.equals("1")) {
repeatCount++;
continue;
}else {
//净值类投资产品
Map<String,String> params = new HashMap<String,String>();
params.put("name", map.get(0).toString());
params.put("code", map.get(1).toString());
params.put("type1", map.get(2).toString());
params.put("type2", map.get(3).toString());
params.put("type3", map.get(4).toString());
params.put("level", map.get(5).toString());
params.put("startpoint", map.get(6).toString());
params.put("pcomment", map.get(7).toString());
params.put("time", map.get(8).toString());
params.put("productnet", map.get(9).toString());
params.put("standard", map.get(10).toString());
params.put("ocri", map.get(11).toString());
params.put("fundid", map.get(12).toString());
params.put("uuid", JmwywUser.getUserId(getSession())+"");
String str = HttpParams("/products/save1", params);
if(!str.equals("0")) {//插入成功一条数据
count++;
}
}
}
sbf.append("净值类投资产品共："+rCount+"条数据。"+"导入成功："+count+"条，导入失败："+(rCount-count-repeatCount)+"条，重复:"+repeatCount+"条。<br>");
}else if(i == 1) {
int count = 0;
int rCount = 0;
int repeatCount = 0;
Map map = new HashMap();
int nullCellNum;
Range[] rang = sheet.getMergedCells();
for(int j = 1;j < row;j++) {
//把该行各列的值存入map中,首先判断是否有空行
nullCellNum = 0;
for(int c = 0; c < column; c++) {//循环列数
String val = sheet.getCell(c, j).getContents();
val = StringUtils.trimToEmpty(val);
if(StringUtils.isBlank(val)){
nullCellNum++;
}
}
if(nullCellNum >= column) {
//如果nullCellNum大于或等于总的列数
continue;
}else {
rCount++;
for(int k = 0; k < column; k++) {
cell = sheet.getCell(k, j);
for (Range r : rang) {
if (j > r.getTopLeft().getRow() && j <= r.getBottomRight().getRow() && k >= r.getTopLeft().getColumn() && k <= r.getBottomRight().getColumn()) {
cell = sheet.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow());
}
}
map.put(k, cell.getContents());
}
}
Map<String,String> attrs = new HashMap<String,String>();
attrs.put("fundid", map.get(sheet.getColumns()-1).toString());
attrs.put("level", map.get(5).toString());
attrs.put("ptype", 2+"");
attrs.put("code", map.get(1).toString());
attrs.put("startpoint", map.get(6).toString());
attrs.put("time", map.get(8).toString());
String boo = HttpParams("/products/findId", attrs);
if(!boo.equals("1")) {
repeatCount++;
continue;
}else {
//理财产品
Map<String,String> params = new HashMap<String,String>();
params.put("name", map.get(0).toString());
params.put("code", map.get(1).toString());
params.put("type1", map.get(2).toString());
params.put("type2", map.get(3).toString());
params.put("type3", map.get(4).toString());
params.put("level", map.get(5).toString());
params.put("startpoint", map.get(6).toString());
params.put("pcomment", map.get(7).toString());
params.put("time", map.get(8).toString());
params.put("lowest", map.get(9).toString());
params.put("max", map.get(10).toString());
params.put("standard", map.get(11).toString());
params.put("ocri", map.get(12).toString());
params.put("fundid", map.get(13).toString());
params.put("uuid", JmwywUser.getUserId(getSession())+"");
// params.put("ptype", value);
String str = HttpParams("/products/save2", params);
if(!str.equals("0")) {
count++;
}
}
}
sbf.append("理财产品共："+rCount+"条数据。"+"导入成功："+count+"条，导入失败："+(rCount-count-repeatCount)+"条，重复:"+repeatCount+"条。<br>");
}else if(i == 2) {
int count = 0;
int rCount = 0;
int repeatCount = 0;
Map map = new HashMap();
int nullCellNum;
Range[] rang = sheet.getMergedCells();
for(int j = 1;j < row;j++) {
//把该行各列的值存入map中,首先判断是否有空行
nullCellNum = 0;
for(int c = 0; c < column; c++) {//循环列数
String val = sheet.getCell(c, j).getContents();
val = StringUtils.trimToEmpty(val);
if(StringUtils.isBlank(val)){
nullCellNum++;
}
}

if(nullCellNum >= column) {
//如果nullCellNum大于或等于总的列数
continue;
}else {
rCount++;
for(int k = 0; k < column; k++) {
cell = sheet.getCell(k, j);
for (Range r : rang) {
if (j > r.getTopLeft().getRow() && j <= r.getBottomRight().getRow() && k >= r.getTopLeft().getColumn() && k <= r.getBottomRight().getColumn()) {
cell = sheet.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow());
}
}
map.put(k, cell.getContents());
}
}
Map<String,String> attrs = new HashMap<String,String>();
attrs.put("fundid", map.get(sheet.getColumns()-2).toString());
attrs.put("level", map.get(5).toString());
attrs.put("ptype", 3+"");
attrs.put("code", map.get(1).toString());
attrs.put("startpoint", map.get(6).toString());
attrs.put("time", "");
String boo = HttpParams("/products/findId", attrs);
if(!boo.equals("1")) {
repeatCount++;
continue;
}else {
//公募基金
Map<String,String> params = new HashMap<String,String>();
params.put("name", map.get(0).toString());
params.put("code", map.get(1).toString());
params.put("type1", map.get(2).toString());
params.put("type2", map.get(3).toString());
params.put("type3", map.get(4).toString());
params.put("level", map.get(5).toString());
params.put("startpoint", map.get(6).toString());
params.put("standard", map.get(7).toString());
params.put("benchmark", map.get(8).toString());
params.put("fundid", map.get(9).toString());
params.put("ocri", map.get(10).toString());
params.put("uuid", JmwywUser.getUserId(getSession())+"");
String str = HttpParams("/products/save3", params);
if(!str.equals("0")) {
count++;
// closeWin();
}
}
}
sbf.append("公募基金共："+rCount+"条数据。"+"导入成功："+count+"条，导入失败："+(rCount-count-repeatCount)+"条，重复:"+repeatCount+"条。<br>");
}else if(i == 3) {
int count = 0;
int rCount = 0;
int repeatCount = 0;
Map map = new HashMap();
int nullCellNum;
Range[] rang = sheet.getMergedCells();
for(int j = 1;j < row;j++) {
//把该行各列的值存入map中,首先判断是否有空行
nullCellNum = 0;
for(int c = 0; c < column; c++) {//循环列数
String val = sheet.getCell(c, j).getContents();
val = StringUtils.trimToEmpty(val);
if(StringUtils.isBlank(val)){
nullCellNum++;
}
}
if(nullCellNum >= column) {
//如果nullCellNum大于或等于总的列数
continue;
}else {
rCount++;
for(int k = 0; k < column; k++) {
cell = sheet.getCell(k, j);
for (Range r : rang) {
if (j > r.getTopLeft().getRow() && j <= r.getBottomRight().getRow() && k >= r.getTopLeft().getColumn() && k <= r.getBottomRight().getColumn()) {
cell = sheet.getCell(r.getTopLeft().getColumn(), r.getTopLeft().getRow());
}
}
map.put(k, cell.getContents());
}
}
Map<String,String> attrs = new HashMap<String,String>();
attrs.put("fundid", map.get(sheet.getColumns()-1).toString());
attrs.put("level", map.get(5).toString());
attrs.put("ptype", 4+"");
attrs.put("code", map.get(1).toString());
attrs.put("startpoint", map.get(6).toString());
attrs.put("time", map.get(8).toString());
String boo = HttpParams("/products/findId", attrs);
if(!boo.equals("1")) {
repeatCount++;
continue;
}else {
//存款类
Map<String,String> params = new HashMap<String,String>();
params.put("name", map.get(0).toString());
params.put("code", map.get(1).toString());
params.put("type1", map.get(2).toString());
params.put("type2", map.get(3).toString());
params.put("type3", map.get(4).toString());
params.put("level", map.get(5).toString());
params.put("startpoint", map.get(6).toString());
params.put("pcomment", map.get(7).toString());
params.put("time", map.get(8).toString());
params.put("lowest", map.get(9).toString());
params.put("max", map.get(10).toString());
params.put("standard", map.get(11).toString());
params.put("ocri", map.get(12).toString());
params.put("fundid", map.get(13).toString());
params.put("uuid", JmwywUser.getUserId(getSession())+"");
String str = HttpParams("/products/save4", params);
if(!str.equals("0")) {
count++;
}
}
}
sbf.append("存款类共："+rCount+"条数据。"+"导入成功："+count+"条，导入失败："+(rCount-count-repeatCount)+"条，重复:"+repeatCount+"条。<br>");
}
}
setAttr("msg",sbf);
render("/m/products/exl.jsp");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

//closeWin();
}

0 0