C#读取Excel,或者多个excel表,返回dataset

把excel 表作为一个数据源进行读取

  /// <summary>
/// 读取Excel单个Sheet
/// </summary>
/// <param name="Path">Excel路径</param>
/// <returns>返回dataset</returns>
public DataSet ExcelToDS(string Path)
{
string filename = "sheet1";//可以指定excel sheet
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", Path);
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = string.Format("select * from [{0}$]", filename);
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}

代码

一个excel存在多表读取方法

 /// <summary>
/// 读取Excel多个Sheet
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名称</param>
/// <returns></returns>
public static DataSet ToDataTable(string filePath,string fileName)
{ string connStr = "";
string fileType = System.IO.Path.GetExtension(fileName);
if (string.IsNullOrEmpty(fileType)) return null; if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null; DataSet ds = new DataSet();
try
{
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open(); // 获取数据源的表定义元数据
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
dtSheetName.DefaultView.Sort = "TABLE_NAME";//针对excel进行排序,
// 初始化适配器
da = new OleDbDataAdapter();
for (int i = ; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"]; if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
} da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, SheetName); ds.Tables.Add(dsItem.Tables[].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}

读取Excel多个Sheet

上一篇:PHP读取EXCEL时间


下一篇:js手机端和pc端加载不同的样式