[Spring Boot]Apache POI를 이용한 엑셀 다루기
엑셀, 워드와 같은 Office 포맷을 다루고 싶다면 Apache POI를 이용할 수 있습니다. 오늘 포스팅에서는 Office 중 엑셀을 자바 언어로 쓰는 방법을 소개합니다. 구글에 많은 자료가 있으니 자료 중 하나로 참고해주세요.
Apache POI
아파치 재단에서 제공하는 라이브러리로서 엑셀(.xls, .xlsx) 워드(.doc, .docx), 파워포인트 등과 같은 Office 파일 포맷을 자바 언어로 읽고 쓰기가 가능하도록 지원해줍니다.
그럼 이제 Apache POI를 이용해서 어떻게 엑셀을 쓸 수 있는지 알아보겠습니다.
먼저 pom.xml에 Apache POI를 추가합니다. 비교적 최신 버전 중 가장 사용량이 많아보인 4.1.2 버전으로 테스트했습니다.
pom.xml
<!-- Apache POI -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
테스트 시나리오는 다음과 같습니다.
1. 웹에서 데이터 입력
2. 웹에서 입력된 데이터가 엑셀 샘플 파일에 저장
3. 저장된 엑셀 파일 다운로드
테스트를 위해 샘플 파일과 페이지 레이아웃을 잡았습니다.
샘플 파일에는 기본과 총액에 숫자 서식을 잡아두었습니다.
test2.jsp
<script>
$(document).ready(function() {
$("#testForm").submit(function(event) {
var url = $("#testForm").attr("action");
var data = $("#testForm").serialize();
$.post(url, data).done();
});
});
</script>
</head>
<body>
<form:form id="testForm" action="/board/goToExcel.do" method="post">
<div class="container">
<div class="row border-bottom mb-3">
<div class="col-12">
<p class="h1">Apache POI Excel Example</p>
</div>
</div>
<div class="row">
<div class="col-4">
<div class="input-group mb-3">
<div class="input-group-prepend">
<span class="input-group-text">성명</span>
</div>
<input type="text" class="form-control" id="name" name="name">
</div>
<div class="input-group mb-3">
<div class="input-group-prepend">
<span class="input-group-text">메일</span>
</div>
<input type="text" class="form-control" id="email" name="email">
</div>
</div>
<div class="col-4">
<div class="input-group">
<div class="input-group-prepend">
<span class="input-group-text">인원</span>
</div>
<input type="text" class="form-control" id="persons" name="persons">
</div>
</div>
<div class="col-4">
<div class="input-group">
<div class="input-group-prepend">
<span class="input-group-text">기본금액</span>
</div>
<input type="text" class="form-control" id="persons" name="money" value="25000" readonly="readonly">
</div>
</div>
<div class="col-12">
<button type="submit" class="btn btn-primary">제출</button>
</div>
</div>
</div>
</form:form>
</body>
이제 제출 버튼을 클릭하면 서버단에서 화면에서 입력된 데이터를 엑셀 파일에 입력하도록 하는 로직을 만들어보겠습니다.
HomeController.java
@RequestMapping(value = "/board/goToExcel.do", method = { RequestMethod.GET,RequestMethod.POST })
public ModelAndView goToExcel(Model model, @RequestParam HashMap<String, Object> paramMap) throws Exception {
String sample = "D:\\PLANM\\file\\excel\\reserv.xlsx";
try {
File file = new File(sample);
Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = workbook.getSheet("Sheet1");
String name = (String) paramMap.get("name");
String email = (String) paramMap.get("email");
int persons = Integer.parseInt(paramMap.get("persons").toString());
int money = Integer.parseInt(paramMap.get("money").toString());
// -------- 엑셀 파일 수정
Cell cell = null;
cell = getCell(sheet, 1, CellReference.convertColStringToIndex("B"));
cell.setCellValue(name);
cell = getCell(sheet, 2, CellReference.convertColStringToIndex("B")); // Cell 알파벳 -> 숫자
cell.setCellValue(email);
cell = getCell(sheet, 1, CellReference.convertColStringToIndex("E"));
cell.setCellValue(money);
cell = getCell(sheet, 2, CellReference.convertColStringToIndex("E"));
cell.setCellValue(persons);
cell = getCell(sheet, 3, CellReference.convertColStringToIndex("E"));
cell.setCellFormula("E2*E3");
// ----------------------------------------------------
// -------- 엑셀 파일 저장
FileOutputStream fos = new FileOutputStream(new File(sample));
workbook.write(fos);
// ----------------------------------------------------
if(fos != null) fos.close();
if(workbook != null) workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
File file = new File(sample);
return new ModelAndView("download", "downloadFile", file);
}
public Cell getCell(Sheet sheet, int rownum, int cellnum) {
Row row = sheet.getRow(rownum);
if(row == null) row = sheet.createRow(rownum);
Cell cell = row.getCell(cellnum);
if(cell == null) cell = row.createCell(cellnum);
return cell;
}
(8번 라인) WorkbookFactory 클래스는 엑셀 확장자 사용시 .xls, .xlsx 파일 모두 사용이 가능하도록 해줍니다.
참고로 엑셀 2007 이전(.xls) 버전 파일을 읽을 때는 HSSFWorkbook 클래스를 사용해야 하며, 엑셀 2007 이후(.xlsx) 버전 파일을 읽을 때는 XSSFWorkbook 클래스를 사용해야 합니다. 또 이에 따라 Sheet, Row, Cell 클래스도 변경됩니다.
- HSSFWorkbook 사용시 HSSFSheet, HSSFRow, HSSFCell
- XSSFWorkbook 사용시 XSSFSheet, XSSFRow, XSSFCell
하지만 WorkbookFactory를 사용하면 두 버전을 따로 구현할 필요가 없습니다.
(19번 라인) CellReference.convertColStringToIndex("B") 함수는 엑셀의 알파벳 열을 숫자로 변환해줍니다. 이 기능이 없었다면 직접 구현을 해야할만큼 필요한 기능입니다.
(32번 라인) setCellFormula를 사용하면 엑셀 수식도 사용이 가능하고 기본적으로 값을 넣을 때는 setCellValue를 사용합니다.
(51번 라인) getCell이라는 함수를 따로 빼서 시트와 행,열 값을 던지면 원하는 셀 값을 리턴하도록 했습니다. 엑셀의 행과 열은 0부터 시작하기 때문에 2행,2열에 값을 넣고 싶다면 (1,1) 값을 던져야 합니다.
시트는 사용하려는 시트명 그대로 입력하면 됩니다. 물론 인덱스 값으로도 사용할 수 있습니다.
*라이브러리 Import시 org.apache.poi.ss.usermodel을 해야 합니다.
[결과]
참고자료
poi 3.14 javadoc (org.apache.poi)
Latest version of org.apache.poi:poi https://javadoc.io/doc/org.apache.poi/poi Current version 3.14 https://javadoc.io/doc/org.apache.poi/poi/3.14 package-list path (used for javadoc generation -link option) https://javadoc.io/doc/org.apache.poi/poi/3.14/p
javadoc.io
CellReference (POI API Documentation)
Common conversion functions between Excel style A1, C27 style cell references, and POI usermodel style row=0, column=0 style references. Handles sheet-based and sheet-free references as well, eg "Sheet1!A1" and "$B$72" Use CellReference when the concept of
poi.apache.org
본 예제에서는 단순히 샘플 파일에 데이터를 입력하는 것이었습니다. 때문에 데이터가 입력되면 샘플 파일이 변하게 됩니다. 이 부분은 다른 방법으로 해결할 수 있습니다.(샘플 파일을 복사한 후에 복사된 파일에 데이터를 입력하는 방법 등)
또 DB를 연동해서 데이터를 입력할 수도 있을 것입니다.
전체 소스는 Github에서 확인하실 수 있습니다 :)