POI导入excel文件2
POI上传到服务器读取excel文件1中已经介绍了上传文件和导入excel所有的内容http://www.cnblogs.com/fxwl/p/5896893.html ，
本文中只是单单读取本地文件并打印到空值台
并对列合并单元格进行了简单的排除
package com.daat.manager.base.web.tools; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.PushbackInputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.poifs.filesystem.POIFSFileSystem; 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.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; public class ExcelInfo { public static void main(String[] args){ long start2003 = System.currentTimeMillis(); String path2003 = "D:\\"; String FileName="aaaaa.xls"; excelInfo(path2003,FileName); long end2003 = System.currentTimeMillis(); System.out.println("解析Excel2003完毕！共用时"+(end2003-start2003)+"毫秒！"); } public static List excelInfo(String path,String FileName){ List excellist=new ArrayList(); String path2003 = "";//Excel2003文件路径 //解析Excel excellist = parseExcel(path+File.separator+FileName); System.out.println(excellist.size()); // deleteFile(path,FileName); return excellist; } /** * 根据路径加载解析Excel * @param path * @return */ public static List parseExcel(String path){ List excellist = new ArrayList(); System.out.println(path); Map map = new HashMap(); File file = null; InputStream input = null; Workbook workBook = null; Sheet sheet = null; if (path != null && path.length() > 7) { // 判断文件是否是Excel(2003、2007) String suffix = path.substring(path.lastIndexOf("."), path.length()); if (".xls".equals(suffix) || ".xlsx".equals(suffix)) {// 2003后缀或2007后缀 file = new File(path); try { input = new FileInputStream(file); } catch (FileNotFoundException e) { e.printStackTrace(); throw new RuntimeException("未找到指定的文件！"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException("读取Excel文件发生异常！"); } if (!input.markSupported()) { input = new PushbackInputStream(input, 8); } try { if (POIFSFileSystem.hasPOIFSHeader(input) || POIXMLDocument.hasOOXMLHeader(input)) { workBook = WorkbookFactory.create(input); } else { throw new RuntimeException("非法的输入流：当前输入流非OLE2流或OOXML流！"); } } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("创建表格工作簿对象发生IO异常！原因："+ e.getMessage()); } catch (InvalidFormatException e) { // Your InputStream was neither an OLE2 stream, nor an OOXML // stream. e.printStackTrace(); throw new RuntimeException("非法的输入流：当前输入流非OLE2流或OOXML流！"); } try { if (workBook != null) { int numberSheet = workBook.getNumberOfSheets(); if (numberSheet > 0) { sheet = workBook.getSheetAt(0);// 获取第一个工作簿(Sheet)的内容【注意根据实际需要进行修改】 //getExcelContent(sheet); readExcelSheet(sheet,0); } else { throw new RuntimeException("目标表格工作簿(Sheet)数目为0！"); } } input.close(); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("关闭输入流异常！" + e.getMessage()); } } else { throw new RuntimeException("非法的Excel文件后缀！"); } } else { throw new RuntimeException("非法的文件路径!"); } return excellist; } /** * 解析(读取)Excel内容 * @param sheet * @return */ @SuppressWarnings("static-access") public static void getExcelContent(Sheet sheet){ int rowCount = sheet.getPhysicalNumberOfRows();// 总行数 int colCount = sheet.getRow(0).getPhysicalNumberOfCells();// 总列数 if(rowCount<=0){ throw new RuntimeException("内容为空"); } Row titleRow=sheet.getRow(0);//标题行 for(int i=0;i<rowCount;i++){ List excetList =new ArrayList(); Row row = sheet.getRow(i);// 第i行 Cell cell = row.getCell(0);// 列1列 for(int j=0;j<colCount;j++){ if(cell.getCellType() == cell.CELL_TYPE_BLANK){//空值 excetList.add(" "); }else if(cell.getCellType()==cell.CELL_TYPE_STRING){//string类型 excetList.add(cell.getStringCellValue().trim()); }else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){//数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期类型 excetList.add(cell.getDateCellValue()); }else{ excetList.add(cell.getNumericCellValue()); } } } System.out.println("第"+i+1+"行-----"+excetList.toString()); } } /** * 读取excel文件 * @param wb * @param sheetIndex sheet页下标：从0开始 * @param startReadLine 开始读取的行:从0开始 * @param tailLine 去除最后读取的行 */ public static void readExcelSheet( Sheet sheet, int startReadLine) { Row row = null; List strlist=new ArrayList(); for(int i=startReadLine; i<=sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for(Cell c : row) { c.setCellType(Cell.CELL_TYPE_STRING); boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex()); //判断是否具有合并单元格 if(isMerge) { String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); if(!contains(strlist,rs)){ System.out.print(rs + " "); } }else { System.out.print(c.getRichStringCellValue()+" "); } } System.out.println(); } } /** * 获取合并单元格的值 * @param sheet * @param row * @param column * @return */ public static String getMergedRegionValue(Sheet sheet ,int row , int column){ int sheetMergeCount = sheet.getNumMergedRegions(); for(int i = 0 ; i < sheetMergeCount ; i++){ CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ Row fRow = sheet.getRow(firstRow); Cell fCell = fRow.getCell(firstColumn); return getCellValue(fCell) ; } } } return null ; } /** * 获取单元格的值 * @param cell * @return */ public static String getCellValue(Cell cell) { if (cell == null) return ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } return ""; } /** * 判断合并了行 * @param sheet * @param row * @param column * @return */ @SuppressWarnings("unused") public static boolean isMergedRow(Sheet sheet,int row ,int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if(row == firstRow && row == lastRow){ if(column >= firstColumn && column <= lastColumn){ return true; } } } return false; } /** * 判断指定的单元格是否是合并单元格 * * @param sheet * @param row * 行下标 * @param column * 列下标 * @return */ @SuppressWarnings("unused") public static boolean isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } /** * 判断sheet页中是否含有合并单元格 * @param sheet * @return */ public boolean hasMerged(Sheet sheet) { return sheet.getNumMergedRegions() > 0 ? true : false; } /** * 合并单元格 * * @param sheet * @param firstRow * 开始行 * @param lastRow * 结束行 * @param firstCol * 开始列 * @param lastCol * 结束列 */ @SuppressWarnings("unused") public void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } /** * 判断某个字符串是否存在于数组中 * @param stringArray 原数组 * @param source 查找的字符串 * @return 是否找到 */ public static boolean contains(List stringArray, String source) { // 利用list的包含方法,进行判断 if (stringArray.contains(source)) { return true; } else { stringArray.add(source); return false; } } }
转载表明出处谢谢
