ChamomileGuides 3.0.4 Help

엑셀 모듈 사용 가이드

개요

조회된 데이터를 엑셀파일로 만들어주거나 사용자에게 다운받을 수 있도록 제공해준다.

엑셀파일을 import가능한 형태의 데이터로 만들어주어 개발자에게 편의성을 제공한다.

Apache Poi 기반으로 작성되어 있어 apache poi 가 내장되어있고 제공해주는 기능 외에 apche poi를 직접 쓸수있다.

엑셀 다운로드

  1. 프로젝트 생성(chamomile-sample-boot-basic)

  2. dependency 등록(pom.xml)

    <dependency> <groupId>net.lotte.chamomile.module</groupId> <artifactId>chamomile-file-excel</artifactId> </dependency>

  3. yml 파일 작성(application.yml)

    chmm: file: repositoryPath: /files allowExtension: "" maxSize: 20MB directoryDateNameFormat: yyyy/MM

  4. 엑셀용 VO 생성
    BoardExcelVO.java

    @Getter @Setter @NoArgsConstructor @SuperBuilder public class BoardExcelVO extends TimeAuthorLog { private Long id; private String title; private String content; public BoardExcelVO(Long id, String title, String content) { this.id = id; this.title = title; this.content = content; } }

  5. mapper 및 쿼리 작성
    BoardMapper.java

    List<BoardExcelVO> getAllBoardsListExcel();

    resources/sql/service/BoardMapper.xml

    <!-- Create --> <select id="getAllBoardsListExcel" resultType="demo.board.domain.BoardExcelVO"> SELECT * FROM BOARD </select>

  6. 서비스 인터페이스 및 서비스 구현체 작성.
    BoardService.java

    List<BoardExcelVO> getAllBoardsExcelList();

    BoardServiceImpl.java

    @Override @Transactional(readOnly = true) public List<BoardExcelVO> getAllBoardsExcelList() { return boardMapper.getAllBoardsListExcel(); }

  7. 컨트롤러 작성
    BoardController.java

    @GetMapping("/excel/download") public ResponseEntity<byte[]> excelDownload() throws IOException { List<BoardExcelVO> result = boardService.getAllBoardsExcelList(); ExcelExporter excelExporter = new ExcelExporter("board"); excelExporter.addDataList(result); return excelExporter.toHttpResponse(); }

엑셀 업로드

  1. 프로젝트 생성(chamomile-sample-boot-basic)

  2. dependency 등록(pom.xml)

    <dependency> <groupId>net.lotte.chamomile.module</groupId> <artifactId>chamomile-file-excel</artifactId> </dependency>

  3. yml 파일 작성(application.yml)

    chmm: file: repositoryPath: /files allowExtension: "" maxSize: 20MB directoryDateNameFormat: yyyy/MM

  4. 엑셀용 VO 생성
    BoardExcelVO.java

    @Getter @Setter @NoArgsConstructor @SuperBuilder public class BoardExcelVO extends TimeAuthorLog { private Long id; private String title; private String content; public BoardExcelVO(Long id, String title, String content) { this.id = id; this.title = title; this.content = content; } }

  5. mapper 및 쿼리 작성
    BoardMapper.java

    void insertResourceExcel(List<ResourceExcelVO> command, Batchable batchable);

    resources/sql/service/BoardMapper.xml

    <!-- Create --> <insert id="insertBoardExcel" parameterType="demo.board.domain.Board"> INSERT INTO BOARD (ID, TITLE, CONTENT, SYS_INSERT_DTM, SYS_UPDATE_DTM) VALUES (#{id}, #{title}, #{content}, #{sysInsertDtm}, #{sysUpdateDtm}) </insert>

  6. 서비스 인터페이스 및 서비스 구현체 작성.
    BoardService.java

    void createBoard(List<BoardExcelVO> request);

    BoardServiceImpl.java

    @Override public void createBoard(List<BoardExcelVO> request) { request.forEach(TimeAuthorLog::onCreate); boardMapper.insertBoardExcel(request, new BatchRequest(1000)); }

  7. 컨트롤러 작성
    BoardController.java

    @PostMapping(value = "/excel/upload", consumes = MediaType.MULTIPART_FORM_DATA_VALUE) public ChamomileResponse<Void> excelUpload(@RequestParam("file") MultipartFile file) { FileMetadataInfoEntity fileVo = fileUploader.fileUpload(file); List<BoardExcelVO> list = new ExcelImporter().toCustomClass(fileVo.getRealFilePath(), BoardExcelVO.class); boardService.createBoard(list); return new ChamomileResponse<>(); }

엑셀 API

클래스

메서드명

파라미터

반환값

설명

ExcelCommonUtil

getColumnInfo

Sheet sheet, int firstRowIndex

List\<Column\>

지정된 sheet의 요청된 행에서 column정보를 얻어온다.

getExcel

String path

Workbook

파일을 읽어 workbook객체를 반환한다.

getSheets

Workbook workbook

List\<Sheet\>

workbook으로 부터 sheet정보들을 읽어와 list로 반환한다.

invokeMethod

String fieldName, Object obj

Object

필드명으로 get메서드를 실행하여 값을 반환한다.

getListFromExcel

SpreadSheet ss, String sheetName, Class\<?\> clazz

List\<?\>

엑셀에서 추출한 SpreadSheet객체를 List형태로 반환한다.

arrangeFields

Class\<?\> clazz

List\<Field\>

excel export시 annotation에 index로 정의된 순서대로 정렬 한 후 annotation이 정의된 field목록들만 반환한다.

ExcelDataHandler

makeViewTitle

List\<?\> data, Sheet sheet, Row row, Cell cell

타이틀 행을 만든다. 설정된 최대 열 이상의 데이터는 무시된다.

makeViewRows

List\<?\> data, Sheet sheet, Row row, Cell cell, Locale locale

int

데이터 타입별로 알맞게 각 행들을 만든다. 설정된 최대 행/최대 열 이상의 데이터는 무시된다.

getRows

Sheet sheet, int firstRowIndex

List\<RowElement\>

엑셀 sheet에서 행/셀 데이터를 추출하여 RowElement로 변환한다.

\- ExcelFile - ExcelViewer

setLocale

Locale locale

로케일정보를 저장한다.

setDocName (ExcelViewer에만 해당됨)

String docName

파일명을 지정한다.

addData

List\<?\> data

조회된 데이터를 추가한다. 이 때 sheet명은 annotation에 지정된 이름으로 한다. 중복될 경우 뒤에 ‘\#’이 자동으로 추가된다.

addData

List\<?\> data, String sheetName

sheet네임을 지정하여 조회된 데이터를 추가한다. 이때 annotatino에 지정된 이름은 무시된다. 중복될 경우 뒤에 ‘\#’이 자동으로 추가된다.

init

리스트를 초기화해준다.

makeExcelFile (ExcelFile에만 해당됨)

String filePath

엑셀 파일을 생성하여 서버에 저장한다.

makeExcel

List\<List\<?\>\> data, List\<SheetElement\> sheetInfo, Workbook workbook

주어진 데이터로 엑셀파일을 구성하는 메서드. 재정의시 해당 메서드를 이용하여 커스터마이징한다.

ImportExcel

importData

String path

SpreadSheet

엑셀파일의 로컬경로를 매개변수로 넘겨 SpreadSheet객체로 받는다

LargeDataExport

createLargeExcel

ResultSet rs, String path

boolean

대용량 엑셀파일을 생성한다. 이 기능이 완료 되기 전까지 connection, Statement등을 close시키지 말아야 한다. 기존파일은 덮어 쓴다.

SpreadSheet

addSheet

SheetElement sheet

SpreadSheet객체는 엑셀파일 한 개로 인식하면 된다. 이 메소드는 sheet를 추가한다.

get

String sheetName

SheetElement

sheet명으로 객체내의 sheet를가져온다.

get

int index

SheetElement

index로 객체내의 sheet를가져온다.

getSheets

List\<SheetElement\>

객체내의 모든 sheet를가져온다.

SheetElement

SheetElement

String sheetName

생성자. sheet명을 지정하여 생성한다.

addRow

RowElement row

sheet내에 행을 추가한다.

setRows

List\<RowElement\> rows

sheet내에 행을 지정한다.

get

int index

RowElement

지정된 행을 얻어온다.

getSheetName

String

현재 sheet명을 얻어온다.

setSheetName

String sheetName

sheet명을 지정한다.

size

int

전체 행 수를 반환한다.

getRows

List\<RowElement\>

전체 행을 반한다.

RowElement

get

String columName

String

컬럼 이름으로 데이터를 얻어온다.

get

int index

String

인덱스 번호로 데이터를 얻어온다.

addCell

CellElement cell

행에 데이터를 추가한다

Excel Export시 사용되는 annotation정보

anntation명

속성

기본값

설명

ExcelSheet - 클래스에 적용한다.

name

sheet

기본sheet명이며 엑셀파일 생성시 addData에서 sheet명을 넘길경우 무시된다.

maxCols

500

최대 열 수 cf.) Excel 97\~2003 형식(*,xls) : 65,536행/256열 Excel 2007\~2013 (*.xlsx/xlsm) : 1,048,576행x16,384열

maxRows

2000

최대 행 수 cf.) Excel 97\~2003 형식(*,xls) : 65,536행/256열 Excel 2007\~2013 (*.xlsx/xlsm) : 1,048,576행x16,384열

foregroundColor

0x16 (HSSFColor


.HSSFColorPredefined


.GREY_25_PERCENT


.getIndex())

배경색 cf. public enum HSSFColorPredefined {BLACK (0x08, -1, 0x000000), BROWN (0x3C, -1, 0x993300), OLIVE_GREEN (0x3B, -1, 0x333300), DARK_GREEN (0x3A, -1, 0x003300), DARK_TEAL (0x38, -1, 0x003366), DARK_BLUE (0x12, 0x20, 0x000080), INDIGO (0x3E, -1, 0x333399), GREY_80_PERCENT (0x3F, -1, 0x333333), ORANGE (0x35, -1, 0xFF6600), DARK_YELLOW (0x13, -1, 0x808000), GREEN (0x11, -1, 0x008000), TEAL (0x15, 0x26, 0x008080), BLUE (0x0C, 0x27, 0x0000FF), BLUE_GREY (0x36, -1, 0x666699), GREY_50_PERCENT (0x17, -1, 0x808080), RED (0x0A, -1, 0xFF0000), LIGHT_ORANGE (0x34, -1, 0xFF9900), LIME (0x32, -1, 0x99CC00), SEA_GREEN (0x39, -1, 0x339966), AQUA (0x31, -1, 0x33CCCC), LIGHT_BLUE (0x30, -1, 0x3366FF), VIOLET (0x14, 0x24, 0x800080), GREY_40_PERCENT (0x37, -1, 0x969696), PINK (0x0E, 0x21, 0xFF00FF), GOLD (0x33, -1, 0xFFCC00), YELLOW (0x0D, 0x22, 0xFFFF00), BRIGHT_GREEN (0x0B, -1, 0x00FF00), TURQUOISE (0x0F, 0x23, 0x00FFFF), DARK_RED (0x10, 0x25, 0x800000), SKY_BLUE (0x28, -1, 0x00CCFF), PLUM (0x3D, 0x19, 0x993366), GREY_25_PERCENT (0x16, -1, 0xC0C0C0), ROSE (0x2D, -1, 0xFF99CC), LIGHT_YELLOW (0x2B, -1, 0xFFFF99), LIGHT_GREEN (0x2A, -1, 0xCCFFCC), LIGHT_TURQUOISE (0x29, 0x1B, 0xCCFFFF), PALE_BLUE (0x2C, -1, 0x99CCFF), LAVENDER (0x2E, -1, 0xCC99FF), WHITE (0x09, -1, 0xFFFFFF), CORNFLOWER_BLUE (0x18, -1, 0x9999FF), LEMON_CHIFFON (0x1A, -1, 0xFFFFCC), MAROON (0x19, -1, 0x7F0000), ORCHID (0x1C, -1, 0x660066), CORAL (0x1D, -1, 0xFF8080), ROYAL_BLUE (0x1E, -1, 0x0066CC), LIGHT_CORNFLOWER_BLUE (0x1F, -1, 0xCCCCFF), TAN (0x2F, -1, 0xFFCC99), AUTOMATIC (0x40, -1, 0x000000);

pattern

FillPatternType


.SOLID_FOREGROUND

배경 패턴 cf. public enum FillPatternType { /** No background */NO_FILL (0), /** Solidly filled */SOLID_FOREGROUND (1), /** Small fine dots */FINE_DOTS (2), /** Wide dots */ALT_BARS (3), /** Sparse dots */SPARSE_DOTS (4), /** Thick horizontal bands */THICK_HORZ_BANDS (5), /** Thick vertical bands */THICK_VERT_BANDS (6), /** Thick backward facing diagonals */THICK_BACKWARD_DIAG (7), /** Thick forward facing diagonals */THICK_FORWARD_DIAG (8), /** Large spots */BIG_SPOTS (9), /** Brick-like layout */BRICKS (10), /** Thin horizontal bands */THIN_HORZ_BANDS (11), /** Thin vertical bands */THIN_VERT_BANDS (12), /** Thin backward diagonal */THIN_BACKWARD_DIAG (13), /** Thin forward diagonal */THIN_FORWARD_DIAG (14), /** Squares */SQUARES (15), /** Diamonds */DIAMONDS (16), /** Less Dots */LESS_DOTS (17), /** Least Dots */LEAST_DOTS (18);

borderLeft

BorderStyle.THIN

왼쪽줄 cf. public enum BorderStyle { /** * No border (default) */NONE (0x0), /** * Thin border */THIN (0x1), /** * Medium border */MEDIUM (0x2), /** * dash border */DASHED (0x3), /** * dot border */DOTTED (0x4), /** * Thick border */THICK (0x5), /** * double-line border */DOUBLE (0x6), /** * hair-line border */HAIR (0x7), /** * Medium dashed border */MEDIUM_DASHED (0x8), /** * dash-dot border */DASH_DOT (0x9), /** * medium dash-dot border */MEDIUM_DASH_DOT (0xA), /** * dash-dot-dot border */DASH_DOT_DOT (0xB), /** * medium dash-dot-dot border */MEDIUM_DASH_DOT_DOT (0xC), /** * slanted dash-dot border */SLANTED_DASH_DOT (0xD);

borderRight

BorderStyle.THIN

오른쪽줄

borderTop

BorderStyle.THIN

윗쪽줄

borderBottom

BorderStyle.THIN

아래쪽줄

ExcelColumn - 출력 하고자 하는 필드에 적용한다.

name

공백문자

타이틀에 표시할 컬럼명을 입력한다.

width

4000

컬럼 폭

l10n

공백

지역화(date, currency)

privacyMasking

10

개인정보 필드 마스킹

index

0

데이터 표시순서

foregroundColor

0x16 (HSSFColor


.HSSFColorPredefined


.GREY_25_PERCENT


.getIndex())

배경색

pattern

FillPatternType


.SOLID_FOREGROUND

배경패턴

borderLeft

BorderStyle.THIN

왼쪽줄

borderRight

BorderStyle.THIN

오른쪽줄

borderTop

BorderStyle.THIN

윗쪽줄

borderBottom

BorderStyle.THIN

아래쪽줄

대용량 엑셀 파일 다운로드

엑셀 파일로 대용량 데이터를 다운로드 받을 때 저장된 건 수가 많다면 부하가 발생하여 OutOfMemoryError가 발생한다.

excel001.png

따라서 대용량 데이터를 엑셀로 다운받으려는 경우 다음과 같은 사용법으로 엑셀 다운로드를 진행해야한다. 다음 코드는 어드민 샘플코드에 있는 예제이다.

  1. Mapper 작성(net.lotte.chamomile.admin.resource.domain.ResourceMapper)

    List<ResourceExcelVO> findResourceListDataExcel2();

  2. 쿼리 작성(resources/sql/service/resource.xml)

    <select id="findResourceListDataExcel2" resultType="net.lotte.chamomile.admin.resource.domain.ResourceExcelVO"> SELECT RESOURCE_ID resourceId, /* 리소스 아이디 */ RESOURCE_URI resourceUri, /* 리소스 경로 */ RESOURCE_NAME resourceName, /* 리소스 명 */ RESOURCE_HTTPMETHOD resourceHttpMethod, /* http method */ RESOURCE_DESC resourceDesc, /* 리소스_설명 */ SECURITY_ORDER securityOrder, /* 리소스 순서 */ USE_YN useYn, /* 사용 여부 */ SYS_INSERT_DTM sysInsertDtm, /* 시스템_입력_일시 */ SYS_INSERT_USER_ID sysInsertUserId, /* 시스템_입력_사용자_아이디 */ SYS_UPDATE_DTM sysUpdateDtm, /* 시스템_수정_일시 */ SYS_UPDATE_USER_ID sysUpdateUserId /* 시스템_수정_사용자_아이디 */ FROM CHMM_RESOURCE_INFO A </select>

  3. 컨트롤러 작성(ResourceController.java)

    @GetMapping(path = "/testBatchExcel") public Void testBatchExcelResource(HttpServletResponse response) { ExcelStreamResultHandler handler = new ExcelStreamResultHandler(response,"test.xlsx"); BatchExcelMapper batchExcelMapper = new BatchExcelMapper(handler, "net.lotte.chamomile.admin.resource.domain.ResourceMapper.findResourceListDataExcel2"); batchExcelMapper.writeExcelFileOnHttpResponse(); return null; }

해당 코드는 캐모마일 어드민 샘플코드를 실행하여 swagger로 직접 테스트해볼 수 있다.

한계점

  1. 엑셀 최대 row를 넘을 수 없기 때문에 100만건 이하의 데이터만 가져올 수 있다.

  2. 해당 작업이 진행되는데에 개발 컴퓨터 기준으로 100만건의 데이터를 가져오는데 110초가 소요되었다. 서버의 용량이 낮다면 더욱 많은 시간이 소요될 수 있다.

  3. 권장되는 row 수는 5만 row까지 이며, 해당 경우 1초 이내에 작업이 처리되었다. 이 이상의 row의 경우 처리지연을 부담해야한다.

  4. 5만 row 이상은 다음과 같은 작업을 권장한다.

    • async 처리를 통해 화면에서 따로 작업을 진행

    • batch 솔루션을 통해 작업 진행

    • 상용 솔루션 구매

Last modified: 10 1월 2025
DB 모듈 사용 가이드캐모마일 파일 모듈 가이드