๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
๊ฐœ๋ฐœ/Java

[Spring] ์Šคํ”„๋ง ์—‘์…€ POI ์Šคํƒ€์ผ, ํฐํŠธ ์ ์šฉ(POI ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ 2ํŽธ) :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2019. 4. 28.

POI ์…€ ์Šคํƒ€์ผ, ํฐํŠธ ์ ์šฉ

POI ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์‚ฌ์šฉ 1ํŽธ์—์„œ ์—‘์…€ํŒŒ์ผ์„ ๋งŒ๋“ค์–ด ๋‹ค์šด๋กœ๋“œ ๊ทธ๋ฆฌ๊ณ  ์—…๋กœ๋“œํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๋ฐ›์•„์˜ค๋Š” ์ž‘์—…์„ ์ง„ํ–‰ํ–ˆ๋‹ค.

[๊ฐœ๋ฐœ/Spring] - [Spring] ์Šคํ”„๋ง ์—‘์…€ํŒŒ์ผ ์—…๋กœ๋“œ ๋‹ค์šด๋กœ๋“œ ๊ธฐ๋ณธ(POI ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ 1ํŽธ) :: ๋งˆ์ด์ž๋ชฝ

์Šคํƒ€์ผ ๋ฐ ํฐํŠธ์ ์šฉ์€ ์œ„ ๊ธ€์—์„œ ์ด์–ด์ง€๋Š” ๋‚ด์šฉ์ด๋ฏ€๋กœ ์•„์ง ์—‘์…€ ๋‹ค์šด๋กœ๋“œ ๊ธฐ๋Šฅ์ด ์™„๋ฃŒ๋˜์ง€ ์•Š์•˜๋‹ค๋ฉด ํ•ด๋‹น ๊ธ€์„ ํ™•์ธํ•˜์—ฌ ์‹ค์Šต์„ ์ง„ํ–‰ํ•œ๋‹ค.

 

์ด๋ฒˆ๊ธ€์—์„œ๋Š” ์—‘์…€ ๋‹ค์šด๋กœ๋“œ๋ฅผ ๋ฐ›์„๋•Œ ์Šคํƒ€์ผ๋ง๊ณผ ํฐํŠธ๋ฅผ ์„ค์ •ํ•ด์„œ ๋ฐ›์„์ˆ˜ ์žˆ๊ฒŒ POI๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์—์„œ ์„ค์ •ํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์‹ค์Šต์„ ํ†ตํ•ด ์ฝ”๋“œ๋ฅผ ์ž‘์„ฑํ•ด๋ณผ๊ฒƒ์ด๋‹ค. ํ•ด๋‹น ์‹ค์Šต์€ POI๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ 4.0.0 ๋ฒ„์ „์„ ์‚ฌ์šฉํ–ˆ๋‹ค.

 

์—‘์…€ ๋‹ค์šด๋กœ๋“œ ์ตœ์ข… ๊ฒฐ๊ณผ๋ฌผ

์ด๋ฒˆ ์‹ค์Šต์„ ํ†ตํ•ด ๋ณ‘ํ•ฉ, ์…€ ์Šคํƒ€์ผ๋ง, ํฐํŠธ๋ฅผ ์ˆ˜์ •ํ•ด์„œ ์•„๋ž˜์™€ ๊ฐ™์€ ์—‘์…€ํŒŒ์ผ์„ ๋งŒ๋“ค์–ด๋ณผ๋ ค๊ณ ํ•œ๋‹ค.

 

 

SXSSFWorkbook ์›Œํฌ๋ถ ์ƒ์„ฑ

1ํŽธ์˜ ๊ฐ„๋žตํ•œ ๋‚ด์šฉ์€ POI ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์˜ SXSSFWorkbook ๊ฐ์ฒด๋ฅผ OutpuStream์„ ํ†ตํ•ด ํŒŒ์ผ๋กœ ๋‚ด๋ณด๋‚ด๋Š” ๊ฒƒ์ด๋‹ค.
์šฐ์„  ์›Œํฌ๋ถ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•ด์ฃผ๊ณ  ์›Œํฌ๋ถ ๋‚ด์— ์‹œํŠธ๋ฅผ ์ƒ์„ฑํ•œ๋‹ค.
์—ด ํญ ์„ธํŒ…์„ ์œ„ํ•ด ์‹œํŠธ์˜ setColumWidth(ํ•ด๋‹น ์—ด, ํญ) ๋ฉ”์†Œ๋“œ๋ฅผ ์ด์šฉํ•œ๋‹ค.
SXSSFWorkbook workbook = new SXSSFWorkbook();
        
// ์‹œํŠธ ์ƒ์„ฑ
SXSSFSheet sheet = workbook.createSheet("๊ณผ์ผํ‘œ");
        
// ์—ด ํญ ์ˆ˜์ •
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 7000);
sheet.setColumnWidth(5, 8000);
sheet.setColumnWidth(6, 6000);
 

 

CellStyle ์ •๋ ฌ Alignment

CellStyle ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜์—ฌ Alignment ์„ธํŒ…ํ•˜๋Š” ํ•จ์ˆ˜๋ฅผ ํ˜ธ์ถœํ•ด์„œ ์ธ์ž๊ฐ’์„ ๋„ฃ์–ด์ค€๋‹ค.
POI๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์Šคํƒ€์ผ ๋ฐ ํฐํŠธ ๊ทธ๋ฆฌ๊ณ  ๊ธฐํƒ€ ๋‹ค๋ฅธ ์„ค์ •๊ฐ’์„ ์„ธํŒ…ํ• ๋•Œ POI๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ๋‚ด์— Enum์„ ์‚ฌ์šฉํ•œ๋‹ค.
์•„๋ž˜๋Š” Horizontal Alignment(๊ฐ€๋กœ)์™€ VerticalAlignment(์„ธ๋กœ)๋ฅผ ๋ชจ๋‘ ๊ฐ€์šด๋ฐ ์ •๋ ฌ ์‹œ์ผฐ๋‹ค.
CellStyle mergeRowStyle1 = workbook.createCellStyle();
mergeRowStyle1.setAlignment(HorizontalAlignment.CENTER);
mergeRowStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
 

 

 

CellStyle ํ…Œ๋‘๋ฆฌ Border

ํ…Œ๋‘๋ฆฌ๋Š” ๊ฐ ์…€๋งˆ๋‹ค ์ƒํ•˜์ขŒ์šฐ ๋ชจ๋‘ ์„ค์ •ํ•ด์ค€๋‹ค.

setBorderTop, Bottom, Left, Right ๋ฉ”์†Œ๋“œ์™€ ์ธ์ž๋กœ POi๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์˜ BorderStyle ์ธ์ž๋ฅผ ๋„ฃ์–ด์„œ ์ ์šฉํ•œ๋‹ค.

CellStyle mergeRowStyle1 = workbook.createCellStyle();
mergeRowStyle1.setBorderTop(BorderStyle.THICK);
mergeRowStyle1.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT_DOT);
 

 

 

CellStyle ๋ฐฐ๊ฒฝ์ƒ‰ ForegroundColor

๋ฐฐ๊ฒฝ์ƒ‰์„ ์ž…ํžˆ๋Š”๋ฐ ๋‘๊ฐ€์ง€ ๋ฐฉ๋ฒ•์ด ์žˆ๋‹ค. 
-ํ•˜๋‚˜๋Š” POI๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์— ๋‚ด์žฌ๋˜์–ด ์žˆ๋Š” Enum์„ ์‚ฌ์šฉํ•ด์„œ ์ฃผ์–ด์ง„ ๋Œ€ํ‘œ ์ƒ‰์„ ์ž…ํžˆ๋Š” ๊ฒƒ.
-๋‘๋ฒˆ์งธ๋Š”  XSSFColor ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•ด์„œ rgb ์ปฌ๋Ÿฌ๋ฅผ ์‚ฌ์šฉํ•œ๋Š”๊ฒƒ

 

ForegroundColor ์ฃผ์–ด์ง„ ๋Œ€ํ‘œ์ƒ‰

setFillForegroundColor ๋ฉ”์†Œ๋“œ์— IndexedColors  Enum์ธ์ž๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค.
setFillPattern์€ ํ•ด๋‹น ์ƒ‰์„ ์–ด๋–ค ํŒจํ„ด์œผ๋กœ ์ž…ํž์ง€๋ฅผ ์ •ํ•œ๋‹ค.
CellStyle mergeRowStyle1 = workbook.createCellStyle();
mergeRowStyle1.setFillForegroundColor(IndexedColors.AQUA.getIndex());
mergeRowStyle1.setFillPattern(FillPatternType.BRICKS);
 

 

 

ForegroundColor rgb ์ปฌ๋Ÿฌ

rgb์ปฌ๋Ÿฌ๋ฅผ ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š”  ๊ธฐ์กด CellStyle์ด ์•„๋‹Œ XSSFCellStyle์„ ์‚ฌ์šฉํ•ด์ค€๋‹ค.
๋˜‘๊ฐ™์ด setFillForegroundColor ๋ฉ”์†Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š”๋ฐ ์ธ์ž๋กœ XSSFColor ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•ด์„œ ๋งŒ๋“ค์–ด์ค€๋‹ค.
XSSFColor ๊ฐ์ฒด์—๋Š” ๋ฐ”์ดํŠธ ๋ฐฐ์—ดํ˜•ํƒœ๋กœ rgb ์ˆ˜์น˜๋ฅผ ๋„ฃ์–ด์ค€๋‹ค.
XSSFCellStyle mergeRowStyle2 = (XSSFCellStyle) workbook.createCellStyle();
mergeRowStyle2.setFillForegroundColor(new XSSFColor(new byte[] {(byte) 192,(byte) 192,(byte) 192}, null));
mergeRowStyle2.setFillPattern(FillPatternType.FINE_DOTS);
 

 

 

 

 Cell Font ํฐํŠธ ์„ค์ •

ํฐํŠธ ์ ์šฉ์„ ์œ„ํ•ด POI ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์˜ Font ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•ด์ค€๋‹ค.
ํ•ด๋‹น ๊ฐ์ฒด์˜ ์„ธํ„ฐ๋ฅผ ์‚ฌ์šฉํ•ด ํฐํŠธ๋ฅผ ์„ค์ •ํ•ด์ค€๋‹ค. ๋Œ€ํ‘œ์ ์œผ๋กœ ๊ธ€์”จ์ฒด, ํฌ๊ธฐ, ์ƒ‰์ƒ, ๊ตต๊ธฐ๋งŒ ์„ค์ •ํ–ˆ๋‹ค.
์ดํ›„ CellStyle์˜ setFont ๋ฉ”์†Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•ด ์ธ์ž๋กœ ํฐํŠธ๋ฅผ ๋„ฃ์–ด์ค€๋‹ค.
Font headerFont = workbook.createFont();
headerFont.setFontName("๋‚˜๋ˆ”๊ณ ๋”•");
headerFont.setFontHeight((short)1000);
headerFont.setColor(IndexedColors.GREEN.getIndex());
headerFont.setBold(true);
        
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
 

 

 

 

Cell Merge ์…€ ๋ณ‘ํ•ฉ

์…€๋ณ‘ํ•ฉ์€ ์‹œํŠธ์˜ addMergeRegion ๋ฉ”์†Œ๋“œ์— CellRangeAddress ๊ฐ์ฒด๋ฅผ ์ธ์ž๋กœ ํ•˜์—ฌ ๋ณ‘ํ•ฉ์‹œํ‚จ๋‹ค.
CellRangeAddress ์ƒ์„ฑ์ž์˜ ์ธ์ž๋กœ(์‹œ์ž‘ ํ–‰, ๋ ํ–‰, ์‹œ์ž‘ ์—ด, ๋ ์—ด) ์ˆœ์„œ๋Œ€๋กœ ๋„ฃ์–ด์„œ ๋ณ‘ํ•ฉ์‹œํ‚ฌ ๋ฒ”์œ„๋ฅผ ์ •ํ•œ๋‹ค. ๋ฐฐ์—ด์—์„œ์™€ ๊ฐ™์ด ์‹œ์ž‘์€ 0๋ถ€ํ„ฐ์ด๋‹ค.
์…€ ๋ณ‘ํ•ฉํ• ๋•Œ ์กฐ๊ธˆ์€ ๊ท€์ฐฎ์ง€๋งŒ ์Šคํƒ€์ผ์ด ๋ฐ”๋กœ ๋‹ค ์ ์šฉ๋˜์ง€ ์•Š๋Š”๋‹ค. ๋ณ‘ํ•ฉ๋˜๋”๋ผ๋„ ์…€์˜ ์œ„์น˜๋งŒ ๋”ฑ ์ ์šฉ๋˜์–ด for๋ฌธ์„ ์‚ฌ์šฉํ•ด์„œ  ๋ณ‘ํ•ฉ๋œ ๋ชจ๋“  ์œ„์น˜์˜ ์…€์Šคํƒ€์ผ์„ ๋ชจ๋‘ ์ ์šฉํ•ด์ฃผ์ž. ์ฃผ์˜์‚ฌํ•ญ์œผ๋กœ ์…€์˜ ๋‚ด์šฉ์€ ์™ผ์ชฝ ๊ฐ€์žฅ ์ƒ๋‹จ ์…€์˜ ๋‚ด์šฉ์ด ๋ณด์—ฌ์ง€๋ฏ€๋กœ for๋ฌธ์„ ์‚ฌ์šฉํ• ๋•Œ ์ฃผ์˜ํ•ด์ฃผ์ž. ํ•„์ž๋Š” ๊ท€์ฐฎ์•„์„œ ๊ทธ๋ƒฅ for๋ฌธ์— ์ถ”๊ฐ€ํ•ด๋ฒ„๋ฆผ.
// ํ–‰ ์ถ”์ ์„ ์œ„ํ•œ ๋ณ€
int rowLocation = 0;
        
Row row = null;
Cell cell = null;
        
// ๋ณ‘ํ•ฉ ํ–‰
row = sheet.createRow(++rowLocation);
        
// ๊ณผ์ผ์žฅํ‘œ ๋ณ‘ํ•ฉ ์ž‘์—…
for(int i=1; i<3; i++) {
    cell = row.createCell(i);
    cell.setCellStyle(mergeRowStyle1);
    cell.setCellValue("๊ณผ์ผ์žฅํ‘œ");
}
sheet.addMergedRegion(new CellRangeAddress(1, (list.size() + 1 + 1), 1, 2)); // ํ–‰์‹œ์ž‘, ํ–‰๋, ์—ด์‹œ์ž‘, ์—ด๋
        
// ๊ณผ์ผ ๋ชฉ๋ก ๋ณ‘ํ•ฉ ์ž‘์—…
for(int i=3; i<7; i++) {
    cell = row.createCell(i);
    cell.setCellStyle(mergeRowStyle2);
    cell.setCellValue("๊ณผ์ผ ๋ชฉ๋ก");
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 6));
 

 

 

์ตœ์ข…๊ฒฐ๊ณผ๋ฌผ ์ „์ฒด์ฝ”๋“œ

์›Œํฌ๋ถ์„ ๋งŒ๋“œ๋Š” ๋ฉ”์†Œ๋“œ์ด๋‹ค.
ํ•ด๋‹น ๋ฉ”์†Œ๋“œ๋ฅผ ๋‹ค์šด๋ฐ›๋Š” ๊ณผ์ •์€ ์ด์ „ 1ํŽธ์˜ ๋‚ด์šฉ์„ ํ™•์ธํ•˜์ž.
public SXSSFWorkbook makeSimpleFruitExcelWorkbookWithStyles(List<Fruit> list) {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
        
    // ์‹œํŠธ ์ƒ์„ฑ
    SXSSFSheet sheet = workbook.createSheet("๊ณผ์ผํ‘œ");
        
    // ์—ด ํญ ์ˆ˜์ •
    sheet.setColumnWidth(3, 6000);
    sheet.setColumnWidth(4, 7000);
    sheet.setColumnWidth(5, 8000);
    sheet.setColumnWidth(6, 6000);
    
    // ๊ณผ์ผ์žฅํ‘œ ์Šคํƒ€์ผ
    CellStyle mergeRowStyle1 = workbook.createCellStyle();
    mergeRowStyle1.setAlignment(HorizontalAlignment.CENTER);
    mergeRowStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
    mergeRowStyle1.setBorderTop(BorderStyle.THICK);
    mergeRowStyle1.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT_DOT);
    mergeRowStyle1.setFillForegroundColor(IndexedColors.AQUA.getIndex());
    mergeRowStyle1.setFillPattern(FillPatternType.BRICKS);

    XSSFCellStyle mergeRowStyle2 = (XSSFCellStyle) workbook.createCellStyle();
    mergeRowStyle2.setAlignment(HorizontalAlignment.CENTER);
    mergeRowStyle2.setVerticalAlignment(VerticalAlignment.TOP);
    mergeRowStyle2.setBorderTop(BorderStyle.THIN);
    mergeRowStyle2.setBorderLeft(BorderStyle.DOTTED);
    mergeRowStyle2.setBorderBottom(BorderStyle.SLANTED_DASH_DOT);
    mergeRowStyle2.setBorderRight(BorderStyle.HAIR);
    mergeRowStyle2.setFillForegroundColor(new XSSFColor(new byte[] {(byte) 192,(byte) 192,(byte) 192}, null));
    mergeRowStyle2.setFillPattern(FillPatternType.FINE_DOTS);
        
    Font headerFont = workbook.createFont();
    headerFont.setFontName("๋‚˜๋ˆ”๊ณ ๋”•");
    headerFont.setFontHeight((short)1000);
    headerFont.setColor(IndexedColors.GREEN.getIndex());
    headerFont.setBold(true);
        
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setAlignment(HorizontalAlignment.LEFT);
    headerStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
    headerStyle.setBorderLeft(BorderStyle.DASH_DOT);
    headerStyle.setBorderBottom(BorderStyle.MEDIUM);
    headerStyle.setBorderRight(BorderStyle.MEDIUM_DASH_DOT);
    headerStyle.setFont(headerFont);
        
        
    CellStyle bodyStyle = workbook.createCellStyle();
    bodyStyle.setAlignment(HorizontalAlignment.CENTER);
    bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    bodyStyle.setBorderTop(BorderStyle.THIN);
    bodyStyle.setBorderBottom(BorderStyle.THIN);
    bodyStyle.setBorderLeft(BorderStyle.THIN);
    bodyStyle.setBorderRight(BorderStyle.THIN);
        
    // ํ–‰ ์ถ”์ ์„ ์œ„ํ•œ ๋ณ€
    int rowLocation = 0;
    
    Row row = null;
    Cell cell = null;
        
    // ๋ณ‘ํ•ฉ ํ–‰
    row = sheet.createRow(++rowLocation);
        
    // ๊ณผ์ผ์žฅํ‘œ ๋ณ‘ํ•ฉ ์ž‘์—…
    for(int i=1; i<3; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(mergeRowStyle1);
        cell.setCellValue("๊ณผ์ผ์žฅํ‘œ");
    }
    sheet.addMergedRegion(new CellRangeAddress(1, (list.size() + 1 + 1), 1, 2)); // ํ–‰์‹œ์ž‘, ํ–‰๋, ์—ด์‹œ์ž‘, ์—ด๋
        
    // ๊ณผ์ผ ๋ชฉ๋ก ๋ณ‘ํ•ฉ ์ž‘์—…
    for(int i=3; i<7; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(mergeRowStyle2);
        cell.setCellValue("๊ณผ์ผ ๋ชฉ๋ก");
    }
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 6));
        
        
    // ํ—ค๋” ํ–‰
    row = sheet.createRow(++rowLocation);
    cell = row.createCell(3);
    cell.setCellValue("๋ฒˆํ˜ธ");
    cell.setCellStyle(headerStyle);
    cell = row.createCell(4);
    cell.setCellValue("์ด๋ฆ„");
    cell.setCellStyle(headerStyle);
    cell = row.createCell(5);
    cell.setCellValue("๊ฐ€๊ฒฉ");
    cell.setCellStyle(headerStyle);
    cell = row.createCell(6);
    cell.setCellValue("์ˆ˜๋Ÿ‰");
    cell.setCellStyle(headerStyle);
        
    // ๋‚ด์šฉ ํ–‰
    for(int i=0; i<list.size(); i++) {
        Fruit fruit = list.get(i);
        // ํ–‰ ์ƒ์„ฑ
        row = sheet.createRow(++rowLocation);
        // ๋ฐ์ดํ„ฐ ๋ฒˆํ˜ธ ํ‘œ์‹œ
        cell = row.createCell(3);
        cell.setCellValue(i + 1);
        cell.setCellStyle(bodyStyle);
        // ๋ฐ์ดํ„ฐ ์ด๋ฆ„ ํ‘œ์‹œ
        cell = row.createCell(4);
        cell.setCellValue(fruit.getName());
        cell.setCellStyle(bodyStyle);
        // ๋ฐ์ดํ„ฐ ๊ฐ€๊ฒฉ ํ‘œ์‹œ
        cell = row.createCell(5);
        cell.setCellValue(fruit.getPrice());
        cell.setCellStyle(bodyStyle);
        // ๋ฐ์ดํ„ฐ ์ˆ˜๋Ÿ‰ ํ‘œ์‹œ
        cell = row.createCell(6);
        cell.setCellValue(fruit.getQuantity());
        cell.setCellStyle(bodyStyle);
    }
    return workbook;
}
 

 

๋Œ“๊ธ€