C# 將List中的資料匯入Excel檔案中

上篇文章說到了將List資料匯入CSV檔案中，這邊文章繼續此類型，簡單的介紹一下將List中的資料匯入Excel檔案中。

具體代碼如下所示：

由於是Excel檔案，所以要用到Office相關的dll，故請添加相應dll的引用，然後在程式中添加如下命名空間：

        using Microsoft.Office.Interop.Excel;

 

Student類：

    public class Student    {        private string id;        public string Id { get { return id; } set { id = value; } }        private string name;        public string Name { get { return name; } set { name = value; } }        private string age;        public string Age { get { return age; } set { age = value; } }    }

 

產生簡單的類比資料：

        private List<Student> GetStudentData()        {            List<Student> studentList = new List<Student>();            Student s1 = new Student();            s1.Id = "1";            s1.Name = "haha";            s1.Age = "10";            Student s2 = new Student();            s2.Id = "2";            s2.Name = "xixi";            s2.Age = "20";            Student s3 = new Student();            s3.Id = "3";            s3.Name = "lolo";            s3.Age = "30";            studentList.Add(s1);            studentList.Add(s2);            studentList.Add(s3);            return studentList;        }

 

用反射擷取類型的所有屬性（以便後續產生所有Column的標題）：

        private PropertyInfo[] GetPropertyInfoArray()        {            PropertyInfo[] props = null;            try            {                Type type = typeof(EricSunApp.Student);                object obj = Activator.CreateInstance(type);                props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);            }            catch (Exception ex)            { }            return props;        }

 

遍曆List，將資料儲存成Excel檔案：

        private void SaveDataToExcelFile(List<Student> studentList, string filePath)        {            object misValue = System.Reflection.Missing.Value;            Application xlApp = new Application();            Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);            Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);            PropertyInfo[] props = GetPropertyInfoArray();            for (int i = 0; i < props.Length; i++)            {                xlWorkSheet.Cells[1, i + 1] = props[i].Name; //write the column name            }            for (int i = 0; i < studentList.Count; i++)            {                xlWorkSheet.Cells[i + 2, 1] = studentList[i].Id;                xlWorkSheet.Cells[i + 2, 2] = studentList[i].Name;                xlWorkSheet.Cells[i + 2, 3] = studentList[i].Age;            }            try            {                xlWorkBook.SaveAs(filePath, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);                xlWorkBook.Close(true, misValue, misValue);                xlApp.Quit();            }            catch (Exception ex)            { }        }

 

 。。。。。。。