2015/03/28

C#.Net DataTable to Excel File


using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Sample
{
    public partial class Form1 : Form
    {
        private readonly String[] Headers = { "姓名", "座號", "國文成績" };
        private readonly String[] Users = { "王小明", "陳大吉", "洪船長" };
        private readonly int[] CH_Score = { 57, 80, 92 };
        private readonly int MAX = 3;
        private DataTable table = new DataTable();


        public Form1()
        {
            InitializeComponent();
            table.Columns.Add("Header");
            table.Columns.Add("User");
            table.Columns.Add("Score");
        }


        private void button1_Click(object sender, EventArgs e)
        {
            for (int index = 0; index < MAX; index++)
            {
                DataRow row = table.NewRow();
                row["Header"] = Headers[index];
                row["User"] = Users[index];
                row["Score"] = CH_Score[index];
                table.Rows.Add(row);
            }

            DataTableToCSV(table);
        }

        private void DataTableToCSV(DataTable table)
        {
            Excel.Application excel = new Excel.Application();

            try
            {
                if (excel == null)
                    throw new Exception();
                //不顯示Excel
                excel.Visible = false;

                //不顯示Excel錯誤
                excel.DisplayAlerts = false;

                Excel._Workbook workbook = excel.Workbooks.Add();

                //Excel已1為開頭
                Excel._Worksheet sheet = (Excel._Worksheet)workbook.Worksheets[1];


                int point = 0;

                foreach (var row in table.AsEnumerable())
                {
                    int maxLength = row.ItemArray.Length;
                    for (int index = 0; index < maxLength; index++)
                        sheet.Cells[point + 1, index + 1] = row.ItemArray[index];

                    point++;
                }

                workbook.SaveAs(String.Format("{0}\\{1}.xls", Directory.GetCurrentDirectory(), DateTime.Now.Ticks),
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                      Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
                      Type.Missing, Type.Missing, Type.Missing);


                workbook.Close(false, Type.Missing, Type.Missing);
                workbook = null;
                sheet = null;
                excel = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show("無法將資料寫入Excel");
            }
        }

    }
}



執行結果: