JSP와 Excel 간의 데이터 주고 받기.

안녕하세요. Sharing Java 운영자 아기팬더 입니다. 자바를 이용하여 엑셀을 다루는 방법이 여러가지 있습니다.

관심이 많아서 네이버를 통해서 학습을 하고자 했는데 일관된 예제들과 생각보다는 부족한 설명으로 인해 많은 이해하기 위해 많은 시간을 보낸 것 같습니다. 개인적으로 공부차원에서 오늘 맘잡고 정리하여 공유합니다. 주석을 완전히 달면서 API 문서를 보려고 하니 생각보다 시간이 많이 걸리더군요. 이클립스를 주로 다루기 때문에 코딩하고 실행하였을 때 이클립스 상에서는 오류가 없었습니다. 혹시라도 실행이 안 되면 답글 달아주시면 바로 수정하겠습니다.

저는 jxl api를 이용하여 정리하였습니다. 참고 바랍니다.

1. 준비사항
당연히 이클립스를 사용하신다면 jxl api(링크주소) 중에서 jexcelapi_2_6_4.zip 를 다운 받아서 사용하시는 프로젝트 파일 주변에 압축을 풀어주세요.
(하단의 색깔부분이 수정된 내용입니다. 배포시 문제 발생 여지가 있어서 수정합니다.)

이클립스 메뉴바에서 현재의 프로젝트명을 오른쪽 클릭 - Build Path - Add Jar Field 클릭한 후 jxl API를 다운받아서 압축을 푼 곳에서 jxl.jar 파일을 찾아서 삽입시켜야 해요.

배포를 고려한다면 엄한 곳에서 가져오지는 마세요 --> (필독 : 사이트 링크)

2. 실전
jxl api 폴더 내의 doc폴더 안을 보시면 java api와 똑 같은 형태의 api문서를 확인하실 수 있습니다. 너무 자세하죠 ?
이제부터 주석을 꼼꼼히 보시면서 실행해 보시면 기분이 좋아지실 겁니다. ^^

3. Jxl_Write.java (엑셀 파일을 생성하는 부분)
//babypanda(2007.07.24)
import java.io.File; // 파일 생성을 위해서 필요
import java.io.FileNotFoundException; // 파일 생성을 위해서 필요
import java.io.IOException; // 파일 생성을 위해서 필요

// 아래의 jxl 부분을 이클립스에서 사용하시기 위해서는 메뉴바에서 현재의 프로젝트명을 오른쪽 클릭 - Build Path - Add External Archives 클릭한 후
// jxl API를 다운받아서 압축을 푼 곳에서 jxl.jar 파일을 찾아서 삽입시켜야 해요. 꼭.... 반드시.... 무조건....
// 배포를 고려한다면 엄한 곳에서 가져오지는 마셈. 가까운 곳에 압축을 풀어서 불러오셈. ^^
import jxl.Workbook; // 엑셀 파일 관리를 위해 처음과 끝에 해당하는 기본이 되는 추상 클래스
import jxl.write.WritableWorkbook; // 실제 엑셀 파일 관리를 위해 Workbook, Sheet을 생성하는 Swing의 Frame과 같은 사막의 오아시스 역할하는 추상 클래스
import jxl.write.WritableSheet; // 쉬트를 관리하는 인터페이스
import jxl.write.WritableCellFormat; // 셀의 포멧 관련 클래스
import jxl.write.WriteException; // 셀의 쓰기 오류를 관리하기 위한
import jxl.write.Label; // 라벨 관리 클래스
//import jxl.write.Blank; // 빈셀 관리 클래스
import jxl.format.*; // 셀 정렬, 보더, 컬러 부분 관련 클래스 임포트

public class Jxl_Write
{
 public static void main(String args[]) throws FileNotFoundException, IOException, WriteException
 {
 // 예외처리를 아예 해주고 내려옵니다.
 WritableWorkbook myWorkbook = Workbook.createWorkbook(new File("jxl_Smile.xls")); // 파일이름을 정하여 생성한다.
 WritableSheet mySheet = myWorkbook.createSheet("first sheet", 0);
 // WritableSheet는 인터페이스
 // WritableWorkbook에서 메소드를 이용하여 생성. 0번, 즉 첫번째 쉬트를 first sheet라는 이름으로 생성한다.
 WritableCellFormat numberFormat = new WritableCellFormat(); // 번호 셀 포멧 생성
 WritableCellFormat nameFormat = new WritableCellFormat(); // 이름 셀 포멧 생성
 WritableCellFormat dataFormat = new WritableCellFormat(); // 데이터 셀 포멧 생성
 // 번호 레이블 셀 포멧 구성(자세한 것은 링크 된 API를 참조하셈) 참고사항 : 여기 부분에서 WriteException이 발생하네요.
 // 그러나 상단에서 미리 예외 처리를 해서 상관 없겠네요.

 numberFormat.setAlignment(Alignment.CENTRE); // 셀 가운데 정렬
 numberFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 셀 수직 가운데 정렬
 numberFormat.setBorder(Border.ALL, BorderLineStyle.THICK); // 보더와 보더라인스타일 설정
 numberFormat.setBackground(Colour.ICE_BLUE); // 여름에 맞는 색깔 ? ^^

 // 이름 레이블 셀 포멧 구성(자세한 것은 링크 된 API를 참조하셈)
 nameFormat.setAlignment(Alignment.CENTRE); // 셀 가운데 정렬
 nameFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 셀 수직 가운데 정렬
 nameFormat.setBorder(Border.BOTTOM, BorderLineStyle.HAIR); // 보더와 보더라인스타일 설정
 nameFormat.setBackground(Colour.GOLD); // 여름에 맞는 색깔 두번째 ? ^^

 // 데이터 셀 포멧 구성
 dataFormat.setAlignment(Alignment.CENTRE); // 셀 가운데 정렬
 dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 셀 수직 가운데 정렬
 // 쉬트의 컬럼 넓이 설정
 mySheet.setColumnView(0, 8); // 쉬트의 번호 컬럼(0번째)의 넓이 설정. setCloumnView(몇번째 컬럼, 넓이)
 mySheet.setColumnView(1, 15); // 쉬트의 이름 컬럼(1번째)의 넓이 설정
 mySheet.setColumnView(2, 20); // 쉬트의 비고 컬럼(2번째)의 넓이 설정

 // 라벨을 이용하여 해당 셀에 정보 넣기 시작
 Label numberLabel = new Label(0, 0, "학번", numberFormat); // 학번 라벨(열,행,"문장",포멧)
 mySheet.addCell(numberLabel); // 쉬트의 addCell 메소드를 사용하여 삽입
 Label nameLabel = new Label(1, 0, "성명", nameFormat); // 성명 라벨(열,행,"문장",포멧)
 mySheet.addCell(nameLabel); // 쉬트의 addCell 메소드를 사용하여 삽입

 //Blank blank = new Blank(2, 0, numberFormat); // 빈 셀(열,행,포멧) -- 필요 시 주석 처리 풀고 사용하셈. ^^
 //sheet.addCell(blank);

 Label postScript = new Label(2, 0, "비고", nameFormat); // 비고 라벨(열,행,"문장",포멧)
 mySheet.addCell(postScript); // 쉬트의 addCell 메소드를 사용하여 삽입
 for(int no=1; no<6; no++)
 {
Label numberLabels = new Label(0, no, "["+no+"]", dataFormat); // 데이터 포멧에 맞게 1에서 5까지 번호 생성
mySheet.addCell(numberLabels); // 셀에 삽입

Label nameLabels =new Label(1, no, (char)(no+64)+"", dataFormat); // 데이터 포멧에 맞게 대문자 A에서 E까지 생성
mySheet.addCell(nameLabels); // 셀에 삽입
}
 // 라벨을 이용하여 해당 셀에 정보 넣기 끝

 myWorkbook.write(); // 준비된 정보를 엑셀 포멧에 맞게 작성
 myWorkbook.close(); // 처리 후 메모리에서 해제 처리
 }
}

4. Jxl_Read.java (엑셀파일을 읽어오는 부분)
//babypanda(2007.07.24)
import java.io.*; // 파일 입력 관련 클래스
import jxl.*; // jxl 관련 클래스

public class Jxl_Read
{
 public static void main(String args[]) throws FileNotFoundException, IOException,jxl.read.biff.BiffException
 {
// 이번에도 파일과 엑셀 파일 입력 관련 예외 선언을 미리 해주고 들어옵니다.

Workbook myWorkbook = Workbook.getWorkbook(new File("jxl_Smile.xls")); // 파일을 읽어 와서...
 Sheet mySheet = myWorkbook.getSheet(0); // 정확한 쉬트를 입력 받아서...
System.out.print("학번\t성명\t비고\t\n"); // 엑셀 제목을 적어 주고
for(int no=1;no<6;no++)
{ // 행의 갯수 만큼 돌리고
 for(int i=0;i<3;i++)
 { // 열의 갯수 만큼 돌려서
Cell myCell = mySheet.getCell(i,no); // 셀의 행과 열의 정보를 가져온 후...
System.out.print(myCell.getContents()+"\t"); // 텝의 거리 만큼 열을 나열 하고...
//getContents()메소드에 대해
//Quick and dirty function to return the contents of this cell as a string. 이라고 API에 쓰여 있더군요.
 }
 System.out.println(); // 행이 바뀔 때 마다 개행하여 출력해 보셈.
}
 }
}

5. 실행화면
1) 엑셀에 쓰기

2) 엑셀 파일의 비고란에 글자를 입력한 후 저장하여 읽어오면 ?

package archives.command;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import k3i.com.db.*;
import k3i.com.mvc.command.Command;
import k3i.com.mvc.config.ViewInfoConfig;
import k3i.com.mvc.config.ViewInfoMap;
import k3i.com.share.CharacterSet;

import archives.bean.*;
import archives.wrapper.*;

import e.*;

import jxl.*;
import jxl.write.*;
import jxl.format.*;

public class ArchivesPRTGetExcelCommand implements Command
{
public ArchivesPRTGetExcelCommand() {}
public ViewInfoConfig execute(ViewInfoMap viewInfoMap, HttpServletRequest request, HttpServletResponse response) throws ServletException
{
String srchValue = CharacterSet.Uni2Kor((String)Elib.getParameter("srchValue",request));
String srchType = CharacterSet.Uni2Kor((String)Elib.getParameter("srchType",request));
String doneType = CharacterSet.Uni2Kor((String)Elib.getParameter("doneType",request));

try
{
ArchPRTManager cArchPRTMng = ArchPRTManager.getInstance();
ArchPRT[] aArchPRT = cArchPRTMng.getArchPRT(srchType, srchValue, doneType);

//WritableWorkbook excelBook = Workbook.createWorkbook(new File("c:/myExcelFile.xls"));
WritableWorkbook excelBook = Workbook.createWorkbook(response.getOutputStream());
WritableSheet sheet = excelBook.createSheet("Sheet1", 0);

WritableCellFormat format = new WritableCellFormat();
WritableCellFormat format0 = new WritableCellFormat();

format.setBackground(jxl.format.Colour.GREY_25_PERCENT );
format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN );
format.setAlignment(jxl.format.Alignment.CENTRE);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

format0.setBackground(jxl.format.Colour.WHITE );
format0.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN );
format0.setAlignment(jxl.format.Alignment.CENTRE);

sheet.setColumnView(0,8);

jxl.write.Label label = null;
label = new jxl.write.Label(0,0,"발행기관",format);
sheet.addCell(label);
label = new jxl.write.Label(1,0,"주관부서",format);
sheet.addCell(label);
label = new jxl.write.Label(2,0,"발행기관 전화번호",format);
sheet.addCell(label);
label = new jxl.write.Label(3,0,"발행기관 팩스번호",format);
sheet.addCell(label);
label = new jxl.write.Label(4,0,"간행물 제목",format);
sheet.addCell(label);
label = new jxl.write.Label(5,0,"발간주기",format);
sheet.addCell(label);
label = new jxl.write.Label(6,0,"발간용도",format);
sheet.addCell(label);
label = new jxl.write.Label(7,0,"발간매체",format);
sheet.addCell(label);
label = new jxl.write.Label(8,0,"페이지수",format);
sheet.addCell(label);
label = new jxl.write.Label(9,0,"가로크기",format);
sheet.addCell(label);
label = new jxl.write.Label(10,0,"세로크기",format);
sheet.addCell(label);
label = new jxl.write.Label(11,0,"내용요약",format);
sheet.addCell(label);
label = new jxl.write.Label(12,0,"문의사항 및 건의사항",format);
sheet.addCell(label);
label = new jxl.write.Label(13,0,"신청자 이름",format);
sheet.addCell(label);
label = new jxl.write.Label(14,0,"신청자 소속기관",format);
sheet.addCell(label);
label = new jxl.write.Label(15,0,"신청자 소속부서",format);
sheet.addCell(label);
label = new jxl.write.Label(16,0,"신청자 전화번호",format);
sheet.addCell(label);
label = new jxl.write.Label(17,0,"저작물이용동의여부",format);
sheet.addCell(label);
label = new jxl.write.Label(18,0,"비동의사유",format);
sheet.addCell(label);
label = new jxl.write.Label(19,0,"저작물공개여부",format);
sheet.addCell(label);
label = new jxl.write.Label(20,0,"비공개사유",format);
sheet.addCell(label);
label = new jxl.write.Label(21,0,"비공개기간 시작일",format);
sheet.addCell(label);
label = new jxl.write.Label(22,0,"비공개기간 종료일",format);
sheet.addCell(label);
label = new jxl.write.Label(23,0,"등록일자",format);
sheet.addCell(label);
label = new jxl.write.Label(24,0,"발간등록번호",format);
sheet.addCell(label);

for(int i = 1 ; aArchPRT != null && i < aArchPRT.length+1; i++)
{
for(int k=0;k<24;k++)
{
label = new jxl.write.Label(0, i, aArchPRT[i-1].getPub_kikwan() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(1, i, aArchPRT[i-1].getPub_place() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(2, i, aArchPRT[i-1].getPub_tel() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(3, i, aArchPRT[i-1].getPub_fax() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(4, i, aArchPRT[i-1].getArc_title() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(5, i, aArchPRT[i-1].getArc_cycle() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(6, i, aArchPRT[i-1].getArc_service() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(7, i, aArchPRT[i-1].getArc_media() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(8, i, String.valueOf(aArchPRT[i-1].getArc_page()) ,format0);
sheet.addCell(label);

label = new jxl.write.Label(9, i, String.valueOf(aArchPRT[i-1].getArc_width()) ,format0);
sheet.addCell(label);
label = new jxl.write.Label(10, i, String.valueOf(aArchPRT[i-1].getArc_height()) ,format0);
sheet.addCell(label);
label = new jxl.write.Label(11, i, aArchPRT[i-1].getArc_contents() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(12, i, aArchPRT[i-1].getArc_request() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(13, i, aArchPRT[i-1].getReg_name() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(14, i, aArchPRT[i-1].getReg_kikwan() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(15, i, aArchPRT[i-1].getReg_place() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(16, i, aArchPRT[i-1].getReg_tel() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(17, i, aArchPRT[i-1].getArc_isusable() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(18, i, aArchPRT[i-1].getArc_reason1() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(19, i, aArchPRT[i-1].getArc_ispub() ,format0);
sheet.addCell(label);
label = new jxl.write.Label(20, i, aArchPRT[i-1].getArc_reason2() ,format0);
sheet.addCell(label);

label = new jxl.write.Label(21, i, String.valueOf(aArchPRT[i-1].getArc_prvsdate()) ,format0);
sheet.addCell(label);
label = new jxl.write.Label(22, i, String.valueOf(aArchPRT[i-1].getArc_prvedate()) ,format0);
sheet.addCell(label);
label = new jxl.write.Label(23, i, String.valueOf(aArchPRT[i-1].getCpl_date()) ,format0);
sheet.addCell(label);
label = new jxl.write.Label(24, i, aArchPRT[i-1].getNo() ,format0);
sheet.addCell(label);
}
}

 /*엑셀파일 다운로드 설정*/
 response.setContentType("text/html; charset=euc-kr");
 response.setHeader("Content-Transfer-Encoding", "7bit");
 if (request.getHeader("User-Agent").indexOf("MSIE 5.5") > -1)
{
 response.setHeader("Content-Disposition","filename=" + java.net.URLEncoder.encode("requestList.csv", "euc-kr") + ";");
 }
else
{
 response.setHeader("Content-Disposition","attachment; filename=" + new String("requestList.csv".getBytes("EUC-KR"), "8859_1") + ";");
 }
 //response.setHeader("Content-Length", "" + excelBook.toString().length());
 response.setHeader("Content-Length", "");
 /*엑셀파일 다운로드 설정*/
 excelBook.write();
 excelBook.close();
}
catch(Exception e)
{
new ExceptionLog(e, "archivesPRTGetExcel.doCommand", "execute()");
System.out.println(e);
}
return viewInfoMap.getViewInfoConfig("archivesPRTGetExcel");
}
}

Jakarta POI

I. POI 란?

일반적으로 POI가 엑셀파일을 쓰는 컴퍼넌트로 알려져 있으나 POI는 프로젝트 이름입니다.
즉 POI는 Microsoft Format File을 액세스 할 수 있는 API를 제공합니다. (한마디로 자바에서 MS파일을 읽고 쓸수있도록 지원합니다.)

POI안에는 여러 컴퍼넌트들이 있습니다.

① POIFS
Microsoft의 OLE2 포맷 형식의 문서를 자바로 읽고 쓸수 있는 컴퍼넌트입니다
기본적으로 POI의 모든 컴퍼넌트들이 POIFS를 사용합니다.
② HSSF
Microsoft의 엑셀파일을 읽고 쓸수 있도록 지원하는 컴퍼넌트입니다.
③ HWPF
Microsoft의 워드파일을 읽고 쓸수 있도록 지원하는 컴퍼넌트입니다.
이 컴퍼넌트는 디자인 초기단계입니다.
④ HPSF
Microsoft의 OLE2 포맷 형식의 문서 속성을 어플리케이션에서 사용 할수 있도록 지원하는 컴퍼넌트입니다.
현재 읽기 기능만 제공합니다.

워드파일을 핸들링 하는 HWPF는 초기단계라 사용을 못하지만 기대는 되는군요 ^^

ps. 영어사전을 찾아보니 poi는 하와이의 토란 요리를 뜻하더군요.
우리나라말로 하니 자카르타 토란 프로젝트 쯤 될라나? ㅎㅎ

II. 다운로드 및 설치
다운로드 받으러 갑시다~!http://jakarta.apache.org/site/downloads/downloads_poi.cgi
현재 2.5.1버젼입니다.
다운받은 파일을 압축을 풀면 *.jar 파일들이 있을겁니다 이 파일들을 자신의 어플리케이션 /lib/에 복사합시다
POI API http://jakarta.apache.org/poi/apidocs/index.html
Quick Guide http://jakarta.apache.org/poi/hssf/quick-guide.html

III. Formula(수식) 지원에 관해..

엑셀을 읽고 쓸때 수식을 지원합니다.
org.apache.poi.hssf.usermodel.HSSFCell의 setCellFormula("formulaString") 메쏘드는 스프레드시트에 수식을 추가하는데 사용되며 getCellFormula() 메쏘드는 수식을 대표하는 문자열을 해석하는데 사용됩니다. 하지만 엑셀에서 사용하는 수식을 모두 사용 할 수는 없습니다.

① 지원되는 부분
-. 셀 참조, 시트참조, 지역참조
-. 상대적 혹은 절대적 참조
-. 수연산 및 논리연산
-. 시트 혹은 매크로 함수
-. 수식 결과값 반환
② 부분적 지원
문자열을 포함하는 수식을 해석할 수는 있지만 문자열값을 반환하는 수식은 아직 지원하지 않습니다.
③ 지원되지 않는 부분
-. 배열 수식
-. 1진법 수식
-. 3D 참조
-. 에러 값 (cells containing #REF's or #VALUE's)

IV. 기본객체

가장 기본이되는 객체가 다음 4가지 입니다.
이름에서 무엇을 뜻하는지 대강 짐작 할 수 있겠죵?

① HSSFWorkbook - 엑셀 워크북을 말합니다.
② HSSFSheet - 엑셀 쉬트를 나타냅니다.
③ HSSFRow - 엑셀에서 특정 행입니다.
④ HSSFCell - 엑셀에서 특정 행에대한 특정 셀입니다

위 4가지 객체는 앞으로 계속 나올겁니다. 눈여겨 미리 봐 둡시다. @.@

V. 엑셀 읽기 예제

① POSFS을 이용하여 엑셀 워크북을 생성합니다.
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("excelfile.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);

② 생성된 워크북을 이용하여 시트 수만큼 돌면서 엑셀 시트 하나씩을 생성합니다.
int sheetNum = workbook.getNumberOfSheets();
for (int k = 0; k < sheetNum; k++)
{
 System.out.println("Sheet Number : "+k);
 System.out.println(Sheet Name : " + workbook.getSheetName(k));
HSSFSheet sheet = workbook.getSheetAt(k);
}

③ 생성된 시트를 이용하여 그 행의 수만큼 돌면서 행을 하나씩 생성합니다.
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
 HSSFRow row = sheet.getRow(r);
System.out.println("Row : "+row.getRowNum());
}

④ 역시나 생성된 행을 이용하여 그 셀의 수만큼 돌면서 셀을 하나씩 생성합니다.
int cells = row.getPhysicalNumberOfCells();
for (short c = 0; c < cells; c++)
{ <--!! short 형입니다. 255개가 max!
HSSFCell cell = row.getCell(c);
int celltype = cell.getCellType();
...
}
셀을 생성하여 셀 타입에 따라 처리를 해주면 끝~

⑤ 주의사항
만약 엑셀에서 A열에 아무런 값이 없으면 그 행은 읽지 못합니다.
행을 읽지 못하니 셀또한 처리 할 수 없습니다.

VI. 엑셀읽기 샘플소스
샘플 데이터

A열은 B열에 대한 셀 타입을 나타내며 C열은 D열에대한 셀 타입을 나타냅니다.
즉 B:1 의 123456의 셀 타입은 A:1 일반 이라는 것이며 마찬가지로 D:1의 2005-02-09의 셀타입은 C:1 사용자정의로 세팅하였다는 겁니다.

이 엑셀의 데이터를 다음 소스로 읽어 보겠습니다.

<%@ page language="java" contentType="text/html;charset=euc-kr"
import="java.io.*,
org.apache.poi.poifs.filesystem.POIFSFileSystem,
org.apache.poi.hssf.record.*,
org.apache.poi.hssf.model.*,
org.apache.poi.hssf.usermodel.*,
org.apache.poi.hssf.util.*" %>

<html>
<head><title>Read example</title></head>
<body>

<%
String excelfile = "C:\\Tomcat 5.0\webapps\\ROOT\\example.xls";

try
{
 POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelfile));

 //워크북을 생성!
 HSSFWorkbook workbook = new HSSFWorkbook(fs);
int sheetNum = workbook.getNumberOfSheets();

 for (int k = 0; k < sheetNum; k++)
{
//시트 이름과 시트번호를 추출
%>
 
Sheet Number <%= k %> 
Sheet Name <%= workbook.getSheetName(k) %> 
<%
HSSFSheet sheet = workbook.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();

for (int r = 0; r < rows; r++)
{
// 시트에 대한 행을 하나씩 추출
HSSFRow row = sheet.getRow(r);
if (row != null)
{
 int cells = row.getPhysicalNumberOfCells();
%>
ROW <%= row.getRowNum() %> <%=cells%> 
<%
for (short c = 0; c < cells; c++)
 {
 // 행에대한 셀을 하나씩 추출하여 셀 타입에 따라 처리
 HSSFCell cell = row.getCell(c);
 if (cell != null)
 {
String value = null;

switch (cell.getCellType())
{
 case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA value=" + cell.getCellFormula();
break;
 case HSSFCell.CELL_TYPE_NUMERIC : //double
 value = "NUMERIC value=" + cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING : //String
value = "STRING value=" + cell.getStringCellValue();
break;
 case HSSFCell.CELL_TYPE_BLANK :
value = null;
break;
case HSSFCell.CELL_TYPE_BOOLEAN : //boolean
value = "BOOLEAN value=" + cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_ERROR : // byte
value = "ERROR value=" + cell.getErrorCellValue();
break;
 default :
 }
%>
<%= "CELL col=" + cell.getCellNum() + " VALUE=" + value %> 
<%
}
}
}
}
}
}
catch (Exception e)
{
%>
 Error occurred: <%= e.getMessage() %>
<%
e.printStackTrace();
}
%>

</body>
</html>

위 소스의 결과입니다.

Sheet Number 0
Sheet Name 한글
ROW 0 4
CELL col=0 VALUE=STRING value=일반
CELL col=1 VALUE=NUMERIC value=123456.0
CELL col=2 VALUE=STRING value=사용자정의
CELL col=3 VALUE=NUMERIC value=38392.0
ROW 1 4
CELL col=0 VALUE=STRING value=숫자
CELL col=1 VALUE=NUMERIC value=123456.0
CELL col=2 VALUE=STRING value=날짜 (yy-m-d h:mm)
CELL col=3 VALUE=NUMERIC value=38393.0
ROW 2 4
CELL col=0 VALUE=STRING value=통화
CELL col=1 VALUE=NUMERIC value=123456.0
CELL col=2 VALUE=STRING value=날짜 (yy年 mm月 dd日)
CELL col=3 VALUE=NUMERIC value=38394.0
ROW 3 4
CELL col=0 VALUE=STRING value=텍스트
CELL col=1 VALUE=NUMERIC value=123456.0
CELL col=2 VALUE=STRING value=날짜 (yyyy년 mm월 dd일)
CELL col=3 VALUE=NUMERIC value=38395.0

결과를 보니 사용자가 지정한 셀 타입에 관계없이 숫자관련 셀은 POI에서 모두 숫자 타입으로 인식해 버렸습니다.
날짜 역시 지정한 셀 타입에 관계없이 모두 숫자 타입으로 인식해 버리는군요!

그럼 어떻게 날짜를 제대로 표현할까요?

날짜 타입을 제대로 나타내기 위해서는 날짜 Cell에는 getDateCellValue()를 사용하면 정상적으로 처리 할 수 있습니다.

SimpleDateformat sdf = new SimpleDateformat("yyyy-MM-dd hh:mm");
String date = sdf.format(cell.getDateCellValue());

등을 이용하면 나타내고자 하는 알짜를 표현 하기 더 쉽겠지요.
나머지 수식을 가져 올때도 마찬가지입니다. 이런 사항을 도표로 나타내보았습니다.

org.apache.poi.hssf.usermodel.HSSFCell 에는 모두 6가지의 Cell Type이 있는데,
cell.getCellType()을 하면 그 셀의 반환값을 알 수 있으며 그에 상응하는 static 필드타입은 다음과 같습니다.

셀타입	필드타입	함수	함수반환값
0	CELL_TYPE_NUMERIC	getNumericCellValue() -> 숫자 타입일때 getDateCellValue() -> 날짜 타입일때	double Date
1	CELL_TYPE_STRING	getStringCellValue()	String
2	CELL_TYPE_FORMULA	getCellFormula() -> 수식자체를 가져올때 getNumericCellValue() -> 수식 반환값이 숫자일때 getStringCellValue() -> 수식 반환값이 문자일때	String double String
3	CELL_TYPE_BLANK
4	CELL_TYPE_BOOLEAN	getBooleanCellValue()	boolean
5	CELL_TYPE_ERROR	getErrorCellvalue()	byte

웹시스템에서 화면상에 보이는 데이타를 엑셀로 변환처리하는 기능은 자주 사용하는 기능 입니다.

일반적으로 엑셀 변환시 아래와 같이 contentType 을 excel 로 주어 처리를 하는게 보통인데

<%@ page contentType="application/vnd.ms-excel; charset=EUC-KR" %>

만약 변환해야하는 데이타 컬럼수 와 ROW 수가 많은 데이타일경우 변환된 파일을 엑셀로 Open 하는데 많은 시간이 걸립니다.
(5M BYTE 넘어가는거는 거의 담배한대 피고와야 열립니다.)

이유는 엑셀 변환한 물리적인파일이 눈으로보기에는 엑셀 파일처럼 보이지만 해당 파일을 EDITER 로 열어보면 HTML코드로 이루어 진것을 볼수 있습니다.

많은 데이타를 포함한 파일일 경우 엑셀이 해당 파일을 OPEN 하면서 변환처리하는 과정을 거치는 과정이 그만큼 많이 소요 되기 때문이라 생각합니다.
이러한 경우 해결 방법은 POI 를 사용하여 엑셀 파일을 생성하면 됩니다.

POI 관련 JAR 파일은 아래의 3가지 파일 입니다.
poi-scratchpad-2.5.1-final-20040804.jar
poi-2.5.1-final-20040804.jar
poi-contrib-2.5.1-final-20040804.jar

위의 3가지 JAR 파일을 프로젝트의 lib 폴더에 복사해 넣으신후 첨부한 엑셀 변환 소스와 생성된 파일을 참조하셔셔서 사용하시면 됩니다.

search_ob_result_S_xls.jsp -> contentType 이용 엑셀 생성 소스
search_ob_result_S_xls_poi.jsp -> POI 방식이용 엑셀 생성 소스

아래는 위의 각각의 파일에의해서 생성된 엑셀 파일입니다.

엑셀변환1.xls -> 기존 contentType 을 사용하여 변환한 엑셀 파일
엑셀변환2.xls -> Poi 방식을 이용하여 변환한 엑셀 파일

poi엑셀변환-vb22.zip

티스토리툴바