C#读取Excel文件

1. OleDb方式 [using System.Data.OleDb]

    Office 2007及以上版本需要使用"Microsoft.ACE.OLEDB.12.0" 的连接字符串，Office 2003及以下版本需要使用"Microsoft.Jet.OLEDB.4.0"。

public IList<ReportItem> LoadExcelFile(string excelFileName)
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";            //Sql statement
            //string strExcel = string.Format("select * from [{0}$]", strSheetName);
            string query = "select * from   [sheet1$]";

            List<ReportItem> reportList = new List<ReportItem>();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                OleDbCommand myCommand = new OleDbCommand(query, conn);
                conn.Open();
                OleDbDataReader rdr = myCommand.ExecuteReader();
                // Always call Read before accessing data.
                while (rdr.Read())
                {
                    ReportItem item = new ReportItem()
                    {
                        LFileName = rdr.GetString(0),
                        RFileName = rdr.GetString(1),
                        ResID = rdr.GetString(2),
                        CoID = rdr.GetString(3),
                        SouString = rdr.IsDBNull(4) == true ? string.Empty : rdr.GetString(4),
                        TaString = rdr.IsDBNull(5) == true ? string.Empty : rdr.GetString(5),
                        Cmts = rdr.GetString(6),
                        Cul = rdr.GetString(7),
                        TarCul = rdr.GetString(7),
                        ModifiedDate = rdr.IsDBNull(8) == true ? null : rdr.GetDateTime(8).ToShortDateString(),
                        Rul = rdr.GetString(9),
                        Mes = rdr.GetString(10),
                        };
                    reportList.Add(item);
                }
                rdr.Close();
            }
            return reportList;
        }

2. Microsoft.Office.Interop.Excel

public IList<ReportItem> LoadExcelFile(string excelFileName)
        {
            List<ReportItem> reportList = new List<ReportItem>();
            string RunDateTime = DateTime.UtcNow.ToLongDateString();
            Excel.Application excel = null;
            Excel.Workbooks wbs = null;
            Excel.Workbook wb = null;
            Excel.Worksheet ws = null;
            Excel.Range range1 = null;

            try
            {
                excel = new Excel.Application();
                //excel.UserControl = true;
                excel.DisplayAlerts = false;

                wb = excel.Application.Workbooks.Open(excelFileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                ws = wb.Worksheets["Sheet1"] as Excel.Worksheet;

                int rowCount = ws.UsedRange.Cells.Rows.Count;
                //int colCount = ws.UsedRange.Cells.Columns.Count;

                for (int i = 2; i <= rowCount; i++)
                {
                    range1 = ws.Cells.get_Range("A" + i.ToString(), "K" + i.ToString());
                    ReportItem item = new ReportItem()
                    {
                        LFileName = ((Excel.Range)range1.Cells[1, 1]).Value.ToString(),
                        RFileName = ((Excel.Range)range1.Cells[1, 2]).Value.ToString(),
                        ResID = ((Excel.Range)range1.Cells[1, 3]).Value.ToString(),
                        ComResID = ((Excel.Range)range1.Cells[1, 4]).Value.ToString(),
                        SouString = ((Excel.Range)range1.Cells[1, 5]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 5]).Value.ToString(),
                        TarString = ((Excel.Range)range1.Cells[1, 6]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 6]).Value.ToString(),
                        Cos = ((Excel.Range)range1.Cells[1, 7]).Value == null ? string.Empty : ((Excel.Range)range1.Cells[1, 7]).Value.ToString(),
                        Cul = ((Excel.Range)range1.Cells[1, 8]).Value.ToString(),
                        TarCulture = ((Excel.Range)range1.Cells[1, 8]).Value.ToString(),
                        ModifiedDate = ((Excel.Range)range1.Cells[1, 9]).Value.ToString(),
                        Ru = ((Excel.Range)range1.Cells[1, 10]).Value.ToString(),
                        Mes = ((Excel.Range)range1.Cells[1, 11]).Value.ToString(),
                                            };
                    reportList.Add(item);
                }
                return reportList;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (excel != null)
                {
                    if (wbs != null)
                    {
                        if (wb != null)
                        {
                            if (ws != null)
                            {
                                if (range1 != null)
                                {
                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
                                    range1 = null;
                                }
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                                ws = null;
                            }
                            wb.Close(false, Nothing, Nothing);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
                            wb = null;
                        }
                        wbs.Close();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
                        wbs = null;
                    }
                    excel.Application.Workbooks.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    excel = null;
                    GC.Collect();
                }
            }
        }