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會(huì)報(bào)如下錯(cuò)誤: 
 * jxl.read.biff.BiffException: Unable to recognize OLE stream 
 * 解決方案:將高版本excel文件另存為Excel97-2003工作薄,然后再解析. 
 * jxl較poi的好處是跨平臺(tái),因?yàn)槭怯眉僯ava編寫(xiě),poi雖然功能比jxl強(qiáng)大,但是是基于windows系統(tǒng)的. 
 * @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("要解析的路徑有問(wèn)題: " + 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("解析文件發(fā)生異常: " + e);  
        } catch (IOException e) {  
            System.out.println("解析文件發(fā)生異常: " + e);  
        } finally {  
            try {  
                if(workBook != null) {  
                    workBook.close();  
                    workBook = null;  
                }  
                if(fis != null) {  
                    fis.close();  
                    fis = null;  
                }  
            } catch (Exception e) {  
                System.out.println("關(guān)閉文件流發(fā)生異常: " + e);  
            }  
        }  
        return listListMap;  
    }  
      
    /** 
     * 解析sheet,需要注意的地方:合并單元格, 
     * 例:如果A6-A12合并了單元格,那么解析excel時(shí),解析類庫(kù)只認(rèn)為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;  
    }  
      
    /** 
     * 將數(shù)據(jù)源寫(xiě)入到excel中. 
     * 注意:20150211加的寫(xiě)入方法. 
     * @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("成功寫(xiě)入文件");  
        } catch (Exception e) {  
            System.out.println("寫(xiě)入文件發(fā)生異常: " + e);  
        } finally {  
            try {  
                if(workBook != null) {  
                    workBook.close();  
                }   
                if(fos != null) {  
                    fos.close();  
                }  
            } catch (IOException e) {  
                System.out.println("關(guān)閉文件流發(fā)生異常: " + e);  
            }  
        }  
        return result;  
    }  
}  

標(biāo)簽：

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。