UserSchema.cs

本文介绍了一种使用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
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值