Skip to main content
yamaga
Gold ⭐️
December 9, 2022
Solved

Write Flowfile content into Excel (or xls) file by Groovy (and Ivy)

Hi

 

I need to extract data from a SQL query into Excel file (or xls)

Is it possible to do it by Groovy script ?

 

yamaga
yamagaAnswer
Gold ⭐️
January 3, 2023

I found a solution, this is the script:

 

@Grapes(@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'))
import com.opencsv.CSVReader
@Grapes(@Grab(group='com.opencsv', module='opencsv', version='4.2'))
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.streaming.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.extractor.*
import java.nio.charset.*
import java.io.*
import org.apache.commons.io.IOUtils

def flowFile = session.get()
def date = new Date()

if(!flowFile) return


flowFile = session.write(flowFile, {inputStream, outputStream ->
SXSSFSheet sheet1 = null;
CSVReader reader = null;
Workbook wb = null;
String generatedXlsFilePath = "/home/";
FileOutputStream fileOutputStream = null;

def filename = flowFile.getAttribute('filename')
def path = flowFile.getAttribute('path')

def nextLine = ''
reader = new CSVReader(new FileReader(path+filename), ',');

//Workbook wb = WorkbookFactory.create(inputStream,);
//Sheet sheet1 = wb.createSheet("Feuille");

wb = new SXSSFWorkbook(inputStream);
sheet1 = (SXSSFSheet) wb.createSheet('Sheet');

def rowNum = 0;
while((nextLine = reader.readNext()) != null) {
Row currentRow = sheet1.createRow(rowNum++);
for(int i=0; i < nextLine.length; i++) {
if(NumberUtils.isDigits(nextLine[i])) {
currentRow.createCell(i).setCellValue(Integer.parseInt(nextLine[i]));
} else if (NumberUtils.isNumber(nextLine[i])) {
currentRow.createCell(i).setCellValue(Double.parseDouble(nextLine[i]));
} else {
currentRow.createCell(i).setCellValue(nextLine[i]);
}
}
}


//fileOutputStream = new FileOutputStream(generatedXlsFilePath.trim());
//wb.write(fileOutputStream);
generatedXlsFilePath = generatedXlsFilePath + 'SAISIE_MAGASING.XLS'
outputStream = new FileOutputStream(generatedXlsFilePath.trim());

wb.write(outputStream);

wb.close();
//fileOutputStream.close();
outputStream.close();
reader.close();
//outputStream.close();
inputStream.close();


} as StreamCallback)



flowFile = session.putAttribute(flowFile, 'filename', filename)

5 replies

Faerballert
Gold ⭐️
December 9, 2022

Hello,

 

you can use after you received the result from your SQL-Database the ConvertRecord Processor to convert from Avro (Reader) to CSV (Writer). That should handle your question to do it without some Script.

 

Greetings

Translate
yamaga
yamagaAuthor
Gold ⭐️
December 9, 2022

My input should be XLS file not csv

I think about Groovy script but did not find anything about it

Translate
Faerballert
Gold ⭐️
December 9, 2022

Sorry, I misread 😞 

But after convertion to csv you could check out topics like https://community.cloudera.com/t5/Support-Questions/how-to-convert-CSV-to-excel-using-apache-nifi/td-p/332089 

Translate
yamaga
yamagaAuthor
Gold ⭐️
December 21, 2022

Any ideas please ?

Translate
yamaga
yamagaAuthorAnswer
Gold ⭐️
January 3, 2023

I found a solution, this is the script:

 

@Grapes(@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'))
import com.opencsv.CSVReader
@Grapes(@Grab(group='com.opencsv', module='opencsv', version='4.2'))
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.streaming.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.extractor.*
import java.nio.charset.*
import java.io.*
import org.apache.commons.io.IOUtils

def flowFile = session.get()
def date = new Date()

if(!flowFile) return


flowFile = session.write(flowFile, {inputStream, outputStream ->
SXSSFSheet sheet1 = null;
CSVReader reader = null;
Workbook wb = null;
String generatedXlsFilePath = "/home/";
FileOutputStream fileOutputStream = null;

def filename = flowFile.getAttribute('filename')
def path = flowFile.getAttribute('path')

def nextLine = ''
reader = new CSVReader(new FileReader(path+filename), ',');

//Workbook wb = WorkbookFactory.create(inputStream,);
//Sheet sheet1 = wb.createSheet("Feuille");

wb = new SXSSFWorkbook(inputStream);
sheet1 = (SXSSFSheet) wb.createSheet('Sheet');

def rowNum = 0;
while((nextLine = reader.readNext()) != null) {
Row currentRow = sheet1.createRow(rowNum++);
for(int i=0; i < nextLine.length; i++) {
if(NumberUtils.isDigits(nextLine[i])) {
currentRow.createCell(i).setCellValue(Integer.parseInt(nextLine[i]));
} else if (NumberUtils.isNumber(nextLine[i])) {
currentRow.createCell(i).setCellValue(Double.parseDouble(nextLine[i]));
} else {
currentRow.createCell(i).setCellValue(nextLine[i]);
}
}
}


//fileOutputStream = new FileOutputStream(generatedXlsFilePath.trim());
//wb.write(fileOutputStream);
generatedXlsFilePath = generatedXlsFilePath + 'SAISIE_MAGASING.XLS'
outputStream = new FileOutputStream(generatedXlsFilePath.trim());

wb.write(outputStream);

wb.close();
//fileOutputStream.close();
outputStream.close();
reader.close();
//outputStream.close();
inputStream.close();


} as StreamCallback)



flowFile = session.putAttribute(flowFile, 'filename', filename)

Translate