apache POI 读取 Excel

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;
	}
}

 

评论

3 楼 tssnowlotus 2012-01-06  

cbxvsli 写道

你60行循环的时候有问题。永远会少列数据
正确的应该是：
// 正文内容应该从第二行开始,第一行为表头的标题
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 = "";
}

－－－－－－－－－－－－－－－－－－－－－－－
恩

2 楼 tssnowlotus 2012-01-06  

呵呵，这么久的贴子还有人回复呢。

1 楼 cbxvsli 2011-12-13  

你60行循环的时候有问题。永远会少列数据
正确的应该是：
// 正文内容应该从第二行开始,第一行为表头的标题
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 = "";
}