java POI读取excel 2007/2003

时间：2023-03-08 18:13:31
2003版office excel读取

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Collections;

import java.util.Iterator;

import java.util.List;

import java.util.concurrent.CopyOnWriteArrayList;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ReadExcel03 {

    /**

     * 读取excel

     * @param is

     * @return

     * @throws FileNotFoundException

     * @throws IOException

     */

	public static  List<List<String>> readExcel(InputStream is)throws FileNotFoundException, IOException {

		// 构造 HSSFWorkbook 对象，传入参数为excel文件的io流

		HSSFWorkbook wb = new HSSFWorkbook(is);

		// 读取第一个sheet的内容

		HSSFSheet sheet = wb.getSheetAt(0);

		// 获取所有行的迭代对象

		Iterator<Row> rowIter = sheet.rowIterator();

		// 获取合并单元格对象的相关信息

		List<ExcelMergedRegionBean> mergedRegionMapList = new CopyOnWriteArrayList<ExcelMergedRegionBean>(getMergedRegionMapList(sheet));

		List<List<String>> contentList = new ArrayList<List<String>>();

		// 迭代所有行

		while (rowIter.hasNext()) {

			List<String> cellList = new ArrayList<String>();

			// 获取每一行

			Row row = rowIter.next();

			// 获取该行的列迭代对象

			Iterator<Cell> cellIter = row.cellIterator();

			// 迭代该行的每一列

			while (cellIter.hasNext()) {

				// 获取该行的每一列

				Cell cell = cellIter.next();

				// 获取该单元格的值

				String content = getActualCellValue(sheet, mergedRegionMapList,cell);

				cellList.add(content);

			}

			contentList.add(cellList);

		}

		return contentList;

	}

	/**

	 * 获取单元格真实的值

	 * @param sheet

	 * @param mergedRegionMapList

	 * @param myCell

	 * @return

	 */

	public static String getActualCellValue(HSSFSheet sheet,List<ExcelMergedRegionBean> mergedRegionMapList, Cell myCell) {

		Cell actualCell = myCell;

		// 迭代合并单元格对象，判断myCell该对象是否属于合并单元格

		for (ExcelMergedRegionBean mb : mergedRegionMapList) {

			if (myCell.getRowIndex() > mb.getLastRow()) {

				mergedRegionMapList.remove(mb);

			}

			// 判断myCell该对象是否属于合并单元格，如果是的话，则直接退出循环

			if (myCell.getColumnIndex() <= mb.getLastCell()

					&& myCell.getColumnIndex() >= mb.getFirstCell()

					&& myCell.getRowIndex() <= mb.getLastRow()

					&& myCell.getRowIndex() >= mb.getFirstRow()) {

				Row row = sheet.getRow(mb.getFirstRow());

				Cell cell = row.getCell(mb.getFirstCell());

				actualCell = cell;

				break;

			}

		}

		// 返回该单元对应的真实值

		return getCellValue(actualCell);

	}

	/**

	 * 处理合并的列

	 * @param sheet

	 * @return

	 */

	public static List<ExcelMergedRegionBean> getMergedRegionMapList(HSSFSheet sheet) {

		List<ExcelMergedRegionBean> mergedRegionMapList = new ArrayList<ExcelMergedRegionBean>();

		// 获得一个 sheet 中合并单元格的数量

		int sheetmergerCount = sheet.getNumMergedRegions();

		// 便利合并单元格

		for (int i = 0; i < sheetmergerCount; i++) {

			// 获得合并单元格

			CellRangeAddress ca = sheet.getMergedRegion(i);

			// 获得合并单元格的起始行, 结束行, 起始列, 结束列

			int firstC = ca.getFirstColumn();

			int lastC = ca.getLastColumn();

			int firstR = ca.getFirstRow();

			int lastR = ca.getLastRow();

			ExcelMergedRegionBean mb = new ExcelMergedRegionBean();

			mb.setFirstRow(firstR);

			mb.setLastRow(lastR);

			mb.setFirstCell(firstC);

			mb.setLastCell(lastC);

			mergedRegionMapList.add(mb);

		}

		// 排序，便于后面循环删除

		Collections.sort(mergedRegionMapList);

		return mergedRegionMapList;

	}

	/**

	 * 获得单元格的值

	 * @param cell

	 * @return

	 */

	public static String getCellValue(Cell cell) {

		String cellValue = "";

		if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

			cellValue = rPadZeroUtil(String.valueOf(cell.getNumericCellValue()));

		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {

			cellValue = String.valueOf(cell.getBooleanCellValue());

		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {

			cellValue = cell.getStringCellValue();

		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {

			cellValue = String.valueOf(cell.getCellFormula());

		}

		return cellValue;

	}

	public static String rPadZeroUtil(String value) {

		if (value != null && !"".equals(value)) {

			if (value.endsWith(".0")) {

				return value.substring(0, value.indexOf(".0"));

			}

		}

		return value;

	}

}

2007版 excel 读取

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Collections;

import java.util.Iterator;

import java.util.List;

import java.util.concurrent.CopyOnWriteArrayList;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel07 {

	/**

	 * 读取excel

	 * @param is

	 * @return

	 * @throws FileNotFoundException

	 * @throws IOException

	 */

	public static  List<List<String>> readExcel(InputStream is)throws FileNotFoundException, IOException {

		// 构造 XSSFWorkbook 对象，传入参数为excel文件的io流

		XSSFWorkbook wb = new XSSFWorkbook(is);

		// 读取第一个sheet的内容

		XSSFSheet sheet = wb.getSheetAt(0);

		// 获取所有行的迭代对象

		Iterator<Row> rowIter = sheet.rowIterator();

		// 获取合并单元格对象的相关信息

		List<ExcelMergedRegionBean> mergedRegionMapList = new CopyOnWriteArrayList<ExcelMergedRegionBean>(getMergedRegionMapList(sheet));

		List<List<String>> contentList = new ArrayList<List<String>>();

		// 迭代所有行

		while (rowIter.hasNext()) {

			List<String> cellList = new ArrayList<String>();

			// 获取每一行

			Row row = rowIter.next();

			// 获取该行的列迭代对象

			Iterator<Cell> cellIter = row.cellIterator();

			// 迭代该行的每一列

			while (cellIter.hasNext()) {

				// 获取该行的每一列

				Cell cell = cellIter.next();

				// 获取该单元格的值

				String content = getActualCellValue(sheet, mergedRegionMapList,cell);

				cellList.add(content);

			}

			contentList.add(cellList);

		}

		return contentList;

	}

	/**

	 * 获取单元格真实的值

	 * @param sheet

	 * @param mergedRegionMapList

	 * @param myCell

	 * @return

	 */

	public static String getActualCellValue(XSSFSheet sheet,List<ExcelMergedRegionBean> mergedRegionMapList, Cell myCell) {

		Cell actualCell = myCell;

		// 迭代合并单元格对象，判断myCell该对象是否属于合并单元格

		for (ExcelMergedRegionBean mb : mergedRegionMapList) {

			if (myCell.getRowIndex() > mb.getLastRow()) {

				mergedRegionMapList.remove(mb);

			}

			// 判断myCell该对象是否属于合并单元格，如果是的话，则直接退出循环

			if (myCell.getColumnIndex() <= mb.getLastCell()

					&& myCell.getColumnIndex() >= mb.getFirstCell()

					&& myCell.getRowIndex() <= mb.getLastRow()

					&& myCell.getRowIndex() >= mb.getFirstRow()) {

				Row row = sheet.getRow(mb.getFirstRow());

				Cell cell = row.getCell(mb.getFirstCell());

				actualCell = cell;

				break;

			}

		}

		// 返回该单元对应的真实值

		return getCellValue(actualCell);

	}

	/**

	 * 处理有合并的列

	 * @param sheet

	 * @return

	 */

	public static List<ExcelMergedRegionBean> getMergedRegionMapList(XSSFSheet sheet) {

		List<ExcelMergedRegionBean> mergedRegionMapList = new ArrayList<ExcelMergedRegionBean>();

		// 获得一个 sheet 中合并单元格的数量

		int sheetmergerCount = sheet.getNumMergedRegions();

		// 便利合并单元格

		for (int i = 0; i < sheetmergerCount; i++) {

			// 获得合并单元格

			CellRangeAddress ca = sheet.getMergedRegion(i);

			// 获得合并单元格的起始行, 结束行, 起始列, 结束列

			int firstC = ca.getFirstColumn();//第一列

			int lastC = ca.getLastColumn();//最后一列

			int firstR = ca.getFirstRow();//第一行

			int lastR = ca.getLastRow();//第二行

			ExcelMergedRegionBean mb = new ExcelMergedRegionBean();

			mb.setFirstRow(firstR);

			mb.setLastRow(lastR);

			mb.setFirstCell(firstC);

			mb.setLastCell(lastC);

			mergedRegionMapList.add(mb);

		}

		// 排序，便于后面循环删除

		Collections.sort(mergedRegionMapList);

		return mergedRegionMapList;

	}

	/**

	 * 获得单元格的值

	 * @param cell

	 * @return

	 */

	public static String getCellValue(Cell cell) {

		String cellValue = "";

		if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)

			cellValue = String.valueOf(cell.getNumericCellValue());

		 else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN)

			cellValue = String.valueOf(cell.getBooleanCellValue());

		else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)

			cellValue = cell.getStringCellValue();

		else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA)

			cellValue = String.valueOf(cell.getCellFormula());

		return cellValue;

	}

}

适配器

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

/**

 * 读取excel适配器

 * @author Administrator

 *

 */

public class ReadExcelAdapter {

	private ReadExcelAdapter(){}

	/**

	 * 适配方法:可读取03版本和07版本的excel

	 * @return

	 */

	public static List<List<String>> readExcel(String filePath){

		InputStream is = null;

		try {

			is = new FileInputStream(filePath);

			return ReadExcel03.readExcel(is);

		} catch (Exception e) {

			try {

				is = new FileInputStream(filePath);

				return ReadExcel07.readExcel(is);

			} catch (FileNotFoundException e1) {

				e1.printStackTrace();

			} catch (IOException e3) {

				e3.printStackTrace();

			}

		}

		return null;

	}

}

测试类

import java.util.List;

public class ReadExcelTest {

	public static void main(String[] args) throws Exception {

		long startTime = System.currentTimeMillis();

		List<List<String>> result =  ReadExcelAdapter.readExcel("C:/test.xlsx");

        System.out.println(result);

		System.out.println("use time:"+ (System.currentTimeMillis() - startTime));

	}

}

相关文章