java jxl excelutil_jxl导出Excel工具类详解

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

/**

* 注意:此工具类只支持解析excel2003,不支持解析高版本的excel,如果解析高版本excel会报如下错误:

* jxl.read.biff.BiffException: Unable to recognize OLE stream

* 解决方案:将高版本excel文件另存为Excel97-2003工作薄,然后再解析.

* jxl较poi的好处是跨平台,因为是用纯java编写,poi虽然功能比jxl强大,但是是基于windows系统的.

* @author IluckySi

* @since 20141215

*/

public class JxlUtil {

private String filePath;

public String getPath() {

return filePath;

}

public void setPath(String filePath) {

this.filePath = filePath;

}

/**

* 解析excel文件.

* @return Map>>

*/

public Map>> parse() {

File file = new File(filePath);

if(!file.exists() || !file.getName().endsWith(".xls")) {

try {

throw new Exception("要解析的路径有问题: " + filePath);

} catch (Exception e) {

e.printStackTrace();

}

}

Map>> listListMap = new HashMap>>();

Workbook workBook = null;

FileInputStream fis = null;

try {

fis = new FileInputStream(file);

workBook = Workbook.getWorkbook(fis);

Sheet[] sheetArray = workBook.getSheets();

for(int i = 0; sheetArray != null && i < sheetArray.length; i++) {

Sheet sheet = sheetArray[i];

List> listList = parseSheet(sheet);

if(listList != null && listList.size() > 0) {

listListMap.put(sheet.getName(), listList);

}

}

} catch (BiffException e) {

System.out.println("解析文件发生异常: " + e);

} catch (IOException e) {

System.out.println("解析文件发生异常: " + e);

} finally {

try {

if(workBook != null) {

workBook.close();

workBook = null;

}

if(fis != null) {

fis.close();

fis = null;

}

} catch (Exception e) {

System.out.println("关闭文件流发生异常: " + e);

}

}

return listListMap;

}

/**

* 解析sheet,需要注意的地方:合并单元格,

* 例:如果A6-A12合并了单元格,那么解析excel时,解析类库只认为A6有值.

* @param sheet

*/

private List> parseSheet(Sheet sheet) {

List> listList = new ArrayList>();

int rowCount = sheet.getRows();

for(int i = 1; i < rowCount; i++) {

List list = new ArrayList();

Cell[] cellArray = sheet.getRow(i);

for(int j = 0; cellArray != null && j < cellArray.length; j++) {

list.add(cellArray[j].getContents());

}

listList.add(list);

}

return listList;

}

/**

* 将数据源写入到excel中.

* 注意:20150211加的写入方法.

* @param listListMap

* @return

*/

public boolean write(Map>> listListMap) {

File file = new File(filePath);

boolean result = false;

WritableWorkbook workBook = null;

FileOutputStream fos = null;

try {

fos = new FileOutputStream(file);

workBook = Workbook.createWorkbook(fos);

int sheetNo = 0;

for(Entry>> entry : listListMap.entrySet()) {

String key = entry.getKey();

List> listList = entry.getValue();

WritableSheet sheet = workBook.createSheet(key, sheetNo++);

for(int i = 0; i < listList.size(); i++) {

List list = listList.get(i);

for(int j = 0; j < list.size(); j++) {

Label label = new Label(j, i, list.get(j));

sheet.addCell(label);

}

}

}

workBook.write();

System.out.println("成功写入文件");

} catch (Exception e) {

System.out.println("写入文件发生异常: " + e);

} finally {

try {

if(workBook != null) {

workBook.close();

}

if(fos != null) {

fos.close();

}

} catch (IOException e) {

System.out.println("关闭文件流发生异常: " + e);

}

}

return result;

}

}