泛型集合导出Excel,加载Xml重写Excel表头

XML文件示例：

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <Table>
    <TableName>Student</TableName>
    <ColumnName>Id</ColumnName>
    <ColName_CH>编号</ColName_CH>
    <Sort>30</Sort>
    <IsShow>true</IsShow>
  </Table>
  <Table>
    <TableName>Student</TableName>
    <ColumnName>Name</ColumnName>
    <ColName_CH>姓名</ColName_CH>
    <Sort>30</Sort>
    <IsShow>true</IsShow>
  </Table>
  <Table>
    <TableName>Student</TableName>
    <ColumnName>Sex</ColumnName>
    <ColName_CH>性别</ColName_CH>
    <Sort>30</Sort>
    <IsShow>true</IsShow>
  </Table>
</NewDataSet>

C#代码：

#region List<Object> 导出 EXCEL

        List<Student> studentList = new List<Student>();
        private void buttonBase1_Click(object sender, EventArgs e)
        {
            SetValue();//初始化集合数据
            List<Object> obj = new List<object>();
            foreach (Student item in studentList)
            {
                obj.Add(item);
            }
            this.Output(obj, typeof(Student));
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="listSource"></param>
        public void Output(List<Object> listSource,Type type)
        {
            string path = "d:\\temp.xls"; //文件导出保存地址
            Export2CSV<Object>(listSource, path, type.Name);
        }

        //XML返回列名
        public static string GetNameByXML(string columnName, string tableName)
        {
            try
            {
                string path = AppDomain.CurrentDomain.BaseDirectory + "ColumnConfig.xml";  // ..\bin\Debug\
                XDocument doc = XDocument.Load(AppDomain.CurrentDomain.BaseDirectory + "ColumnConfig.xml");
                IEnumerable<XElement> xes = doc.Element("NewDataSet").Elements("Table").Where(x => x.Element("TableName").Value.ToString().Trim() == tableName);
                XElement newDataSet = doc.Element("NewDataSet");
                for (int i = 0; i < xes.Count(); i++)
                {
                    string ColumnName = xes.ElementAt(i).Element("ColumnName").Value.ToString();

                    if (columnName == ColumnName)
                    {
                        return xes.ElementAt(i).Element("ColName_CH").Value.ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return columnName;
        }

        /// <summary>
        /// List<T>2Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filename"></param>
        /// <param name="tableName"></param>
        /// <param name="exportHeader"></param>
        public static void Export2CSV<T>(List<T> data, string filename,string tableName, bool exportHeader = true)
        {
            if (File.Exists(filename))
                File.Delete(filename);

            var type = typeof(T);
            PropertyInfo[] propertys = data[0].GetType().GetProperties();
            var buffer = new StringBuilder();

            IWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet("shee1");
            IRow header = sheet.CreateRow(0);

            if (exportHeader)
            {
                for (var i = 0; i < propertys.Length; i++)
                {
                    string name = GetNameByXML(propertys[i].Name, tableName);      

                    ICell cell = header.CreateCell(i);
                    string val = name;
                    cell.SetCellValue(val);
                }
            }
            int rowIndex = 1;
            for (var i = 0; i < data.Count; i++)
            {
                IRow excelRow = sheet.CreateRow(rowIndex++);
                for (var j = 0; j < propertys.Length; j++)
                {
                    excelRow.CreateCell(j).SetCellValue(propertys[j].GetValue(data[i], null).ToString());      
                }
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);

            byte[] data1 = ms.ToArray();

            if (!File.Exists(filename))
            {
                FileStream fs = new FileStream(filename, FileMode.CreateNew);
                fs.Write(data1, 0, data1.Length);
                fs.Close();
            }
        }

        public void SetValue()
        {
            Student s1 = new Student();
            s1.Id = 1;
            s1.Name = "涨势";
            s1.Sex = "男";
            studentList.Add(s1);
            Student s2 = new Student();
            s2.Id = 2;
            s2.Name = "历史";
            s2.Sex = "女";
            studentList.Add(s2);
            Student s3 = new Student();
            s3.Id = 3;
            s3.Name = "王五";
            s3.Sex = "男";
            studentList.Add(s3);
        }

        public class Student
        {
            private int id;

            public int Id
            {
                get { return id; }
                set { id = value; }
            }
            private string name;

            public string Name
            {
                get { return name; }
                set { name = value; }
            }
            private string sex;

            public string Sex
            {
                get { return sex; }
                set { sex = value; }
            }
        }
        #endregion

 

转载于:https://www.cnblogs.com/liudiwei/p/6041790.html