in plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java [754:1041]
public void prepareNextOutputFile(Object[] row) throws HopException {
try {
// Validation
//
// sheet name shouldn't exceed 31 character
if (data.realSheetname != null && data.realSheetname.length() > 31) {
throw new HopException(
BaseMessages.getString(
PKG, "ExcelWriterTransform.Exception.MaxSheetName", data.realSheetname));
}
// Getting field names from input is not supported in a Beam context
//
if (data.isBeamContext() && meta.getFile().isFileNameInField()) {
throw new HopException(
BaseMessages.getString(
PKG, "ExcelWriterTransform.Exception.FilenameFromFieldNotSupportedInBeam"));
}
// clear style cache
int numOfFields =
meta.getOutputFields() != null && !meta.getOutputFields().isEmpty()
? meta.getOutputFields().size()
: 0;
if (numOfFields == 0) {
numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0;
}
int splitNr = 0;
if (!meta.getFile().isFileNameInField()) {
splitNr = getNextSplitNr(meta.getFile().getFileName());
}
FileObject file = getFileLocation(row);
if (!file.getParent().exists() && meta.getFile().isCreateParentFolder()) {
logDebug(
"Create parent directory for "
+ file.getName().toString()
+ " because it does not exist.");
createParentFolder(file);
}
if (isDebug()) {
logDebug(
BaseMessages.getString(
PKG, "ExcelWriterTransform.Log.OpeningFile", file.getName().toString()));
}
// determine whether existing file must be deleted
if (file.exists() && data.createNewFile && !file.delete()) {
if (isBasic()) {
logBasic(
BaseMessages.getString(
PKG,
"ExcelWriterTransform.Log.CouldNotDeleteStaleFile",
file.getName().toString()));
}
setErrors(1);
throw new HopException("Could not delete stale file " + file.getName().toString());
}
// adding filename to result
if (meta.isAddToResultFilenames()) {
// Add this to the result file names...
ResultFile resultFile =
new ResultFile(
ResultFile.FILE_TYPE_GENERAL,
file,
getPipelineMeta().getName(),
getTransformName());
resultFile.setComment(
"This file was created with an Excel writer transform by Hop : The Hop Orchestration Platform");
addResultFile(resultFile);
}
boolean appendingToSheet = true;
// if now no file exists we must create it as indicated by user
if (!file.exists()) {
// if template file is enabled
if (meta.getTemplate().isTemplateEnabled()) {
// handle template case (must have same format)
// ensure extensions match
String templateExt =
HopVfs.getFileObject(data.realTemplateFileName, variables).getName().getExtension();
if (!meta.getFile().getExtension().equalsIgnoreCase(templateExt)) {
throw new HopException(
"Template Format Mismatch: Template has extension: "
+ templateExt
+ ", but output file has extension: "
+ meta.getFile().getExtension()
+ ". Template and output file must share the same format!");
}
if (HopVfs.getFileObject(data.realTemplateFileName, variables).exists()) {
// if the template exists just copy the template in place
copyFile(HopVfs.getFileObject(data.realTemplateFileName, variables), file);
} else {
// template is missing, log it and get out
if (isBasic()) {
logBasic(
BaseMessages.getString(
PKG, "ExcelWriterTransform.Log.TemplateMissing", data.realTemplateFileName));
}
setErrors(1);
throw new HopException("Template file missing: " + data.realTemplateFileName);
}
} else {
// handle fresh file case, just create a fresh workbook
try (Workbook wb =
meta.getFile().getExtension().equalsIgnoreCase("xlsx")
? new XSSFWorkbook()
: new HSSFWorkbook()) {
wb.createSheet(data.realSheetname);
try (OutputStream out = HopVfs.getOutputStream(file, false)) {
wb.write(out);
}
}
}
appendingToSheet = false;
}
// Start creating the workbook
Workbook wb;
Sheet sheet;
// file is guaranteed to be in place now
if (meta.getFile().getExtension().equalsIgnoreCase("xlsx")) {
try (InputStream inputStream = HopVfs.getInputStream(HopVfs.getFilename(file), variables)) {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
if (meta.getFile().isStreamingData() && !meta.getTemplate().isTemplateEnabled()) {
wb = new SXSSFWorkbook(xssfWorkbook, 100);
} else {
// Initialize it later after writing header/template because SXSSFWorkbook can't
// read/rewrite existing data,
// only append.
wb = xssfWorkbook;
}
}
} else {
try (InputStream inputStream = HopVfs.getInputStream(file)) {
wb = new HSSFWorkbook(inputStream);
}
}
int existingActiveSheetIndex = wb.getActiveSheetIndex();
int replacingSheetAt = -1;
if (wb.getSheet(data.realSheetname) != null && data.createNewSheet) {
// sheet exists, replace or reuse as indicated by user
replacingSheetAt = wb.getSheetIndex(wb.getSheet(data.realSheetname));
wb.removeSheetAt(replacingSheetAt);
}
// if sheet is now missing, we need to create a new one
if (wb.getSheet(data.realSheetname) == null) {
if (meta.getTemplate().isTemplateSheetEnabled()) {
Sheet ts = wb.getSheet(data.realTemplateSheetName);
// if template sheet is missing, break
if (ts == null) {
throw new HopException(
BaseMessages.getString(
PKG,
"ExcelWriterTransform.Exception.TemplateNotFound",
data.realTemplateSheetName));
}
sheet = wb.cloneSheet(wb.getSheetIndex(ts));
wb.setSheetName(wb.getSheetIndex(sheet), data.realSheetname);
// unhide sheet in case it was hidden
wb.setSheetHidden(wb.getSheetIndex(sheet), false);
if (meta.getTemplate().isTemplateSheetHidden()) {
wb.setSheetHidden(wb.getSheetIndex(ts), true);
}
} else {
// no template to use, simply create a new sheet
sheet = wb.createSheet(data.realSheetname);
}
if (replacingSheetAt > -1) {
wb.setSheetOrder(sheet.getSheetName(), replacingSheetAt);
}
// preserves active sheet selection in workbook
wb.setActiveSheet(existingActiveSheetIndex);
wb.setSelectedTab(existingActiveSheetIndex);
appendingToSheet = false;
} else {
// sheet is there and should be reused
sheet = wb.getSheet(data.realSheetname);
}
// if use chose to make the current sheet active, do so
if (meta.isMakeSheetActive()) {
int sheetIndex = wb.getSheetIndex(sheet);
wb.setActiveSheet(sheetIndex);
wb.setSelectedTab(sheetIndex);
}
// handle write protection
if (meta.getFile().isProtectsheet()) {
protectSheet(sheet, data.realPassword);
}
// starting cell support
if (!Utils.isEmpty(data.realStartingCell)) {
CellReference cellRef = new CellReference(data.realStartingCell);
data.startingRow = cellRef.getRow();
data.startingCol = cellRef.getCol();
} else {
data.startingRow = 0;
data.startingCol = 0;
}
// Calculate the starting positions in the sheet.
//
int posX;
int posY;
posX = data.startingCol;
posY = data.startingRow;
// Find last row and append accordingly
if (!data.createNewSheet && meta.isAppendLines() && appendingToSheet) {
if (sheet.getPhysicalNumberOfRows() > 0) {
posY = sheet.getLastRowNum() + 1;
} else {
posY = 0;
}
}
// offset by configured value
// Find last row and append accordingly
if (!data.createNewSheet && meta.getAppendOffset() != 0 && appendingToSheet) {
posY += meta.getAppendOffset();
}
// may have to write a few empty lines
if (!data.createNewSheet && meta.getAppendEmpty() > 0 && appendingToSheet) {
for (int i = 0; i < meta.getAppendEmpty(); i++) {
sheet = openLine(sheet, posY);
if (!data.shiftExistingCells || meta.isAppendLines()) {
posY++;
}
}
}
// save file for later usage
String baseFileName;
if (!meta.getFile().isFileNameInField()) {
baseFileName = meta.getFile().getFileName();
} else {
baseFileName = file.getName().toString();
}
// If starting cell provided, use the posY derived above, otherwise use default behaviour
int startY =
!Utils.isEmpty(data.realStartingCell) ? posY : Math.max(posY, sheet.getLastRowNum());
ExcelWriterWorkbookDefinition workbookDefinition =
prepareWorkbookDefinition(
numOfFields, splitNr, file, wb, sheet, posX, baseFileName, startY);
// may have to write a header here
if (meta.isHeaderEnabled()
&& !(!data.createNewSheet && meta.isAppendOmitHeader() && appendingToSheet)) {
data.currentWorkbookDefinition.setSheet(writeHeader(workbookDefinition, sheet, posX, posY));
}
// Reload Worksheet in Streaming mode when a template is used
if (meta.getFile().getExtension().equalsIgnoreCase("xlsx")
&& meta.getFile().isStreamingData()
&& meta.getTemplate().isTemplateEnabled()) {
try (InputStream inputStream = HopVfs.getInputStream(HopVfs.getFilename(file), variables)) {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
wb = new SXSSFWorkbook(xssfWorkbook, 100);
sheet = wb.getSheet(data.realSheetname);
// Replace workbookDefinition with reloaded one
data.usedFiles.remove(workbookDefinition);
prepareWorkbookDefinition(
numOfFields, splitNr, file, wb, sheet, posX, baseFileName, startY);
}
}
if (isDebug()) {
logDebug(
BaseMessages.getString(
PKG, "ExcelWriterTransform.Log.FileOpened", file.getName().toString()));
}
} catch (Exception e) {
logError("Error opening new file", e);
setErrors(1);
throw new HopException("Error opening new file", e);
}
}