信息导到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" }));
}
大家感觉有用的自己改改.有更好建议请提出来.谢谢.
