✏️POI 란?
- Apache POI 는 아파치재단에서 만든 라이브러리로, 마이크로 오피스 파일 포멧을 순수 자바 언어로 읽고 쓰는 기능을 제공하는 라이브러리.
☕️ JDK 호환
참고 : https://poi.apache.org/devel/
필자는 JDK 1.7 버전을 사용했기 때문에 3.16 버전을 사용하였다.
- JDK 1.8 이상 버전은 poi 4.0 이상
- poi 3.11 과 3.x 버전은 JDK 1.6 이상
- poi 3.5 에서 3.10 버전은 JDK 1.5 이상
- poi 3.5 버전까지는 JDK 1.4 이상
✏️ lib 폴더에 .jar 파일 추가
- 위의 사진에 선택된 5개의 jar파일을 추가해 주었다.
- xmlbeans.jar와 commons-collections.jar 파일은 엑셀 작성을위해 워크북을 생성하던 중 (java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject) 오류가 발생하여 해결 하기 위해 추가해 주었다.
✏️ Project Structure에 라이브러리 등록
- Project Structure - Libaries 에서 + 버튼을 누르고 lib폴더를 등록해주었다.
✏️ 엑셀 파일 생성하기
⭐️ 핵심용어
-
workbook : 하나의 엑셀 파일
-
sheet : 엑셀파일(workbook)안에 있는 시트
-
row : 행(가로)
-
cell : 열(세로)
💻 코드
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.XSSFFont;
//엑셀저장
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
String strDownDate = sdf2.format(new Date());//현재날짜
String strDownExcelName = "엑셀 이름";
String strExcelName = "attachment; filename=" + strDownExcelName + "_" + strDownDate + ".xlsx";
OutputStream outputStream = response.getOutputStream(); // ⭐️중요!! 꼭 추가해주어야 한다. (작성하지 않으면 손상된 파일로 쓰기가 됨)
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", new String(strExcelName.getBytes("KS_C_5601-1987"), "8859_1"));
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 워크시트 생성
SXSSFSheet sheet = workbook.createSheet(strDownExcelName);
// 행 생성
SXSSFRow row = null;
// 열 생성
SXSSFCell cell = null;
// 셀 스타일
CellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 가로 가운데 정렬
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); // 세로 가운데 정렬
cellStyle1.setBorderTop(BorderStyle.THIN); // 상단 테두리 설정
cellStyle1.setBorderBottom(BorderStyle.THIN); // 하단 테두리 설정
cellStyle1.setBorderLeft(BorderStyle.THIN); // 좌측 테두리 설정
cellStyle1.setBorderRight(BorderStyle.THIN); // 우측 테두리 설정
// Font 객체 생성
XSSFFont font1 = (XSSFFont) workbook.createFont();
font1.setBold(true); // 폰트 굵게
// CellStyle에 Font 설정
cellStyle1.setFont(font1);
CellStyle data = workbook.createCellStyle();
data.setBorderTop(BorderStyle.THIN);
data.setBorderBottom(BorderStyle.THIN);
data.setBorderLeft(BorderStyle.THIN);
data.setBorderRight(BorderStyle.THIN);
data.setAlignment(HorizontalAlignment.RIGHT);
CellStyle data2 = workbook.createCellStyle();
data2.setBorderTop(BorderStyle.THIN);
data2.setBorderBottom(BorderStyle.THIN);
data2.setBorderLeft(BorderStyle.THIN);
data2.setBorderRight(BorderStyle.THIN);
data2.setAlignment(HorizontalAlignment.CENTER);
data2.setVerticalAlignment(VerticalAlignment.CENTER);
// 첫번째 컬럼의 너비를 지정해준다.
// 그냥 sheet.setColumWidth(0, 20) 을 하면 출력되지 않는다.
//POI에서는 엑셀에서 셀의 너비를 지정할 때 셀의 폭을 1/256 단위로 지정합니다. 이는 엑셀에서 셀의 폭을 1/256 단위로 표현하기 때문입니다.
//따라서, POI에서 셀의 너비를 지정할 때도 너비 값을 256으로 곱해야 합니다.
//예를 들어, sheet.setColumnWidth(colIndex, 20)과 같이 셀의 너비를 20으로 지정하면, 엑셀에서는 실제로 폭이 약 3.14 정도로 지정됩니다.
//이를 정확하게 20으로 지정하려면, sheet.setColumnWidth(colIndex, 20 * 256)과 같이 너비 값을 256으로 곱해주어야 합니다. 이 경우, 엑셀에서 셀의 폭이 20으로 정확하게 지정됩니다.
sheet.setColumnWidth(0, 20*256);
CellRangeAddress region = new CellRangeAddress(0,1,0,0); // 셀 병합
sheet.addMergedRegion(region);
row = sheet.getRow(0);
if (row == null) {
row = sheet.createRow(0);
}
// 기본 너비를 sheet.getDefaultRowHeght() 로 가져올 수 있다. 거기에 4배를 해준 값
// 따라서 기본 셀 높이의 4배로 높이를 지정한 것
row.setHeight((short) (4 * sheet.getDefaultRowHeight()));
cell = row.createCell(0);
cell.setCellValue("상태");
cell.setCellStyle(cellStyle1);
int startRow = 0; // 셀 데이터를 쓸 시작 행
int startCol = 1; // 셀 병합을 시작할 시작 열
int endCol = startCol + 2; // 셀 병합을 시작할 끝 열
for (int i = 0; i < selectedCallCenterAgentList.size(); i++) {
// 1행 병합
CellRangeAddress merge = new CellRangeAddress(startRow, startRow, startCol, endCol);
sheet.addMergedRegion(merge);
row = sheet.getRow(0);
if (row == null) {
row = sheet.createRow(0);
}
row.setHeight((short) (2 * sheet.getDefaultRowHeight()));
cell = row.createCell(startCol);
cell.setCellValue(((CallCenterAgentDTO)selectedCallCenterAgentList.get(i)).getAgtName());
cell.setCellStyle(cellStyle1);
row = sheet.getRow(1);
if (row == null) {
row = sheet.createRow(1);
}
row.setHeight((short) (2 * sheet.getDefaultRowHeight()));
cell = row.createCell(startCol);
cell.setCellValue("수");
cell.setCellStyle(cellStyle1);
cell = row.createCell(startCol+1);
cell.setCellValue("총시간");
cell.setCellStyle(cellStyle1);
cell = row.createCell(startCol+2);
cell.setCellValue("평균시간");
cell.setCellStyle(cellStyle1);
startCol = endCol + 1; // 다음 병합을 시작할 시작 열로 이동
endCol = startCol + 2; // 다음 병합을 시작할 끝 열로 이동
}
double n = 0;
double n2 = 0;
for (int i = 0; i < allCodeList.size(); i++) {
row = sheet.getRow(i+2);
if (row == null) {
row = sheet.createRow(i+2);
}
cell = row.createCell(0);
cell.setCellValue(aStatistics[i][1]);
cell.setCellStyle(data2);
for (int j = 0; j < selectedCallCenterAgentList.size(); j++) {
n = Double.parseDouble(aStatistics[i][(j*3)+3]);
n2 = Double.parseDouble(aStatistics[i][(j*3)+4]);
row = sheet.getRow(i+2);
if (row == null) {
row = sheet.createRow(i+2);
}
cell = row.createCell((j*3)+1);
cell.setCellValue(Double.parseDouble(aStatistics[i][(j*3)+2]));
cell.setCellStyle(data);
cell = row.createCell((j*3)+2);
cell.setCellValue(parseN(n));
cell.setCellStyle(data);
cell = row.createCell((j*3)+3);
cell.setCellValue(parseN(n2));
cell.setCellStyle(data);
}
}
workbook.write(outputStream);
outputStream.close();
workbook.dispose();parseN 메소드
// double 형태로 들어오는 초(second)를 h:mm:ss 형식으로 바꿔주는 메서드
public String parseN(double n){
String result = "";
if (n == 0){
result = "0:00:00";
} else if(n > 0){
int hours = (int) (n / 3600);
int minutes = (int) ((n % 3600) / 60);
int seconds = (int) (n % 60);
result = String.format("%d:%02d:%02d", hours, minutes, seconds);
}
return result;
}⭐️ 코드 해석(poi)사용법
// 워크북(엑셀) 생성
SXSSFWorkbook workbook = new SXSSFWorkbook();
// 워크시트 생성
SXSSFSheet sheet = workbook.createSheet(strDownExcelName);
// 행 생성
SXSSFRow row = null;
// 열 생성
SXSSFCell cell = null;워크북을 생성한다. 여기서 워크시트 안의()에는 시트의 이름을 넣어준다.
row = sheet.getRow(0);
if (row == null) {
row = sheet.createRow(0);
}1번째 로우(행 - 가로)을 선택하고 null일경우 행을 만들어 준다.
sheet.createRow(rowNum)생성할 로우의 행의 값을 넣어준다.
cell = row.createCell(0);
cell.setCellValue("상태");
cell.setCellStyle(cellStyle1);선택한 row에 1번째 cell(열 - 세로)에 "상태"라는 값을 넣고, cellStyle1을 적용한다.
row.createCell(colNum)위에서 선택한 로우에서 값을 넣고싶은 열의 위치를 넣는다
//예제 java에서는 0번부터 시작하므로 헷갈리지 않게 유의하자
row = sheet.createRow(3);
cell = row.createCell(4);
cell.setCellValue("4행 5열에 값넣기");위의 예제는 4행의 5열에 값을 넣는 예제이다. (가로 4번째 줄의 세로 5번째)
// 셀 병합 new CellRangeAddress(fist Row, last Row, first Col, last Col)
CellRangeAddress region = new CellRangeAddress(0,1,0,0);
sheet.addMergedRegion(region);1번째 Row의 1번째 셀부터 2번째 Row의 1번째 셀을 병합한 것이다. (세로 첫번째 줄(cell = 0) 위에서부터 2칸 병합)
// 2행 2열부터 5행 5열까지의 범위를 정의합니다.
CellRangeAddress region = new CellRangeAddress(1, 4, 1, 4);
sheet.addMergedRegion(region);
// (2,2)부터 (5,5)까지의 셀을 병합이부분이 많이 헷갈릴 수 있으니 실제로 해보는 것 추천
위의 코드에 주석을 달아놨으니 주석부분도 확인할 것.
2022.11.01 💻~ing