客户端导出服务器端在数据库中查询的报表数据到excel

我是用flex实现客户端导出报表数据到excel的，实现步骤如下：

jxls下载地址
http://jxls.sourceforge.net/

1. flex端代码

<mx:Script>
<![CDATA[
import net.shopin.service.ReportService;
import net.shopin.vo.DateKeyVO;
import mx.controls.Alert;

[Bindable]
private var sales:Array=new Array;

public function check():Boolean
{
	if (start.text == "" || end.text == "")
	{
		Alert.show("请您选择起始和结束日期");
		return false;
	}
	else
	{
		var dateKeyVO:DateKeyVO=new DateKeyVO();
		dateKeyVO.startDate=DateField.stringToDate(start.text, "YYYY-MM-DD");
		dateKeyVO.endDate=DateField.stringToDate(end.text, "YYYY-MM-DD");
		if (dateKeyVO.startDate.getFullYear() > dateKeyVO.endDate.getFullYear())
		{
Alert.show("您选择结束日期小于起始日期");
return false;
		}
		else
		{
if (dateKeyVO.startDate.getMonth() > dateKeyVO.endDate.getMonth() && dateKeyVO.startDate.getFullYear() == dateKeyVO.endDate.getFullYear())
{
	Alert.show("您选择结束日期小于起始日期");
	return false;
}
else
{
	if (dateKeyVO.startDate.getDate() > dateKeyVO.endDate.getDate() && dateKeyVO.startDate.getFullYear() == dateKeyVO.endDate.getFullYear() && dateKeyVO.startDate.getMonth() == dateKeyVO.endDate.getMonth())
	{
		Alert.show("您选择结束日期小于起始日期");
		return false;
	}
	else
	{
		return true;
	}
}
		}
	}
}
//查询报表数据
public function search():void
{
	var flag:Boolean=check();
	if (flag == true)
	{
		var dateKeyVO:DateKeyVO=new DateKeyVO();
		dateKeyVO.startDate=DateField.stringToDate(start.text, "YYYY-MM-DD");
		dateKeyVO.endDate=DateField.stringToDate(end.text, "YYYY-MM-DD");
		var backService:ReportService=new ReportService(result);
		backService.getRefunReport(dateKeyVO);
	}
}


//查询返回数据
public function result(data:Object):void
{
	sales=data as Array;
	refunGrid.dataProvider=sales;
}

//导出报表数据到客户端供下载	
public function export():void{
	var flag:Boolean=check();
	if (flag == true)
	{
	var start:String=start.text;
	var end:String=end.text;
	navigateToURL(new URLRequest("http://localhost:80/web/getRefunReport/" + start + "/" + end + "/report.xls"), "_blank");
	}
}

//导出报表数据到服务器	
public function exportToServer():void
{
	var flag:Boolean=check();
	if (flag == true)
	{
		var dateKeyVO:DateKeyVO=new DateKeyVO();
		dateKeyVO.reportType="退货报表";
		dateKeyVO.startDate=DateField.stringToDate(start.text, "YYYY-MM-DD");
		dateKeyVO.endDate=DateField.stringToDate(end.text, "YYYY-MM-DD");
		var backService:ReportService=new ReportService(resultExport);
		backService.toExport(dateKeyVO);
	}
}

public function resultExport(data:Object):void
{
	var flag:Boolean=data as Boolean;
	if (flag)
	{
		Alert.show("成功导出退货报表到服务器D盘，稍后下载");
	}
	else
	{
		Alert.show("导出报表失败");
	}

}
//合计汇总数据列
public function sumFunction(col:DataGridColumn):String
{
	var n:int=sales.length;
	var sum:Number=0;
	for (var i:int=0; i < n; i++)
	{
		sum+=sales[i][col.dataField];
	}

	return sum.toFixed(2);
}
]]>
</mx:Script>
<mx:Label x="222" y="30" text="退货日期  从"/>
<mx:Label x="437" y="30" text="到"/>
<mx:Button x="614" y="30" label="查询" click="search()"/>
<mx:Button x="715" y="30" label="导出报表到Execl" click="export()"/>

<mx:DateField id="start" x="313" y="28" yearNavigationEnabled="true" dayNames="['日','一','二','三','四','五','六']"  monthNames='["一","二","三","四","五","六","七","八","九","十","十一","十二"]' formatString="YYYY-MM-DD"/>

<mx:DateField id="end" x="482" y="28" yearNavigationEnabled="true" dayNames="['日','一','二','三','四','五','六']"  monthNames='["一","二","三","四","五","六","七","八","九","十","十一","十二"]' formatString="YYYY-MM-DD"/>

2. 用spring mvc 实现的java端控制层

@Controller
public class LoadExcelController {
    @Autowired
    BackControlService backService;

    @RequestMapping(value = "/getSaleReport/{start}/{end}/report.xls", method = RequestMethod.GET)
    public String getSaleReport(Model m,@PathVariable("start") String start,@PathVariable("end") String end) {
        DateKeyVO dateKeyVO = new DateKeyVO();
        SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
        //把地址栏传过来的字符串转换成日期
        try {
            Date start1 = f.parse(start);
            Date end1 = f.parse(end);
            dateKeyVO.setStartDate(start1);
            dateKeyVO.setEndDate(end1);
        } catch (ParseException px) {
            px.printStackTrace();
        }
        //获得service层查询的报表数据
        List<SaleReportVO> list = backService.getSaleReport(dateKeyVO);
        //找到报表的模板
        m.addAttribute("Template", "/templates/sale.xls");
        m.addAttribute("list", list);
        return "excelView";
    }
}

模板sale.xls代码

  销售日期	             订单号	    销售单号
<jx:forEach var="sale" items="${list}">						
${sale.saleTime}${sale.orderNumber}	${sale.saleNO}	

</jx:forEach>						

3.控制层return "excelView"映射

views.properties，views_zh_CN.properties

excelView.(class)=com.shopin.modules.web.view.ExcelView

ExcelView 代码

package com.shopin.modules.web.view;

import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;
import java.io.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import org.springframework.web.servlet.view.AbstractView;
import org.springframework.util.ResourceUtils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import net.sf.jxls.transformer.XLSTransformer;


/**
 * 数据导出到Excel中
 */
public class ExcelView extends AbstractView {
    public ExcelView() {
        super();
        setContentType("application/vnd.ms-excel");
    }

    @Override
    protected final void renderMergedOutputModel(
            Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        Resource resource = new ClassPathResource((String) model.get("Template"));
        HSSFWorkbook book = new XLSTransformer().transformXLS(resource.getInputStream(), model);
        response.setContentType(getContentType());
        ServletOutputStream out = response.getOutputStream();
        book.write(out);
        out.flush();
    }

}

4.java端service层代码

 public List<SaleReportVO> getSaleReport(DateKeyVO dateKeyVO) {
        Session session = null;
        Transaction tx = null;
        List<SaleReportVO> listVO = new ArrayList<SaleReportVO>();
        try {
            session = sessionFactory.openSession();
            tx = session.beginTransaction();
            Query query = session.createSQLQuery(ReportSQL.salesSQL);
            query.setDate(0,dateKeyVO.getStartDate());
            query.setDate(1,dateKeyVO.getEndDate());
            List list = query.list();
            for (int i = 0; i < list.size(); i++) {
                Object[] objs = (Object[]) list.get(i);
//               SalesBean salesBean=new SalesBean((String)objs[0],((BigDecimal)objs[1]).doubleValue(),((BigDecimal)objs[2]).doubleValue(),((BigDecimal)objs[3]).doubleValue(),((BigDecimal)objs[4]).doubleValue(),((BigDecimal)objs[5]).doubleValue(),((BigDecimal)objs[6]).doubleValue(),((BigDecimal)objs[7]).doubleValue(),((BigDecimal)objs[8]).doubleValue(),((BigDecimal)objs[9]).doubleValue(),((BigDecimal)objs[10]).doubleValue(),((BigDecimal)objs[11]).doubleValue(),(String)objs[12],(String)objs[13],(String)objs[14],(String)objs[15],(String)objs[16],(String)objs[17]);
                SaleReportVO salesBean = new SaleReportVO();
                if (objs[0] != null) {
                    salesBean.setSaleTime(((Date) objs[0]).toString());
                }
                if (objs[1] != null) {
                    salesBean.setOrderNumber((String) objs[1]);
                }
                if (objs[2] != null) {
                    salesBean.setSaleNO((String) objs[2]);
                }
                //把数据库中查询出的BigDecimal类型转换成Double类型
                if (objs[3] != null) {
                    salesBean.setSaleNum(((BigDecimal) objs[3]).doubleValue());
                }
                if (objs[4] != null) {
                    salesBean.setOrderSum(((BigDecimal) objs[4]).doubleValue());
                }
                if (objs[5] != null) {
                    salesBean.setProductSale(((BigDecimal) objs[5]).doubleValue());
                }
                if (objs[6] != null) {
                    salesBean.setSendCost(((BigDecimal) objs[6]).doubleValue());
                }
                if (objs[7] != null) {
                    salesBean.setRealCost(((BigDecimal) objs[7]).doubleValue());
                }
               listVO.add(salesBean);
            }
        } catch (Exception e) {
            if (session != null && tx != null && session.isOpen()) {
                tx.rollback();
            }
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return listVO;
    }