struts2中用apache的jxl.jar导出Excel表格

yllplay

一.在SSH中导入生Excel表格的依赖包Jxl.jar
二.在service层创建接口IExcelService
package com.crms.service;

import java.io.InputStream;
import java.util.List;
/**
*  struts2导出Excel
* @author Administrator
*
*/
public interface IExcelService {
  public InputStream getExcelInputStream(List<Object[]> objList);     
}

三.创建接口的实现类
package com.crms.service.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.crms.service.IExcelService;

/**
* 写入Excel文件
*
* 用來生成一个Excel文件   
*   
* @author Administrator
*
*/
public class ExcelServiceImpl implements IExcelService {
// 定义request ,response.
HttpServletResponse response = ServletActionContext.getResponse();
HttpServletRequest request = ServletActionContext.getRequest();
/**
* 将OutputStream转化为InputStream
*/
public InputStream getExcelInputStream(List<Object[]> dataList) {
ByteArrayOutputStream out = new ByteArrayOutputStream();
putDataOnOutputStream(out,dataList);
return new ByteArrayInputStream(out.toByteArray());
}

/**
* 生成数据表(Excel)
*/
private void putDataOnOutputStream(OutputStream os,List<Object[]> dataList) {

//构造Workbook（工作薄）对象   
WritableWorkbook workbook=null;
//创建工作表
WritableSheet ws=null;
try {
//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
workbook = Workbook.createWorkbook(os);
/*
             * 创建一个可写入的工作表  
             * Workbook的createSheet方法有两个参数，
             *第一个是工作表的名称，第二个是工作表在工作薄中的位置
             * 生成名为"第一页"的工作表，参数0表示这是第一页
             */
ws= workbook.createSheet("offerUp", 0);
//创建列名
// 定义格式, 字体, 下划线, 斜体, 粗体, 颜色
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
//创建格式化对象实例   
WritableCellFormat totalx2Format = new WritableCellFormat(wf);   
//垂直居中   
totalx2Format.setVerticalAlignment(VerticalAlignment.CENTRE);   
//水平居中   
totalx2Format.setAlignment(Alignment.CENTRE);
// 设置标题.ws.addCell(new jxl.write.Label(列(从0开始), 行(从1开始), 内容.));
ws.addCell(new Label(0, 0, "编号",totalx2Format));
ws.addCell(new Label(1, 0, "客户名称",totalx2Format));
ws.addCell(new Label(2, 0, "订单金额（元）",totalx2Format));
//合并单元格，参数格式（开始列，开始行，结束列，结束行）
ws.mergeCells(0, 0, 0, 0);
ws.mergeCells(1, 0, 1, 0);
ws.mergeCells(2, 0, 2, 0);
//设置单元格的宽度
ws.setColumnView(0, 10);
ws.setColumnView(1, 40);
ws.setColumnView(2, 20);


//添加数据
if (null!=workbook) {
           
//下面开始添加单元格数据   
            for(int i=0;i<dataList.size();i++){   
                for(int j=0;j<dataList.get(0).length;j++){   
                    //这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行   
                    Label labelC = new Label(j, i+1,dataList.get(i)[j].toString(),totalx2Format);   
                    try {   
                        //将生成的单元格添加到工作表中   
                    ws.addCell(labelC);   
                    } catch (RowsExceededException e) {   
                        e.printStackTrace();   
                    } catch (WriteException e) {   
                        e.printStackTrace();   
                    }   
                }   
            }   
}
    //从内存中写入文件中   
workbook.write();   
               //关闭资源，释放内存   
workbook.close();   
} catch (Exception e) {
e.printStackTrace();
}
}
}
四.Action层将数据库的数据传给Excel,并返回结果
package com.crms.web.action;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.crms.biz.StatisticsBiz;
import com.crms.pojo.Customerinfo;
import com.crms.pojo.msms.Orderdetail;
import com.crms.pojo.msms.Orders;
import com.crms.service.IExcelService;
import com.crms.service.impl.ExcelServiceImpl;
/**
* struts2导出Excel
* @author Administrator
*
*/

public class ExcelAction {
private StatisticsBiz statisticsBiz;
private List<Orderdetail> orderdetailList;
private Customerinfo customerinfo;
private ExcelServiceImpl excelServiceImpl=new ExcelServiceImpl();

public StatisticsBiz getStatisticsBiz() {
return statisticsBiz;
}

public void setStatisticsBiz(StatisticsBiz statisticsBiz) {
this.statisticsBiz = statisticsBiz;
}

public List<Orderdetail> getOrderdetailList() {
return orderdetailList;
}

public void setOrderdetailList(List<Orderdetail> orderdetailList) {
this.orderdetailList = orderdetailList;
}

public IExcelService getExcelService() {
return excelService;
}public void setExcelService(IExcelService excelService) {
this.excelService = excelService;
}

public ExcelServiceImpl getExcelServiceImpl() {
return excelServiceImpl;
}

public void setExcelServiceImpl(ExcelServiceImpl excelServiceImpl) {
this.excelServiceImpl = excelServiceImpl;
}

//+++++++++++++++++++++++++++
private InputStream excelStream;
private IExcelService excelService = new ExcelServiceImpl();

public InputStream getExcelStream() {
return excelStream;
}

public void setExcelStream(InputStream excelStream) {
this.excelStream = excelStream;
}

public String execute() {
String customerId = null;
List<Object[]> objectList = new ArrayList<Object[]>();
try {
orderdetailList = statisticsBiz.getOrderdetailService().queryAll();
Integer i = 0;
for (Orderdetail orderdetail_2 : orderdetailList) {
customerId = ((Orders) (orderdetail_2.getOrders())).getCustomer_id();
customerinfo = statisticsBiz.getCustomerinfoService().queryById(customerId);
((Orders) (orderdetailList.get(i).getOrders())).setCustomer_id(customerinfo.getCustomerName());
Object [] object={i+1,customerinfo.getCustomerName(),orderdetail_2.getSub_price()};
objectList.add(object);
i++;
}

} catch (Exception e) {
e.printStackTrace();
}
excelStream = excelService.getExcelInputStream(objectList);
return "excel";
}
}
五.Struts2配置文件
<?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">
<!--  struts2导出Excel -->
<struts>
<package name="excel" namespace="/export2excel"  extends="struts-default">
<action name="excel" class="com.crms.web.action.ExcelAction">
<result name="excel_*" type="stream">
<param name="contentType">
application/vnd.ms-excel   
            </param>
<param name="inputName">excelStream</param>
<param name="contentDisposition">
filename="{1}.xls"   
            </param>
<param name="bufferSize">1024</param>
</result>
</action>   
</package>   
</struts>