UserSchema.cs

最新推荐文章于 2024-08-26 17:25:33 发布

美好晨光

最新推荐文章于 2024-08-26 17:25:33 发布

阅读量336

点赞数

CC 4.0 BY-SA版权

分类专栏： ASP.NET 文章标签： string microsoft excel office table class

本文链接：https://blog.youkuaiyun.com/kimcheolhyun/article/details/5380859

ASP.NET 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一种使用C#将DataTable数据导出到Excel的方法，包括如何设置单元格格式、合并单元格以及自动调整列宽等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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
}