org.apache.poi.hssf.usermodel.HSSFCell Java Examples

/**
 * 表头条件
 * @param sheet
 * @param t
 * @param cellCount
 * @return
 */
void writeCondtions(HSSFSheet sheet){
	T t = getConditions();
	if (t!=null) {
		HSSFRow row = sheet.createRow(getRowNumber());
		row.setHeight((short) 500);
		CellRangeAddress cra = new CellRangeAddress(getRowNumber(), getRowNumber(), 0, getColumnJson().size());
		sheet.addMergedRegion(cra);
		HSSFCell cell = row.createCell(0);
		HSSFCellStyle style = cell.getCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true);
		cell.setCellStyle(style);
		setCellValue(cell, formatCondition(t));
		addRowNumber();
	}
}
 

/**
   * 初始化表头
   * @param sheet
   * @param columnJson
   * @param rowNumber
   */
  private static void writeSheetHead(HSSFSheet sheet,JSONObject columnJson,int rowNumber){
if (logger.isDebugEnabled()) {
	logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - start"); //$NON-NLS-1$
}

Set<String> keySet = columnJson.keySet();
int cellNumber = 0;
HSSFRow row = sheet.createRow(rowNumber);
for (String k : keySet) {//k:GOODS_NO
	String name = columnJson.getString(k);//品项编码
	sheet.autoSizeColumn(cellNumber);
	HSSFCell cell = row.createCell(cellNumber++);
	cell.setCellValue(name);
}

if (logger.isDebugEnabled()) {
	logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - end"); //$NON-NLS-1$
}
  }
 

@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new HSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       HSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			HSSFCell cell = headerRow.createCell(col);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(new HSSFRichTextString(s));
			col++;
		}
	}		
}
 

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();
}
 

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

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

    final HSSFCell cell = row.getCell(c);

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

    try {
        return cell.getBooleanCellValue();
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw e;
    }
}
 

public static CellLocation findCell(final HSSFSheet sheet, final String str, final int colNum) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }
        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (!Check.isEmpty(cellValue.getString())) {
            if (cellValue.getString().equals(str)) {
                return new CellLocation(rowNum, (short) colNum);
            }
        }
    }

    return null;
}
 

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 void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldStartRowNum, final int oldEndRowNum, final int newStartRowNum) {
    final HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

    int newRowNum = newStartRowNum;

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

    final HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

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

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(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;
}
 

private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
	cell.setCellType(CellType.STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return false;
	}
	value = cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(CellType.NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	value = new Long(new Double(d).longValue()).toString();
    }
    if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) {
	return true;
    }
}
return false;
   }
 

protected void setColumnData(final Map<String, String> keywordsValueMap, final ColumnTemplate columnTemplate, final HSSFRow row, final NormalColumn normalColumn, final TableView tableView, final int order) {

        for (final int columnNum : columnTemplate.columnTemplateMap.keySet()) {
            final HSSFCell cell = row.createCell(columnNum);
            final String template = columnTemplate.columnTemplateMap.get(columnNum);

            String value = null;
            if (KEYWORD_ORDER.equals(template)) {
                value = String.valueOf(order);

            } else {
                value = getColumnValue(keywordsValueMap, normalColumn, tableView, template);
            }

            try {
                final double num = Double.parseDouble(value);
                cell.setCellValue(num);

            } catch (final NumberFormatException e) {
                final HSSFRichTextString text = new HSSFRichTextString(value);
                cell.setCellValue(text);
            }
        }
    }
 

public static Integer findMatchColumn(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) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

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

    return null;
}
 

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;
}
 

private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}
 

@Override
protected void writeDirectTestData(ERTable table,
		Map<NormalColumn, String> data, String database) {
	HSSFRow row = this.sheet.createRow(this.rowNum++);

	int col = 0;

	for (NormalColumn column : table.getExpandedColumns()) {
		HSSFCell cell = row.createCell(col++);

		String value = Format.null2blank(data.get(column));

		if (value == null || "null".equals(value.toLowerCase())) {

		} else {
			cell.setCellValue(new HSSFRichTextString(value));
		}
	}
}
 

@SuppressWarnings("deprecation")
private static String getCellData(Cell cell) {
    String value = null;
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            value = dataFormatter.formatCellValue(cell);
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            value = null;
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "#ERROR#";
            break;
    }
    return value;
}
 

private int getColumnPosition(String columnName, HSSFRow row){
	int cells = row.getPhysicalNumberOfCells();
	logger.debug("\nROW " + row.getRowNum() + " has " + cells
			+ " cell(s).");
	for (int c = 0; c < cells; c++) {
		//get single cell
		HSSFCell cell = row.getCell(c);
		
		String valueField = null;
		try {
			valueField = parseCell(cell);
			
			if (valueField.equals(columnName)){
				return c;
			}
			
		} catch(Throwable t) {
			throw new RuntimeException("Impossible to parse cell [" + c + "]", t);
		}

	}
	return -1;
	
}
 

@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 

@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 void testBug() throws ResourceException, IOException, ReportProcessingException {
  final MasterReport report = DebugReportRunner.parseGoldenSampleReport( "Prd-3889.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 );

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

  // assert that there are no extra columns ..
  final HSSFRow row8 = sheetAt.getRow( 7 );
  assertNull( row8 );

}
 

private static void insert(int rowNum, HSSFSheet sheet, HSSFRow row, HSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

@Before
public void testXLS1() throws IOException {
       try (HSSFWorkbook workbook = new HSSFWorkbook()) {
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

public void postProcessXLS(Object document) {  
    HSSFWorkbook wb = (HSSFWorkbook) document;  
    HSSFSheet sheet = wb.getSheetAt(0);  
    HSSFRow header = sheet.getRow(0);  
    HSSFCellStyle cellStyle = wb.createCellStyle();    
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);  
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
      
    for(int i=0; i < header.getPhysicalNumberOfCells();i++) {  
        HSSFCell cell = header.getCell(i);  
          
        cell.setCellStyle(cellStyle);  
    }  
    Row row=sheet.createRow((short)sheet.getLastRowNum()+3);
    Cell cellDisclaimer = row.createCell(0);
    HSSFFont customFont= wb.createFont();
    customFont.setFontHeightInPoints((short)10);
    customFont.setFontName("Arial");
    customFont.setColor(IndexedColors.BLACK.getIndex());
    customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    customFont.setItalic(true);
    
    cellDisclaimer.setCellValue("Disclaimer");
    HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
    cellStyleDisclaimer.setFont(customFont);
    cellDisclaimer.setCellStyle(cellStyleDisclaimer);
    
    Row row1=sheet.createRow(sheet.getLastRowNum()+2);
    Cell cellDisclaimerContent1 = row1.createCell(0);
    cellDisclaimerContent1.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");	    
    Row row2=sheet.createRow(sheet.getLastRowNum()+1);
    Cell cellDisclaimerContent2 = row2.createCell(0);
    cellDisclaimerContent2.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");
    
}
 

public void fillCellValue(Cell cell, Object value, SimpleDateFormat sdf) {
	if (value != null) {
		if (value instanceof Date) {
			String result = sdf.format(value);
			if (result.endsWith("00:00:00")) {
				result = result.substring(0, 11);
			}
			cell.setCellValue(result);
		} else if (value instanceof Double) {
			cell.setCellValue((Double) value);
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} else if (value instanceof Byte) {
			cell.setCellValue((Byte) value);
		} else if (value instanceof Short) {
			cell.setCellValue((Short) value);
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof Long) {
			cell.setCellValue((Long) value);
		} else if (value instanceof Float) {
			cell.setCellValue((Float) value);
		} else {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value.toString());
		}
	} else {
		cell.setCellValue("");
	}
}
 

/**
 * 写入表头
 * @param sheet
 */
void writeHead(HSSFSheet sheet){
	LinkedHashMap<String, Object> columnJson = getColumnJson();
	Set<String> keySet = columnJson.keySet();
	int cellNumber = 0;
	HSSFRow row = sheet.createRow(addRowNumber());
	for (String k : keySet) {
		Object name = columnJson.get(k);//品项编码
		sheet.autoSizeColumn(cellNumber);
		HSSFCell cell = row.createCell(cellNumber++);
		setCellValue(cell, name);
		pubMaxValue(k,name);
	}
}
 

protected void createMergeRegion(JRExporterGridCell gridCell, int colIndex, int rowIndex, HSSFCellStyle cellStyle)
{
	boolean isCollapseRowSpan = getCurrentItemConfiguration().isCollapseRowSpan();
	int rowSpan = isCollapseRowSpan ? 1 : gridCell.getRowSpan();
	if (gridCell.getColSpan() > 1 || rowSpan > 1)
	{
		sheet.addMergedRegion(new CellRangeAddress(rowIndex, (rowIndex + rowSpan - 1), 
				colIndex, (colIndex + gridCell.getColSpan() - 1)));

		for(int i = 0; i < rowSpan; i++)
		{
			HSSFRow spanRow = sheet.getRow(rowIndex + i);
			if (spanRow == null)
			{
				spanRow = sheet.createRow(rowIndex + i);
			}
			for(int j = 0; j < gridCell.getColSpan(); j++)
			{
				HSSFCell spanCell = spanRow.getCell((colIndex + j));
				if (spanCell == null)
				{
					spanCell = spanRow.createCell((colIndex + j));
				}
				spanCell.setCellStyle(cellStyle);
			}
		}
	}
}
 

@SuppressWarnings("resource")
private void assertXlsOutputCorrect(byte[] output) throws Exception {
	HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(output));
	HSSFSheet sheet = workbook.getSheetAt(0);
	assertNotNull("Sheet should not be null", sheet);
	HSSFRow row = sheet.getRow(3);
	HSSFCell cell = row.getCell((short) 1);
	assertNotNull("Cell should not be null", cell);
	assertEquals("Cell content should be Dear Lord!", "Dear Lord!", cell.getRichStringCellValue().getString());
}
 

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

	HSSFRichTextString cellValue = cell.getRichStringCellValue();

	return cellValue.toString();
}
 

/**
 * Convenient method to obtain the cell in the given sheet, row and column.
 * <p>Creates the row and the cell if they still doesn't already exist.
 * Thus, the column can be passed as an int, the method making the needed downcasts.
 * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
 * @param row the row number
 * @param col the column number
 * @return the HSSFCell
 */
protected HSSFCell getCell(HSSFSheet sheet, int row, int col) {
	HSSFRow sheetRow = sheet.getRow(row);
	if (sheetRow == null) {
		sheetRow = sheet.createRow(row);
	}
	HSSFCell cell = sheetRow.getCell(col);
	if (cell == null) {
		cell = sheetRow.createCell(col);
	}
	return cell;
}