Como armazenar/recuperar documentos XLS para/do SQL Server

Como armazenar/recuperar documentos XLS para/do SQL Server

O exemplo abaixo mostra como armazenar e ler dados xls do SQL server.

import java.sql.*;
<B><SPAN lang=EN-US >import </SPAN></B><SPAN lang=EN-US >java.io.*;</SPAN>

<B><SPAN lang=EN-US >public class </SPAN></B><SPAN lang=EN-US >saveXLSToDb {</SPAN>
<B><SPAN lang=EN-US >public static void </SPAN></B><SPAN lang=EN-US >main(String[] args) {</SPAN>
<SPAN lang=EN-US >DB db = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >DB();</SPAN>
<SPAN lang=EN-US >Connection conn=db.dbConnect(</SPAN>
<SPAN lang=EN-US >"jdbc:jtds:sqlserver://localhost:1433/smpp"</SPAN><SPAN lang=EN-US >,</SPAN><SPAN lang=EN-US >"sa"</SPAN><SPAN lang=EN-US >,</SPAN><SPAN lang=EN-US >""</SPAN><SPAN lang=EN-US >);</SPAN>

<SPAN lang=EN-US >db.insertXLS(conn,</SPAN><SPAN lang=EN-US >"d://servlet//jdbc//temp.xls"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >db.getXLSData(conn);</SPAN>
<SPAN lang=EN-US >}</SPAN>

<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >class </SPAN></B><SPAN lang=EN-US >DB {</SPAN>
<B><SPAN lang=EN-US >public </SPAN></B><SPAN lang=EN-US >DB() {}</SPAN>

<B><SPAN lang=EN-US >public </SPAN></B><SPAN lang=EN-US >Connection dbConnect(String db_connect_string,</SPAN>
<SPAN lang=EN-US >String db_userid, String db_password) {</SPAN>
<B><SPAN lang=EN-US >try </SPAN></B><SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >Class.forName(</SPAN><SPAN lang=EN-US >"net.sourceforge.jtds.jdbc.Driver"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >Connection conn = DriverManager.getConnection(</SPAN>
<SPAN lang=EN-US >db_connect_string, db_userid, db_password);</SPAN>

<SPAN lang=EN-US >System.out.println(</SPAN><SPAN lang=EN-US >"conectado"</SPAN><SPAN lang=EN-US >);</SPAN>
<B><SPAN lang=EN-US >return </SPAN></B><SPAN lang=EN-US >conn;</SPAN>

<SPAN lang=EN-US >} </SPAN><B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e) {</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<B><SPAN lang=EN-US >return null</SPAN></B><SPAN lang=EN-US >;</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >public void </SPAN></B><SPAN lang=EN-US >insertXLS(Connection conn,String filename) {</SPAN>
<B><SPAN lang=EN-US >int </SPAN></B><SPAN lang=EN-US >len;</SPAN>
<SPAN lang=EN-US >String query;</SPAN>
<SPAN lang=EN-US >PreparedStatement pstmt;</SPAN>

<B><SPAN lang=EN-US >try </SPAN></B><SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >File file = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >File(filename);</SPAN>
<SPAN lang=EN-US >FileInputStream fis = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >FileInputStream(file);</SPAN>
<SPAN lang=EN-US >len = (</SPAN><B><SPAN lang=EN-US >int</SPAN></B><SPAN lang=EN-US >)file.length();</SPAN>
<SPAN lang=EN-US >query = (</SPAN><SPAN lang=EN-US >"insert into TableImage VALUES(?,?,?)"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >pstmt = conn.prepareStatement(query);</SPAN>
<SPAN lang=EN-US >pstmt.setString(</SPAN><SPAN lang=EN-US >1</SPAN><SPAN lang=EN-US >,file.getName());</SPAN>
<SPAN lang=EN-US >pstmt.setInt(</SPAN><SPAN lang=EN-US >2</SPAN><SPAN lang=EN-US >, len);</SPAN>
<SPAN lang=EN-US >pstmt.setBinaryStream(</SPAN><SPAN lang=EN-US >3</SPAN><SPAN lang=EN-US >, fis, len); </SPAN>
<SPAN lang=EN-US >pstmt.executeUpdate();</SPAN>

<SPAN lang=EN-US >} </SPAN><B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e) {</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >public void </SPAN></B><SPAN lang=EN-US >getXLSData(Connection conn) {</SPAN>

<B><SPAN lang=EN-US >byte</SPAN></B><SPAN lang=EN-US >[] fileBytes;</SPAN>
<SPAN lang=EN-US >String query;</SPAN>
<B><SPAN lang=EN-US >try </SPAN></B><SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >query = </SPAN>
<SPAN lang=EN-US >"select data from tableimage where filename like '%.xls%'"</SPAN><SPAN lang=EN-US >;</SPAN>

<SPAN lang=EN-US >Statement state = conn.createStatement();</SPAN>
<SPAN lang=EN-US >ResultSet rs = state.executeQuery(query);</SPAN>
<B><SPAN lang=EN-US >if </SPAN></B><SPAN lang=EN-US >(rs.next()) {</SPAN>
<SPAN lang=EN-US >fileBytes = rs.getBytes(</SPAN><SPAN lang=EN-US >1</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >OutputStream targetFile=  </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >FileOutputStream(</SPAN>
<SPAN lang=EN-US >"d://servlet//jdbc//newtemp.xls"</SPAN><SPAN lang=EN-US >);</SPAN>

<SPAN lang=EN-US >targetFile.write(fileBytes);</SPAN>
<SPAN lang=EN-US >targetFile.close();</SPAN>
<SPAN lang=EN-US >}</SPAN>

<SPAN lang=EN-US >} </SPAN><B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e) {</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >};</SPAN>