【JAVA】读取excel内容（兼容03和07格式）

jar 包：

    poi-3.13.jar

    poi-ooxml-3.13.jar

    poi-ooxml-schemas-3.13.jar

    xmlbeans-2.6.0.jar

 

    slf4j-api-1.4.3.jar

    slf4j-log4j12-1.4.3.jar

    log4j-1.2.17.jar

 

    junit-4.10.jar

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * @ClassName: ReadExcelUtils
 * @Description: 读取excel(兼容03和07格式)
 * @author 
 * @company 
 * @date 2015年12月18日
 * @version V1.0
 */

public class ReadExcelUtils {

	private static final Logger log = LoggerFactory
			.getLogger(ReadExcelUtils.class);

	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";

	/**
	 * 读取excel文件内容
	 * 
	 * @param filePath
	 * @throws Exception
	 */
	public List<Map<String, String>> readExcel(String filePath)
			throws Exception {

		log.info("Start to read excel file:" + filePath);

		List<Map<String, String>> list = new LinkedList<Map<String, String>>();

		// 检查
		this.preReadCheck(filePath);
		// 获取workbook对象
		Workbook workbook = null;

		workbook = this.getWorkbook(filePath);
		// 读文件 一个sheet一个sheet地读取
		for (int numSheet = 0; numSheet < workbook
				.getNumberOfSheets(); numSheet++) {
			Sheet sheet = workbook.getSheetAt(numSheet);
			if (sheet == null) {
				continue;
			}

			log.info("=======================" + sheet.getSheetName()
					+ "=========================");

			int firstRowIndex = sheet.getFirstRowNum();
			int lastRowIndex = sheet.getLastRowNum();

			// 读取首行 即,表头
			Row firstRow = sheet.getRow(firstRowIndex);

			log.info("firstRow=" + firstRow);

			if (null != firstRow) {

				ConcurrentMap<String, String> map = null;

				// 读取数据行
				for (int rowIndex = firstRowIndex
						+ 1; rowIndex <= lastRowIndex; rowIndex++) {
					// 当前行
					Row currentRow = sheet.getRow(rowIndex);
					// 首列
					int firstColumnIndex = currentRow.getFirstCellNum();
					// 最后一列
					int lastColumnIndex = currentRow.getLastCellNum();

					for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
						// 标题列
						Cell titleCell = firstRow.getCell(columnIndex);
						// 标题列对应的值
						String titleCellValue = this.getCellValue(titleCell,
								true);

						// 当前单元格
						Cell currentCell = currentRow.getCell(columnIndex);
						// 当前单元格的值
						String currentCellValue = this.getCellValue(currentCell,
								true);

						map = new ConcurrentHashMap<String, String>(16);

						map.put(titleCellValue, currentCellValue);

						list.add(map);
					}
				}
			} // end of if (null != firstRow)
		}

		log.info("Read excel file '" + filePath + "' finished.");

		return list;
	}

	/***
	 * <pre>
	 * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
	 *   xls:HSSFWorkbook
	 *   xlsx：XSSFWorkbook
	 * &#64;param filePath
	 * &#64;return
	 * &#64;throws IOException
	 * </pre>
	 */
	private Workbook getWorkbook(String filePath) throws IOException {

		Workbook workbook = null;
		InputStream is = null;

		try {
			is = new FileInputStream(filePath);

			if (filePath.endsWith(EXTENSION_XLS)) {
				workbook = new HSSFWorkbook(is);
			} else if (filePath.endsWith(EXTENSION_XLSX)) {
				workbook = new XSSFWorkbook(is);
			}

		} catch (FileNotFoundException e) {
			throw e;
		} catch (IOException e) {
			throw e;
		} finally {
			CommonsIOUtils.closeStream(is, null);
		}

		return workbook;
	}

	/**
	 * 文件检查
	 * 
	 * @param filePath
	 * @throws FileNotFoundException
	 * @throws FileFormatException
	 */
	private void preReadCheck(String filePath)
			throws FileNotFoundException, FileFormatException {
		// 常规检查
		File file = new File(filePath);
		if (!file.exists()) {
			throw new FileNotFoundException("传入的文件不存在：" + filePath);
		}

		if (!(filePath.endsWith(EXTENSION_XLS)
				|| filePath.endsWith(EXTENSION_XLSX))) {
			throw new FileFormatException("传入的文件不是excel");
		}
	}

	/**
	 * 取单元格的值
	 * 
	 * @param cell
	 *            单元格对象
	 * @param treatAsStr
	 *            为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
	 * @return
	 */
	private String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return "";
		}

		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
}

 

关闭 I/O 流：

public static void closeStream(InputStream is, OutputStream os) {

	if (null != is) {
		try {
			is.close();
			is = null;
		} catch (IOException e) {
			log.error("close InputStream fail!", e);
		}
	}

	if (null != os) {
		try {
			os.close();
			os = null;
		} catch (IOException e) {
			log.error("close OutputStream fail!", e);
		}
	}
}

 

文件内容：

 

测试（junit4）：

import java.util.List;
import java.util.Map;

import org.junit.Test;

public class TestReadExcelUtils {

	@Test
	public void readExcel() {
		try {
			// f:\\Test.xlsx
			List<Map<String, String>> list = new ReadExcelUtils()
					.readExcel("f:\\红外系统权限统计--系统设置.xls");

			if (null != list && !list.isEmpty()) {
				System.out.println(list);
			} else {
				System.out.println("Empty");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

测试结果：

[
{LEAF_=1}, {DISPLAY_NAME=图像归档日期}, {NAME=settting:imgfiledate}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=PMS远程调用地址}, {NAME=settting:pmsrpcurl}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=诊断规则列表}, {NAME=setting:rulelist}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=添加诊断规则}, {NAME=setting:addrule}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=编辑诊断规则}, {NAME=setting:editrule}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=删除诊断规则}, {NAME=setting:delrule}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=图谱特征提取列表}, {NAME=setting:charactlist}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=提取特征}, {NAME=setting:extractcharacter}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=用户管理列表}, {NAME=setting:userlist}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=添加用户}, {NAME=setting:adduser}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=编辑用户}, {NAME=setting:edituser}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=删除用户}, {NAME=setting:deluser}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=角色管理列表}, {NAME=setting:rolelist}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=添加角色}, {NAME=setting:addrole}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=编辑角色}, {NAME=setting:editrole}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=删除角色}, {NAME=setting:delrole}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=权限管理列表}, {NAME=setting:permissionlist}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=添加权限}, {NAME=setting:addperimssion}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=编辑权限}, {NAME=setting:editpermission}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}, 
{LEAF_=1}, {DISPLAY_NAME=删除权限}, {NAME=setting:delpermission}, {PARENT_=CED99042927A4B0A9C5326B3C1A98601}, {MENU=Y}, 
{=}
]

 

 

评论

1 楼 wajjj 2016-08-04  

[flash=200,200][url][img][list]

[*]

引用

[/list][/img][/url][/flash]

引用

[b][/b]