将List中的额数据写入Excel
由于是Excel文件,所以要用到Office相关的dll,故请添加相应dll的引用,然后在程序中添加如下命名空间:
using Microsoft.Office.Interop.Excel;
自定义类:
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)
{ }
}
结束。
//下面这段代码是SaveAs()的格式
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local);
原文链接:https://blog.csdn.net/qq_30507287/article/details/53856267