應用c#從Excel讀取資料

 //初始化對象

Application app = null;

            Workbook MyBook = null;

            Worksheet mySheet = null;

            string sPath = ConfigurationManager.AppSettings["ExportTemp"].ToString();
            string sMsg = "";
            try
            {
                sPath += Path.GetFileName(file.FileName);

                try
                {
                    file.SaveAs(sPath);

                    ModelSurveyCommon oModel = new ModelSurveyCommon();

//建立對象

                    app = new Application();
                    MyBook = app.Workbooks.Open(sPath, 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);
                    mySheet = (Worksheet)MyBook.Worksheets[1];
                    Range cellB;
                    string sValue, aTemp = "";
                    cellB = (Range)mySheet.Cells[1, 1];//取得儲存格[1，1]的資料

                    if (cellB.Value2.ToString() == "SURVEY_COMMON")
                    {
                        for (int j = 1; j < mySheet.UsedRange.Columns.Count + 1; j++)
                        {
                            cellB = (Range)mySheet.Cells[2, j];
                            if (cellB.Value2 != "" && cellB.Value2 != null)
                            {
                                aTemp = aTemp + cellB.Value2.ToString() + "#^$@@";
                            }
                            else
                            {
                                aTemp = aTemp + "#^$@@";
                            }
                        }
                        string[] sFieldName = Common.Common.SplitString(aTemp, "#^$@@");
                        //Response.Write("aTemp:" + aTemp + "<br>");
                        for (int i = 3; i < mySheet.UsedRange.Rows.Count + 1; i++)
                        {
                            sValue = "";
                            for (int j = 1; j < mySheet.UsedRange.Columns.Count + 1; j++)
                            {
                                cellB = (Range)mySheet.Cells[i, j];
                                if (cellB.Value2 != "" && cellB.Value2 != null)
                                {
                                    sValue = sValue + cellB.Value2.ToString() + "#^$@@";
                                }
                                else
                                {
                                    sValue = sValue + "#^$@@";
                                }
                            }
                            //Response.Write("sValue:" + sValue);
                            //Response.Write("sValue:" + sValue + "<br>");
                            string[] sFiledValue = Common.Common.SplitString(sValue, "#^$@@");

                            oModel.ImportSurvey(sFieldName, sFiledValue);
                        }
                        sMsg = "Import Successfully.";
                    }
                    else
                    {
                        sMsg = "Table incorrect.";
                    }

                }
                catch
                {
                    sMsg = "Import data error.";
                }
            }
            catch
            {
                sMsg = "Please choose file.";
            }

 

//清Null 物件

            if (mySheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                mySheet = null;
            }
            if (MyBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
                MyBook = null;
            }

            //關閉對象
            app.Workbooks.Close();
            app.Quit();

            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
            GC.Collect(); //記憶體回收

 

 

 

 

利用二組數組匯入資料到EXCEL

 string sPath =@"C:/Inetpub/wwwroot/test_20110222/test/Report/";
            string sFileName = "test_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            sPath += sFileName;

            System.IO.File.Copy(@"C:/Inetpub/wwwroot/test_20110222/test/Report/test.xls", sPath, true);

            Application app = null;
            Workbook MyBook = null;
            Worksheet mySheet = null;
            app = new Application();
            MyBook = app.Workbooks.Open(sPath, 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);
            mySheet = (Worksheet)MyBook.Worksheets[1];
            int row = 10000, colCount =30;
            string[,] ss = new string[row, colCount];

            for (int j = 1; j < row; j++)
            {
                for (int k = 1; k < colCount; k++)
                {
                    ss[j, k] = j+":"+k;
                   // mySheet.Cells[j, k] = j + ":" + k;
                }
            }  

            mySheet.get_Range("A2", "Z10000").Value2 = ss;
           
            MyBook.Save();

            if (mySheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
                mySheet = null;
            }
            if (MyBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
                MyBook = null;
            }
            //關閉對象
            app.Workbooks.Close();
            app.Quit();

            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }
            GC.Collect();
            GC.WaitForPendingFinalizers();