Java Code Examples for org.apache.poi.hssf.usermodel.HSSFRow#getCell()

public static List<HSSFCellStyle> copyCellStyle(HSSFWorkbook workbook,
		HSSFRow row) {
	List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

		HSSFCell cell = row.getCell(colNum);
		if (cell != null) {
			HSSFCellStyle style = cell.getCellStyle();
			HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
			cellStyleList.add(newCellStyle);
		} else {
			cellStyleList.add(null);
		}
	}

	return cellStyleList;
}
 

public static String getCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

    if (row == null) {
        return null;
    }

    final HSSFCell cell = row.getCell(c);

    if (cell == null) {
        return null;
    }

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}
 

protected Map<String, String> buildKeywordsValueMap(final HSSFSheet wordsSheet, final int columnNo, final String[] keywords) {
    final Map<String, String> keywordsValueMap = new HashMap<String, String>();

    for (final String keyword : keywords) {
        final CellLocation location = POIUtils.findCell(wordsSheet, keyword, columnNo);
        if (location != null) {
            final HSSFRow row = wordsSheet.getRow(location.r);

            final HSSFCell cell = row.getCell(location.c + 2);
            final String value = cell.getRichStringCellValue().getString();

            if (value != null) {
                keywordsValueMap.put(keyword, value);
            }
        }
    }

    return keywordsValueMap;
}
 

public static Integer findColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            final HSSFRichTextString cellValue = cell.getRichStringCellValue();

            if (str.equals(cellValue.getString())) {
                return Integer.valueOf(colNum);
            }
        }
    }

    return null;
}
 

public static Integer findMatchColumn(HSSFRow row, String str) {
	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}

		if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
			continue;
		}

		HSSFRichTextString cellValue = cell.getRichStringCellValue();

		if (cellValue.getString().matches(str)) {
			return Integer.valueOf(colNum);
		}
	}

	return null;
}
 

public static void copyRow(HSSFSheet oldSheet, HSSFSheet newSheet,
		int oldStartRowNum, int oldEndRowNum, int newStartRowNum) {
	HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

	int newRowNum = newStartRowNum;

	for (int oldRowNum = oldStartRowNum; oldRowNum <= oldEndRowNum; oldRowNum++) {
		POIUtils.copyRow(oldSheet, newSheet, oldRowNum, newRowNum++);
	}

	HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

	if (oldAboveRow != null) {
		for (int colNum = newTopRow.getFirstCellNum(); colNum <= newTopRow
				.getLastCellNum(); colNum++) {
			HSSFCell oldAboveCell = oldAboveRow.getCell(colNum);
			if (oldAboveCell != null) {
				HSSFCell newTopCell = newTopRow.getCell(colNum);
				newTopCell.getCellStyle().setBorderTop(
						oldAboveCell.getCellStyle().getBorderBottom());
			}
		}
	}
}
 

@Test
public void testFastExport() throws ResourceException, ReportProcessingException, IOException {
  // This establishes a baseline for the second test using the slow export.

  final MasterReport report = DebugReportRunner.parseLocalReport( "Prd-5391.prpt", Prd5391IT.class );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  FastExcelReportUtil.processXls( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  Assert.assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  Assert.assertEquals( "FFFF:8080:8080", fillForegroundColorColor.getHexString() );

  HSSFFont font = cellStyle.getFont( wb );
  Assert.assertEquals( "Times New Roman", font.getFontName() );
}
 

public static List<HSSFCellStyle> copyCellStyle(final HSSFWorkbook workbook, final HSSFRow row) {
    final List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

        final HSSFCell cell = row.getCell(colNum);
        if (cell != null) {
            final HSSFCellStyle style = cell.getCellStyle();
            final HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
            cellStyleList.add(newCellStyle);
        } else {
            cellStyleList.add(null);
        }
    }

    return cellStyleList;
}
 

protected Map<String, String> buildKeywordsValueMap(HSSFSheet wordsSheet,
		int columnNo, String[] keywords) {
	Map<String, String> keywordsValueMap = new HashMap<String, String>();

	for (String keyword : keywords) {
		CellLocation location = POIUtils.findCell(wordsSheet, keyword,
				columnNo);
		if (location != null) {
			HSSFRow row = wordsSheet.getRow(location.r);

			HSSFCell cell = row.getCell(location.c + 2);
			String value = cell.getRichStringCellValue().getString();

			if (value != null) {
				keywordsValueMap.put(keyword, value);
			}
		}
	}

	return keywordsValueMap;
}
 

public void testExcelExport() throws Exception {
  final MasterReport report = DebugReportRunner.parseGoldenSampleReport( "Prd-3625.prpt" );

  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );
  assertEquals( Cell.CELL_TYPE_NUMERIC, cell0.getCellType() );
  assertEquals( "yyyy-MM-dd", cell0.getCellStyle().getDataFormatString() );
  final HSSFCell cell1 = row.getCell( 1 );
  assertEquals( Cell.CELL_TYPE_NUMERIC, cell1.getCellType() );
  assertEquals( "#,###.00;(#,###.00)", cell1.getCellStyle().getDataFormatString() );
}
 

public static boolean getBooleanCellValue(HSSFSheet sheet, int r, int c) {
	HSSFRow row = sheet.getRow(r);
	
	if (row == null) {
		return false;
	}
	
	HSSFCell cell = row.getCell(c);
	
	if (cell == null) {
		return false;
	}
	
	return cell.getBooleanCellValue();
}
 

public static HSSFCell getCellFromReference(HSSFSheet templateSheet, int colIndex, int rowIndex) {
    HSSFRow row = templateSheet.getRow(rowIndex);
    row = row == null ? templateSheet.createRow(rowIndex) : row;
    HSSFCell cell = row.getCell(colIndex);
    cell = cell == null ? row.createCell(colIndex) : cell;
    return cell;
}
 

public static void reader(String filePath) {
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell((short) 0);
		int type = cell.getCellType();
		String msg = getCellStringValue(cell);
		System.out.println(type + ":" + msg);
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

public static short getCellColor(HSSFSheet sheet, int r, int c) {
	HSSFRow row = sheet.getRow(r);
	if (row == null) {
		return -1;
	}
	HSSFCell cell = row.getCell(c);

	return cell.getCellStyle().getFillForegroundColor();
}
 

public static boolean isBlankRow(HSSFRow row, int index, int rowCount){
	if(row == null)
		return true;
	for(int i=index; i < rowCount; i++){
		if(row.getCell(i) != null && 
				!"".equals(row.getCell(i).getStringCellValue().trim())){
			return false;
		}
	}
	return true;
}
 

private void compareFiles(String etalonFile, String resultFile) throws IOException {
    HSSFWorkbook result = new HSSFWorkbook(FileUtils.openInputStream(new File(etalonFile)));
    HSSFWorkbook etalon = new HSSFWorkbook(FileUtils.openInputStream(new File(resultFile)));

    HSSFSheet resultSheet = result.getSheetAt(0);
    HSSFSheet etalonSheet = etalon.getSheetAt(0);

    for (int row = 0; row < 10; row++) {
        HSSFRow resultRow = resultSheet.getRow(row);
        HSSFRow etalonRow = etalonSheet.getRow(row);
        if (resultRow == null && etalonRow == null) {
            continue;
        } else if ((resultRow == null) || (etalonRow == null)) {
            Assert.fail("fail on row [" + row + "]");
        }

        for (int cell = 0; cell < 10; cell++) {
            HSSFCell resultCell = resultRow.getCell(cell);
            HSSFCell etalonCell = etalonRow.getCell(cell);

            if (resultCell != null && etalonCell != null) {
                Assert.assertEquals(String.format("fail on cell [%d,%d]", row, cell), etalonCell.getNumericCellValue(), resultCell.getNumericCellValue());
            } else if ((resultCell == null && etalonCell != null) || (resultCell != null)) {
                Assert.fail(String.format("fail on cell [%d,%d]", row, cell));
            }
        }
    }
}
 

public static String getCellValue(final HSSFSheet sheet, final CellLocation location) {
    final HSSFRow row = sheet.getRow(location.r);
    final HSSFCell cell = row.getCell(location.c);

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}
 

static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow, HSSFWorkbook workbook) {
    logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);

    List<Column> columnList = new ArrayList<Column>();
    List<String> primaryKeyList = new ArrayList<String>();
    for (int i = 0;; i++) {
        HSSFCell cell = sampleRow.getCell(i);
        if (cell == null) {
            break;
        }

        String columnName = cell.getRichStringCellValue().getString();
        if (columnName != null) {
            columnName = columnName.trim();
        }

        // Bugfix for issue ID 2818981 - if a cell has a formatting but no
        // name also ignore it
        if (columnName.length() <= 0) {
            logger
                    .debug(
                            "The column name of column # {} is empty - will skip here assuming the last column was reached",
                            String.valueOf(i));
            break;
        }

        Column column = new Column(columnName, DataType.UNKNOWN);
        columnList.add(column);
        
        // Unique identification key
        byte underline = cell.getCellStyle().getFont(workbook).getUnderline();
        if (underline == 1) {
            primaryKeyList.add(columnName);
        } 

    }
    Column[] columns = columnList.toArray(new Column[0]);
    
    if(!primaryKeyList.isEmpty()){
    	return new DefaultTableMetaData(tableName, columns,primaryKeyList.toArray(new String[primaryKeyList.size()]));
    }else{
    	return new DefaultTableMetaData(tableName, columns);
    }
    
}
 

private MatrixCellStyle createMatrixCellStyle(HSSFWorkbook workbook,
		HSSFSheet sheet, CellLocation matrixCellLocation) {

	int matrixRowNum = matrixCellLocation.r;
	int matrixColumnNum = matrixCellLocation.c;

	HSSFRow matrixHeaderTemplateRow = sheet.getRow(matrixRowNum);
	HSSFCell matrixHeaderTemplateCell = matrixHeaderTemplateRow
			.getCell(matrixColumnNum);

	MatrixCellStyle matrixCellStyle = new MatrixCellStyle();

	matrixCellStyle.headerTemplateCellStyle = matrixHeaderTemplateCell
			.getCellStyle();

	matrixCellStyle.style11 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, true, true,
			false);

	matrixCellStyle.style12 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, true, true,
			true);

	matrixCellStyle.style13 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, false, false, true,
			true);

	matrixCellStyle.style21 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, true,
			false);

	matrixCellStyle.style22 = this
			.createMatrixCellStyle(workbook,
					matrixCellStyle.headerTemplateCellStyle, true, true,
					true, true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style22);

	matrixCellStyle.style23 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, false, true,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style23);

	matrixCellStyle.style31 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, false,
			false);

	matrixCellStyle.style32 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, true, false,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style32);

	matrixCellStyle.style33 = this.createMatrixCellStyle(workbook,
			matrixCellStyle.headerTemplateCellStyle, true, false, false,
			true);
	this.setIndexMatrixColor(workbook, matrixCellStyle.style33);

	return matrixCellStyle;
}
 

@Override
   @SuppressWarnings("unchecked")
   public int importOutcomes(MultipartFile fileItem) throws IOException {
int counter = 0;
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
try (HSSFWorkbook wb = new HSSFWorkbook(fs)) {
    HSSFSheet sheet = wb.getSheetAt(0);
    int startRow = sheet.getFirstRowNum();
    int endRow = sheet.getLastRowNum();
    User user = null;

    // make import work with files with header ("exported on") or without (pure data)
    HSSFRow row = sheet.getRow(startRow);
    HSSFCell cell = row.getCell(0);
    String header = cell.getStringCellValue();
    startRow += "name".equalsIgnoreCase(header) ? 1 : 5;

    for (int i = startRow; i < (endRow + 1); i++) {
	row = sheet.getRow(i);
	cell = row.getCell(1);
	String code = cell.getStringCellValue();
	List<Outcome> foundOutcomes = outcomeDAO.findByProperty(Outcome.class, "code", code);
	if (!foundOutcomes.isEmpty()) {
	    if (log.isDebugEnabled()) {
		log.debug("Skipping an outcome with existing code: " + code);
	    }
	    continue;
	}
	cell = row.getCell(3);
	String scaleCode = cell.getStringCellValue();
	List<OutcomeScale> foundScales = outcomeDAO.findByProperty(OutcomeScale.class, "code", scaleCode);
	OutcomeScale scale = foundScales.isEmpty() ? null : foundScales.get(0);
	if (scale == null) {
	    if (log.isDebugEnabled()) {
		log.debug("Skipping an outcome with missing scale with code: " + scaleCode);
	    }
	    continue;
	}
	cell = row.getCell(0);
	String name = cell.getStringCellValue();
	cell = row.getCell(2);
	String description = cell == null ? null : cell.getStringCellValue();

	Outcome outcome = new Outcome();
	outcome.setName(name);
	outcome.setCode(code);
	outcome.setDescription(description);
	outcome.setScale(scale);
	if (user == null) {
	    UserDTO userDTO = OutcomeService.getUserDTO();
	    user = (User) outcomeDAO.find(User.class, userDTO.getUserID());
	}
	outcome.setCreateBy(user);
	outcome.setCreateDateTime(new Date());
	outcomeDAO.insert(outcome);

	counter++;
    }
}
return counter;
   }