Excel COM 读写代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 Excel COM 读写
作者:today / 发布于2013/6/17/ 547
过滤Excel中的数据

	sing System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Data;
	using System.Data.OleDb;
	using System.IO;
	using Microsoft.Office.Interop.Excel;
	using System.Diagnostics;
	using System.Collections;
	using System.Reflection;
	namespace Console
	{
	 class Program
	 {

	 #region Vars
	 static string[] Files;
	 static List<_Worksheet> objSheetList;
	 static Microsoft.Office.Interop.Excel.Application objApp;
	 static object miss = System.Reflection.Missing.Value;
	 #endregion

	 static void Main(string[] args)
	 {
	 ReadFiles();

	 Filter();

	 //foreach (_Workbook sheet in objSheetList)
	 //{ ObjectDumper.Write(sheet); }

	 SaveFiles();
	 }

	 private static void SaveFiles()
	 {
	 string path = Environment.CurrentDirectory + "/Results/";
	 if (!Directory.Exists(path)) Directory.CreateDirectory(path);
	 foreach (_Worksheet sheet in objSheetList )
	 sheet.SaveAs(path + sheet.Name + ".xls", miss, miss, miss, miss, miss, miss, miss, miss,miss);
	 ReleaseObject(objApp);
	
	 }

	 private static void Filter()
	 {
	 foreach (_Worksheet objSheet in objSheetList)
	 {
	 List<Range> deleteRows = new List<Range>();
	 foreach (Range row in objSheet.Rows)
	 {
	 if (row.Row > 200) break;

	 if (row.Row == 1) continue;

	 DateTime birthDay = DateTime.MinValue;
	 string dateStr=(objSheet.Cells[row.Row, 7] as Range).Text as string;
	 if (DateTime.TryParse(dateStr, out birthDay) & birthDay.Year > 1960 & birthDay.Year < 1990)
	 continue;
	 deleteRows.Add(row);
	
	 }
	 foreach (Range row in deleteRows)
	 row.Delete(XlDeleteShiftDirection .xlShiftUp);
	 }
	 }

	 /// <summary>
	 /// 释放对象
	 /// </summary>
	 /// <param name="obj"></param>
	 static private void ReleaseObject(object obj)
	 {
	 try
	 {
	 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
	 }
	 catch { }
	 finally { obj = null; }
	 }

	 /// <summary>
	 /// Read excel and init objSheet
	 /// </summary>
	 private static void ReadFiles()
	 {
	 string path = Environment.CurrentDirectory + "/恒华村/";
	 Files = Directory.GetFiles(path);
	 objSheetList = new List<_Worksheet>();
	 objApp = new Microsoft.Office.Interop.Excel.Application();
	 Microsoft.Office.Interop.Excel.Workbooks workbooks = objApp.Workbooks;
	 foreach (string file in Files)
	 {
	 workbooks.Open(file, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);

	 Microsoft.Office.Interop.Excel.Sheets objSheets = objApp.Worksheets;

	 Microsoft.Office.Interop.Excel._Worksheet objSheet;

	 objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);

	
	 objSheetList.Add(objSheet);
	 }
	 }
	 }
	 /// <summary>
	 /// Adapted from the VS2008 Samples ObjectDumper.
	 /// </summary>
	 internal sealed class ObjectDumper
	 {
	 private TextWriter _writer;
	 private int _position;
	 private int _level;
	 private int _depth;

	 public static void Write(object element, int depth, TextWriter log)
	 {
	 ObjectDumper dumper = new ObjectDumper(depth);
	 dumper._writer = log;
	 dumper.WriteObject(null, element);
	 }

	 public static string Write(object element)
	 {
	 using (StringWriter sw = new StringWriter())
	 {
	 ObjectDumper.Write(element, 3, sw);
	 return sw.ToString();
	 }
	 }

	 private ObjectDumper(int depth)
	 {
	 this._depth = depth;
	 }

	 private void Write(string s)
	 {
	 if (s != null)
	 {
	 _writer.Write(s);
	 _position += s.Length;
	 }
	 }

	 private void WriteIndent()
	 {
	 for (int i = 0; i < _level; i++) _writer.Write(" ");
	 }

	 private void WriteLine()
	 {
	 _writer.WriteLine();
	 _position = 0;
	 }

	 private void WriteTab()
	 {
	 Write(" ");
	 while (_position % 8 != 0) Write(" ");
	 }

	 private void WriteObject(string prefix, object element)
	 {
	 if (element == null || element is ValueType || element is string)
	 {
	 WriteIndent();
	 Write(prefix);
	 WriteValue(element);
	 WriteLine();
	 }
	 else
	 {
	 IEnumerable enumerableElement = element as IEnumerable;
	 if (enumerableElement != null)
	 {
	 foreach (object item in enumerableElement)
	 {
	 if (item is IEnumerable && !(item is string))
	 {
	 WriteIndent();
	 Write(prefix);
	 Write("...");
	 WriteLine();
	 if (_level < _depth)
	 {
	 _level++;
	 WriteObject(prefix, item);
	 _level--;
	 }
	 }
	 else
	 {
	 WriteObject(prefix, item);
	 }
	 }
	 }
	 else
	 {
	 MemberInfo[] members = element.GetType().GetMembers(BindingFlags.Public | BindingFlags.Instance);
	 WriteIndent();
	 Write(prefix);
	 bool propWritten = false;
	 foreach (MemberInfo m in members)
	 {
	 FieldInfo f = m as FieldInfo;
	 PropertyInfo p = m as PropertyInfo;
	 if (f != null || p != null)
	 {
	 if (propWritten)
	 {
	 WriteTab();
	 }
	 else
	 {
	 propWritten = true;
	 }
	 Write(m.Name);
	 Write("=");
	 Type t = f != null ? f.FieldType : p.PropertyType;
	 if (t.IsValueType || t == typeof(string))
	 {
	 WriteValue(f != null ? f.GetValue(element) : p.GetValue(element, null));
	 }
	 else
	 {
	 if (typeof(IEnumerable).IsAssignableFrom(t))
	 {
	 Write("...");
	 }
	 else
	 {
	 Write("{ }");
	 }
	 }
	 }
	 }
	 if (propWritten) WriteLine();
	 if (_level < _depth)
	 {
	 foreach (MemberInfo m in members)
	 {
	 FieldInfo f = m as FieldInfo;
	 PropertyInfo p = m as PropertyInfo;
	 if (f != null || p != null)
	 {
	 Type t = f != null ? f.FieldType : p.PropertyType;
	 if (!(t.IsValueType || t == typeof(string)))
	 {
	 object value = f != null ? f.GetValue(element) : p.GetValue(element, null);
	 if (value != null)
	 {
	 _level++;
	 WriteObject(m.Name + ": ", value);
	 _level--;
	 }
	 }
	 }
	 }
	 }
	 }
	 }
	 }

	 private void WriteValue(object o)
	 {
	 if (o == null)
	 {
	 Write("null");
	 }
	 else if (o is DateTime)
	 {
	 Write(((DateTime)o).ToShortDateString());
	 }
	 else if (o is ValueType || o is string)
	 {
	 Write(o.ToString());
	 }
	 else if (o is IEnumerable)
	 {
	 Write("...");
	 }
	 else
	 {
	 Write("{ }");
	 }
	 }
	 }

	}
试试其它关键字
　Excel　　COM　
同语言下
可能有用的
today贡献的其它代码(7)