欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C# WinForm导出Excel方法介绍

程序员文章站 2024-02-22 14:50:22
.net开发人员首选的方法,通过com组件调用office软件本身来实现文件的创建和读写,但是数据量较大的时候异常缓慢;如下代码所示已经做了优化,将一个二维对象数组赋值到一...

.net开发人员首选的方法,通过com组件调用office软件本身来实现文件的创建和读写,但是数据量较大的时候异常缓慢;如下代码所示已经做了优化,将一个二维对象数组赋值到一个单元格区域中(下面的代码中只能用于导出列数不多于26列的数据导出):

office pia

复制代码 代码如下:

public static void exporttoexcel(dataset dataset, string outputpath)
{
    excel.applicationclass excel = new excel.applicationclass();
    excel.workbook workbook = excel.workbooks.add(type.missing);
    int sheetindex = 0;
    foreach (system.data.datatable dt in dataset.tables)
    {
        object[,] data = new object[dt.rows.count + 1, dt.columns.count];
        for (int j = 0; j < dt.columns.count; j++)
        {
            data[0, j] = dt.columns[j].columnname;
        }
        for (int j = 0; j < dt.columns.count; j++)
        {
            for (int i = 0; i < dt.rows.count; i++)
            {
                data[i + 1, j] = dt.rows[i][j];
            }
        }
        string finalcolletter = string.empty;

        string colcharset = "abcdefghijklmnopqrstuvwxyz";
        int colcharsetlen = colcharset.length;
        if (dt.columns.count > colcharsetlen)
        {
            finalcolletter = colcharset.substring(
                (dt.columns.count - 1) / colcharsetlen - 1, 1);
        }
        finalcolletter += colcharset.substring(
                (dt.columns.count - 1) % colcharsetlen, 1);

        excel.worksheet sheet = (excel.worksheet)workbook.sheets.add(
            workbook.sheets.get_item(++sheetindex),
            type.missing, 1, excel.xlsheettype.xlworksheet);
        sheet.name = dt.tablename;
        string range = string.format("a1:{0}{1}", finalcolletter, dt.rows.count + 1);
        sheet.get_range(range, type.missing).value2 = data;
        ((excel.range)sheet.rows[1, type.missing]).font.bold = true;
    }
    workbook.saveas(outputpath, excel.xlfileformat.xlworkbooknormal, type.missing,
        type.missing, type.missing, type.missing, excel.xlsaveasaccessmode.xlexclusive,
        type.missing, type.missing, type.missing, type.missing, type.missing);
    workbook.close(true, type.missing, type.missing);
    workbook = null;
    excel.quit();
    killspecialexcel(excel);
    excel = null;
    gc.collect();
    gc.waitforpendingfinalizers();
}

[dllimport("user32.dll", setlasterror = true)]
static extern int getwindowthreadprocessid(intptr hwnd, out int processid);

static void killspecialexcel(excel.application app)
{
    try
    {
        if (app != null)
        {
            int processid;
            getwindowthreadprocessid(new intptr(app.hwnd), out processid);
            system.diagnostics.process.getprocessbyid(processid).kill();
        }
    }
    catch (exception ex)
    {
        throw ex;
    }
}

文件流

这种方法的效率明显高于第一种,而且也不需要安装office,但是导出的xls文件并不符合excel的格式标准,在打开生成的xls文件时会提示:the file you are trying to open is in a different format that specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening the file.

复制代码 代码如下:

public static void exporttoexcel(system.data.dataset ds, string path)
{
    streamwriter sw = null;
    try
    {
        long totalcount = ds.tables[0].rows.count;
        sw = new streamwriter(path, false, encoding.unicode);
        stringbuilder sb = new stringbuilder();
        for (int i = 0; i < ds.tables[0].columns.count; i++)
        {
            sb.append(ds.tables[0].columns[i].columnname + "\t");
        }
        sb.append(environment.newline);
        for (int i = 0; i < ds.tables[0].rows.count; i++)
        {
            for (int j = 0; j < ds.tables[0].columns.count; j++)
            {
                sb.append(ds.tables[0].rows[i][j].tostring() + "\t");
            }
            sb.append(environment.newline);
        }
        sw.write(sb.tostring());
        sw.flush();
    }
    catch (ioexception ioe)
    {
        throw ioe;
    }
    finally
    {
        if (sw != null)
        {
            sw.close();
        }
    }
}