The solution wasn’t totally obvious, I had to fight through several different examples, so I thought I would put something out on the interscape to help others out. I am definitely not a Java programmer, but this may point you in the right direction if you have a requirement to rip excel documents into database tables. I have borrowed much of the code from different places and put it together for my needs:
I used what I thought were going to be the best maintained libraries out there, but there are many to choose from:
Apache Commons IO, Apache Commons File Upload, Apache POI and Oracle JDBC
A style sheet to make it pretty:
BODY
{
margin-left: auto;
margin-right: auto;
margin-top: auto;
margin-bottom: auto;
background-color: #336699;
color: black;
font-family: Arial, Helvetica, sans-serif;
}
H1
{
color: navy;
font-family: Arial, Helvetica, sans-serif;
font-size: x-large;
font-weight: bolder;
text-transform: capitalize;
}
H2
{
color: navy;
font-family: Arial, Helvetica, sans-serif;
font-size: large;
font-weight: bolder;
text-transform: capitalize;
}
H3
{
color: navy;
font-family: Arial, Helvetica, sans-serif;
font-size: large;
font-weight: lighter;
text-transform: capitalize;
}
a:link {
color: white;
font-weight: bolder;
}
a:visited {
color: white;
font-weight: bolder;
}
.hdr {
background-color: #336699;
color: white;
}
.odd {
background-color: #CCCCFF;
}
.even {
background-color: #FFFFCC;
}
The Start Page (upload.jsp):
<jsp:useBean id="uploadbean" scope="session" class="uploadfile.DbConn"/>
<html>
<head>
<title>File Upload</title>
<link type="text/css" rel="stylesheet" href="./css/sylesheet.css"></link>
</head>
<body>
<h1>Marketing Spreadsheet Loader.</h1>
<form action="uploadWork.jsp" method="post" enctype="multipart/form-data">
<p><br/>
Choose your file :
<input name="myFile" type="file"/><br/>
On the server, save the file as: (Enter something here if you want the file to be saved in a different file name.)
<input name="filename" type="text"/>
</p>
<hr/>
<p>
<input type="submit"/>
<input type="reset"/>
</p>
</form>
<% out.print(uploadbean.getOutput()); %>
</body>
</html>
The jsp where all the work gets done:
<jsp:useBean id="uploadbean" scope="session" class="uploadfile.DbConn"/>
<%@ page import="org.apache.commons.fileupload.*, org.apache.commons.fileupload.servlet.ServletFileUpload, org.apache.commons.fileupload.disk.DiskFileItemFactory, org.apache.commons.io.FilenameUtils, java.util.*, java.io.File, java.lang.Exception"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>File Upload Example</title>
</head>
<body><h1>Data Received at the Server</h1><hr/><p>
<%
if (ServletFileUpload.isMultipartContent(request)){
ServletFileUpload servletFileUpload = new ServletFileUpload(new DiskFileItemFactory());
List fileItemsList = servletFileUpload.parseRequest(request);
String optionalFileName = "";
FileItem fileItem = null;
Iterator it = fileItemsList.iterator();
while (it.hasNext()){
FileItem fileItemTemp = (FileItem)it.next();
if (fileItemTemp.isFormField()){
%>
<b>Name-value Pair Info:</b><br/>
Field name:
<%= fileItemTemp.getFieldName() %><br/>
Field value:
<%= fileItemTemp.getString() %><br/><br/>
<%
if (fileItemTemp.getFieldName().equals("filename"))
optionalFileName = fileItemTemp.getString();
}
else
fileItem = fileItemTemp;
}
if (fileItem!=null){
String fileName = fileItem.getName();
%>
<b>Uploaded File Info:</b><br/>
Content type:
<%= fileItem.getContentType() %><br/>
Field name:
<%= fileItem.getFieldName() %><br/>
File name:
<%= fileName %><br/>
File size:
<%= fileItem.getSize() %><br/><br/>
<%
/* Save the uploaded file if its size is greater than 0. */
if (fileItem.getSize() > 0){
if (optionalFileName.trim().equals(""))
fileName = FilenameUtils.getName(fileName);
else
fileName = optionalFileName;%>
<% uploadbean.insertFile(fileItem.getInputStream()); %>
<% try {
//fileItem.write(saveTo);
%>
<b>The uploaded file has been saved successfully.</b>
<%
catch (Exception e){
%>
<b>An error occurred when we tried to save the uploaded file.</b>
<%
}
}
}
response.sendRedirect("./upload.jsp");
}
%>
</p></body>
</html>
The Java Class that I use
package uploadfile;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.*;
import oracle.sql.BLOB;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class DbConn {
// Holder for the excel spreadsheet. I want to save it in it's original form as
// well as shred it into a database table
private BLOB blob;
// Database connection information
private String url = "jdbc:oracle:thin:@<yourserver>:<yourport>:<yoursid>";
private String username = "<youruser>";
private String pass = "<yourpass>";
private Connection conn = null;
private PreparedStatement ps;
private OracleDataSource ds;
// This is going to puke out the results from what was shredded into
// the database.
private String output;
// This is going to help us get the right information for the file that
// we just loaded into the database
private int originalFileId;
public DbConn() {
}
/**
* Method takes in a file stream our excel spreadsheet
* and passes it to some helper methods to load into the database.
*/
public void insertFile(InputStream pFile) throws SQLException, IOException {
// Get a connection to the database
ds = new OracleDataSource();
ds.setURL(url);
ds.setUser(username);
ds.setPassword(pass);
conn = ds.getConnection();
try {
// Pass the file stream to this helper method. It converts the file
// stream to a blob and loads the original file into the database
// as a blob.
originalFileId = this.insertOriginalFile(pFile, conn);
// This helper method converts the spreadsheet into records that it
// loads into the database.
this.loadFileRecord(originalFileId, conn);
// This method sets a string that displays what records were actually
// loaded into the database. It displays to the user
this.output = viewResults(originalFileId);
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
conn.close();
pFile.close();
}
}
/** Helper method that takes the blob I setup and converts it into
* strings that can be inserted into the database
*/
private void loadFileRecord(int pOriginalFileId,
Connection conn) throws SQLException,
IOException {
// SQL for loading the records into my table.
String stmt =
"insert into file_record(file_record_id, original_file_id, address, reason) values" +
"(file_record_seq.nextval, ?, ?, ?)";
// Prepare the statement
ps = conn.prepareStatement(stmt);
// Setup some of the Apache POI objects with the information from
// my blob.
HSSFWorkbook wb = new HSSFWorkbook(blob.getBinaryStream());
// Look at the first sheet of the workbook becuase that's all I
// care about at this time.
HSSFSheet sheet = wb.getSheetAt(0); // first sheet
// Loop through the rows which should be dynamic
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
HSSFRow row = sheet.getRow(i);
// I only have two data points that I was capturing in my spreadsheet
HSSFCell addressCell = row.getCell(0);
HSSFCell reasonCell = row.getCell(1);
// Setup the insert statement and load it with my values
ps.setInt(1, pOriginalFileId);
ps.setString(2, addressCell.getRichStringCellValue().toString());
ps.setString(3, reasonCell.getRichStringCellValue().toString());
ps.executeUpdate();
//pFile.close();
}
}
/** I want to store the excel spreadsheet in it's original format for
* possible future use and troubleshooting
*/
private int insertOriginalFile(InputStream pFile,
Connection conn) throws IOException,
SQLException {
// Simple stored proc I setup
String stmt = "call p_original_file_ins(?, ?)";
// Prepare the call
CallableStatement cs = conn.prepareCall(stmt);
// Create a temporary blob in the database for the excel spreadsheet
blob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
OutputStream outputStream = blob.setBinaryStream(0L);
byte[] buffer = new byte[blob.getBufferSize()];
int byteread = 0;
while ((byteread = pFile.read(buffer)) != -1) {
outputStream.write(buffer, 0, byteread);
}
outputStream.close();
// Set the parameters
cs.setBlob(1, blob);
// Catch the file id on the way back used later
cs.registerOutParameter(2, OracleTypes.NUMBER);
cs.execute();
return cs.getInt(2);
}
/** Method shows the records that were just loaded into the database
*/
private String viewResults(int pOriginalFileId) throws SQLException {
String stmt = "select a.insert_date, b.address, b.reason " +
"from original_file a inner join file_record b on a.original_file_id = b.original_file_id " +
"where a.original_file_id = ?";
ps = conn.prepareStatement(stmt);
ps.setInt(1,pOriginalFileId);
return this.formatResult(ps.executeQuery(),false);
}
/** Method that makes the result set pretty
*/
private String formatResult(ResultSet rset,
Boolean checkbox) throws SQLException {
// Formats the ResultSet into a pretty html table. If the checkbox
// parameter is passed then it turns into a table surrounded by a form.
// that form accepts parameters and then passses them to the
// runProcManyParam.jsp
StringBuffer sb = new StringBuffer();
ResultSetMetaData rsmd = rset.getMetaData();
int cols;
cols = rsmd.getColumnCount();
if (!rset.next())
sb.append("<p> No matching rows.\n</p>");
else {
int cnt = 0;
String c1, c2, c;
c1 = "#CCCCFF";
c2 = "#FFFFCC";
if (checkbox) {
sb.append("<form name=\"processForm\" action=\"runProcWParam.jsp\" method=\"post\">");
}
sb.append("<TABLE border=0 bgColor='white' cellpading=5 cellspacing=1 style='font-family: Courier New; font-size: 10pt; border: 1 solid #800000'>");
sb.append("<tr bgColor='#336699'>");
for (int j = 1; j <= cols; j++) {
sb.append("<td><b><font color='white'> " +
rsmd.getColumnName(j) + " </font><b></td>");
}
if (checkbox) {
sb.append("<td><input type=\"checkbox\" name=\"multiSel\" " +
"value=\"Check All\" onClick=\"this.value=check(this.form.multiSel);\"></td>");
}
sb.append("</tr>");
do {
if (cnt % 2 == 0)
c = c1;
else
c = c2;
sb.append("<tr bgColor=" + c + ">");
for (int i = 1; i <= cols; i++) {
sb.append("<td>" + rset.getString(i) + "</td>");
}
cnt++;
if (checkbox) {
sb.append("<td><input type=\"checkbox\" name=\"multiSel\" value=\"" +
rset.getString(1) + "\"/></td>");
}
sb.append("</tr>");
} while (rset.next());
sb.append("</table>Number of rows : <b> " + cnt + "</b></br>");
if (checkbox) {
sb.append("<input type=\"hidden\" name=\"formParameter\" value=\"\"/>");
sb.append("<p/> <input type=\"submit\" name=\"submitUpdate\" value=\"Update\"/>");
sb.append("</form>");
}
}
return sb.toString();
}
/** Getter for the results
*/
public String getOutput(){
return this.output;
}
/** Getter for the file id
*/
public int getOriginalFileId(){
return this.originalFileId;
}
}
And then finally the DDL for the database objects:
CREATE TABLE "ORIGINAL_FILE"
("ORIGINAL_FILE_ID" NUMBER,
"INSERT_DATE" DATE,
"ORIGINAL_FILE" BLOB,
CONSTRAINT "ORIGINAL_FILE_PK" PRIMARY KEY ("ORIGINAL_FILE_ID") );
CREATE TABLE "FILE_RECORD"
("FILE_RECORD_ID" NUMBER,
"ORIGINAL_FILE_ID" NUMBER,
"ADDRESS" VARCHAR2(1000 BYTE),
"REASON" VARCHAR2(1000 BYTE),
FOREIGN KEY ("ORIGINAL_FILE_ID")
REFERENCES "ORIGINAL_FILE" ("ORIGINAL_FILE_ID")) ;
CREATE SEQUENCE FILE_RECORD_SEQ;
CREATE SEQUENCE ORIGINAL_FILE_SEQ;
create or replace procedure p_original_file_ins(p_original_file in blob, p_original_file_id out number) as
begin
insert into original_file(original_file_id, insert_date, original_file) values (original_file_seq.nextval,
sysdate, p_original_file) returning original_file_id into p_original_file_id;
end;
No comments:
Post a Comment