개발업무/개발

Java 대용량 엑셀 다운로드 (SXSSF)

NickTop 2024. 4. 10. 16:09

엑셀 용량이 크다면 SXSSF를 써야합니다

 

https://poi.apache.org/components/spreadsheet/

SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

윈도우 내에 있는 행만 접근하여 Heap 메모리가 낮게 사용할수있습니다. 단, 이전 행을 읽을 수 없습니다 (쓰기만 가능)

 

 

엑셀 예시

위와 같은 엑셀을 만들고 싶다고 가정합시다

 

Controller

@Controller
@RequiredArgsConstructor
public class MyExcelController {
    private final MyExcelService myExcelService;
    @RequestMapping("download")
    public void create(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "Attachment; Filename=myExcel.xlsx");
        ServletOutputStream servletOutputStream = response.getOutputStream();
        Workbook workbook = myExcelService.getExcel();
        workbook.write(servletOutputStream);
        workbook.close();
    }
}

 

 

Service

엑셀 파일을 작성하는 것은 service layer에서 합시다

SXSSFWorkbook을 생성합니다

@Service
@RequiredArgsConstructor
public class MyExcelService {
    private final WorkerRepository workerRepository;
    public Workbook getExcel() {
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        setTitle(sheet, createTitleStyle(workbook));
        setHeader(sheet, createHeaderStyle(workbook));
        setBody(sheet, createBodyStyle(workbook));
        return workbook;
    }
  ...

 

3개의 스타일이 필요합니다 (제목, 헤더, body)

    private CellStyle createTitleStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();

        // 배경색 지정
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 테두리
        cellStyle.setBorderTop(BorderStyle.MEDIUM);
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);

        // 폰트 지정
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);

        cellStyle.setFont(font);
        return cellStyle;
    }

    private CellStyle createHeaderStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();

        // 배경색 지정
        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 테두리
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        return cellStyle;
    }

    private CellStyle createBodyStyle(Workbook wb){
        CellStyle cellStyle = wb.createCellStyle();
        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 테두리
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        return cellStyle;
    }

 

제목과 헤더를 그립니다

row생성=>cell생성=>값넣기/스타일지정

순으로 코드를 작성해야합니다

    private void setTitle(Sheet sheet, CellStyle cellStyle) {
        Row titleRow = sheet.createRow(1);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("제목입니다");
        titleCell.setCellStyle(cellStyle);
        titleCell = titleRow.createCell(1);
        titleCell.setCellStyle(cellStyle);
        titleCell = titleRow.createCell(2);
        titleCell.setCellStyle(cellStyle);
        titleCell = titleRow.createCell(3);
        titleCell.setCellStyle(cellStyle);
        titleCell = titleRow.createCell(4);
        titleCell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,4)); // 셀병합
    }

    private void setHeader(Sheet sheet, CellStyle cellStyle) {
        Cell headerCell;
        Row headerRow = sheet.createRow(3);
        headerCell = headerRow.createCell(0);
        headerCell.setCellValue("순번");
        headerCell.setCellStyle(cellStyle);
        headerCell = headerRow.createCell(1);
        headerCell.setCellValue("날짜");
        headerCell.setCellStyle(cellStyle);
        headerCell = headerRow.createCell(2);
        headerCell.setCellValue("Id");
        headerCell.setCellStyle(cellStyle);
        headerCell = headerRow.createCell(3);
        headerCell.setCellValue("직원번호");
        headerCell.setCellStyle(cellStyle);
        headerCell = headerRow.createCell(4);
        headerCell.setCellValue("description");
        headerCell.setCellStyle(cellStyle);
        sheet.setColumnWidth(4, 20000); // description column width 조정
    }

 

body는 10만개의 데이터를 db에서 가져와서 읽습니다 (커서기반 페이징으로 db에서 가져왔습니다)

    private void setBody(Sheet sheet, CellStyle cellStyle) {
        Cell bodyCell;
        int row = 4;
        int seq = 1;
        List<Worker> workerList = workerRepository.findTop5000ByYyyymmddOrderByYyyymmddAscIdAsc("20250117");
        while (workerList.size()>0){
            for (Worker worker : workerList){
                Row bodyRow = sheet.createRow(row++);
                bodyCell = bodyRow.createCell(0); // 순번
                bodyCell.setCellValue(seq++);
                bodyCell.setCellStyle(cellStyle);
                bodyCell = bodyRow.createCell(1); // 날짜
                bodyCell.setCellValue(worker.getYyyymmdd());
                bodyCell.setCellStyle(cellStyle);
                bodyCell = bodyRow.createCell(2); // ID
                bodyCell.setCellValue(worker.getId());
                bodyCell.setCellStyle(cellStyle);
                bodyCell = bodyRow.createCell(3); // 직원번호
                bodyCell.setCellValue(worker.getWorkerId());
                bodyCell.setCellStyle(cellStyle);
                bodyCell = bodyRow.createCell(4); // description
                bodyCell.setCellValue(worker.getDescription());
                bodyCell.setCellStyle(cellStyle);
            }
            Worker worker = workerList.get(workerList.size()-1);
            workerList = workerRepository.findTop5000Page("20250117",worker.getId());
        }
    }

 

중복된 코드가 많아 우아한 형제들 기술블로그에는, DTO에 header와 스타일을 annotation으로 정의하는 방식으로 엑셀 다운로드를 구현했습니다

https://techblog.woowahan.com/2698/

 

Entity & Repository

@Data
public class WorkerPK {
    private String yyyymmdd;
    private Long id;
}

...

@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@IdClass(WorkerPK.class)
@Entity
@EntityListeners(AuditingEntityListener.class)
public class Worker {
    @Id
    private String yyyymmdd;
    @Id
    private Long id;

    private int workerId;

    private String description;
}

 

worker 생성 create문입니다

CREATE TABLE `worker` (
  `id` bigint NOT NULL,
  `yyyymmdd` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `worker_id` int NOT NULL,
  PRIMARY KEY (`yyyymmdd`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
@Repository
public interface WorkerRepository extends JpaRepository<Worker, Long> {
    @Query("select w from Worker w where w.yyyymmdd = :yyyymmdd and w.id > :id order by yyyymmdd, id limit 5000")
    List<Worker> findTop5000Page(String yyyymmdd, Long id);

    List<Worker> findTop5000ByYyyymmddOrderByYyyymmddAscIdAsc(String yyyymmdd);
}

 

테스트

주소창에 localhost:8080/download를 쳐서 테스트합니다

 

더미데이터 생성 및 통합테스트

더보기

2025-01-10 에서 2025-01-30까지 각 일자별 10만건의 테스트 데이터를 생성합니다

@Service
@AllArgsConstructor
public class InitData {
    private final WorkerRepository workerRepository;
    private final int leftLimit = 97; // letter 'a'
    private final int rightLimit = 122; // letter 'z'
    private final int targetStringLength = 70;

    private final Random random = new Random();

    @PostConstruct
    public void init(){
        if (workerRepository.findTop5000ByYyyymmddOrderByYyyymmddAscIdAsc("20250110").size()>0){
            return;
        }
        List<Worker> workers = new ArrayList<>();
        for(int i=10;i<=30;i++){
            String yyyymmdd = "202501" + i;
            for(int j=0;j<=100000;j++){
                String generatedString = random.ints(leftLimit, rightLimit + 1)
                        .limit(targetStringLength)
                        .collect(StringBuilder::new, StringBuilder::appendCodePoint, StringBuilder::append)
                        .toString();
                Worker worker = new Worker(yyyymmdd, (long) j,(i+j)%1000,generatedString);
                workers.add(worker);
                if (workers.size()==10000){
                    workerRepository.saveAll(workers);
                }
            }
        }
    }
}

random string : https://linkeverything.github.io/java/java-random-string/

통합테스트 코드

@SpringBootTest
@AutoConfigureMockMvc
class MyExcelControllerTest {

    private long startTime;
    @Autowired
    MockMvc mockMvc;

    @RepeatedTest(10)
    void testFileDownload() throws Exception {
        MockHttpServletResponse response = mockMvc.perform(get("/download"))
                .andExpect(status().isOk())
                .andExpect(header().string("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) // Adjust based on your file type
                .andExpect(header().exists("Content-Disposition"))
                .andReturn().getResponse();
    }

    @BeforeEach
    void init() {
        startTime = System.nanoTime();
    }

    @AfterEach
    void tearDown() {
        Runtime runtime = Runtime.getRuntime();

        long memoryUsed = (runtime.totalMemory() - runtime.freeMemory()) / (1024 * 1024);
        long totalMemory = runtime.totalMemory() / (1024 * 1024);
        long maxMemory = runtime.maxMemory() / (1024 * 1024);

        System.out.println("Used Memory: " + memoryUsed + " MB");
        System.out.println("Total Memory: " + totalMemory + " MB");
        System.out.println("Max Memory: " + maxMemory + " MB");
        long duration = System.nanoTime() - startTime;
        System.out.println(String.format("Test took %d ms.", duration / 1_000_000));
        System.out.println();
    }
}

 

결과

Used Memory: 186 MB
Total Memory: 304 MB
Max Memory: 7092 MB
Test took 3068 ms.

Used Memory: 189 MB
Total Memory: 516 MB
Max Memory: 7092 MB
Test took 2063 ms.

Used Memory: 380 MB
Total Memory: 708 MB
Max Memory: 7092 MB
Test took 1784 ms.

Used Memory: 399 MB
Total Memory: 708 MB
Max Memory: 7092 MB
Test took 1760 ms.

Used Memory: 711 MB
Total Memory: 864 MB
Max Memory: 7092 MB
Test took 1759 ms.

Used Memory: 433 MB
Total Memory: 864 MB
Max Memory: 7092 MB
Test took 1744 ms.

Used Memory: 645 MB
Total Memory: 864 MB
Max Memory: 7092 MB
Test took 1674 ms.

Used Memory: 735 MB
Total Memory: 1040 MB
Max Memory: 7092 MB
Test took 1734 ms.

Used Memory: 717 MB
Total Memory: 1040 MB
Max Memory: 7092 MB
Test took 1700 ms.

Used Memory: 710 MB
Total Memory: 1040 MB
Max Memory: 7092 MB
Test took 1650 ms.

2024-04-10T16:08:28.918+09:00  INFO 14164 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2024-04-10T16:08:28.920+09:00  INFO 14164 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2024-04-10T16:08:28.928+09:00  INFO 14164 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0

 

'개발업무 > 개발' 카테고리의 다른 글

Apache web server request body 로깅  (0) 2024.05.30
Apache Server 설치  (0) 2024.05.29
Spring 실행 윈도우 스크립트  (1) 2024.01.11
Nginx reverse proxy 설치 및 구성  (0) 2023.11.18
[Git] pull request view - diff  (0) 2023.11.15

'개발업무/개발'의 다른글

  • 현재글Java 대용량 엑셀 다운로드 (SXSSF)

관련글

댓글

티스토리툴바