Open XML SDK を利用してExcel シートに値を設定する - C#

Open XML SDK を利用してExcel シートに値を設定するコードを紹介します。

概要

こちらの記事では、Open XML SDK を利用して空のExcelブックファイルを作成するコードを紹介しました。 この記事では、Open XML SDKを利用してExcelのシートに値を設定するコードを紹介します。

事前準備

プロジェクトを作成しOpen XML SDKをインストールします。インストール手順はこちらの記事を参照してください。

プログラム1

UI

下図のフォームを作成します。Buttonが2つ配置されていますが、今回は下のボタンのみを利用します。

コード

以下のコードを記述します。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ExcelApp
{
  public partial class Form_SimpleCreateFile : Form
  {
    public Form_SimpleCreateFile()
    {
      InitializeComponent();
    }

    private void button2_Click(object sender, EventArgs e)
    {
      SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test2.xlsx", SpreadsheetDocumentType.Workbook);

      WorkbookPart wbpart = doc.AddWorkbookPart();
      wbpart.Workbook = new Workbook();
      WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
      wspart.Worksheet = new Worksheet(new SheetData());
      Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

      Sheet sheet = new Sheet();
      sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
      sheet.SheetId = 1;
      sheet.Name = "商品";
      sheets.Append(sheet);


      Cell cellA1 = InsertCellInWorksheet("A",1, wspart);
      cellA1.CellValue = new CellValue("ぺんぎんクッキー");
      cellA1.DataType = new EnumValue<CellValues>(CellValues.String);
      
      Cell cellB1 = InsertCellInWorksheet("B", 1, wspart);
      cellB1.CellValue = new CellValue("130");
      cellB1.DataType = new EnumValue<CellValues>(CellValues.Number);

      Cell cellA2 = InsertCellInWorksheet("A", 2, wspart);
      cellA2.CellValue = new CellValue("らくだキャラメル");
      cellA2.DataType = new EnumValue<CellValues>(CellValues.String);

      Cell cellB2 = InsertCellInWorksheet("B", 2, wspart);
      cellB2.CellValue = new CellValue("80");
      cellB2.DataType = new EnumValue<CellValues>(CellValues.Number);

      Cell cellA3 = InsertCellInWorksheet("A", 3, wspart);
      cellA3.CellValue = new CellValue("合計");
      cellA3.DataType = new EnumValue<CellValues>(CellValues.String);

      Cell cellB3 = InsertCellInWorksheet("B", 3, wspart);
      cellB3.CellFormula = new CellFormula("=B1+B2");

      wbpart.Workbook.Save();

      doc.Close();
      MessageBox.Show("Excel Bookを作成しました。");
    }

    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
    {
      Worksheet worksheet = worksheetPart.Worksheet;
      SheetData sheetData = worksheet.GetFirstChild<SheetData>();
      string cellReference = columnName + rowIndex;

      Row row;
      if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
        row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
      }
      else {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
      }

      if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
        return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
      }
      else {
        Cell refCell = null;
        foreach (Cell cell in row.Elements<Cell>()) {
          if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
            refCell = cell;
            break;
          }
        }

        Cell newCell = new Cell() { CellReference = cellReference };
        row.InsertBefore(newCell, refCell);

        worksheet.Save();
        return newCell;
      }
    }
  }
}

解説

前半部分のコードはExcelのブックとシートを作成するコードになります。詳しくはこちらの記事を参照してください。

      SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test2.xlsx", SpreadsheetDocumentType.Workbook);

      WorkbookPart wbpart = doc.AddWorkbookPart();
      wbpart.Workbook = new Workbook();
      WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
      wspart.Worksheet = new Worksheet(new SheetData());
      Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

      Sheet sheet = new Sheet();
      sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
      sheet.SheetId = 1;
      sheet.Name = "商品";
      sheets.Append(sheet);

セルオブジェクトを取得するコードです。
行オブジェクトが存在しない場合は行オブジェクトを挿入します。 その後、行オブジェクト内のセルを取得してメソッドの戻り値とします。行オブジェクト内にセルオブジェクトがない場合には、セルオブジェクトを新規作成し、 作成したセルオブジェクトを行オブジェクトに挿入し、作成したセルオブジェクトをメソッドの戻り値として返します。
Whereメソッドの動作についてはこちらの記事を参照してください。

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
  Worksheet worksheet = worksheetPart.Worksheet;
  SheetData sheetData = worksheet.GetFirstChild<SheetData>();
  string cellReference = columnName + rowIndex;

  Row row;
  if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
  }
  else {
    row = new Row() { RowIndex = rowIndex };
    sheetData.Append(row);
  }

  if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
  }
  else {
    Cell refCell = null;
    foreach (Cell cell in row.Elements<Cell>()) {
      if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
        refCell = cell;
        break;
      }
    }

    Cell newCell = new Cell() { CellReference = cellReference };
    row.InsertBefore(newCell, refCell);

    worksheet.Save();
    return newCell;
  }
}

ワークシートオブジェクト、シートデータオブジェクト、セルの座標文字列を取得して準備します。

  Worksheet worksheet = worksheetPart.Worksheet;
  SheetData sheetData = worksheet.GetFirstChild<SheetData>();
  string cellReference = columnName + rowIndex;

sheetData.Elements Enumerableオブジェクトの中で RowIndex プロパティが与えた行番号と一致している要素を取得します。その要素のCountメソッドの値が 0 の場合は、 Rowオブジェクトを新規に作成します。作成したRowオブジェクトの RowIndexプロパティに与えた行番号を設定して、sheetData オブジェクトに追加します。
Countメソッドの値が 0 以外の場合はsheetData.Elements Enumerableオブジェクトの中で RowIndex プロパティが与えた行番号と一致している要素の中で、Firstメソッドにより最初の要素を取得します。
(sheetData.Elements のRowオブジェクトで同じRowIndexを持つ要素は無いため、Firstメソッドを利用していますが、基本的にRowIndex プロパティが与えた行番号と一致している要素が取得されます。)

  if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
  }
  else {
    row = new Row() { RowIndex = rowIndex };
    sheetData.Append(row);
  }

RowオブジェクトのElements Enumerableオブジェクトの中で CellReference.Value プロパティの値が、セルの座標名と一致する要素を取得します。
その要素のCountメソッドの値が 0 より大きい場合は、Elements Enumerableオブジェクトの中で CellReference.Value プロパティの値が、セルの座標名と一致する要素の 一番最初の要素をメソッドの戻り値として返します。
(row.Elements のCellオブジェクトで同じCellReference.Valueを持つ要素は無いため、Firstメソッドを利用していますが、基本的にCellReference.Value プロパティが与えた セルの座標名と一致している要素が取得されます。)

Countメソッドの値が0 以下の場合は、セルのオブジェクトが存在しないため、Cellオブジェクトを一つずつ確認します。 セルオブジェクトのCellReference.Value と与えたセル座標を比較し、座標値を超えた位置のセルを取得しrefCell に記録します。
新しいCellオブジェクトを作成し、CellReferenceに取得したいセルの位置として与えられたセルの座標名を設定します。
InsertBeforeメソッドを呼び出し、refCellの一つ手前の位置にセルを挿入します。

  if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
  }
  else {
    Cell refCell = null;
    foreach (Cell cell in row.Elements<Cell>()) {
      if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
        refCell = cell;
        break;
      }
    }

    Cell newCell = new Cell() { CellReference = cellReference };
    row.InsertBefore(newCell, refCell);

    worksheet.Save();
    return newCell;
  }

セルに値を設定するコードです。 InsertCellInWorksheetメソッドを呼び出し、セルオブジェクトを取得します。取得するセルの位置を第一引数と第二引数で指定しします。
取得したセルオブジェクトのCellValueプロパティに、CellValueオブジェクトのインスタンスを設定してセルに値を設定します。 また、セルのタイプは、DataType プロパティに設定します。文字列をセルに設定する場合は、EnumValueコンストラクタにCellValues.Stringを与えます。

  Cell cellA1 = InsertCellInWorksheet("A",1, wspart);
  cellA1.CellValue = new CellValue("ぺんぎんクッキー");
  cellA1.DataType = new EnumValue<CellValues>(CellValues.String);

数値をセルに設定する場合は、EnumValueコンストラクタにCellValues.Stringを与えたEnumValueオブジェクトをDataType プロパティに設定します。

  Cell cellB2 = InsertCellInWorksheet("B", 2, wspart);
  cellB2.CellValue = new CellValue("80");
  cellB2.DataType = new EnumValue<CellValues>(CellValues.Number);

数式をセルに設定する場合は、CellFormula プロパティに CellFormula オブジェクトを設定します。数式はCellFormula オブジェクトのコンストラクタに与えます。

  Cell cellB3 = InsertCellInWorksheet("B", 3, wspart);
  cellB3.CellFormula = new CellFormula("=B1+B2");

実行結果

プロジェクトを実行します。下図のウィンドウが表示されます。


2つ目のボタン[Create Book and Value]のボタンをクリックします。クリックして処理が完了すると「Excel Bookを作成しました。」のメッセージボックスが表示されます。


Excelファイルの出力先を確認します。ワークシートのファイルが作成されていることが確認できます。


ワークシートのファイルを開きます。シート名がコードで設定したシート名になっています。また、セルに値が挿入されていることも確認できます。


セルの幅を変更してセルの値を確認します。コードで設定した値がセルに設定されていることが確認できます。

プログラム2 : 上の行から順番に書き出す場合

上の行から順番にシーケンシャルで書き出す場合はコードが若干シンプルになります。

UI

下図のフォームを作成します。フォームにボタンを配置します。

コード

下記のコードを記述します。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;


namespace ExcelApp
{
  public partial class Form_DataOutput : Form
  {
    List<DataItem> dataList = new List<DataItem>();
    class DataItem
    {
      public int id;
      public string name;
      public string code;
      public int price;

    }

    public Form_DataOutput()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      dataList.Clear();
      dataList.Add( new DataItem() { id = 1, name = "ぺんぎんクッキー", code = "PCD-001", price = 180 });
      dataList.Add( new DataItem() { id = 2, name = "あひるサブレ", code = "PCD-002", price = 120 });
      dataList.Add( new DataItem() { id = 3, name = "らくだキャラメル", code = "PCD-003", price = 40 });
      dataList.Add( new DataItem() { id = 4, name = "しろくまアイス", code = "PCA-001", price = 230 });
      dataList.Add( new DataItem() { id = 5, name = "だちょうカステラ", code = "PCD-004", price = 90 });

      //シート作成
      SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test-ex.xlsx", SpreadsheetDocumentType.Workbook);

      WorkbookPart wbpart = doc.AddWorkbookPart();
      wbpart.Workbook = new Workbook();
      WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
      wspart.Worksheet = new Worksheet(new SheetData());
      Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

      Sheet sheet = new Sheet();
      sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
      sheet.SheetId = 1;
      sheet.Name = "商品";
      sheets.Append(sheet);

      //データ挿入
      Row row;
      UInt32Value row_index = 1;

      foreach (DataItem di in dataList) {
        row = new Row() { RowIndex = row_index };
        wspart.Worksheet.GetFirstChild<SheetData>().Append(row);

        Cell refCell = null;
        Cell newCell;

        newCell = new Cell() { CellReference = "A" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.id.ToString());
        newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "B" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.name);
        newCell.DataType = new EnumValue<CellValues>(CellValues.String);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "C" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.code);
        newCell.DataType = new EnumValue<CellValues>(CellValues.String);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "D" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.price.ToString());
        newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;

        //row.InsertBefore(

        row_index++;
      }

      wbpart.Workbook.Save();

      doc.Close();
      MessageBox.Show("Excel Bookを作成しました。");

    }
  }
}

解説

データのレコードを保持するリストをクリアし、要素を作成してデータを作成しています。

      dataList.Clear();
      dataList.Add( new DataItem() { id = 1, name = "ぺんぎんクッキー", code = "PCD-001", price = 180 });
      dataList.Add( new DataItem() { id = 2, name = "あひるサブレ", code = "PCD-002", price = 120 });
      dataList.Add( new DataItem() { id = 3, name = "らくだキャラメル", code = "PCD-003", price = 40 });
      dataList.Add( new DataItem() { id = 4, name = "しろくまアイス", code = "PCA-001", price = 230 });
      dataList.Add( new DataItem() { id = 5, name = "だちょうカステラ", code = "PCD-004", price = 90 });

Excelのドキュメント、シートを作成する処理です。詳しくはこちらの記事を参照してください。

      //シート作成
      SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test-ex.xlsx", SpreadsheetDocumentType.Workbook);

      WorkbookPart wbpart = doc.AddWorkbookPart();
      wbpart.Workbook = new Workbook();
      WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
      wspart.Worksheet = new Worksheet(new SheetData());
      Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

      Sheet sheet = new Sheet();
      sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
      sheet.SheetId = 1;
      sheet.Name = "商品";
      sheets.Append(sheet);

データをExcelシートに配置する処理です。行はforeachループによるループで処理をします。

      //データ挿入
      Row row;
      UInt32Value row_index = 1;

      foreach (DataItem di in dataList) {
        row = new Row() { RowIndex = row_index };
        wspart.Worksheet.GetFirstChild<SheetData>().Append(row);

        Cell refCell = null;
        Cell newCell;

        newCell = new Cell() { CellReference = "A" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.id.ToString());
        newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "B" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.name);
        newCell.DataType = new EnumValue<CellValues>(CellValues.String);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "C" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.code);
        newCell.DataType = new EnumValue<CellValues>(CellValues.String);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;
        //
        newCell = new Cell() { CellReference = "D" + row_index.ToString() };
        newCell.CellValue = new CellValue(di.price.ToString());
        newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
        row.InsertAfter(newCell, refCell);
        refCell = newCell;

        row_index++;
      }

ループの最初で行オブジェクトを挿入します。RowIndexには行番号を設定します。

    row = new Row() { RowIndex = row_index };
    wspart.Worksheet.GetFirstChild<SheetData>().Append(row);

行オブジェクトにセルオブジェクトを挿入しセルに値を設定しています。セルオブジェクトのCellReference プロパティにセルの座標名を設定します。 ("B5" など)
4列ありますので、4列分の処理　(A,B,C,D列) を実行します。

    newCell = new Cell() { CellReference = "A" + row_index.ToString() };
    newCell.CellValue = new CellValue(di.id.ToString());
    newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
    row.InsertAfter(newCell, refCell);
    refCell = newCell;

ワークブックを保存しドキュメントを閉じます。

   wbpart.Workbook.Save();
   doc.Close();

実行結果

プロジェクトを実行します。下図のウィンドウが表示されます。フォームのボタンをクリックします」。


処理が完了するとメッセージボックスが表示されます。


処理が完了すると、出力先のディレクトリにExcelのワークシートファイル(xlsx形式)が作成されます。


ワークシートのファイルを開きます。下図の画面が表示されます。


B列の幅を調整して表示内容を確認します。コードで記述したデータがExcelのシートに書き出されていることが確認できます。


Excelシートに値を設定することができました。