導出excel方法學習並比較!

        最近都在學習導出excel的方法，從網絡上找了不少方法，感覺網絡真的是太神奇了，什麼東西都能找到!
現在特向大家分享我所收集整理出來的一些方法，希望對大家能有所幫助!
1.Stream導出(名字亂起的)，使用流導出文件超級快，不用擔心office版本問題，文件又小，比一般文件小一半，因為沒有任何格式，相當不錯。缺點當然也是沒有格式啊，大家可以想想辦法的!

DataGridView，ListView導出方法
 1/**//// <summary>
 2        /// 導出ListView數據
 3        /// </summary>
 4        /// <param name="aListView">輸入ListView控件</param>
 5        /// <param name="aFilePath">輸入文件名稱路徑</param>
 6        /// <param name="abIsVisible">設置是否打開文件</param>
 7        /// <returns>是否導出成功</returns>
 8        public static bool StreamWriterToExcel_ListView(ListView aListView, string aFilePath, bool abIsVisible)
 9        {
10            bool bIsExport = false;
11            try
12            {
13                System.IO.StreamWriter sw = new System.IO.StreamWriter(aFilePath, false, System.Text.Encoding.Default);
14                int ColumnCount = aListView.Columns.Count;
15                int RowCount = aListView.Items.Count - 1;
16                for (int i = 0; i < ColumnCount; ++i)
17                {
18                    sw.Write(aListView.Columns[i].Text.ToString());
19                    sw.Write('\t');
20                }
21                sw.Write("\r\n");
22                for (int i = 0; i < RowCount; i++)
23                {
24                    for (int j = 0; j < ColumnCount; j++)
25                    {
26                        sw.Write(aListView.Items[i].SubItems[j].Text.ToString());
27                        if (CMFormat.IsNumeric(aListView.Items[i].SubItems[j].Text.ToString()) || CMFormat.IsDataTime(aListView.Items[i].SubItems[j].Text.ToString()))
28                        {
29                            sw.Write("'"+aListView.Items[i].SubItems[j].Text.ToString());
30                        }
31                        else
32                        {
33                            sw.Write(aListView.Items[i].SubItems[j].Text.ToString());
34                        }
35                        sw.Write('\t');
36                    }
37                    sw.Write("\r\n");
38                }
39                sw.Flush();
40                sw.Close();
41                bIsExport = true;
42            }
43            catch (Exception ex)
44            {
45                throw ex;
46            }
47            return bIsExport;
48        }
49
50        /**//// <summary>
51        /// 導出DataGridView數據
52        /// </summary>
53        /// <param name="aDataGridView">輸入DataGridView控件</param>
54        /// <param name="aFilePath">輸入文件名稱路徑</param>
55        /// <param name="abIsVisible">設置是否打開文件</param>
56        /// <returns>是否導出成功</returns>
57        public static bool StreamWriterToExcel_DataGridView(DataGridView aDataGridView, string aFilePath, bool abIsVisible)
58        {
59            bool bIsExport = false;
60            try
61            {
62                System.IO.StreamWriter sw = new System.IO.StreamWriter(aFilePath, false, System.Text.Encoding.UTF8);
63                int ColumnCount = aDataGridView.ColumnCount;
64                int RowCount = aDataGridView.RowCount - 1;
65                for (int i = 0; i < aDataGridView.ColumnCount; ++i)
66                {
67                    sw.Write(aDataGridView.Columns[i].HeaderText.ToString());
68                    sw.Write('\t');
69                }
70                sw.Write("\r\n");
71                for (int i = 0; i < RowCount; i++)
72                {
73                    for (int j = 0; j < ColumnCount; ++j)
74                    {
75                        if (CMFormat.IsNumeric(aDataGridView[j, i].Value.ToString())||CMFormat.IsDataTime(aDataGridView[j, i].Value.ToString()))
76                        {
77                            sw.Write("'"+aDataGridView[j, i].Value.ToString());
78                        }
79                        else
80                        {
81                            sw.Write(aDataGridView[j, i].Value.ToString());
82                        }
83                        sw.Write('\t');
84                    }
85
86                    sw.Write("\r\n");
87                }
88                sw.Flush();
89                sw.Close();
90                bIsExport = true;
91            }
92            catch (Exception ex)
93            {
94                MessageBox.Show(ex.ToString());
95            }
96            return bIsExport;
97        }

2.ClipboardToExcel就是利用剪貼板導出到excel，同樣不需要excel控件，速度很快，也有格式，文件大小一般， 推薦使用。

利用剪貼板把信息傳遞給EXCEL中
/**//// <summary>
        /// 利用剪貼板把信息傳遞給EXCEL中
        /// </summary>
        /// <param name="aDataView">Datagridview物件</param>
        /// <param name="aFileName">要存放文件的文件名</param>
        /// <returns>是否存放成功</returns>
        public static bool ClipboardToExcel(DataGridView aDataView, string aFileName)
        {
            bool bIsExport = false;
            string sExcelData = null;
            for (int iTitle = 0; iTitle < aDataView.ColumnCount; iTitle++)
            {
                sExcelData += aDataView.Columns[iTitle].HeaderText + "\t";
            }
            sExcelData += "\r\n";
            for (int irow = 0; irow < aDataView.RowCount - 1; irow++)
            {
                for (int icol = 0; icol < aDataView.Columns.Count; icol++)
                {
                    if (CMFormat.IsNumeric(aDataView[icol, irow].Value.ToString()) || CMFormat.IsDataTime(aDataView[icol, irow].Value.ToString()))
                    {
                        sExcelData += "'" + aDataView[icol, irow].Value.ToString() + "\t";
                    }
                    else
                    {
                        sExcelData += aDataView[icol, irow].Value.ToString() + "\t";
                    }
                }
                sExcelData += "\r\n";
            }
            object objbook = Type.Missing;
            System.Windows.Forms.Clipboard.SetDataObject(sExcelData);
            Excel.Application m_objExcel = new Excel.Application();
            Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            try
            {
                Excel._Workbook m_objBook = (Excel._Workbook)(m_objBooks.Add(objbook));
                Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
                Excel.Range m_objRange = m_objSheet.get_Range("A1", objbook);
                m_objSheet.Paste(m_objRange, false);
                for (int i = 1; i <= aDataView.Columns.Count; i++)
                {
                    m_objRange = m_objExcel.get_Range(m_objSheet.Cells[1, i], m_objSheet.Cells[1, i]);
                }
                m_objSheet.Columns.Font.Name = "Arial";
                m_objSheet.Columns.EntireColumn.AutoFit();//列寬自適應。
                m_objBook.SaveCopyAs(aFileName);
                m_objBook.Saved = true;
                bIsExport = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            m_objBooks.Close();
            m_objExcel.Quit();
            GC.Collect();
            return bIsExport;
        }

3.在孟子e章裡看到的從web上導gridview數據，優點是不用exce物件，速度快，可是真能在web導出有用，windows ap好象就不行了，大家有誰知道請告知啊!謝謝!

web中把GridView數據導出到Excel中
/**//// <summary>
        /// 把GridView數據導出到Excel中
        /// </summary>
        /// <param name="agridview">目標Gridview</param>
        public static void WebResponseExportToExcel_GridView(GridView agridview)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            //Response.Charset = "GB2312";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
            // 如果設置為 GetEncoding("GB2312");導出的檔將會出現亂碼！！！
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;
            //設置輸出檔類型為excel檔。 
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            agridview.RenderControl(oHtmlTextWriter);
            HttpContext.Current.Response.Output.Write(oStringWriter.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }

4.利用excel物件中的rang的value屬性直接賦值，利用excel物件，建議版本97/2000，否則可能不向下兼容啊!

導出ListView數據
/**//// <summary>
        /// 導出ListView數據
        /// </summary>
        /// <param name="aListView">輸入ListView控件</param>
        /// <param name="aFilePath">輸入文件名稱路徑</param>
        /// <param name="abIsVisible">設置是否打開文件</param>
        /// <returns>是否導出成功</returns>
        public static bool RangExportToExcel_ListView(ListView aListView, string aFilePath, bool abIsVisible)
        {
            bool bIsExport = false;
            object[,] cache = new object[aListView.Items.Count + 1, aListView.Columns.Count];
            for (int i = 0; i < aListView.Columns.Count; i++)
            {
                cache[0, i] = aListView.Columns[i].Text;
                for (int j = 0; j < aListView.Items.Count; j++)
                {
                    cache[j + 1, i] = aListView.Items[j].SubItems[i].Text;
                }
            }
            try
            {
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook bookApp = excelApp.Workbooks.Add(Type.Missing);
                Excel.Worksheet sheetApp = (Excel.Worksheet)bookApp.Sheets[1];
                string sRange = string.Format("A1:{0}{1}", (char)('A' + (aListView.Columns.Count - 1)), aListView.Items.Count + 1);
                sheetApp.get_Range(sRange, Type.Missing).Value2 = cache;
                bookApp.SaveCopyAs(aFilePath);
                bookApp.Saved = true;
                excelApp.Visible = abIsVisible;
                bIsExport = true;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            return bIsExport;
        }

其中有做格式化的部分大家可以注釋掉，當然，還有其它的方法，比如逐個讀取就沒有必要了，浪費生命的事情我是不做的!
其實我也是在找更好的方法，希望大家不吝賜教啊!
小弟第一次發貼，多多包涵啊，哈哈!
所有代碼在此：Export