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)
            { }

        }

 

 。。。。。。。