Asp.net 生成 导出 Excel 下载

Asp.net 生成 导出 Excel 下载

// 方法一：使用DCOM 生成Excel

private void export(HttpContext context)
        {
            string ret = "";

            try
            {
                // 参数取得
                string DOCNO = Operate.getParameter(context, "DOCNO");
                string BASENO = Operate.getParameter(context, "BASENO");

                NRSS.BLL.XMK_Z_GCYS_CPXX_FB bll = new NRSS.BLL.XMK_Z_GCYS_CPXX_FB();

                DataSet dsFB = bll.GetListExt("DOCNO = '" + DOCNO + "'");
                if (dsFB.Tables.Count <= 0 || dsFB.Tables[0].Rows.Count < 0)
                {
                    throw new Exception("没有分布工程数据");
                }

                System.Data.DataTable dtFB = dsFB.Tables[0];

                Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
                if (appExcel == null)
                {
                    throw new Exception("Excel程序启动失败。");
                }

                // ------------ Excee 操作 -------------
                appExcel.Visible = false;
                appExcel.UserControl = true;
                Object miss = Missing.Value;

                if (appExcel.Workbooks.Count <= 0)
                {
                    appExcel.Workbooks.Add(miss);
                }

                Microsoft.Office.Interop.Excel.Workbook workbook = appExcel.ActiveWorkbook;

                string[] colTitleFB = { "ID", "预算单号", "分布工程编号", "分布工程名称", "排序", "数量", "说明", "备注" };
                string[] colTitleQD = { "ID", "预算单号", "分布工程编号", "分布工程名称", "排序", "数量", "说明", "备注" };
                string[] colTitleFX = { "ID", "预算单号", "分布工程编号", "分布工程名称", "排序", "数量", "说明", "备注" };
                string[] colTitleXY = { "ID", "预算单号", "分布工程编号", "分布工程名称", "排序", "数量", "说明", "备注" };

                int iRowsCntFB = dtFB.Rows.Count;
                for (int i = 0; i < iRowsCntFB; i++)
                {
                    DataRow drFB = dtFB.Rows[i];

                    Microsoft.Office.Interop.Excel.Worksheet wsheet = null;

                    wsheet = workbook.Worksheets.Add(miss, miss, miss, miss);

                    string FB_NO = drFB["FB_NO"].ToString();

                    wsheet.Name = FB_NO;
                    wsheet.Cells[1, 1] = "分布工程信息";

                    writeDataFB(wsheet, 2, drFB);


                }


                string date = DateTime.Now.ToString("yyyyMMddHHmmss");
                string fileName = "工程预算_" + BASENO +"_"+ DOCNO + "_" + date + ".xls";

                int FormatNum;
                string Version;//excel版本号
                Version = appExcel.Version;//获取你使用的excel 的版本号
                if (Convert.ToDouble(Version) < 12)//You use Excel 97-2003
                {
                    FormatNum = -4143;
                }
                else//you use excel 2007 or later
                {
                    FormatNum = 56;
                }

                string strFullPath = context.Server.MapPath("~/temp_ckd/" + fileName);
                workbook.SaveAs(strFullPath, FormatNum, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                appExcel.Quit();

                excelReleaseObject(workbook);

                excelReleaseObject(appExcel);

                downloadExcel(context, fileName, strFullPath);
            }
            catch (Exception ex)
            {
                ret = Operate.getJSONResult("error", ex.Message);
            }
        }

        private string getRowDataStr(DataRow dr, int col)
        {
            if (dr.IsNull(col))
            {
                return "";
            }

            if (dr[col] == null)
            {
                return "";
            }

            return dr[col].ToString();
        }

private void excelReleaseObject(Object obj)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        }
        private void downloadExcel(HttpContext context, string fileName, string strFullPath)
        {
            // 取得下载文件
            string strFileName = fileName;

            {
                context.Response.Buffer = true;
                context.Response.Clear();

                context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                context.Response.ContentType = "application/ms-excel;charset=gb2312";

                string downFile = System.IO.Path.GetFileName(strFileName);
                string EncodeFileName = HttpUtility.UrlEncode(downFile, System.Text.Encoding.UTF8);
                context.Response.AddHeader("Content-Disposition", "attachment;filename=" + EncodeFileName + ";");
                context.Response.BinaryWrite(System.IO.File.ReadAllBytes(strFullPath));//返回文件数据给客户端下载
                context.Response.Flush();

                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
        }

// 方法二： 不使用DCOM 生成Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

using System.Data.OleDb;
using System.Text;

namespace XXX.YYY
{
    public class ExportExcelHelper
    {
        //  当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
        //  当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
        //  当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
        private const string CONNECTION_FORMAT = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=0\"";

        public static void CreateSheet(string argPath, string argSheetName, string[] argColNames)
        {
            OleDbConnection OleDb_Conn = new OleDbConnection();

            OleDb_Conn.ConnectionString = string.Format(CONNECTION_FORMAT, argPath);

            try
            {
                OleDb_Conn.Open();
                OleDbCommand OleDb_Comm = new OleDbCommand();

                OleDb_Comm.Connection = OleDb_Conn;
                string strCmd;

                try 
                {
                    strCmd = "drop table [" + argSheetName + "]";
                    OleDb_Comm.CommandText = strCmd;
                    OleDb_Comm.ExecuteNonQuery();
                }
                catch{}

                strCmd = "create Table [" + argSheetName + "](";

                for (int i=0; i< argColNames.Length; i++)
                {
                    strCmd += "["+argColNames[i]+"] TEXT,";
                }

                strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1);//delete the last comma

                strCmd += ")";

                OleDb_Comm.CommandText = strCmd;
                OleDb_Comm.ExecuteNonQuery();

                OleDb_Conn.Close();
                OleDb_Conn = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (OleDb_Conn != null)
                {
                    OleDb_Conn.Close();
                    OleDb_Conn = null;
                }
            }
        }

        public static void WriteSheetData(string argPath, string argSheetName, DataRow[] dtrows, string[] argColNames)
        {
            OleDbConnection OleDb_Conn = new OleDbConnection();

            OleDb_Conn.ConnectionString = string.Format(CONNECTION_FORMAT, argPath);

            StringBuilder CommText = new StringBuilder();

            try
            {
                OleDb_Conn.Open();

                int col = argColNames.Length;
                foreach (DataRow dr in dtrows)
                {
                    CommText.Append("INSERT INTO [" + argSheetName + "] VALUES(");
                    for (int i = 0; i < col; i++)
                    {
                        if (i + 1 == col)
                        {
                            CommText.Append("'" + ReplaceAll(dr[i].ToString()) + "'");
                        }
                        else
                        {
                            CommText.Append("'" + ReplaceAll(dr[i].ToString()) + "',");
                        }
                    }
                    CommText.Append(");");

                    using (OleDbCommand cmd = new OleDbCommand(CommText.ToString().Replace(':', ';').TrimEnd(';'), OleDb_Conn))
                    {
                        //导出数据
                        cmd.ExecuteNonQuery();

                    }
                    CommText.Remove(0, CommText.Length);
                }
            }
            catch (Exception ex) 
            {
                throw ex;
            }
            finally
            {
                if (OleDb_Conn != null)
                {
                    OleDb_Conn.Close();
                    OleDb_Conn = null;
                }
            }
        }

        private static string ReplaceAll(object val)
        {
            if (val == null)
            {
                return "";
            }

            return val.ToString();
        }
    }
}