Gino的筆記本

最近因為專案需要將資料會出EXCEL,所以上網找了資料寫出個匯出的功能,並且可以設定每個Sheet的資料數量

首先要 using Microsoft.Office.Interop.Excel;

        /// <summary>
        /// 匯出資料至EXCEL表並分頁
        /// </summary>
        /// <param name="title">EXCEL表的標題</param>
        /// <param name="dt">傳入的datatable</param>
        /// <param name="pages">每頁資料的比數</param>
        /// <returns>1:成功;2:失敗</returns>

public static string Export_Excel(string[] titles, System.Data.DataTable dt, int pages)
        {
                //引用Excel Application類別
                _Application myExcel = null;
                //引用活頁簿類別
                _Workbook myBook = null;
                //引用工作表類別
                _Worksheet mySheet = null;
                //引用Range類別
                Range myRange = null;

                //開啟一個新的應用程式
                myExcel = new Microsoft.Office.Interop.Excel.Application();
                //加入新的活頁簿
                myExcel.Workbooks.Add(true);
                //停用警告訊息
                myExcel.DisplayAlerts = false;
                //讓Excel文件可見
                myExcel.Visible = false;
                //引用第一個活頁簿
                myBook = myExcel.Workbooks[1];
                ////設定活頁簿焦點
                myBook.Activate();             
               

                int total_page = 1, Cell_Range1 = 0;
                string result_msg = "1";
              
                try
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {


                        if (i % pages == 0)
                        {

                            //加入新的工作表在第N張工作表之後
                            myBook.Sheets.Add(Type.Missing, myBook.Worksheets[total_page], 1, Type.Missing);
                            //引用新加入的工作表
                            mySheet = (_Worksheet)myBook.Worksheets[total_page + 1];
                          
                            //命名工作表的名稱
                            mySheet.Name = total_page.ToString();
                            total_page += 1;
                            Cell_Range1 = 0;
                           

                            //寫入Title
                            int title_count = 0;
                            foreach (string title in titles)
                            {
                                myRange = mySheet.get_Range("A1", Type.Missing); myRange.get_Offset(1, title_count).Select();
                                myRange.get_Range(myExcel.Cells[1, 1 + title_count], myExcel.Cells[1, 1 + title_count]).Select();
                                myExcel.Cells[1, 1 + title_count] = "'" + title.ToString();
                                title_count += 1;
                            }


                        }

                        int Cell_Range2 = 0;

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                           
                            //myData[i, j] = dt.Rows[i][j].ToString();

                            myRange = mySheet.get_Range("A2", Type.Missing); myRange.get_Offset(Cell_Range1, Cell_Range2).Select();

                            //用Cells寫入陣列資料
                            myRange.get_Range(myExcel.Cells[2 + Cell_Range1, 1 + Cell_Range2], myExcel.Cells[2 + Cell_Range1, 1 + Cell_Range2]).Select();
                            myExcel.Cells[2 + Cell_Range1, 1 + Cell_Range2] = "'" + dt.Rows[i][j].ToString();
                            Cell_Range2 =Cell_Range2 + 1;
                        }

                        Cell_Range1 += 1;
                    }
                   
                    //刪除第一個Sheet
                    mySheet = (_Worksheet)myBook.Worksheets[1];
                    mySheet.Delete();                         
           

                    string PathFile = "C:" + @"\test.xls";

                    //另存活頁簿
                    myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    //關閉活頁簿
                    myBook.Close(false, Type.Missing, Type.Missing);

                    //關閉Excel
                    myExcel.Quit();
                    //釋放Excel資源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                    myBook = null;
                    mySheet = null;
                    myRange = null;
                    myExcel = null;
                    GC.Collect();

                   
                }

                catch (Exception ex)
                {
                    result_msg = "2";
                }

                return result_msg;
        }