DataSet から Excel.Worksheet へデータ書き出しのメモ

1. 設計

2. 結果

4. コード

using Excel = Microsoft.Office.Interop.Excel;

public partial class Form1 : Form
{
    private const string COL_NAME = "Name";
    private const string COL_KAMI = "Kami";
    private const string COL_SIMO = "Simo";

    private const string RANGE_BASE = "Base";

    private DataSet _dataSet;

    private Excel.Worksheet _wSheet;

    // Excelのテンプレートファイルを開いて、データを書き込んで、名前を付けて保存
    private void btnExec_Click(object sender, EventArgs e)
    {
        const string path = @"C:\Users\Kanda\Documents\Study\Cs\Excel\";

        // データ準備
        SetDataToDataSet("_1_");

        Excel.Application excel = new Excel.Application();
        try
        {
            Excel.Workbook wBook = excel.Workbooks.Add(path + "Template01.xlt");
            _wSheet = wBook.Sheets[1];

            OutputDataToWorkSheet("_1_");

            ClearDataSet();

            wBook.SaveAs(path + "OutBook01.xls", Excel.XlFileFormat.xlWorkbookNormal);
            _wSheet = null;
            wBook.Close();
            wBook = null;
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex.Message);
        }
        finally
        {
            excel.Quit();
            excel = null;
        }
    }

    // ワークシートへデータを書き込む
    private void OutputDataToWorkSheet(string tableName)
    {
        // 行を準備する（基となる行をコピーして必要分だけ挿入する）
        if (MakeOutputRanges(tableName) == false) return;

        // データテーブル参照取得
        DataTable table = _dataSet.Tables[tableName];

        for (int r = 0; r < table.Rows.Count; r++)
        {
            DataRow dataRow = table.Rows[r];

            for (int c = 0; c < table.Columns.Count; c++)
            {
                string columnName = table.Columns[c].ColumnName;
                if (!dataRow[columnName].Equals(DBNull.Value))
                {
                    int iRow = 0, iCol = 0;
                    if (SetIndexRowColFromColumnName(tableName, columnName, out iRow, out iCol) > 0)
                    {
                        _wSheet.Cells[iRow + r, iCol] = dataRow[columnName];
                    }
                }
            }
        }
    }

    // 基となる行をコピーして、必要な分だけ挿入する
    private bool MakeOutputRanges(string tableName)
    {
        int iRow = 0, iCol = 0;
        int rowsNum = SetIndexRowColFromColumnName(tableName, RANGE_BASE, out iRow, out iCol);
        if (rowsNum == 0) return false;

        DataTable table = _dataSet.Tables[tableName];
        for (int r = 0 + rowsNum; r < table.Rows.Count; r += rowsNum)
        {
            string rowSrce = string.Format("{0}:{1}", iRow, iRow + rowsNum - 1);
            iRow = iRow + rowsNum;
            string rowDest = string.Format("{0}:{1}", iRow, iRow + rowsNum - 1);
            Excel.Range rngSrce = _wSheet.get_Range(rowSrce, Missing.Value);
            Excel.Range rngDest = _wSheet.get_Range(rowDest, Missing.Value);
            rngSrce.Copy();
            rngDest.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
        }
        return true;
    }

    // ワークシートから名前が付いた範囲を探し、行・列のインデックスを返す
    private int SetIndexRowColFromColumnName(string tableName, string columnName, out int iRow, out int iCol)
    {
        string searchName = string.Concat(tableName, columnName);
        foreach (Excel.Name name in _wSheet.Names)
        {
            if (name.Name.Contains(searchName))
            {
                Excel.Range range = name.RefersToRange;
                if (true.Equals(range.MergeCells)) // セル範囲は結合セルか？
                {
                    range = range.MergeArea;
                }
                iRow = range.Row + range.Rows.Count;
                iCol = range.Column;
                return range.Rows.Count;  // 範囲の行数を返す
            }
        }
        iRow = iCol = 0;
        return 0;
    }

    // データセット クリア
    private void ClearDataSet()
    {
        _dataSet.Clear();
        _dataSet.Dispose();
        _dataSet = null;
    }

    // データセットへテストデータを入れる
    private void SetDataToDataSet(string tableName)
    {
        _dataSet = new DataSet("2012");
        DataTable table = _dataSet.Tables.Add(tableName);
        table.Columns.Add(COL_NAME, typeof(string));
        table.Columns.Add(COL_KAMI, typeof(int));
        table.Columns.Add(COL_SIMO, typeof(int));

        string[] name = new string[3] { "りんご", "みかん", "バナナ" };
        int[, ,] data = new int[3, 2, 2] {
            { { 2, 2 },
                { 1, 3 } },
            { { 5, 5 },
                { 6, 4 } },
            { { 8, 9 },
                { 7, 8} },
        };

        for (int r = 0; r < 3; r++)
        {
            DataRow row = table.NewRow();
            row[COL_NAME] = name[r];
            row[COL_KAMI] = data[r, 0, 0];
            row[COL_SIMO] = data[r, 0, 1];
            table.Rows.Add(row);

            row = table.NewRow();
            row[COL_KAMI] = data[r, 1, 0];
            row[COL_SIMO] = data[r, 1, 1];
            table.Rows.Add(row);
        }
    }
}