ASP.NET C# IList/dataset导出excel

原文地址：http://blog.163.com/guyywan_1314/blog/static/128854250200910261022349/

搞了两天，总算彻底搞定。成就啊！哈哈哈。。。。分享一下

---

页面按钮，代码如下：

<asp:Button ID="btnReportExcel" runat="server" Text="导出EXCEL"  OnClick="btnReportExcel_Click" />

---

后台cs代码为：

protected void btnReportExcel_Click(object sender, EventArgs e)
{

          //调用公有方法，弹出选择文件对话框
            string filePath = GetFilePath(); //该方法见下面
            if (filePath == "")
            {
                return;
            }
            if (filePath == string.Empty)
            {
                //如果返回路径是空的，报错

            }
            
            //iList转换成dataSet
            IList personalEnterpriseInfos2 = (List<PersonalEnterpriseInfo>)ViewState["excel_personalInfos"];
            //定义标题数组（导出数据有多少列就有多少项，标题不能相同）
            string[] titles = { "序号", "编号", "企业名称", "法人代表", "地区", "公司电话", "具体地址", "邮编", "创建用户", "主要产品和服务", "行业", "企业性质", "注册资金", "企业规模", "注册日期", "手机", "网站", "传真", "邮箱", "客户状态", "联络状态", "跟踪状态", "企业信息编号", "忽略1", "忽略2", "忽略3" };
           
            //调用Common公有方法，List转换成DataSet, 如果方法返回就是dataSet，就不用转换了
            DataSet dataSet = GetDataSetByList(personalEnterpriseInfos2, titles);
            //创建导出excel的类,调用方法导出excel,类在下面ExportExcel 
            ExportExcel exportExcel = new ExportExcel(ref dataSet, filePath);
            //提示操作成功
            if (exportExcel.Convert())
            {
                MessageBox message = new MessageBox();
                message.MessageIconType = IconType.Ok;
                message.MessageTitle = "操作提示";
                message.MessageBody = "excel已导出成功！";
                message.MessageButtonList.Add(new NavigationUrl("返回", "~/Personal/MyPersonal.aspx", "点击按钮返回！", UrlType.Href, true));
                EventMessage.MessageBox(message);
            }

}

---

//弹出对话框方法

string GetFilePath()

{

string filePath = string.Empty;
            try
            {
                 SaveFileDialog sfd = new SaveFileDialog();//实例化选择打开窗口对象
                sfd.AddExtension = true;
                sfd.DefaultExt = "xls";
                //sfd.Filter = "(*.*)|*.*";//设置可以选择所有文件
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    filePath = sfd.FileName;
                    sfd.Dispose();
                }
                else
                {
                    return filePath;
                }
            }
            catch (Exception)
            {
                return filePath;
            }
            
            return filePath;

}

---

//ilist转换成dataSet方法

public static DataSet GetDataSetByList(IList list, string[] titles)
 {
            DataSet result = new DataSet();
            DataTable _DataTable = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                for (int i = 0; i < propertys.Length; i++)
                {
                    PropertyInfo pi = propertys[i];
                    _DataTable.Columns.Add(titles[i], pi.PropertyType);
                }
                
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    _DataTable.LoadDataRow(array, true);
                }
            }
            result.Tables.Add(_DataTable);
            return result;       

        }

---

dataset数据与 excel互转 的类代码

using System;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

namespace Purdue.CRM.Common
{
    /// <summary>
    /// 将数据集转换成excel工作簿
    /// </summary>

    public class ExportExcel
    {

        private DataSet mDs = new DataSet(); //存放数据源
        private string mFilePath = "d:\\temp.xls"; //excel文件名，保存的路径

        public ExportExcel(ref DataSet ds, string filePath)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.mDs = ds;
            this.mFilePath = filePath;
        }

        /// <summary>
        /// 将数据表转换成excel工作簿中的sheet
        /// </summary>
        /// <param name="tb">要转换的数据表（引用类型）</param>
        /// <param name="xSheet">目标sheet</param>
        /// <param name="SheetName">sheet名字</param>
        /// <returns></returns>
        private bool DataTable2Sheet(ref System.Data.DataTable tb, ref Microsoft.Office.Interop.Excel._Worksheet xSheet, string SheetName)
        {

            try
            {
                int rowIndex = 2;
                int colIndex = 0;
                if (SheetName == "")
                {
                    xSheet.Name = tb.TableName;
                }
                else
                {
                    xSheet.Name = SheetName;
                }
                foreach (DataColumn tempCol in tb.Columns)
                {

                    xSheet.Cells[1, colIndex + 1] = tempCol.ColumnName;

                    rowIndex = 2;
                    foreach (DataRow tempRow in tb.Rows)
                    {
                        xSheet.Cells[rowIndex, colIndex + 1] = "'" + tempRow[colIndex].ToString();
                        rowIndex++;
                    }
                    colIndex++;
                }

                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 将指定数据集里的表转换成工作簿里sheet
        /// </summary>
        /// <param name="starPos">数据表开始位置从0开始计数</param>
        /// <param name="Count">要转换数据表的数目</param>
        /// <returns>成功返回true</returns>
        public bool Convert(int starPos, int Count)
        {
            try
            {
                System.Data.DataTable tempTable; //创建临时表
                Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
                xApp.Visible = false;
                object objOpt = System.Reflection.Missing.Value;

                Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks.Add(true);//添加新工作簿
                Microsoft.Office.Interop.Excel.Sheets xSheets = xBook.Sheets;
                Microsoft.Office.Interop.Excel._Worksheet xSheet = null;
                //
                //转换从指定起始位置以后一定数目的数据集
                //
                for (int i = starPos, iCount = 1; iCount <= Count && i < this.mDs.Tables.Count; i++, iCount++)
                {
                    tempTable = this.mDs.Tables[i];
                    //
                    //创建空的sheet
                    //
                    xSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xBook.Sheets.Add(objOpt, objOpt, objOpt, objOpt));

                    DataTable2Sheet(ref tempTable, ref  xSheet, "");

                }

                //
                //获取默认生成的sheet并将其删除
                //
                //Excel._Worksheet tempXSheet = (Excel._Worksheet) (xSheets.get_Item(1)) ;
                //
                Microsoft.Office.Interop.Excel._Worksheet tempXSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xBook.Worksheets[Count + 1]);
                tempXSheet.Delete();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(tempXSheet);
                tempXSheet = null;
                //
                //保存
                //
                xBook.Saved = true;
                xBook.SaveCopyAs(this.mFilePath);
                //
                //释放资源
                //
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet);
                xSheet = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheets);
                xSheets = null;

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
                xBook = null;
                xApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
                xApp = null;
                GC.Collect();//强行销毁
                return true;
            }
            catch
            {
                return false;
            }

        }

        /// <summary>
        /// 重载convert，将数据集里所有的表转换工作簿的sheet
        /// </summary>
        /// <returns></returns>
        public bool Convert()
        {
            return this.Convert(0, this.mDs.Tables.Count);
        }

    }

    /// <summary>
    /// WorkBook2DataSet 的摘要说明。将工作簿转换成dataset
    /// </summary>
    public class WorkBook2DataSet
    {
        private string mFilePath = "";
        private DataSet mDs = new DataSet();

        public WorkBook2DataSet(string path, ref DataSet ds)
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.mDs = ds;
            this.mFilePath = path;

        }

        /// <summary>
        /// 将工作簿中指定的sheet转换成dataset中的表
        /// </summary>
        /// <param name="pos">sheet在工作簿中的位置</param>
        /// <returns>成功返回true</returns>
        public bool Convert(int pos)
        {
            bool r = false;
            string strSql = "";
            string sheetName = "";
            System.Data.DataTable tTable;
            OleDbDataAdapter objDa;
            //
            //创建excel进程
            //
            object obj = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass xxApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application(); 
            Microsoft.Office.Interop.Excel.Workbook xxBook = null;
            Microsoft.Office.Interop.Excel._Worksheet xxSheet = null;

            try
            {

                //
                //打开excel文件，并获取指定sheet的名字
                //
                xxBook = xxApp.Workbooks.Open(this.mFilePath, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);//添加新工作簿
                xxSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xxBook.Worksheets[pos]);
                sheetName = xxSheet.Name.ToString();
                //
                //释放excel资源
                //
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxSheet);
                xxSheet = null;
                GC.Collect();
                xxBook.Close(false, obj, obj);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxBook);
                xxBook = null;
                xxApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
                xxApp = null;
                //
                //创建数据连接
                //
                OleDbConnection objConn = new OleDbConnection(
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.mFilePath + ";Extended Properties=Excel 8.0;");

                //
                //获取工作簿中的表
                //
                strSql = "select * from [" + sheetName + "$]";
                tTable = new System.Data.DataTable(sheetName);
                //
                //将sheet填入table中
                //
                objDa = new OleDbDataAdapter(strSql, objConn);
                objDa.Fill(tTable);
                this.mDs.Tables.Add(tTable);
                //
                //摧毁连接
                //
                objConn.Dispose();
                r = true;

            }
            catch
            {
                r = false;
            }

            GC.Collect();
            return r;
        }
        /// <summary>
        /// 转换工作簿中所有的sheet到dataset
        /// </summary>
        /// <returns></returns>
        public bool Convert()
        {
            bool r = false; //返回值
            //
            //创建excel进程
            //
            object obj = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
            xApp.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks.Open(this.mFilePath, false, false, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);//

            int count = xBook.Sheets.Count;
            //
            //释放资源
            //
            xBook.Close(false, this.mFilePath, obj);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
            xBook = null;
            xApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
            xApp = null;
            GC.Collect();
            for (int i = 1; i <= count; i++)
            {
                r = Convert(i);
            }

            return r;
            //return this.Convert(1,count) ;
        }
    }
}

---

可能会遇到的问题：

1，弹出文件对话框在后面？这个问题我也很奇怪，也没改什么，自己就好了。

2.弹出对话框的代码"sfd.ShowDialog() == DialogResult.OK"报错，线程问题，页面@page指令，加上AspCompat="true"就好了