Java读取Excel文件的几种方法.docx

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

2、excel表格。优点是：不需要第三方的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 = DriverMana

3、ger.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 (表格列数+numberOfColumns ); System.out.println( rsmd.getCol

4、umnName(1)+ , + rsmd.getColumnName(2) + , + rsmd.getColumnName(3); while (rs.next() for (int i = 1; i 1) System.out.print(, ); String columnValue = rs.getString(i); System.out.print(columnValue); System.out.println(); rs.close(); st.close(); catch(Exception ex) System.err.print(Exception: ); System.

5、err.println(ex.getMessage(); finally con.close(); 执行结果如下：2、jxl.jarjxl.jar为开源代码，任何运行java虚拟机的操作系统都能使用这个jar包操作excel表格。优点是：不依赖其他任何第三方的库。下载地址： :/mirrors.ibiblio.org/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.Wor

6、kbook;/java -classpath .;E:eclipse3.6.2workspaceCrazyJavalibjxl.jar xuzhe.ExcelJXLpublic class ExcelJXL static String sourceFile = c:name.xls; / 源文件 public static void main(String args) try Workbook book = Workbook.getWorkbook(new File(sourceFile); /0代表第一个工作表对象 Sheet sheet = book.getSheet(0); int ro

7、ws = sheet.getRows(); int cols = sheet.getColumns(); String colname1 = sheet.getCell(0, 0).getContents().trim(); String colname2 = sheet.getCell(1, 0).getContents().trim(); String colname3 = sheet.getCell(2, 0).getContents().trim(); System.out.println(colname1+,+colname2+,+colname3); for (int z = 1;

8、 z rows; z+) /0代表列数，z代表行数 String name = sheet.getCell(0, z).getContents(); String sex = sheet.getCell(1, z).getContents(); String ID = sheet.getCell(2, z).getContents(); System.out.println(name+,+sex+,+ID); catch(Exception e) e.printStackTrace(); 执行结果如下：3、jcom.jarjcom.jar是日本人开发的，也是一个开源工程，下载地址： :/将jc

9、om.jar拷贝到classlib目录下，将jcom.dll放到你的JAVA_HOME/bin目录下，否那么会出现下面错误。程序如下：package xuzhe;import jp.ne.so_net.ga2.no_ji.jcom.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(); JC

10、OMCreateExcel(); static void JCOMReadExcel() ReleaseManager rm = new ReleaseManager(); try System.out.println(EXCEL startup.); / if already started, open new window ExcelApplication excel = new ExcelApplication(rm); excel.Visible(true); String Filename = c:name.xls; ExcelWorkbooks xlBooks = excel.Wo

11、rkbooks(); ExcelWorkbook xlBook = xlBooks.Open(Filename); ExcelWorksheets xlSheets = xlBook.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(i3) System.o

12、ut.print(,); System.out.println(); catch(Exception e) e.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); ex

13、cel.Visible(true); / display any information 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 = xlBooks.Ad

14、d(); / 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(filename ); xlRange.Item(

15、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); File path = new File(./); String filenames = path.list(); for(int i=0; ifilenames.length; i+

16、) 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( new Date(file.lastModified() ); xlRange.Item(4,i+2).Value( file.isDirectory()?Yes:No ); xlRange.Item(5,i+2).Value( fi

17、le.isFile()?Yes:No ); xlRange.Item(6,i+2).Value( file.canRead()?Yes:No ); xlRange.Item(7,i+2).Value( file.canWrite()?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.

18、println(expression= + 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 out. / output d

19、efault 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); xlBook.Close(false,null,false); excel.Quit(); System.out.

20、println(thank you .); catch(Exception e) e.printStackTrace(); finally rm.release(); 执行结果如下：4、poi.jarpoi.jar是apache旗下的一个开源工程，下载地址： :/ apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip程序如下：package xuzhe;import org.apache.poi.hssf.usermodel.*;import java.io.FileInputStream;import java.

21、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.HSSFWorkbook;/public class ExcelPOI /* * param args * throws IOException * jav

22、a xuzhe.ExcelPOI */ SuppressWarnings(deprecation) public static void main(String args) throws IOException / TODO Auto-generated method stub ExcelPOI.POICreateExcel(); ExcelPOI.POIReadExcel(); public static void POICreateExcel() throws IOException HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet

23、 = 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); wb.write(fileOut); fileOut.close(); private static HSSFWorkbook readFile(String filename) throws IOExceptio

24、n 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)

25、; int rows = sheet.getPhysicalNumberOfRows(); System.out.println(Sheet + k + + wb.getSheetName(k) + has + rows + row(s).); for (int r = 0; r rows; r+) HSSFRow row = sheet.getRow(r); if (row = null) continue; int cells = row.getPhysicalNumberOfCells(); System.out.println(nROW + row.getRowNum() + has

26、+ cells + cell(s).); for (int c = 0; c cells; c+) HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType() case HSSFCell.CELL_TYPE_FORMULA: value = FORMULA value= + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = NUMERIC value= + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = STRING value= + cell.getStringCellValue(); break; default: System.out.println(CELL col= + cell.getColumnIndex() + VALUE= + value); 执行结果如下：以上就是我的一点心得体会，有误的地方请指正，来信发xuzhe_hnyahoo 。