C#でExcelを編集することのSource

 

１．Excel Object を作成時にExcel　Fileを開けいました。

２．Dataを　書いて　Dataを　読むの方法を作成してしました。

３．RowとColumnについて（Rangeの域）検索するの方法

４．重构

//ExcelHooker.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;

using Excel;
using System.Reflection;

namespace ExcelHooker
...{
    public class Program
    ...{
        private Excel.Application oXL;
        private Excel._Workbook oWB;
        private Excel._Worksheet oSheet;
        private Excel.Range oRng;

        private Object objMissing = System.Reflection.Missing.Value;

        private string path ="C:/Documents and Settings/Administrator/デスクトップ/CreateCodeTool/ExcellHooker/ExcelHooker/ExcelHooker/tmp/";
        private string fileName = "Book1.xls";


        private string a = "A1", b = "A2";


        public Program()
        ...{
            oXL = new Excel.Application();
            oXL.Visible = true;
        }

        private Excel._Workbook OpenFile(string fn)
        ...{

            return (Excel._Workbook)(oXL.Workbooks.Open(path + fileName,objMissing, objMissing, objMissing, objMissing,
                                                                objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing));
        }

        private Excel._Worksheet getSheet(int i, Excel._Workbook oB)
        ...{
            return (Excel._Worksheet)oB.Worksheets.get_Item(i);
        }

        private Excel.Range getRange(string start, string stop, Excel._Worksheet oS)
        ...{
            return oS.get_Range(start, stop);
        }

        public void StartExcel()
        ...{
            string tmpStr;

            try
            ...{
                //Open the Excel file
                System.Console.WriteLine("try to open the excel file!");

                oWB = OpenFile(path + fileName);

                //Get a sheet in the book
                oSheet = getSheet(1, oWB);

                //Get a range of data
                oRng = getRange(a,b, oSheet);

                if (oRng.Value.GetType() == typeof(Object[,]))
                ...{
                    Object[,] objd = (System.Object[,])oRng.Value;
                    tmpStr = objd[1, 1].ToString();
                }
                else
                ...{
                    Object obj = (System.Object)oRng.Value;
                    tmpStr = obj.ToString();
                }

                System.Console.WriteLine(tmpStr);

                oSheet.get_Range("A1", "D1").Font.Bold = true;

                string col = new string("");
                string row = new string("");

                FindData(oRng, tmpStr, col, row);


            }catch(Exception e)...{
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, e.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, e.Source);

                System.Console.WriteLine(errorMessage, "error");
            }
        }

        public void FindData(Excel.Range range, string target, string col, string row)
        ...{
            string position = FindData(range, target);

            string[] str = position.Split('$');

            foreach (string s in str)
            ...{
                System.Console.WriteLine(s);
            }
        }

        public string FindData(Excel.Range range, string target)
        ...{
            return Util.Find(range, target);
        }
    }
}

 

//Util.cs
using System;
using System.Collections.Generic;
using System.Text;

namespace ExcelHooker
...{
    public class Util
    ...{
        public static string Find(Excel.Range range, string target)
        ...{
            Excel.Range currentFind = null;
            Excel.Range firstFind = null;

            Object missing = System.Reflection.Missing.Value;
 
            currentFind = range.Find(target, missing,
                                    Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                                    Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                                    missing);

            while (currentFind != null)
            ...{
                // Keep track of the first range you find. 
                if (firstFind == null)
                ...{
                    firstFind = currentFind;

                    break;
                }

                /**///// If you didn't move to a new range, you are done.
                //else if (currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing)
                //      == firstFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))
                //{
                //    break;
                //}

                //currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                //currentFind.Font.Bold = true;

                //currentFind = this.Fruits.FindNext(currentFind);
            }

            return currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing);

        }
    }
}

 

参照：

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncscol/html/csharp05152003.asp