엑셀 파일을 파싱하고 이를 활용하여 급여 데이터를 등록하는 작업은 많은 데이터를 처리할 때 유용합니다. 이 글에서는 Kotlin을 사용하여 엑셀 파일을 동적으로 파싱하고 급여 데이터를 효율적으로 등록하는 방법을 설명하겠습니다.
- 엑셀 파일 처리 추상 클래스 설계
엑셀 파일은 다양한 형식과 데이터를 포함할 수 있으므로, 추상 클래스를 통해 재사용 가능한 구조를 설계하는 것이 중요합니다. 아래는 엑셀 파일 처리에 대한 기본적인 추상 클래스입니다.
abstract class ExcelFileProcessor<S: Workbook, D> : DataProcessor<S, D> {
abstract val startRow: Int
abstract val chunk: Int
override fun <H: Header> process(source: S, validator: Validator<H, in D>): List<D> {
val sheet = source.getSheetAt(0)
val header = header(sheet)
val res: MutableList<D> = mutableListOf()
@Suppress("UNCHECKED_CAST")
validator.checkHeader(header as H)
run breaker@{
(startRow..sheet.lastRowNum step chunk).forEach {
val rows = (it until (it + chunk)).map { idx -> sheet.getRow(idx) }
if (isBreaking(rows[0])) {
return@breaker
}
val processedData = processRowData(source, header, rows, it) ?: return@forEach
try {
validator.checkData(processedData)
res.add(processedData)
} catch (t: Throwable) {
log.error(t) { "error!!!" }
log.info { "Data failed the predicate check, not adding to the result list" }
}
}
}
return res
}
protected abstract fun header(sheet: Sheet): Header?
protected abstract fun processRowData(source: S, header: Header?, unit: List<Row>, idx: Int): D?
protected abstract fun isBreaking(row: Row): Boolean
}- 먼저 ExcelFileProcessor라는 추상 클래스를 정의하였습니다. 이 클래스는 Excel 파일을 처리하기 위한 기본 구조를 제공합니다. startRow와 chunk를 통해 데이터의 시작 행과 처리할 행의 덩어리 크기를 정의합니다.
process 메서드는 Excel 파일의 첫 번째 시트를 읽고, 유효성 검사를 수행한 후 데이터를 처리합니다.
- 급여 등록 데이터 처리:
PayrollRegisterMultiRowProcessor
급여 대장을 처리하기 위해서 ObjectMapper를 사용하여 JSON 데이터를 쉽게 다룰 수 있도록 했습니다. 또한, 비과세 항목을 효율적으로 계산하기 위해 리스트 필터링과 맵핑을 사용했습니다.
class PayrollRegisterMultiRowProcessor: ExcelFileProcessor<PayrollSource, PayrollRegister>() {
override val startRow: Int = 10
override val chunk: Int = 4
// JSON 파싱을 위한 ObjectMapper 인스턴스 생성
private val objectMapper = ObjectMapper()
private val String.toWorkTimes get(): BigDecimal =
this.replace("""(일|시간)""".toRegex(), "").trim().toBigDecimal()
private object formatter {
fun of(pattern: String) = DateTimeFormatter.ofPattern(pattern)!!
}
data class SalaryHeader(override val items: List<I>,
private val properties: Map<Property, Any>): Header {
data class I(
val group: String,
override val name: String,
override val row: Int,
override val column: Int
): Item
@Suppress("UNCHECKED_CAST")
override fun <P : Any> property(key: Any): P? = properties[key as Property] as P?
}
override fun header(sheet: Sheet): Header {
val regExp = "\\s".toRegex()
val h = sheet.getRow(4)
var temp = ""
val group = (0 until h.physicalNumberOfCells).associateWith {
h.getCell(it).stringCellValue
.takeIf {v -> v.isNotEmpty() }
?.let inner@{v ->
temp = v.replace(regExp, "")
return@inner temp
} ?: temp
}
val headers = (5..8).map {r ->
val row = sheet.getRow(r)
(0 until row.physicalNumberOfCells).map {c ->
SalaryHeader.I(
group = group[c]!!,
name = row.getCell(c).stringCellValue.replace(regExp, ""),
row = r-5,
column = c
)
}
}.flatten()
val properties = sheet.getRow(2).let { r ->
val hospitalName = r.getCell(0)
val dates = (1 until r.lastCellNum)
.first { r.getCell(it)?.stringCellValue?.contains("귀속") ?: false }
.let {
val pattern = """\[(귀속|지급):([\s\w년월일]+)]""".toRegex()
val space = """\s""".toRegex()
pattern.findAll(r.getCell(it).stringCellValue).associate { x ->
val (key, value) = x.destructured
key.replace(space, "") to value.replace(space, "")
}
}
listOf(
HOSPITAL_NAME to hospitalName.toString(),
SALARY_MONTH to dates["귀속"]?.let {d -> YearMonth.parse(d, formatter.of("yyyy년M월")) },
PAYMENT_AT to dates["지급"]?.let {d -> LocalDate.parse(d, formatter.of("yyyy년M월d일")) },
).filter {(_, v) -> v != null}.associate {(k, v) -> k to v!!}
}
return SalaryHeader(items = headers, properties = properties)
}
override fun processRowData(source: PayrollSource, header: Header?, unit: List<Row>, idx: Int): PayrollRegister {
val h = header as SalaryHeader
// 인적, 수당, 공제 정보를 필터링하여 매핑
val employeeDetails = h.items.filter { it.group.contains("인적") }
.associate { it.name to stringValue(it, unit) }
val salaryDetails = h.items.filter { it.group.contains("수당") }
.associate { it.name to numericValue(it, unit) }
val deductionDetails = h.items.filter { it.group.contains("공제") }
.associate { it.name to numericValue(it, unit) }
// 병원명 가져오기
val hospitalName = h.property<String>(HOSPITAL_NAME) ?: throw IllegalArgumentException(
"""
{
"title": "급여대장 업로드 확인",
"content": "병원명이 필요합니다."
}
""".trimIndent()
)
// 지급일자 가져오기
val paymentDate = h.property<LocalDate>(PAYMENT_AT) ?: throw IllegalArgumentException(
"""
{
"title": "급여대장 업로드 확인",
"content": "지급일자가 필요합니다."
}
""".trimIndent()
)
// 귀속월 가져오기
val salaryMonth = h.property<YearMonth>(SALARY_MONTH) ?: throw IllegalArgumentException(
"""
{
"title": "급여대장 업로드 확인",
"content": "귀속월이 필요합니다."
}
""".trimIndent()
)
// 병원명 및 귀속월 검증
require(source.hospitalName.equals(hospitalName, ignoreCase = true)) {
"""{
"title": "급여대장 업로드 확인",
"content": "업로드하신 급여대장은 '$hospitalName' 자료 입니다. 현재 '${source.hospitalName}' 급여대장 등록 작업중입니다.\r\n 엑셀파일 확인 후 다시 시도해 주십시오."
}
""".trimIndent()
}
require(source.salaryMonth == salaryMonth) {
"""{
"title": "급여대장 업로드 확인",
"content": "업로드하신 급여대장은 ${salaryMonth.year}년 ${salaryMonth.monthValue}월(귀속)분 입니다.\r\n 현재 ${source.salaryMonth.year}년 ${source.salaryMonth.monthValue}월(귀속)분 등록 작업중입니다. 엑셀파일 확인 후 다시 시도해 주십시오."
}
""".trimIndent()
}
// 비과세 항목 리스트
val taxExemptionList = listOf(
"식대", "식비", "식대(비과세)", "차량유지비", "자가운전",
"자가운전보조금", "자가운전수당", "차량보조금",
"차량운전보조금", "차량지원비", "연구개발비"
)
// 비과세 금액 계산 (식대, 운전보조금, 연구개발비 - 항목 당 최대 20만원)
val taxExemptionAmount = salaryDetails
.filterKeys { it in taxExemptionList }
.values
.map { it?.min(BigDecimal(200000)) ?: BigDecimal.ZERO }
.reduce { acc, v -> acc.add(v) }
// 직원 정보 생성
val employee = _employee(
number = stringValue(h["사원번호"], unit),
name = stringValue(h["성명"], unit) ?: throw IllegalArgumentException("성명이 필요합니다."),
...
).apply {
employeeDetails.forEach { (k, v) -> items += k to (v ?: "") }
}
// 급여 정보 생성
val salary = _salary(
basicSalary = numericValue(h["기본급"], unit) ?: throw IllegalArgumentException("기본급이 필요합니다."),
totalSalary = numericValue(h["지급합계"], unit) ?: throw IllegalArgumentException("지급합계가 필요합니다."),
taxExemption = taxExemptionAmount
).apply {
salaryDetails.forEach { (k, v) -> items += k to (v ?: BigDecimal.ZERO) }
}
// 공제 정보 생성
val deduction = _deduction(
totalDeduction = numericValue(h["공제합계"], unit) ?: throw IllegalArgumentException("공제합계가 필요합니다."),
).apply {
deductionDetails.forEach { (k, v) -> items += k to (v ?: BigDecimal.ZERO) }
}
return employeeAndSalary(
source.salaryMonth,
paymentDate,
employee,
salary,
deduction
)
}
override fun isBreaking(row: Row): Boolean {
return row.getCell(0).stringCellValue.trim().contains("합계")
}
private fun numericValue(header: Item?, unit: List<Row>): BigDecimal? {
try {
return header?.let { unit[it.row].getCell(it.column).numericCellValue.toBigDecimal() }
} catch (t: Throwable) {
processFail(t) { "Fail to read data from cell ${header?.let { "[${it.name}](${it.column}, ${unit[it.row].rowNum})" }} : ${t.message}" }
}
}
private fun stringValue(header: Item?, unit: List<Row>): String? {
try {
return header?.let { unit[it.row].getCell(it.column).stringCellValue }?.takeIf { it.trim().isNotEmpty() }
} catch (t: Throwable) {
processFail(t) { "Fail to read data from cell ${header?.let { "[${it.name}](${it.column}, ${unit[it.row].rowNum})" }} : ${t.message}" }
}
}
}- 유효성 검사:
PayrollValidator
이PayrollValidator클래스는Validator인터페이스를 구현하여 급여 데이터의 유효성을 검사하는 역할을 합니다.
여기서는 두 가지 주요 검증이 이루어집니다: 헤더 검사와 데이터 검사입니다.
class PayrollValidator : Validator<SalaryHeader, PayrollRegister> {
private val log = KotlinLogging.logger {}
override fun checkHeader(header: SalaryHeader): SalaryHeader {
val duplicates = header.items.groupingBy { item -> item.name }
.eachCount()
.filterKeys { it.isNotEmpty() }
.filterValues { it > 1 }
if (duplicates.isNotEmpty()) {
log.info("duplicates :: $duplicates")
processFail {
"""{
"title": "급여대장 동일항목 확인",
"content": "업로드하신 엑셀 파일에 동일한 항목이 중복으로 있습니다. \r\n [${duplicates.keys.joinToString(",")}] \r\n 엑셀파일 확인 후 다시 시도해 주십시오."
}
""".trimIndent()
}
}
return header
}
override fun checkData(data: PayrollRegister): PayrollRegister {
val required = mapOf(
"성명" to data.employee.name,
"주민등록번호" to data.employee.rrn,
"기본급 " to data.salary.basicSalary,
).filterValues { it == null }.keys
if(required.isNotEmpty()) {
processFail {
log.info("required :: $required")
"""{
"title": "급여대장 필수정보 누락",
"content": "업로드하신 엑셀 파일에서 ${required.joinToString(",")}을 확인할 수 없습니다. \r\n 엑셀파일 확인 후 다시 시도해 주십시오."
}
""".trimIndent()
}
}
return data
}
}- checkHeader는 급여 대장의 헤더에서 중복 항목을 검사하며, 중복이 발견되면 사용자에게 이를 알리는 메시지를 반환합니다.
checkData는 급여 데이터의 필수 정보가 누락되었는지 검사하며, 누락된 정보가 있으면 사용자에게 이를 알리는 메시지를 반환합니다.
개발
