【Java】JXL和POI操作Excel
一、Java中常见的用来操作Excl的方式一般有2种：jxl和poi。
二、两种方式区别在于：
JXL只能对Excel进行操作,属于比较老的框架，它只支持到Excel 95-2000的版本。现在已经停止更新和维护。
POI是apache的项目,可对微软的Word,Excel,Ppt进行操作,包括office2003和2007,Excl2003和2007。poi现在一直有更新。所以现在主流使用POI。
三、下面分别使用两者代码进行常见操作：
1.JXL
public void outputExcel(HttpServletRequest request, HttpServletResponse response,String title)throws IOException { //获取输出流 OutputStream os = response.getOutputStream(); HttpSession session=request.getSession(); String oprator=(String)session.getAttribute("yhxm"); //设置编码 response.setHeader("Content-disposition", "attachment; filename="+"cprdpxx"+".xls");// 设定输出文件头 request.setCharacterEncoding("utf-8"); //设置文件格式 response.setContentType("application/vnd.ms-excel;charset=utf-8"); //加标题 //标题字体 //设置单元格字体，位置 jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.BOLD, true); jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc); try { wcfFC.setAlignment(jxl.format.Alignment.CENTRE); wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //字段字体 jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK); jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1); wcfFC1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50); //设置字体位置 wcfFC1.setAlignment(jxl.format.Alignment.CENTRE); wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); ////////////////////////////////////////////// //2013/7/17 //标题字体 jxl.write.WritableFont wfc3 = new jxl.write.WritableFont(WritableFont.COURIER, 14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK); jxl.write.WritableCellFormat wcfFC3 = new jxl.write.WritableCellFormat(wfc3); wcfFC3.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50); //设置字体位置 wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //////////////////////////////////////////////// //查询结果字体 jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat(); wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); WritableWorkbook wbook = Workbook.createWorkbook(os); //设置默认字体 WritableFont font=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat cFormat = new WritableCellFormat(font); cFormat.setAlignment(jxl.format.Alignment.CENTRE); cFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50); cFormat.setBackground(Colour.WHITE); //2013/7/17 //设置信息头栏字体 WritableFont font11=new WritableFont(WritableFont.COURIER, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat cFormat11 = new WritableCellFormat(font11); cFormat11.setAlignment(jxl.format.Alignment.CENTRE); cFormat11.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.GRAY_50); cFormat11.setBackground(Colour.WHITE); //写sheet名称 WritableSheet wsheet = wbook.createSheet("长江大学职称评审投票信息统计表", 0); //设置单元格默认列宽 wsheet.getSettings().setDefaultColumnWidth(10); wsheet.getSettings().setDefaultRowHeight(350); //设置sheet页面水平打印 wsheet.getSettings().setHorizontalCentre(true); //默认为横向打印//2013/7/17 wsheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0.5d,0.5d); /** * lrr 2014-05-27 BEGIN * 功能描述：设置页码格式：第 X 页（共 X 页） */ // wsheet.setFooter("", "&P", "");//过时的方法 ··HeaderFooter footer = new HeaderFooter(); ··Contents contentsFooter = footer.getCentre(); contentsFooter.setFontSize(10); contentsFooter.append("第 "); contentsFooter.appendPageNumber(); contentsFooter.append(" 页 （ 共 "); contentsFooter.appendTotalPages(); contentsFooter.append(" 页 ）"); //设置打印标题行 // wsheet.getSettings().setVerticalFreeze(3); SheetSettings ss=wsheet.getSettings(); ss.setFooter(footer);// 设置页脚 /** * lrr 2014-05-27 END * 功能描述：设置页码格式：第 X 页（共 X 页） */ // ss.setPrintTitles(0,2,0,10); ss.setPrintTitlesRow(0,2);//设置固定打印标题 ss.setOrientation(PageOrientation.LANDSCAPE); wsheet.setPageSetup(PageOrientation.LANDSCAPE); //标题 wsheet.mergeCells(0, 0, 10, 0); /** * lrr 2014-05-24 改 BEGIN * 功能描述：修改投票信息统计表格式 */ wsheet.addCell(new jxl.write.Label(0, 0, "长江大学职称评审投票信息统计表",wcfFC3));//设置统计表标题 WritableFont font1=new WritableFont(WritableFont.COURIER, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat cFormat1 = new WritableCellFormat(font1); cFormat1.setAlignment(jxl.format.Alignment.RIGHT); cFormat1.setBorder(Border.NONE,BorderLineStyle.THIN,Colour.GRAY_50); cFormat1.setBackground(Colour.WHITE); wsheet.addCell(new jxl.write.Label(0,1,"评审日期："+(new SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date())) ,cFormat1));//合计列 /** * lrr 2014-05-24 改 END * 功能描述：修改投票信息统计表格式 */ /** * 以下为从数据库中查询数据添加到wsheet中 */ PersistenceManagerOfZcps pmoz=new PersistenceManagerOfZcps(); String zsql="select count(*) from view_cprdp where 1=1 "+title;//总人数 //String xb_n_sql="select count(*) from view_cprxx where xb='1'";//性别男 人数 String hj="";//合计 String sql="select * from view_cprdp where 1=1 "+title; // String year=request.getParameter("year"); sql+="order by year_dm desc,zcbh asc,ls,ty_num desc,cpbh"; // //System.out.println("sql="+sql); ResultSet rs=pmoz.executeQuery(sql); List<zc_cprdpxx> cprdp_list=new ArrayList<zc_cprdpxx>(); int zs = 0;//参评人总人数 int ns=0;//男参评人数 try { while(rs.next()) { zc_cprdpxx zp=new zc_cprdpxx(); PersistenceManagerOfZcps pm=new PersistenceManagerOfZcps(); zp.setYear(rs.getString("year_mc")==null?"":rs.getString("year_mc").trim()); zp.setCpbh(rs.getString("cpbh")==null?"":rs.getString("cpbh").trim()); zp.setCpxm(rs.getString("cpxm")==null?"":rs.getString("cpxm").trim()); zp.setLxdh(rs.getString("lxdh")==null?"":rs.getString("lxdh").trim()); zp.setTy_num(rs.getString("ty_num")==null?"":rs.getString("ty_num").trim()); zp.setBty_num(rs.getString("bty_num")==null?"":rs.getString("bty_num").trim()); zp.setQq_num(rs.getString("qq_num")==null?"":rs.getString("qq_num").trim()); zp.setSftg(rs.getString("sftg")==null?"":rs.getString("sftg").trim()); zp.setLs(rs.getString("ls")==null?"":rs.getString("ls").trim()); zp.setZwm(rs.getString("zwm_mc")==null?"":rs.getString("zwm_mc").trim());//2013/7/21 zp.setZcbh(pm.getPureCode_zcm(rs.getString("zcbh")==null?"":rs.getString("zcbh").trim())); zp.setCprzt(pm.getPureCode("code_zczt", rs.getString("zcbh").substring(1, 2))); pm.close(); //zp.setNl(rs.getString("nl")==null?"":rs.getString("nl").trim()); cprdp_list.add(zp); } rs=pmoz.executeQuery(zsql); while(rs.next()) { zs=rs.getInt(1); } rs.close(); }catch (SQLException e) { os.close(); // TODO Auto-generated catch block this.cwts("导出失败，请稍后重试！", response); e.printStackTrace(); } finally { pmoz.close();//关闭数据连接 } //hj="评委总人数为："+String.valueOf(zs)+" 性别 男："+String.valueOf(ns)+" 女："+String.valueOf(zs-ns); wsheet.mergeCells(0, 1, 10, 1);//合并0到10单元格 //wsheet.addCell(new jxl.write.Label(0,1,hj ,cFormat));//合计列 // 设置固定的打印标题//2013/7/17 //// wsheet.getSettings().setPrintArea(0, 2, 0,7); // SheetSettings setting = wsheet.getSettings(); // setting.setPrintTitlesRow(0, 3); int i = 0; int j=0; //String[] colum={"年度","参评人编号","单位名称","参评人姓名","性别","申报职称级别","申报职称名称"}; //2013/7/17修改 String[] colum={"年度", "参评人编号","工作单位","参评人姓名", "申报职称","申报状态","轮数","同意数","不同意数","弃权数","是否通过"}; for (i = 0; i <11; i++) { // 加入行字段名 wsheet.addCell(new jxl.write.Label(i, 2, colum[i], cFormat11)); } for(i=3;i<cprdp_list.size()+3;i++) { wsheet.addCell(new jxl.write.Label(0,i, cprdp_list.get(i-3).getYear(), cFormat)); wsheet.addCell(new jxl.write.Label(1,i, cprdp_list.get(i-3).getCpbh(), cFormat)); wsheet.addCell(new jxl.write.Label(2,i, cprdp_list.get(i-3).getLxdh(), cFormat)); wsheet.addCell(new jxl.write.Label(3,i, cprdp_list.get(i-3).getCpxm(), cFormat)); wsheet.addCell(new jxl.write.Label(4,i, cprdp_list.get(i-3).getZwm(), cFormat)); wsheet.addCell(new jxl.write.Label(5,i, cprdp_list.get(i-3).getCprzt(), cFormat)); wsheet.addCell(new jxl.write.Label(6,i, cprdp_list.get(i-3).getLs(), cFormat)); wsheet.addCell(new jxl.write.Label(7,i, cprdp_list.get(i-3).getTy_num(), cFormat)); wsheet.addCell(new jxl.write.Label(8,i, cprdp_list.get(i-3).getBty_num(), cFormat)); wsheet.addCell(new jxl.write.Label(9,i, cprdp_list.get(i-3).getQq_num(), cFormat)); wsheet.addCell(new jxl.write.Label(10,i, cprdp_list.get(i-3).getSftg(), cFormat)); } wsheet.mergeCells(0, i, 10, i);//合并0到10单元格 /** * lrr 2014-05-24 增 BEGIN * 功能描述：增加四行：1.监票人 2.评审委员会主任 */ wsheet.addCell(new jxl.write.Label(0,i,"制表人："+oprator ,cFormat1));//合计列 i = i+1; wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格 wsheet.addCell(new jxl.write.Label(0,i,"监票人： " ,cFormat1));//合计列 i = i+2; wsheet.mergeCells(0, i, 10, i+1);//合并两行并同时合并单元格 wsheet.addCell(new jxl.write.Label(0,i,"评审委员会主任： " ,cFormat1));//合计列 /** * lrr 2014-05-24 增 END * 功能描述：增加两行：1.监票人 2.评审委员会主任 */ wbook.write(); wbook.close(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); //this.cwts("导出失败，请稍后重试！", response); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); //this.cwts("导出失败，请稍后重试！", response); }finally{ try{ os.close(); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); //this.cwts("导出失败，请稍后重试！", response); } } }
2.POI
public static void main(String[] args) throws IOException { //新建文件输出流对象 FileOutputStream out = new FileOutputStream("F:/poitest.xls"); //新建workbook HSSFWorkbook wb = new HSSFWorkbook(); //新建sheet HSSFSheet sheet = wb.createSheet(); //新建行 HSSFRow row = sheet.createRow(2); //设置行高 row.setHeightInPoints(20); //新建单元格 HSSFCell cell = row.createCell(2); //创建整个文本的字体对象,workbook创建 HSSFFont cnFont = wb.createFont(); //设置字体行高，字体名字 cnFont.setFontHeightInPoints((short)10); cnFont.setFontName("隶书"); //将文本字面格式用到单元格上,新建单元格风格 HSSFCellStyle cnStyle = wb.createCellStyle(); cnStyle.setFont(cnFont); cell.setCellStyle(cnStyle); //单元格内文本对象新建，HSSFRichTextString的应用 HSSFRichTextString richText = new HSSFRichTextString("中文字体测试"); cell.setCellValue(richText); //再建一个单元格,重复上面的设置 HSSFCell enCell = row.createCell(3); HSSFFont enFont = wb.createFont(); enFont.setFontHeightInPoints((short) 10); enFont.setFontName("Arial Black"); HSSFCellStyle enStyle = wb.createCellStyle(); enStyle.setFont(enFont); enCell.setCellStyle(enStyle); enCell.setCellValue(new HSSFRichTextString("English font test")); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); //输出 //设置边框 sheet.setDisplayGridlines(true); //设置打印的边框 sheet.setPrintGridlines(false); //设置打印对象 HSSFPrintSetup printSetup = sheet.getPrintSetup(); //设置页边距 printSetup.setHeaderMargin((double) 0.44); // 页眉 printSetup.setFooterMargin((double) 0.2);//页脚 //设置页宽 printSetup.setFitWidth((short)1); printSetup.setFitHeight((short)1000); //设置打印方向，横向就是true printSetup.setLandscape(false); //设置A4纸 printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印，关闭流 wb.write(out); out.close(); System.out.println("ok"); }
