| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
Tags
- mysql
- Oracle
- checkbox 값처리
- 한국소프트웨어산업협회
- http https
- ant path pattern
- 첫주 및 마지막주 날짜 전체 포함
- update
- String[]
- map api v3
- 접속ip
- ajax async
- eclipse hotdeploy
- 서브쿼리
- jquery selectbox change
- extundelete
- mysql trigger
- remote ip
- ajax 동기방식처리
- 아이폰키보드
- partition by
- KOSA
- String 배열
- 2개 테이블
- TM128
- 컴퓨터 드라이버
- 폐업자에 대한 업종등의 정보내역
- 주소 좌표변환
- group by max
- multipart
Archives
- Today
- Total
하은양 믿음군 효실맘 호홍홍집s
poi를 이용한 엑셀파일 읽어오기 본문
엑셀파일을 서버에 저장하고
그 파일을 불러와서 작업하는 소스..ㅋㅋ
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.sql.DriverManager" %>
<%@ page import = "java.sql.Connection" %>
<%@ page import = "java.sql.Statement" %>
<%@ page import = "java.sql.ResultSet" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "java.sql.Timestamp" %>
<%@ page import = "java.io.*" %>
<%@ page import = "java.io.File" %>
<%@ page import = "org.apache.commons.io.FileUtils"%>
<%@ page import = "java.util.*" %>
<%@ page import = "java.text.*" %>
<%@ page import = "java.net.URLConnection"%>
<%@ page import = "java.net.HttpURLConnection"%>
<%@ page import = "java.net.URL"%>
<%@ page import = "java.net.URLEncoder"%>
<%@ page import = "org.springframework.context.ApplicationContext"%>
<%@ page import = "org.springframework.context.support.ClassPathXmlApplicationContext"%>
<%@ page import = "javax.sql.DataSource"%>
<%@ page import="org.apache.commons.fileupload.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<% out.clear(); %>
<%
String sSql = "";
//////////////////////////////////////////////////////////////////////////////
String sSavePath = "/home/web/RFC3/upload_data/OrgExcel/";
String sFilename = "";
String sAlertMsg = "";
String sGubun = "";
// MultiPart
try{
DiskFileUpload fu = new DiskFileUpload();
fu.setSizeMax(20*1024*1024);
fu.setSizeThreshold(4096);
Hashtable ht = new Hashtable();
try{
fu.setHeaderEncoding("utf-8");
List items = fu.parseRequest(request);
Iterator iter = items.iterator();
String sItmName="";
String sItmValue="";
while(iter.hasNext()){
FileItem item = (FileItem)iter.next();
if(item.isFormField()){
sItmName = item.getFieldName();
// System.out.println("Filename::"+item.getString("utf-8"));
sItmValue = item.getName();
ht.put(sItmName.trim(),sItmValue.trim());
out.println(":::::::::::::::::"+sItmName+"/////"+sItmValue+"
");
}else{
sItmName = item.getFieldName();
sItmValue = item.getName();
if(!sItmValue.equals("")){
if(sItmValue.length() > 1){
sFilename = sItmValue.trim();
String fileExt = sFilename.substring(sFilename.lastIndexOf(".")).replace(".","");
// sYYYYMMDD = sFilename.replace("."+fileExt,"");
if(!"xls".contains(fileExt.toLowerCase())){
sAlertMsg = "파일첨부불가 \\n첨부가능 엑셀파일 (xls)"+fileExt.toLowerCase();
break;
}else{
File upFile = new File(sSavePath+sFilename);
item.write(upFile);
sFilename = upFile.getName();
break;
}
}else{
sAlertMsg = "파일명이 없습니다.";
break;
}
}
}
}
}catch(FileUploadBase.SizeLimitExceededException e){
// System.out.println(e.toString());
sAlertMsg = "첨부파일 용량이 초과되었습니다.(20MB이하 가능)";
}catch(Exception ee){
// System.out.println("용량이외의 오류:::" + ee.toString());
sAlertMsg = "첨부파일 오류 : "+ee.toString();
}
sGubun = (String)ht.get("gubun"); if(sGubun == null) sGubun = "OFFICE";
out.println(":::::::::::::::::"+sGubun+"
");
// No MultiPart
}catch(Exception ee){
// System.out.println("87Line:: "+ee.toString());
sAlertMsg = "Form의 Method가 File 업로드 형식이 아닙니다.";
}
if(sAlertMsg.equals("") && !sFilename.equals("")){
// 업로드된 파일 불러오기
FileInputStream inputStream = null;
// 기본변수 설정
Connection DesConn = null;
Statement DesStmt = null;
PreparedStatement destPsmt = null;
try{
inputStream = new FileInputStream(new File(sSavePath + sFilename));
HSSFWorkbook wb = new HSSFWorkbook(inputStream);
if(wb != null){
HSSFSheet sheet = wb.getSheetAt(0);
if(sheet != null) {
int rowCount = sheet.getLastRowNum();
// DB 연결
ApplicationContext context = new ClassPathXmlApplicationContext("datasource.xml");
DataSource ds = (DataSource)context.getBean("IamDataSource");
DesConn = ds.getConnection();
if(DesConn != null) DesStmt = DesConn.createStatement();
if(sGubun != null && DesConn != null && "ORG".equals(sGubun)){
destPsmt = DesConn.prepareStatement("delete from RFC_COMTNEMINWON");
destPsmt.executeUpdate();
destPsmt.close();
}else if(sGubun != null && DesConn != null && "OFFICE".equals(sGubun)){
destPsmt = DesConn.prepareStatement("delete from RFC_COMTNEMINWONPART");
destPsmt.executeUpdate();
destPsmt.close();
}
Iterator rowIterator = sheet.rowIterator();
int iRowCnt = 0;
while (rowIterator.hasNext()) {
HSSFRow row = (HSSFRow)rowIterator.next();
if(iRowCnt == 0){
iRowCnt++;
continue;
}
Iterator cellIterator = row.cellIterator();
String[] ss = new String[120];
int n=0;
while (cellIterator.hasNext()) {
HSSFCell cell = (HSSFCell)cellIterator.next();
String cellStringValue = null;
/* CellType
CELL_TYPE_ERROR Error Cell type (5)
CELL_TYPE_BOOLEAN Boolean Cell type (4)
CELL_TYPE_BLANK Blank Cell type (3)
CELL_TYPE_FORMULA Formula Cell type (2)
CELL_TYPE_STRING Cell type (1)
CELL_TYPE_NUMERIC Numeric Cell type (0)
*/
if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue();
int intValue = Integer.parseInt(String.valueOf(Math.round(doubleValue)));
cellStringValue = intValue+"";
}else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}else if (cell.getCellType() == 2) {
java.util.Date dateValue = cell.getDateCellValue();
// SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(dateValue != null){
SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd");
cellStringValue = transFormat.format(dateValue);
}else{
cellStringValue = "";
}
}else if (cell.getCellType() == 3) {
cellStringValue = "";
}else if (cell.getCellType() == 4) {
boolean booleanValue = cell.getBooleanCellValue();
cellStringValue = Boolean.toString(booleanValue);
}else if (cell.getCellType() == 5) {
Byte byteValue = cell.getErrorCellValue();
if(byteValue != null){
cellStringValue = byteValue.toString();
}else{
cellStringValue = "";
}
}
if (cellStringValue != null) {
out.println(cellStringValue);
}
ss[n] = cellStringValue;
n++;
}
if(sGubun != null && DesConn != null && "ORG".equals(sGubun)) {
destPsmt = DesConn.prepareStatement(" " +
"INSERT INTO RFC_COMTNEMINWON (EMINWONOP_SID,EMINWONOP_DEPTID,EMINWONOP_DEPTNAME,EMINWONOP_PARID,EMINWONOP_SEQ) "+
"VALUES ( ?, ?, ?, ?, ?)"+
"");
destPsmt.setInt(1, Integer.parseInt(ss[0])); //COMMENT_SID NUMBER(19)
destPsmt.setString(2, ss[1]); // USER_ID VARCHAR2(64)
destPsmt.setString(3, ss[2]); // USER_NICK VARCHAR2(64)
destPsmt.setString(4, ss[3]); // USER_PW VARCHAR2(255)
destPsmt.setString(5, ss[4]); // COMMENT_TITLE VARCHAR2(2
destPsmt.executeUpdate();
destPsmt.close();
}else if(sGubun != null && DesConn != null && "OFFICE".equals(sGubun)){
destPsmt = DesConn.prepareStatement(" " +
"INSERT INTO RFC_COMTNEMINWONPART (EMINWONPART_IDS,EMINWONPART_USERID,EMINWONPART_DEPTID,EMINWONPART_NAME,EMINWONPART_STATUS,EMINWONPART_SEQ,EMINWONPART_PHONE,EMINWONPART_FAX,"+
"EMINWONPART_EMAIL,EMINWONPART_BUSINESS,EMINWONPART_DUTYNAME,EMINWONPART_WORKCODE,EMINWONPART_WORKNM,EMINWONPART_ENGMSE) "+
"VALUES ( ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?, ?, ?, ?)"+
"");
destPsmt.setInt(1, Integer.parseInt(ss[0])); //COMMENT_SID NUMBER(19)
destPsmt.setString(2, ss[1]); // USER_ID VARCHAR2(64)
destPsmt.setString(3, ss[2]); // USER_NICK VARCHAR2(64)
destPsmt.setString(4, ss[3]); // USER_PW VARCHAR2(255)
destPsmt.setString(5, ss[4]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(6, ss[5]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(7, ss[6]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(8, ss[7]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(9, ss[8]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(10, ss[9]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(11, ss[10]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(12, ss[11]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(13, ss[12]); // COMMENT_TITLE VARCHAR2(2
destPsmt.setString(14, ss[13]); // COMMENT_TITLE VARCHAR2(2
destPsmt.executeUpdate();
destPsmt.close();
}
out.println("
");
}
}
}
}catch(Exception ee){
out.println(ee.toString());
}finally{
if(inputStream != null){
inputStream.close();
}
if (DesStmt != null) try { DesStmt.close(); } catch(SQLException ex) {}
if (destPsmt != null) try { destPsmt.close(); } catch(SQLException ex) {}
if (DesConn != null) try { DesConn.close(); } catch(SQLException ex) {}
}
}
%>
<%=sAlertMsg%>
'가벼운 배움 > jsp' 카테고리의 다른 글
| 파일다운로드 취약점관련 (0) | 2015.07.10 |
|---|---|
| javac 인코딩 변경해서 Compile하기 (0) | 2015.07.09 |
| jstl 배열의 배열불러오기 (0) | 2015.01.22 |
| jstl 가변 변수 설정하기^^ (0) | 2015.01.13 |
| 간단한 Sendmail을 이용한 메일보내기 (0) | 2014.12.26 |