in plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java [498:687]
void writeField(
ExcelWriterWorkbookDefinition workbookDefinition,
Object v,
IValueMeta vMeta,
ExcelWriterOutputField excelField,
Row xlsRow,
int posX,
Object[] row,
int fieldNr,
boolean isTitle)
throws HopException {
try {
boolean cellExisted = true;
// get the cell
Cell cell = xlsRow.getCell(posX);
if (cell == null) {
cellExisted = false;
cell = xlsRow.createCell(posX);
}
// if cell existed and existing cell's styles should not be changed, don't
if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
// if the style of this field is cached, reuse it
if (!isTitle && workbookDefinition.getCachedStyle(fieldNr) != null) {
cell.setCellStyle(workbookDefinition.getCachedStyle(fieldNr));
} else {
// apply style if requested
if (excelField != null) {
// determine correct cell for title or data rows
String styleRef = null;
if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) {
styleRef = excelField.getStyleCell();
} else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) {
styleRef = excelField.getTitleStyleCell();
}
if (styleRef != null) {
Cell styleCell = getCellFromReference(workbookDefinition, styleRef);
if (styleCell != null && cell != styleCell) {
cell.setCellStyle(styleCell.getCellStyle());
}
}
}
// set cell format as specified, specific format overrides cell specification
if (!isTitle
&& excelField != null
&& !Utils.isEmpty(excelField.getFormat())
&& !excelField.getFormat().startsWith("Image")) {
setDataFormat(workbookDefinition, excelField.getFormat(), cell);
}
if (!isTitle && excelField != null && Utils.isEmpty(excelField.getFormat())) {
if (vMeta.getType() == IValueMeta.TYPE_DATE
|| vMeta.getType() == IValueMeta.TYPE_TIMESTAMP) {
String format = vMeta.getFormatMask();
if (!Utils.isEmpty(format)) {
setDataFormat(workbookDefinition, format, cell);
}
}
}
// cache it for later runs
if (!isTitle) {
workbookDefinition.cacheStyle(fieldNr, cell.getCellStyle());
}
}
}
// create link on cell if requested
if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) {
String link =
data.inputRowMeta
.getValueMeta(data.linkfieldnrs[fieldNr])
.getString(row[data.linkfieldnrs[fieldNr]]);
if (!Utils.isEmpty(link)) {
CreationHelper ch = workbookDefinition.getWorkbook().getCreationHelper();
// set the link on the cell depending on link type
Hyperlink hyperLink = null;
if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) {
hyperLink = ch.createHyperlink(HyperlinkType.URL);
hyperLink.setLabel("URL Link");
} else if (link.startsWith("mailto:")) {
hyperLink = ch.createHyperlink(HyperlinkType.EMAIL);
hyperLink.setLabel("Email Link");
} else if (link.startsWith("'")) {
hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT);
hyperLink.setLabel("Link within this document");
} else {
hyperLink = ch.createHyperlink(HyperlinkType.FILE);
hyperLink.setLabel("Link to a file");
}
hyperLink.setAddress(link);
cell.setHyperlink(hyperLink);
// if cell existed and existing cell's styles should not be changed, don't
if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {
if (workbookDefinition.getCachedLinkStyle(fieldNr) != null) {
cell.setCellStyle(workbookDefinition.getCachedLinkStyle(fieldNr));
} else {
Font origFont =
workbookDefinition.getWorkbook().getFontAt(cell.getCellStyle().getFontIndex());
Font hlinkFont = workbookDefinition.getWorkbook().createFont();
// reproduce original font characteristics
hlinkFont.setBold(origFont.getBold());
hlinkFont.setCharSet(origFont.getCharSet());
hlinkFont.setFontHeight(origFont.getFontHeight());
hlinkFont.setFontName(origFont.getFontName());
hlinkFont.setItalic(origFont.getItalic());
hlinkFont.setStrikeout(origFont.getStrikeout());
hlinkFont.setTypeOffset(origFont.getTypeOffset());
// make it blue and underlined
hlinkFont.setUnderline(Font.U_SINGLE);
hlinkFont.setColor(IndexedColors.BLUE.getIndex());
CellStyle style = cell.getCellStyle();
style.setFont(hlinkFont);
cell.setCellStyle(style);
workbookDefinition.cacheLinkStyle(fieldNr, cell.getCellStyle());
}
}
}
}
// create comment on cell if requested
if (!isTitle
&& excelField != null
&& data.commentfieldnrs[fieldNr] >= 0
&& workbookDefinition.getWorkbook() instanceof XSSFWorkbook) {
String comment =
data.inputRowMeta
.getValueMeta(data.commentfieldnrs[fieldNr])
.getString(row[data.commentfieldnrs[fieldNr]]);
if (!Utils.isEmpty(comment)) {
String author =
data.commentauthorfieldnrs[fieldNr] >= 0
? data.inputRowMeta
.getValueMeta(data.commentauthorfieldnrs[fieldNr])
.getString(row[data.commentauthorfieldnrs[fieldNr]])
: "Apache Hop";
cell.setCellComment(createCellComment(workbookDefinition, author, comment));
}
}
// cell is getting a formula value or static content
if (!isTitle && excelField != null && excelField.isFormula()) {
// formula case
cell.setCellFormula(vMeta.getString(v));
} else {
// static content case
switch (vMeta.getType()) {
case IValueMeta.TYPE_DATE:
if (v != null && vMeta.getDate(v) != null) {
cell.setCellValue(vMeta.getDate(v));
}
break;
case IValueMeta.TYPE_BOOLEAN:
if (v != null) {
cell.setCellValue(vMeta.getBoolean(v));
}
break;
case IValueMeta.TYPE_BIGNUMBER, IValueMeta.TYPE_NUMBER, IValueMeta.TYPE_INTEGER:
if (v != null) {
cell.setCellValue(vMeta.getNumber(v));
}
break;
default:
// fallthrough: output the data value as a string
if (v != null) {
cell.setCellValue(vMeta.getString(v));
}
break;
}
}
} catch (Exception e) {
logError(
"Error writing field ("
+ workbookDefinition.getPosX()
+ ","
+ workbookDefinition.getPosY()
+ ") : "
+ e.toString());
logError(Const.getStackTracker(e));
throw new HopException(e);
}
}