軒袁寨

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace excel_test
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        internal struct test
        {
            public int Id { get; set; }

            public string Name { get; set; }

            public int Eng { get; set; }

            public int math { get; set; }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var list = new List<test>();
            list.Add(new test { Id = 1, Name = "joe", Eng = 90, math = 90 });
            list.Add(new test { Id = 2, Name = "jack", Eng = 60, math = 100 });
            list.Add(new test { Id = 2, Name = "ann", Eng = 100, math = 80 });
            dataGridView1.DataSource = list;

            ExportExcel(dataGridView1);
            GC.Collect();  //再度強制釋放回收
        }

        #region excel

        private void ExportExcel(DataGridView myDGV)
        {
            //bool fileSaved = false;
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
                return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("無法建立Excel，可能您的電腦未安装Excel");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            worksheet.get_Range(worksheet.Cells[1, 8], worksheet.Cells[1, 14]).MergeCells = true; //左右合併
            worksheet.get_Range(worksheet.Cells[2, 8], worksheet.Cells[2, 14]).MergeCells = true; //左右合併
            worksheet.get_Range(worksheet.Cells[1, 8], worksheet.Cells[2, 8]).MergeCells = true; //上下合併 1.2列合併
            worksheet.get_Range(worksheet.Cells[3, 8], worksheet.Cells[3, 14]).MergeCells = true; //左右合併
            worksheet.get_Range(worksheet.Cells[4, 8], worksheet.Cells[4, 14]).MergeCells = true; //左右合併
            worksheet.get_Range(worksheet.Cells[3, 8], worksheet.Cells[4, 8]).MergeCells = true; //上下合併 3.4列合併

            worksheet.Cells[1, 8] = "XX學生資料表";   
            worksheet.Cells[3, 8] = "10X學年成績單";
            worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 12]).Cells.Font.Size = 15;
            worksheet.get_Range(worksheet.Cells[3, 6], worksheet.Cells[3, 12]).Cells.Font.Size = 15;
            worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 12]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            worksheet.get_Range(worksheet.Cells[3, 6], worksheet.Cells[3, 12]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            worksheet.get_Range(worksheet.Cells[3, 6], worksheet.Cells[3, 12]).Cells.Font.Bold = true; //粗體字
            worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 12]).Cells.Font.Bold = true;

            //寫入欄位名稱(姓名、學號....) 依照Datagridview 的欄位名稱
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                // 依照上面合併使用過的列數(最大值+1) ，從第五列開始寫欄位名稱
                worksheet.Cells[5, i + 1] = myDGV.Columns[i].HeaderText;
            }
            ////塞入數字
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    if (int.Parse(myDGV.Rows[r].Cells[2].Value.ToString()) <= 70 && int.Parse(myDGV.Rows[r].Cells[2].Value.ToString()) >= 60)
                    {
                        worksheet.get_Range("C" + (r + 6).ToString(), "C" + (r + 6).ToString()).Interior.ColorIndex = 39;

                        //http://blog.csdn.net/dkman803/article/details/1904194 //顏色表
                    }

                    worksheet.Cells[r + 6, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//自動調整欄位

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);

                    //       fileSaved = true;
                }
                catch (Exception ex)
                {
                    //          fileSaved = false;
                    MessageBox.Show("匯出文件時出錯,EXCEL文件可能正在使用中！\n" + ex.Message);
                }
            }

            //else
            //{
            //    fileSaved = false;
            //}
            xlApp.Quit();
            GC.Collect();  //回收釋放建議是額外放在外面

            //判斷檔案條件是否都成立，沒問題就開啟EXCEL
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //開啟EXCEL
            MessageBox.Show(saveFileName + "的資料匯出成功", "提示", MessageBoxButtons.OK);
        }

        #endregion excel
    }
}