/// <summary>
/// 将指定Excel文件的内容读取到DataSet中
/// </summary>
public static DataSet ReadXlsxFileForOleDb(string filePath, string excelName, ref string tableName, out string errorString)
{
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataSet ds = null;
try
{
// 初始化连接并打开，framework2.0时需要将平台目标改为x86否则会报错
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
conn = new OleDbConnection(connectionString);
conn.Open();
// 获取数据源的表定义元数据
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
tableName = ExcelMethods.GetTableName(excelName);
List <string> excelSheetNames = new List <string>();
excelSheetNames = ExcelMethods.GetExcelSheetName(tableName, dtSheet);
if (excelSheetNames.Count == 1)
{
if (excelSheetNames.Contains(ExcelTableSetting.ExcelConfigSheetName))
{
errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
return(null);
}
}
else if (excelSheetNames.Count == 0)
{
errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
return(null);
}
ds = new DataSet();
foreach (string sheetName in excelSheetNames)
{
if (sheetName != ExcelTableSetting.ExcelConfigSheetName)
{
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(String.Format("Select * FROM [{0}]", sheetName), conn);
da.Fill(ds, sheetName);
// 删除表格末尾的空行
DataRowCollection rows = ds.Tables[sheetName].Rows;
int rowCount = rows.Count;
for (int i = rowCount - 1; i >= ExcelTableSetting.DataFieldDataStartRowIndex; --i)
{
if (string.IsNullOrEmpty(rows[i][0].ToString()))
{
rows.RemoveAt(i);
}
else
{
break;
}
}
}
else
{
//da.Dispose();
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(String.Format("Select * FROM [{0}]", ExcelTableSetting.ExcelConfigSheetName), conn);
da.Fill(ds, ExcelTableSetting.ExcelConfigSheetName);
}
}
}
catch (Exception e)
{
errorString = "错误：连接Excel失败，你可能尚未安装Office数据连接组件: http://www.microsoft.com/en-US/download/details.aspx?id=23734 \n";
tableName = null;
return(null);
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
// 由于C#运行机制，即便因为表格中没有Sheet名为data的工作簿而return null，也会继续执行finally，而此时da为空，故需要进行判断处理
if (da != null)
{
da.Dispose();
}
conn.Dispose();
}
}
errorString = null;
return(ds);
}