Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#getNumberOfSheets()

@SuppressWarnings("resource")
private List<Map<String,Object>> parseExcel(InputStream stream) throws Exception {
	List<Map<String,Object>> mapList=new ArrayList<Map<String,Object>>();
	XSSFWorkbook wb = new XSSFWorkbook(stream);
	for (int i = 0; i < wb.getNumberOfSheets(); i++) {
		XSSFSheet sheet = wb.getSheetAt(i);
		if (sheet == null) {
			continue;
		}
		String name = sheet.getSheetName();
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name",name);
		map.put("data", buildVariables(sheet));
		mapList.add(map);
	}
	return mapList;
}
 

@Override
public boolean initialize(MetaFile input, String separator) {

  if (input == null) {
    return false;
  }

  File inFile = MetaFiles.getPath(input).toFile();
  if (!inFile.exists()) {
    return false;
  }

  try {
    FileInputStream inSteam = new FileInputStream(inFile);
    book = new XSSFWorkbook(inSteam);
    if (book.getNumberOfSheets() == 0) {
      return false;
    }
    formatter = new DataFormatter();
  } catch (IOException e) {
    e.printStackTrace();
    return false;
  }

  return true;
}
 

public static boolean compareTwoFile(XSSFWorkbook workbook1, XSSFWorkbook workbook2) {
    int nbSheet1 = workbook1.getNumberOfSheets();
    int nbSheet2 = workbook2.getNumberOfSheets();
    if (nbSheet1 != nbSheet2) {
        return false;
    }
    boolean equalFile = true;
    for (int i = 0; i <= nbSheet1 - 1; i++) {
        XSSFSheet sheet1 = workbook1.getSheetAt(i);
        XSSFSheet sheet2 = workbook2.getSheetAt(i);
        if (!compareTwoSheets(sheet1, sheet2)) {
            equalFile = false;
            break;
        }
    }

    return equalFile;
}
 

/**
 * 读取Excel 2007版，xlsx格式
 *
 * @param is
 * @return
 * @throws IOException
 */
private List<List<Object>> read2007Excel(InputStream is) throws IOException {
    List<List<Object>> list = new LinkedList<>();
    // 构造 XSSFWorkbook 对象，strPath 传入文件路径
    XSSFWorkbook xwb = new XSSFWorkbook(is);
    int sheetCount = xwb.getNumberOfSheets();
    for (int n = 0; n < sheetCount; n++) {
        XSSFSheet sheet = xwb.getSheetAt(n);
        Object value;
        XSSFRow row;
        XSSFCell cell;
        int counter = 0;
        for (int i = (startReadPos - 1); counter < sheet.getPhysicalNumberOfRows() - (startReadPos - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                value = getCellValue(cell);
                if (value == null || "".equals(value)) {
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
    }
    return list;
}
 

private String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}
 

public static Object[] readAllExcelSheet(InputStream inputStream) throws IOException {
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	int sheets = xssfWorkbook.getNumberOfSheets();
	Object[] objects = new Object[sheets];
	for(int i=0;i<sheets;i++){
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(i);
		objects[i] = readSheet(xssfSheet);
	}
	xssfWorkbook.close();
	return objects;
}
 

/**
 * 解析sheet 页数据 
 * @param fileInp
 * @param fileName
 * @return
 * @throws Exception
 */
public static Map<String,Object> analysisExcel2007(FileInputStream fileInp,String fileName) throws Exception{
	boolean sheet_first_exists = false;
	boolean sheet_second_exists = false;
	Map<String,Object> map = new HashMap<String,Object>();
	XSSFWorkbook xssf = new XSSFWorkbook(fileInp);
	for (int numSheet = 0; numSheet < xssf.getNumberOfSheets(); numSheet++) {
		if(SHEET_CREIDT_NAME.equals(xssf.getSheetName(numSheet))){
			sheet_first_exists = true;
		}
		if(SHEET_REPAY_NAME.equals(xssf.getSheetName(numSheet))){
			sheet_second_exists = true;
		}
	}
	if(!sheet_first_exists){
		throw new Exception(SHEET_CREIDT_NAME+"不存在,无法解析");
	}
	if(!sheet_second_exists){
		throw new Exception(SHEET_REPAY_NAME+"不存在,无法解析");
	}
	//1:获取债权信息Sheet页数据
	List<CreditInfoVo> creditList=creditSheetExcel2007(fileInp,fileName,xssf,xssf.getSheetName(0));
	map.put("creditList",creditList);
	//2:获取还款Sheet页数据
	List<RepayPlanVo> repayList=repaySheetExcel2007(fileInp,fileName,xssf,xssf.getSheetName(1));
	map.put("repayList",repayList);
	return map;
}
 

public Collection getSheets(XSSFWorkbook workbook) {
    ArrayList<String> sheets = new ArrayList();
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        sheets.add(workbook.getSheetName(i));
    }
    return sheets;
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) {
    for (int i = 0; i < document.getNumberOfSheets(); i++) {
        final XSSFSheet sheet = document.getSheetAt(i);
        buffy.append(document.getSheetName(i)).append(' ');

        // Header(s), if present
        extractHeaderFooter(buffy, sheet.getFirstHeader());
        extractHeaderFooter(buffy, sheet.getOddHeader());
        extractHeaderFooter(buffy, sheet.getEvenHeader());

        // Rows and cells
        for (final Object rawR : sheet) {
            final Row row = (Row) rawR;
            for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) {
                final Cell cell = ri.next();

                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    buffy.append(cell.getRichStringCellValue().getString()).append(' ');
                } else {
                    final XSSFCell xc = (XSSFCell) cell;
                    final String rawValue = xc.getRawValue();
                    if (rawValue != null) {
                        buffy.append(rawValue).append(' ');
                    }

                }

                // Output the comment in the same cell as the content
                final Comment comment = cell.getCellComment();
                if (comment != null) {
                    buffy.append(comment.getString().getString()).append(' ');
                }
            }
        }

        // Finally footer(s), if present
        extractHeaderFooter(buffy, sheet.getFirstFooter());
        extractHeaderFooter(buffy, sheet.getOddFooter());
        extractHeaderFooter(buffy, sheet.getEvenFooter());
    }
}