实现java导出Excel表

实现java导出Excel表

1、项目需要导入的包

      其中jxl.jar就是操作excel表的核心包，其他的包都是struts2的包

2、项目的结构

ExcelAction.java

package com.jxl.action;

import java.io.InputStream;  
import java.io.UnsupportedEncodingException;

import com.jxl.service.ExcelServiceImpl;
import com.jxl.service.IExcelService;

public class ExcelAction {

	    private String fileName;
	    private InputStream excelStream;  

	    public String getFileName() {
	    	try {  
	            fileName = new String(fileName.getBytes(),"ISO8859-1");  
	        } catch (UnsupportedEncodingException e) {  
	            e.printStackTrace();  
	        }  
	        return fileName + ".xls";
		}
		public void setFileName(String fileName) {
			this.fileName = fileName;
		}
		public InputStream getExcelStream() {
			return excelStream;
		}
		public void setExcelStream(InputStream excelStream) {
			this.excelStream = excelStream;
		}
		  
	    public String exportExcel(){  
	        IExcelService es = new ExcelServiceImpl();  
	        excelStream = es.getExcelInputStream();
	        this.fileName = "学生成绩表";
	        return "excel";  
	    }
		
}  

IExcelService.java

package com.jxl.service;

import java.io.InputStream;

public interface IExcelService {
	/**
	 * 获得Excel表的输入流
	 * @return InputStream
	 *                输入流 
	 */
	public InputStream getExcelInputStream();
    
}

ExcelServiceImpl.java

package com.jxl.service;

import java.io.ByteArrayInputStream;  
import java.io.ByteArrayOutputStream;  
import java.io.InputStream;  
import java.io.OutputStream;  
  
import jxl.Workbook;  
import jxl.write.WritableSheet;  
import jxl.write.WritableWorkbook;  

import com.jxl.service.IExcelService;
  
public class ExcelServiceImpl implements IExcelService {  
  
    @Override  
    public InputStream getExcelInputStream() {  
        //将OutputStream转化为InputStream  
        ByteArrayOutputStream out = new ByteArrayOutputStream();  
        putDataOnOutputStream(out);  
        return new ByteArrayInputStream(out.toByteArray());  
    }  
  
    private void putDataOnOutputStream(OutputStream os) {  
        jxl.write.Label label1;
        jxl.write.Label label2;
        WritableWorkbook workbook;  
        try {  
            workbook = Workbook.createWorkbook(os);  
            WritableSheet sheet1 = workbook.createSheet("计算机01班", 0);  
            label1 = new jxl.write.Label(0, 0, "计算机01班excel成绩表");  
            sheet1.addCell(label1);
            
            WritableSheet sheet2 = workbook.createSheet("计算机02班", 0);  
            label2 = new jxl.write.Label(0, 0, "计算机02班excel成绩表");  
            sheet2.addCell(label2);
              
            workbook.write();  
            workbook.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
} 

struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
	 <!-- 配置 Struts 2 应用中的常量 --> 
	 <constant name="struts.i18n.encoding" value="UTF-8"/> 
	 
	 <package name="platform-default" extends="struts-default">
	 	<action name="excel" class="com.jxl.action.ExcelAction" method="exportExcel">  
            <!-- 下载文件类型定义，即定义为“stream” --> 
            <result name="excel" type="stream">
                <!-- application/vnd.ms-excel表示的是 EXCEL文件 -->  
                <param name="contentType">  
                    application/vnd.ms-excel  
                </param>
                <!-- 下载文件输出流定义 -->  
                     <!-- 这里的inputName元素所对应的value值excelStream，在action中一定要有对应的getExcelStream()方法 -->  
                <param name="inputName">excelStream</param>
                <!-- 下载文件处理方法 -->  
                <param name="contentDisposition">
                <!-- attachment表示附件方式，即下载时打开保存对话窗，filename表示下载时显示的保存时的文件名 -->  
                         <!-- 如果不写attachment;或者是写的是inline; 则表示内联，即会在浏览器中尝试打开下载的文件，而不是下载-->  
                    attachment;filename=${fileName}  <!-- 在对象的aciton中要有fileName属性和getFileName()方法 -->
                </param>
                <!-- 下载缓冲区的大小 -->  
                <param name="bufferSize">1024</param>  
            </result>  
        </action> 
	</package>
	 
</struts>    

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">

  <!-- 设置struts 2过滤器 -->
  <filter>
      <filter-name>struts 2</filter-name>
      <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
  </filter>
  <filter-mapping>
      <filter-name>struts 2</filter-name>
      <url-pattern>/*</url-pattern>
  </filter-mapping>
  
  <!-- 设置欢迎页面 -->
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  
  <!-- session超时定义,单位为分钟 -->
  <session-config>
	<session-timeout>30</session-timeout>
  </session-config>
  
</web-app>

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    <title>首页</title>
  </head>
  <body>
     	<a href="excel.action">去下载一个excel文件</a>
  </body>
</html>

完成以上所有的步骤后，把项目发布到服务器，用浏览器访问index.jsp，里面有功能入口

本实例只实现了最最简单的操作、下载excel表的功能，至于更详细的excel表的属性设置，可以参考 http://merrygrass.iteye.com/blog/558274  和  http://www.blogjava.net/Vincent-chenxj/articles/242512.html