java excel 처리 정리

매번 엑셀을 java로 읽을때마다 찾아보는게 귀찮아서 정리한다.

대충 블로그 짤방

사용 모듈 apache poi

jar 리스트

- poi-3.11.jar

- poi-ooxml-3.11.jar

- poi-ooxml-schemas-3.11.jar

- xmlbeans-2.6.0.jar

 

1. 엑셀 파일 열기 & 닫기

String excel_filePath = "test.xlsx"; // 엑셀 파일 경로 지정
FileOutputStream fos = new FileOutputStream(excel_filePath); // FileOutputStream 으로 불러오기
XSSFWorkbook workbook = new XSSFWorkbook(); // 엑셀 쓰기전 workbook 지정

// do excel something

workbook.write(fos); // 작업이 끝난후 해당 workbook객체를 FileOutputStream에 쓰기
fos.close(); // FileOutputStream 닫기

 

2. 엑셀 파일 쓰기

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("test");
XSSFRow curRow;

curRow = sheet.createRow(0); // row 생성 -> 행 먼저 지정
// cell 값 채우기 -> 행, 열 값을 통해 특정 cell에 값 채워 넣기
curRow.createCell(0).setCellValue("test value 1"); 

 

3. 특정 범위 cell merge(합치기)

sheet.addMergedRegion(new CellRangeAddress(prev_text_index,rowIndex-1,0,0)); 
//행시작, 행종료, 열시작, 열종료 (0부터 시작)
// 본인의 경우 특정 행들을 서로 결합시켜야 해서 앞부분 rowIndex 쪽을 변경시켜 사용했다.

 

4. 특정 cell 값 읽기

FileReader fr = new FileReader(filepath);
FileInputStream file = new FileInputStream(filepath);
XSSFWorkbook workbook = new XSSFWorkbook(file);

XSSFSheet sheet=workbook.getSheetAt(0); // 0번째 시트 가져오기
XSSFRow row = sheet.getRow(row_index); // row_index 값으로 행 값 조회
XSSFCell cell = row.getCell(cell_index); // cell_index 값으로 열 값 조회

//행, 열로 해당 cell 접근해 cell값 가져오기
String value = cell.toString();

 

 

5. merge된 cell 값 가져오기

한 cell 씩 조회해서 가져오는데 해당 cell이 merge되어 있다면 ""로 가져오는 문제가 발생했다.
구글에 "java excel merge cell read" 같은 키워드로 검색해 나온 결과를 정리 해본다.

- 순서
    1. 원하는 cell이 merge cell인지 아닌지 검사
    2. 해당 cell의 값을 가져오기

 

// XSSFSheet sheet : 값을 조회하려는 sheet 
// int row : 원하는 cell의 행(row) index
// int column : 원하는 cell의 열(column) index

public int isMergedRegion(XSSFSheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions(); // 값을 가져오려는 sheet의 모든 merge cell값 개수 조회 
    for (int i = 0; i < sheetMergeCount; i++) { // merge cell 개수 만큼 반복
        CellRangeAddress range = sheet.getMergedRegion(i); // sheet의 mergecell 을 전부 조사
        // merge cell의 영역을 조사
        int firstColumn = range.getFirstColumn(); 
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();

        //원하는 cell이 merge cell 영역안에 있다면 merge된 cell이라고 판단
        if (row >= firstRow && row <= lastRow) {
            if (column >= firstColumn && column <= lastColumn) {
                return i; // merge cell index를 반환
            }
        }
    }
    return -1; // merge cell이 아니라면 index 값이 될 수 없는 -1 반환
}

String value = "";
int mergedCellIndex = er.isMergedRegion(sheet, rowindex, cell.getColumnIndex());

if(mergedCellIndex != -1){ // 만약 원하는 cell이 merge된 cell이라면
    CellRangeAddress ca = sheet.getMergedRegion(mergedCellIndex); //반환된 mergeCell index로 값조회
    int firstRow = ca.getFirstRow();
    int firstColumn = ca.getFirstColumn();
    XSSFRow fRow = sheet.getRow(firstRow);
    XSSFCell fCell = fRow.getCell(firstColumn);
    value = cell.toString();
}else{
    value = cell.toString();
}

 

6. style 변경

 

- 폰트 변경

XSSFCellStyle style = workbook.createCellStyle();

XSSFFont boldFont = workbook.createFont(); //폰트 객체 생성
boldFont.setFontName("맑은 고딕"); // 글자 체 변경
boldFont.setFontHeight((short)10*1); // 글자 크기 조정
boldFont.setBold(true); // 굵게
style.setFont(boldFont);

- 글자 위치 변경

XSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER); // 가운데 정렬 (가로 기준)
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 중앙 정렬 (세로 기준)

- cell 외곽선 변경

XSSFCellStyle style = workbook.createCellStyle();

style.setBorderRight(HSSFCellStyle.BORDER_THIN); //우
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //좌
style.setBorderTop(HSSFCellStyle.BORDER_THIN); //위
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //아래

- cell 배경색 변경

XSSFCellStyle style = workbook.createCellStyle();

style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 노란색으로 변경
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

//배경색을 rgb 코드로 변경하고 싶다면
XSSFColor myColor_header = new XSSFColor(new java.awt.Color(218, 238, 243));
style.setFillForegroundColor(myColor_header);

 

해당 기능들만 사용하였기에 정리해둔다.

 

*******************************
2021-04-20 추가

1. merge된 셀의 경우

    - 병합 이후 값을 쓰면 병합 셀의 개수가 1개로 잡히지 않는다.
    - 병합셀에 기존과 같이 스타일을 적용하면 스타일을 적용한 특정 셀만 스타일이 적용된다.
        - 경계선의 경우 선이 이상하게 출력됌

 

//위 문제점 기록 및 병합셀의 경계선 그리기 코드
curRow = sheet.createRow(1);
curRow.createCell(1).setCellValue("날짜"); // 임의 값
curRow.getCell(1).setCellStyle(header_style); // 임의의 스타일

//2행의 2열부터 12열까지 병합
CellRangeAddress date_range = new CellRangeAddress(1,1,1,11);// 행시작, 행끝, 열시작, 열끝
sheet.addMergedRegion(date_range);

RegionUtil.setBorderTop(CellStyle.BORDER_THIN, date_range, sheet, workbook);
RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, date_range, sheet, workbook);
RegionUtil.setBorderRight(CellStyle.BORDER_THIN, date_range, sheet, workbook);
RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, date_range, sheet, workbook);

 

*******************************

2021-04-23 추가

1. 텍스트 값이 cell 범위를 삐져나가 보기 안좋은 경우가 발생

 

// cell 범위 내에서만 표시 되게끔 style 객체에 명시
header_style.setWrapText(true);

 

*******************************

2022-10-04 추가

1. 링크 (String) 값을 넣을때 해당 셀은 HyperLink로 만들기

import org.apache.poi.common.usermodel.HyperlinkType;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    //import org.apache.poi.ss.usermodel.Hyperlink;

    public class testExample {
        public static void main(String[] args) {
            String link = "https://www.google.com";
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("sheet1");
            HSSFRow row = null;
            HSSFCell cell = null;

            row = sheet.createRow(0);
            cell = sheet.createCell(0);
            cell.setCellValue(link);
            Hyperlink link_article = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
            link_article.setAddress(articleLink);
            cell.setHyperlink(link_article);

            //저장은 위 참고
        }
    }

 

*******************************

2023-04-02 추가

 

1. column, row의 width(너비) 를 조절

// column 너비 조절
sheet.setColumnWidth(0, 2000);

// row 너비 조절
row.setHeight(2000);

 

 

2. 엑셀 칸에 적히는 글자를 bold(굵게) 만들기

Font font = workbook.createFont();
font.setBold(true);

CellStyle style = workbook.createCellStyle();
style.setFont(font);

HSSFRow row = null;
HSSFCell cell = null;

row = sheet.getRow(0);
cell = row.getCell(0);

cell.setValue("test");
cell.setStyle(style);