~楓花雪岳~: [C#] DataGridView 匯出 Excel

星期五, 8月 28, 2015

[C#] DataGridView 匯出 Excel

論壇問題 - 把 DataGridView 內資料匯出 Excel

Employ Class

namespace DataGridView2Excel
{
    public class Employ
    {
        public string EmpNO { get; set; }
        public string EmpName { get; set; }
        public DateTime Birthday { get; set; }
        public decimal Salary { get; set; }
    }
}

WinForm C# Demo

using Excel = Microsoft.Office.Interop.Excel;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = GetSource();
        }

        private List<Employ> GetSource()
        {
            List<Employ> source = new List<Employ>();
            source.Add(new Employ() { EmpNO = "001", EmpName = "張三", Birthday = new DateTime(1970, 1, 1), Salary = 55000 });
            source.Add(new Employ() { EmpNO = "002", EmpName = "李四", Birthday = new DateTime(1981, 11, 11), Salary = 35000 });
            source.Add(new Employ() { EmpNO = "003", EmpName = "王五", Birthday = new DateTime(1985, 7, 7), Salary = 25000 });
            return source;
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog save = new SaveFileDialog();
            save.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            save.FileName = "Excel Export demo";
            save.Filter = "*.xlsx|*.xlsx";
            if (save.ShowDialog() != DialogResult.OK) return;

            // Excel 物件
            Excel.Application xls = null;
            try
            {
                xls = new Excel.Application();
                // Excel WorkBook
                Excel.Workbook book = xls.Workbooks.Add();
                // Excel WorkBook，預設會產生一個 WorkSheet，索引從 1 開始，而非 0
                // 寫法1
                Excel.Worksheet Sheet = (Excel.Worksheet)book.Worksheets.Item[1];
                // 寫法2
                Excel.Worksheet Sheet = (Excel.Worksheet)book.Worksheets[1];
                // 寫法3
                Excel.Worksheet Sheet = xls.ActiveSheet;

                // 把 DataGridView 資料塞進 Excel 內
                DataGridView2Excel(Sheet);
                // 儲存檔案
                book.SaveAs(save.FileName);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                xls.Quit();
            }
        }

        private void DataGridView2Excel(Excel.Worksheet Sheet)
        {
            // 下面方法二選一使用
            // 利用 DataGridView 
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    string value = dataGridView1[j, i].Value.ToString();
                    Sheet.Cells[i + 1, j + 1] = value;
                }
            }

            // 利用 List<Employ>
            List<Employ> empList = (List<Employ>)dataGridView1.DataSource;
            foreach (Employ emp in empList)
            {
                int
                    rowindex = empList.IndexOf(emp) + 1,
                    colindex = 1;

                Sheet.Cells[rowindex, colindex++] = emp.EmpNO;
                Sheet.Cells[rowindex, colindex++] = emp.EmpName;
                Sheet.Cells[rowindex, colindex++] = emp.Birthday;
                Sheet.Cells[rowindex, colindex++] = emp.Salary;
            }   
        }
    }
}