Java开发中经常会遇到需要导出Excel数据表或根据Excel中数据生成对应的数据库表,下面本站素文宅博客blog.yoodb.com为大家讲述两种Excel导入方式,分别是利用JXL和POI技术实现Excel导入,注意的是JXL不能读取高版本的Excel(07版本以上),而POI兼容性相对不错。
1. Java实现Excel导入
方式一:JXL导入Excel
JXL是用java完成的一个项目,使用jxl.jar可以方便的来操做excel(对于excel2010并不支持)
jxl官方下载地址:http://sourceforge.net/projects/jxl/
jxl官方文档:http://jxl.sourceforge.net/javadoc/index.htmlpublic static List readExcelByJXL(String filePath){
List infoList =new ArrayList();
Map map =new HashMap();
infoList.clear();
try{
InputStream is =new FileInputStream(filePath);
Workbook workbook =Workbook.getWorkbook(is);
//获取第1张表
Sheet sheet = workbook.getSheet(0);
//获取总的列数
int columns = sheet.getColumns();
//获取总的行数
int rows = sheet.getRows();
//先列后行(j,i)
for(int i =1; i
List contentList =new ArrayList();
contentList.clear();
for(int j =1; j
contentList.add(sheet.getCell(j,i).getContents());
}
map.put("info"+i, contentList);
}
//遍历map集合,封装成bean
for(Map.Entry entry : map.entrySet()){
List list = entry.getValue();
ProductInfo info =new ProductInfo();
info.setProductcode(list.get(0));
info.setProductsort(list.get(1));
info.setProductbrand(list.get(2));
info.setProductname(list.get(3));
info.setProductquantity(list.get(4));
info.setProductcontent(list.get(5));
info.setProductnetweight(list.get(6));
info.setProductcountry(list.get(7));
info.setProductpdate(list.get(8));
info.setProductprice(list.get(9));
info.setProductmark(list.get(10));
infoList.add(info);
}
is.close();
}catch(Exception e){
e.printStackTrace();
}
return infoList;
}
方式二:POI导入Excel
所需jar包,包含如下:
poi-3.6-20091214.jar
poi-ooxml-3.6-20091214.jar
poi-ooxml-schemas-3.6-20091214.jar
xmlbeans-2.3.0.jar
dom4j-1.6.1.jar
jdom-2.0.6.jarpublic static List readExcelByPOI(String filePath){
List infoList =new ArrayList();
Map map =new HashMap();
infoList.clear();
try{
InputStream is = new FileInputStream(filePath);
int index = filePath.lastIndexOf(".");
String postfix = filePath.substring(index+1);
Workbook workbook =null;
if("xls".equals(postfix)){
workbook =new HSSFWorkbook(is);
}elseif("xlsx".equals(postfix)){
workbook =new XSSFWorkbook(is);
}
//获取第1张表
Sheet sheet = workbook.getSheetAt(0);
//总的行数
int rows = sheet.getLastRowNum();
//总的列数--->最后一列为null则有问题,读取不完整,将表头的数目作为总的列数,没有的则补为null
int columns = sheet.getRow(0).getLastCellNum();
//先列后行
for(int i =1; i <= rows; i++){
Row row = sheet.getRow(i);
if(null!= row && row.getFirstCellNum()==-1){//这一行是空行,不读取
continue;
}
//这一行的总列数
// columns = row.getLastCellNum();
List contentList =new ArrayList();
contentList.clear();
for(int j =1; j
if(row.getCell(j)!=null){
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
contentList.add(row.getCell(j).getStringCellValue());
}else{
contentList.add("");
}
}
map.put("info"+i, contentList);
}
//遍历map集合,封装成bean
for(Map.Entry entry : map.entrySet()){
List list = entry.getValue();
ProductInfo info =new ProductInfo();
info.setProductcode(list.get(0));
info.setProductsort(list.get(1));
info.setProductbrand(list.get(2));
info.setProductname(list.get(3));
info.setProductquantity(list.get(4));
info.setProductcontent(list.get(5));
info.setProductnetweight(list.get(6));
info.setProductcountry(list.get(7));
info.setProductpdate(list.get(8));
info.setProductprice(list.get(9));
info.setProductmark(list.get(10));
infoList.add(info);
}
is.close();
}catch(Exception e){
e.printStackTrace();
}
return infoList;
}