java实现Excel的导入、导出

一、excel的导入

导入可采用两种方式，一种是jxl，另一种是poi，但前者不能读取高版本的excel(07以上)，后者更具兼容性。由于对两种方式都进行了尝试，就都贴出来分享(若有错误，请给予指正)

方式一、jxl导入  所需jar包 jxl.jar

publicstaticlist<putstorageinfo> readexcelbyjxl(string filepath){
list<putstorageinfo> infolist =newarraylist<putstorageinfo>();
map<string,list<string>> map =newhashmap<string,list<string>>();
 infolist.clear();
try{
inputstream is =newfileinputstream(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 < rows; i++){
list<string> contentlist =newarraylist<string>();
 contentlist.clear();
for(int j =1; j < columns; j++){
 contentlist.add(sheet.getcell(j,i).getcontents());
}
 map.put("storageinfo"+i, contentlist);
}

//遍历map集合，封装成bean
for(map.entry<string,list<string>> entry : map.entryset()){
list<string> list = entry.getvalue();
putstorageinfo storageinfo =newputstorageinfo();
 storageinfo.setproductcode(list.get(0));
 storageinfo.setproductsort(list.get(1));
 storageinfo.setproductbrand(list.get(2));
 storageinfo.setproductname(list.get(3));
 storageinfo.setproductquantity(list.get(4));
 storageinfo.setproductcontent(list.get(5));
 storageinfo.setproductnetweight(list.get(6));
 storageinfo.setproductcountry(list.get(7));
 storageinfo.setproductpdate(list.get(8));
 storageinfo.setproductprice(list.get(9));
 storageinfo.setproductmark(list.get(10));

 infolist.add(storageinfo);
}
 is.close();
}catch(exception e){
 e.printstacktrace();
}
return infolist;
}

方式二、poi导入 

所需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.jar

publicstaticlist<putstorageinfo> readexcelbypoi(string filepath){
list<putstorageinfo> infolist =newarraylist<putstorageinfo>();
map<string,list<string>> map =newhashmap<string,list<string>>();
 infolist.clear();
try{
inputstream is =newfileinputstream(filepath);

int index = filepath.lastindexof(".");
string postfix = filepath.substring(index+1);

workbook workbook =null;
if("xls".equals(postfix)){
 workbook =newhssfworkbook(is);
}elseif("xlsx".equals(postfix)){
 workbook =newxssfworkbook(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<string> contentlist =newarraylist<string>();
 contentlist.clear();
for(int j =1; j < columns; 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("storageinfo"+i, contentlist);
}

//遍历map集合，封装成bean
for(map.entry<string,list<string>> entry : map.entryset()){
list<string> list = entry.getvalue();
putstorageinfo storageinfo =newputstorageinfo();
 storageinfo.setproductcode(list.get(0));
 storageinfo.setproductsort(list.get(1));
 storageinfo.setproductbrand(list.get(2));
 storageinfo.setproductname(list.get(3));
 storageinfo.setproductquantity(list.get(4));
 storageinfo.setproductcontent(list.get(5));
 storageinfo.setproductnetweight(list.get(6));
 storageinfo.setproductcountry(list.get(7));
 storageinfo.setproductpdate(list.get(8));
 storageinfo.setproductprice(list.get(9));
 storageinfo.setproductmark(list.get(10));

 infolist.add(storageinfo);
}
 is.close();
}catch(exception e){
 e.printstacktrace();
}

return infolist;
} 

二、excel导出

采用jxl实现

publicstaticvoid createxcel(list<putstorageinfo> storageinfolist,string filename){
try{
outputstream os =newfileoutputstream(filename);
//创建可写的工作薄
writableworkbook workbook =workbook.createworkbook(os);
//创建第一张表
writablesheet sheet = workbook.createsheet("sheet1",0);
//设置根据内容自动宽度
cellview cellview =newcellview();
 cellview.setautosize(true);
//在下边for循环中为每一列设置

//设置列宽度,此种方式参数的意思，i-->对应的行或列 j-->要设置的宽度
// sheet.setcolumnview(0, 100);
// sheet.setrowview(0, 300);
//设置字体加粗且背景颜色为黄色
writablefont boldfont =newwritablefont(writablefont.arial,10,writablefont.bold);//黑体 
writablecellformat cellrformate =newwritablecellformat(boldfont);
 cellrformate.setbackground(colour.yellow);
//先添加表头
list<string> titlelist = gettitlelist();
//循环创建单元格,先列后行
for(int i =0; i < titlelist.size(); i++){
//sheet.setcolumnview(i, cellview);
 sheet.setcolumnview(i,20);

label label =newlabel(i,0, titlelist.get(i), cellrformate);
 sheet.addcell(label);
}

logutil.logout(jxlwriteexcel.class,storageinfolist.size()+"");

string[][] content = converttoarr(storageinfolist);
//设置content的自适应当前列的宽度，文本太对会自动换行 new label(j, i+1, content[i][j-1],contentformat);
writablecellformat contentformat =newwritablecellformat();
 contentformat.setwrap(true);

//然后添加入库信息条目
for(int i =0; i < storageinfolist.size(); i++){
label labelid =newlabel(0,i+1,(i+1)+"");
 sheet.addcell(labelid);

for(int j =1; j < titlelist.size(); j++){
label label =newlabel(j, i+1, content[i][j-1]);
 sheet.addcell(label);
}
}

//把创建的内容写入到输出流中，并关闭输出流
workbook.write();
 workbook.close();
 os.close();

//将存储了入库bean的list清空
storageinfolist.clear();

}catch(exception e){
 e.printstacktrace();
}
}

privatestaticstring[][] converttoarr(list<putstorageinfo> storageinfolist){
string[][] content =newstring[storageinfolist.size()][11];
for(int i =0; i < storageinfolist.size(); i++){
putstorageinfo info = storageinfolist.get(i);
//每个bean中总项有11项
content[i][0]= info.getproductcode();
 content[i][1]= info.getproductsort();
 content[i][2]= info.getproductbrand();
 content[i][3]= info.getproductname();
 content[i][4]= info.getproductquantity();
 content[i][5]= info.getproductcontent();
 content[i][6]= info.getproductnetweight();
 content[i][7]= info.getproductcountry();
 content[i][8]= info.getproductpdate();
 content[i][9]= info.getproductprice();
 content[i][10]= info.getproductmark();
}
return content;

}

privatestaticlist<string> gettitlelist(){
list<string> list =newarraylist<string>();
 list.add("item no.");
 list.add("product code");
 list.add("sort");
 list.add("brand");
 list.add("product name");
 list.add("quantity(pieces)");
 list.add("content");
list.add("net weight");
 list.add("country");
 list.add("best before date");
 list.add("price(euro)");
 list.add("remarks");

return list;
}

 以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。