利用Aspose.cells 将查询出的数据导出为excel，并在浏览器中下载。

正题前的唠叨

本人是才出来工作不久的小白菜一颗，技术很一般，总是会有遇到一些很简单的问题却不知道怎么做，这些问题可能是之前解决过的。发现这个问题，想着提升一下自己的技术水平，将一些学的新的‘好’东西记录下来，一是加深印象；二是以后可以作为参考；三是希望博友们可以提出不足和可以优化的地方，一起讨论。

本方法是通过ajax直接下载，当然也可以直接button下载，本方法类已经实现导入导出，请执行扩展。谢谢

效果图：点击导出按钮

前端 AJAX

    //导出ececl
function btn_excel(ExeclID) {
        var myConfirmCode = ' <div id="downloaddiv" style="display: none">
        <a id = "downloadRul" href = "" > 
            <p>
            </p>
            </a >
            </div >';



        $("body").append(myConfirmCode);


        $.ajax({
            type: "GET",
            url: "../../tools/downloadExecl.ashx?action=GetExcel",
            data: { "ExeclID": ExeclID, "TempName":"", "Outname":"" },
            dataType: "json",
            success: function (data) {
                if (data.status == 1) {
                    $("#downloadRul").attr("href", data.msg);
                    $('#downloadRul>p').trigger("click");

                    $("#downloaddiv").remove();
                } else {
                    $("#downloaddiv").remove();
                    alert("请登入系统");
                }
            }
        });
   // $("#downloaddiv").remove();

}

主要是导出excel并在浏览器下载下来。 但是会有不同的细微的需求差别。

进入正题

简单的需求描述

将查询出来已经转化好了的list<T> xxx 的数据的某些列，导入到excel之中，并在浏览器中下载下来。

public void GetExcel(HttpContext context)
        {  
            string   ExeclID = MXRequest.GetString("ExeclID");//导出模板ID，管理sql
            BLL.InfoExecl infoExeclbll = new BLL.InfoExecl();
         infoExecl=  infoExeclbll.GetModel(MyCommFun.Obj2Int( ExeclID));
            if (Outname != "") {
                infoExecl.titlename = Outname;
            }
            if (TempName != "")
            {
                infoExecl.title = TempName;
            }
           

            DataTable dt = new DataTable(); // DataTable 数据源
        
            string download = Manage.ExeclOUtAjax(infoExecl.Sql, infoExecl.title, infoExecl.titlename);

            context.Response.Write("{"status":1, "msg": ""+ download + ""}");
        
        }

        /// <summary>
        /// 导出Exec方法 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="temppathName"></param>
        /// <param name="Outname"></param>
        public string  ExeclOUtAjax(string sql, string temppathName, string Outname)
        {
          string timeflie=  DateTime.Now.ToString("yyyyMMddHHmmss");
            DataSet ds = DBUtility.DbHelperSQL.Query(sql);
            //导出数据
            string temppath = Server.MapPath("\UpFile");
            string filepath = temppath + "\ExportExcel\" + temppathName + ".xls";//临时文件,也作为模板文件
            string downUrl = "\ExcelOut\" + timeflie+ Outname+ ".xls";//导出文件路径
            string downUrlAjax = "/UpFile/ExcelOut/" + timeflie + Outname + ".xls";//下载文件路径
            string outfilepath = temppath + downUrl;//导出文件路径
            AsposeExcel asposeexcel = new AsposeExcel(outfilepath, filepath);
            asposeexcel.DatatableToExcel(ds.Tables[0]);
            //return DownFile("\UpFile" + downUrl, DateTime.Now.ToString("yyyyMMdd") + "零售品牌汇总导出.xls");
            //获取项目绝对路径地址
            string url = HttpContext.Current.Request.Url.AbsoluteUri.ToString().Split('/')[0] + "//" + HttpContext.Current.Request.Url.Authority.ToString();
            var virtualPath = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            string fileName = "";
            if (virtualPath != "/")
            {
                //有子应用程序
                fileName = virtualPath + "/UpFile/";
            }
            else
            {
              fileName = "/Document/TemporaryDocuments/" + tempExcelName;
            }

            //清除历史文件，避免历史文件越来越多，可进行删除
            DirectoryInfo dyInfo = new DirectoryInfo(HttpContext.Current.Server.MapPath("/UpFile/ExcelOut/"));
            //获取文件夹下所有的文件
            foreach (FileInfo feInfo in dyInfo.GetFiles())
            {
                //判断文件日期是否小于两天前，是则删除
                if (feInfo.CreationTime < DateTime.Today.AddDays(-2))
                    feInfo.Delete();

            }


            return url+ downUrlAjax;


        }

　　

　　

后端 公共操作类

AsposeExcel.cs 类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Aspose.Cells;
using System.IO;
using System.Web;

namespace Flysem.Common
{
    public class AsposeExcel
    {
        private string outFileName = "";
        private string fullFilename = "";
        private Workbook book = null;
        private Worksheet sheet = null;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="outfilename">导出文件路径</param>
        /// <param name="tempfilename">模板文件路径</param>
        public AsposeExcel(string outfilename, string tempfilename) //导出构造数
        {
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense("Aspose.Cells.lic");

            outFileName = outfilename;
            book = new Workbook();
            //book.Open(tempfilename);//这里我们暂时不用模板
                                    //  打开 Excel 模板
            book = File.Exists(tempfilename) ? new Workbook(tempfilename) : new Workbook();
            sheet = book.Worksheets[0];
        }
        /// <summary>
        /// 保存文件
        /// </summary>
       
        /// <param name="name"></param>
        public void SaveReprotFile( string name)
        {
         //  book.Save(System.Web.HttpContext.Current.Response, "学生信息.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));

            book.Save(System.Web.HttpContext.Current.Response, HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + ".xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
            //try
            //{
            //   // filePath = Server.MapPath(filePath);
            //    if (File.Exists(name))
            //    {
            //        FileInfo info = new FileInfo(name);
            //        long fileSize = info.Length;
            //        HttpContext.Current.Response.Clear();
            //        HttpContext.Current.Response.ContentType = "application/octet-stream";
            //        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachement;filename=" + (info.FullName.));
            //        //指定文件大小   
            //        HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
            //        HttpContext.Current.Response.WriteFile(name, 0, fileSize);
            //        HttpContext.Current.Response.Flush();
            //    }
            //}
            //catch
            //{ }

            //#region 输出到Excel
            //using (MemoryStream ms = new MemoryStream())
            //{
            //    //  生成的文件名称
            //    // string ReportFileName = string.Format("Excel_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd") + name);

            //    book.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));//默认支持xls版，需要修改指定版本
            //    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name));
            //    System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            //    System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            //    book = null;
            //    System.Web.HttpContext.Current.Response.End();
            //} 
            //#endregion
        }
   
        /// <summary>
        /// 
        /// </summary>
        /// <param name="outfilename">导出文件路径</param>
        /// <param name="tempfilename">导入文件路径</param>
        /// <param name="tempfilename">模板文件路径</param>
        public AsposeExcel(string outfilename, string fullfilename, string tempfilename) //导出构造数
        {
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense("Aspose.Cells.lic");

            outFileName = outfilename;
            fullFilename = fullfilename;
            book = new Workbook();
            // book.Open(tempfilename);这里我们暂时不用模板
            sheet = book.Worksheets[0];
        }
        /// <summary>
        /// 导入文件路径
        /// </summary>
        /// <param name="fullfilename"></param>
        public AsposeExcel(string fullfilename) //导入构造数
        {
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense("Aspose.Cells.lic");

            fullFilename = fullfilename;
            // book = new Workbook();
            // book.Open(tempfilename);
            // sheet = book.Worksheets[0];
        }
        private void AddTitle(string title, int columnCount)
        {
            sheet.Cells.Merge(0, 0, 1, columnCount);
            sheet.Cells.Merge(1, 0, 1, columnCount);
            Cell cell1 = sheet.Cells[0, 0];
            cell1.PutValue(title);
            Style style = new Style();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Name = "黑体";
            style.Font.Size = 14;
            style.Font.IsBold = true;
            Cell cell2 = sheet.Cells[1, 0];
            cell1.PutValue("查询时间：" + DateTime.Now.ToLocalTime());
            cell2.SetStyle(style);
        }
        private void AddHeader(DataTable dt)
        {
            Cell cell = null;
            Style style = new Style();
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = sheet.Cells[0, col];
                cell.PutValue(dt.Columns[col].ColumnName);
                style.Font.IsBold = true;
                cell.SetStyle(style);
            }
        }
        private void AddBody(DataTable dt)
        {
            Style style = new Style();
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    sheet.Cells[r+1, c].PutValue(dt.Rows[r][c].ToString());
                    style.Font.Name = "宋体";
                    style.Font.Size = 11;
                    sheet.Cells[r + 1, c].SetStyle(style);
                    //r+数值,这个数值再加1表示从第几行开始
                    //sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                }
            }
        }

        //导出------------下一篇会用到这个方法
        public Boolean DatatableToExcel(DataTable dt)
        {
            Boolean yn = false;
            try
            {
                //sheet.Name = sheetName;
                //AddTitle(title, dt.Columns.Count);
                //AddHeader(dt);
                AddBody(dt);
                sheet.AutoFitColumns();
                //sheet.AutoFitRows();
                book.Save(outFileName);
                yn = true;
                return yn;
            }
            catch (Exception e)
            {
                return yn;
                // throw e;
            }
        }
        public DataTable ExcelToDatatalbe()//导入
        {
            Workbook book = new Workbook();
            book.Open(fullFilename);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
            // dt_Import.
            return dt_Import;
        }
    }
}