JAVA에서 Excel 파일 읽기/쓰기

작성자메룽_성완|작성시간07.04.04|조회수4,645 목록 댓글 0

Apache Jakarta의 하위 프로젝트 POI 싸이트(http://jakarta.apache.org/poi/)에서

api를 다운받는다. (현재 가능한 버전이 2004년 8월 4일 릴리즈된 2.5.1임)

1. poi 패키지 이용하여 읽기

import org.apache.poi.hssf.usermodel.*;
import java.io.*;

class ExcelTestRead
{
public static void main(String[] args)
{
        try {
                //입력 스트림 생성
                FileInputStream fileInput = new FileInputStream("test.xls");

                //Workbook 읽기
                HSSFWorkbook workbook = new HSSFWorkbook(fileInput);
                fileInput.close();

                //Sheet 읽기
                HSSFSheet sheet = workbook.getSheetAt(0);

                //Row 읽기
                HSSFRow row = sheet.getRow(0);

                //Cell 3개 읽기
                HSSFCell cell1 = row.getCell((short)0);
                String string1 = cell1.getStringCellValue();

                HSSFCell cell2 = row.getCell((short)1);
                String string2 = cell2.getStringCellValue();

                HSSFCell cell3 = row.getCell((short)2);
                String string3 = cell3.getStringCellValue();

                System.out.println("Excel File의 내용: "+string1+" | "+string2+" | "+string3);
        }
        catch(Exception e) {
                System.out.println(e);
        }
}
}

2. poi 패키지 이용하여 쓰기(java)

import org.apache.poi.hssf.usermodel.*;
import java.io.*;

class ExcelTestWrite
{
public static void main(String[] args)
{
        try {
                //Workbook 생성
                HSSFWorkbook workbook = new HSSFWorkbook();

                //Sheet를 생성
                HSSFSheet sheet = workbook.createSheet("TEST SHEET");

                //Row 생성
                HSSFRow row = sheet.createRow(0);

                //Cell 3개 생성
                HSSFCell cell1 = row.createCell((short)0);
                cell1.setCellValue("cell 1");

                HSSFCell cell2 = row.createCell((short)1);
                cell2.setCellValue("cell 2");

                HSSFCell cell3 = row.createCell((short)2);
                cell3.setCellValue("cell 3");

                //파일로 쓰기
                FileOutputStream fileOutput = new FileOutputStream("test.xls");

                workbook.write(fileOutput);
                fileOutput.close();

        }
        catch(Exception e) {
                System.out.println(e);
        }
}
}

3. poi 패키지 이용하여 쓰기(JSP/Servlet)

<%@ page language="java" contentType="application/vnd.ms-excel; name='excel', text/html; charset=UTF-8"

pageEncoding="UTF-8" errorPage="" %>

<%@ page import="org.w3c.dom.*"
%><%@ page import="com.inswave.util.*"
%><%@ page import="com.inswave.system.*"
%><%@ page import="org.apache.poi.hssf.util.*,org.apache.poi.hssf.usermodel.*,java.io.*,java.util.*"
%><%

HSSFWorkbook workbook = new HSSFWorkbook(); //workbook생성
HSSFSheet sheet = workbook.createSheet("총괄상환판"); //worksheet에 new sheet생성

    OutputStream fileOut = response.getOutputStream();

try{

        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = workbook.createCellStyle();
        //style.setFillBackgroundColor(HSSFColor.ORANGE.index);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFCell cell;

cell = row.createCell((short)j);
cell.setCellValue(tit1[j]);
cell.setCellStyle(style);

     workbook.write(fileOut);

} catch (Exception e){
  throw e;
    } finally {
        if (fileOut != null) {
           fileOut.close();
        }
    }

%>

//가로 병합

sheet.addMergedRegion(new Region(int 시작, short 시작col, int 종료row, short 종료col));

//세로 병합

sheet.addMergedRegion(new CellRangeAddress(first row, last row, first column, last column));

4. Html을 Excel 파일로 변환

<%@ page language="java" contentType="application/vnd.ms-excel; name='excel', text/html; charset=UTF-8"

pageEncoding="UTF-8" errorPage="" %>

//EXCEL 파일명 지정
String filename = request.getParameter( "title" );
filename = new String(filename.getBytes("KSC5601"), "8859_1");
//response.setHeader("Content-Type", "application/vnd.ms-excel;charset=EUC-KR");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename="+filename+".xls");
response.setHeader("Content-Description", "JSP Generated Data");
response.setHeader("Content-Transfer-Encoding", "binary;");
response.setHeader("Pragma", "no-cache;");
response.setHeader("Expires", "-1;");

out.clearBuffer();

td {font-family:돋움;}

</style>

<table>

<tr>

</tr>

<tr>

</tr>

</table>

//한글깨짐

head 에 다음과 같은 처리를 같이 해주면 왠만하면 해결이 된다.

response.reset(); // response 버퍼를 비우고 respose 값을 세로 세팅

엑셀 녀석이 데이터를 인코딩 태그로 인식하는 경우가 간혹 생기는데 데이터 타입 앞에  만 입력해주면 끝. 아주 깨끗하게 잘 나온다.

<td> <%= crset.getString(1) %></td> ← 이런 식으로

//숫자형식 엑셀에서 표현하기

td {mso-number-format:000000;}

</style>

또는

000000 : 소수도 여섯자리 정수 (반올림)로 표현된다. 여섯자리 앞의 빈칸은 0으로 채워짐

1.23 => 000001, 67.67 => 000068

000.000 : 소수자리 세자리까지 (반올림) 표현된다. 앞 뒤 빈칸은 0으로 채워짐

format은 0.00 인데 숫자가 15.1 인 경우 15.10으로 표현됨

1.5678 => 001.568

\@ : 셀형식을 텍스트형으로 표현

00035.90 인 경우 셀 형식이 숫자형이라면 35.9로 표현되지만 문자형으로 하면 0을 포함하여 보이는 그대로 표현됨

//그 외 mso-number-format 요소들

mso-number-format:"0"
        NO Decimals
mso-number-format:"0\.000"
        3 Decimals
mso-number-format:"\#\,\#\#0\.000"
        Comma with 3 dec
mso-number-format:"mm\/dd\/yy"
        Date7
mso-number-format:"mmmm\ d\,\ yyyy"
        Date9
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM"
        D -T AMPM
mso-number-format:"Short Date"
        01/03/1998
mso-number-format:"Medium Date"
        01-mar-98
mso-number-format:"d\-mmm\-yyyy"
        01-mar-1998
mso-number-format:"Short Time"
        5:16
mso-number-format:"Medium Time"
        5:16 am
mso-number-format:"Long Time"
        5:16:21:00
mso-number-format:"Percent"
        Percent - two decimals
mso-number-format:"0%"
        Percent - no decimals
mso-number-format:"0\.E+00"
        Scientific Notation
mso-number-format:"\@"
        Text
mso-number-format:"\#\ ???\/???"
        Fractions - up to 3 digits (312/943)
mso-number-format:"\0022￡\0022\#\,\#\#0\.00"
        ￡12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\"
        2 decimals, negative numbers in red and signed(1.56   -1.56)

//한 셀 안에서 줄바꿈

<style>

.xl24 {mso-number-format:"\@";}

br {mso-data-placement:same-cell;}

</style>

JSP에서 excel 변환문제는 여러 포스트에서 볼수 있다.(나도 몰라서 쉽게 찾아서 했다.)

현업에서 단가를 소수점 까지 나타내 달라는 요청이 왔는데.

JSP에선 간단하게 sql에서 값을 double로 받고 그대로 뿌려주면 끝났었다.

BUT 문제는 InsertComma()!

excel에선 InsertComma()를 쓰고 서식을 보게 되면 (셀서식->범주) 만일 단위가 100단위인 경우엔 InsertComma()가 적용되지 않아서 일반으로 되고, 1000단위 이상은 숫자로 되어 자릿수를 표기하는것을 볼수 있다.

- 사실 여기 까지 알아내는데 하루가 걸렸다. 왜 엑셀로 변환하면 않되는지 몰라서 계속 소스만 보다가 단위문제인것을 알았다.

InsertComma가 들어갔지만 자릿수때문에 형식이 일반으로 되었다.

InsertComma가 제대로 들어간거 (숫자로 자릿수까지 잘 적용되었다.)

그러면 InsertComma대신 다른것을 써야 하는데 어떻게 해야하는가?!

(자릿수에 따른 자바가 다른게 자릿수를 선택한다는것을 알게될것이다.)

엑셀을 브라우져에서 볼수 있는것이 힌트이다.

요즘에 오피는 openOffice를 표방하며 어디서든지 구동이 가능하게 되었는데. Office가 스크립트에서 작동하도록 한것이다. 그러면 일반 엑셀파일도 html로 변환이 가능하므로 변환값의 함수를 찾기만 하면 된다.(사실 처음엔 함수라고 생각했다.)

- 지금부터는 파일 서식(html에서의 style 을 보기위해서 하는것이기 떄문에 원하는 형식으로 엑셀파일으르 작성한 다음 부터 따라 하시면 됩니다.

1.서식을 소수점까지 한다음에(자릿수 체크) 다른이름으로 저장->다른형식으로 저장을 클릭한다.

2. 파일 형식을 웹페이지(*.htm; *.html) 로 저장한다.

3.저장한 파일을 열어보면(notepad 같은 걸로 여시면 됩니다.) 값이 들어있던 자리의 style을 보시면 됩니다.

여기선 3Dx169, 3Dx170 등으로 되어있군요. 이름이 틀려서 나오네요 찾는데 애먹었습니다;;

-2007의 문제인지는 모르겠으나 2003에서 했을경우 정확한 이름이 나왔습니다. 확인해야겠습니다.

4. x170 으로 클래스로 지정되어 있습니다. 여기서 다른 폰트스타일은 제외하고 mso-number-format:"~~~"부분을 주목하세요! 그 부분을 이제 JSP파일로 복사해서 저장할려는 테이블 혹은 글씨의 스타일에 주면 되는겁니다.

5. 일단 값이 sql 에서 받기 때문에 Double로 받습니다.(그래야 소수점까지 표시가 잘 되겠죠? 아니면 소수점이 짤려서 값이 .0 으로 표기 됩니다.)

6. 해당하는(저장하려는 부분에 )스타일을 위에서 복사한 값을 주시면 됩니다.

4. Spring MVC에서 jexcel 모듈을 활용한 엑셀 파일 다운로드

Spring MVC의 유연함을 확인할 수 있는 기능 중에 하나가 엑셀 다운로드와 같은 기능이리라. 지금까지 JSP 기반으로 UI를 서비스하다가 특정 기능에 대하여 엑셀로 다운로드하는 기능을 구현하고 싶다면 Spring MVC에서는 엑셀 다운로드를 담당하는 View를 만들어 교체해 주기만 하면 된다. 이전에 개발했던 비지니스 로직과 모델 API는 그대로 사용 가능하다. 물론 Spring MVC를 이용하지 않더라도 같은 기능을 구현할 수 있다. 하지만 Spring MVC를 이해하고 이 기반으로 엑셀 다운로드 기능을 구현하기란 정말 쉽다.

Spring MVC 3.0 기능에 대하여 먼저 공부하고 싶은 개발자가 있다면 최근에 공개된 Spring MVC 3 Showcase 문서를 참고한다. 동영상까지 제공하고 있으며, Spring MVC 3.0에서 제공하는 모든 기능을 예제 소스까지 포함하고 있어 Spring MVC 3.0을 공부하는 개발자에게 많은 도움이 되리라 생각한다.

먼저 엑셀 다운로드 기능을 구현하기 위하여 Controller를 다음과 같이 구현한다. 구현할 기능은 설문 결과를 엑셀로 다운로드 하는 기능이다.

import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.View;

@Controller
@RequestMapping("/survey")
public class SurveyController {
    @RequestMapping(value="/excel", method=RequestMethod.GET)
    public View excel(ModelMap model) {
        model.addAttribute("surveys", Survey.findAll(Survey.class));
        return new SurveyExcelView();
    }
}

위 예제 소스와 같이 설문 조사 결과를 Survey 클래스에서 조회한 후 ModelMap을 통하여 전달한다. 위와 같이 간단하게 Controller를 구현한 후 SurveyExcelView 클래스를 구현하면 된다.

이 글의 댓글에 토비님이 의견을 달았듯이 Spring의 View 클래스는 무상태 클래스이기 때문에 위 예제 소스와 같이 View 클래스를 매번 생성하기 보다는 설정 파일에서 View 클래스를 관리하고 DI를 통해서 구현하는 것이 더 좋은 습관이다. 위 예제 소스를 보면 단위 테스트 없이 구현한 것이 확 들어날 것이다.

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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

import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.springframework.web.servlet.view.document.AbstractJExcelView;

public class SurveyExcelView extends AbstractJExcelView {
    protected void buildExcelDocument
       (Map<String, Object> model, WritableWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
        String fileName = createFileName();
        setFileNameToResponse(request, response, fileName);

        List<Survey> surveys = (List<Survey>)model.get("surveys");
        WritableSheet sheet = workbook.createSheet("설문 목록", 0);

        sheet.addCell(new jxl.write.Label(0, 0, "사용자아이디"));
        for(int i=1; i < 13; i++) {
            sheet.addCell(new jxl.write.Label(i, 0, "답변" + i));
        }

        int row = 1;
        for (Iterator iterator = surveys.iterator(); iterator.hasNext();) {
            Survey survey = (Survey) iterator.next();
            sheet.addCell(new jxl.write.Label(0, row, survey.getUserId()));
            sheet.addCell(new jxl.write.Number(1, row, survey.getAnswer1()));
            sheet.addCell(new jxl.write.Number(2, row, survey.getAnswer2()));
            sheet.addCell(new jxl.write.Label(3, row, survey.getAnswer3()));
            sheet.addCell(new jxl.write.Number(4, row, survey.getAnswer4()));
            row++;
        }
    }

    private void setFileNameToResponse
       (HttpServletRequest request, HttpServletResponse response, String fileName) {
        String userAgent = request.getHeader("User-Agent");
        if (userAgent.indexOf("MSIE 5.5") >= 0) {
            response.setContentType("doesn/matter");
            response.setHeader("Content-Disposition","filename=\""+fileName+"\"");
        } else {
            response.setHeader("Content-Disposition","attachment; filename=\""+fileName+"\"");
        }
    }

    private String createFileName() {
        SimpleDateFormat fileFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
        return new StringBuilder("설문조사")
            .append("-").append(fileFormat.format(new Date())).append(".xls").toString();
    }
}

위와 같은 형태로 JExcel 기반으로 엑셀 파일을 생성한다. 위 예제 소스를 보면 알 수 있듯이 JExcel API를 이용하여 WritableWorkbook을 생성하고 엑셀 파일을 생성하는 부분은 모두 Spring 프레임워크에서 제공하는 AbstractJExcelView클래스가 담당하고 있다. 우리가 추가적으로 구현해야 할 부분은 WritableWorkbook API에 생성할 데이터를 추가하는 작업만 해주면 된다.

이와 같이 Spring MVC는 하나의 데이터를 여러 가지 View로 변환하는 작업을 어렵지 않게 할 수 있다. 앞으로 사용자가 엑셀 다운로드와 같은 기능을 요청하면 상당히 어려운 작업이라고 뻥치고 짧은 시간 내에 구현해 버려라. 이렇게 얻은 시간으로 다른 것을 공부하는데 투자하면 좋을 듯 하다.

다음검색

북마크

댓글 0
댓글쓰기
답글쓰기

댓글 리스트

CAFE

java tip

JAVA에서 Excel 파일 읽기/쓰기

댓글

카페 검색