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<String, List<List<String>>>  
     */   
    public Map<String, List<List<String>>> parse() {   
        File file = new File(filePath);   
        if(!file.exists() || !file.getName().endsWith(".xls")) {   
            try {   
                throw new Exception("要解析的路径有问题: " + filePath);   
            } catch (Exception e) {   
                e.printStackTrace();   
            }   
        }   
        Map<String, List<List<String>>> listListMap = new HashMap<String, List<List<String>>>();   
        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<List<String>> 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<List<String>> parseSheet(Sheet sheet) {   
        List<List<String>> listList = new ArrayList<List<String>>();   
        int rowCount = sheet.getRows();   
        for(int i = 1; i < rowCount; i++) {   
            List<String> list = new ArrayList<String>();   
            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<String, List<List<String>>> 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<String, List<List<String>>> entry : listListMap.entrySet()) {   
                String key = entry.getKey();   
                List<List<String>> listList = entry.getValue();   
                WritableSheet sheet = workBook.createSheet(key, sheetNo++);   
                for(int i = 0; i < listList.size(); i++) {   
                    List<String> 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;   
    }   
}