Java读取Excel文件的几种方法
最近单位有个项目需要读取excel文件的内容，特别对java读取excel文件的方法做了一点学习，也为了其他人以后能更简单地开发，少走弯路，特写此文，以下程序经过了我的测试，可以保证程序可用，如果你照搬都不行，可能是你的环境有问题。
读取excel文件的常用开源免费方法有以下几种：
JDBC-ODBC Excel Driver
jxl.jar
jcom.jar
poi.jar
下面分别对这几种方法分别进行探讨
1、 JDBC-ODBC Excel Driver
这种方法是将excel看成是数据库进行操作，使用SQL Select语句即可查询excel表格。优点是：不需要第三方的jar包。
如下表样
首先在控制面板进行数据源ODBC登记
具体方法如下：
下面就是代码了。
package xuzhe;
import java.io.*;
import java.sql.*;
//java xuzhe.ExcelJDBC
public 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 <= numberOfColumns; i++) {
if (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.jar
jxl.jar为开源代码，任何运行java虚拟机的操作系统都能使用这个jar包操作excel表格。优点是：不依赖其他任何第三方的库。下载地址：http://mirrors.ibiblio.org/pub/mirrors/maven2/net/sourceforge/jexcelapi/jxl 下载最新版本
jxl-*-sources.jar 添加之后可以看到源码
jxl-*.jar jar包
jxl-*-javadoc.jar html版本的sources解释
程序如下：
package xuzhe;
import java.io.File;
import jxl.Sheet;
import jxl.Workbook;
//java -classpath .;E:\eclipse3.6.2\workspace\CrazyJava\lib\jxl.jar xuzhe.ExcelJXL
public 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 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.jar
jcom.jar是日本人开发的，也是一个开源项目，下载地址：http://sourceforge.net/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(i<3)
{
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; i<filenames.length; i++) {
File file = new File(filenames[i]);
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.jar
poi.jar是apache旗下的一个开源项目，下载地址：
http://www.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.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 stub
ExcelPOI.POICreateExcel();
ExcelPOI.POIReadExcel();
}
public static void POICreateExcel() throws IOException
{
HSSFWorkbook 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 = 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 " + 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);
}
}
}
}
}
执行结果如下：
web中使用POI导入导出EXCEL文件的例子
struts1.x的例子，struts2.x可以参考自己修改
1.action的写法
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.struts.action.*;
import org.apache.struts.upload.FormFile;
import org.apache.commons.beanutils.BeanUtils;
public class Action {
/**//*
* 把数据库中的字段导入到Excel ，并生成Excel文档
**/
public ActionForward getDownload(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) throws Exception {
Form fm = (Form) actionForm;
// Excel 文件存放在服务器的相对路径下
String outputFile = request.getRealPath("/tmp/Excel.xls");
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel 工作簿中建一工作表
HSSFSheet sheet = workbook.createSheet("Sheet1");
// 设置单元格格式(文本)
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
// 在索引0的位置创建行（第一行）
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell1 = row.createCell((short) 0);// 第一列
HSSFCell cell2 = row.createCell((short) 1);
HSSFCell cell3 = row.createCell((short) 2);
// 定义单元格为字符串类型
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
// 在单元格中输入数据
cell1.setCellValue("姓名");
cell2.setCellValue("性别");
cell3.setCellValue("年龄");
Connection connection = session.connection();
String sql = "Select t.name, t.sex, t.age from table t where t.sex = ?";
try {
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, fm.getSex());// 传入查询条件
ResultSet rs = ps.executeQuery();// 查询结果存入rs
connection.commit();// 执行SQL
while (rs.next()) {
//设置j行从第二行开始
int j = 1;
row = sheet.createRow((short) j);
//设置i列从第二列开始
for (int i = 1; i <= 3; i++) {
HSSFCell cell = row.createCell((short) (i-1));
// 设置单元格格式
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getString(i));
}
j++;
}
request.setAttribute("message", "文件生成成功！");
} catch (SQLException e) {
request.setAttribute("message", "创建文件失败！");
e.printStackTrace();
}
// 删除路径下同名的Excel 文件
File path = new File(outputFile);
path.delete();
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
// 操作结束，关闭文件
fOut.flush();
fOut.close();
//该处如果Excel过大会影响效率，谁有好的想法可以提出来参考（不过从页面下载完后就会清空）
request.getSession().setAttribute("Download", outputFile);
} catch (Exception ioexception) {
request.setAttribute("message", "创建文件失败！");
return actionMapping.findForward("outJSP");
}
return actionMapping.findForward("outJSP");
}
/**//*
* 从Excel文件中读取数据，并导入到数据库中
**/
public ActionForward getUpload(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// 获取excel 文件
Form fm = (Form) actionForm;
FormFile formfile = fm.getUploadfile();
InputStream inputstream = formfile.getInputStream();
fm.clear();// 清空
Session session = HibernateSession.currentSession();
ArrayList list = new ArrayList();
int input = 0; //导入记数
String name = null;
String sex = null;
String age = null;
try {
//通过得到的文件输入流inputstream创建一个HSSFWordbook对象
HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);//第一个工作表
HSSFRow hssfrow = hssfsheet.getRow(0);//第一行
//遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
for (int i = 0; i < hssfworkbook.getNumberOfSheets(); i++) {
hssfsheet = hssfworkbook.getSheetAt(i);
//遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 1; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
hssfrow = hssfsheet.getRow(j);
//判断是否还存在需要导入的数据
if (hssfrow == null) {
System.out.println("这里已没有数据，在第"+i+"列,第"+j+"行");
break;
}
/** *//**将EXCEL中的第 j 行，第一列的值插入到实例中*/
if (hssfrow.getCell((short) 0) == null) {
name = "";
} else if (hssfrow.getCell((short) 0).getCellType() == 0) {
name = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else {
name = hssfrow.getCell((short) 0).getStringCellValue().trim();
}
/** *//**将EXCEL中的第 j 行，第二列的值插入到实例中*/
//姓名
if(hssfrow.getCell((short) 1) == null){
sex = "";
} else if(hssfrow.getCell((short) 1).getCellType() == 0) {
sex = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else {
sex = hssfrow.getCell((short) 1).getStringCellValue().trim();
}
/** *//**将EXCEL中的第 j 行，第三列的值插入到实例中*/
//姓名
if(hssfrow.getCell((short) 1) == null){
age = "";
} else if(hssfrow.getCell((short) 1).getCellType() == 0) {
age = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
}
//如果EXCEL表格中的数据类型为字符串型
else {
age = hssfrow.getCell((short) 1).getStringCellValue().trim();
}
name = name.trim();
sex = sex.toUpperCase();
if (name.equals("")) {
error.setName(name);
error.setMessage("姓名不能为空");
list.add(error);
continue;
} else {
fm.setName(name);
fm.setSex(sex);
fm.setAge(age);
session.save(fm);
}
//导入成功加1
input++;
}
}
session.saveObjs(list.toArray());
} catch () {
}
}
}
2.Form的写法
import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;
public class Form extends ActionForm {
// 上传的文件
private FormFile _flddo;
public void setUploadfile(FormFile formfile) {
_flddo = formfile;
}
public FormFile getUploadfile() {
return _flddo;
}
public void clear() {
_flddo = null;
}
}
3.上传页面Upload.jsp
<%@ page contentType="text/html; charset=GBK" language="java"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html"%>
<html>
<html:form action="/Action.do?method=getUpload" method="POST" enctype="multipart/form-data">
<html:file property="uploadfile" size="80%" />
<input type="button" value="导 入" οnclick="upload(this.form)" class="buttonGray">
</html:form>
</html>
<script language="javascript">
function upload(obj)
{
if(confirm("您现在选择的是XXX,您确定要导入吗？"))
{
var uploadfile = document.all.uploadfile.value;
if((null == uploadfile) ||( "" == uploadfile))
{
alert("上传文件没有指定！");
return false;
}
obj.action = '<html:rewrite page="/Action.do?method=getUpload"/>';
obj.submit();
}
}
</script>
4.下载页面Download.jsp
<%@ page contentType="text/html; charset=GBK"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-bean" prefix="bean" %>
<%
//获取下载文件
String download = (String) request.getSession().getAttribute("Download");
//清空文件
request.getSession().removeAttribute("Download");
%>
<html>
下传文件 <a href="<%=download %>" name="下载">下载</a>
</html>
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
