example-excel-to-sql.groovy

@Grapes([
    @Grab(group='org.apache.poi', module='poi', version='3.15'),
    @Grab(group='org.apache.poi', module='poi-ooxml', version='3.15'),
])

import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook

// input file: make sure we can read it
File inFile = new File('../input/contacts.xlsx')
println "Reading from ->    ${inFile.canonicalPath}"
assert inFile.exists() && inFile.canRead()

// set up output
File outFile = new File('../output/create-contacts.sql')
println "Printing to ->     ${outFile.canonicalPath}"
if ( outFile.exists() ) {
    outFile.delete()
}


// loop through a sheet in the workbook, appending sql to outfile
Workbook wb = new XSSFWorkbook(inFile.canonicalPath)
Sheet worksheet = wb.getSheetAt(0)
// offset by 1 if headers, 0 if none...
(1..worksheet.lastRowNum).each{ rowIndex ->
    Row row = worksheet.getRow(rowIndex)

    outFile.append("""
insert into contact (first_name, last_name) values (
    '${row.getCell(0).stringCellValue}',
    '${row.getCell(1).stringCellValue}',
)
    """)
}