C# excel导入汇总

 #region 导入1（Interop）
        /// <summary>
        /// 导入1（Interop）
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet Interop(string filePath)
        {
            DataSet ds = null;
            DataTable dt = null;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
            Microsoft.Office.Interop.Excel.Sheets sheets = null;
            Microsoft.Office.Interop.Excel.Range range = null;
            object missing = System.Reflection.Missing.Value;
            try
            {
                if (excel == null)
                {
                    return null;
                }
                //打开Excel文件
                workbook = excel.Workbooks.Open(
                filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //获取所有的sheet表
                sheets = workbook.Worksheets;
                ds = new DataSet();
                for (int i = 1; i <= sheets.Count; i++)
                {
                    //获取第一个表
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
                    int rowCount = worksheet.UsedRange.Rows.Count;
                    int colCount = worksheet.UsedRange.Columns.Count;
                    int rowIndex = 1;
                    int colIndex = 1;
                    DataColumn dc;
                    dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    //读取列名
                    for (int j = 0; j < colCount; j++)
                    {
                        range = worksheet.Cells[rowIndex, colIndex + j];
                        dc = new DataColumn();
                        dc.DataType = Type.GetType("System.String");
                        dc.ColumnName = range.Text.ToString().Trim();
                        //添加列
                        dt.Columns.Add(dc);
                    }
                    //读取行数据
                    for (int k = 1; k < rowCount; k++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int l = 0; l < colCount; l++)
                        {
                            range = worksheet.Cells[rowIndex + k, colIndex + l];
                            dr[l] = range.Text.ToString();
                        }
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                workbook.Close();
                excel.Quit();
                Marshal.ReleaseComObject(worksheet);
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(excel);
                worksheet = null;
                workbook = null;
                excel = null;
                GC.Collect();
            }
            return ds;
        }
        #endregion

        #region 导入2(Oledb)
        /// <summary>
        /// 导入2(Oledb)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet Oledb(string filePath)
        {
            DataSet ds = null;
            OleDbConnection conn;
            string strConn = string.Empty;
            string sheetName = string.Empty;

            try
            {
                // Excel 2003 版本连接字符串
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1;'";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch
            {
                // Excel 2007 以上版本连接字符串
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }

            //获取所有的 sheet 表
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            ds = new DataSet();

            for (int i = 0; i < dtSheetName.Rows.Count; i++)
            {
                DataTable dt = new DataTable();
                dt.TableName = "table" + i.ToString();

                //获取表名
                sheetName = dtSheetName.Rows[i]["TABLE_NAME"].ToString();
                OleDbDataAdapter oleda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);

                oleda.Fill(dt);
                ds.Tables.Add(dt);
            }
            //关闭连接，释放资源
            conn.Close();
            conn.Dispose();
            return ds;
        }
        #endregion

        #region 导入3(NPOI)
        /// <summary>
        /// 导入3(NPOI)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet NPOI(string filePath)
        {
            DataSet ds = null;
            try
            {
                FileStream fileStream = new FileStream(filePath, FileMode.Open);
                XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
                ISheet sheet = null;
                IRow row = null;
                ds = new DataSet();
                DataTable dt = null;
                for (int i = 0; i < workbook.Count; i++)
                {
                    dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    //获取sheet表
                    sheet = workbook.GetSheetAt(i);
                    //起始行索引
                    int rowIndex = sheet.FirstRowNum;
                    //获取行数
                    int rowCount = sheet.LastRowNum;
                    //获取第一行
                    IRow firstRow = sheet.GetRow(rowIndex);
                    //起始列索引
                    int colIndex = firstRow.FirstCellNum;
                    //获取列数
                    int colCount = firstRow.LastCellNum;
                    DataColumn dc = null;
                    //获取列数
                    for (int j = colIndex; j < colCount; j++)
                    {
                        dc = new DataColumn(firstRow.GetCell(j).StringCellValue);
                        dt.Columns.Add(dc);
                    }
                    //跳过第一行列名
                    rowIndex++;
                    for (int k = rowIndex; k <= rowCount; k++)
                    {
                        DataRow dr = dt.NewRow();
                        row = sheet.GetRow(k);
                        for (int l = colIndex; l < colCount; l++)
                        {
                            if (row.GetCell(l) == null)
                            {
                                continue;
                            }
                            dr[l] = row.GetCell(l).StringCellValue;

                        }
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                }
                sheet = null;
                workbook = null;
                fileStream.Close();
                fileStream.Dispose();
            }
            catch (Exception ex)
            {
                throw;
            }
            return ds;
        }
        #endregion

        #region 导入4(Aspose)
        /// <summary>
        /// 导入4(Aspose)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet Aspose(string filePath)
        {
            DataSet ds = null;
            try
            {
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath);

                ds = new DataSet();
                DataTable dt = null;
                int rowIndex = 0;
                int colIndex = 0;
                for (int i = 0; i < workbook.Worksheets.Count; i++)
                {
                    dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
                    //获取每个sheet表的所有单元格
                    Aspose.Cells.Cells cells = worksheet.Cells;
                    dt = cells.ExportDataTableAsString(rowIndex, colIndex, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
                    ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return ds;
        }
        #endregion

        #region 导入5(EPPlus)
        /// <summary>
        /// 导入5(EPPlus)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet EPPlus(string filePath)
        {
            DataSet ds = null;
            try
            {
                FileStream fileStream = new FileStream(filePath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite);
                ExcelPackage package = new ExcelPackage(fileStream);
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                ExcelWorksheets worksheets = package.Workbook.Worksheets;
                ExcelWorksheet worksheet = null;
                ds = new DataSet();
                DataTable dt = null;
                for (int i = 0; i < worksheets.Count; i++)
                {
                    dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    worksheet = worksheets[i];
                    int rowCount = worksheet.Dimension.End.Row;
                    int colCount = worksheet.Dimension.End.Column;
                    int rowIndex = worksheet.Dimension.Start.Row;
                    int colIndex = worksheet.Dimension.Start.Column;
                    DataColumn dc = null;
                    for (int j = colIndex; j <= colCount; j++)
                    {
                        dc = new DataColumn(worksheet.Cells[rowIndex, j].Value.ToString());
                        dt.Columns.Add(dc);
                    }
                    rowIndex++;
                    for (int k = rowIndex; k <= rowCount; k++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int l = colIndex; l <= colCount; l++)
                        {
                            if (worksheet.GetValue(k, l) == null)
                            {
                                continue;
                            }
                            dr[l - 1] = worksheet.GetValue(k, l).ToString();
                        }
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                }
                package.Dispose();
                worksheet = null;
                worksheets = null;
                package = null;
                fileStream.Close();
                fileStream.Dispose();
            }
            catch(Exception ex)
            {
                throw;
            }
            return ds;
        }
        #endregion