用连接数据库的方式读取excel

来源：互联网发布：timeline软件使用编辑：程序博客网时间：2024/06/07 03:23

之前喜欢用 hssfworkbook直接将excel读取到workbook中，这样直接对sheet进行解析。可是当数据量很大，数据文件到5M以后c#的内存占用很大，就会很卡了。所以改用这种方法读取到dataset中，再对数据进行解析。直接上代码：

public static DataSet GetExcelTableByOleDB(string strExcelPath)        {            try            {                DataTable dtExcel = new DataTable();                string strExtension = Path.GetExtension(strExcelPath);                OleDbConnection objConn = null;                switch (strExtension)                {                    case ".xls":                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");                        break;                    case ".xlsx":                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");                        break;                    default:                        objConn = null;                        break;                }                if (objConn == null)                {                    return null;                }                objConn.Open();                DataTable sheetNames = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                DataSet ds = new DataSet();                if (sheetNames == null)                    return null;                foreach (DataRow dr in sheetNames.Rows)                {                    string strSql = "select * from [" + dr[2] + "]";                    OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);                    myData.Fill(ds, dr[2].ToString());                }                objConn.Close();                return ds;            }            catch (Exception ex)            {                throw ex;            }        }

阅读全文

0 0