UserSchema

using System; using System.Data; using System.Linq; 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[] cn = { "UserName", "UserEmail", "Content" }; string[] tn = { "用户名", "电子邮件", "内容" }; foreach (int index in Enumerable.Range(0, 3)) { dataTable.Columns.Add(cn[index], typeof(string)).Caption = tn[index]; } dataTable.Columns.Add("SendTime", typeof(DateTime)).Caption = "时间"; dataTable.Constraints.Add("Name", dataTable.Columns["UserName"], true); } public static DataTable UserTable { get { return dataTable; } set { dataTable = value; } } #endregion #region PrintToExcel public static void PrintToExcel(this DataTable dataTable, string caption) { Excel.Application newExcel = new Excel.Application(); try { Excel.Workbook newBook = newExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet newSheet = newBook.ActiveSheet as Excel.Worksheet; newExcel.Visible = true; newBook.Password = "jinzhexian"; newSheet.Name = caption; int column = dataTable.Columns.Count; Excel.Range newRange = newSheet.get_Range("B2", Type.Missing).get_Resize(2, column); newRange.MergeCells = true; // 合并单元格。 newRange.Font.Size = 16; newRange.Value2 = caption; newRange = newRange.get_Offset(1, 0).get_Resize(dataTable.Rows.Count + 1, column); var query = dataTable.Columns.Cast().Select(sm => sm.Caption as object); query = query.Concat(dataTable.AsEnumerable().SelectMany(sm => sm.ItemArray)); column = -1; foreach (Excel.Range item in newRange.Cells) { item.Value2 = string.Format("'{0}", query.ElementAt(++column)); } newRange.EntireColumn.AutoFit(); // 自动调整列宽。 newRange.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 水平居中。 newRange.Borders.LineStyle = Excel.XlLineStyle.xlDouble; // 设置外边框样式。 (newRange[2, 0] as Excel.Range).Select(); newExcel.SendKeys("%WFF", true); // 冻结拆分窗格 Microsoft Office 2003(%WF), 2007(%WFF)。 } catch { newExcel.Quit(); } finally { newExcel = null; GC.Collect(); } } #endregion }