AKHILESH MISHRA IN OCEAN OF JAVA

Ocean Of Java: Convert .xls into .txt

Tuesday, May 22, 2007

Convert .xls into .txt

The following java program is intended to convert .xls file into .txt file. This is simple java program which uses only java API.

import java.sql.*;
import java.util.*;
import java.io.*;

public class DataConversion
{
public static void main(String[] ak)
{
FileOutputStream fos;
PrintStream p;
try{
//String query="Select * from CCAPS_APP_REJECT_REASONS where rownum<='100'";
String query="SELECT * FROM [Sheet1$]";
//Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection con = DriverManager.getConnection("jdbc:oracle:thin:@10.94.191.142:1521:testdata", "user", "password");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:XLDSN","","");
Statement st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rst=st.executeQuery(query);

fos= new FileOutputStream("myfile.txt");
p=new PrintStream(fos);

ResultSetMetaData rsmd = rst.getMetaData();
int Columns = rsmd.getColumnCount();

while(rst.next())
{

for(int i=1;i<=Columns;i++)
{

if((rst.getString(i))==null)
{
p.print("\"\",");
/*if(i==15)
{
for(int j=1;j<16;j++)
{
p.print(",\"\"");
}
}
else if(i==16)
{

for(int j=1;j<4;j++)
{
p.print(",\"\"");
}
}*/

}

else
{
p.print("\""+rst.getString(i)+"\""+",");


}
}
p.println("\n");
}




//p.println("No. of column="+Columns);
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}

To run this program simply copy and paste on any location, create DSN by name 'XLDSN', create an excel file with some entry, compile the java file and run.

No comments: