JAVA读取Excel

1 JAVAJAVAJAVAJAVA读取 ExcelExcelExcelExcel --------用 POIPOIPOIPOI与 ExcelExcelExcelExcel交互 第一章 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; 2 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日期的显示格式 3 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)); 4 这里面我们还要介绍一个经常会遇到的问题，就是怎么来冻结一个窗口。poi也为我们集成 了这样的事情了。代码如下： sheet.createFreezePane(1,2); � 在这里我们需要注意的是 一、该方法是在一个具体的 sheet里面来进行操作。 二、方法 createFreezepane;有 2 个参数。前一个参数代表列；后一个参数代表行。 上边的代码对应的 excel文件如下： 我么在画面上看到了明显的两条黑线，这就是冻结的窗口。 然后我们来看一个完整的 STRUTS 的小例子，在这个例子里面我们要做的事情是要模拟移 动公司的网上营业厅里面的一个功能，我们要把一个客户当月的通话和各种信息查询出 来，并且生成一张 excel报表。首先，我们来看一下网上效果的截图。 5 然后就是我们具体的代码实现了。 struts-config.xml index.jsp <%@ page contentType="text/html; charset=gb2312" language="java"%> 6

欢迎进入 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); 7 } } 为了体现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; 8 } 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; 9 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 = newArrayList(); 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); } 10 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; 13 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; 14 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