导出excel时,我以前使用的是POI,如下:
public class Test { private void exportEXCEL(List<D502> d502s) { Field[] fields = d502s.get(0).getClass().getDeclaredFields(); Workbook wb = new HSSFWorkbook(); try { FileOutputStream fileOut = new FileOutputStream("C:/Users/Administrator/Desktop/abc.xls"); Sheet sheet = wb.createSheet(); sheet.setColumnWidth(2, 5120); //System.out.println(sheet.getColumnWidth(2)); for(int i = 0; i < d502s.size(); i++) { Row row = sheet.createRow(i); for(Field field : fields ) { Class<?> typeClass = field.getType(); String typeName = typeClass.getName(); int idx = typeName.indexOf("."); if(idx != -1) { Object obj = typeClass.newInstance(); if(obj instanceof Date) { Cell cell = row.createCell(2); CreationHelper ch = wb.getCreationHelper(); CellStyle cs = wb.createCellStyle(); cs.setIndention((short)50); cs.setDataFormat(ch.createDataFormat().getFormat("yyyy-MM-dd")); cell.setCellStyle(cs); cell.setCellValue(d502s.get(i).getD50209()); } else if(obj instanceof String) { Cell cell = row.createCell(1); cell.setCellValue(d502s.get(i).getD50202()); } } else { Cell cell = row.createCell(0); cell.setCellValue(d502s.get(i).getD50200()); } } } wb.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } } private static Connection getConn() { String dbUrl = "jdbc:oracle:thin:@192.168.1.111:1521:ORCL"; String theUser = "user"; String thePw = "123"; Connection conn = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); try { conn = DriverManager.getConnection(dbUrl, theUser, thePw); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } public static void main(String[] args) { Test test = new Test(); test.exportEXCEL(test.getD502s()); } private List<D502> getD502s() { Connection conn = getConn(); PreparedStatement ps; List<D502> d502s = new ArrayList<D502>(); try { ps = conn.prepareStatement("select * from d502"); ResultSet rs = ps.executeQuery(); D502 d502 = null; while(rs.next()) { d502 = new D502(); d502.setD50200(rs.getLong("d502_00")); d502.setD50202(rs.getString("d502_02")); d502.setD50209(rs.getDate("d502_09")); d502s.add(d502); } } catch (Exception e) { e.printStackTrace(); } return d502s; } }
2、使用POI的好处是,它可以运行于各个系统平台上,如:Windows、Linux、Unix等;但是,在我们通常使用的Windows中又显得笨重,最近发现,在jsp页面中本身提供了导出excel的方法,即:设置头部信息为:contentType="application/msexcel;utf-8",或者是在跳转到这个需要导出的页面中使用
response.setContentType("application/msexcel;charset=UTF-8");
response.setHeader("Content-disposition","inline;
filename=deliverydj.xls");
request.getRequestDispatcher("/report/myExcel.jsp").forward(request,
response);
这样的话,这个页面就是一个”假的“excel页面。
3、在我现在的项目中使用的就是jsp的excel,但是这样有一个坏处!需要从后台查询出内容(本身显示给用的的jsp页面查了一次,导出时又查了一次)!因此,我又找到了另外一种方式,直接可以在显示给用户的jsp中改变头部消息,导出本页面的table数据到excel中(在这里我加入了一个js脚本,下面会用到):
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> <script type="text/javascript" scr="js/jquery-1.10.2.js"></script> </head> <body> <% String exportToExcel = request.getParameter("exportToExcel"); if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "inline; filename=" + "excel.xls"); } %> <table align="left" border="2" id="datatable"> <thead> <tr bgcolor="lightgreen"> <th>1</th> <th>2</th> <th>3</th> </tr> </thead> <tbody> <% for (int i = 0; i < 10; i++) { %> <tr bgcolor="lightblue"> <td align="center"><%=i + 1%></td> <td align="center">This is text data <%=i%><div id="aaa"></div></td> <td align="center"><%=i * i%></td> </tr> <% } %> </tbody> </table> <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> <script type="text/javascript"> document.getElementById("aaa").innerHTML="js测试"; </script> <% if (exportToExcel == null) { %> <a href="exportToExcel.jsp?exportToExcel=YES">Export to Excel</a> <% } %> </body> </html>
4、从上面导出的excel中可以看到js修饰的内容并没有导出,为什么呢?个人愚见是:2、3两个方式都不支持js,导出excel时,数据是excel流,这种流不支持js。
但是,我在我的工作中有用到了js修饰,怎么办呢?我又找到了另外一种方式---使用IE的ActiveXObject!
所以,在上面的代码中加入一个代码块如下:
<input type="button" onClick="return AutomateExcel();" value="导出到EXCEL" /> </br> <script language="javascript"> function AutomateExcel(){ var elTable = document.getElementById("datatable"); //要导出的table id。 var oRangeRef = document.body.createTextRange(); oRangeRef.moveToElementText(elTable); oRangeRef.execCommand("Copy"); var appExcel = new ActiveXObject("Excel.Application"); appExcel.Workbooks.Add().Worksheets.Item(1).Paste(); appExcel.Visible = true; appExcel = null; } </script>
这样就可以导出用js修饰的内容了!(不过,要在IE浏览器中设置一下ActiveX插件的启用方式)
原文:http://www.cnblogs.com/lvzhanhui/p/xiaoqiaolv_export_excel.html