健巳創作

.net Framework 3.xでエクセルを扱う方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using myExcel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace WindowsFormsApplication1 {
    public class excel {
        protected List _header;
        protected List> node;
        protected bool onLoad;

        ///

        /// データの有無を取得
        /// 

        public bool exists {
            get { return onLoad; }
        }

        ///

        /// データの件数取得
        /// 

        public int data_num {
            get { return node.Count; }
        }

        ///

        /// ヘッダ情報取得
        /// 

        public List header {
            get { return _header; }
        }

        ///

        /// データの取得
        /// 

        public List> data {
            get { return node; }
        }

        public enum readType:int{
            all = 0,
            brand,
            items
        }
        protected readType dataType;

        public excel(readType type) {
            _header = new List();
            node = new List>();
            onLoad = false;
            dataType = type;
        }

        ///

        /// エクセルファイルからデータを取得する
        /// 

        ///
ファイル名
        public void loadExcel(string name) {
            myExcel.ApplicationClass app = new myExcel.ApplicationClass();

            //非表示設定
            app.Visible = false;
            app.DisplayAlerts = false;

            //ブック取得
            myExcel.Workbook book = app.Workbooks.Open(name,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //シート取得
            myExcel.Worksheet sheet = (myExcel.Worksheet)book.Sheets[1];
            sheet.Activate();

            ReadExcel(sheet);
            //保存
            //book.Save();

            //保存したことにする
            book.Saved = true;

            //デストラクタ処理
            Marshal.ReleaseComObject(sheet);
            sheet = null;

            book.Close(Type.Missing, Type.Missing, Type.Missing);
            Marshal.ReleaseComObject(book);
            book = null;

            app.Quit();
            Marshal.ReleaseComObject(app);
            app = null;

            onLoad = true;
        }

        ///

        /// データの読み出し
        /// 

        ///
アクティブなシート
        private void ReadExcel(myExcel.Worksheet sheet) {
            myExcel.Range range;

            //ヘッダ読み出し
            int col = 1;
            range = (myExcel.Range)sheet.Cells[1, col];
            while (range.Text.ToString().Length > 0) {
                _header.Add(range.Text.ToString());
                col++;
                range = (myExcel.Range)sheet.Cells[1, col];
            }

            //データ読み出し
            int len = _header.Count;
            int row = 1;
            col = 0;
            while (row < 1000) {
                row++;
                range = (myExcel.Range)sheet.Cells[row, 2];
                string tmp = (string)range.Text;
                if (tmp.Length == 0) break;
                List p = new List();
                for (col = 1; col <= len; col++) {
                    range = (myExcel.Range)sheet.Cells[row, col];
                    tmp = (string)range.Text;
                    p.Add(tmp);
                }
                node.Add(p);
            }
        }
    }
}