0

I want to convert csv flowfile content into XLS file usin groovy script + POI API

I m using ivy to grab POI dependencies and it seems work well.

please find more details below:

This is my Nifi flow:

This is my script

@Grapes(@Grab(group='org.apache.poi', module='poi-ooxml', version='3.9'))
import org.apache.poi.ss.usermodel.*
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 ->
try {
    
Workbook wb = WorkbookFactory.create(inputStream,);
Sheet mySheet = wb.getSheetAt(0);


def record = IOUtils.toString(inputStream, StandardCharsets.UTF_8)
outputStream.write(record.getBytes(StandardCharsets.UTF_8))

wb.close();

} catch(e) {
 log.error("Error during processing", e)
 session.transfer(flowFile, REL_FAILURE)
}
} as StreamCallback)


def filename = flowFile.getAttribute('filename')+'.xlsx'
flowFile = session.putAttribute(flowFile, 'filename', filename) 

session.transfer(flowFile, REL_SUCCESS)

I get this error enter image description here

Any idea about this error ?

Edit: I changed my script and still had the same error:

@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.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 ->
try {
def nextLine = ''
reader = new CSVReader(new FileReader(csvFilePath), FILE_DELIMITER);

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

// workBook = new SXSSFWorkbook();
//sheet = (SXSSFSheet) workBook.createSheet('Sheet');

int 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);

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

} catch(e) {
session.transfer(flowFile, REL_FAILURE)
}
} as StreamCallback)

def filename = flowFile.getAttribute('filename')+'.xlsx'
flowFile = session.putAttribute(flowFile, 'filename', filename)

session.transfer(flowFile, REL_SUCCESS)
O. Sam
  • 178
  • 1
  • 5
  • 17
  • 1
    Does your code work outside of NiFi? As I can't see anything reading a CSV, can't see anything writing meaningful stuff to rows and cells in Apache POI, I'm guessing not... You'll need to fix your code to work standalone first! – Gagravarr Dec 23 '22 at 12:20
  • 1
    You are creating WorkbookFactory from input stream but you are saying that input contains csv file. So, poi saying that can't read input stream because it's not ole2 or ooxml... – daggett Dec 24 '22 at 23:04
  • @daggett I want to convert csv content into xls format, can you help with the script please? – O. Sam Dec 28 '22 at 23:38

0 Answers0