Java读取Excel文件的几种方法

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表格。优点是：不需要第三方的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 = DriverManager.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.getColumnName(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.err.println(ex.getMessage(); finally con.close(); 执行结果如下：2、 jxl.jarjxl.jar为开源代码，任何运行java虚拟机的操作系统都能使用这个jar包操作excel表格。优点是：不依赖其他任何第三方的库。下载地址：/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; / 源文件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).getContents().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 = sheet.getCell(2, z).getContents();System.out.println(name+,+sex+,+ID); catch(Exception e)e.printStackTrace();执行结果如下：3、 jcom.jarjcom.jar是日本人开发的，也是一个开源项目，下载地址：/projects/jcom/files/latest/download将jcom.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(); JCOMCreateExcel(); 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.Workbooks(); 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.out.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); excel.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.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(filename ); 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); File path = new File(./); String filenames = path.list(); for(int i=0; ifilenames.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( new 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.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.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 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); xlBook.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.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.HSSFWorkbook;/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 POICreateExcel() 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);wb.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