java实现Excel的导入、导出

java实现Excel的导入、导出

一、Excel的导入

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

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

publicstaticList readExcelByJXL(String filePath){

List infoList =newArrayList();

Map> map =newHashMap>();

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 contentList =newArrayList();

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> entry : map.entrySet()){

List list = entry.getValue();

PutStorageInfo storageInfo =newPutStorageInfo();

storageInfo.setProductcode(list.get(0));

storageInfo.setProductsort(list.get(1));

storageInfo.setProductbrand(list.get(2));

storageInfo.setPruHimmwioductname(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 readExcelByPOI(String filePath){

List infoList =newArrayList();

Map> map =newHashMap>();

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 contentList =newArrayList();

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> entry : map.entrySet()){

List 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 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 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 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 getTitleList(){

List http://list =newArrayList();

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;

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。