将实体对象集合数据写入Excel的通用类

好久没更新博客了！发一个实用的Excel工具类，用来将实体类集合写入到Excel,并且可以设定Excel中列规则，包括是否可以编辑，是否允许重复值。下面是完整代码，主函数里内容为使用说明。DataObject是演示用的一个测试实体类，ExcelWriter和PropertyInfo是主要的类。别忘了加上对Excel程序集的引用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Web;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelWriter writer = new ExcelWriter(@"D:\bbb.xls");
            IList<DataObject> objList = new List<DataObject>()
            {
                new DataObject{P1="1",P2="2",P3="3"},
                 new DataObject{P1="a1",P2="a2",P3="a3"},
                  new DataObject{P1="b1",P2="b2",P3="b3"},
                   new DataObject{P1="c1",P2="c2",P3="c3"},
                    new DataObject{P1="d1",P2="d2",P3="d3"}
            };
            IList<PropertyInfo> propertyInfoList = new List<PropertyInfo>()
            {
                new PropertyInfo{HeadText="一",Name="P1",IsLocked=true,IsUnique=true},
                 new PropertyInfo{HeadText="二",Name="P2",IsLocked=false,IsUnique=true},
                  new PropertyInfo{HeadText="三",Name="P3",IsLocked=false,IsUnique=false}
            };
            writer.WriteDataToExcel<DataObject>(objList, propertyInfoList,null);

        }
    }
    public class DataObject
    {
        public string P1 { get; set; }
        public string P2 { get; set; }
        public string P3 { get; set; }
    }
    public  class ExcelWriter
    {
        private Excel.Application application;
        private Excel.Workbook workbook;
        private Excel.Worksheet worksheet;
        private object missing = Type.Missing;
        private string filePath="";
        public ExcelWriter(string filePath)
        {
            application = new Excel.Application();
            workbook = (Excel.Workbook)application.Workbooks.Add(missing);
            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
            this.filePath = filePath;
        }
        /// <summary>
        /// 将平面二维数组批量写入Excel
        /// </summary>
        /// <param name="table"></param>
        private void WriteData(string[,] table)
        {
            Excel.Range tableRange = worksheet.get_Range("A1", "A1");
            tableRange = tableRange.get_Resize(table.GetLength(0), table.GetLength(1));
            tableRange.Value2 = table;
        }
        /// <summary>
        /// 设定列规则，包括不能重复和不能编辑
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="columnIndex"></param>
        private void SetColumnRules(PropertyInfo propertyInfo,int columnIndex)
        {
           Range columnRange= worksheet.Columns.get_Item(columnIndex + 1, missing) as Range;
            //设定锁定规则
           columnRange.Locked = propertyInfo.IsLocked;
            
            //设定唯一性规则
           if (propertyInfo.IsUnique == true)
           {
               Range firstCell = columnRange.Cells[1, 1] as Range;
               string columnAddress = columnRange.get_Address(missing, missing, XlReferenceStyle.xlA1, missing, missing);
               string firstCellAddress = firstCell.get_Address(missing, missing, XlReferenceStyle.xlA1, missing, missing);
               string formula = "=COUNTIF(" + columnAddress + "," + firstCellAddress + ")<=1";
               formula = formula.Replace("$", "");
               columnRange.Validation.
                   Add(XlDVType.xlValidateCustom, XlDVAlertStyle.xlValidAlertStop, missing, formula, missing);
           }
        }
        /// <summary>
        /// 给文件增加一个不能从用户界面查看的隐藏Sheet,并将文件批号写入该隐藏Sheet的第一个单元格
        /// 将来可根据文件批号确定Excel文件数据是否可以回读到数据库
        /// </summary>
        /// <param name="sn"></param>
        private void SetFileSN(string sn)
        {
            Worksheet hiddenSheet = workbook.Sheets.Add(worksheet, missing, missing, missing) as Worksheet;
            hiddenSheet.Name = "HiddenSheet";
            hiddenSheet.get_Range("A1", "A1").Value2 = sn;
            hiddenSheet.Visible = XlSheetVisibility.xlSheetHidden;
        }
        /// <summary>
        /// 保存并关闭文件
        /// </summary>

        private void CloseExcel()
        {
            workbook.SaveAs(filePath, missing, missing,
                                     missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
                                      missing, missing, missing, missing, missing);
            workbook.Close(missing,missing,missing);
            application.Quit();
            KillExcelProcess(application);
        }
        /// <summary>
        /// 用来关闭打开的Excel进程
        /// </summary>
        /// <param name="application"></param>
        private static void KillExcelProcess(Excel.Application application)
        {
            IntPtr t = new IntPtr(application.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out   k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);
        /// <summary>
        /// 将对象列表写到Excel中，并根据制定的属性和属性规则确定要写入的数据列和列规则
        /// </summary>
        /// <typeparam name="T">对象的类型</typeparam>
        /// <param name="objList">对象列表</param>
        /// <param name="propertyInfoList">要写入的属性和列规则</param>
        /// <param name="fileSN">生成文件的序列号，将来从Excel回读数据时来确定文件的合法性</param>
        public void WriteDataToExcel<T>
            (IList<T> objList,IList<PropertyInfo> propertyInfoList,string fileSN)
        {
            int columnCout = propertyInfoList.Count;
            int rowCount = objList.Count + 1;
            string[,] table = new string[rowCount, columnCout];
            //从对象列表收集数据到table
            for (int columnIndex = 0; columnIndex < columnCout; columnIndex++)
            {
                //获取列信息
                PropertyInfo propertyInfo = propertyInfoList[columnIndex];
                //设定列规则
                SetColumnRules(propertyInfo, columnIndex);
                //设定列标题
                table[0, columnIndex] = propertyInfo.HeadText;
                //设定该列内容单元格
                for (int rowIndex = 1; rowIndex < rowCount; rowIndex++)
                {
                    //由于内容是从第二行开始所以下标减一
                        T rowObj = objList[rowIndex - 1];
                        table[rowIndex, columnIndex] =
                            rowObj.GetType().GetProperty(propertyInfo.Name).GetValue(rowObj, null).ToString();
                }
            }
            //写入数据到Excel
            WriteData(table);
            //使列规则生效
            worksheet.Protect("hanjie", true, true,
           missing, missing, missing, missing,
           missing, missing, missing, missing,
           missing, missing, missing, missing, missing);
            //设定文件批号
            if (fileSN != null)
            {
                SetFileSN(fileSN);
            }

            //关闭文件
            CloseExcel();
      
    
        }
        
    }
    /// <summary>
    /// 用来存取要在Excel中显示的列的信息，包括显示的标题和对应对象的属性，
    /// 和列的规则，是否可以编辑，是否可以有重复值
    /// </summary>
    public class PropertyInfo
    {
        public string HeadText {get;set;}
        public string Name { get; set; }
        public bool IsLocked { get; set; }
        public bool IsUnique { get; set; }
    }
}

 

 再补充上ExcelReader将数据从Excel读取到DataTable中，并可以检查ExcelWriter中写入的文件批号 保证文件的合法性

    public class ExcelReader
    {
        private Excel.Application application;
        private Excel.Workbook workbook;
        private Excel.Worksheet worksheet;
        private object missing = Type.Missing;
        private string filePath = "";

        public ExcelReader(string filePath)
        {
            this.filePath = filePath;
        }
        /// <summary>
        /// 验证文件隐藏Sheet中保存的文件批号是否和要求的一致
        /// </summary>
        /// <param name="rightSN"></param>
        private void ValidateFileSN(string rightSN)
        {
            //读取隐藏Sheet中的文件批号
            application = new Excel.Application();
            workbook = application.Workbooks.Open(filePath, missing, missing, missing, missing,
            missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            worksheet = workbook.Sheets.get_Item("HiddenSheet") as Excel.Worksheet;
            Excel.Range firstRange = worksheet.get_Range("A1", "A1");
            string fileSN = firstRange.get_Value(missing).ToString();
            //退出并关闭Excel
            workbook.Close(missing, missing, missing);
            application.Quit();
            ExcelWriter.KillExcelProcess(application);
            //验证批号
            if (fileSN != rightSN)
            {
                throw new Exception("文件批号不正确！");
            }
        }
        /// <summary>
        /// 从Excel中读取数据到DataTable如果rightSN为null则不检查文件批号
        /// </summary>
        /// <param name="rightSN"></param>
        /// <returns></returns>
        public DataTable ReadDataFromExcel(string rightSN)
        {
            //验证文件批号
            if (rightSN != null)
            {
                ValidateFileSN(rightSN);
            }
            //读取Excel数据到DataTable
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = new DataSet();
            strExcel = "select * from [sheet1$]";
            try
            {
                conn.Open();
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, "dtSource");
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }