C#中dataset匯出到excel

c#中dataset匯出到excel

cn = new adodb.connection
                str = "provider=microsoft.jet.oledb.4.0;data source=" & me.ofdselectexcel.filename & ";extended properties=""excel 8.0;hdr=yes"""
                cn.open(str)
                '擷取所有sheet的名稱
                cbsheet.properties.items.clear()
                dim rs1 as new adodb.recordset
                rs1 = cn.openschema(adodb.schemaenum.adschematables)
                while not rs1.eof
                    cbsheet.properties.items.add(rs1.fields("table_name").value)
                    rs1.movenext()
                end while
                cn.close()

 

////上面是擷取選中excel的sheet名稱

出錯是因為你的excel的sheet名稱被改過了，預設是sheet1$

好了給合上面我們來看個完整的dataset匯出到excel代碼。

/// <summary>
/// 將datatable的資料匯出到excel中。
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="xlsfiledir">匯出的excel檔案存放目錄（絕對路徑，最後帶“”）</param>
/// <param name="namelist">datatable中列名的中文對應表</param>
/// <param name="strtitle">excel表的標題</param>
/// <returns>excel檔案名稱</returns>
public static string exportdatatoexcel(system.data.datatable dt, string xlsfiledir, hashtable namelist, string strtitle)
{
    if (dt == null) return "";

    microsoft.office.interop.excel.applicationclass excel = new microsoft.office.interop.excel.applicationclass();
    microsoft.office.interop.excel.workbooks workbooks = excel.workbooks;
    microsoft.office.interop.excel.workbook workbook = workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
    microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];

    int titlerowscount = 0;
    if (strtitle != null && strtitle.trim() != "")
    {
        titlerowscount = 1;
        excel.get_range(excel.cells[1, 1], excel.cells[1, dt.columns.count]).font.bold = true;
        excel.get_range(excel.cells[1, 1], excel.cells[1, dt.columns.count]).font.size = 16;
        excel.get_range(excel.cells[1, 1], excel.cells[1, dt.columns.count]).mergecells = true;
        worksheet.cells[1, 1] = strtitle;
    }
    if (!system.io.directory.exists(xlsfiledir))
    {
        system.io.directory.createdirectory(xlsfiledir);
    }
    string strfilename = datetime.now.tostring("yyyymmddhhmmssff") + ".xls";

    string tempcolumnname = "";

    for (int i = 0; i < dt.rows.count; i++)
    {
        for (int j = 0; j < dt.columns.count; j++)
        {
            if (i == 0)
            {
                tempcolumnname = dt.columns[j].columnname.trim();
                if (namelist != null)
                {
                    idictionaryenumerator enum = namelist.getenumerator();
                    while (enum.movenext())
                    {
                        if (enum.key.tostring().trim() == tempcolumnname)
                        {
                            tempcolumnname = enum.value.tostring();
                        }
                    }
                }
                worksheet.cells[titlerowscount + 1, j + 1] = tempcolumnname;
            }
            worksheet.cells[i + titlerowscount + 2, j + 1] = dt.rows[i][j].tostring();
        }
    }
    excel.get_range(excel.cells[titlerowscount + 1, 1], excel.cells[titlerowscount + 1, dt.columns.count]).font.bold = true;
    excel.get_range(excel.cells[1, 1], excel.cells[titlerowscount + 1 + dt.rows.count, dt.columns.count]).horizontalalignment = xlvalign.xlvaligncenter;
    excel.get_range(excel.cells[1, 1], excel.cells[titlerowscount + 1 + dt.rows.count, dt.columns.count]).entirecolumn.autofit();

    workbook.saved = true;
    workbook.savecopyas(xlsfiledir + strfilename);
    system.runtime.interops教程ervices.marshal.releasecomobject(worksheet);
    worksheet = null;
    system.runtime.interopservices.marshal.releasecomobject(workbook);
    workbook = null;
    workbooks.close();
    system.runtime.interopservices.marshal.releasecomobject(workbooks);
    workbooks = null;
    excel.quit();
    system.runtime.interopservices.marshal.releasecomobject(excel);
    excel = null;
    return strfilename;
}