Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getSheetName()

@Override
public List<Sheet> buildExcelSheetsFromFile(File file) throws EmptySheetException {
  List<Sheet> sheets = newArrayList();
  try (Workbook workbook = WorkbookFactory.create(file)) {
    int numberOfSheets = workbook.getNumberOfSheets();

    for (int index = 0; index < numberOfSheets; index++) {
      Sheet sheet = workbook.getSheetAt(index);
      if (sheet.getPhysicalNumberOfRows() == 0) {
        throw new EmptySheetException("Sheet [" + sheet.getSheetName() + "] is empty");
      } else if (sheet.getPhysicalNumberOfRows() == 1) {
        throw new MolgenisDataException(
            "Header was found, but no data is present in sheet [" + sheet.getSheetName() + "]");
      } else {
        sheets.add(sheet);
      }
    }

  } catch (IOException | EncryptedDocumentException ex) {
    LOG.error(ex.getLocalizedMessage());
    throw new MolgenisDataException("Could not create excel workbook from file");
  }
  return sheets;
}
 

/**
 * lazy load by sheet since reading can be expensive
 * 
 * @param sheet
 * @return unmodifiable list of rules
 */
protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet) {
    final String sheetName = sheet.getSheetName();
    List<EvaluationConditionalFormatRule> rules = formats.get(sheetName);
    if (rules == null) {
        if (formats.containsKey(sheetName)) {
            return Collections.emptyList();
        }
        final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
        final int count = scf.getNumConditionalFormattings();
        rules = new ArrayList<EvaluationConditionalFormatRule>(count);
        formats.put(sheetName, rules);
        for (int i=0; i < count; i++) {
            ConditionalFormatting f = scf.getConditionalFormattingAt(i);
            //optimization, as this may be expensive for lots of ranges
            final CellRangeAddress[] regions = f.getFormattingRanges();
            for (int r=0; r < f.getNumberOfRules(); r++) {
                ConditionalFormattingRule rule = f.getRule(r);
                rules.add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
            }
        }
        // need them in formatting and priority order so logic works right
        Collections.sort(rules);
    }
    return Collections.unmodifiableList(rules);
}
 

/**
 * アノテーション{@link XlsArrayColumns}の属性columnNameで指定した値がヘッダーセルに存在するかチェックする。
 * @since 2.0
 * @param sheet
 * @param recordClass
 * @param headers
 * @param reader
 * @param config
 * @throws CellNotFoundException セルが見つからない場合
 */
public static void checkArrayColumns(final Sheet sheet, final Class<?> recordClass,
        final List<RecordHeader> headers, final AnnotationReader reader, final Configuration config) {
    
    List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(recordClass, reader, XlsArrayColumns.class);
    
    for(FieldAccessor property : properties) {
        final XlsArrayColumns arrayColumns = property.getAnnotationNullable(XlsArrayColumns.class);
        if(arrayColumns.optional()) {
            continue;
        }
        
        final String columnName = arrayColumns.columnName();
        boolean found = headers.stream()
            .filter(info -> Utils.matches(info.getLabel(), columnName, config))
            .findFirst()
            .isPresent();
        
        if(!found) {
            throw new CellNotFoundException(sheet.getSheetName(), columnName);
        }
        
    }
    
}
 

public Topic getSheetTopic(Cell cell, TopicMap tm) throws TopicMapException {
    Sheet sheet = cell.getSheet();
    if(sheet != null) {
        String sheetName = sheet.getSheetName();
        Topic topic=getOrCreateTopic(tm, EXCEL_SHEET_SI_PREFIX+"/"+urlEncode(sheetName), sheetName);
        topic.addType(getSheetTypeTopic(tm));
        return topic;
    }
    return null;
}
 

public String getSheetName( int sheetNr ) {
  Sheet sheet = (Sheet) getSheet( sheetNr );
  if ( sheet == null ) {
    return null;
  }
  return sheet.getSheetName();
}
 

/**
 * マッピング対象となるセルの結合サイズが、全て同じかチェックする。
 * @since 1.4
 * @param sheet
 * @param records
 * @return 結合したセルのサイズを返す。
 * @throws NestedRecordMergedSizeException
 */
public static int checkNestedMergedSizeRecords(final Sheet sheet, final List<MergedRecord> records) 
        throws NestedRecordMergedSizeException {
    
    int mergedSize = -1;
    
    for(MergedRecord record : records) {
        
        if(mergedSize < 0) {
            mergedSize = record.getMergedSize();
            continue;
        }
        
        if(mergedSize != record.getMergedSize()) {
            
            String message = MessageBuilder.create("anno.XlsNestedRecords.mergeSizeNoMatch")
                    .var("sheetName", sheet.getSheetName())
                    .var("address", record.getMergedRange().formatAsString())
                    .var("actualMergeSize", record.getMergedSize())
                    .var("expectedMergeSize", mergedSize)
                    .format();
            throw new NestedRecordMergedSizeException(sheet.getSheetName(), record.getMergedSize(), message);
        }
    }
    
    return mergedSize;
    
}
 

/**
 * アノテーション{@link XlsMapColumns}の属性previousColumnName、nextColumnNameで指定した値がヘッダーセルに存在するかチェックする。
 * @since 2.0
 * @param sheet
 * @param recordClass
 * @param headers
 * @param reader
 * @param config
 * @throws CellNotFoundException セルが見つからない場合
 */
public static void checkMapColumns(final Sheet sheet, final Class<?> recordClass,
        final List<RecordHeader> headers, final AnnotationReader reader, final Configuration config)
                throws CellNotFoundException {
    
    List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(recordClass, reader, XlsMapColumns.class);
    
    for(FieldAccessor property : properties) {
        final XlsMapColumns mapColumns = property.getAnnotationNullable(XlsMapColumns.class);
        if(mapColumns.optional()) {
            continue;
        }
        
        final String previousColumnName = mapColumns.previousColumnName();
        boolean foundPrevious = headers.stream()
            .filter(info -> Utils.matches(info.getLabel(), previousColumnName, config))
            .findFirst()
            .isPresent();
        
        if(!foundPrevious) {
            throw new CellNotFoundException(sheet.getSheetName(), previousColumnName);
        }
        
        final String nextColumnName = mapColumns.nextColumnName();
        if(!nextColumnName.isEmpty()) {
            boolean foundNext = headers.stream()
                    .filter(info -> Utils.matches(info.getLabel(), nextColumnName, config))
                    .findFirst()
                    .isPresent();
            
            if(!foundNext) {
                throw new CellNotFoundException(sheet.getSheetName(), nextColumnName);
            }
        }
        
    }
}
 

/**
 * アノテーション{@link XlsColumn}の属性columnNameで指定した値が、ヘッダーセルに存在するかチェックする。
 * @param sheet
 * @param recordClass
 * @param headers
 * @param reader
 * @param config
 * @throws CellNotFoundException セルが見つからない場合
 */
public static void checkColumns(final Sheet sheet, final Class<?> recordClass,
        final List<RecordHeader> headers, final AnnotationReader reader, final Configuration config)
                throws CellNotFoundException {
    
    List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(recordClass, reader, XlsColumn.class);
    
    for(FieldAccessor property : properties) {
        final XlsColumn column = property.getAnnotationNullable(XlsColumn.class);
        
        if(column.optional()){
            continue;
        }
        
        String columnName = column.columnName();
        boolean find = false;
        for(RecordHeader info: headers){
            if(Utils.matches(info.getLabel(), columnName, config)){
                find = true;
                break;
            }
        }
        if(!find){
            throw new CellNotFoundException(sheet.getSheetName(), columnName);
        }
    }
    
}
 

@Override
public void saveProcess(final Sheet sheet, final Object targetObj, final XlsSheetName anno, final FieldAccessor accessor,
        final Configuration config, final SavingWorkObject work) throws XlsMapperException {

    if(!Utils.isSaveCase(anno.cases())) {
        return;
    }

    final String sheetName = sheet.getSheetName();
    accessor.setValue(targetObj, sheetName);

}
 

@Override
public void loadProcess(final Sheet sheet, final Object beansObj, final XlsSheetName anno, final FieldAccessor accessor,
        final Configuration config, final LoadingWorkObject work) {

    if(!Utils.isLoadCase(anno.cases())) {
        return;
    }

    final String sheetName = sheet.getSheetName();
    accessor.setValue(beansObj, sheetName);

}
 

public void populateData() throws SQLException {
    Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
    int noOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < noOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        ColumnInfo[] headers = this.extractColumnHeaders(sheet);
        DataTable dataTable = new FixedDataTable(sheetName, headers);

        Iterator<Row> rowItr = sheet.rowIterator();
        while (rowItr.hasNext()) {
            Row row = rowItr.next();
            if (row.getRowNum() != 0) {
                DataRow dataRow = new DataRow(row.getRowNum() - 1);
                Iterator<Cell> cellItr = row.cellIterator();
                int cellIndex = 0;
                while (cellItr.hasNext()) {
                    Cell cell = cellItr.next();
                    DataCell dataCell =
                            new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
                    dataRow.addCell(dataCell.getColumnId(), dataCell);
                    cellIndex++;
                }
                dataTable.addRow(dataRow);
            }
        }
        this.getData().put(dataTable.getTableName(), dataTable);
    }
}
 

public Topic getCellTopic(Cell cell, TopicMap tm) throws TopicMapException {
    String cellIdentifier = null;
    switch(CELL_TOPIC_IS_BASED_ON) {
        case CELL_VALUE: {
            cellIdentifier = getCellValueAsString(cell);
            break;
        }
        case CELL_SHEET_AND_LOCATION: {
            Sheet sheet = cell.getSheet();
            String sheetName = sheet.getSheetName();
            cellIdentifier = sheetName+"-"+cell.getColumnIndex()+"-"+cell.getRowIndex();
            break;
        }
        case CELL_LOCATION: {
            cellIdentifier = cell.getColumnIndex()+"-"+cell.getRowIndex();
            break;
        }
        case CELL_HASH: {
            cellIdentifier = Integer.toString(cell.hashCode());
            break;
        }
    }
    if(cellIdentifier != null) {
        String si = EXCEL_CELL_SI_PREFIX +"/"+ urlEncode(cellIdentifier);
        Topic cellTopic = getOrCreateTopic(tm, si, cellIdentifier);
        cellTopic.addType(getCellTypeTopic(tm));
        return cellTopic;
    }
    return null;
}
 

public void setActiveSheet( int sheetNo ){
	Sheet sheet	= workbook.getSheetAt( sheetNo );
	if ( sheet != null ){
		workbook.setActiveSheet( sheetNo );
		workbook.setSelectedTab( sheetNo );
		activeSheetName	= sheet.getSheetName();
		activeSheetObj	= sheet;
	}
}
 

/****
 * 
 * @param workbook
 * @param extractor
 * @param startSheet include
 * @param endSheet not include
 * @return
 */
public <T> Map<String, T> readData(Workbook workbook, TableDataExtractor<T, Sheet> extractor, int startSheet, int readCount){
	Assert.notNull(workbook, "workbook can not be null");
	try {
		int sheetCount = workbook.getNumberOfSheets();
		Sheet sheet = null;
		Map<String, T> datas = new LinkedHashMap<String, T>();
		
		if(startSheet<0)
			startSheet = 0;
		if(readCount<0)
			readCount = sheetCount;
		
		int hasReadCount = 0;
		for(int i=startSheet; i<sheetCount; i++){
			if(hasReadCount<readCount){
				sheet = workbook.getSheetAt(i);
				String name = sheet.getSheetName();
				if(sheet.getPhysicalNumberOfRows()<1)
					continue;
				if(ExcelUtils.isBlank(name))
					name = "" + i;
				T extractData = extractor.extractData(sheet);
				datas.put(name, extractData);
				
				hasReadCount++;
			}
		}
		return datas;
	}catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("read excel file error.", e);
	}
}
 

public String getSheetName( int sheetNr ) {
  Sheet sheet = (Sheet) getSheet( sheetNr );
  if ( sheet == null ) {
    return null;
  }
  return sheet.getSheetName();
}
 

/**
 * create restrictions on the data cells
 * 
 * @param mainsheet sheet with data
 * @param restrictionsheet sheet with restriction values
 * @param column index of column (starting with zero)
 * @param nbofchoices number of choices (starting with zero)
 * @param nbofrows number of rows (starting with zero)
 */
public static  void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
	DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
	String columnletter =  CellReference.convertNumToColString(column);
	String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
	DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
	CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
	
	DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	dataValidation.setSuppressDropDownArrow(true);
	mainsheet.addValidationData(dataValidation);
}
 

private List<Schema.SheetContent> parseAllSheetsStream(Request request) {
    Workbook workbook = StreamingReader
            .builder() //
            .bufferSize(4096) //
            .rowCacheSize(1) //
            .open(request.getContent());
    try {
        List<Schema.SheetContent> schemas = new ArrayList<>();
        int sheetNumber = 0;
        for (Sheet sheet : workbook) {
            List<ColumnMetadata> columnsMetadata = createMetadataFromFirstNonEmptyRowAndInitSheet(sheet);
            int totalColumnsNumber = getTotalColumnsNumber((StreamingSheet) sheet);

            /*
             * Protecting the app against too large data sets => It would break mongo by submitting too large empty
             * column metadata or saturate the memory during analysis.
             *
             * @see https://jira.talendforge.org/browse/TDP-3459
             */
            if (totalColumnsNumber > maxNumberOfColumns) {
                throw new TDPException(DataSetErrorCodes.DATASET_HAS_TOO_MANY_COLUMNS,
                        ExceptionContext.build().put("number-of-columns", totalColumnsNumber).put("max-allowed",
                                maxNumberOfColumns));
            }

            String sheetName = sheet.getSheetName();
            Schema.SheetContent sheetContent = new Schema.SheetContent(
                    StringUtils.isEmpty(sheetName) ? "sheet-" + sheetNumber : sheetName, columnsMetadata);

            // if less columns found than the metadata we complete
            completeWithEmptyColumnsMetadata(columnsMetadata, totalColumnsNumber);
            schemas.add(sheetContent);
        }
        return schemas;
    } finally {
        try {
            workbook.close();
        } catch (IOException e) {
            LOGGER.error("Unable to close excel file.", e);
        }
    }
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    // Get the url and pass it to a InputStream for downloading the file
    String fileLocation = DatasourceId.ONSLifeExpectancy.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    // Initialise the array that will store the dataset values
    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    // Initialise the workbook that will be used to interact with the excel dataset
    HSSFWorkbook workbook = new HSSFWorkbook(isr);
    int sheetIndex = 0;

    // Getting the indices of the columns containing the data.
    List<Integer> columnLoop = new ArrayList<Integer>(Arrays.asList(4,8,13));

    // Looping through the excel sheets: HE - male at bith, HE - female at birth, HE - male at 65, HE - females at 65
    // We skip the first (Contents)
    for (int sheet = 1; sheet <= 4; sheet++) {
        Sheet datatypeSheet = workbook.getSheetAt(sheet);

        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();

        // Skipping unrelevant rows
        int ignore = 0;
        while (ignore++ < 4) {
            rowIterator.next();
        }

        Row rowTime = datatypeSheet.getRow(0);
        String year = rowTime.getCell(0).getStringCellValue();
        LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year.substring(year.length() - 4));
        log.info("Time is presented in the dataset as {} and we persist it as {}", year, timestamp);

        Row rowAttribute = datatypeSheet.getRow(3);


        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // The Area Codes is the first column in the excel file
            String geograghy = String.valueOf(row.getCell(0)).trim();

            // Fetch the geometry for our subjects based on the geography code.
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geograghy);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geograghy);

            // Checking if subject is null
            if (subject != null) {

                // Looping through the columns attributes
                for (Integer item : columnLoop) {
                    try {
                        // We are distinguishing our attributes using a combination of column name and sheet name
                        String attributeName = rowAttribute.getCell(item).toString() + " " + datatypeSheet.getSheetName();
                        // Here is where we are assigning the values of our .xls file to the attribute fields we created.
                        Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
                        Double record = row.getCell(item).getNumericCellValue();
                        timedValues.add(new TimedValue(subject, attribute, timestamp, record));
                    } catch (java.lang.IllegalStateException ne) {
                        continue;
                    }
                }
            }
        }
    }
    // Finally we save the values in the database
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}