[C#] Excel 多個 sheet

利用 AdventureWorks2014 當成資料來源，要把個部門員工匯出至 Excel 內且一個部門要單獨在一個 Sheet 內。

參考

加入 Microsoft.Office.Interop.Excel 和 System.Configuration

專案和 Layout

Store Procedure

USE [AdventureWorks2014]
GO 

CREATE PROCEDURE dbo.GetDepartment
AS
BEGIN
    SELECT 
        DepartmentID AS DepNO ,
        Name AS DepName
    FROM [HumanResources].[Department]
    ORDER BY DepNO
END
GO

USE [AdventureWorks2014]
GO

CREATE PROCEDURE dbo.GetEmployee (@DepNO smallint)
AS
BEGIN
    SELECT
        D.DepartmentID AS DepNO ,
        D.Name AS DepName ,
        P.BusinessEntityID AS EmpNO,
        CONCAT(P.FirstName , ' - ' , P.LastName) AS EmpName ,
        E.jobTitle ,
        E.HireDate ,
        E.BirthDate
    FROM [HumanResources].[Employee] AS E
        JOIN [Person].[Person] AS P ON E.BusinessEntityID = P.BusinessEntityID
        JOIN [HumanResources].[EmployeeDepartmentHistory] AS H ON E.BusinessEntityID = H.BusinessEntityID
        JOIN [HumanResources].[Department] AS D ON H.DepartmentID = D.DepartmentID 
    WHERE H.EndDate IS NULL 
        AND D.DepartmentID = @DepNO
    ORDER BY DepNO , EmpNO
END
GO

App.config

把連線字串放在裡面

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <connectionStrings>
        <add name="default" connectionString="Data Source=InstanceName;Initial Catalog=AdventureWorks2014;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

C# Code

using System.Configuration;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace Data2ExcelSheet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog save = new SaveFileDialog();
            save.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            save.FileName = "Excel Export demo";
            save.Filter = "*.xlsx|*.xlsx";
            if (save.ShowDialog() != DialogResult.OK) return;

            DataTable Department = GetDepartment();
            Excel.Application xls = new Excel.Application();
            try
            {
                // 設定新增 WorkBook 時，會有幾個 sheet
                xls.SheetsInNewWorkbook = Department.Rows.Count;
                Excel.Workbook book = xls.Workbooks.Add();

                int index, DepNO;
                string DepName;
                Excel.Worksheet sheet;
                foreach (DataRow row in Department.Rows)
                {
                    DepNO = Convert.ToInt16(row["DepNO"]);
                    DepName = row["DepName"].ToString();

                    index = Department.Rows.IndexOf(row) + 1;
                    sheet = (Excel.Worksheet)xls.Worksheets[index];
                    sheet.Name = DepName;

                    Data2Sheet(sheet, GetEmployee(DepNO));
                }

                book.SaveAs(save.FileName);
                }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                xls.Quit();
            }
        }

        private void Data2Sheet(Excel.Worksheet sheet, DataTable dt)
        {
            int rowindex, colindex;
            foreach (DataRow row in dt.Rows)
            {
                rowindex = dt.Rows.IndexOf(row) + 1;

                foreach (DataColumn col in dt.Columns)
                {
                    colindex = dt.Columns.IndexOf(col) + 1;
                    sheet.Cells[rowindex, colindex] = row[col].ToString();
                }
            }   
        }

        private DataTable GetEmployee(int DepNO)
        {
            try
            {
                string ConnString = ConfigurationManager.ConnectionStrings["default"].ToString();

                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "GetEmployee";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@DepNO", DepNO);

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private DataTable GetDepartment()
        {
            try
            {
                string ConnString = ConfigurationManager.ConnectionStrings["default"].ToString();
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "GetDepartment";
                    cmd.CommandType = CommandType.StoredProcedure;

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

匯出資料

延伸閱讀
• [C#] foreach 中的 index

參考資料
• 論壇討論