Transforms .csv data to a MS Excel Workbook

skvFiler.groovy

#!/usr/bin/env groovy

@Grapes([
	@Grab(group='org.apache.poi', module='poi', version='3.16'),
    @Grab(group='net.sf.opencsv', module='opencsv', version='2.3')
])

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.CellType
import java.io.FileOutputStream
import au.com.bytecode.opencsv.CSVReader
import groovy.io.FileType

def cli = new CliBuilder( usage:"${this.class.getSimpleName()}.groovy", stopAtNonOption:true )
cli.d( longOpt:'dir', required:true, 'Directory att .skv filer i' )
cli.h( longOpt:'help', 'Prints this message' )
def options

if('-h' in args || '--help' in args) {
    cli.usage()
    return
} else {
    options = cli.parse( args )
}

HSSFWorkbook wb = new HSSFWorkbook()

(new File( options.arguments().first())).eachFile( FileType.FILES ) { file ->

	if ( file.toString().endsWith('.skv') ) {
		println "Processa fil: '${file}'"
		
		HSSFSheet sheet = wb.createSheet()
		CSVReader reader = new CSVReader( new InputStreamReader(new FileInputStream( file ), 'ISO_8859_1'), ';' as char )

		def dateList = []
		def rowCnt = 0
		reader.readAll().each{
			HSSFRow row = sheet.createRow( rowCnt++ as short )
			
			if (!it[0].equals(""))
				dateList << it[0]

			HSSFCell cell0 = row.createCell( 0 as short ).setCellValue( it[0] )
			HSSFCell cell1 = row.createCell( 1 as short ).setCellValue( it[1] )
			HSSFCell cell2 = row.createCell( 2 as short ).setCellValue( it[2].equals("") ? "" : it[2] as Double )
			HSSFCell cell3 = row.createCell( 3 as short ).setCellValue( it[3].equals("") ? "" : it[3] as Double )
			HSSFCell cell4 = row.createCell( 4 as short ).setCellValue( it[4] )
		}

		sheet.autoSizeColumn(0)
		sheet.autoSizeColumn(1)
		sheet.autoSizeColumn(2)
		sheet.autoSizeColumn(3)
		sheet.autoSizeColumn(4)

		def sheetName = "${dateList.min()} - ${dateList.max()}" //.replaceAll("-", "")
		wb.setSheetName( wb.getSheetIndex( sheet ), sheetName )
    }
}

def outFile =  new File (options.arguments().first(), "samling_skv_fil.xls" )
FileOutputStream fOut = new FileOutputStream( outFile ) 
wb.write( fOut )
fOut.flush()
fOut.close()