C# Excel读写(泛型通用方法IList<T> To Excel, Excel To IList<T>)

C# Excel读写的通用(泛型)代码

转载请注明出处

本文实现了两种两种方式：COM和NPOI，其中COM库需要安装excel(即office)，NPOI建议通过NuGet下载

COM相关的动态库：Microsoft.Office.Interop.Excel.dll

NPOI相关的动态库：ICSharpCode.SharpZipLib.dll，NPOI.dll， NPOI.OOXML.dll，NPOI.OpenXml4Net.dll，NPOI.OpenXmlFormats.dll

推荐使用NPOI方式，直接上代码，话不多说！！

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel; //COM
using System.Reflection;
using NPOI.SS.UserModel; // NPOI
using NPOI.HSSF.UserModel;  // NPOI( .xls)
using NPOI.XSSF.UserModel;  // NPOI( .xlsx)
using System.Runtime.InteropServices;
using System.Diagnostics;

namespace Excel
{
    /// <summary>
    /// Excel操作类(读取/写入)
    ///     目前实现了两种方式：COM和NPOI, 推荐使用NPOI; COM方式可能无法正确关闭Excel进程, 如果执行完成后, 查看任务管理器发现Excel进程未关闭，建议强制结束Excel进程
    /// </summary>
    public static class ExcelHelper
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// DataTable转换为IList<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static IList<T> TableToList<T>(this System.Data.DataTable dt) where T :new()
        {
            List<T> result = new List<T>();
            // 获取泛型T的所有属性
            var properties = typeof(T).GetProperties();
            // 转换数据
            foreach (DataRow row in dt.Rows)
            {
                T t = new T();
                foreach (var p in properties)
                {
                    if (dt.Columns.Contains(p.Name))
                    {
                        object value = row[p.Name];
                        // 值不存在
                        if (value == DBNull.Value)
                            continue;
                        // 防止属性为可空类型
                        if (p.PropertyType.Name.Contains("Nullable"))
                            value = Convert.ChangeType(value, Nullable.GetUnderlyingType(p.PropertyType));
                        // 设置属性值
                        p.SetValue(t, value, null);
                    }
                }
                result.Add(t);
            }
            return result;
        }

        /// <summary>
        /// 数据导出到Excel(COM)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static void ListToExcel_COM<T>(IList<T> data, string filePath) where T : new()
        {
            T t = new T();
            var type = t.GetType();
            var properties = type.GetProperties();
            StringBuilder str = new StringBuilder();
            #region 添加列标题
            foreach (var p in properties)
            {
                string columnName = p.Name; // 默认用属性名代替列标题
                // 查找是否忽略了当前属性列
                var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                if (ignoreAtt.Count() != 0)
                    continue;
                // 查找是否为当前属性列单独指定了列标题
                var columnNameAtt = p.GetCustomAttributes(typeof(ColumnNameAttribute), true);
                if (columnNameAtt.Count() != 0)
                {
                    // 使用指定的列标题
                    columnName = ((ColumnNameAttribute)columnNameAtt[0]).ColumnName;
                }
                str.Append(columnName + "\t");
            }
            #endregion
            // 换行
            str.Append(Environment.NewLine);
            #region 添加数据
            foreach (T row in data)
            {
                foreach (var p in properties)
                {
                    // 查找是否忽略了当前属性列
                    var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                    if (ignoreAtt.Count() != 0)
                        continue;
                    // 获取列对应的属性值
                    var value = p.GetValue(row);
                    str.Append(value + "\t");
                }
                str.Append(Environment.NewLine);
            }
            #endregion
            // 现在所有数据在str字符串中了（包括列标题和数据）
            // 删除原有Excel表
            if (File.Exists(filePath))
                File.Delete(filePath);
            // 剪切板
            //this.Invoke(new System.Action(() => Clipboard.SetText(str.ToString())));
            Clipboard.SetText(str.ToString()); // 这里注意有可能会报错，需将Main()方法标记为[STAThread]
            // 开始写入Excel表
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
            if (xApp == null)
                return;
            Workbooks workbooks = xApp.Workbooks;
            Workbook book = workbooks.Add(Missing.Value);
            Worksheet sheet = (Worksheet)book.Sheets[1];
            sheet.Paste();
            // 退出
            book.SaveAs(filePath, XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            book.Close(Missing.Value, Missing.Value, Missing.Value);
            Marshal.ReleaseComObject(book);
            book = null;
            xApp.DisplayAlerts = false;
            xApp.Quit();
            KillExcel(xApp);
            Marshal.ReleaseComObject(xApp);
            xApp = null;
            GC.Collect();
        }

        /// <summary>
        /// 数据导出到Excel(NPOI)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filePath"></param>
        public static void ListToExcel_NPOI<T>(IList<T> data, string filePath) where T : new()
        {
            IWorkbook workBook;
            if (filePath.Contains(".xlsx"))
                workBook = new XSSFWorkbook(); // .xlsx格式
            else
                workBook = new HSSFWorkbook(); // .xls格式
            ISheet sheet = workBook.CreateSheet("Sheet0");

            T t = new T();
            var type = t.GetType();
            var properties = type.GetProperties();
            int columnIndex = 0;
            #region 添加列标题
            // 创建标题列
            var columnNameRow = sheet.CreateRow(0);
            foreach (var p in properties)
            {
                string columnName = p.Name; // 默认用属性名代替列标题
                // 判断是否忽略了当前属性列
                var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                if (ignoreAtt.Count() != 0)
                    continue;
                // 查找是否为当前属性列单独指定了列标题
                var columnNameAtt = p.GetCustomAttributes(typeof(ColumnNameAttribute), true);
                if (columnNameAtt.Count() != 0)
                {
                    // 使用指定的列标题
                    columnName = ((ColumnNameAttribute)columnNameAtt[0]).ColumnName;
                }
                // 写入列标题
                var columnNameCell = columnNameRow.CreateCell(columnIndex++);
                columnNameCell.SetCellValue(columnName.ToString());
            }
            #endregion
            int rowIndex = 1;
            #region 添加数据
            foreach (T row in data)
            {
                var dataRow = sheet.CreateRow(rowIndex++);
                columnIndex = 0;
                foreach (var p in properties)
                {
                    // 判断是否忽略了当前属性列
                    var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                    if (ignoreAtt.Count() != 0)
                        continue;
                    // 获取列对应的属性值
                    var value = p.GetValue(row);
                    var dataCell = dataRow.CreateCell(columnIndex++);
                    if(value != null)
                        dataCell.SetCellValue(value.ToString());
                }
            }
            #endregion
            // 写入Excel
            using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workBook.Write(stream);
            }
        }

        /// <summary>
        /// 从Excel导入数据(COM)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static IList<T> ExcelToList_COM<T>(string filePath, bool isExistsColumnName = true) where T : new()
        {
            if (!File.Exists(filePath))
                return null;
            List<T> result = null;
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook workBook = xApp.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            if(workBook != null)
            {
                Worksheet sheet = (Worksheet)workBook.Worksheets.Item[1];
                if(sheet != null)
                {
                    result = new List<T>();
                    var properties = typeof(T).GetProperties();
                    /*
                     * 特别注意：以COM方式读取时，行索引和列索引是以1开始，而不是以0开始
                     */
                    int rowIndex = 2;
                    // 如果不存在列标题行,则从第1行开始读取数据
                    if (!isExistsColumnName)
                        rowIndex = 1;
                    while (rowIndex <= sheet.UsedRange.Rows.Count)
                    {
                        T t = new T();
                        int columnIndex = 1;
                        foreach (var p in properties)
                        {
                            // 判断是否忽略了当前属性列
                            var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                            if (ignoreAtt.Count() != 0)
                                continue;
                            // 获取Cell值
                            var cellValue = ((Range)sheet.Rows.Cells[rowIndex, columnIndex++]).Text;
                            // 如果值为空，则读取下一个Cell
                            if (string.IsNullOrWhiteSpace(cellValue))
                                continue;
                            // 设置属性值
                            if (p.PropertyType == typeof(string))
                                p.SetValue(t, cellValue, null);
                            else
                            {
                                // 防止属性为可空类型
                                if (p.PropertyType.Name.Contains("Nullable"))
                                {
                                    var value = Convert.ChangeType(cellValue, Nullable.GetUnderlyingType(p.PropertyType));
                                    p.SetValue(t, value, null);
                                }
                                else
                                {
                                    p.SetValue(t, cellValue, null);
                                }
                            }
                        }
                        result.Add(t);
                        rowIndex++;
                    }
                }
                workBook.Close(false, Missing.Value, Missing.Value);
                Marshal.ReleaseComObject(workBook);
                workBook = null;
                xApp.Workbooks.Close();
                xApp.Quit();
                KillExcel(xApp);
                Marshal.ReleaseComObject(xApp);
                xApp = null;
                GC.Collect();
            }
            return result;
        }

        /// <summary>
        /// 从Excel导入数据(NPOI)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static IList<T> ExcelToList_NPOI<T>(string filePath, bool isExistsColumnName = true) where T : new()
        {
            if (!File.Exists(filePath))
                return null;
            List<T> result = null;
            IWorkbook workBook;
            using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                if (filePath.Contains(".xlsx"))
                    workBook = new XSSFWorkbook(stream); // .xlsx格式
                else
                    workBook = new HSSFWorkbook(stream); // .xls格式
            }
            // 获取第一张表
            var sheet = workBook.GetSheetAt(0);
            //var sheet = workBook.GetSheet(sheetName); // 通过表名获取表对象
            if (sheet != null)
            {
                result = new List<T>();
                var properties = typeof(T).GetProperties();
                int rowIndex = 1;
                // 如果不存在列标题行,则从第0行开始读取数据
                if (!isExistsColumnName)
                    rowIndex = 0;
                while (rowIndex <= sheet.LastRowNum)
                {
                    var dataRow = sheet.GetRow(rowIndex++);
                    T t = new T();
                    int columnIndex = 0;
                    foreach (var p in properties)
                    {
                        // 判断是否忽略了当前属性列
                        var ignoreAtt = p.GetCustomAttributes(typeof(IgnoreColumnAttribute), true);
                        if (ignoreAtt.Count() != 0)
                            continue;
                        // 获取Cell值
                        var dataCell = dataRow.GetCell(columnIndex++);
                        // 如果值为空，则读取下一个Cell
                        if (string.IsNullOrWhiteSpace(dataCell.StringCellValue))
                            continue;
                        // 设置属性值
                        if (p.PropertyType == typeof(string))
                            p.SetValue(t, dataCell.StringCellValue, null);
                        else
                        {
                            // 防止属性为可空类型
                            if (p.PropertyType.Name.Contains("Nullable"))
                            {
                                var value = Convert.ChangeType(dataCell.StringCellValue, Nullable.GetUnderlyingType(p.PropertyType));
                                p.SetValue(t, value, null);
                            }
                            else
                            {
                                p.SetValue(t, dataCell.StringCellValue, null);
                            }
                        }
                    }
                    result.Add(t);
                }
            } /*End of if(sheet != null)*/
            return result;
        }

        /// <summary>
        /// 强制关闭Excel进程(因为以COM非托管的方式操作Excel时，Excel进程没有正常关闭)
        /// </summary>
        private static void KillExcel(Microsoft.Office.Interop.Excel.Application xApp)
        {
            IntPtr t = new IntPtr(xApp.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k); // 得到本进程唯一标志k
            Process p = Process.GetProcessById(k);
            p.Kill();
        }
    }
}

下面两个类是用于标记列标题的特性类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Excel
{
    /// <summary>
    /// 用于标记Excel表的列标题
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class ColumnNameAttribute : Attribute
    {
        /// <summary>
        /// 列标题
        /// </summary>
        public string ColumnName { get; set; }

        public ColumnNameAttribute(string columnName)
        {
            ColumnName = columnName;
        }
    }

    /// <summary>
    /// 用于标记忽略掉的列
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class IgnoreColumnAttribute : Attribute
    {
        public IgnoreColumnAttribute()
        { }
    }
}

附个简单的使用样例

public class Student
{
    [ColumnName("姓名")]
    public string Name { get; set; }

    [ColumnName("年龄")]
    public int? Age { get; set; }

    [IgnoreColumn]
    public int IgnoreField { get; set; }

    public string ReserveField { get; set; }
}

class Program
{
    [STAThread]
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Age", typeof(int));
        dt.Columns.Add("ReserveField", typeof(string));

        DataRow dr1 = dt.NewRow();
        dr1["Name"] = "小王";
        dr1["Age"] = 28;
        dr1["ReserveField"] = "保留字段1";
        dt.Rows.Add(dr1);

        DataRow dr2 = dt.NewRow();
        dr2["Name"] = "小李";
        dr2["Age"] = 27;
        dr2["ReserveField"] = "保留字段2";
        dt.Rows.Add(dr2);

        List<Student> lst = new List<Student>()
        {
            new Student(){Name = "小王", IgnoreField = 1, ReserveField = "保留字段1"},
            new Student(){Name = "小李",Age = 27, IgnoreField = 2, ReserveField = "保留字段2"}
        };

        var result = dt.TableToList<Student>(); // 测试完成

        ExcelHelper.ListToExcel_COM(lst, Application.StartupPath + "\\COM.xls"); // 测试完成
        ExcelHelper.ListToExcel_COM(lst, Application.StartupPath + "\\COM.xlsx"); // 测试完成
        var lst1 = ExcelHelper.ExcelToList_COM<Student>(Application.StartupPath + "\\COM.xls");  // 测试完成
        var lst2 = ExcelHelper.ExcelToList_COM<Student>(Application.StartupPath + "\\COM.xlsx");  // 测试完成

        ExcelHelper.ListToExcel_NPOI(lst, Application.StartupPath + "\\NPOI.xls"); // 测试完成
        ExcelHelper.ListToExcel_NPOI(lst, Application.StartupPath + "\\NPOI.xlsx"); // 测试完成
        var lst3 = ExcelHelper.ExcelToList_NPOI<Student>(Application.StartupPath + "\\NPOI.xls"); // 测试完成
        var lst4 = ExcelHelper.ExcelToList_NPOI<Student>(Application.StartupPath + "\\NPOI.xlsx"); // 测试完成
    }
}