apache POI 读取 Excel

 

package org.cric.action;

import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.IOException;  
import java.util.Date;  
import java.util.HashMap;  
import java.util.Map;  
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.poifs.filesystem.POIFSFileSystem;  
  
public class ExcelReader {  
    private POIFSFileSystem fs;  
    private HSSFWorkbook wb;  
    private HSSFSheet sheet;  
    private HSSFRow row;  
    private FileInputStream input;  
    private String[] excleTitle;  
    public String[] readExcelTitle(String excelPath) {// 读取Excel表格表头的内容  
        try {  
            input = new FileInputStream(new File(excelPath));// excelPath,Excel  
            // 文件 的绝对路径  
            fs = new POIFSFileSystem(input);
            wb = new HSSFWorkbook(fs);
            sheet = wb.getSheetAt(0);  
            row = sheet.getRow(0);// 得到标题的内容对象。  
            int colNum = row.getPhysicalNumberOfCells(); // 得到标题总列数  
            excleTitle = new String[colNum];  
            for (int i = 0; i < colNum; i++) {  
                excleTitle[i] = getStringCellValue(row.getCell((short) i));  
            }  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if (input != null) {  
                    input.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return excleTitle;  
    }  
    public Map<Integer, String> readExcelContent(String excelPath) {// 读取Excel数据内容  
        Map<Integer, String> content = new HashMap<Integer, String>();  
        String excelStr = "";// excel 内容  
        try {  
            input = new FileInputStream(new File(excelPath));  
            fs = new POIFSFileSystem(input);  
            wb = new HSSFWorkbook(fs);  
            sheet = wb.getSheetAt(0);  
            int rowNum = sheet.getLastRowNum(); // 得到总行数  
            row = sheet.getRow(0);// 得到标题的内容对象。  
            int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。  
            for (int i = 1; i < rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题  
                row = sheet.getRow(i);  
                int j = 0;  
                while (j < colNum) {  
                    excelStr += getStringCellValue(row.getCell((short) j)).trim()+ "&";
                    j++;  
                }  
                content.put(i, excelStr);  
                excelStr = "";  
            }  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if (input != null) {  
                    input.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return content;  
    }  
    private String getStringCellValue(HSSFCell cell) {// 获取单元格数据内容为字符串类型的数据  
        String strCell = "";  
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_STRING:  
            strCell = cell.getStringCellValue();  
            break;  
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());  
            break;  
        case HSSFCell.CELL_TYPE_BOOLEAN:  
            strCell = String.valueOf(cell.getBooleanCellValue());  
            break;  
        case HSSFCell.CELL_TYPE_BLANK:  
            strCell = "";  
            break;  
        default:  
            strCell = "";  
            break;  
        }  
        if (strCell.equals("") || strCell == null) {  
            return "";  
        }  
        if (cell == null) {  
            return "";  
        }  
        return strCell;  
    }
    
    private String getDateCellValue(HSSFCell cell) {// 获取单元格数据内容为日期类型的数据  
        String result = "";  
        try {  
            int cellType = cell.getCellType();  
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {  
                Date date = cell.getDateCellValue();  
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)  
                        + "-" + date.getDate();  
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {  
                String date = getStringCellValue(cell);  
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();  
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {  
                result = "";  
            }  
        } catch (Exception e) {  
            System.out.println("日期格式不正确!");  
            e.printStackTrace();  
        }  
        return result;  
    }  
}

 

 

 

package org.cric.action;

import java.util.Collection;
import java.util.Iterator;
import java.util.Map;

public class ExcelReaderTest {

	public static void main(String[] args) {
		ExcelReader excelReader = new ExcelReader();
		String excelPath = new String("E:\\报表.xls");
		String[] headers = excelReader.readExcelTitle(excelPath);
		for(int i = 0; i < headers.length; i++){
			System.out.print(headers[i]+"\t");
			if(i == headers.length-1){
				System.out.println();
			}
		}
		System.out.println("--------------------------------------------------");
		
		Map<Integer,String> content = excelReader.readExcelContent(excelPath);
		Collection<String> collection = content.values();
		Iterator<String> it = collection.iterator();
		while(it.hasNext()){
			String value = it.next();
			String[] cellValue = value.split("&");
			for(int i = 0; i < cellValue.length; i++){
				System.out.print(cellValue[i]+"\t");
				if(i == cellValue.length-1){
					System.out.println();
				}
			}
			System.out.println("--------------------------------------------------");
		}
		
	}

}