web.xml:
<web-app id="WebApp_9" version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <filter> <filter-name>struts2</filter-name> <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class> </filter> <filter-mapping> <filter-name>struts2</filter-name> <url-pattern>/*</url-pattern> </filter-mapping></web-app>struts.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN" "http://struts.apache.org/dtds/struts-2.0.dtd"><struts>
<constant name="struts.multipart.maxSize" value="20971520000"></constant> <package name="default" namespace="/" extends="json-default"> <action name="excelAction" class="com.huawei.s2.action.ExcelAction" method="excelTest"> <result name="excelTest" type="stream"> <param name="contentDisposition">attachment;filename=${fileName}</param> </result> </action> </package></struts>WebContent下的jsp(1.jsp):
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <title>测试struts2</title> </head> <body> <a href="excelAction">生成excel</a> </body></html>action:
public class ExcelAction {
private String fileName; private InputStream inputStream; public String excelTest() throws Exception{ Person p1 = new Person(1,"aaa",Date.valueOf("2012-12-12"),2000.0); Person p2 = new Person(2,"bbb",Date.valueOf("2012-12-12"),2000.0); Person p3 = new Person(3,"张三",Date.valueOf("2012-02-12"),2000.0); List<Person> personList = new ArrayList<Person>(); personList.add(p1); personList.add(p2); personList.add(p3); //excel表格浏览器下载时显示的名字 fileName = "测试表格.xls"; //创建生成excel 数据的对象 XLSExport xls = new XLSExport(); String[] tabHeadContent = new String[]{"编号","姓名","入职日期","工资"}; xls.createTable(true, personList, tabHeadContent); //导出excel inputStream = xls.exportXLSByStruts(); return "excelTest"; } public String getFileName() throws Exception{ return new String(fileName.getBytes("gbk"),"iso-8859-1"); }public void setFileName(String fileName) {
this.fileName = fileName; }public InputStream getInputStream() {
return inputStream; } public void setInputStream(InputStream inputStream) { this.inputStream = inputStream; }}po:
public class Person {
private Integer pid; private String pname; private Date hiredate; private Double sal; public Person(Integer pid, String pname, Date hiredate, Double sal) { super(); this.pid = pid; this.pname = pname; this.hiredate = hiredate; this.sal = sal; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Double getSal() { return sal; } public void setSal(Double sal) { this.sal = sal; } public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } public String getPname() { return pname; } public void setPname(String pname) { this.pname = pname; }}util:
public class XLSExport {
// 定制日期格式 private static final String DATE_FORMAT = "m/d/yy h:mm" ; // "m/d/yy h:mm" // 定制浮点数格式 private static final String NUMBER_FORMAT = " #,##0.00 " ; private String xlsFileName; private HSSFWorkbook workbook; private HSSFSheet sheet; private HSSFRow row; /** * 初始化Excel * * @param fileName * 导出文件名 */ public XLSExport(String fileName) { this .xlsFileName = fileName; this .workbook = new HSSFWorkbook(); this .sheet = workbook.createSheet(); } public XLSExport() { this .workbook = new HSSFWorkbook(); this .sheet = workbook.createSheet(); } public void createTabHeader(String...tabHeadContent){ createRow(0); for(int i = 0;i<tabHeadContent.length;i++){ setCell(i, tabHeadContent[i]); } } public void createTable(boolean flag,List list,String...tabHeadContent) throws Exception{ if(flag){//如果有表头就创建表头 createTabHeader(tabHeadContent); } for(int i = 0;i<list.size();i++){ createRow(flag?i+1:i);//每条数据对应一行 Object obj = list.get(i); Class clazz = obj.getClass(); Field[] fields = clazz.getDeclaredFields(); for(int j=0;j<fields.length;j++){ Class type = fields[j].getType(); String fieldName = fields[j].getName(); String getter = (type==boolean.class?"is":"get")+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); Method method = clazz.getDeclaredMethod(getter); Object fieldValue = method.invoke(obj); if(fieldValue!=null){ if(type==Integer.class||type==int.class){ setCell(j, (Integer)fieldValue); }else if(type==Double.class||type==double.class){ setCell(j, (Double)fieldValue); }else if(type==Date.class||type==java.sql.Date.class){ setCell(j, (Date)fieldValue);//一会测试测试 }else if(type==String.class){ setCell(j, (String)fieldValue);//一会测试测试 } }else{ setCell(j, ""); } } } } public InputStream exportXLSByStruts()throws Exception{ ByteArrayOutputStream bos = new ByteArrayOutputStream();//这个类ByteArrayOutputStream在这里很重要 workbook.write(bos); InputStream inputStream = new ByteArrayInputStream(bos.toByteArray()); return inputStream; } /** * 导出Excel文件 * * @throws XLSException */ public void exportXLS() throws XLSException { try { FileOutputStream fOut = new FileOutputStream(xlsFileName); workbook.write(fOut); fOut.flush(); fOut.close(); } catch (FileNotFoundException e) { throw new XLSException( " 生成导出Excel文件出错! "); } catch (IOException e) { throw new XLSException( " 写入Excel文件出错! "); }}
/** * 增加一行 * * @param index * 行号 */ public void createRow( int index) { this .row = this .sheet.createRow(index); }/**
* 设置单元格 * * @param index * 列号 * @param value * 单元格填充值 */ public void setCell( int index, String value) { HSSFCell cell = this .row.createCell(index); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(value); }/**
* 设置单元格 * * @param index * 列号 * @param value * 单元格填充值 */ public void setCell( int index, Calendar value) { HSSFCell cell = this .row.createCell(index); cell.setCellValue(value.getTime()); HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式 cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式 sheet.setColumnWidth(index, 5000); } public void setCell( int index, Date utilDate) { Calendar c = new GregorianCalendar(); c.setTimeInMillis(utilDate.getTime()); setCell(index,c); } /** * 设置单元格 * * @param index * 列号 * @param value * 单元格填充值 */ public void setCell( int index, int value) { HSSFCell cell = this .row.createCell(index); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value); }/**
* 设置单元格 * * @param index * 列号 * @param value * 单元格填充值 */ public void setCell( int index, double value) { HSSFCell cell = this .row.createCell(index); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value); HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式 HSSFDataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式 cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式 } public String getXlsFileName() { return xlsFileName; } public void setXlsFileName(String xlsFileName) { this.xlsFileName = xlsFileName; } public HSSFWorkbook getWorkbook() { return workbook; } public void setWorkbook(HSSFWorkbook workbook) { this.workbook = workbook; } public HSSFSheet getSheet() { return sheet; } public void setSheet(HSSFSheet sheet) { this.sheet = sheet; } public HSSFRow getRow() { return row; } public void setRow(HSSFRow row) { this.row = row; }}XLSException:
public class XLSException extends Exception{
public XLSException() { } public XLSException(String message) { super(message); }}XLSDemo:
public class XLSDemo {
/** * 学习方法： * 1：资料 先看懂 根据程序 测试数据 * 2：根据自己需要 做一些改动 * 3：再根据自己需要 做一些封装 * 4： 直接应用 */ public static void main(String[] args) throws Exception { Person p1 = new Person(1,"aaa",Date.valueOf("2012-12-12"),2000.0); Person p2 = new Person(2,"bbb",Date.valueOf("2012-12-12"),2000.0); Person p3 = new Person(3,"张三",Date.valueOf("2012-02-12"),2000.0); List<Person> personList = new ArrayList<Person>(); personList.add(p1); personList.add(p2); personList.add(p3); XLSExport xls = new XLSExport("D:/test2.xls"); //先搞一个表头 String[] tabHeadContent = new String[]{"编号","姓名","入职日期","工资"}; xls.createTable(true, personList, tabHeadContent); //再生成文件 xls.exportXLS(); } }