private DataSet ExcelToDS(string fileName)
{
fileName = DocPath + fileName; //绝对路径
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
ArrayList tblNames = new ArrayList();
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in tblSchema.Rows)
{
tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
}
// 初始化适配器
da = new OleDbDataAdapter();
// 准备数据,导入DataSet
DataSet ds = new DataSet();
foreach (string tblName in tblNames)
{
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
try
{
da.Fill(ds, tblName);
}
catch
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
throw;
}
}
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return ds;
}
public string ExcelReader(string filename)
{
int i, j,row,columns;
DataSet ds = ExcelToDS(filename);
StringBuilder sb = new StringBuilder();
foreach (DataTable dt in ds.Tables)
{
columns = dt.Columns.Count;
row = dt.Rows.Count;
for (i = 0; i < row; i++)
{
DataRow dr = dt.Rows[i];
for (j = 0; j < columns; j++)
{
sb.Append(dr.ItemArray[j].ToString());
}
sb.Append("/r/n");
}
}
return sb.ToString();
}