코틀린에서 엑셀시트 다루기

20190323-spread-sheet-manager-by-kotlin.adoc

간단한 스프레드시트 처리기(by 코틀린(kotlin))

build.gradle.kts

plugins {
    id("org.springframework.boot") version "2.1.3.RELEASE"
    id("org.jetbrains.kotlin.jvm") version "1.2.71"
    id("org.jetbrains.kotlin.plugin.spring") version "1.2.71"
}

apply {
    plugin("org.springframework.boot")
    plugin("io.spring.dependency-management")
    plugin("kotlin-kapt")
}

group = "io.woowa.bizmoney.migration"
version = "0.0.1-SNAPSHOT"

repositories {
    mavenCentral()
}

dependencies {
    compile("org.jetbrains.kotlin:kotlin-reflect")
    compile("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    compile("com.fasterxml.jackson.module:jackson-module-kotlin") // (1)
    compile("org.apache.poi:poi-ooxml:4.0.1")

    kapt("org.springframework.boot:spring-boot-configuration-processor")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
}

tasks {
    compileKotlin {
        kotlinOptions {
            freeCompilerArgs = mutableListOf("-Xjsr305=strict")
            jvmTarget = "1.8"
        }
        dependsOn(processResources)
    }
    compileTestKotlin {
        kotlinOptions {
            freeCompilerArgs = mutableListOf("-Xjsr305=strict")
            jvmTarget = "1.8"
        }
    }
}

1. 코틀린에서 Jackson 을 사용하기 위해서 KotlinModule 이 필요하다.

SpreadSheet

class SpreadSheet(
        val name: String,
        var header: Row?,
        val rows: List<Row>) {

    fun hasHeader(): Boolean {
        return header != null
    }

    class Row {
        private val cells = ArrayList<Cell<*>>()

        fun addCell(cell: Cell<*>): Row {
            this.cells.add(cell)
            return this
        }

        fun addCell(cellValue: Any): Row {
            return this.addCell(Cell(cellValue))
        }

        fun getCells(): List<Cell<*>> {
            return cells
        }

        fun getFirstCellValue(): String = getCells()[0].value.toString()

        fun getCellValue(index: Int): String = getCells()[index].value.toString()
    }

    data class Cell<T>(val value: T)
}

SpreadSheetManager

class SpreadSheetManager {
    private val log: Logger = LoggerFactory.getLogger(this.javaClass)

    /**
     * 스프레드시트 작성
     *
     * @param filepath: 생성파일 경로
     * @param spreadSheet 스프레스시트 파일 생성 원천
     */
    fun writeToFile(filepath: String, sheetName: String, spreadSheet: SpreadSheet) {
        var workbook = XSSFWorkbook()

        workbook.use {
            var sheet = workbook.createSheet(sheetName)

            var rowIdx = 0

            if (spreadSheet.hasHeader()) {
                var headerFont = workbook.createFont()
                headerFont.bold = true

                var headerStyle = workbook.createCellStyle()
                headerStyle.setFont(headerFont)

                var headerRow = sheet.createRow(rowIdx)
                var cell: Cell
                for (headerCellIdx in 0 until spreadSheet.header?.getCells()?.size!!) {
                    cell = headerRow.createCell(headerCellIdx)
                    cell.cellType = CellType.STRING
                    cell.cellStyle = headerStyle
                    cell.setCellValue(spreadSheet.header?.getCells()?.get(headerCellIdx)?.value.toString())
                }

                rowIdx++
            }

            for (sourceRow in spreadSheet.rows) {
                generateRow(sourceRow, sheet.createRow(rowIdx++))
            }

            FileOutputStream(filepath).use {
                workbook.write(it)
            }
        }
    }

    private fun generateRow(sourceRow: SpreadSheet.Row, destinationRow: Row) {
        for (cellIdx in 0 until sourceRow.getCells().size) {
            destinationRow.createCell(cellIdx)
            destinationRow.getCell(cellIdx).cellType = CellType.STRING
            destinationRow.getCell(cellIdx).setCellValue(sourceRow.getCells()[cellIdx].value.toString())
        }

    }

    /**
     * 스프레드시트 읽기
     */
    fun readFromFile(sourceFilePath: String, sheetIndex: Int, hasHeader: Boolean): SpreadSheet {
        log.debug("Read File[path: {}, sheetIndex: {}, hasHeader: {}]", sourceFilePath, sheetIndex, hasHeader)
        val file = File(sourceFilePath)

        WorkbookFactory.create(file).use {
            val sheet = it.getSheetAt(sheetIndex)

            var header: SpreadSheet.Row? = null
            var rows = ArrayList<SpreadSheet.Row>()

            if (hasHeader) {
                header = extractRow(sheet.getRow(0))
                for (rowIdx in 1..sheet.lastRowNum) {
                    rows.add(extractRow(sheet.getRow(rowIdx)))
                }
            } else {
                for (rowIdx in 0..sheet.lastRowNum) {
                    rows.add(extractRow(sheet.getRow(rowIdx)))
                }
            }

            return SpreadSheet(file.name, header, rows)
        }
    }

    private fun extractRow(source: Row): SpreadSheet.Row {
        var row = SpreadSheet.Row()

        for (idx in source.firstCellNum until source.lastCellNum) {
            if(source.getCell(idx) == null) {
                row.addCell(SpreadSheet.Cell(""))
                continue
            }

            var sourceCell = source.getCell(idx)
            when (sourceCell.cellType) {
                CellType.NUMERIC -> row.addCell(SpreadSheet.Cell(NumberToTextConverter.toText(sourceCell.numericCellValue)))
                else -> {
                    row.addCell(SpreadSheet.Cell(sourceCell.stringCellValue))
                }
            }
        }

        return row
    }
}