信息导到Execl上.

项目里有个小的模块.就是把数据库里的信息输出到Execl上.开始因为这部分只会有一二个文件.没有怎么封装.后来文件多了.

看到很多处代码都有打开Execl的这种代码.封装一下(现这部分代码全是测试用的.).

首先是封装打开写入信息到Execl文件的代码.

    public class DataFile
    {
        public string SavePath { get; set; }
        public string SrcPathName { get; set; }
        public string SaveName { get; set; }

        public string OnAction(params Action<Microsoft.Office.Interop.Excel.Worksheet>[] actions)
        {
            DirectoryInfo path = new DirectoryInfo(SavePath);
            if (path.GetFiles().Where(p => p.Name == SaveName).Count() > 0)
                SaveName += DateTime.Now.ToString("yyyymmddHHMMss");
            string SavePathName = "";
            if (SavePath.EndsWith(@"\"))
                SavePathName = SavePath + SaveName + ".xls";
            else
                SavePathName = SavePath + @"\" + SaveName + ".xls";
            Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
            myExcel.Workbooks.Open(SrcPathName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);           
            Microsoft.Office.Interop.Excel.Workbook wbook = myExcel.Workbooks[1];
            Microsoft.Office.Interop.Excel.Worksheet wsheet = null;
            for (int i = 0; i < actions.Length; i++)
            {
                wsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(i+1);                
                actions[i](wsheet);
            }
            wbook.SaveAs(SavePathName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            myExcel.Quit();
            Kill(myExcel);
            myExcel = null;
            GC.Collect();
            return SavePathName;

        }
        public void Kill(Microsoft.Office.Interop.Excel.Application excel)
        {
            if (excel == null)
                return;
            IntPtr t = new IntPtr(excel.Hwnd);   
            int k = 0;
            GetWindowThreadProcessId(t, out k);    
            Process p = Process.GetProcessById(k); 
            p.Kill();      //关闭进程k
        }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
         
    }

这部分代码是帮助把数据集合导入到Execl里面的.在这里说一句.为什么VS2008里的C#还不支持默认参数!!(不知是不是我OUT了.)

    public static class PropertyHelper
    {
        public static List<T> DataTableToList<T>(DataTable table) where T : new()
        {
            PropertyInfo[] ps = typeof(T).GetProperties();
            List<T> ts = new List<T>();
            foreach (DataRow row in table.Rows)
            {
                T t = new T();// T t = Activator.CreateInstance<T>();
                foreach (PropertyInfo p in ps)
                {
                    p.SetValue(t, row[p.Name], null);
                }
                ts.Add(t);
            }
            return ts;
        }
        public static DataTable ListToDataTable<T>(this IEnumerable<T> data)
        {
            PropertyInfo[] ps = typeof(T).GetProperties();
            DataTable table = new DataTable();
            foreach (PropertyInfo p in ps)
            {
                table.Columns.Add(p.Name, p.PropertyType);
            }
            foreach (T t in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo p in ps)
                {
                    row[p.Name] = p.GetValue(t, null);
                }
                table.Rows.Add(row);
            }
            return table;
        }

        public static void CompressFile(string sourceFile, string destinationFile, string desName)
        {
            if (File.Exists(sourceFile) == false)
                throw new FileNotFoundException();
            FileStream reader = null;
            FileStream writer = null;
            MemoryStream ms = null;
            try
            {
                //文件流
                reader = File.Open(sourceFile, FileMode.Open);
                writer = File.Create(destinationFile);
                //压缩相关的流 
                ms = new MemoryStream();
                GZipStream zipStream = new GZipStream(ms, CompressionMode.Compress, true);
                //往压缩流中写数据 
                byte[] sourceBuffer = new byte[reader.Length];
                reader.Read(sourceBuffer, 0, sourceBuffer.Length);
                zipStream.Write(sourceBuffer, 0, sourceBuffer.Length);
                //一定要在内存流读取之前关闭压缩流 
                zipStream.Close();
                zipStream.Dispose();

                //从内存流中读数据 
                ms.Position = 0; //注意，不要遗漏此句 
                byte[] header = new byte[10];
                ms.Read(header, 0, 10);
                header[3] = 8;        //表示包含文件名信息
                byte[] fielContent = new byte[ms.Length - 10];
                ms.Read(fielContent, 0, fielContent.Length);

                byte[] filename = System.Text.Encoding.Default.GetBytes(desName);
                writer.Write(header, 0, header.Length);
                writer.Write(filename, 0, filename.Length);
                writer.WriteByte(0);
                writer.Write(fielContent, 0, fielContent.Length);
            }
            catch (ApplicationException ex)
            {
                new Exception("压缩文件出错!" + ex.Message);
            }
            finally
            {
                // Make sure we allways close all streams
                ms.Close();
                ms.Dispose();

                //关闭并释放文件流 
                writer.Close();
                writer.Dispose();
                reader.Close();
                reader.Dispose();
            }
        }
        #region "IEnumerable导出EXECL"
        public static void EnumToExel<T>(this IEnumerable<T> data, Microsoft.Office.Interop.Excel.Worksheet wsheet)
        {
            data.EnumToExel<T>(wsheet, 1, 1);
        }
        public static void EnumToExel<T>(this IEnumerable<T> data, Microsoft.Office.Interop.Excel.Worksheet wsheet, int top, int left)
        {
            data.EnumToExel<T>(wsheet, top, left, false);
        }
        public static void EnumToExel<T>(this IEnumerable<T> data, Microsoft.Office.Interop.Excel.Worksheet wsheet, int top, int left, bool IsHaveCoulumn)
        {
            data.EnumToExel<T>(wsheet, top, left, IsHaveCoulumn, null);
        }
        public static void EnumToExel<T>(this IEnumerable<T> data, Microsoft.Office.Interop.Excel.Worksheet wsheet, int top, int left, bool IsHaveCoulumn, string[] removeProperty)
        {
            List<PropertyInfo> ps = typeof(T).GetProperties().ToList();
            if (removeProperty != null)
            {
                foreach (string pro in removeProperty)
                {
                    ps.Remove(ps.FirstOrDefault(p => p.Name.ToUpper() == pro.ToUpper()));
                }
            }
            int nTop = top;
            int nLeft = left;
            if (IsHaveCoulumn)
            {
                foreach (PropertyInfo info in ps)
                {
                    wsheet.Cells[nTop, nLeft] = info.Name;
                    nLeft++;
                }
                nTop++;
            }
            foreach (T t in data)
            {
                nLeft = left;
                foreach (PropertyInfo info in ps)
                {
                    wsheet.Cells[nTop, nLeft] = info.GetValue(t, null);
                    nLeft++;
                }
                nTop++;
            }
        }
        #endregion ""
        #region "DataTable导出EXECL"
        public static void EnumToExel(Microsoft.Office.Interop.Excel.Worksheet wsheet, DataTable data)
        {
            EnumToExel(wsheet, data, 1, 1, false, null);
        }
        public static void EnumToExel(Microsoft.Office.Interop.Excel.Worksheet wsheet, DataTable data, int top, int left)
        {
            EnumToExel(wsheet, data, top, left, false, null);
        }
        public static void EnumToExel(Microsoft.Office.Interop.Excel.Worksheet wsheet, DataTable data, int top, int left, bool IsHaveCoulumn)
        {
            EnumToExel(wsheet, data, top, left, IsHaveCoulumn, null);
        }
        public static void EnumToExel(Microsoft.Office.Interop.Excel.Worksheet wsheet, DataTable data, int top, int left, bool IsHaveCoulumn, string[] removeCoulumn)
        {
            if (removeCoulumn != null)
            {
                foreach (string pro in removeCoulumn)
                {
                    int nCount = data.Columns.Count;
                    for (int i = 0; i < nCount; i++)
                    {
                        string coulumn = data.Columns[i].ColumnName.ToUpper();
                        if (coulumn == pro.ToUpper())
                            data.Columns.Remove(data.Columns[i]);
                    }
                }
            }
            int nTop = top;
            int nLeft = left;
            if (IsHaveCoulumn)
            {
                foreach (DataColumn info in data.Columns)
                {
                    wsheet.Cells[nTop, nLeft] = info.ColumnName;
                    nLeft++;
                }
                nTop++;
            }
            foreach (DataRow t in data.Rows)
            {
                nLeft = left;
                foreach (DataColumn info in data.Columns)
                {
                    wsheet.Cells[nTop, nLeft] = t[info];
                    nLeft++;
                }
                nTop++;
            }
        }
        #endregion

        public static void Write<T>(T s)
        {
            System.Reflection.PropertyInfo[] ms = typeof(T).GetProperties();
            foreach (PropertyInfo minfo in ms)
            {
                if (minfo.GetValue(s, null) != null)
                {
                    string value = minfo.GetValue(s, null).ToString();
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    Console.Write(minfo.Name);
                    Console.ForegroundColor = ConsoleColor.White;
                    Console.Write(":");
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.Write(value);
                    Console.Write(" ");
                }
            }
            Console.WriteLine();
        }
    }

下面测试用法.是否正确导入数据.

        public void TestFun()
        {
            DataFile ListBomPart = new DataFile();
            ListBomPart.SaveName = "TestFile";
            ListBomPart.SavePath = Directory.GetCurrentDirectory();
            ListBomPart.SrcPathName = ConfigurationSettings.AppSettings["TestFileName"];
            string resultfilename = ListBomPart.OnAction(
                p => Db.TableOne.ToList().EnumToExel(p, 2, 1, false, new string[] { "ID" })
               , t => Db.TableTwo.ToList().EnumToExel(t, 2, 1, false, new string[] { "ID", "Updatedate", "Updatename" }));

        }

大家感觉有用的自己改改.有更好建议请提出来.谢谢.