SSブログ

OleDBを用いてDataTable又はDataGridViewに格納されたデータからExcel又はCSVファイルに出力する [プログラム]

C#からOleDBを用いたExcelへの記述は、読み込みはあっても書き込みはあまりないので、作成しました。
VS2008のC#で確認しています。

間違い等、指摘いただきましたら、修正いたします。

Excelのセルの型は、int、text、timeしか用意していません。

OleDBはどうやら、拡張子を見ているようです。
拡張子が、.xlsなら~Excel2003、.xlsxならExcel2007形式です。

xls形式は、Excelがインストールされていなくても動作し出力しますが、xlsxはプロバイダがないと怒られます。多分、Excel2007が必要だと思います。

また、.xlsなのに動作しない方は、AnyCPUでコンパイルするのではなく、x86でコンパイルして下さい。

ExcelOut()がDataTableからExcel又はCSVファイルへ
ExcelDGVOut()がDataGridViewからExcel又はCSVファイルへ

CSVファイルを作成する場合は、時間がかかります。
また、schema.iniというファイルが作成されますが、仕様です。

ポイントは、データベースのように扱えることです。

・.xlsx(Excel2007)のプロバイダストリング

OleDbConnection conn;
string strFileName = "test";

conn = new OleDbConnection(
 @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
 strFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");


・xls(Excel2003)のプロバイダストリング

conn = new OleDbConnection(
 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
 strFileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"");


・CSVのプロバイダストリング

//csvファイルにするとschema.iniが作成されるが仕様である
//非常に時間がかかる
//ポイントは指定がフォルダ名である

conn = new OleDbConnection(
 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
 Path.GetDirectoryName(strFileName) +
 ";Extended Properties=\"Text;FMT=Delimited;HDR=Yes\"");


-----------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO; 


namespace ExcelOutPut
{
  class ExcelDefine
  {
    /// <summary>
    /// テキスト型
    /// </summary>
    public const string EXCELVARCHAR = "memo";

    public const string EXCEL_EXT_2007 = ".xlsx";
    public const string EXCEL_EXT_200X = ".xls";

    //エラー
    public const Int32 EXCEL_NO_ERROR = 0x0;
    public const Int32 EXCEL_OPEN_ERROR = 0x10;
    public const Int32 EXCEL_CLOSE_ERROR = 0x11;
    public const Int32 EXCEL_TABLE_ERROR = 0x15;
    public const Int32 EXCEL_INSERT_ERROR = 0x1a;
    public const Int32 EXCEL_OTHER_ERROR = 0x1f;
  }
  class ExcelOutClass
  {
    //時刻表示指定
    public string strEXCELTIME;

    /// <summary>
    /// コンストラクタ
    /// </summary>
    public ExcelOutClass()
    {
      this.strEXCELTIME = "yyyy/MM/dd hh:mm:ss";
    }

    /// <summary>
    /// エクセル出力関数(DataTable)
    /// </summary>
    /// <param name="strFileName">ファイル名</param>
    /// <param name="dt">DataTable</param>
    /// <param name="strErrMessage">エラーメッセージ</param>
    /// <returns>リターン値</returns>
    public Int32 ExcelOut(
      string strFileName,
      DataTable dt,
      ref string strErrMessage
      )
    {
      OleDbCommand command = new OleDbCommand();
      OleDbConnection conn = new OleDbConnection();
      string strCommand = "create table ";
      string strTable = "";

      try
      {
        this.ExcelProvider( strFileName, ref strTable, ref conn);
        try
        {
          conn.Open();
        }
        catch (Exception ex)
        {
          strErrMessage = "ファイルが開けません" +
            Environment.NewLine + ex.Message;
          return ExcelDefine.EXCEL_OPEN_ERROR;
        }

        //ファイルを作成
        command = conn.CreateCommand();

        // テーブルを作成
        if ( strTable == "")
        {
          strTable = "1";
        }
        strCommand += "[" + strTable + "]" + "(";
        for (Int32 i = 0; i < dt.Columns.Count; i++)
        {
          if (dt.Columns[i].DataType.Name == "Integer" |
            dt.Columns[i].DataType.Name == "Int32" |
            dt.Columns[i].DataType.Name == "Decimal" |
            dt.Columns[i].DataType.Name == "Long" |
            dt.Columns[i].DataType.Name == "Double" |
            dt.Columns[i].DataType.Name == "Short")
          {
            //セルの書式を数値型に設定 
            strCommand += "[" + dt.Columns[i].ColumnName + "] int ";
          }
          else if (dt.Columns[i].DataType.Name == "DateTime")
          {
            //セルの書式を日付型に設定 
            strCommand += "[" + dt.Columns[i].ColumnName + "] time ";
          }
          else
          {
            strCommand += "[" + dt.Columns[i].ColumnName + "] " + ExcelDefine.EXCELVARCHAR;
          }
          //最後じゃなければ「,」をつける
          if (i != dt.Columns.Count - 1)
          {
            strCommand += ",";
          }
        }
        strCommand += ")";
        command.CommandText = strCommand;

        try
        {
          command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
          strErrMessage = "Tableの作成に失敗しました" + Environment.NewLine +
            "コマンド:" + strCommand + " " + Environment.NewLine +
            ex.Message;
          conn.Close();
          return ExcelDefine.EXCEL_TABLE_ERROR;
        }

        //データ入力(パラメータマッチング)
        strCommand = " INSERT INTO " + "[" + strTable + "]" + " VALUES (";
        for (Int32 i = 0; i < dt.Columns.Count; i++)
        {
          if (dt.Columns[i].DataType == null)
          {
            continue;
          }
          strCommand += "?";
          //最後じゃなければ「,」をつける
          if (i != dt.Columns.Count - 1)
          {
            strCommand += ",";
          }
        }
        strCommand += ")";


        command.CommandText = strCommand;
        foreach (DataRow dtRow in dt.Rows)
        {
          command.Parameters.Clear();
          for (Int32 i = 0; i < dt.Columns.Count; i++)
          {
            //日時の場合
            if (dt.Columns[i].DataType.Name == "DateTime")
            {
              DateTime dTime = (DateTime)dtRow[i];
              command.Parameters.AddWithValue(
                "@" + i.ToString(),
                "time"
                ).Value = dTime.ToString(this.strEXCELTIME);
            }
            //数値の場合
            else if (dt.Columns[i].DataType.Name == "Integer" |
              dt.Columns[i].DataType.Name == "Int32" |
              dt.Columns[i].DataType.Name == "Decimal" |
              dt.Columns[i].DataType.Name == "Long" |
              dt.Columns[i].DataType.Name == "Double" |
              dt.Columns[i].DataType.Name == "Short")
            {
              command.Parameters.AddWithValue("@" + i.ToString(), "int").Value = dtRow[i];
            }
            //それ以外の場合
            else
            {
              command.Parameters.AddWithValue("@" + i.ToString(), "text").Value = dtRow[i];
            }
          }

          try
          {
            //コマンド実行
            command.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
            strErrMessage = "データ出力に失敗しました" + Environment.NewLine +
              "コマンド:" + command.CommandText + " " +
              Environment.NewLine + ex.Message;
            conn.Close();
            return ExcelDefine.EXCEL_INSERT_ERROR;
          }
        }

        try
        {
          conn.Close();
        }
        catch (Exception ex)
        {
          strErrMessage = "Excelファイルの終了処理に失敗しました" +
            Environment.NewLine + ex.Message;
          return ExcelDefine.EXCEL_CLOSE_ERROR;
        }

        return ExcelDefine.EXCEL_NO_ERROR;
      }
      catch (Exception ex)
      {
        strErrMessage = "Excelファイルの出力処理に失敗しました" +
          Environment.NewLine + ex.Message;
        return ExcelDefine.EXCEL_OTHER_ERROR;
      }
    }

    /// <summary>
    /// エクセル出力関数(DataGridViewから)
    /// </summary>
    /// <param name="strFileName">ファイル名</param>
    /// <param name="bCurr">trueなら選択のみ</param>
    /// <param name="dgv">DataGridView</param>
    /// <param name="strErrMessage">エラーメッセージ</param>
    /// <returns>リターン値</returns>
    public Int32 ExcelDGVOut(
      string strFileName,
      Boolean bCurr,
      DataGridView dgv,
      ref string strErrMessage
      )
    {
      OleDbCommand command = new OleDbCommand();
      OleDbConnection conn = new OleDbConnection();
      string strCommand = "create table ";
      string strTable = "";
      Int32 iRet = ExcelDefine.EXCEL_NO_ERROR;

      try
      {
        this.ExcelProvider(strFileName, ref strTable, ref conn);

        try
        {
          conn.Open();
        }
        catch (Exception ex)
        {
          strErrMessage = "ファイルが開けません" +
            Environment.NewLine + ex.Message;
          return ExcelDefine.EXCEL_OPEN_ERROR;
        }

        //ファイルを作成
        command = conn.CreateCommand();

        // テーブルを作成
        if (strTable == "")
        {
          strTable = "1";
        }
        strCommand += "[" + strTable + "]" + "(";
        for (Int32 i = 0; i < dgv.Columns.Count; i++)
        {
          if (dgv.Columns[i].ValueType.Name == "Integer" |
            dgv.Columns[i].ValueType.Name == "Int32" |
            dgv.Columns[i].ValueType.Name == "Decimal" |
            dgv.Columns[i].ValueType.Name == "Long" |
            dgv.Columns[i].ValueType.Name == "Double" |
            dgv.Columns[i].ValueType.Name == "Short")
          {
            //セルの書式を数値型に設定 
            strCommand += "[" + dgv.Columns[i].HeaderText + "] int ";
          }
          else if (dgv.Columns[i].ValueType.Name == "DateTime")
          {
            //セルの書式を日付型に設定 
            strCommand += "[" + dgv.Columns[i].HeaderText + "] time ";
          }
          else
          {
            strCommand += "[" + dgv.Columns[i].HeaderText + "] " + ExcelDefine.EXCELVARCHAR;
          }
          //最後じゃなければ「,」をつける
          if (i != dgv.Columns.Count - 1)
          {
            strCommand += ",";
          }
        }
        strCommand += ")";
        command.CommandText = strCommand;

        try
        {
          command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
          strErrMessage = "Tableの作成に失敗しました" + Environment.NewLine +
            "コマンド:" + strCommand + " " + Environment.NewLine +
            ex.Message;
          conn.Close();
          return ExcelDefine.EXCEL_TABLE_ERROR;
        }

        //データ入力(パラメータマッチング)
        strCommand = " INSERT INTO " + "[" + strTable + "]" + " VALUES (";
        for (Int32 i = 0; i < dgv.Columns.Count; i++)
        {
          if (dgv.Columns[i].ValueType == null)
          {
            continue;
          }
          strCommand += "?";
          //最後じゃなければ「,」をつける
          if (i != dgv.Columns.Count - 1)
          {
            strCommand += ",";
          }
        }
        strCommand += ")";

        command.CommandText = strCommand;
        //全部出力
        if (bCurr == false)
        {
          foreach (DataGridViewRow dtRow in dgv.Rows)
          {
            if ((iRet = this.DGVSetFunc(
              dgv,
              dtRow,
              ref conn,
              ref command,
              ref strErrMessage
              )) != ExcelDefine.EXCEL_NO_ERROR
              )
            {
              return iRet;
            }
          }
        }
        //選択のみ出力
        else
        {
          DataGridViewSelectedRowCollection dtRows = dgv.SelectedRows;
          foreach (DataGridViewRow dtRow in dtRows)
          {
            if ((iRet = this.DGVSetFunc(
              dgv,
              dtRow,
              ref conn,
              ref command,
              ref strErrMessage
              )) != ExcelDefine.EXCEL_NO_ERROR
              )
            {
              return iRet;
            }
          }
        }
        try
        {
          conn.Close();
        }
        catch (Exception ex)
        {
          strErrMessage = "Excelファイルの終了処理に失敗しました" +
            Environment.NewLine + ex.Message;
          return ExcelDefine.EXCEL_CLOSE_ERROR;
        }

        return iRet;
      }
      catch (Exception ex)
      {
        strErrMessage = "Excelファイルの出力処理に失敗しました" +
          Environment.NewLine + ex.Message;
        conn.Close();
        return ExcelDefine.EXCEL_OTHER_ERROR;
      }
    }

    /// <summary>
    /// プロバイダ選定
    /// </summary>
    /// <param name="strFileName">保存ファイル名</param>
    /// <param name="strExt">拡張子</param>
    /// <param name="strTable">テーブル名またはファイル名</param>
    /// <param name="conn">OleDbConnectionハンドル</param>
    private void ExcelProvider(
      string strFileName,
      ref string strTable,
      ref OleDbConnection conn
      )
    {
      string strExt;

      //ファイルの拡張子チェック
      //拡張子で判断しないとプロバイダが受け付けてくれないため
      strExt = Path.GetExtension(strFileName);
      
      if (strExt == ExcelDefine.EXCEL_EXT_2007)
      {
        conn = new OleDbConnection(
          @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
          strFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");
        strTable = "1";
      }
      else if (strExt == ExcelDefine.EXCEL_EXT_200X)
      {
        conn = new OleDbConnection(
          @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
          strFileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"");
        strTable = "1";
      }
      else
      {
        //csvファイルにするとschema.iniが作成されるが仕様である
        //非常に時間がかかる
        conn = new OleDbConnection(
          @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
          Path.GetDirectoryName(strFileName) +
          ";Extended Properties=\"Text;FMT=Delimited;HDR=Yes\"");
        strTable = Path.GetFileName(strFileName);
      }
    }

    /// <summary>
    /// DataGridViewからExcelファイルに出力
    /// </summary>
    /// <param name="dgv">DataGridView</param>
    /// <param name="dtRow">DataRow</param>
    /// <param name="conn">OleDbConnection</param>
    /// <param name="command">OleDbCommand</param>
    /// <param name="strErrMessage">エラーメッセージ</param>
    /// <returns>エラー値</returns>
    private Int32 DGVSetFunc(
      DataGridView dgv,
      DataGridViewRow dtRow,
      ref OleDbConnection conn,
      ref OleDbCommand command,
      ref string strErrMessage
      )
    {
      command.Parameters.Clear();
      for (Int32 i = 0; i < dgv.Columns.Count; i++)
      {
        //日時の場合
        if (dgv.Columns[i].ValueType.Name == "DateTime")
        {
          DateTime dTime = (DateTime)dtRow.Cells[i].Value;
          command.Parameters.AddWithValue(
            "@" + i.ToString(),
            "time"
            ).Value = dTime.ToString(this.strEXCELTIME);
        }
        //数値の場合
        else if (dgv.Columns[i].ValueType.Name == "Integer" |
          dgv.Columns[i].ValueType.Name == "Int32" |
          dgv.Columns[i].ValueType.Name == "Decimal" |
          dgv.Columns[i].ValueType.Name == "Long" |
          dgv.Columns[i].ValueType.Name == "Double" |
          dgv.Columns[i].ValueType.Name == "Short")
        {
          command.Parameters.AddWithValue("@" + i.ToString(), "int").Value = dtRow.Cells[i].Value;
        }
        //それ以外の場合
        else
        {
          command.Parameters.AddWithValue("@" + i.ToString(), "text").Value = dtRow.Cells[i].Value;
        }
      }

      try
      {
        //コマンド実行
        command.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        strErrMessage = "データ出力に失敗しました" + Environment.NewLine +
          "コマンド:" + command.CommandText + " " +
          Environment.NewLine + ex.Message;
        return ExcelDefine.EXCEL_INSERT_ERROR;
      }
      return ExcelDefine.EXCEL_NO_ERROR;
    }
  }
}

コメント(1)  トラックバック(0) 
共通テーマ:パソコン・インターネット

コメント 1

miris

参考になりました。
ただ、Excel出力する際、項目名の行とデータ行の間が1行あいてしまうのが気になります。
by miris (2011-11-11 15:22) 

コメントを書く

お名前:[必須]
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

※ブログオーナーが承認したコメントのみ表示されます。

トラックバック 0

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。