//获excel中多个sheet中的数据/// <summary>/// 读取导入Excel文件内容/// </summary>/// <param name="fileName">文件路径(上传后)</param>/// <param name="columnString">Excel中的列 名</param>/// <param name="isReadAllExcelSheet">是否读取多个Sheet</param>/// <param name="message">(out)消息提示</param>/// <returns></returns>public DataTable ReadDataFromExcel(string fileName, string columnString, bool isReadAllExcelSheet, out string message){message = "";try{string strCon = "";string fileExt = Path.GetExtension(fileName).ToLower();if (fileExt == ".xls"){strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;}else if (fileExt == ".xlsx"){strCon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;}else{message = "读取失败，非excel文件格式。";return null;}OleDbConnection excelConnection = new OleDbConnection(strCon);excelConnection.Open();#region 获取所有sheet表名称DataTable excelData = new DataTable();DataTable getTableNameData = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取excel中的第一个sheet中的数据//ReadEachExcelSheetData(excelConnection, ((String)getTableNameData.Rows[0]["TABLE_NAME"]).ToString(), columnString, ref excelData);//获取excel中有多个sheet中的数据foreach (DataRow row in getTableNameData.Rows){excelData = ReadEachExcelSheetData2(excelConnection, ((String)row["TABLE_NAME"]).ToString(), columnString);if (excelData.Rows.Count <= 0){break;}}getTableNameData = null;#endregionreturn excelData;}catch (Exception ex){message = "数据文件或者内容格式有严重错误(" + ex.Message + ")，请检查！";return null;}}public void ReadEachExcelSheetData(OleDbConnection excelConnection, string tableName, string columnString, ref DataTable excelData){try{tableName = "[" + tableName + "]";string sql = "";string queryFieldText = string.Empty;if (string.IsNullOrEmpty(queryFieldText)){queryFieldText = "*";}else{foreach (string column in columnString.Split(',')){queryFieldText += "[" + column + "],";}queryFieldText = queryFieldText.Trim(',');}sql = @"SELECT {0}FROM {1}";sql = string.Format(sql, queryFieldText, tableName);DataSet ds = new DataSet();OleDbDataAdapter myAdp = new OleDbDataAdapter(sql, excelConnection);myAdp.Fill(ds, tableName);if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0){excelData.Merge(ds.Tables[0]);}}catch (Exception ex){throw ex;}}//获取excel中第一个sheet中的数据/// <summary>/// 读取导入Excel文件内容/// </summary>/// <param name="fileName">文件路径(上传后)</param>/// <param name="columnString">Excel中的列 名</param>/// <param name="isReadAllExcelSheet">是否读取多个Sheet</param>/// <param name="message">(out)消息提示</param>/// <returns></returns>public DataTable ReadDataFromExcel(string fileName, string columnString, bool isReadAllExcelSheet, out string message){message = "";try{string strCon = "";string fileExt = Path.GetExtension(fileName).ToLower();if (fileExt == ".xls"){strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;}else if (fileExt == ".xlsx"){strCon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;}else{message = "读取失败，非excel文件格式。";return null;}OleDbConnection excelConnection = new OleDbConnection(strCon);excelConnection.Open();#region 获取所有sheet表名称DataTable excelData = new DataTable();DataTable getTableNameData = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取excel中的第一个sheet中的数据ReadEachExcelSheetData(excelConnection, ((String)getTableNameData.Rows[0]["TABLE_NAME"]).ToString(), columnString, ref excelData);//获取excel中有多个sheet中的数据//foreach (DataRow row in getTableNameData.Rows)//{// excelData = ReadEachExcelSheetData2(excelConnection, ((String)row["TABLE_NAME"]).ToString(), columnString);// if (excelData.Rows.Count <= 0)// {// break;// }//}getTableNameData = null;#endregionreturn excelData;}catch (Exception ex){message = "数据文件或者内容格式有严重错误(" + ex.Message + ")，请检查！";return null;}}public DataTable ReadEachExcelSheetData2(OleDbConnection excelConnection, string tableName, string columnString){DataTable excelData = new DataTable();try{tableName = "[" + tableName + "]";string sql = "";string queryFieldText = string.Empty;if (string.IsNullOrEmpty(queryFieldText)){queryFieldText = "*";}else{foreach (string column in columnString.Split(',')){queryFieldText += "[" + column + "],";}queryFieldText = queryFieldText.Trim(',');}sql = @"SELECT {0}FROM {1}";sql = string.Format(sql, queryFieldText, tableName);DataSet ds = new DataSet();OleDbDataAdapter myAdp = new OleDbDataAdapter(sql, excelConnection);myAdp.Fill(ds, tableName);if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0){excelData.Merge(ds.Tables[0]);}}catch (Exception ex){throw ex;}return excelData;}
