Struts
2 excel file upload - converting into XML – Created XML reading through handler and
converting into java object
Create excel file with below data which is mentioned in following screen shot
========================================================
WEB-INF\web.xml
=======================================================
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>Struts2Day1</display-name>
<filter>
<filter-name>struts</filter-name>
<filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class></filter>
<filter-mapping>
<filter-name>struts</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<listener>
<listener-class>org.apache.struts2.tiles.StrutsTilesListener</listener-class>
</listener>
<context-param>
<param-name>tilesDefinitions</param-name>
<param-value>/WEB-INF/tiles.xml</param-value>
</context-param>
</web-app>
src\struts.xml
=====================================================================
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
<constant name="struts.devMode" value="true" />
<constant name="struts.custom.i18n.resources" value="resource.application" />
<constant name="struts.multipart.maxSize" value="1000000" />
<package name="default" namespace="/" extends="struts-default">
<result-types>
<result-type name="tiles"
class="org.apache.struts2.views.tiles.TilesResult" />
</result-types>
<action name="landing_page">
<result name="success" type="tiles">/landing_page</result>
</action>
<action name="excelUploadpage">
<result name="success" type="tiles">/excelFileUploadPage</result>
</action>
<action name="doExcelUpload" method="doExcelUpload" class="com.xyz.abc.actions.UploadAction">
<result name="success" type="tiles">/excelFileUploadSuccess</result>
</action>
</package>
</struts>
WEB-INF\tiles.xml
===================================================
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE tiles-definitions PUBLIC
"-//Apache Software Foundation//DTD Tiles Configuration 2.0//EN"
"http://tiles.apache.org/dtds/tiles-config_2_0.dtd">
<tiles-definitions>
<definition name="baseLayout" template="/jsp/baselayout.jsp">
<put-attribute name="title" value="" />
<put-attribute name="header" value="/jsp/header.jsp" />
<put-attribute name="menu" value="/jsp/menu.jsp" />
<put-attribute name="contentbody" value="" />
<put-attribute name="footer" value="/jsp/footer.jsp" />
</definition>
<definition name="/landing_page" extends="baseLayout">
<put-attribute name="title" value="Landing Page" />
<put-attribute name="contentbody" value="/jsp/welcome.jsp" />
</definition>
<definition name="/excelFileUploadPage" extends="baseLayout">
<put-attribute name="title" value="Excel File Upload" />
<put-attribute name="contentbody" value="/jsp/fileupload.jsp" />
</definition>
<definition name="/excelFileUploadSuccess" extends="baseLayout">
<put-attribute name="title" value="Excel File Upload Success" />
<put-attribute name="contentbody" value="/jsp/exceluploadsuccess.jsp" />
</definition>
</tiles-definitions>
com\xyz\abc\actions\UploadAction.java
==============================================================
package com.xyz.abc.actions;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.SAXException;
import com.opensymphony.xwork2.ActionSupport;
import com.xyz.abc.dto.StudentDataVo;
import com.xyz.abc.handlers.StudentDataHandler;
import com.xyz.abc.util.ExcelToXML;
/**
* @author Pedababu M
*
*/
public class UploadAction extends ActionSupport {
/**
*
*/
private static final long serialVersionUID = 1L;
private File file;
private String contentType;
private String filename;
final Logger LOG = LoggerFactory.getLogger(UploadAction.class);
public void setUpload(File file) {
this.file = file;
}
public void setUploadContentType(String contentType) {
this.contentType = contentType;
}
public void setUploadFileName(String filename) {
this.filename = filename;
}
public String doExcelUpload() {
LOG.info("UploadAction doExcelUpload start");
LOG.info("*** " + file + "\t" + file.length());
LOG.info("filenames:");
LOG.info("*** " + filename);
LOG.info("content types:");
LOG.info("*** " + contentType);
String fileName = "file"+System.currentTimeMillis();
String xmlFileData = ExcelToXMLUtil.getXMLFileDataFromExcel(file);
File xmlFile = ExcelToXMLUtil.createXMLFile(xmlFileData, fileName);
try {
//Create a "parser factory" for creating SAX parsers
SAXParserFactory spfac = SAXParserFactory.newInstance();
//Now use the parser factory to create a SAXParser object
SAXParser sp = spfac.newSAXParser();
StudentDataHandler studentDataHandler = new StudentDataHandler();
InputStream xmlFileInputStream = new FileInputStream(xmlFile);
sp.parse(xmlFileInputStream, studentDataHandler);
StudentDataVo studentDataVo = studentDataHandler.getStudentData();
LOG.info("SAX Student ID::::" + studentDataVo.getStuentId());
LOG.info("SAX Student Name::::" + studentDataVo.getStudentName() );
LOG.info("SAX Street::::" + studentDataVo.getStreet());
LOG.info("SAX City::::" + studentDataVo.getCity());
LOG.info("SAX State::::" + studentDataVo.getState());
LOG.info("SAX Zip::::" + studentDataVo.getZip());
} catch(SAXException ioex) {
ioex.printStackTrace();
} catch(ParserConfigurationException ioex) {
ioex.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
LOG.info("UploadAction doExcelUpload end");
return SUCCESS;
}
}
com\xyz\abc\dto\StudentDataVo.java
====================================================
package com.xyz.abc.dto;
/**
* @author Pedababu M
*
*/
public class StudentDataVo {
private transient String headers;
private String stuentId;
private String studentName;
private String street;
private String city;
private String state;
private String zip;
/**
* @return the headers
*/
public String getHeaders() {
return headers;
}
/**
* @param headers the headers to set
*/
public void setHeaders(String headers) {
this.headers = headers;
}
/**
* @return the stuentId
*/
public String getStuentId() {
return stuentId;
}
/**
* @param stuentId the stuentId to set
*/
public void setStuentId(String stuentId) {
this.stuentId = stuentId;
}
/**
* @return the studentName
*/
public String getStudentName() {
return studentName;
}
/**
* @param studentName the studentName to set
*/
public void setStudentName(String studentName) {
this.studentName = studentName;
}
/**
* @return the street
*/
public String getStreet() {
return street;
}
/**
* @param street the street to set
*/
public void setStreet(String street) {
this.street = street;
}
/**
* @return the city
*/
public String getCity() {
return city;
}
/**
* @param city the city to set
*/
public void setCity(String city) {
this.city = city;
}
/**
* @return the state
*/
public String getState() {
return state;
}
/**
* @param state the state to set
*/
public void setState(String state) {
this.state = state;
}
/**
* @return the zip
*/
public String getZip() {
return zip;
}
/**
* @param zip the zip to set
*/
public void setZip(String zip) {
this.zip = zip;
}
}
com\xyz\abc\handlers\StudentDataHandler.java
===========================================================
package com.xyz.abc.handlers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
import com.xyz.abc.dto.StudentDataVo;
/**
* @author Pedababu M
*
*/
public class StudentDataHandler extends DefaultHandler {
private StudentDataVo studentDataVo;
private String temp;
final Logger log = LoggerFactory.getLogger(StudentDataHandler.class);
//private ArrayList<Account> accList = new ArrayList<Account>();
/*
* When the parser encounters plain text (not XML elements),
* it calls(this method, which accumulates them in a string buffer
*/
public void characters(char[] buffer, int start, int length) {
temp = new String(buffer, start, length);
}
/*
* Every time the parser encounters the beginning of a new element,
* it calls this method, which resets the string buffer
*/
public void startElement(String uri, String localName,
String qName, Attributes attributes) throws SAXException {
log.info("startElement --- uri" + uri);
log.info("startElement --- qName::::" + qName);
log.info("startElement --- localName" + localName);
log.info("startElement --- attributes" + attributes.getLocalName(1));
temp = "";
if (qName.equalsIgnoreCase("studentdata")) {
studentDataVo = new StudentDataVo();
log.info("StudentDataVo created.");
}
}
/*
* When the parser encounters the end of an element, it calls this method
*/
public void endElement(String uri, String localName, String qName)
throws SAXException {
if (qName.equalsIgnoreCase("studentid")) {
studentDataVo.setStuentId(temp);
} else if (qName.equalsIgnoreCase("studentname")) {
studentDataVo.setStudentName(temp);
} else if (qName.equalsIgnoreCase("street")) {
studentDataVo.setStreet(temp);
} else if (qName.equalsIgnoreCase("city")) {
studentDataVo.setCity(temp);
} else if (qName.equalsIgnoreCase("state")) {
studentDataVo.setState(temp);
} else if (qName.equalsIgnoreCase("zip")) {
studentDataVo.setZip(temp);
}
}
public StudentDataVo getStudentData() {
log.info("StudentDataVo getStudentData.");
return studentDataVo;
}
}
com\xyz\abc\util\ExcelToXMLUtil.java
=============================================================
package com.xyz.abc.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.xyz.abc.dto.StudentDataVo;
/**
* @author Pedababu M
*
*/
public class ExcelToXMLUtil {
final static Logger log = LoggerFactory.getLogger(ExcelToXMLUtil.class);
public static String getXMLFileDataFromExcel(File file) {
log.info("ExcelToXMLUtil getXMLFileDataFromExcel start");
StudentDataVo studentDataVo = null;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
log.info("File not found in the specified path.");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
try {
XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
XSSFSheet mySheet = workBook.getSheetAt(0);
int totalRows = mySheet.getPhysicalNumberOfRows();
log.info("total no of rows >>>>"+totalRows);
String headers = null;
boolean isFirstRow = true;
for(Row myRow : mySheet){
studentDataVo = new StudentDataVo();
if (isFirstRow) {
headers = addHeaderRowCells(myRow);
studentDataVo.setHeaders(headers);
} else {
addVoRowCells(studentDataVo, myRow);
}
if(isFirstRow) {
isFirstRow = false;
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
String loanData = generateXMLFile(studentDataVo);
log.info("ExcelToXMLUtil getXMLFileDataFromExcel end");
return loanData;
}
public static void addVoRowCells(StudentDataVo vo, Row row) {
log.info("ExcelToXMLUtil addVoRowCells start");
int cellIndex = 0;
Cell myCell = row.getCell(cellIndex++);
try{
vo.setStuentId(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
myCell = row.getCell(cellIndex++);
try{
vo.setStudentName(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
myCell = row.getCell(cellIndex++);
try{
vo.setStreet(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
myCell = row.getCell(cellIndex++);
try{
vo.setCity(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
myCell = row.getCell(cellIndex++);
try{
vo.setState(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
myCell = row.getCell(cellIndex++);
try{
vo.setZip(myCell.getStringCellValue());
}catch(Exception e){
e.printStackTrace();
}
log.info("ExcelToXMLUtil addVoRowCells end");
}
public static String addHeaderRowCells(Row row) {
log.info("ExcelToXMLUtil addHeaderRowCells start");
int cellIndex = 0;
String header = "";
try{
Cell myCell = row.getCell(cellIndex++);
header = myCell.getStringCellValue();
myCell = row.getCell(cellIndex++);
header += "," + myCell.getStringCellValue();
myCell = row.getCell(cellIndex++);
header += "," + myCell.getStringCellValue();
myCell = row.getCell(cellIndex++);
header += "," + myCell.getStringCellValue();
myCell = row.getCell(cellIndex++);
header += "," + myCell.getStringCellValue();
myCell = row.getCell(cellIndex++);
header += "," + myCell.getStringCellValue();
}catch(Exception e){
e.printStackTrace();
}
log.info("ExcelToXMLUtil addHeaderRowCells end");
return header;
}
public static String generateXMLFile(StudentDataVo studentDataVo) {
log.info("ExcelToXMLUtil generateXMLFile start");
StringBuilder xmlFileData = new StringBuilder();
xmlFileData.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
xmlFileData.append("<studentdata>\r\n");
xmlFileData.append("<studentid>" + studentDataVo.getStuentId() + "</studentid>\r\n");
xmlFileData.append("<studentname>" + studentDataVo.getStudentName() + "</studentname>\r\n");
xmlFileData.append("<street>" + studentDataVo.getStreet() + "</street>\r\n");
xmlFileData.append("<city>" + studentDataVo.getCity() + "</city>\r\n");
xmlFileData.append("<state>" + studentDataVo.getState() + "</state>\r\n");
xmlFileData.append("<zip>" + studentDataVo.getZip() + "</zip>\r\n");
xmlFileData.append("</studentdata>");
log.info(xmlFileData.toString());
log.info("ExcelToXMLUtil generateXMLFile end");
return xmlFileData.toString();
}
public static File createXMLFile(String xmlFileData, String fileName) {
log.info("ExcelToXMLUtil createXMLFile start");
File xmlFile = null;
OutputStream outputstream = null;
try {
File directory = new File("C:\\xml");
// if the directory does not exist, create it
if (!directory.exists()) {
log.info("creating directory: xml");
boolean isDirCreated = directory.mkdir();
if(isDirCreated) {
log.info("DIR created");
}
}
xmlFile = new File("c:\\xml\\" + fileName + ".xml");
outputstream = new FileOutputStream(xmlFile);
outputstream.write(xmlFileData.getBytes());
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
outputstream.close();
} catch(Exception e) {
e.printStackTrace();
}
}
log.info("ExcelToXMLUtil createXMLFile end");
return xmlFile;
}
}
src\log4j.properties
====================================================================
# Direct log messages to a log file
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=${jboss.server.home.dir}/log/uploadexcel.log
log4j.appender.file.MaxFileSize=1MB
log4j.appender.file.MaxBackupIndex=1
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
# Direct log messages to stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
# Root logger option
log4j.rootLogger=INFO, file, stdout
# Log everything. Good for troubleshooting
log4j.logger.org.hibernate=INFO
# Log all JDBC parameters
log4j.logger.org.hibernate.type=ALL
css\uploadexcel.css
=====================================================================
@charset "utf-8";
.text {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
color: #333333;
text-decoration: none;
}
.textbox {
BACKGROUND-COLOR: #ffffff;
BORDER-BOTTOM: #455f55 1px solid;
BORDER-LEFT: #455f55 1px solid;
BORDER-RIGHT: #455f55 1px solid;
BORDER-TOP: #455f55 1px solid;
COLOR: #000000;
FONT-FAMILY: verdana, arial;
FONT-SIZE: 11px;
FONT-WEIGHT: normal;
height: 25px;
WIDTH: 70px
}
.filltextbox {
BACKGROUND-COLOR: #ffffff;
BORDER-BOTTOM: #455f55 1px solid;
BORDER-LEFT: #455f55 1px solid;
BORDER-RIGHT: #455f55 1px solid;
BORDER-TOP: #455f55 1px solid;
COLOR: #000000;
FONT-FAMILY: verdana, arial;
FONT-SIZE: 11px;
FONT-WEIGHT: normal;
height: 25px;
WIDTH: 40px
}
.button {
BORDER-RIGHT: #999999 1px solid; BORDER-TOP: #999999 1px solid; FONT-WEIGHT: bold;
FONT-SIZE: 11px; BORDER-LEFT: #999999 1px solid; COLOR: #666666;
BORDER-BOTTOM: #999999 1px solid; FONT-FAMILY: Arial, Helvetica, sans-serif;
WIDTH: 100px; BACKGROUND-COLOR: #cccccc
}
.imgtagborder {
border-style: none
}
.boxhead {
color: #000000;
text-decoration: none;
}
.border {
BACKGROUND-COLOR: #ffffff;
BORDER-BOTTOM: #455f55 1px solid;
BORDER-LEFT: #455f55 1px solid;
BORDER-RIGHT: #455f55 1px solid;
BORDER-TOP: #455f55 1px solid;
COLOR: #000000;
}
.selectborder {
background: url(../images/select_border.jpg) repeat-x top;
BORDER-BOTTOM: #455f55 1px solid;
BORDER-LEFT: #455f55 1px solid;
BORDER-RIGHT: #455f55 1px solid;
BORDER-TOP: #455f55 1px solid;
border:1px solid;
}
.boxinside_selectrightVM {
line-height:1em;
margin-right:1em;
margin-top:1em;
}
.mttdfontstyle {
color:#000000;
font-weight: bolder;
}
.mtheadtdfontstyle {
color:#FFFFFF;
font-weight: bolder;
}
.tableborder{
border: 1px solid black;
}
.divminheight {
min-height: 20px;
height: 100%;
}
jsp\baselayout.jsp
==========================================================
<%@ taglib uri="http://tiles.apache.org/tags-tiles" prefix="tiles"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title><tiles:insertAttribute name="title" ignore="true" /></title>
<link href="css/uploadexcel.css" rel="stylesheet" type="text/css" />
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%" align="center">
<tr height="15%">
<td><tiles:insertAttribute name="header" />
</td>
</tr>
<tr height="75%">
<td>
<TABLE border="0" cellpadding="0" cellspacing="0" width="100%" height="100%">
<TR>
<td height="80%" width="10%"><tiles:insertAttribute name="menu" /></td>
<td width="90%">
<table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%">
<tr height="70%"><td><tiles:insertAttribute name="contentbody" /></td></tr>
</table>
</td>
</TR>
</TABLE>
</td>
</tr>
<tr height="10%">
<td><tiles:insertAttribute name="footer" />
</td>
</tr>
</table>
</body>
</html>
jsp\exceluploadsuccess.jsp
=============================================================
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
Excel File Upload Successful
jsp\fileupload.jsp
===============================================================
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<s:form action="doExcelUpload" method="post" enctype="multipart/form-data">
<s:file name="upload" label="File"/>
<s:submit/>
</s:form>
jsp\footer.jsp
==============================================================
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<TABLE border="0" cellpadding="0" cellspacing="0" width="100%" height="100%">
<TR>
<TD><center>©Babu Tech Stuff</center></TD>
</TR>
</TABLE>
jsp\header.jsp
=====================================================================
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<table width="100%" height="100%" cellspacing="0" cellpadding="0">
<tr height="50%">
<td width="10%"> </td>
<td width="75%"> </td>
<td width="15%"><span style="float: right"><u>Sign Out</u></span></td>
</tr>
<TR height="50%">
<TD colspan="3">
<div class="divminheight">
<TABLE WIDTH="100%" cellspacing="0" cellpadding="0">
<TR>
<TD bgcolor="#EFEDF1"> Header</TD>
<TD bgcolor="#EFEDF1" align="right"> </TD>
</TR>
</TABLE>
</div>
</TD>
</TR>
</table>
jsp\menu.jsp
=================================================
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<TABLE border="0" cellpadding="0" cellspacing="0" width="100%" height="100%">
<TR height="5%">
<TD> </TD>
</TR>
<TR height="5%">
<TD><CENTER><A href="<s:url action="excelUploadpage"/>" ><b>Excel File Upload</b></A></CENTER></TD>
</TR>
<TR height="90%">
<TD> </TD>
</TR>
</TABLE>
jsp\welcome.jsp
==================================================================
<table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%">
<tr><td> </td></tr>
</table>
index.jsp
==================================================================
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Welcome</title>
</head>
<body>
<center><a href="landing_page.action">Click here to get Excel Upload Home Page</a></center>
</body>
</html>
No comments:
Post a Comment