Xls.jsp<%-- Document : Form Created on : Dec 12, 2011, 10:08:48 AM Author : pradeep --%> <%@page contentType="text/html" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page</title> </head> <body> <form name="readxls" action="xls.jsp" method="post" enctype="multipart/form-data"> <center> <h2> READ XLS FILE</h2> <table border="0"> <tr><td>Company Name</td><td><input type ="text" name ="companyname"/></td></tr> <tr><td>Company ID</td><td><input type ="text" name ="companyID"/></td></tr> <tr><td>File Path</td><td><input type ="File" name ="filepath"/></td></tr> <tr><td align="right"><input type="submit" value="Submit"> </td><td align="left"> <input type="reset" name="Reset" value="Reset"/></td></tr> </table> </center> </form> </body> </html>
<%--
Document : Xls
Created on : Dec 12, 2011, 10:49:54 AM
Author : pradeep
--%>
<%@page import="org.apache.commons.fileupload.FileItem"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@page import="org.apache.commons.fileupload.FileItemFactory"%>
<%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="org.apache.commons.io.output.DeferredFileOutputStream" %>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="jxl.Cell"%>
<%@page import="jxl.Sheet"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="jxl.Workbook"%>
<%@page import="java.io.File"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.io.InputStream"%>
<%@page import="java.util.List"%>
<%@page import="java.util.Iterator"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<script type="text/javascript">
function download()
{
// var val=document.getElementById("data").value;
window.location="downloadfile.jsp?filename=errorreport";
}
</script>
</head>
<body>
<right>
<a onclick="download()">Download Error Report</a>
</right>
<center>
<table border ="1">
<tr >
<th>UserName</th>
<th>PassWord</th>
<th colspan="16">Failure Reason</th>
</tr>
<%
StringBuffer sb = new StringBuffer();
sb.append("UserName" + ",");
sb.append("Password" + ",");
sb.append("Company Name" + ",");
sb.append("Company ID" + ",");
sb.append("Failure Reason" + ",");
sb.append("\n");
int count = 0;
String companyname = "";
String companyID = "";
String file_name = "";
String extension = "";
InputStream input = null;
Connection con = null;
FileItemFactory factory = new DiskFileItemFactory();
System.out.println("1");
ServletFileUpload upload = new ServletFileUpload(factory);
System.out.println("2");
List items = null;
try {
items = upload.parseRequest(request);
} catch (Exception e) {
e.printStackTrace();
}
Iterator itr = items.iterator();
while (itr.hasNext()) {
FileItem item = (FileItem) itr.next();
if (item.isFormField()) {
String name = item.getFieldName();
String value = item.getString();
System.out.println(name + " : " + value);
if (name.equals("companyname")) {
companyname = value;
}
if (name.equals("companyID")) {
companyID = value;
}
} else {
file_name = item.getName();
System.out.println("file name" + item.getName());
input = item.getInputStream();
extension = file_name.substring(file_name.indexOf("."));
System.out.println(extension);
}
}
try {
if (extension.equalsIgnoreCase(".xls") || extension.equalsIgnoreCase(".xlsx")) {
String connectionUrl = "";
File inputWorkbook = new File(file_name);
Workbook w;
ResultSet rs;
w = Workbook.getWorkbook(input);
Sheet sheet = w.getSheet(0);
String reason[] = new String[sheet.getRows() + 1];
String rowValues[] = new String[sheet.getColumns()];
System.out.println("no of rows :" + sheet.getRows());
if (sheet.getRows() > 1) {
for (int row = 1; row < sheet.getRows(); row++) {
for (int col = 0; col < sheet.getColumns(); col++) {
Cell cell = sheet.getCell(col, row);
rowValues[col] = cell.getContents();
}
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String connectionURL = "jdbc:sqlserver://Ip:portNo;databaseName=Dbname;user=sa;password=admin";
System.out.println(driver);
System.out.println(connectionURL);
Class.forName(driver);
con = DriverManager.getConnection(connectionURL);
Statement st = con.createStatement();
System.out.println("After connections ");
String username1 = rowValues[0];
String password1 = rowValues[1];
reason[row] = null;
System.out.println("Row values:" + rowValues[0]);
String queryUser = "select username from sampledata where username='" + rowValues[0] + "'";
System.out.println(queryUser);
rs = st.executeQuery(queryUser);
if (rs.next()) {
reason[row] = "userName : " + rowValues[0] + " is already Exists.";
}
st.close();
int res = 0; // for checking weather row is inserted or not
System.out.println("reason inside" + reason[row]);
//inserting the data into database
if (reason[row] == null) {
String insertQuery = "insert into sampledata (username,password,companyName,comapnyId)"
+"values ('" + username1 + "','" + password1 + "','" + companyname + "','" + companyID + "')";
System.out.println(insertQuery);
Statement st2 = con.createStatement();
res = st2.executeUpdate(insertQuery);
st2.close();
if (res == 0) {
reason[row] = "Failed When Inserting";
} else {
count++;
System.out.println("one row inserted");
}
} else if (reason[row] != null || res == 0) {
//If any validation failed it should enter into the else
%>
<tr >
<td align="cener"><h4><font color="Red"/><%= username1%></h4></td>
<td align="cener"><h4><font color="Red"/><%= password1%></h4></td>
<td align="cener" colspan="16"><font color="Red"/> <h4><%= reason[row]%></h4></td>
</tr>
<%
sb.append(username1 + ",");
sb.append(password1 + ",");
sb.append(companyname + ",");
sb.append(companyID + ",");
sb.append(reason[row] + ",");
sb.append("\n");
}
}
%>
<h4>Successfully Inserted Records Are <%= count%></h4>
<h4>Below Records are Fail to Insert with reason please download</h4>
<%
} else {
%>
</table>
<table ><tr><td> <h4>You have uploaded <b><%=file_name%></b></td></tr><br><br>
<tr><td><a>No Data Found in the Uploaded file</a></h4></td></tr>
<td align="right" colspan="19">Click here to back to <b><u><a href="Form.jsp">Upload</a></u></b> Screen</td>
<%
}
}
} catch (Exception e) {
%>
<p align="center"> <% out.println("Pls Select The Given format of xl file");
e.printStackTrace();%> </p>
<%
// con.rollback();
} finally {
if (con != null) {
con.close();
}
if (input != null) {
input.close();
}
}
session.setAttribute("data", sb.toString());
%>
</table>
</center>
</body>
</html>
<%--
Document : Downloadfile
Created on : Dec 12, 2011, 2:23:42 PM
Author : pradeep
--%>
<%@page import="java.io.OutputStream"%>
<%@page import="java.io.File"%>
<%@page import="java.io.FilenameFilter"%>
<%@page import="java.awt.Frame"%>
<%@page import="java.awt.FileDialog"%>
<%@page import="java.io.FileOutputStream"%>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFCell" %>
<%@page import="org.apache.poi.hssf.usermodel.HSSFCellStyle" %>
<%@page import= "org.apache.poi.hssf.usermodel.HSSFDataFormat" %>
<%@page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="java.io.InputStream" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body >
<table width="800" align="center"><tr><td>
<center><table width="700"><tr><td align="center"><h2><
font style="font-weight: 900;font-family:'Lucida Casual';font-size: 20px" color="#7E3517" >
Errors Report</font></h2></td></tr></table></center>
<%
String file_name = request.getParameter("filename");
System.out.println("Filename:"+file_name);
String buffer = session.getAttribute("data").toString();
String date = "";
String filename = "";
SimpleDateFormat df1 = new SimpleDateFormat("dd-MMM-yyyy-kk.mm.ss");
date = df1.format(new java.util.Date());
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet worksheet = wb.createSheet("Rejected Data");
System.out.println("buffer:\n" + buffer);
StringTokenizer rowToken = new StringTokenizer(buffer, "\n");
int rowCount = 0;
HSSFRow row[] = new HSSFRow[rowToken.countTokens()];
while (rowToken.hasMoreTokens()) {
String rowData = rowToken.nextToken();
StringTokenizer columnToken = new StringTokenizer(rowData, ",");
row[rowCount] = worksheet.createRow((short) rowCount);
int col = 0;
while (columnToken.hasMoreTokens()) {
HSSFCell cellA1 = row[rowCount].createCell((short) col++);
cellA1.setCellValue(columnToken.nextToken());
}
rowCount++;
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Cache-Control", "no-cache"); // HTTP 1.1
response.setHeader("Cache-Control", "max-age=0");
response.setHeader("Content-disposition", "attachment; filename=" + file_name + date + ".xls");
OutputStream output = response.getOutputStream();
wb.write(output);
output.close();
%>
<p align="center">File downloaded Successfully at location <%= filename%><br><br><br><br></p>
</div>
<br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br>
</form>
</body>
</html>
