2014年7月4日 星期五

將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的參數可參考以下連結:

2 則留言: