This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/master by this push:
new 98172aae18 Updated Excel Transform to allow streaming where a template
is used
new 89ba7e37ff Merge pull request #2896 from
dameikle/excel_streaming_template
98172aae18 is described below
commit 98172aae188405e18532be926ccba18791f45b70
Author: David Meikle <[email protected]>
AuthorDate: Wed May 3 15:24:09 2023 +0100
Updated Excel Transform to allow streaming where a template is used
---
.../excelwriter/ExcelWriterTransform.java | 33 ++++++++++++---
.../excelwriter/ExcelWriterTransformTest.java | 47 ++++++++++++++++++++-
.../template_with_formatting_streaming.xlsx | Bin 0 -> 10206 bytes
3 files changed, 74 insertions(+), 6 deletions(-)
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
index 083bd80007..7afa795a75 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransform.java
@@ -985,17 +985,28 @@ public class ExcelWriterTransform
int startY = !Utils.isEmpty(data.realStartingCell) ? posY :
Math.max(posY, sheet.getLastRowNum());
ExcelWriterWorkbookDefinition workbookDefinition =
- new ExcelWriterWorkbookDefinition(baseFileName, file, wb, sheet,
posX, startY);
- workbookDefinition.setSplitNr(splitNr);
- data.usedFiles.add(workbookDefinition);
- data.currentWorkbookDefinition = workbookDefinition;
- data.currentWorkbookDefinition.clearStyleCache(numOfFields);
+ 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))) {
+ 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 (log.isDebug()) {
logDebug(
BaseMessages.getString(
@@ -1009,6 +1020,18 @@ public class ExcelWriterTransform
}
}
+ private ExcelWriterWorkbookDefinition prepareWorkbookDefinition(int
numOfFields, int splitNr, FileObject file,
+ Workbook wb,
Sheet sheet, int posX,
+ String
baseFileName, int startY) {
+ ExcelWriterWorkbookDefinition workbookDefinition =
+ new ExcelWriterWorkbookDefinition(baseFileName, file, wb, sheet,
posX, startY);
+ workbookDefinition.setSplitNr(splitNr);
+ data.usedFiles.add(workbookDefinition);
+ data.currentWorkbookDefinition = workbookDefinition;
+ data.currentWorkbookDefinition.clearStyleCache(numOfFields);
+ return workbookDefinition;
+ }
+
private Sheet openLine(Sheet sheet, int posY) {
if (data.shiftExistingCells) {
sheet.shiftRows(posY, Math.max(posY, sheet.getLastRowNum()), 1);
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformTest.java
index bcfb6c5fcf..ef99fd912e 100644
---
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformTest.java
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelwriter/ExcelWriterTransformTest.java
@@ -203,7 +203,7 @@ public class ExcelWriterTransformTest {
when(vmi.getNumber(anyObject())).thenReturn(12.0);
when(metaMock.getTemplate().isTemplateEnabled()).thenReturn(true);
- when(metaMock.getFile().isStreamingData()).thenReturn(true);
+ when(metaMock.getFile().isStreamingData()).thenReturn(false);
when(metaMock.isHeaderEnabled()).thenReturn(false);
when(metaMock.getFile().getExtension()).thenReturn(XLSX);
when(metaMock.getOutputFields()).thenReturn(fields);
@@ -223,6 +223,51 @@ public class ExcelWriterTransformTest {
verify(dataMock.currentWorkbookDefinition.getSheet()).getRow(1);
}
+
+ @Test
+ public void testWriteUsingTemplateWithFormatting_Streaming() throws
Exception {
+
+ String path = Files.createTempDir().getAbsolutePath() + File.separator +
"formatted_streaming.xlsx";
+
+ dataMock.fieldnrs = new int[] {0};
+ dataMock.linkfieldnrs = new int[] {-1};
+ dataMock.commentfieldnrs = new int[] {-1};
+ dataMock.createNewFile = true;
+ dataMock.realTemplateFileName =
+
getClass().getResource("template_with_formatting_streaming.xlsx").getFile();
+ dataMock.realSheetname = "Data";
+ dataMock.inputRowMeta = mock(IRowMeta.class);
+
+ List<ExcelWriterOutputField> fields = new ArrayList<>();
+ fields.add(new ExcelWriterOutputField());
+
+ IValueMeta vmi = mock(ValueMetaInteger.class);
+ when(vmi.getType()).thenReturn(IValueMeta.TYPE_INTEGER);
+ when(vmi.getName()).thenReturn("name");
+ when(vmi.getNumber(anyObject())).thenReturn(12.0);
+
+ when(metaMock.getTemplate().isTemplateEnabled()).thenReturn(true);
+ when(metaMock.getFile().isStreamingData()).thenReturn(true);
+ when(metaMock.getStartingCell()).thenReturn("A2");
+
+ when(metaMock.isHeaderEnabled()).thenReturn(false);
+ when(metaMock.getFile().getExtension()).thenReturn(XLSX);
+ when(metaMock.getOutputFields()).thenReturn(fields);
+
+ when(dataMock.inputRowMeta.size()).thenReturn(10);
+ when(dataMock.inputRowMeta.getValueMeta(anyInt())).thenReturn(vmi);
+
+ when(transform.buildFilename(0)).thenReturn(path);
+ dataMock.usedFiles.add(dataMock.currentWorkbookDefinition);
+ transform.prepareNextOutputFile(any(Object[].class));
+
+ dataMock.currentWorkbookDefinition.setPosY(1);
+
dataMock.currentWorkbookDefinition.setSheet(spy(dataMock.currentWorkbookDefinition.getSheet()));
+ transform.writeNextLine(dataMock.currentWorkbookDefinition, new Object[]
{12});
+
+ verify(dataMock.currentWorkbookDefinition.getSheet(),
times(1)).createRow(1);
+ verify(dataMock.currentWorkbookDefinition.getSheet()).getRow(1);
+ }
@Test
public void testValueBigNumber() throws Exception {
diff --git
a/plugins/transforms/excel/src/test/resources/org/apache/hop/pipeline/transforms/excelwriter/template_with_formatting_streaming.xlsx
b/plugins/transforms/excel/src/test/resources/org/apache/hop/pipeline/transforms/excelwriter/template_with_formatting_streaming.xlsx
new file mode 100644
index 0000000000..28130df8a9
Binary files /dev/null and
b/plugins/transforms/excel/src/test/resources/org/apache/hop/pipeline/transforms/excelwriter/template_with_formatting_streaming.xlsx
differ
