RealGrid HELPC9 RealGrid에 바이너리 엑셀 파일 불러오기(Import)
들어가며
이번 강좌에서는 local에 있는 바이너리 엑셀 파일을 그리드에 불러오는 방법을 소개하겠습니다.
이론
개발환경 구성
Server Side
- eclipse Luna SR2 4.4.2
- Spring 3.1.1
- tomcat 7.0
- Java 7
- JRE - poi, poi-ooxmi, json-lib, jackson-mapper-asl, commons-io, commons-fileupload
Client Side
- jQuery 1.11.1
- jQuery.form
- RealGrid JS 1.0.13
주요 Library
본 강좌는 Java Spring환경에서 구현한 방법으로 Apache에서 제공하는 poi 라이브러리를 사용하였습니다.
POI
아파치 POI(Apache POI)는 아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다.
주로 워드, 엑셀, 파워포인트와 파일을 지원하며 최근의 오피스 포맷인 Office Open XML File Formats (OOXML, 즉 xml 기반의 *.docx, *.xlsx, *.pptx 등) 이나 아웃룩, 비지오, 퍼블리셔 등으로 지원 파일 포맷을 늘려가고 있다.
Client
JavaScript
- uploadToGrid
uploadToGrid함수는 form테그 안에 파일을 등록하고 submit하면 발생하는 이벤트로 jQuery ajaxSubmit을 사용하여 비동기로 처리하였습니다.
본 강좌에서는 데이터의 값에따라 동적으로 그리드를 구성하도록 하였습니다. 데이터를 정상적으로 가져오면 오브젝트의 key 값으로 그리드의 DataField와 DataColumn을 생성합니다.
function uploadToGrid() {
var input = $('#excelFile')[0];
if (!input.value) {
alert("Um, couldn't find the Excel InputFile element.");
} else {
$(this).ajaxSubmit({
dataType : "json",
success : function(data) {
var fieldNames = Object.keys(data.results[0]); //object의 key값을 배열로 반환.
dataProvider.setFields(fieldNames);
gridView.setColumns(dataProvider.getFields());
dataProvider.fillJsonData(data.results);
},
error : function(error) {
console.log(error);
}
});
}
return false;
}
Java
- excelToJson
클라이언트에서 Excel파일을 전송하면 excelToJson controller로 전달 됩니다. 전달 받은 Excel파일을 JSON Array로 변환하여 클라이언트에 리턴하는 역활을 합니다.
엑셀 파일 확장자에 따라 poi.HSSF 또는 poi.XSSF를 사용하여 모든 버전에 대응도록 구현하였습니다.
@RequestMapping(value="excelLoad.do", method = RequestMethod.POST)
public @ResponseBody ModelMap excelToJson(@RequestParam("excelFile") MultipartFile file,
ModelMap model) {
try {
JSONArray jArr = new JSONArray();
JSONObject jObj = new JSONObject();
String fileName = file.getOriginalFilename();
String extension = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
Sheet sh;
if (extension.equalsIgnoreCase("xlsx")) {
XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}else {
HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}
...
생략
return model;
}
실습
먼저 이번 강좌에 필요한 Java 라이브러리를 등록합니다.
pom.xml
excel파일을 Java로 읽어오기 위한 POI 라이브러리.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
JSON클래스 사용과 통신을 위한 라이브러리.
<dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mapper-asl</artifactId> <version>1.9.1</version> </dependency>
Multipart 기능을 위한 Apache commons 라이브러리.
<dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.2.1</version> </dependency>
그리고 Multipart기능을 지원하는 multipartResolver를 스프링 설정 파일에 등록 해주어야 합니다. CommonsMultipartResolver를 multipartResolver로 사용하기 위해 다음과 같이 빈을 등록합니다.
<beans:bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>
클라이언트에서 전송한 파일을 전달받을 Controller를 생성합니다.
Java
파일에 접근하는 방법에는 여러가지가 있습니다.
MultipartHttpServlertRequest를 이용하는 방법.
@ReqeustParam 어노테이션을 이용하는 방법.
빈객체를 사용하여 @ModelAttribute 어노테이션을 이용하는 방법.
본 강좌에서는 @RequestParam을 사용하였습니다.
@RequestMapping(value="excelLoad.do", method = RequestMethod.POST)
public @ResponseBody ModelMap excelToJson(@RequestParam("excelFile") MultipartFile file,
ModelMap model) {
try {
JSONArray jArr = new JSONArray();
JSONObject jObj = new JSONObject();
String fileName = file.getOriginalFilename();
String extension = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
Sheet sh;
if (extension.equalsIgnoreCase("xlsx")) {
XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}else {
HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}
int rowCnt = sh.getPhysicalNumberOfRows();
Row headerRow = sh.getRow(0);
int colCnt = headerRow.getPhysicalNumberOfCells();
String[] colName = new String[colCnt];
for(int colidx = headerRow.getFirstCellNum(), idx=0;
colidx < headerRow.getLastCellNum(); colidx++, idx++){
colName[idx] = headerRow.getCell(colidx).getStringCellValue();
}
for(int rowidx=1; rowidx < rowCnt; rowidx++){
Row row = sh.getRow(rowidx);
for(int colidx = row.getFirstCellNum(), idx=0;
colidx<row.getLastCellNum(); colidx++, idx++){
Cell cell = row.getCell(colidx);
Object value = null;
if(cell != null){
switch(cell.getCellType()) {
case XSSFCell.CELL_TYPE_FORMULA : value = cell.getCellFormula(); break;
case XSSFCell.CELL_TYPE_NUMERIC :
if(DateUtil.isCellDateFormatted(cell)){ //날짜데이터 포멧설정
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else{
value = String.valueOf(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING : value = cell.getStringCellValue(); break;
case XSSFCell.CELL_TYPE_BLANK : value = cell.toString(); break;
case XSSFCell.CELL_TYPE_BOOLEAN : value = cell.getBooleanCellValue(); break;
case XSSFCell.CELL_TYPE_ERROR : value = cell.getErrorCellValue(); break;
}
}
jObj.put(colName[idx], value);
}
jArr.add(jObj);
jObj.clear();
}
model.addAttribute("results", jArr);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return model;
}
파일을 서버로 전송할 화면을 생성해 보겠습니다.
HTML
먼저 파일 전송에 사용할 form태그와 file type의 input태그, submit input태그를 생성합니다.
form태그에 multipart를 사용하기 위해 enctype 속성을 multipart/form-data로 설정합니다.
그리고 그리드를 생성할 div element를 생성합니다.
<form id="excelForm" action="excelLoad.do" method="post" enctype="multipart/form-data"> <input type="file" name="excelFile" id="excelFile" /> <input type="submit" id="fileUpload" value="불러오기" /> </form> <div id="realgrid" ></div>
우선 DataField와 DataColumn이 없는 그리드를 생성합니다.
var gridView, dataProvider;
$(function() {
RealGridJS.setRootContext("lib")
dataProvider = new RealGridJS.LocalDataProvider();
gridView = new RealGridJS.GridView("realgrid");
gridView.setDataSource(dataProvider);
$("#excelForm").submit(uploadToGrid);
});
submit 되었을때 현재 화면에 생성되어 있는 그리드에 데이터를 불러오기 위해 form에 있는 파일을 서버로 전달하여 JSON Array형식으로 전달받는 과정을 비동기로 처리하기위해 jQuery ajaxSubmit을 사용하였습니다.
실제 form submit하였을때 동작할 uploadToGrid함수를 생성합니다.
function uploadToGrid() {
var input = $('#excelFile')[0];
if (!input.value) {
alert("Um, couldn't find the Excel InputFile element.");
} else {
$(this).ajaxSubmit({
dataType : "json",
success : function(data) {
var fieldNames = Object.keys(data.results[0]);
dataProvider.setFields(fieldNames);
gridView.setColumns(dataProvider.getFields());
dataProvider.fillJsonData(data.results);
},
error : function(error) {
console.log(error);
}
});
}
return false;
}
이제 파일 버튼을 클릭하여 Excel파일을 불러옵니다.
그리고 불러오기 버튼을 클릭하면
정상적으로 Excel파일을 읽어온 것을 확인 할 수 있습니다.
전체 소스코드
SCRIPT
<script type="text/javascript" src="js/jquery-1.11.1.js"></script>
<script type="text/javascript" src="js/jquery.form.js"></script>
<script type="text/javascript" src="lib/realgridjs-lic.js"></script>
<script type="text/javascript" src="lib/realgridjs_eval.1.0.14.min.js"></script>
<script type="text/javascript" src="lib/realgridjs-api.1.0.14.js"></script>
<script type="text/javascript" src="lib/jszip.min.js"></script>
<script type="text/javascript">
var gridView, dataProvider;
$(function() {
RealGridJS.setRootContext("lib")
dataProvider = new RealGridJS.LocalDataProvider();
gridView = new RealGridJS.GridView("realgrid");
gridView.setDataSource(dataProvider);
$("#excelForm").submit(uploadToGrid);
});
function uploadToGrid() {
var input = $('#excelFile')[0];
if (!input.value) {
alert("Um, couldn't find the Excel InputFile element.");
} else {
$(this).ajaxSubmit({
dataType : "json",
success : function(data) {
var fieldNames = Object.keys(data.results[0]);
dataProvider.setFields(fieldNames);
gridView.setColumns(dataProvider.getFields());
dataProvider.fillJsonData(data.results);
},
error : function(error) {
console.log(error);
}
});
}
return false;
}
</script>
HTML
<form id="excelForm" action="excelLoad.do" method="post" enctype="multipart/form-data"> <input type="file" name="excelFile" id="excelFile" /> <input type="submit" id="fileUpload" value="불러오기" /> </form> <div id="realgrid"></div>
Java
package test.excell.ctrl.web;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
@Controller
public class ExcellWeb {
@RequestMapping("index")
public String excellUploadPage(){
return "excelUpload";
}
@RequestMapping(value="excelLoad.do", method = RequestMethod.POST)
public @ResponseBody ModelMap excelToJson(@RequestParam("excelFile") MultipartFile file,
ModelMap model) {
try {
JSONArray jArr = new JSONArray();
JSONObject jObj = new JSONObject();
String fileName = file.getOriginalFilename();
String extension = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
Sheet sh;
if (extension.equalsIgnoreCase("xlsx")) {
XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}else {
HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream()); //넘어온 파일Stream
sh = wb.getSheetAt(0);
}
int rowCnt = sh.getPhysicalNumberOfRows();
Row headerRow = sh.getRow(0);
int colCnt = headerRow.getPhysicalNumberOfCells();
String[] colName = new String[colCnt];
for(int colidx = headerRow.getFirstCellNum(), idx=0;
colidx < headerRow.getLastCellNum(); colidx++, idx++){
colName[idx] = headerRow.getCell(colidx).getStringCellValue();
}
for(int rowidx=1; rowidx < rowCnt; rowidx++){
Row row = sh.getRow(rowidx);
for(int colidx = row.getFirstCellNum(), idx=0;
colidx<row.getLastCellNum(); colidx++, idx++){
Cell cell = row.getCell(colidx);
Object value = null;
if(cell != null){
switch(cell.getCellType()) {
case XSSFCell.CELL_TYPE_FORMULA : value = cell.getCellFormula(); break;
case XSSFCell.CELL_TYPE_NUMERIC :
if(DateUtil.isCellDateFormatted(cell)){ //날짜데이터 포멧설정
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else{
value = String.valueOf(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING : value = cell.getStringCellValue(); break;
case XSSFCell.CELL_TYPE_BLANK : value = cell.toString(); break;
case XSSFCell.CELL_TYPE_BOOLEAN : value = cell.getBooleanCellValue(); break;
case XSSFCell.CELL_TYPE_ERROR : value = cell.getErrorCellValue(); break;
}
}
jObj.put(colName[idx], value);
}
jArr.add(jObj);
jObj.clear();
}
model.addAttribute("results", jArr);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return model;
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>test.excell</groupId>
<artifactId>ctrl</artifactId>
<name>ExcellCtrl</name>
<packaging>war</packaging>
<version>1.0.0-BUILD-SNAPSHOT</version>
<properties>
<java-version>1.6</java-version>
<org.springframework-version>3.1.1.RELEASE</org.springframework-version>
<org.aspectj-version>1.6.10</org.aspectj-version>
<org.slf4j-version>1.6.6</org.slf4j-version>
</properties>
<dependencies>
<!-- Spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework-version}</version>
<exclusions>
<!-- Exclude Commons Logging in favor of SLF4j -->
<exclusion>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- AspectJ -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${org.aspectj-version}</version>
</dependency>
<!-- Logging -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${org.slf4j-version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jcl-over-slf4j</artifactId>
<version>${org.slf4j-version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${org.slf4j-version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.15</version>
<exclusions>
<exclusion>
<groupId>javax.mail</groupId>
<artifactId>mail</artifactId>
</exclusion>
<exclusion>
<groupId>javax.jms</groupId>
<artifactId>jms</artifactId>
</exclusion>
<exclusion>
<groupId>com.sun.jdmk</groupId>
<artifactId>jmxtools</artifactId>
</exclusion>
<exclusion>
<groupId>com.sun.jmx</groupId>
<artifactId>jmxri</artifactId>
</exclusion>
</exclusions>
<scope>runtime</scope>
</dependency>
<!-- @Inject -->
<dependency>
<groupId>javax.inject</groupId>
<artifactId>javax.inject</artifactId>
<version>1</version>
</dependency>
<!-- Servlet -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- Test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.7</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.1</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-eclipse-plugin</artifactId>
<version>2.9</version>
<configuration>
<additionalProjectnatures>
<projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
</additionalProjectnatures>
<additionalBuildcommands>
<buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
</additionalBuildcommands>
<downloadSources>true</downloadSources>
<downloadJavadocs>true</downloadJavadocs>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.5.1</version>
<configuration>
<source>1.6</source>
<target>1.6</target>
<compilerArgument>-Xlint:all</compilerArgument>
<showWarnings>true</showWarnings>
<showDeprecation>true</showDeprecation>
</configuration>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.2.1</version>
<configuration>
<mainClass>org.test.int1.Main</mainClass>
</configuration>
</plugin>
</plugins>
</build>
</project>
spring 설정
<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:beans="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
<!-- Enables the Spring MVC @Controller programming model -->
<annotation-driven />
<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
<resources mapping="/resources/**" location="/resources/" />
<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<beans:property name="prefix" value="/WEB-INF/views/" />
<beans:property name="suffix" value=".jsp" />
</beans:bean>
<beans:bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>
<context:component-scan base-package="test.excell.ctrl" />
</beans:beans>