using System;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
// 添加引用 -> .NET -> Microsoft.Office.Interop.Excel(2003->11.0, 2007->12.0)
public static class UserSchema
{
#region DataTable
private static DataTable dataTable;
static UserSchema()
{
dataTable = new DataTable("User");
dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
string[] columns = { "UserName", "UserEmail", "Content" };
foreach (string name in columns)
{
dataTable.Columns.Add(name, typeof(String));
}
dataTable.Columns.Add("SendTime", typeof(DateTime));
dataTable.Constraints.Add("Name", dataTable.Columns["UserName"], true);
}
public static DataTable UserTable
{
get { return dataTable; }
}
#endregion
#region PrintToExcel
public static void PrintToExcel(this DataTable table, string caption, params string[] columns)
{
Excel.Application xls = new Excel.Application();
try
{
Excel.Workbook book = xls.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet sheet = book.ActiveSheet as Excel.Worksheet;
xls.Visible = true;
book.Password = "jinzhexian";
sheet.Name = caption;
sheet.get_Range("E4", Type.Missing).EntireColumn.NumberFormat = "yyyy年M月d日";
Excel.Range range = sheet.get_Range("B1", Type.Missing).get_Resize(2, table.Columns.Count);
range.MergeCells = true; // 合并单元格。
range.Font.Size = 16;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value2 = caption;
range = range.get_Offset(1, 0).get_Resize(1, table.Columns.Count);
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value2 = columns;
foreach (DataRow row in table.Rows)
{
range = range.get_Offset(1, 0);
range.Value2 = row.ItemArray;
}
range = range.get_Offset(2, 0);
range.MergeCells = true; // 合并单元格。
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value2 = string.Format("打印日期: {0:yyyy'年'M'月'd'日'}", DateTime.Today);
range.EntireColumn.AutoFit(); // 自动调整列宽。
sheet.get_Range("A4", Type.Missing).Select();
xls.SendKeys("%WFF", true); // 冻结拆分窗格 Microsoft Office 2003(%WF), 2007(%WFF)。
}
catch
{
xls.Quit();
}
finally
{
xls = null;
GC.Collect();
}
}
#endregion
}