public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
/**
* ハイパーリンクの設定
*
* @param sheet シート
* @param nRow 対象行番号
* @param nColumn 対象列番号
* @param value ハイパーリンクテキスト
* @param url ハイパーリンク先URL
*/
public static void setHyperLink(
XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
assert sheet != null;
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
hyperlink.setAddress(url);
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellValue(value);
cell.setHyperlink(hyperlink);
// ハイパーリンクテキストの装飾
XSSFFont font = workbook.createFont();
XSSFCellStyle style = workbook.createCellStyle();
// font.setColor(new XSSFColor(new Color(0, 0, 255)));
font.setUnderline(XSSFFont.U_SINGLE);
style.setFont(font);
cell.setCellStyle(style);
}
public static Hyperlink LinkDataToMap(HashMap<String, String> linkData) {
Hyperlink linkInfo = new HSSFHyperlink(org.apache.poi.common.usermodel.Hyperlink.LINK_URL);
linkInfo.setAddress(linkData.get("URL"));
linkInfo.setLabel(linkData.get("Label"));
return linkInfo;
}
