Apache POI를 이용해서 자바에서 엑셀(xls, xlsx)파일 입출력 하는 방법(엑셀 자료 정리를 matlab 대신 자바로 해버렸습니다.)

Apache POI를 이용한 엑셀 파일 입출력

보통 자바에서 .log(txt), .csv, .properties, .ini 같은 파일을 자주 사용하지 엑셀파일은 잘 다루지 않는다.

하지만 대량의 자료를 정리할 필요가 있어서 한 번 사용해보았다.

---

- Apache POI 라이브러리 적용하기

메이븐을 이용해서 라이브러리를 불러왔다. pom.xml에 추가한다. (현재 3.17버전까지 나왔다.)

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.13</version>
</dependency>

- 코드 보기 전 상황 요약

<각 폴더> : 이 안에 아래의 엑셀파일 리스트들이 쭉 있음

<디렉토리별 파일 현황> : 년도도 파일마다 다름

<파일 내부> : 어떤 날은 측정을 하고 어떤 날은 측정을 안한 상황

이 상황에서 원하는 결과값은?

해당 디렉토리별로 엑셀 시트를 만들어서 왼쪽에 날짜와 측정값을 세로로 정렬해보자!

(여기서 방법은 다양하게 있는걸로 알고 있다. 예를들면 엑셀함수를 쓰는 방법, matlab을 이용하는 방법등... 그러나 그런 것들은 잘 다루지 못하고 자바를 잘 다룰 수 있으므로.. 이렇게 한다.)

<결과>

<코드>

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.Vector;
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;
 
class Data{
   private String date;
   private String value;
   public String getDate() {
      return date;
   }
   public void setDate(String date) {
      this.date = date;
   }
   public String getValue() {
      return value;
   }
   public void setValue(String value) {
      this.value = value;
   }
   public Data(String date, String value) {
      super();
      this.date = date;
      this.value = value;
   }
   public Data() {}
}
 
 
 
public class ExcelParser {
   public static final String projectPath = System.getProperty("user.dir");
   public static int[] days = {31,28,31,30,31,30,31,31,30,31,30,31};
   public static void main(String[] args) {
      GregorianCalendar gregori = new GregorianCalendar();//윤년 확인을 위한 객체
      Vector<Data> vector = new Vector<Data>();//데이터 수집용 벡터
      String zone = null;//시트명
      Data newData;
      try {
         //디렉토리에서 파일 읽기
         File dirFile = new File(projectPath+"\\source\\"); //프로젝트 폴더에 source폴더
         File[] fileList = dirFile.listFiles();//해당 디렉토리에 파일리스트를 
         for(File tempFile : fileList) {
           if(tempFile.isFile()) {
               String fileName = tempFile.getName();//파일명 "Temp~~.xls"
               //엑셀파일이 아니면 건너뜀
               if(!(fileName.endsWith(".xls") && fileName.startsWith("Temp"))) {
                  continue;
               }
               String filePath = tempFile.getAbsolutePath();//전체절대경로
               String[] dirName = filePath.split("\\\\");//해당 폴더명 가져오기 위해 자르기
               zone = dirName[dirName.length-2];//바로 위의 디렉토리 명
               String temp = fileName.substring(0, fileName.indexOf("."));
               String year = temp.substring(temp.length()-4, temp.length());//year
 
               //윤년계산
               if(gregori.isLeapYear(Integer.parseInt(year))) {
                  days[1] = 29;
               }else {
                  days[1] = 28;
               }
 
               FileInputStream inputStream = new FileInputStream(filePath);
               HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//엑셀읽기
               HSSFSheet sheet = workbook.getSheetAt(0);//시트가져오기 0은 첫번째 시트
               int rows = sheet.getPhysicalNumberOfRows();//시트에서 총 행수
               for(int j=1;j<=12;j++) {
                  for(int i=3;i<rows;i++) {
                     //해당 월의 말일보다 크면 무시
                     if(i>(2+days[j-1])) {
                        continue;
                     }
                     //해당 셀 값 가져오기
                     HSSFCell cell = sheet.getRow(i).getCell(j);
                     String value = "";
                     if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
                            value="";
                     }else{
                            //타입별로 내용 읽기
                            switch (cell.getCellType()){
                            case HSSFCell.CELL_TYPE_FORMULA:
                                value=cell.getCellFormula();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                value=cell.getNumericCellValue()+"";
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                value=cell.getStringCellValue()+"";
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                value=cell.getBooleanCellValue()+"";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                value=cell.getErrorCellValue()+"";
                                break;
                            }
                        }
                     SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");//포맷정의
                     String date = year+"-"+j+ "-"+ (i-2);//날짜만들기 ex)2005-8-10
                     Date date1 = java.sql.Date.valueOf(date);//String->Date타입
                     String str = format.format(date1);//포맷에 매핑 ex)2005-08-10
                     newData = new Data(str,value);
                     vector.add(newData);
                  }
               }
            }
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
      try {
         Iterator<Data> iter = vector.iterator();
         HSSFWorkbook writebook = new HSSFWorkbook();//새 엑셀파일만들기
         HSSFSheet mySheet = writebook.createSheet(zone);//새 시트 만들기 (zone이라는 이름의 시트)
         int rowIndex = 0;
         //파일 생성
         HSSFRow row;
         Data d;
         while(iter.hasNext()) {
            d = iter.next();
            row = mySheet.createRow(++rowIndex);//행 생성
            HSSFCell cell = row.createCell(0);//해당 행의 1열
            cell.setCellValue(d.getDate());//값넣기
            cell = row.createCell(1);//해당 행의 2열
            cell.setCellValue(d.getValue());//값넣기
         }
         FileOutputStream output = new FileOutputStream(projectPath+File.separator+"dest\\result.xls");
         writebook.write(output);//파일 생성
         output.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

빠르게 정리만 하려고하다보니 한 클래스파일에 엉망진창으로 소스코드를 작성했지만 정리를 해본다.

(File.separator 쓰다가 '\\' 쓰다가 난리나고 for문 안에서 불필요하게 객체 생성하고 총체적 난국)

- 엑셀 파일 읽기

1. FileInputStream fis = new FileInputStream("엑셀파일경로");  //파일 읽기

2. HSSFWorkbook workbook = new HSSFWorkbook(fis); //엑셀파일을 관리하는 객체로 받아오기

3. HSSFSheet sheet = workbook.getSheet(0); // 인덱스로 원하는 시트 가져오기

4. HSSFCell cell = sheet.getRow(x).getCell(y); // 해당 시트에서 x행 y열의 셀 가져오기

5. cell.getCellType() 메서드로 타입확인 후 타입별로 데이터 받기 (NumberCell, StringCell, BooleanCell등..)

- 엑셀 파일 쓰기

1. HSSFWorkbook workbook = new HSSFWorkbook(); // 새 엑셀 파일 만들기

2. HSSFSheet sheet = workbook.createSheet(); // 엑셀 워크북에서 새 시트 만들기

3. HSSFRow row = sheet.createRow(x); // x행에 만들기(접근)

4. HSSFCell cell = row.createCell(y); // 해당 행의 y열 셀 만들기(접근)

5. cell.setCellValue(값); // 접근한 셀에 값 입력하기

6. FileOutputStream fos = new FileOutputStream("만든엑셀파일경로"); 

7. workbook.write(fos); //파일 출력하기

* 유의 사항

- 참고로 POI 라이브러리 사용은 엑셀파일이어야만 가능하다. 무슨말이냐면 예를들어 html을 엑셀포맷으로 바꿔볼 수 있는 것처럼 그냥 문서로 확인할 때는 엑셀파일인데 내부적으로 다른 확장자로 저장되어있을 때는 라이브러리에서 에러를 내뱉는다.

따라서 라이브러리를 사용하기 전에 "다른이름으로 저장"을 눌러서 파일 형식이 .xls 또는 .xlsx가 맞는지 확인해야하고 만약 html같은 형식으로 되어있으면 다른이름으로 저장할 때 파일 형식을 바꿔주면 된다.

- HSSF~~는 .xls 형식일 때 사용하는 클래스다. 만약 .xlsx 형식의 엑셀파일을 다룰 때에는 XSSF~~~로 workbook, sheet, row, cell을 다 바꿔줘야 한다.

- 위 소스코드는 복사붙여넣기해서 바로 되는것이 아니다. 파일 경로랑 이런 것들을 유의해서 맞춰주어야 한다.