org.apache.poi.ss.usermodel.Row Java Examples

@Test
public void testVerificationCol() throws Exception {
	String different = "DIFFERENT";
	getDescribedPackage(doc3).getPackageVerificationCode().setValue(different);
	comparer.compare(new SpdxDocument[] {doc1, doc2, doc3});
	pkgSheet.importCompareResults(comparer, docNames);
	Row row = findRow(PackageSheet.VERIFICATION_FIELD_TEXT);
	assertEquals(PackageSheet.DIFFERENT_STRING, getEqualCellValue(row));
	assertEquals(getDescribedPackage(doc1).getPackageVerificationCode().getValue(), getDocCellValue(0, row));
	assertEquals(getDescribedPackage(doc2).getPackageVerificationCode().getValue(), getDocCellValue(1, row));
	assertEquals(different, getDocCellValue(2, row));
	comparer.compare(new SpdxDocument[] {doc1, doc2});
	pkgSheet.importCompareResults(comparer, new String[] {docNames[0], docNames[1]});
	row = findRow(PackageSheet.VERIFICATION_FIELD_TEXT);
	assertEquals(PackageSheet.EQUAL_STRING, getEqualCellValue(row));
}
 

@Override
public List<Attribute> getTimedValueAttributes(String datasourceId) throws Exception {

    Sheet metadataSheet = getWorkbook().getSheet("Metadata");

    List<Attribute> attributes = new ArrayList<>();
    Row row;
    int rowId = 12;
    while((row = metadataSheet.getRow(rowId)) != null && row.getCell(0) != null){
        rowId++;

        String label = row.getCell(1).getStringCellValue();
        String description = row.getCell(2).getStringCellValue();
        String parameterValue = row.getCell(3).getStringCellValue();

        if (parameterValue.startsWith("Reference"))
            continue;

        attributes.add(new Attribute(getProvider(), label, description));
    }

    return attributes;
}
 

@Test
public void testRelationshipsCol() throws InvalidSPDXAnalysisException, SpdxCompareException {
	SpdxElement relatedElement = getDescribedPackage(doc3).getFiles()[0];
	Relationship[] different = new Relationship[] {
			new Relationship(relatedElement, RelationshipType.CONTAINED_BY, "Comment")};
	getDescribedPackage(doc3).setRelationships(different);
	comparer.compare(new SpdxDocument[] {doc1, doc2, doc3});
	pkgSheet.importCompareResults(comparer, docNames);
	Row row = findRow(PackageSheet.RELATIONSHIPS_FIELD_TEXT);
	assertEquals(PackageSheet.DIFFERENT_STRING, getEqualCellValue(row));
	String expected = CompareHelper.relationshipToString(getDescribedPackage(doc1).getRelationships()[0]);
	assertEquals(expected, getDocCellValue(0, row));
	expected = CompareHelper.relationshipToString(getDescribedPackage(doc2).getRelationships()[0]);
	assertEquals(expected, getDocCellValue(1, row));
	assertEquals(CompareHelper.relationshipToString(different[0]), getDocCellValue(2, row));
	comparer.compare(new SpdxDocument[] {doc1, doc2});
	pkgSheet.importCompareResults(comparer, new String[] {docNames[0], docNames[1]});
	row = findRow(PackageSheet.RELATIONSHIPS_FIELD_TEXT);
	assertEquals(PackageSheet.EQUAL_STRING, getEqualCellValue(row));
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void main(String[] args) throws IOException {
  for(int i = 0; i < 10; i++) {
    long start = System.currentTimeMillis();
    InputStream is = new FileInputStream(new File("/Users/thundermoose/Downloads/SampleXLSFile_6800kb.xlsx"));
    try(Workbook workbook = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .open(is)) {

      for(Row r : workbook.getSheet("test")) {
        for(Cell c : r) {
          //do nothing
        }
      }
    }

    long end = System.currentTimeMillis();
    System.out.println("Time: " + (end - start) + "ms");
  }
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    columnLabels = new HashMap();
    rowLabels = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst) {
            processAsLabels(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

private boolean moreDataToRead(final Sheet sheet, final int firstColumn, final int firstRow, final int lastRow, final int rowNum) {

        final int height = lastRow - firstRow + 1;
        if (height > 1 && firstRow + rowNum > lastRow) {
            return false;
        }

        // check if the cell is empty
        final Row row = sheet.getRow(firstRow + rowNum);
        if (row == null) {
            return false;
        }

        final Cell cell = row.getCell(firstColumn);
        if (cell == null) {
            return false;
        }
        final String str = cell.toString();
        return !(str == null || "".equals(str));
    }
 

/**
 * @param reference
 * @return the cell the reference points to
 */
private Cell getCellFromReference( String reference ) {

  CellReference cellRef = new CellReference( reference );
  String sheetName = cellRef.getSheetName();

  Sheet sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( data.realSheetname ) : data.sheet;
  if ( !Utils.isEmpty( sheetName ) ) {
    sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( sheetName ) : data.wb.getSheet( sheetName );
  }
  if ( sheet == null ) {
    return null;
  }
  // reference is assumed to be absolute
  Row xlsRow = sheet.getRow( cellRef.getRow() );
  if ( xlsRow == null ) {
    return null;
  }
  Cell styleCell = xlsRow.getCell( cellRef.getCol() );
  return styleCell;
}
 

@Test
public void testSummaryCol() throws Exception {
	String different = "DIFFERENT";
	getDescribedPackage(doc3).setSummary(different);
	comparer.compare(new SpdxDocument[] {doc1, doc2, doc3});
	pkgSheet.importCompareResults(comparer, docNames);
	Row row = findRow(PackageSheet.SUMMARY_FIELD_TEXT);
	assertEquals(PackageSheet.DIFFERENT_STRING, getEqualCellValue(row));
	assertEquals(getDescribedPackage(doc1).getSummary(), getDocCellValue(0, row));
	assertEquals(getDescribedPackage(doc2).getSummary(), getDocCellValue(1, row));
	assertEquals(different, getDocCellValue(2, row));
	comparer.compare(new SpdxDocument[] {doc1, doc2});
	pkgSheet.importCompareResults(comparer, new String[] {docNames[0], docNames[1]});
	row = findRow(PackageSheet.SUMMARY_FIELD_TEXT);
	assertEquals(PackageSheet.EQUAL_STRING, getEqualCellValue(row));
}
 

private TableRange findRowArea(Row row) {
  int col1 = -1;
  int col2 = -1;
  for (Cell cell : row) {
    Boolean blank = isBlank(cell);
    if (col1 < 0 && Boolean.FALSE.equals(blank)) {
      col1 = cell.getColumnIndex();
      col2 = col1;
    } else if (col1 >= 0 && col2 >= col1) {
      if (Boolean.FALSE.equals(blank)) {
        col2 = cell.getColumnIndex();
      } else if (Boolean.TRUE.equals(blank)) {
        break;
      }
    }
  }
  return col1 >= 0 && col2 >= col1 ? new TableRange(0, 0, col1, col2) : null;
}
 

/**
 * Builds sheet from parent workbook and 2D array with cell
 * values. Creates rows anyway (even if row contains only null
 * cells), creates cells if either corresponding array value is not
 * null or createEmptyCells property is true.
 * The conversion is performed in the following way:
 * <p>
 * <ul>
 * <li>Numbers become numeric cells.</li>
 * <li><code>java.util.Date</code> or <code>java.util.Calendar</code>
 * instances become date cells.</li>
 * <li>String with leading '=' char become formulas (leading '='
 * will be truncated).</li>
 * <li>Other objects become strings via <code>Object.toString()</code>
 * method call.</li>
 * </ul>
 *
 * @return newly created sheet
 */
public Sheet build() {
    Sheet sheet = (sheetName == null) ? workbook.createSheet() : workbook.createSheet(sheetName);
    Row currentRow = null;
    Cell currentCell = null;

    for (int rowIndex = 0; rowIndex < cells.length; ++rowIndex) {
        Object[] rowArray = cells[rowIndex];
        currentRow = sheet.createRow(rowIndex);

        for (int cellIndex = 0; cellIndex < rowArray.length; ++cellIndex) {
            Object cellValue = rowArray[cellIndex];
            if (cellValue != null || shouldCreateEmptyCells) {
                currentCell = currentRow.createCell(cellIndex);
                setCellValue(currentCell, cellValue);
            }
        }
    }
    return sheet;
}
 

/**
 * Sets the indexes based on the header.
 *
 * @param row
 */
protected void prepare(Row row) {

    for (ImportColumn c : super.getColumns()) {
        ImportColumnExcel column = (ImportColumnExcel) c;
        if (!column.isIndexSpecified()) {
            boolean found = false;
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                if (row.getCell(i).getStringCellValue().equals(column.getName())) {
                    found = true;
                    column.setIndex(i);
                }
            }
            if (!found) {
                throw new IllegalArgumentException("Index for column '" + column.getName() + "' couldn't be found");
            }
        }
    }
}
 

private void prepareSplittingMap(HSSFSheet sheetConfig) throws IOException, TransformerException {
    mSplittingMap = new TreeMap<Integer, String>();
    mOutputFileNames = new HashMap<String, String>();
    Iterator<Row> it = sheetConfig.rowIterator();
    while (it.hasNext()) {
        Row row = it.next();
        if (row == null || row.getCell(0) == null || row.getCell(1) == null) {
            return;
        }
        String splitName = row.getCell(1).getStringCellValue();
        mSplittingMap.put((int)row.getCell(0).getNumericCellValue(), splitName);
        if (row.getCell(2) != null) {
            mOutputFileNames.put(splitName, row.getCell(2).getStringCellValue());
        }
    }
}
 

@Test
  public void testStringFormattedFormulaCell() throws Exception {
    try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
      Sheet sheet = workbook.getSheetAt(0);
      Iterator<Row> rowIterator = sheet.rowIterator();

      Cell B1 = getCellFromNextRow(rowIterator, 1);
      nextRow(rowIterator);
      Cell B3 = getCellFromNextRow(rowIterator, 1);

      expectType(B3, FORMULA);
//      expectCachedType(B3, STRING); // this can't return FUNCTION as cached type as per javadoc ! fix in future work
      expectFormula(B3, "B1");
      expectSameStringContent(B1, B3);
      expectStringContent(B3, "a");
    }
  }
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}

	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Sheet sheet = wb.createSheet(sheetName);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
	Row dataRow = sheet.createRow(1);
	Cell ssVersionCell = dataRow.createCell(SPREADSHEET_VERSION_COL);
	ssVersionCell.setCellValue(CURRENT_VERSION);
}
 

private void validateExcelSheet( final ByteArrayOutputStream boutSlow, final TableModel data ) throws IOException,
  InvalidFormatException {
  Workbook workbook = WorkbookFactory.create( new ByteArrayInputStream( boutSlow.toByteArray() ) );
  Sheet sheet = workbook.getSheetAt( 0 );
  Assert.assertEquals( 0, sheet.getFirstRowNum() );
  Assert.assertEquals( data.getRowCount() - 1, sheet.getLastRowNum() );

  for ( int r = 0; r < data.getRowCount(); r += 1 ) {
    Row row = sheet.getRow( r );
    for ( int c = 0; c < data.getColumnCount(); c += 1 ) {
      Cell cell = row.getCell( c );

      Object valueAt = data.getValueAt( r, c );
      if ( valueAt == null ) {
        if ( cell != null ) {
          // excel cells never return null
          Assert.assertEquals( "", cell.getStringCellValue() );
        }
      } else {
        Assert.assertEquals( valueAt, cell.getStringCellValue() );
      }
    }

  }

}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    rolesPerColumn = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst && FIRST_ROW_CONTAINS_ROLES) {
            processRowAsRoles(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}
 

public static void afterRowCreate(WriteContext writeContext, Row row, Integer relativeRowIndex, Boolean isHead) {
    List<WriteHandler> handlerList = writeContext.currentWriteHolder().writeHandlerMap().get(RowWriteHandler.class);
    if (handlerList == null || handlerList.isEmpty()) {
        return;
    }
    for (WriteHandler writeHandler : handlerList) {
        if (writeHandler instanceof RowWriteHandler) {
            ((RowWriteHandler) writeHandler).afterRowCreate(writeContext.writeSheetHolder(),
                writeContext.writeTableHolder(), row, relativeRowIndex, isHead);
        }
    }

}
 

@Override
public SpreadsheetExporter addHeader(String... values) {
    CellStyle cellStyle = createHeaderStyle();
    Row headerRow = dataSheet.createRow(rowCount++);
    for (int i = 0; i < values.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(new HSSFRichTextString(values[i]));
    }
    return this;
}
 

private List<Cell> getAllCells(Row row) {
	List<Cell> cells = new ArrayList<>();
	Iterator<Cell> it = row.cellIterator();
	while (it.hasNext()) {
		Cell cell = it.next();
		cells.add(cell);
	}
	return cells;
}
 

@Override
public E get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK : return null;
            case Cell.CELL_TYPE_STRING : return Enum.valueOf(enumClass, cell.getStringCellValue());
            case Cell.CELL_TYPE_NUMERIC : return values[(int)cell.getNumericCellValue()];
            default:
                throw new UnsupportedOperationException("Cannot convert cell to enum");
        }
    } else {
        return null;
    }
}
 

@Test
public void testIdCol() throws InvalidSPDXAnalysisException, SpdxCompareException {
	comparer.compare(new SpdxDocument[] {doc1, doc2, doc3});
	pkgSheet.importCompareResults(comparer, docNames);
	Row row = findRow(PackageSheet.ID_FIELD_TEXT);
	assertEquals(PackageSheet.EQUAL_STRING, getEqualCellValue(row));
	assertEquals(getDescribedPackage(doc1).getId(), getDocCellValue(0, row));
	assertEquals(getDescribedPackage(doc2).getId(), getDocCellValue(1, row));
	assertEquals(getDescribedPackage(doc3).getId(), getDocCellValue(2, row));
}
 

@Override
public Long get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return (long)cell.getNumericCellValue();
    } else {
        return null;
    }
}
 

/**
 * 添加单元格
 */
public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
{
    Cell cell = null;
    try
    {
        // 设置行高
        row.setHeight((short) (attr.height() * 20));
        // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
        if (attr.isExport())
        {
            // 创建cell
            cell = row.createCell(column);
            cell.setCellStyle(styles.get("data"));

            // 用于读取对象中的属性
            Object value = getTargetValue(vo, field, attr);
            String dateFormat = attr.dateFormat();
            String readConverterExp = attr.readConverterExp();
            if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
            {
                cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
            }
            else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
            {
                cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
            }
            else
            {
                // 设置列类型
                setCellVo(value, attr, cell);
            }
        }
    }
    catch (Exception e)
    {
        log.error("导出Excel失败{}", e);
    }
    return cell;
}
 

private void serializeColumns(Workbook workbook, JsonGenerator generator, Sheet sheet, List<ColumnMetadata> columns)
        throws IOException {

    for (int i = 0, size = sheet.getLastRowNum(); i <= size; i++) {
        if (limit > 0 && i > limit) {
            break;
        }
        // is header line?
        Row row = sheet.getRow(i);
        if (isHeaderLine(i, columns) || row == null) {
            continue;
        }

        generator.writeStartObject();
        for (ColumnMetadata columnMetadata : columns) {

            // do not write the values if this has been detected as an header
            if (i < columnMetadata.getHeaderSize()) {
                continue;
            }

            int colId = Integer.parseInt(columnMetadata.getId());
            String cellValue =
                    getCellValueAsString(row.getCell(colId), workbook.getCreationHelper().createFormulaEvaluator());
            LOG.trace("cellValue for {}/{}: {}", i, colId, cellValue);
            generator.writeFieldName(columnMetadata.getId());
            if (cellValue != null) {
                generator.writeString(cellValue);
            } else {
                generator.writeNull();
            }
        }
        generator.writeEndObject();
    }
}
 

private void addColumn( Sheet sheet, String[] colData, boolean bInsert, int rowNo, int column ){
 	/*
	 * Run around the loop
	 */
	for ( int r=0; r < colData.length; r++ ){
		int rowCurrent	= rowNo + r;
		
		// Create the necessary row
  	Row row = sheet.getRow( rowCurrent );
  	if ( row == null ){
  		SheetUtility.insertRow( sheet, rowCurrent );
  		row = sheet.getRow( rowCurrent );
  	}

  	// We will have to shift the cells up one
  	if ( bInsert && column < row.getLastCellNum() ){
			SheetUtility.shiftCellRight( row, column );
		}
  	
  	Cell cell = row.createCell( column );

  	// Set the data; trying to see if its a number
		try{
			cell.setCellValue( Double.valueOf(colData[r]) );
			cell.setCellType( Cell.CELL_TYPE_NUMERIC );
		}catch(Exception e){
			cell.setCellValue( colData[r] );
			cell.setCellType( Cell.CELL_TYPE_STRING );
		}
	}
}
 

@Override
public String verify() {
	try {
		if (sheet == null) {
			return "Worksheet for SPDX Snippets does not exist";
		}
		Row firstRow = sheet.getRow(firstRowNum);
		for (int i = 0; i < NUM_COLS- 1; i++) { 	// Don't check the last (user defined) column
			Cell cell = firstRow.getCell(i+firstCellNum);
			if (cell == null || 
					cell.getStringCellValue() == null ||
					!cell.getStringCellValue().equals(HEADER_TITLES[i])) {
				return "Column "+HEADER_TITLES[i]+" missing for SPDX Snippet worksheet";
			}
		}
		// validate rows
		boolean done = false;
		int rowNum = getFirstDataRow();
		while (!done) {
			Row row = sheet.getRow(rowNum);
			if (row == null || row.getCell(firstCellNum) == null) {
				done = true;
			} else {
				String error = validateRow(row);
				if (error != null) {
					return error;
				}
				rowNum++;
			}
		}
		return null;
	} catch (Exception ex) {
		return "Error in verifying SPDX Snippet work sheet: "+ex.getMessage();
	}
}
 

/**
 * @param column
 * @param line
 * @param value
 * @param style
 */
private void writeValue(String column, int line, String value, CellStyle style) {
    log.debug("Writing: [{}] at line [{}] in column [{}]", value, line, column);
    final int colIndex = columns.indexOf(column);
    final Sheet sheet = workbook.getSheetAt(0);
    final Row row = sheet.getRow(line);
    Cell cell = row.getCell(colIndex);
    if (cell != null) {
        row.removeCell(cell);
    }
    cell = row.createCell(colIndex);
    cell.setCellStyle(style);
    cell.setCellValue(value);
    saveOpenExcelFile();
}
 

/**
 * 
 * @param spreadsheetData
 * @return
 */
protected Workbook getAsWorkbookTest(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	
	short rowPos = 0;
	for( List<Object> rowData: spreadsheetData ) {
		short cellPos = 0;
		if (rowPos == 0) {
			// By convention, the first list in the list contains column headers.
			Row headerRow = sheet.createRow(rowPos++);
			for( Object header: rowData ) {
				createCell(headerRow, cellPos++, null).setCellValue(header.toString());
			}
		}
		else {
			Row row = sheet.createRow(rowPos++);
			for ( Object data : rowData ) {
				Cell cell = createCell(row, cellPos++, null);
				if (data != null) {
					if (data instanceof Double) {
						cell.setCellValue(((Double)data).doubleValue());
					} 
					else {
						cell.setCellValue(data.toString());
					}
				}
			}
		}
	}

	return wb;
}