JAVA读取Excel

JAVA读取Excel --用POI与Excel交互 第1章 POI简介 --Jakata Poi HSSF：纯java的Excel解决 在我们实际的开发中，表现层的解决方案虽然有多样，但是IE浏览器已成为最多人使用的浏览器，因为大家都用Windows。在企业办公系统中，常常有客户这样子要求：你要把我们的报表直接用Excel打开(电信系统、银行系统)。或者是：我们已经习惯用Excel打印。 Apache的Jakata项目的POI子项目，目前比较成熟的是HSSF接口，处理MSExcel对象。它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel对象，你可以控制一些属性如sheet,cell等等。 首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。 POI可以到www.apache.org下载到。实际运行时，需要有poi包就可以了。HSSF提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象： HSSFWorkbook excel的文档对象 HSSFSheet excel的表单 HSSFRow excel的行 HSSFCell excel的格子单元 HSSFFont excel字体 HSSFDataFormat 日期格式 在poi1.7中才有以下2项： HSSFHeader sheet头 HSSFFooter sheet尾（只有打印的时候才能看到效果） 和这个样式 HSSFCellStyle cell样式 辅助操作包括 HSSFDateUtil 日期 HSSFPrintSetup 打印 HSSFErrorConstants 错误信息表 以下可能需要使用到如下的类 import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; 先看poi的examples包中提供的最简单的例子，建立一个空xls文件。 import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelSample1 { public static void main(String[] args) throws IOException { //创建一个excel文件 HSSFWorkbook wb= new HSSFWorkbook(); FileOutputStream fileOut= new FileOutputStream("c:\\workbook.xls"); // FileOutputStream fileOut= new FileOutputStream("c:/workbook.xls"); wb.write(fileOut); fileOut.close(); } } 通过这个例子，我们在c盘下建立的是一个空白的xls文件（不是空文件）。在此基础上，我们可以进一步看其它的例子。 import org.apache.poi.hssf.usermodel.*; import java.io.FileOutputStream; import java.io.IOException; public class CreateCells { public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); //建立新HSSFWorkbook对象 HSSFSheet sheet = wb.createSheet("new sheet"); //建立新的sheet对象 HSSFRow row = sheet.createRow((short)0); //在sheet里创建一行，参数为行号（第一行，此处可想象成数组） HSSFCell cell = row.createCell((short)0); //在row里建立新cell（单元格），参数为列号（第一列） cell.setCellvalue(1); //设置cell的整数类型的值 row.createCell((short)1).setCellvalue(1.2); //设置cell浮点类型的值 row.createCell((short)2).setCellvalue("test"); //设置cell字符类型的值 row.createCell((short)3).setCellvalue(true); //设置cell布尔类型的值 HSSFCellStyle cellStyle = wb.createCellStyle(); //建立新的cell样式 cellStyle.setDataFormat(HSSFDataFormat. getBuiltinFormat("m/d/yy h:mm")); //设置cell样式为定制的日期格式 HSSFCell dCell =row.createCell((short)4); dCell.setCellvalue(new Date()); //设置cell为日期类型的值 dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式 HSSFCell csCell =row.createCell((short)5); csCell.setEncoding(HSSFCell.ENCODING_UTF_16); //设置cell编码解决中文高位字节截断 csCell.setCellvalue("中文测试_Chinese Words Test"); //设置中西文结合字符串 row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR); //建立错误cell FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); } } 通过这个例子，我们可以清楚的看到xls文件从大到小包括了HSSFWorkbook HSSFSheet HSSFRow HSSFCell这样几个对象。我们可以在cell中设置各种类型的值。 尤其要注意的是如果你想正确的显示非欧美的字符时，尤其象中日韩这样的语言，必须设置编码为16位的即是HSSFCell.ENCODING_UTF_16，才能保证字符的高8位不被截断而引起编码失真形成乱码。 其他测试可以通过参考examples包中的测试例子掌握poi的详细用法，包括字体的设置，cell大小和低纹的设置等。需要注意的是POI是一个仍然在完善中的公开代码的项目，所以有些功能正在不断的扩充。 感觉上面的操作比较的繁琐，然后就自己写了一个方法。这个方法不需要事先创建row和cell，直接进行cteateCell就可以了，在程序中会自动进行判断，如果不存在的话会创建。 private static void cteateCell(HSSFWorkbook wb,HSSFRow row,short col,short align,String val){ HSSFCell cell = row.createCell(col); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(val); HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(align); cell.setCellStyle(cellstyle); } 对里面的几个参数的说明： short col 应该是你的cell单元格的位置也就是列号； short align 应该是你的对齐方式； String val 应该是你单元格里面要添加的值； 具体的调用如下： HSSFRow row = sheet.createRow((short)1); cteateCell(wb,row,(short)0,HSSFCellStyle.ALIGN_CENTER_SELECTION,"SampleID"); 在上边的例子里我们看到了要设置一个单元格里面信息的格式（例如，要将信息居中）设置的操作如下： HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cell.setCellStyle(cellstyle); 还有我们我们经常会用到的合并单元格，在这里我们也有这样的操作，代码如下： sheet.addMergedRegion(new Region(1,(short)1,2,(short)4)); 这里面我们还要介绍一个经常会遇到的问题，就是怎么来冻结一个窗口。poi也为我们集成了这样的事情了。代码如下： sheet.createFreezePane(1,2); · 在这里我们需要注意的是 1、 该方法是在一个具体的sheet里面来进行操作。 2、 方法createFreezepane;有2个参数。前一个参数代表列；后一个参数代表行。 上边的代码对应的excel文件如下： 我么在画面上看到了明显的两条黑线，这就是冻结的窗口。 然后我们来看一个完整的STRUTS的小例子，在这个例子里面我们要做的事情是要模拟移动公司的网上营业厅里面的一个功能，我们要把一个客户当月的通话和各种信息查询出来，并且生成一张excel报表。首先，我们来看一下网上效果的截图。 然后就是我们具体的代码实现了。 struts-config.xml index.jsp <%@ page contentType="text/html; charset=gb2312" language="java"%>

欢迎进入POI-Excel文件报表系统

进入查询页面

连接数据库的SQLBean,这个bean和我们之前在分页里面用到的bean是一样的。 package bean; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SQLBean { String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=poi_mvc"; Connection con=null; Statement sta=null; public SQLBean() { try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); con=DriverManager.getConnection(url,"sa",""); sta=con.createStatement(); } catch(Exception e) { e.printStackTrace(); } } public ResultSet select(String selects) throws Exception { return sta.executeQuery(selects); } } 为了体现MVC模式，我们在这里面引入了一个存储数据的Bean。EntityBean.java package bean; public class EntityBean { private String sjhm; private String hjlx; private String dfhm; private String qssj; private String thsc; private String thdd; private String ctlx; private String jbhf; private String chf; private String zhf; private String zjls; public EntityBean(String sjhm,String hjlx,String dfhm,String qssj, String thsc,String thdd,String ctlx,String jbhf, String chf,String zhf){ this.sjhm=sjhm; this.hjlx=hjlx; this.dfhm=dfhm; this.qssj=qssj; this.thsc=thsc; this.thdd=thdd; this.ctlx=ctlx; this.jbhf=jbhf; this.chf=chf; this.zhf=zhf; } public String getChf() { return chf; } public void setChf(String chf) { this.chf = chf; } public String getCtlx() { return ctlx; } public void setCtlx(String ctlx) { this.ctlx = ctlx; } public String getDfhm() { return dfhm; } public void setDfhm(String dfhm) { this.dfhm = dfhm; } public String getHjlx() { return hjlx; } public void setHjlx(String hjlx) { this.hjlx = hjlx; } public String getJbhf() { return jbhf; } public void setJbhf(String jbhf) { this.jbhf = jbhf; } public String getQssj() { return qssj; } public void setQssj(String qssj) { this.qssj = qssj; } public String getSjhm() { return sjhm; } public void setSjhm(String sjhm) { this.sjhm = sjhm; } public String getThdd() { return thdd; } public void setThdd(String thdd) { this.thdd = thdd; } public String getThsc() { return thsc; } public void setThsc(String thsc) { this.thsc = thsc; } public String getZhf() { return zhf; } public void setZhf(String zhf) { this.zhf = zhf; } public String getZjls() { return zjls; } public void setZjls(String zjls) { this.zjls = zjls; } } 然后让我们来看看SearchAction,他的主要功能把数据库里的数据提取出来，然后封装到EntityBean里面，在转发到一个新的页面。 package action; import java.sql.ResultSet; import java.util.ArrayList; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; import bean.EntityBean; import bean.SQLBean; public class SearchAction extends Action { public Log log = LogFactory.getLog("Poi_mvc"); public ActionForward execute( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { ArrayList list = new ArrayList(); SQLBean sq = new SQLBean(); String sql = "select * from detial"; try{ ResultSet res = sq.select(sql); while(res.next()){ String sjhm = res.getString("sjhm"); String hjlx = res.getString("hjlx"); String dfhm = res.getString("dfhm"); String qssj = res.getString("qssj"); String thsc = res.getString("thsc"); String thdd = res.getString("thdd"); String ctlx = res.getString("ctlx"); String jbhf = res.getString("jbhf"); String chf = res.getString("chf"); String zhf = res.getString("zhf"); log.info("开始封装数据：手机号码="+sjhm+"呼叫类型" +hjlx+"对方号码"+dfhm+"起始时间"+qssj+"通话时间" +thsc+"通话地点"+thdd+"长途类型"+ctlx+"基本话费" +jbhf+"常话费"+chf+"总话费"+zhf+"总纪录"+i); EntityBean eb = new EntityBean(sjhm,hjlx,dfhm,qssj,thsc,thdd,ctlx,jbhf,chf,zhf); list.add(eb); } res.close(); } catch(Exception e){ e.printStackTrace(); } request.setAttribute("result",list); return mapping.findForward("success"); } } 显示页面detail.jsp <%@ page contenttype="text/html; charset=gb2312" language="java"%> <%@ taglib uri="http://jakarta.apache.org/struts/tags-bean" prefix="bean"%> <%@ taglib uri="http://jakarta.apache.org/struts/tags-html" prefix="html"%> <%@ taglib uri="http://jakarta.apache.org/struts/tags-logic" prefix="logic"%> 模拟效果图： 当我们点击生成excel文件这个超链接的时候，会找到这个DownAction package action; import java.io.OutputStream; import java.sql.ResultSet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; import bean.ExcelBean; import bean.SQLBean; public class DownAction extends Action { public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { SQLBean sq = new SQLBean(); String sql = "select * from detial"; try { String fname = "detial";// Excel文件名 OutputStream os = response.getOutputStream();// 取得输出流 response.reset();// 清空输出流 response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls"); // 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。 response.setContentType("application/msexcel"); // 定义输出类型 ResultSet res = sq.select(sql); ExcelBean eb = new ExcelBean(); eb.createFixationSheet(res, os);// 调用生成excel文件bean res.close(); } catch (Exception e) { System.out.println(e); } return mapping.findForward("display"); } } 在这个action里面我们调用了ExcelBean package bean; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.Region; public class ExcelBean { private HSSFWorkbook wb=null; public ExcelBean() { wb=new HSSFWorkbook(); } public void createFixationSheet(ResultSet res,OutputStream os) throws IOException { HSSFSheet sheet=wb.createSheet("new sheet"); wb.setSheetName(0,"话费详单",HSSFWorkbook.ENCODING_UTF_16); HSSFRow row=sheet.createRow((short)0); sheet.createFreezePane(0,1); cteateCell(wb,row,(short)0,"手机号码"); cteateCell(wb,row,(short)1,"呼叫类型"); cteateCell(wb,row,(short)2,"对方号码"); cteateCell(wb,row,(short)3,"起始时间"); cteateCell(wb,row,(short)4,"通话时间"); cteateCell(wb,row,(short)5,"通话地点"); cteateCell(wb,row,(short)6,"长途类型"); cteateCell(wb,row,(short)7,"基本话费"); cteateCell(wb,row,(short)8,"长话费"); cteateCell(wb,row,(short)9,"总话费"); int ii=0; try { int i=0; ii=res.getMetaData().getColumnCount(); while(res.next()) { i++; HSSFRow row2=sheet.createRow((short)i); for(int j=0;j