C#中datatabel導出excel(三種方法)

　　方法一(拷貝直接可以使用適合大批量資料 上萬筆)
MicrosoftOfficeInteropExcelApplication appexcel = new MicrosoftOfficeInteropExcelApplication();
SaveFileDialog savefiledialog = new SaveFileDialog();
SystemReflectionMissing miss = SystemReflectionMissingValue;
appexcel = new MicrosoftOfficeInteropExcelApplication();
MicrosoftOfficeInteropExcelWorkbook workbookdata;
MicrosoftOfficeInteropExcelWorksheet worksheetdata;
MicrosoftOfficeInteropExcelRange rangedata;
//設置對象不可見
appexcelVisible = false;
SystemGlobalizationCultureInfo currentci = SystemThreadingThreadCurrentThreadCurrentCulture;
SystemThreadingThreadCurrentThreadCurrentCulture = new SystemGlobalizationCultureInfo(enus);
workbookdata = appexcelWorkbooksAdd(miss);
worksheetdata = (MicrosoftOfficeInteropExcelWorksheet)workbookdataWorksheetsAdd(miss miss miss miss);
//給工作表賦名稱
worksheetdataName = saved;
for (int i = ; i < dtColumnsCount; i++)
{
    worksheetdataCells[ i + ] = dtColumns[i]ColumnNameToString();
}
//因為第一行已經寫了表頭所以所有數據都應該從a開始
rangedata = worksheetdataget_Range(a miss);
MicrosoftOfficeInteropExcelRange xlrang = null;
//irowcount為實際行數最大行
int irowcount = dtRowsCount;
int iparstedrow = icurrsize = ;
//ieachsize為每次寫行的數值可以自己設置
int ieachsize = ;
//icolumnaccount為實際列數最大列數
int icolumnaccount = dtColumnsCount;
//在內存中聲明一個ieachsize×icolumnaccount的數組ieachsize是每次最大存儲的行數icolumnaccount就是存儲的實際列數
object[] objval = new object[ieachsize icolumnaccount];
icurrsize = ieachsize;

　　while (iparstedrow < irowcount)
{
    if ((irowcount iparstedrow) < ieachsize)
        icurrsize = irowcount iparstedrow;
    //用for循環給數組賦值
    for (int i = ; i < icurrsize; i++)
    {
        for (int j = ; j < icolumnaccount; j++)
            objval[i j] = dtRows[i + iparstedrow][j]ToString();
        SystemWindowsFormsApplicationDoEvents();
    }
    string X = A + ((int)(iparstedrow + ))ToString();
    string col = ;
    if (icolumnaccount <= )
    {
        col = ((char)(A + icolumnaccount ))ToString() + ((int)(iparstedrow + icurrsize + ))ToString();
    }
    else
    {
        col = ((char)(A + (icolumnaccount / )))ToString() + ((char)(A + (icolumnaccount % )))ToString() + ((int)(iparstedrow + icurrsize + ))ToString();
    }
    xlrang = worksheetdataget_Range(X col);
    // 調用range的value屬性把內存中的值賦給excel
    xlrangValue = objval;
    iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
SystemRuntimeInteropServicesMarshalReleaseComObject(xlrang);
xlrang = null;
//調用方法關閉excel進程
appexcelVisible = true;

　　方法二(自己建函數適合大批量資料 上萬筆)
using SystemIO;
private void dataTableToCsv(DataTable table string file)
{
    string title = ;
    FileStream fs = new FileStream(file FileModeOpenOrCreate);
    //FileStream fs = FileOpen(file FileModeOpen FileAccessRead);
    StreamWriter sw = new StreamWriter(new BufferedStream(fs) SystemTextEncodingDefault);
    for (int i = ; i < tableColumnsCount; i++)
    {
        title += tableColumns[i]ColumnName + \t; //欄位自動跳到下一單元格
    }
    title = titleSubstring( titleLength ) + \n;
    swWrite(title);
    foreach (DataRow row in tableRows)
    {
        string line = ;
        for (int i = ; i < tableColumnsCount; i++)
        {
            line += row[i]ToString()Trim() + \t; //內容自動跳到下一單元格
        }
        line = lineSubstring( lineLength ) + \n;
        swWrite(line);
    }
    swClose();
    fsClose();
}
dataTableToCsv(dt @c:\xls); //調用函數
SystemDiagnosticsProcessStart(@c:\xls);  //打開excel文件

　　方法三:(可以自己調整單元格的格式適合小批量的數量)
try
{
    //沒有數據的話就不往下執行
    if (dataGridViewRowsCount == )
        return;
    //實例化一個ExcelApplication對象
    MicrosoftOfficeInteropExcelApplication excel = new MicrosoftOfficeInteropExcelApplication();
    //讓後台執行設置為不可見為true的話會看到打開一個Excel然後數據在往裡寫
    //excelVisible = false;
    excelVisible = true;
    //新增加一個工作簿Workbook是直接保存不會彈出保存對話框加上Application會彈出保存對話框值為false會報錯
    excelApplicationWorkbooksAdd(true);
    //生成Excel中列頭名稱
    for (int i = ; i < dataGridViewColumnsCount; i++)
    {
        excelCells[ i + ] = dataGridViewColumns[i]HeaderText;
    }
    //把DataGridView當前頁的數據保存在Excel中
    for (int i = ; i < dataGridViewRowsCount ; i++)
    {
        for (int j = ; j < dataGridViewColumnsCount; j++)
        {
            if (dataGridView[j i]ValueType == typeof(string))
            {
                excelCells[i + j + ] = + dataGridView[j i]ValueToString();
            }
            else
            {
                excelCells[i + j + ] = dataGridView[j i]ValueToString();
            }
        }
    }
    //設置禁止彈出保存和覆蓋的詢問提示框
    excelDisplayAlerts = false;
    excelAlertBeforeOverwriting = false;
    ////保存工作簿
    //excelApplicationWorkbooksAdd(true)Save();
    ////保存excel文件
    //excelSave(D: + \\KKHMDxls);
    ////確保Excel進程關閉
    //excelQuit(); //可以直接打開文件
    //excel = null;
}
catch (Exception ex)
{
    MessageBoxShow(exMessage 錯誤提示);
}
Exceloutput((DataTable)dataGridViewDataSource);

From:http://tw.wingwit.com/Article/program/net/201311/11464.html