C# WinForm导出Excel方法介绍

.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();
        }
    }
}