Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

Hi

 

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

Is it possible to do it by Groovy script ?

 

1 ACCEPTED SOLUTION

avatar
Contributor

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)

View solution in original post

5 REPLIES 5

avatar
Expert Contributor

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

avatar
Contributor

My input should be XLS file not csv

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

avatar
Expert Contributor

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... 

avatar
Contributor

Any ideas please ?

avatar
Contributor

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)