最近做Flex里的Excel下载,用as3xls进行Excel导出后,Excel修改编辑后老出现:不能以当前格式保存。。。若要保存所做的更改,请单击“确定”,然后将其另存为最新的格式。
最后通过JAVA的POI实现Excel下载的,具体实现思路:
1.DataGrid数据传到服务器。
2.打开Excel模板。
3.向模板写入数据。
4.客户端下载写好的Excel。
前台Flex代码
public function ToExcel(dg:AdvancedDataGrid,name:String) { var url:String="http://localhost:8080/Demo/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"); fileReference.addEventListener(Event.COMPLETE,doEvent); } private function doEvent(evt:Event):void { Alert.show("下载完成"); } //表头转为字符串,用";"隔开 private 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 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.test.ExcelServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ExcelServlet</servlet-name>
<url-pattern>/servlet/ExcelServlet</url-pattern>
</servlet-mapping>
后台JAVA代码:
package com.test;
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\\Demo\\model\\Model.xls";
}
else //只有一级 如:"d:\tomcat6"
{
tmp += "\\webapps\\Demo\\model\\Model.xls";
}
//创建输出数据文件
File modelFile =new File(tmp);
// 路径下不存在此模板文件
if (!modelFile.getParentFile().exists()) {
return null;
}
else{
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);
}
}
return wb;
}
}
}
注:
1.给Servlet传值时HTTP有参数长度的限制,参数超过时必须指定为POST方法,此次传Excel文件内容,不能用GET方法,因此要加上request.method = URLRequestMethod.POST;
2.flex中隐含为utf-8编码,因此必须含有request.setCharacterEncoding("UTF-8")语句来编码接收的数据,也必须含有response.setCharacterEncoding("UTF-8");语句以编码输出数据,否则会出现汉字乱码。
3、tomcat6在默认状态下最大的post请求是2M ,以此要在server.xml文件中设maxPostSize属性
<Connector port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" maxPostSize="0" />
maxPostSize: 0 表示不限制 单位为:byte
本文参考链接:http://pufan53.iteye.com/blog/903468
POST相关限制文章:http://blog.csdn.net/jackyren007/article/details/3715444