fuzzycsv.Excel2Csv.groovy Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of fuzzy-csv Show documentation
Show all versions of fuzzy-csv Show documentation
A groovy/java tabular Data (from CSV,SQL,JSON) processing library that supports fuzzy column matching,tranformations/merging/querying etc
package fuzzycsv
import fuzzycsv.FuzzyCSVTable
import groovy.transform.CompileStatic
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.slf4j.Logger
import org.slf4j.LoggerFactory
@CompileStatic
class Excel2Csv {
private static Logger log = LoggerFactory.getLogger(Excel2Csv)
static void testClassPath() {
try {
Class.forName('org.apache.poi.ss.usermodel.Workbook')
} catch (Throwable e) {
log.error("Apache Poi No Found.")
printRequiredDependencies()
throw e
}
}
static void printRequiredDependencies() {
println("""Add to Gradle:
compileOnly 'org.apache.poi:poi-ooxml:3.16', {
exclude group: 'stax', module: 'stax-api'
}
compileOnly 'org.apache.poi:ooxml-schemas:1.3', {
exclude group: 'stax', module: 'stax-api'
}""")
}
static Map toCsv(File file, int startRow = 0, int endRow = Integer.MAX_VALUE) {
Workbook wb = null
if (file.name.endsWith(".xls")) {
file.withInputStream {
wb = new HSSFWorkbook(it)
}
} else {
wb = new XSSFWorkbook(file)
}
return allSheetsToCsv(wb, startRow, endRow)
}
static Map allSheetsToCsv(Workbook wb, int startRow = 0, int endRow = Integer.MAX_VALUE) {
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator()
Map entries = wb.collectEntries { Sheet sheet -> [sheet.sheetName, sheetToCsvImpl(sheet, fe, startRow, endRow)] }
return entries
}
static FuzzyCSVTable toCsv(Workbook wb, int sheetNo = 0) {
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator()
def sheet = wb.getSheetAt(sheetNo)
return sheetToCsvImpl(sheet, fe)
}
static FuzzyCSVTable sheetToCsv(Sheet sheet, int startRow = 0, int endRow = Integer.MAX_VALUE) {
def evaluator = sheet.workbook.creationHelper.createFormulaEvaluator()
return sheetToCsvImpl(sheet, evaluator, startRow, endRow)
}
private
static FuzzyCSVTable sheetToCsvImpl(Sheet sheet, FormulaEvaluator fe, int startRow = 0, int endRow = Integer.MAX_VALUE) {
List result = []
int index = 0
for (Row row in sheet) {
if (row == null || index++ < startRow) {
continue
}
List