package kr.itn.itnhub.seed; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.jupiter.api.Test; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.util.List; import static org.assertj.core.api.Assertions.assertThat; import static org.assertj.core.api.Assertions.assertThatThrownBy; class SeedParserTest { /** 실제 시트와 같은 배치로 최소 통합문서를 만든다. 데이터는 3행(index 2)부터. */ private byte[] workbook(String[]... dataRows) throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) { Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME); DataFormat fmt = wb.createDataFormat(); CellStyle text = wb.createCellStyle(); text.setDataFormat(fmt.getFormat("@")); sheet.createRow(0).createCell(1).setCellValue("자료 넘버링"); Row header = sheet.createRow(1); header.createCell(1).setCellValue("연번1"); header.createCell(2).setCellValue("연번2"); header.createCell(5).setCellValue("기관명"); int rowNum = 2; for (String[] data : dataRows) { Row row = sheet.createRow(rowNum++); for (int i = 0; i < data.length; i++) { if (data[i] == null) { continue; } // B=1, C=2, F=5, G=6, H=7, I=8, J=9, K=10 int col = switch (i) { case 0 -> 1; case 1 -> 2; case 2 -> 5; default -> i + 3; }; Cell cell = row.createCell(col); cell.setCellStyle(text); cell.setCellValue(data[i]); } } wb.write(out); return out.toByteArray(); } } private SeedParser parser = new SeedParser(); @Test void 대표기관_행을_읽는다() throws Exception { byte[] xlsx = workbook(new String[]{ "001", "00", "국제방송교류재단", "데이터정보화팀", "송민지", "과장", "02-3475-5434", "ming@arirang.com"}); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx)); assertThat(rows).hasSize(1); SeedRow row = rows.get(0); assertThat(row.orgNo()).isEqualTo("001"); assertThat(row.orgName()).isEqualTo("국제방송교류재단"); assertThat(row.channelSlug()).isEqualTo("001"); assertThat(row.deptName()).isEqualTo("데이터정보화팀"); assertThat(row.managerName()).isEqualTo("송민지"); assertThat(row.managerTitle()).isEqualTo("과장"); assertThat(row.managerPhone()).isEqualTo("02-3475-5434"); assertThat(row.managerEmail()).isEqualTo("ming@arirang.com"); } @Test void 하위기관은_대표기관으로_흡수되어_행이_늘지_않는다() throws Exception { byte[] xlsx = workbook( new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"}, new String[]{"008", "02", "서울청", null, null, null, null, null}, new String[]{"008", "03", "부산청", null, null, null, null, null}); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx)); assertThat(rows).hasSize(1); assertThat(rows.get(0).orgName()).isEqualTo("경찰청"); } @Test void 치안정책연구소는_하위기관이지만_별도_기관으로_남는다() throws Exception { byte[] xlsx = workbook( new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"}, new String[]{"008", "01", "경찰청_치안정책연구소", null, null, null, null, null}, new String[]{"008", "02", "서울청", null, null, null, null, null}); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx)); assertThat(rows).hasSize(2); assertThat(rows).extracting(SeedRow::orgName) .containsExactly("경찰청", "경찰청_치안정책연구소"); assertThat(rows).extracting(SeedRow::channelSlug) .containsExactly("008", "008-policy"); } @Test void 연번의_앞자리_영이_보존된다() throws Exception { byte[] xlsx = workbook(new String[]{ "007", "00", "기후에너지환경부", "정보화담당관", "임난주", "공무직", "044-201-6431", "envlib@korea.kr"}); assertThat(parser.parse(new ByteArrayInputStream(xlsx)).get(0).orgNo()) .isEqualTo("007"); } @Test void 연번이_숫자로_저장돼_있어도_세자리로_복원한다() throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) { Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME); sheet.createRow(0); sheet.createRow(1); Row row = sheet.createRow(2); row.createCell(1).setCellValue(7); // B: 숫자 7 row.createCell(2).setCellValue(0); // C: 숫자 0 row.createCell(5).setCellValue("기후에너지환경부"); wb.write(out); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray())); assertThat(rows.get(0).orgNo()).isEqualTo("007"); } } @Test void 빈_행은_건너뛴다() throws Exception { byte[] xlsx = workbook( new String[]{"001", "00", "국제방송교류재단", "팀", "김", "과장", "02", "a@b.kr"}, new String[]{null, null, null, null, null, null, null, null}, new String[]{"002", "00", "세종학당재단", "팀", "권", "6급", "02", "c@d.kr"}); assertThat(parser.parse(new ByteArrayInputStream(xlsx))).hasSize(2); } @Test void 연번1이_부동소수점_오차로_정수보다_살짝_작아도_반올림한다() throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) { Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME); sheet.createRow(0); sheet.createRow(1); Row row = sheet.createRow(2); // 부동소수점 연산 결과로 흔히 나타나는 오차: 7이 아니라 6.999999999999998로 저장됨 row.createCell(1).setCellValue(6.999999999999998); row.createCell(2).setCellValue(0); row.createCell(5).setCellValue("기후에너지환경부"); wb.write(out); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray())); assertThat(rows.get(0).orgNo()).isEqualTo("007"); } } @Test void 기관명이_수식_셀이어도_캐시된_문자열_결과를_읽는다() throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) { Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME); sheet.createRow(0); sheet.createRow(1); Row row = sheet.createRow(2); row.createCell(1).setCellValue("001"); row.createCell(2).setCellValue("00"); Cell orgNameCell = row.createCell(5); orgNameCell.setCellFormula("\"국제방송교류재단\""); // 수식을 평가하지 않고 캐시된 결과 문자열을 직접 심어 둔다(파일에 저장된 캐시값 재현). orgNameCell.setCellValue("국제방송교류재단"); wb.write(out); List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray())); assertThat(rows).hasSize(1); assertThat(rows.get(0).orgName()).isEqualTo("국제방송교류재단"); } } @Test void 시트가_없으면_설명이_있는_예외를_던진다() throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) { wb.createSheet("다른시트"); wb.write(out); byte[] xlsx = out.toByteArray(); assertThatThrownBy(() -> parser.parse(new ByteArrayInputStream(xlsx))) .isInstanceOf(SeedParseException.class) .hasMessageContaining(SeedParser.SHEET_NAME); } } }