c#获取excel数据

namespace EnergyAnalyseBaseDataCreate
{
    public class ExcelOperator
    {
        public ExcelOperator()
        {

        }
        /// <summary>
        /// excel读取
        /// </summary>
        /// <param name="strXLSPath">path</param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataSet LoadDataSetFromXLS(string strXLSPath,string sheetName)
        {
            DataSet ds = null;

            if (File.Exists(strXLSPath))
            {
                try
                {
                    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strXLSPath + ";" + "Extended Properties='Excel 12.0;HDR=YES\'";

                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();

                    OleDbDataAdapter myCommand = null;
                    string strExcel = "select * from [" + sheetName + "$]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);

                    ds = new DataSet();
                    myCommand.Fill(ds);

                    conn.Close();
                    conn = null;
                }
                catch (System.Exception e)
                {
                    Writelog.WriteLogs(e.Message);
                    return null;
                }
            }

            return ds;
        }

        /// <summary>
        /// 读取excel所有的sheet数据
        /// </summary>
        /// <param name="strXLSPath">excelpath</param>
        /// <returns></returns>
        public static DataSet LoadDataSetFromXLS(string strXLSPath)
        {
            DataSet ds = null;

            if (File.Exists(strXLSPath))
            {
                try
                {
                    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strXLSPath + ";" + "Extended Properties='Excel 12.0;HDR=YES\'";

                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = string.Empty;
                    OleDbDataAdapter myCommand = null;
                    DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                    string[] strTableNames = new string[dtSheetName.Rows.Count];
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {
                        strTableNames[k] = dtSheetName.Rows[dtSheetName.Rows.Count - k - 1]["TABLE_NAME"].ToString();
                    }

                    ds = new DataSet();

                    for (int i = 0; i < strTableNames.Length; i++)
                    {
                        strExcel = "select * from [" + strTableNames[i] + "]";
                        myCommand = new OleDbDataAdapter(strExcel, strConn);
                        myCommand.Fill(ds, "table" + i);

                    }
                    conn.Close();
                    conn = null;
                }
                catch (System.Exception e)
                {
                    Writelog.WriteLogs(e.Message);
                    return null;
                }
            }

            return ds;
        }

        public static int SaveDataSetToXLS(DataSet ds, string strXLSPath)
        {
            if (null == ds || null == strXLSPath || "" == strXLSPath)
                throw new Exception("[ExcelOperator] SaveDataSetToXLS@data error");

            Excel.Application excel = null;
            Excel.Workbook xBook = null;
            int count = 0;
            try
            {
                excel = new Excel.Application();
                xBook = excel.Application.Workbooks.Add(Missing.Value);

               DataTable dt = ds.Tables[0];
                // save the columns name
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                }

                // save the rows
                for (int i = 0; i < dt.Rows.Count; i++, count++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
                    }
                }

                xBook.SaveAs(strXLSPath,
                  Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                  Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
                  Missing.Value, Missing.Value);
            }
            catch
            {

            }
            finally
            {
                xBook.Close(false, null, null);
                excel.Application.Workbooks.Close();
                excel.Application.Quit();
                excel.Quit();

                xBook = null;
                excel = null;
                System.GC.Collect();
            }

            return count;
        }
    }
}