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

The following examples show how to use org.apache.poi.ss.usermodel.Row. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: TestPackageSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
@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));
}
 
Example #2
Source File: AccessibilityImporter.java    From TomboloDigitalConnector with MIT License 6 votes vote down vote up
@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;
}
 
Example #3
Source File: TestPackageSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
@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));
}
 
Example #4
Source File: PerFileSheetV1d1.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example #5
Source File: PerformanceTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
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");
  }
}
 
Example #6
Source File: ExcelAdjacencyMatrixExtractor.java    From wandora with GNU General Public License v3.0 6 votes vote down vote up
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);
        }
    }
}
 
Example #7
Source File: Excel.java    From objectlabkit with Apache License 2.0 6 votes vote down vote up
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));
    }
 
Example #8
Source File: ExcelWriterStep.java    From pentaho-kettle with Apache License 2.0 6 votes vote down vote up
/**
 * @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;
}
 
Example #9
Source File: TestPackageSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
@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));
}
 
Example #10
Source File: XlsxReader.java    From tablesaw with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #11
Source File: SheetBuilder.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example #12
Source File: ImportConfigurationExcel.java    From arx with Apache License 2.0 6 votes vote down vote up
/**
 * 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");
            }
        }
    }
}
 
Example #13
Source File: ToolImportSplitter.java    From android-lang-tool with Apache License 2.0 6 votes vote down vote up
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());
        }
    }
}
 
Example #14
Source File: StreamingWorkbookTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
@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");
    }
  }
 
Example #15
Source File: OriginsSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
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);
}
 
Example #16
Source File: Prd5180IT.java    From pentaho-reporting with GNU Lesser General Public License v2.1 6 votes vote down vote up
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() );
      }
    }

  }

}
 
Example #17
Source File: SnippetSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example #18
Source File: ExcelAdjacencyListExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up
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);
        }
    }
}
 
Example #19
Source File: WriteHandlerUtils.java    From easyexcel with Apache License 2.0 5 votes vote down vote up
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);
        }
    }

}
 
Example #20
Source File: SpreadsheetExporter.java    From sakai with Educational Community License v2.0 5 votes vote down vote up
@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;
}
 
Example #21
Source File: SsioIntegrationTest.java    From sep4j with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #22
Source File: PoiEnumGetter.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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;
    }
}
 
Example #23
Source File: TestPackageSheet.java    From tools with Apache License 2.0 5 votes vote down vote up
@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));
}
 
Example #24
Source File: PoiLongGetter.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@Override
public Long get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return (long)cell.getNumericCellValue();
    } else {
        return null;
    }
}
 
Example #25
Source File: ExcelUtil.java    From RuoYi-Vue with MIT License 5 votes vote down vote up
/**
 * 添加单元格
 */
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;
}
 
Example #26
Source File: XlsRunnable.java    From data-prep with Apache License 2.0 5 votes vote down vote up
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();
    }
}
 
Example #27
Source File: SpreadsheetAddColumn.java    From openbd-core with GNU General Public License v3.0 5 votes vote down vote up
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 );
		}
	}
}
 
Example #28
Source File: SnippetSheet.java    From tools with Apache License 2.0 5 votes vote down vote up
@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();
	}
}
 
Example #29
Source File: ExcelDataProvider.java    From NoraUi with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * @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();
}
 
Example #30
Source File: QuestionPoolBean.java    From sakai with Educational Community License v2.0 5 votes vote down vote up
/**
 * 
 * @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;
}