將DataSet轉成Excel

整個是一個Application

Book1是一個活頁簿

Sheet1是一個工作表

ColumnName是標題1、標題2、標題3

Cell是裡面的表格

使用套件Microsoft.Office.Interop.Excel;

//new 一個 Excel

Application application = new Application();

//在excel中加入新的活頁簿

Workbook wBook = application.Workbooks.Add(Type.Missing);

for (int z = 0; z < dsData.Tables.Count; z++)

{

    //建立新的工作表

    Worksheet workSheet = (Worksheet)wBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    workSheet.Name = dsData.Tables[z].TableName;

    //column headings

    for (int i = 0; i < dsData.Tables[z].Columns.Count; i++)

    {

        Range headRange = (Range)workSheet.Cells[1, i + 1];

        headRange.Font.Bold = true; //粗體

        headRange.Value2 = dsData.Tables[z].Columns[i].ColumnName;

    }

    //rows

    for (int i = 0; i < dsData.Tables[z].Rows.Count; i++)

    {

        //cells

        for (int j = 0; j < dsData.Tables[z].Columns.Count; j++)

        {

            workSheet.Cells[(i + 2), (j + 1)] = dsData.Tables[z].Rows[i][j];

        }

    }

}

wBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

    XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, Type.Missing);

application.Quit();

其中Workbook.SaveAs的參數可參考以下連結：

http://msdn.microsoft.com/zh-tw/library/microsoft.office.tools.excel.workbook.saveas.aspx