Java读取Excel文件的几种方法

1、Java读取Excel文件的几种方法最近单位有个项目需要读取excel文件的内容，特别对java读取excel文件的方法做了一点学习，也为了其他人以后能更简单地开发，少走弯路，特写此文，以下程序经过了我的测试，可以保证程序可用，如果你照搬都不行，可能是你的环境有问题。读取excel文件的常用开源免费方法有以下几种：l JDBC-ODBC Excel Driverl jxl.jarl jcom.jarl poi.jar下面分别对这几种方法分别进行探讨1、 JDBC-ODBC Excel Driver这种方法是将excel看成是数据库进行操作，使用SQL Select语句即可查询excel表格。优

2、点是：不需要第三方的jar包。如下表样首先在控制面板进行数据源ODBC登记具体方法如下： 下面就是代码了。package xuzhe;import java.io.*;import java.sql.*;/java xuzhe.ExcelJDBCpublic class ExcelJDBC public static void main(String args) throws SQLException Connection con = null; try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverM

3、anager.getConnection( "jdbc:odbc:ExcelJDBC" ); Statement st = con.createStatement(); ResultSet rs = st.executeQuery( "Select * from Sheet1$" ); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); System.out.println ("表格列数"+numberOfCol

4、umns ); System.out.println( rsmd.getColumnName(1)+ "," + rsmd.getColumnName(2) + "," + rsmd.getColumnName(3); while (rs.next() for (int i = 1; i <= numberOfColumns; i+) if (i > 1) System.out.print(", "); String columnValue = rs.getString(i); System.out.print(colum

5、nValue); System.out.println(""); rs.close(); st.close(); catch(Exception ex) System.err.print("Exception: "); System.err.println(ex.getMessage(); finally con.close(); 执行结果如下：2、 jxl.jarjxl.jar为开源代码，任何运行java虚拟机的操作系统都能使用这个jar包操作excel表格。优点是：不依赖其他任何第三方的库。下载地址：

6、/pub/mirrors/maven2/net/sourceforge/jexcelapi/jxl/2.6.10/jxl-2.6.10.jar程序如下：package xuzhe;import java.io.File;import jxl.Sheet;import jxl.Workbook;/java -classpath .;E:eclipse3.6.2workspaceCrazyJavalibjxl.jar xuzhe.ExcelJXLpublic class ExcelJXL static String sourceFile = "c:name.xls" / 源文件

7、public static void main(String args) tryWorkbook book = Workbook.getWorkbook(new File(sourceFile);/0代表第一个工作表对象Sheet sheet = book.getSheet(0);int rows = sheet.getRows();int cols = sheet.getColumns();String colname1 = sheet.getCell(0, 0).getContents().trim();String colname2 = sheet.getCell(1, 0).getCo

8、ntents().trim();String colname3 = sheet.getCell(2, 0).getContents().trim();System.out.println(colname1+","+colname2+","+colname3);for (int z = 1; z < rows; z+) /0代表列数，z代表行数String name = sheet.getCell(0, z).getContents();String sex = sheet.getCell(1, z).getContents();String ID

9、= sheet.getCell(2, z).getContents();System.out.println(name+","+sex+","+ID); catch(Exception e)e.printStackTrace();执行结果如下：3、 jcom.jarjcom.jar是日本人开发的，也是一个开源项目，下载地址：将jcom.jar拷贝到classlib目录下，将jcom.dll放到你的JAVA_HOME/bin目录下，否则会出现下面错误。程序如下：package xuzhe;import jp.ne.so_net.ga2.no_ji.jcom

10、.excel8.*;import jp.ne.so_net.ga2.no_ji.jcom.*;import java.io.File;import java.util.Date;public class ExcelJCOM public static void main(String args) throws Exception JCOMReadExcel(); JCOMCreateExcel(); static void JCOMReadExcel() ReleaseManager rm = new ReleaseManager(); try System.out.println("

11、;EXCEL startup."); / if already started, open new window ExcelApplication excel = new ExcelApplication(rm); excel.Visible(true); String Filename = "c:name.xls" ExcelWorkbooks xlBooks = excel.Workbooks(); ExcelWorkbook xlBook = xlBooks.Open(Filename); ExcelWorksheets xlSheets = xlBook.

12、Worksheets(); /第一个工作表 ExcelWorksheet xlSheet = xlSheets.Item(1); ExcelRange xlRange = xlSheet.Cells(); int i; int j; for(j=1;j<=4;j+) for(i=1;i<=3;i+) System.out.print(xlRange.Item(j,i).Value(); if(i<3) System.out.print(","); System.out.println(""); catch(Exception e) e.

13、printStackTrace(); finally rm.release(); static void JCOMCreateExcel() ReleaseManager rm = new ReleaseManager(); try System.out.println("EXCEL startup."); / if already started, open new window ExcelApplication excel = new ExcelApplication(rm); excel.Visible(true); / display any information

14、 System.out.println("Version="+excel.Version(); System.out.println("UserName="+excel.UserName(); System.out.println("Caption="+excel.Caption(); System.out.println("Value="+excel.Value(); ExcelWorkbooks xlBooks = excel.Workbooks(); ExcelWorkbook xlBook = xlBook

15、s.Add(); / create new book / enumurate all files System.out.println ("set infomation of files in current directory to cell ."); ExcelWorksheets xlSheets = xlBook.Worksheets(); ExcelWorksheet xlSheet = xlSheets.Item(1); ExcelRange xlRange = xlSheet.Cells(); xlRange.Item(1,1).Value("fil

16、ename" ); xlRange.Item(2,1).Value("size" ); xlRange.Item(3,1).Value("last modified time"); xlRange.Item(4,1).Value("is directory"); xlRange.Item(5,1).Value("is file"); xlRange.Item(6,1).Value("can read"); xlRange.Item(7,1).Value("can write&

17、quot;); File path = new File("./"); String filenames = path.list(); for(int i=0; i<filenames.length; i+) File file = new File(filenamesi); System.out.println(file); xlRange.Item(1,i+2).Value( file.getName() ); xlRange.Item(2,i+2).Value( (int)file.length() ); xlRange.Item(3,i+2).Value( n

18、ew Date(file.lastModified() ); xlRange.Item(4,i+2).Value( file.isDirectory()?"Yes":"No" ); xlRange.Item(5,i+2).Value( file.isFile()?"Yes":"No" ); xlRange.Item(6,i+2).Value( file.canRead()?"Yes":"No" ); xlRange.Item(7,i+2).Value( file.canWri

19、te()?"Yes":"No" ); char start = 'B' char end = (char)(byte)start + filenames.length - 1); System.out.println("end=" + end + ""); String expression = "=Sum(B2:" + String.valueOf(end) + "2)" System.out.println("expression="

20、+ expression + ""); System.out.println ("embed equation, calculate sum of filesize: "+expression); xlRange.Item(1,filenames.length+2).Value("sum"); xlRange.Item(2,filenames.length+2).Formula(expression); xlRange.Columns().AutoFit(); / fit columns / comment out, if print

21、 out. / output default printer. / System.out.println("print out."); / xlSheet.PrintOut(); / comment out, if book save to file. / if no path, save to(My Documents) / System.out.println / ("save to file. (My Documents)testExcel.xls"); xlBook.SaveAs("testExcel.xls"); xlBoo

22、k.Close(false,null,false); excel.Quit(); System.out.println("thank you ."); catch(Exception e) e.printStackTrace(); finally rm.release(); 执行结果如下：4、 poi.jarpoi.jar是apache旗下的一个开源项目，下载地址：/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip程序如下：package xuzhe;import org.

23、apache.poi.hssf.usermodel.*;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;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.HSSFWor

24、kbook;/public class ExcelPOI /* * param args * throws IOException * java xuzhe.ExcelPOI */SuppressWarnings("deprecation")public static void main(String args) throws IOException / TODO Auto-generated method stubExcelPOI.POICreateExcel();ExcelPOI.POIReadExcel();public static void POICreateEx

25、cel() throws IOExceptionHSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");/0行HSSFRow row = sheet.createRow(short)0);/1列row.createCell(short)1).setCellValue("HelloWorld");FileOutputStream fileOut = new FileOutputStream("c:workbook.xls");w

26、b.write(fileOut);fileOut.close();private static HSSFWorkbook readFile(String filename) throws IOException return new HSSFWorkbook(new FileInputStream(filename);public static void POIReadExcel() throws IOException String fileName = "c:name.xls" HSSFWorkbook wb = ExcelPOI.readFile(fileName);System.out.println("Data dump:n");for (int k = 0; k < wb.getNumberOfSheets(); k+) HSSFSheet sheet = wb.getSheetAt(k);int rows = sheet.getPhysicalNumberOfRows();System.out.println("Sheet " + k +