Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

xls file

 
Michael.H.Sun
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Friends,
After I run the following code, I can not read the Welcome.xls. Would you explain why? Thanks.
import java.io.*;
import java.lang.*;
public class XlsFile {
public static void main(String[] args) {
try {
File inputFile = new
File("c:/windows/desktop/data.xls");
String [] filenames = inputFile.list ();
File outputFile = new
File("c:/windows/desktop/Welcome.xls");
FileReader in = new FileReader(inputFile);
FileWriter out = new FileWriter(outputFile);
for (int i=0; i<filenames.length; i++)
{
if (filenames [i].endsWith (".xls"))
{
LineNumberReader lnr = new LineNumberReader(in);
String s;
while ( (s = lnr.readLine()) != null ) {
System.out.println(lnr.getLineNumber() + "==> " + s );
out.write(s);
}
lnr.close();
in.close();
out.close();
}
}
}
catch (IOException ie) { System.out.println("input/output error"); }
}
}
 
Cindy Glass
"The Hood"
Sheriff
Posts: 8521
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An .xls file is a complicated combination of spreadsheet data and hidden databases that hold formatting information for all the cells and named ranges and etc. etc. and embedded Visual Basic macros and procedures. If you have ever done anything complicated in Excel you realize just how much is hidden under the covers. As much as I like to participate in MicroSoft bashing, they can be VERY clever.
For you to de-tangle the stuff in an .xls you will probably need to use the Microsoft DLLs somehow.
 
Michael.H.Sun
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cindy,
Thank you so your explanation. I will try.
 
Dirk Mohr
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Micheal!
If you save your Welcome.xls as a csv file (means each item is separated by ; or , or tab, for example
Welcome.csv:
"a";"b";"c" //first row
1;2;3 //second row)
you can read this line by line and identify the items by the used seperator.
Bye, Dirk
 
Cindy Glass
"The Hood"
Sheriff
Posts: 8521
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, what I now know is that Excel is actually a database that holds all the information to display in a spreadsheet. You can use JDBC to get at it using the Microsoft provided driver.
 
Sathyee
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
For fetching data from Excel spreadsheet, you have two ways.
1. By saving the .xsl file as .csv(Comma Seperated Values) and then fetching the calues using string tokenizers.
2. Another way is to treat .xls file as a database and then using JDBC ODBC driver, we can extract the values. See the below example can be used to fetch the values from .xls file,
public void readXLS(File xlsfile) {

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

connStr = "jdbc dbc river={Microsoft Excel Driver (*.xls)};DBQ=" + file;
Connection connection = DriverManager.getConnection(connStr);

Statement statement = connection.createStatement();

// Query statements ...
// Resultset fetches ...

}
catch (SQLException e){
e.printStackTrace();
}

}
}
Note: In the above, we have to use the xls file name as the table name. We have to use the first row of the xls file as the column header and we can fetch the values from the second row onwards.
 
Marilyn de Queiroz
Sheriff
Posts: 9059
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sathyee,

Hi,

Welcome to JavaRanch! Please adjust your display name to meet the JavaRanch Naming Policy.
You can change it here.

Thanks!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic