C#实现导出数据到Excel的完整源码指南

using excel = microsoft.office.interop.excel;

excel.application xlapp = new excel.application();
xlapp.visible = true; // 可选项，设置为false可以隐藏excel界面

// 打开一个工作簿
excel.workbook xlworkbook = xlapp.workbooks.open(@"c:\path\to\your\workbook.xlsx");

excel.workbook newworkbook = xlapp.workbooks.add();

datatable datatable = new datatable();
datatable.columns.add("id", typeof(int));
datatable.columns.add("name", typeof(string));
// 添加数据
for (int i = 1; i <= 10; i++)
{
    datatable.rows.add(i, "name" + i);
}
// 删除数据
datarow[] rows = datatable.select("id > 5");
foreach (datarow row in rows)
{
    row.delete();
}
// 提交更改
datatable.acceptchanges();
// 查询数据
datarow foundrow = datatable.rows.find(3);
if (foundrow != null)
{
    console.writeline("found: " + foundrow["name"]);
}
else
{
    console.writeline("not found.");
}

string connectionstring = "data source=服务器地址;initial catalog=数据库名;user id=用户名;password=密码";
sqlconnection connection = new sqlconnection(connectionstring);
string query = "select * from users";

sqldataadapter adapter = new sqldataadapter(query, connection);
datatable datatable = new datatable();
adapter.fill(datatable);
connection.close();

// 现在datatable包含了从数据库查询到的数据

var query = from user in datatable.asenumerable()
            where user.field<string>("name").startswith("n")
            select user;

foreach (datarow row in query)
{
    console.writeline("id: " + row["id"] + ", name: " + row["name"]);
}

dataview view = datatable.defaultview;
view.sort = "name asc"; // 按name升序排序
view.rowfilter = "id > 5"; // 筛选id大于5的行
view.grouping = "name"; // 按name分组

// 排序后的datatable的分组与筛选后的结果可以通过dataview访问
foreach (datarowview drv in view)
{
    console.writeline("id: " + drv["id"] + ", name: " + drv["name"]);
}

var users = datatable1.asenumerable();
var orders = datatable2.asenumerable();

var result = from u in users
             join o in orders on u.field<int>("userid") equals o.field<int>("userid")
             select new { userid = u["userid"], username = u["name"], orderdetails = o["orderdetails"] };

// 输出合并后的结果
foreach (var item in result)
{
    console.writeline($"user id: {item.userid}, name: {item.username}, order details: {item.orderdetails}");
}

// 工作簿对象
workbook workbook;
// 工作簿中包含的多个工作表对象
worksheet[] worksheets;

// 获取工作簿中的工作表数量
int sheetcount = workbook.worksheets.count;
// 通过索引访问特定工作表
worksheet specificsheet = workbook.worksheets[1];

// 插入一个新的工作表
worksheet newsheet = workbook.worksheets.add();
// 删除一个工作表
workbook.worksheets.delete(specificsheet);
// 重命名工作表
specificsheet.name = "renamedsheet";

// 设置单元格数据
specificsheet.cells[1, 1] = "sample data";
// 读取单元格数据
object cellvalue = specificsheet.cells[1, 1].value;

// 创建并设置样式
style style = workbook.styles.add("mystyle");
style.font.size = 12;
style.font.bold = true;
// 应用样式到指定单元格范围
range range = specificsheet.range["a1:a10"];
range.style = style;

// 保护工作表
specificsheet.protect(password: "password");
// 取消保护工作表
specificsheet.unprotect("password");
// 保护工作簿结构
workbook.protect(password: "password");

' vba代码示例
sub mymacro()
    ' vba代码逻辑
end sub

using excel = microsoft.office.interop.excel;

public void writedatatabletoexcel(datatable table, string filepath)
{
    excel.application excelapp = new excel.application();
    if (excelapp == null)
    {
        throw new exception("excel application could not be launched");
    }

    excel.workbook workbook = excelapp.workbooks.add(type.missing);
    excel._worksheet worksheet = workbook.sheets[1];

    // 假设table为已经填充好的datatable
    for (int i = 0; i < table.rows.count; i++)
    {
        for (int j = 0; j < table.columns.count; j++)
        {
            worksheet.cells[i + 1, j + 1] = table.rows[i][j]; // 将datatable的值写入excel单元格
        }
    }

    // 设置标题行
    for (int j = 0; j < table.columns.count; j++)
    {
        worksheet.cells[1, j + 1] = table.columns[j].caption;
    }

    // 保存并关闭工作簿
    workbook.saveas(filepath, type.missing, type.missing, type.missing, type.missing, type.missing, excel.xlsaveasaccessmode.xlexclusive, type.missing, type.missing, type.missing, type.missing, type.missing);
    workbook.close(false, type.missing, type.missing);
    excelapp.quit();

    // 释放com对象
    releaseobject(worksheet);
    releaseobject(workbook);
    releaseobject(excelapp);
}

// 在写入之前，先进行数据类型转换和格式化
for (int i = 0; i < table.rows.count; i++)
{
    for (int j = 0; j < table.columns.count; j++)
    {
        var value = table.rows[i][j];
        if (value != dbnull.value)
        {
            // 数据类型检测并转换
            if (value is datetime)
            {
                worksheet.cells[i + 1, j + 1].value2 = ((datetime)value).toshortdatestring();
            }
            else
            {
                worksheet.cells[i + 1, j + 1].value2 = value.tostring();
            }
        }
        else
        {
            worksheet.cells[i + 1, j + 1].value2 = null;
        }
    }
}

// 假设我们要将某个列格式化为货币格式
for (int i = 0; i < table.rows.count; i++)
{
    // 获取货币格式化字符串
    string currencyformat = string.format("c", system.globalization.cultureinfo.currentculture);

    // 假设我们要格式化的列是第三列（索引为2）
    double value = convert.todouble(table.rows[i][2]);
    worksheet.cells[i + 1, 3].numberformat = currencyformat; // 设置列的格式
    worksheet.cells[i + 1, 3].value2 = value; // 设置列的值
}

// 示例代码展示如何将datetime转换为excel中的序列号
datetime datevalue = datetime.parse("2023-04-01");
double excelserialdate = datevalue.tooadate();
worksheet.cells[i + 1, j + 1].value2 = excelserialdate;

// 通过二维数组实现批量写入
object[,] datatowrite = new object[table.rows.count, table.columns.count];
for (int i = 0; i < table.rows.count; i++)
{
    for (int j = 0; j < table.columns.count; j++)
    {
        datatowrite[i, j] = table.rows[i][j];
    }
}
rangevalue.value2 = datatowrite; // rangevalue是excel范围对象

public async task writedatatabletoexcelasync(datatable table, string filepath)
{
    excel.application excelapp = new excel.application();
    if (excelapp == null)
    {
        throw new exception("excel application could not be launched");
    }

    excel.workbook workbook = excelapp.workbooks.add(type.missing);
    excel._worksheet worksheet = workbook.sheets[1];

    // 异步写入操作
    await task.run(() =>
    {
        for (int i = 0; i < table.rows.count; i++)
        {
            for (int j = 0; j < table.columns.count; j++)
            {
                worksheet.cells[i + 1, j + 1] = table.rows[i][j];
            }
        }

        // 设置标题行
        for (int j = 0; j < table.columns.count; j++)
        {
            worksheet.cells[1, j + 1] = table.columns[j].caption;
        }
    });

    // 异步保存和关闭工作簿
    await task.run(() =>
    {
        workbook.saveas(filepath, type.missing, type.missing, type.missing, type.missing, type.missing, excel.xlsaveasaccessmode.xlexclusive, type.missing, type.missing, type.missing, type.missing, type.missing);
        workbook.close(false, type.missing, type.missing);
        excelapp.quit();
    });

    // 释放com对象
    releaseobject(worksheet);
    releaseobject(workbook);
    releaseobject(excelapp);
}

// c# 代码示例：设置excel单元格字体大小
var cell = worksheet.get_range("a1");
cell.font.size = 12; // 设置字体大小为12

// c# 代码示例：设置excel单元格字体样式为粗体和斜体
cell.font.bold = true;
cell.font.italic = true;

// c# 代码示例：设置excel单元格字体颜色
cell.font.color = system.drawing.color.red; // 设置字体颜色为红色

// c# 代码示例：创建颜色渐变效果
var gradientfill = cell.interiorمسأل.interior as excel.gradientfill;
gradientfill.type = excel.xlgradienttype.xlgradienthorizontal; // 水平渐变
gradientfill.gradientstops.insert("color1", excel.xlrgbcolor.rgbred);
gradientfill.gradientstops.insert("color2", excel.xlrgbcolor.rgbblue);

// c# 代码示例：创建图案填充效果
cell.interior新模式 = xlpattern.xlpatterndarkupwarddiagonal;
cell.interior新模式.color = system.drawing.color.gray;
cell.interior新模式.tintandshade = 0;

// c# 代码示例：设置excel单元格边框
var borders = cell.borders;
borders[xlbordersindex.xledgeleft].linestyle = xllinestyle.xlcontinuous;
borders[xlbordersindex.xledgeleft].color = system.drawing.color.black;
borders[xlbordersindex.xledgeleft].weight = xlborderweight.xlthin;
// 重复设置其他边框

// c# 代码示例：设置excel单元格图案填充
cell.interior新模式 = xlpattern.xlpatternlightdownwarddiagonal;
cell.interior新模式.color = system.drawing.color.yellow;
cell.interior新模式.tintandshade = 0;

// c# 代码示例：设置excel单元格对齐
cell.verticalalignment = xlvalign.xlvaligncenter;
cell.horizontalalignment = xlhalign.xlhaligncenter;

// 示例代码：将excel工作簿保存为pdf
workbook.exportasfixedformat(xlfixedformattype.xltypepdf, "output.pdf");

public class excelinterop : idisposable
{
    private workbook workbook;
    private application excelapp;

    public void dispose()
    {
        if (workbook != null)
            workbook.close(false);
        if (excelapp != null)
            excelapp.quit();

        // 注意：这里不应直接调用gc.collect()，仅作为示例
        system.runtime.interopservices.marshal.releasecomobject(workbook);
        system.runtime.interopservices.marshal.releasecomobject(excelapp);
        workbook = null;
        excelapp = null;
    }

    // 其他方法...
}

try
{
    // 尝试保存excel文件
}
catch (comexception ex)
{
    // 异常处理逻辑，例如：提示用户检查文件权限、磁盘空间等
}

// 示例代码：实现idisposable接口的清理方法
public void dispose()
{
    dispose(true);
    gc.suppressfinalize(this);
}

protected virtual void dispose(bool disposing)
{
    if (disposing)
    {
        // 释放托管资源
    }
    // 释放非托管资源
}