Flex按模板导出Excel

    Flex前端+Java后台，要将Flex界面中AdvancedDataGrid的数据按Excel模板导出Excel文件。
    前台as3xls对Excel模板样式的获取不足，因此选择在Java后台使用POI。
实现思路：
1.DataGrid数据传到服务器。
2.打开Excel模板。
3.向模板写入数据。
4.客户端下载写好的Excel。

AS代码：

public static function ToExcel(dg:AdvancedDataGrid,name:String)
{		      
    url="http://localhost:8080/XXX/servlet/ExcelServlet"; 
    var variables:URLVariables = new URLVariables(); 
    variables.head = convertDGHead(dg);
    variables.data = convertDGData(dg);
			
    var request:URLRequest = new URLRequest(url);
    request.method = URLRequestMethod.POST;
    request.data = variables;
			
    var fileReference:FileReference=new FileReference();
    fileReference.download(request,name+".xls");
			
}

//表头转为字符串，用";"隔开
private static function convertDGHead(dg:AdvancedDataGrid):String {
    var str:String = '';
    for(var i:int = 0;i<dg.columns.length;i++) {
        if(dg.columns[i].headerText != undefined) {
            str+=dg.columns[i].headerText+";";
        } else {
            str+=dg.columns[i].dataField+";";
        }
    }
	   		return str;
}

//表内容转为字符串，用";"隔开
private static function convertDGData(dg:AdvancedDataGrid):String {
    var str:String = '';
    for(var j:int =0;j<dg.dataProvider.length;j++)     
    {                    
        for(var k:int=0; k < dg.columns.length; k++) {
            if(dg.dataProvider.getItemAt(j) != undefined && dg.dataProvider.getItemAt(j) != null) {
                if(dg.columns[k].labelFunction != undefined) {
                    str += dg.columns[k].labelFunction(dg.dataProvider.getItemAt(j),dg.columns[k].dataField)+";";
                } else {
	                  str += dg.dataProvider.getItemAt(j)[dg.columns[k].dataField]+";";
                }
            }
        }
    }
    return str;
}

web.xml中增加：

<servlet>
    <servlet-name>ExcelServlet</servlet-name>
    <servlet-class>Com.Export.ExcelServlet</servlet-class>
</servlet>

<servlet-mapping>
    <servlet-name>ExcelServlet</servlet-name>
    <url-pattern>/servlet/ExcelServlet</url-pattern>
</servlet-mapping>

ExcelServlet.java:

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    public ExcelServlet() {
        super();
    }

    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
		// Put your code here
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doMainDispatch(request, response);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doMainDispatch(request, response);
    }

    public void init() throws ServletException {
        // Put your code here
    }

    protected void doMainDispatch(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String filename = "excel.xls";
        resp.reset();
        req.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("application/vnd.ms-excel");
        resp.addHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
        String head = req.getParameter("head");
        String data = req.getParameter("data");
        HSSFWorkbook wb = generateExcel(head,data);
        wb.write(resp.getOutputStream());
        resp.getOutputStream().flush();
        resp.getOutputStream().close();
        req.getInputStream().close();
    }

    private HSSFWorkbook generateExcel(String head, String data) throws IOException {
        String tmp = (new File("")).getCanonicalPath();
        //如果不是只有一级 如："d:\tomcat6\bin"
        if(tmp.indexOf("bin") != -1)
        {
            tmp = tmp.substring(0, tmp.lastIndexOf("\\"))
                + "\\webapps\\XXX\\model\\Model.xls";
        }
        else //只有一级 如："d:\tomcat6"
        {
            tmp += "\\webapps\\XXX\\model\\Model.xls";
        }
	    
        FileInputStream fileinputstream = new FileInputStream(tmp);
        POIFSFileSystem poifsfilesystem = new POIFSFileSystem(fileinputstream);
        HSSFWorkbook wb = new HSSFWorkbook(poifsfilesystem);
        HSSFSheet sheet = wb.getSheetAt(0); 
		
        String[] headArr = head.split(";");
        String[] dataArr = data.split(";");
		
        for(int rowNum=0; rowNum<=dataArr.length/headArr.length; rowNum++)
        {
            HSSFRow row = sheet.getRow(rowNum);
            HSSFCellStyle rowstyle=null;
            if(row == null)  //如果模板中没有这一行，则新建
              {
                row = sheet.createRow(rowNum);
            }
            else  //如果有这一行，获取样式
              {
                rowstyle = row.getRowStyle();
            }
			
            for(int colNum=0; colNum<headArr.length; colNum++)
            {
                HSSFCell cell = row.getCell(colNum);
	       HSSFCellStyle cellstyle=null;
                if(cell == null){
                    cell = row.createCell(colNum);
                }else{
                    cellstyle = cell.getCellStyle();
                }
				
                if(cellstyle != null)
		  cell.setCellStyle(cellstyle);
                else if(rowstyle != null)
                    cell.setCellStyle(rowstyle);
				
                if(rowNum == 0)  {  //表头 
                    cell.setCellValue(headArr[colNum]);
                }else{
                    cell.setCellValue(dataArr[colNum+headArr.length*(rowNum-1)]);
                }
            }
        }
        return wb;
    }

}

要注意的问题：
1.给Servlet传值时HTTP有参数长度的限制，大约120个字符，参数超过时必须指定为POST方法，即加上此句：request.method = URLRequestMethod.POST;
2.flex中隐含为utf-8编码，因此必须含有request.setCharacterEncoding("UTF-8")语句来编码接收的数据，也必须含有response.setCharacterEncoding("UTF-8");语句以编码输出数据，否则会出现汉字乱码。

评论

3 楼 sxm2017 2012-12-07  

谢谢，写的真不错，谢谢分享。。。。

2 楼 pufan53 2011-08-06  

liuwenfeng554 写道

AdvancedDataGrid 请问一下你前台是怎么调用的

AdvancedDataGrid中的数据最开始也是从java传来的，显示出来后，要导出的时候才用到上面所说的，再把AdvancedDataGrid的数据传回到java

1 楼 liuwenfeng554 2011-08-03  

AdvancedDataGrid 请问一下你前台是怎么调用的