学步园

1 应用场合 Flex+Asp.net

2 具体步骤：

   Flex的DataGrid控件编程Xml数据；

   Xml数据通过UrlRequest的Post方法上传到服务器；

   服务器吧Xml转换为DataSet；

   通过通用xls文件生成导出类导出xls文件。（Excel模板导出)

3 代码如下：

  1 Flex Code

public static function ExcelExport(vDataGrid:DataGrid,vUrl:String):void{<br /> var str:String=DBInface.convertDGToXml(vDataGrid);<br /> var variables:URLVariables =new URLVariables();<br /> var u:URLRequest =new URLRequest(vUrl);<br /> variables.htmltable= encodeURIComponent(str);<br /> u.data = variables; //Pass the variables<br /> u.method = URLRequestMethod.POST; //Don't forget that we need to send as POST<br /> navigateToURL(u,'_blank');<br /> }<br /> }<br /> }  

 2 AshxCode

<%@ WebHandler Language="C#" Class="ExcelOut" %></p> <p>using System;<br /> using System.Web;<br /> using System.Data;<br /> using System.Collections.Generic;<br /> using ExcelOutputLib;</p> <p>public class ExcelOut : IHttpHandler {</p> <p> public void ProcessRequest (HttpContext context)<br /> {</p> <p> DataSet ds = new DataSet();<br /> String UpLoadStr="";<br /> String TemplateFilePath = "";<br /> String TemplateFileName = "";<br /> String FileName = "";<br /> IList<String> ColumnNames = new List<String>();<br /> DataTable dt = new DataTable();</p> <p> try<br /> {<br /> //客户端上传Xml数据<br /> UpLoadStr = HttpUtility.HtmlDecode(context.Request.Form["htmltable"]);</p> <p> if (UpLoadStr.Trim() == "" || UpLoadStr == null)<br /> {<br /> return;<br /> }</p> <p> //客户端上传模板文件名<br /> TemplateFileName = context.Request.QueryString["TemplateFileName"].ToString();</p> <p> //获得模板文件路径<br /> TemplateFilePath = context.Server.MapPath(@"~/ExcelTemplate/");</p> <p> //导出Xls文件路径<br /> FileName = context.Server.MapPath(@"~/ExcelFile/");</p> <p> //Xml转DataSet<br /> ds = new DataSet();<br /> ds = XmlBaseCls.DataToXml.ConvertXMLToDataSet(UpLoadStr);</p> <p> //追加Number列<br /> ColumnNames.Add("Number");<br /> foreach (DataColumn dc in ds.Tables[0].Columns)<br /> {<br /> ColumnNames.Add(dc.Caption);<br /> }</p> <p> foreach (string obj in ColumnNames)<br /> dt.Columns.Add(obj);</p> <p> int z=1;<br /> DataRow Tempdr=null; </p> <p> foreach (DataRow dr in ds.Tables[0].Rows)<br /> {<br /> Tempdr = dt.NewRow();<br /> foreach (DataColumn dc in dt.Columns)<br /> {<br /> if (dc.Caption == "Number")<br /> {<br /> Tempdr["Number"] = z.ToString();<br /> }<br /> else<br /> {<br /> Tempdr[dc.Caption] = dr[dc.Caption];<br /> }<br /> }<br /> dt.Rows.Add(Tempdr);<br /> z++;<br /> }<br /> ds.Tables.Add(dt);<br /> }<br /> catch<br /> {<br /> HttpContext.Current.Response.Clear();<br /> HttpContext.Current.Response.ClearContent();<br /> HttpContext.Current.Response.ClearHeaders();<br /> HttpContext.Current.Response.Flush();<br /> HttpContext.Current.Response.End();<br /> return;<br /> }</p> <p> //导出xls文件全路径<br /> FileName = FileName + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";<br /> TemplateFileName = TemplateFilePath + TemplateFileName;<br /> ExcelOutputLib.ExcelHandler excelhandler = new ExcelHandler(TemplateFileName);</p> <p> //excelhandler.DownExcelFile(@"D:/ExcelOutWeb/ExcelFile/2009-11-27-13-46-31.xls");</p> <p> //导出Xls方法<br /> lock ("string")<br /> {<br /> System.Threading.Thread.Sleep(1000);<br /> if (excelhandler.ExportExcel(dt, "", FileName))<br /> {<br /> excelhandler.DownExcelFile(FileName);<br /> }<br /> else<br /> {<br /> HttpContext.Current.Response.Clear();<br /> HttpContext.Current.Response.ClearContent();<br /> HttpContext.Current.Response.ClearHeaders();<br /> HttpContext.Current.Response.Write(excelhandler.ErrMSG);<br /> HttpContext.Current.Response.Flush();<br /> HttpContext.Current.Response.End();<br /> }<br /> }<br /> return;<br /> }</p> <p> public bool IsReusable {<br /> get {<br /> return false;<br /> }<br /> }</p> <p>}

3 Excel通用模板(坐标计算)

using System;<br /> using System.Collections.Generic;<br /> using System.Text;<br /> using System.Data;<br /> using System.Web;<br /> using Excel = Microsoft.Office.Interop.Excel; </p> <p>namespace ExcelOutputLib<br /> {<br /> public class ExcelCoordinate<br /> {<br /> /// <summary><br /> /// 计算WorkSheet多表开始坐标<br /> /// </summary><br /> /// <param name="workSheet"></param><br /> /// <param name="x"></param><br /> /// <param name="y"></param><br /> /// <returns></returns><br /> public ExcelCoordinateData CalBeginCoordinate(Excel._Worksheet workSheet,Int32 x,Int32 y)<br /> {<br /> int i, j;<br /> ExcelCoordinateData coord = new ExcelCoordinateData();<br /> coord.p_X = -1;<br /> coord.p_Y = -1;</p> <p> for (i = 1; i < 4; i++)<br /> {<br /> for (j = 1; j < 4; j++)<br /> {<br /> if (""!=workSheet.Cells[i, j].ToString())<br /> break;<br /> }<br /> if (j != 3)<br /> {<br /> coord.p_X = i;<br /> coord.p_Y = j;<br /> break;<br /> }<br /> else<br /> continue;<br /> }<br /> return coord;<br /> }</p> <p> /// <summary><br /> /// 计算WorkSheet单表坐标<br /> /// </summary><br /> /// <param name="workSheet">workSheet实例名称</param><br /> /// <returns>WorkSheet记录开始坐标</returns><br /> public ExcelCoordinateData CalBeginCoordinate(Excel._Worksheet workSheet)<br /> {<br /> int i,j;<br /> ExcelCoordinateData coord = new ExcelCoordinateData();<br /> coord.p_X = -1;<br /> coord.p_Y = -1;</p> <p> for (i = 1; i < 4; i++)<br /> {<br /> for (j = 1; j < 4; j++)<br /> {<br /> if (""!=((Excel.Range)workSheet.Cells[i, j]).Text.ToString().Trim())<br /> break;<br /> }<br /> if (j != 4)<br /> {<br /> coord.p_X = i+1;<br /> coord.p_Y = j;<br /> break;<br /> }<br /> else<br /> continue;<br /> }<br /> return coord;<br /> }</p> <p> /// <summary><br /> /// 计算WorkSheet结束坐标<br /> /// </summary><br /> /// <param name="x">WorkSheet开始坐标X</param><br /> /// <param name="y">WorkSheet开始坐标Y</param><br /> /// <param name="dt">表格</param><br /> /// <returns>WorkSheet结束坐标</returns><br /> public ExcelCoordinateData CalEndCoordinate(int x,int y,DataTable dt)<br /> {<br /> ExcelCoordinateData coord = new ExcelCoordinateData();<br /> coord.p_X = -1;<br /> coord.p_Y = -1;</p> <p> if (dt == null)<br /> return coord;<br /> else<br /> {<br /> coord.p_X = x + dt.Rows.Count - 1;<br /> coord.p_Y = y + dt.Columns.Count-1;<br /> }<br /> return coord;<br /> }<br /> }<br /> }<br />

 4 Excel通用模板(Excel导出)

using System;<br /> using System.Data;<br /> using System.Collections.Generic;<br /> using System.Text;<br /> using System.IO;<br /> using System.Web;<br /> using System.Diagnostics;<br /> using Excel = Microsoft.Office.Interop.Excel;<br /> using System.Runtime.InteropServices;</p> <p>namespace ExcelOutputLib<br /> {<br /> public class ExcelHandler:IDisposable<br /> {<br /> public ExcelFontInfo excelFontInfo = null; //Excel字体信息<br /> public String ErrMSG = "";<br /> private ExcelCoordinate excelRangeCoord = null; //Excel模板开始坐标计算<br /> private Boolean disposed = false; //资源释放标记<br /> private String ExcelTempfName = "";//Excel模板命名<br /> private DateTime ExcelBeginTime = DateTime.Now;<br /> private DateTime ExcelEndTime = DateTime.Now;</p> <p> private System.Reflection.Missing miss = System.Reflection.Missing.Value;//保存文件默认参数<br /> private Excel.Application app = null; //Excel进程<br /> private Excel.Workbooks workbooks = null;//Excel工作簿<br /> private Excel.Sheets sheets = null;//Excelsheet<br /> private Excel._Worksheet worksheet = null;//当前Excel工作Sheet<br /> private Excel._Workbook workbook = null; ////Excel工作簿<br /> private Excel.Range range = null;</p> <p> #region 构造方法</p> <p> /// <summary><br /> /// 初始化ExcelHandler<br /> /// </summary><br /> /// <param name="ExcelTemplateName">模板文件全路径</param><br /> public ExcelHandler()<br /> {<br /> excelFontInfo = new ExcelFontInfo();<br /> excelRangeCoord = new ExcelCoordinate();<br /> }</p> <p> /// <summary><br /> /// 初始化ExcelHandler<br /> /// </summary><br /> /// <param name="ExcelTemplateName">模板文件全路径</param><br /> public ExcelHandler(String ExcelTemplateName)<br /> {<br /> ExcelTempfName = ExcelTemplateName;<br /> excelFontInfo = new ExcelFontInfo();<br /> excelRangeCoord = new ExcelCoordinate();<br /> }</p> <p> /// <summary><br /> /// 初始化ExcelHandler<br /> /// </summary><br /> /// <param name="ExcelTemplateName">模板文件全路径</param><br /> /// <param name="ExcelFontContext">导出Excel文件字体设定</param><br /> public ExcelHandler(String ExcelTemplateName, ExcelFontInfo ExcelFontContext)<br /> {<br /> ExcelTempfName = ExcelTemplateName;<br /> excelFontInfo = ExcelFontContext;<br /> excelRangeCoord = new ExcelCoordinate();<br /> }<br /> #endregion </p> <p> #region Excel文件操作</p> <p> /// <summary><br /> /// Excel文件输出<br /> /// </summary><br /> /// <param name="Path">Excel模板路径</param><br /> public void DownExcelFile(String FileName)<br /> {<br /> try<br /> {<br /> //string fileName = Path.Combine(path, name);<br /> FileInfo fileInfo = new FileInfo(FileName);<br /> HttpContext.Current.Response.Clear();<br /> HttpContext.Current.Response.ClearContent();<br /> HttpContext.Current.Response.ClearHeaders();<br /> //HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Path.GetFileName(FileName), System.Text.Encoding.UTF8));<br /> HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=OperatorCal.xls");<br /> HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());<br /> HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");<br /> //HttpContext.Current.Response.ContentType = "application/octet-stream";<br /> HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";<br /> HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;<br /> HttpContext.Current.Response.WriteFile(fileInfo.FullName);<br /> HttpContext.Current.Response.Flush();<br /> HttpContext.Current.Response.End();<br /> }<br /> catch (Exception ex)<br /> {<br /> ErrMSG = "6";<br /> }<br /> }</p> <p> #region Excel文件生成</p> <p> /// <summary><br /> /// Excel文件生成<br /> /// </summary><br /> /// <param name="dt">导出文件内容</param><br /> /// <param name="ExcelTemplateName">Excel模板全路径</param><br /> /// <param name="ExportExcelFileName"></param><br /> /// <param name="ColumnNames"></param><br /> /// <returns></returns><br /> public Boolean ExportExcel(DataTable dt, string ExcelTemplateName, string ExportExcelFileName)<br /> {<br /> //如果导出文件大于65535行，则退出<br /> if (IsMaxRowCount(dt) == false)<br /> {<br /> return false;<br /> }<br /> //如果导出文件大于65535行，则退出</p> <p> ExcelCoordinateData BeginCoordData = new ExcelCoordinateData();<br /> ExcelCoordinateData EndCoordData = new ExcelCoordinateData();</p> <p> try<br /> {</p> <p> ExcelBeginTime = DateTime.Now;</p> <p> //1 新建ExcelApplication进程<br /> app = new Excel.Application();</p> <p> if (app == null)<br /> {<br /> return false;<br /> }<br /> app.Visible = false;<br /> app.UserControl = true;<br /> //1 新建ExcelApplication进程</p> <p> ExcelEndTime = DateTime.Now;</p> <p> //2 调用模板工作簿到导出Xls文件内<br /> workbooks = app.Workbooks;<br /> if (ExcelTempfName.Trim() == "")<br /> workbook = workbooks.Add(ExcelTemplateName);<br /> else<br /> workbook = workbooks.Add(ExcelTempfName);<br /> sheets = workbook.Worksheets;<br /> //2 调用模板工作簿到导出Xls文件内</p> <p> //3 获得模板的sheet<br /> worksheet = (Excel._Worksheet)sheets.get_Item(1);<br /> if (worksheet == null)<br /> {<br /> return false;<br /> }<br /> //3 获得模板的sheet</p> <p> //4 计算坐标点<br /> BeginCoordData = excelRangeCoord.CalBeginCoordinate(worksheet);<br /> if (BeginCoordData.p_X == -1)<br /> {<br /> return false;<br /> }<br /> EndCoordData = excelRangeCoord.CalEndCoordinate(BeginCoordData.p_X, BeginCoordData.p_Y, dt);<br /> if (EndCoordData.p_Y == -1)<br /> {<br /> return false;<br /> }<br /> //4 计算坐标点</p> <p> //5 向Xls文件加入数据<br /> for (int i = 0; i < dt.Rows.Count; i++)<br /> {<br /> for (int j = 0; j < dt.Columns.Count; j++)<br /> worksheet.Cells[i + BeginCoordData.p_X, j + BeginCoordData.p_Y] = dt.Rows[i][j].ToString();<br /> }<br /> //5 向Xls文件加入数据</p> <p> //6 为Excle画线<br /> range = app.get_Range(app.Cells[BeginCoordData.p_X, BeginCoordData.p_Y], app.Cells[EndCoordData.p_X, EndCoordData.p_Y]);<br /> //app.Cells.EntireColumn.AutoFit();<br /> range.Borders.LineStyle = 1;<br /> range.HorizontalAlignment = Excel.Constants.xlCenter;<br /> //6 为Excle画线</p> <p> //7 设置字体<br /> range.Cells.Font.Name = excelFontInfo.P_FontName;<br /> range.Cells.Font.Size = excelFontInfo.P_FontSize;<br /> //7 设置字体</p> <p> //8 判断要保存Xls文件是否存在；如果存在则删除<br /> if (File.Exists(ExportExcelFileName))<br /> {<br /> File.Delete(ExportExcelFileName);<br /> }<br /> //8 判断要保存Xls文件是否存在；如果存在则删除</p> <p> //9 保存Xls文件<br /> workbook.SaveAs(ExportExcelFileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlExclusive, miss, miss, miss, miss, miss);<br /> //9 保存Xls文件</p> <p> Dispose();<br /> return true;<br /> }</p> <p> catch (Exception ex)<br /> {<br /> Dispose();<br /> ErrMSG = ex.Message;<br /> return false;<br /> }<br /> }<br /> #endregion</p> <p> #endregion</p> <p> #region 私有方法</p> <p> /// <summary><br /> /// 判断Excel文件最大值<br /> /// </summary><br /> /// <param name="dt">表格内容</param><br /> /// <returns>true成功;flase失败</returns><br /> private Boolean IsMaxRowCount(DataTable dt)<br /> {<br /> try<br /> {<br /> if (dt.Rows.Count > 65535)<br /> return false;<br /> else<br /> return true;<br /> }<br /> catch<br /> {<br /> return false;<br /> }<br /> }</p> <p> #endregion </p> <p> #region 资源释放</p> <p> /// <summary><br /> /// 释放Excel资源<br /> /// </summary><br /> public void Dispose()<br /> {<br /> this.Dispose(true);<br /> //KillExcelProcess();<br /> //GC.SuppressFinalize(this);<br /> }</p> <p> /// <summary><br /> /// 强制杀死Excel进程<br /> /// </summary><br /> private void KillExcelProcess()<br /> {<br /> Process[] myProcesses;<br /> DateTime startTime;<br /> myProcesses = Process.GetProcessesByName("Excel");</p> <p> //得不到Excel进程ID，暂时只能判断进程启动时间<br /> foreach (Process myProcess in myProcesses)<br /> {<br /> try<br /> {<br /> startTime = myProcess.StartTime;</p> <p> if (startTime > ExcelBeginTime && startTime < ExcelEndTime)<br /> {<br /> myProcess.Kill();<br /> }<br /> }<br /> catch<br /> {<br /> }<br /> }<br /> }</p> <p> /// <summary><br /> /// 释放资源<br /> /// </summary><br /> /// <param name="disposing">第一次执行为True;否则为False</param><br /> private void Dispose(bool disposing)<br /> {<br /> // 检查dispose方法是否被调用，如果disposed=True，则Dispose方法已经被调用。<br /> if (!this.disposed)<br /> {<br /> //disposing=True,清理托管资源<br /> if (disposing)<br /> {<br /> //private Excel.Application app = null; //Excel进程<br /> //private Excel.Workbooks workbooks = null;//Excel工作簿<br /> //private Excel.Sheets sheets = null;//Excelsheet<br /> //private Excel._Worksheet worksheet = null;//当前Excel工作Sheet<br /> //private Excel._Workbook workbook = null; ////Excel工作簿<br /> //private Excel.Range range = null;<br /> workbook.Close(null, null, null);<br /> workbooks.Close();<br /> app.Quit();<br /> worksheet = null;<br /> workbook = null;<br /> workbooks = null;<br /> app = null;<br /> range = null;<br /> GC.Collect();<br /> }<br /> //调用合适方法，清理非托管资源<br /> }<br /> disposed = true;<br /> }<br /> #endregion </p> <p> }<br /> }<br />