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

/**
 * 创建List之后的各个Cells
 * 
 * @param styles
 */
private static void createListCells(Drawing patriarch, int index,
		int cellNum, Object obj, List<ExcelExportEntity> excelParams,
		Sheet sheet, Workbook workbook) throws Exception {
	ExcelExportEntity entity;
	Row row;
	if (sheet.getRow(index) == null) {
		row = sheet.createRow(index);
		row.setHeight((short) 350);
	} else {
		row = sheet.getRow(index);
	}
	for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
		entity = excelParams.get(k);
		Object value = getCellValue(entity, obj);
		if (entity.getType() != 2) {
			createStringCell(row, cellNum++,
					value == null ? "" : value.toString(), entity, workbook);
		} else {
			createImageCell(patriarch, entity, row, cellNum++,
					value == null ? "" : value.toString(), obj, workbook);
		}
	}
}
 

/**
 * @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 = data.sheet;
  if ( !Utils.isEmpty( sheetName ) ) {
    sheet = 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;
}
 

/**
 * @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;
}
 

/**
 * 按报表模板格式写标题
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-17
 * @version     v1.0
 *
 * @param i_DataWorkbook  数据工作薄
 * @param i_DataSheet     数据工作表
 * @param io_RTotal       将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue 系统变量信息
 * @param i_Datas         数据
 * @param i_RTemplate     报表模板对象
 */
public final static void writeTitle(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue ,Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet    = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCount = i_RTemplate.getRowCountTitle();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

    copyMergedRegionsTitle(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesTitle(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
    {
        int v_TemplateRowNo = i_RTemplate.getTitleBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);
        
        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
            
        if ( v_TemplateRow != null ) // 模板空白行（无任何数据）时，可能返回NULL
        {
            copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
        }
    }
}
 

/**
 * 获取合并单元格的值
 * @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 ;
}
 

@Test
public void test355() throws IOException, InvalidFormatException {
    File file = TestFileUtil.readFile("dataformat" + File.separator + "dataformat.xlsx");
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 

protected Cell getCell(Sheet sheet, int row, int col) {
	Row sheetRow = sheet.getRow(row);
	if (sheetRow == null) {
		sheetRow = sheet.createRow(row);
	}
	Cell cell = sheetRow.getCell(col);
	if (cell == null) {
		cell = sheetRow.createCell(col);
	}
	return cell;
}
 

public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

@Test
public void saveMapsTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
	Map<String, Object> record = new LinkedHashMap<>();
	record.put("primIntProp", 1);
	record.put("intObjProp", 100);
	record.put("strProp", "some string");
	record.put("dateProp", "2000-01-01 00:00:00");


	Collection<Map<String, Object>> records = Arrays.asList(record);
	File outputFile = createFile("saveMapsTest_UsingGeneratedHeader_File");
	// save it
	Ssio.saveMaps(Arrays.asList("primIntProp", "intObjProp", "strProp", "dateProp"), records, outputFile);


	// then parse it
	byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);
	Row dataRow = sheet.getRow(1);

	List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
	List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
	Map<String, Object> keyValueMap = new LinkedHashMap<>();
	for (int i = 0; i < headerCells.size(); i++) {
		keyValueMap.put(headerCells.get(i), dataCells.get(i));
	}

	Assert.assertEquals(4, keyValueMap.size());
	Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
	Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
	Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
	Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop"));


}
 

@Override
public List<String> mapTitleRow(Sheet sheet) {
	try {
		Row titleRow = sheet.getRow(getTitleRowIndex());
		return ExcelUtils.getRowValues(titleRow);
	} catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
	}
}
 

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn( Sheet sheet, int columnToDelete ){
	int maxColumn = 0;
	for ( int r=0; r < sheet.getLastRowNum()+1; r++ ){
		Row	row	= sheet.getRow( r );
		
		// if no row exists here; then nothing to do; next!
		if ( row == null )
			continue;
		
		int lastColumn = row.getLastCellNum();
		if ( lastColumn > maxColumn )
			maxColumn = lastColumn;
		
		// if the row doesn't have this many columns then we are good; next!
		if ( lastColumn < columnToDelete )
			continue;
		
		for ( int x=columnToDelete+1; x < lastColumn + 1; x++ ){
			Cell oldCell	= row.getCell(x-1);
			if ( oldCell != null )
				row.removeCell( oldCell );
			
			Cell nextCell	= row.getCell( x );
			if ( nextCell != null ){
				Cell newCell	= row.createCell( x-1, nextCell.getCellType() );
				cloneCell(newCell, nextCell);
			}
		}
	}

	
	// Adjust the column widths
	for ( int c=0; c < maxColumn; c++ ){
		sheet.setColumnWidth( c, sheet.getColumnWidth(c+1) );
	}
}
 

@Test
public void givenFormualCell_whenGetCellStringValueForFormula_thenReturnOriginalFormulatring() throws IOException {
    Workbook workbook = new XSSFWorkbook(fileLocation);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);

    ExcelCellFormatter formatter = new ExcelCellFormatter();
    assertEquals("3", formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook));
    workbook.close();
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER, OaImporter.OaType.lsoa.name(), OaImporter.OaType.lsoa.datasourceSpec.getDescription());
    // Loop over years
    for (int sheetId = 0; sheetId < getWorkbook().getNumberOfSheets(); sheetId++){
        Sheet sheet = getWorkbook().getSheetAt(sheetId);

        int year;
        try {
            year = Integer.parseInt(sheet.getSheetName().substring(sheet.getSheetName().length()-4, sheet.getSheetName().length()));
        }catch (NumberFormatException e){
            // Sheetname does not end in a year
            continue;
        }

        // Create extractors for each timed value
        List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();

        RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
        ConstantExtractor timestampExtractor = new ConstantExtractor(String.valueOf(year));

        // Get the attribute label row and create TimedValueExtractors
        Row attributeLabelRow = sheet.getRow(5);
        for (int columnId = 0; columnId < attributeLabelRow.getLastCellNum(); columnId++){
            RowCellExtractor tmpAttributeLabelExtractor = new RowCellExtractor(columnId, CellType.STRING);
            tmpAttributeLabelExtractor.setRow(attributeLabelRow);
            Attribute attribute = AttributeUtils.getByProviderAndLabel(getProvider(), tmpAttributeLabelExtractor.extract());
            if (attribute != null){
                ConstantExtractor attributeExtractor = new ConstantExtractor(attribute.getLabel());
                RowCellExtractor valueExtractor = new RowCellExtractor(columnId, CellType.NUMERIC);
                timedValueExtractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectExtractor, attributeExtractor, timestampExtractor, valueExtractor));
            }
        }

        // Extract timed values
        excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
    }

    getWorkbook().close();
}
 

@Override
public List<String> mapTitleRow(Sheet sheet){
	try {
		Row titleRow = sheet.getRow(0);
		return ExcelUtils.getRowValues(titleRow);
	} catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
	}
}
 

@Test
public void testMultiRowEmptinessXlsx() throws BirtException, IOException {

	debug = false;
	InputStream inputStream = runAndRenderReport("Issue29.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );

		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 6, this.firstNullRow(sheet));

		for( int i = 0; i < 4; ++i ) {
			for( Cell cell : sheet.getRow(i) ) {
				assertEquals( 0, cell.getCellStyle().getBorderTop() );
				assertEquals( 0, cell.getCellStyle().getBorderLeft() );
				assertEquals( 0, cell.getCellStyle().getBorderRight() );
				assertEquals( 0, cell.getCellStyle().getBorderBottom() );
			}
		}
		assertEquals( "Bibble", sheet.getRow(5).getCell(0).getStringCellValue() );
		assertEquals( 24.0, sheet.getRow(0).getHeightInPoints(), 0.1 );
	
	} finally {
		inputStream.close();
	}
}
 

/**
 * 获取表头元数据
 *
 * @param filePath
 * @return 属性名称列表、字段类型、描述说明
 */
public static Args.Three<List<String>, List<String>, List<String>> getMetaData(String filePath, String sheetName) throws Exception {
    Workbook workBook = getWorkBook(filePath);
    if (workBook == null) {
        return null;
    }
    Sheet sheet = workBook.getSheet(sheetName);
    if (sheet == null) {
        return null;
    }

    List<String> fieldList = new ArrayList<>();
    List<String> typeList = new ArrayList<>();
    List<String> descList = new ArrayList<>();

    //前三行为元数据
    for (int i = 0; i < 3; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        int lastCellNum = row.getPhysicalNumberOfCells();
        for (int j = 0; j < lastCellNum; j++) {
            String value = row.getCell(j).toString();
            switch (i) {
                case 0:
                    fieldList.add(value);
                    break;
                case 1:
                    typeList.add(value);
                    break;
                default:
                    descList.add(value);
                    break;
            }
        }
    }
    workBook.close();
    return Args.of(fieldList, typeList, descList);
}
 

public T onRead(Sheet sheet, int sheetIndex) {
	T dataModelInst = createDataModel();
	int rowCount = sheet.getPhysicalNumberOfRows();
	for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
		for(RowStreamReader<T> reader : rowReaders) {
			Row row = sheet.getRow(rowIndex);
			if (reader.match(rowIndex)) {
				reader.onRead(dataModelInst, sheet, sheetIndex, row, rowIndex);
			}
		}
	}
	return dataModelInst;
}
 

@Test
public void poi0702() throws Exception {
    Workbook workbook = WorkbookFactory.create(new FileInputStream("D:\\test\\t2.xlsx"));
    workbook = WorkbookFactory.create(new File("D:\\test\\t2.xlsx"));
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    System.out.println(row.getCell(0).getNumericCellValue());
}
 

private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
    throws IOException
{
    if (wb.getNumberOfSheets() > 1)
    {
        logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() + 
                " sheets, ignoring  all except the first one"); 
    }
    
    int firstRow = 0;
    Sheet s = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    
    String[][] data = new String[s.getLastRowNum()+1][];
                                 
    // If there is a heading freezepane row, skip it
    PaneInformation pane = s.getPaneInformation();
    if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
    {
        firstRow = pane.getHorizontalSplitTopRow();
        logger.debug("Skipping excel freeze header of " + firstRow + " rows");
    }
    
    // Process each row in turn, getting columns up to our limit
    for (int row=firstRow; row <= s.getLastRowNum(); row++)
    {
        Row r = s.getRow(row);
        if (r != null)
        {
            String[] d = new String[COLUMNS.length];
            for (int cn=0; cn<COLUMNS.length; cn++)
            {
                Cell cell = r.getCell(cn);
                if (cell != null && cell.getCellType() != CellType.BLANK)
                {
                    d[cn] = df.formatCellValue(cell);
                }
            }
            data[row] = d;
        }
    }
    
    // Handle the contents
    processSpreadsheetUpload(data, users);
}
 

@Test
public void saveTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
	HeaderUtilsTestRecord record = new HeaderUtilsTestRecord();
	record.setPrimIntProp(1);
	record.setIntObjProp(100);
	record.setStrProp("some string");
	record.setDateProp("2000-01-01 00:00:00");		 
	record.setWriteOnlyProp("would not be saved");

	

	Collection<HeaderUtilsTestRecord> records = Arrays.asList(record);
	File outputFile = createFile("saveTest_UsingGeneratedHeader_File");
	// save it
	Ssio.save(HeaderUtilsTestRecord.class, records, outputFile);
	

	// then parse it
	byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);
	Row dataRow = sheet.getRow(1);
	
	List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
	List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
	Map<String, Object> keyValueMap = new LinkedHashMap<>();
	for (int i = 0; i < headerCells.size(); i++) {
		keyValueMap.put(headerCells.get(i), dataCells.get(i));
	}
	
	Assert.assertEquals(6, keyValueMap.size());
	Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
	Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
	Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
	Assert.assertTrue(keyValueMap.containsKey("Date Prop"));
	Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop Str"));
	Assert.assertNull(keyValueMap.get("Read Only Prop"));
	  		
}