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 * @param filePath * @return * @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}, {=} ]
相关推荐
java读取excel的工具类型,可同时支持excel2003和2007版本
java读取excel,并且兼容2003和2007. 附带实例 可自定义表头,行读取开始位置、结束位置,列开始位置、结束位置
java 读取 Excel 读取 兼容2003,2007
java读取excel(兼容2003和2007)[定义].pdf
java代码读取excel文件,同时兼容2003和2007
java读取excel文件,兼容xlsx格式,可以用于07版本和10版本
/*** * 读取Excel文件里数据... * 读取Excel的兼容性/03/07以上 * 参数一读取所需的文件对象 * 参数二自定义需要对应实体对象相关的属性 * 参数三将数据转化成对应的class实体对象 * 参数四从文档的第几行读取 */
java 读取 Excel兼容2003,2007的项目,里面有需要的jar包和类
java 读取 Excel兼容2003,2007的项目,里面有需要的jar包和类
ExcelUtil万能读取类兼容03/07
poi读取excel2007和2003兼容工具例子.利用简单工厂方法
直接读取excel,兼容2003,以及2007以上版本,发送邮件源码,有问题可以直接加我
JAVA用POI读取和创建2003和2007版本Excel完美示例 同时还有相关的类包 注意工程里面别存在冲突类包
java编写的兼容2003、2007Excel文件读取工程,本人亲自编写
最好用的excel导入导出工具for Java(兼容2003和2007) 读取excel为List,Object>>方便转换,excel2db,db2excel
ExcelUtil借助反射和POI对Excel读取,省略了以往读取Excel的繁琐步骤,调用ExcelUtil只需要2步,对,你没有看错,2步足以读取到Excel的内容.兼容03/07版Excel.
使用POI官网上的事件驱动模式的示例方法,读取单sheet的Excel表格文件(.xlsx),Microsoft Excel和WPS Excel创建的表格文件可以正常读取数据,但是java代码创建的表格文件(不使用软件打开并保存)却读取不到数据。...
一个读取excel2003、2007、2010的类
ExcelUtil 借助反射和 POI 对 Excel 读取,省略了以往读取 Excel 的繁琐步骤,调用 ExcelUtil 只需要2步,对,你没有看错,2步足以读取到 Excel 的内容。...兼容03,07版Excel。大家项目中直接引入maven就可以了。
applications和applet,applications可以在控制台直接运行,与其他高级编程语言没有太大区别,而java的特色在于它具有编制小应用程序的功能,applet可以在internet上传输并在兼容java的web浏览器中运行的程序;...