POI框架实现EXCEL文件读写

 工作需要做一个小工具，把从服务器上爬下来的数据写入到xls文件中，目前工具已经能够使用，xlsx文件还不支持，有时间继续完善。

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.util.*;
import org.apache.commons.math3.util.*;

public class ExcelIO {
	
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
	
	public boolean readExcelFile(String path) {
		String excelType = path.substring(path.lastIndexOf(".") + 1, path.length());
		if (excelType.equalsIgnoreCase(XLS)) {
			readXLSFile(path);
        } else if (excelType.equalsIgnoreCase(XLSX)) {
        	readXLSXFile(path);
        }
		
		return true;
	}
	
	public boolean writeStringToXLSFileCell(String file, short sheet, short row, short column, String value) {
	    try {
            InputStream in = new FileInputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            //System.out.printf("Sheet num %d\n",  wb.getNumberOfSheets());
            if (sheet > wb.getNumberOfSheets()) {
                wb.close();
                in.close();
                return false;
            }
            
            HSSFSheet hs = wb.getSheetAt(sheet);
	        HSSFRow hr = hs.getRow((short)(row)); 
	        HSSFCell hc = hr.getCell(column);
	        
	        //System.out.printf("Last Row %d\n",  hs.getLastRowNum());
	        //System.out.printf("Last Cel %d\n",  hr.getLastCellNum());
            if ((row > hs.getLastRowNum()) || (column >= hr.getLastCellNum())){
                wb.close();
                in.close();
                return false;
            }
            
	        hc.setCellValue(value);
	        
	        FileOutputStream out = new FileOutputStream(file);
	        out.flush();
	        wb.write(out);    
	        out.close();
	        
            wb.close();
	        in.close();
	        
	    }catch(IOException e) {
            e.printStackTrace();
            return false;
        }
	    
	    return true;
	}
	
    public boolean insertListToXLSFileRow(String file, short sheet, short row, List list) {
        HSSFSheet hs;
        HSSFRow hr;
        HSSFCell hc;
        try {
            InputStream in = new FileInputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            //System.out.printf("Sheet num %d\n",  wb.getNumberOfSheets());
            if (sheet > wb.getNumberOfSheets()) {
                hs = wb.createSheet(String.valueOf(sheet));
                
            }else {
                hs = wb.getSheetAt(sheet);           
            }
            
            //System.out.printf("Last Row %d\n",  hs.getLastRowNum());
            //System.out.printf("Last Cel %d\n",  hr.getLastCellNum());
            if (row > hs.getLastRowNum()){
                hr = hs.createRow(row);
            }else {
                hr = hs.getRow((short)(row));                
            }
            
            for (int i = 0; i < list.size(); i++) {
                if ((i >= hr.getLastCellNum())) {
                    hr.createCell(i);
                }
                    
                hc = hr.getCell(i);
                hc.setCellValue((String)list.get(i));
            }
            
            FileOutputStream out = new FileOutputStream(file);
            out.flush();
            wb.write(out);    
            out.close();
            
            wb.close();
            in.close();
            
        }catch(IOException e) {
            e.printStackTrace();
            return false;
        }
        
        return true;
    }
	
    public String readStringFromXLSFileCell(String file, short sheet, short row, short column) {
        String value = null;
        try {
            InputStream in = new FileInputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            
            if (sheet > wb.getNumberOfSheets()) {
                wb.close();
                in.close();
                return value;
            }
            
            HSSFSheet hs = wb.getSheetAt(sheet);
            System.out.println(hs.getSheetName());
            
            if (row > hs.getLastRowNum()){
                wb.close();
                in.close();
                return value;
            }

            HSSFRow hr = hs.getRow(row);
            if (column >= hr.getLastCellNum()){
                wb.close();
                in.close();
                return value;
            }
            
            HSSFCell hc = hr.getCell(column);

            switch (hc.getCellType()) { 
            case BOOLEAN: 
                System.out.print(String.valueOf(hc.getBooleanCellValue()) + "  "); 
                break;
            case NUMERIC: 
                System.out.print(String.valueOf(hc.getNumericCellValue()) + "  "); 
                break;
            default: 
                System.out.print(String.valueOf(hc.getStringCellValue()) + "  ");
                break;
            }
            
            value = String.valueOf(hc.getStringCellValue());

            if(in != null){
                in.close();
            }
            if(wb != null){
                wb.close();
            }
            
        }catch(IOException e) {
            e.printStackTrace();
        }
        
        
        return value;
        
    }
    
    public int getLastRowNum(String file, short sheet) {
        int num = -1;
        try {
            InputStream in = new FileInputStream(file);
            HSSFWorkbook wb = new HSSFWorkbook(in);
            if (sheet > wb.getNumberOfSheets()) {
                wb.close();
                in.close();
                return num;
            }
            
            HSSFSheet hs = wb.getSheetAt(sheet);
            System.out.println(hs.getSheetName());
            
            num = hs.getLastRowNum();

            if(in != null){
                in.close();
            }
            if(wb != null){
                wb.close();
            }
            
        }catch(IOException e) {
            e.printStackTrace();
        }
        
        return num;
        
    }
	
	private boolean readXLSFile(String path) {
		try {
			InputStream is = new FileInputStream(path);
			HSSFWorkbook wb = new HSSFWorkbook(is);

			for(int sheet=0; sheet < wb.getNumberOfSheets(); sheet++){
				HSSFSheet hs = wb.getSheetAt(sheet);
				System.out.println(hs.getSheetName());
				if(hs == null){
					continue;
				}

				for(int row = 0; row <= hs.getLastRowNum(); row++){
					HSSFRow hr = hs.getRow(row);
					if(hr == null){
						continue;
					}

					for(int cell =0; cell <= hr.getLastCellNum(); cell++){
						HSSFCell hc = hr.getCell(cell);
						if(hc == null){
							continue;
						}
						//判断单元格数据类型
						switch (hc.getCellType()) { 
						case BOOLEAN: 
							System.out.print(String.valueOf(hc.getBooleanCellValue()) + "  "); 
							break;
						case NUMERIC: 
							System.out.print(String.valueOf(hc.getNumericCellValue()) + "  "); 
							break;
						default: 
							System.out.print(String.valueOf(hc.getStringCellValue()) + "  ");
							break;
						}
					}
					System.out.println();
				}
				System.out.println();
			}
			if(is != null){
				is.close();
			}
			if(wb != null){
			    wb.close();
			}
		}catch(IOException e) {
			e.printStackTrace();
		}
		
		return true;
		
	}
	
	private boolean readXLSXFile(String path) {
		try {
		    InputStream is = null;
		    try
	        {
	            is = new FileInputStream(path);
	        }
	        catch (FileNotFoundException e)
	        {
	            System.out.println("文件不存在或者文件不可读或者文件是目录");
	            return false;
	        } 
		    XSSFWorkbook wb = null;
		    try
            {
                wb = new XSSFWorkbook(is);
            }
            catch (FileNotFoundException e)
            {
                System.out.println("xls文件不存在或者文件不可读或者文件是目录");
                return false;
            } 

			for(int sheet=0; sheet < wb.getNumberOfSheets(); sheet++){
				
				XSSFSheet xs = wb.getSheetAt(sheet);
				System.out.println(xs.getSheetName());
				if(xs == null){
					continue;
				}

				for(int row = 0; row <= xs.getLastRowNum(); row++){
					XSSFRow xr = xs.getRow(row);
					if(xr == null){
						continue;
					}

					for(int cell =0; cell <= xr.getLastCellNum(); cell++){
						XSSFCell xc = xr.getCell(cell);
						if(xc == null){
							continue;
						}

						switch (xc.getCellType()) { 
						case BOOLEAN: 
							System.out.print(String.valueOf(xc.getBooleanCellValue()) + "  "); 
							break;
						case NUMERIC: 
							System.out.print(String.valueOf(xc.getNumericCellValue()) + "  "); 
							break;
						default: 
							System.out.print(String.valueOf(xc.getStringCellValue()) + "  ");
							break;
						}
					}
					System.out.println();
				}
				System.out.println();
			}
			if(is != null){
				is.close();
			}
			if(wb != null){
				wb.close();
			}
		}catch(IOException e) {
			e.printStackTrace();
		}
		
		
		return true;
		
	}
	
	public boolean createExcelFile(String fileName, String sheetName) {
	    HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet(sheetName);
	    HSSFRow row = sheet.createRow(0);
	    
	    try {
	        FileOutputStream output = new FileOutputStream(fileName);
	        wb.write(output);
	        output.flush();
	        
	        
	        output.close();
	        wb.close();	        
        }catch(IOException e) {
            e.printStackTrace();
            return false;
        }

		return true;
	}

}