#region 读取导入Excel数据
/// <summary>
///
/// </summary>
/// <param name="filename"></param>
/// <param name="fields"></param>
/// <returns></returns>
public DataSet ReadDataFromExcel(string filename, string fields)
{
DataSet ds = new DataSet();
try
{
string cnn = string.Empty;
string ext = Path.GetExtension(filename).ToLower();
if (ext == ".xls")
{
cnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filename + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;
}
else if (ext == ".xlsx")
{
cnn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filename + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;
}
else
{
throw new Exception("读取失败,非excel文件格式。");
}
using (OleDbConnection connection = new OleDbConnection(cnn))
{
connection.Open();
DataTable tables = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in tables.Rows)
{
string table = row["TABLE_NAME"].ToString();
DataTable dt = ReadEachExcelSheetData(connection, table, fields);
if (dt != null)
{
ds.Tables.Add(dt.Copy());
}
}
}
return ds;
}
catch (Exception ex)
{
throw new Exception("数据文件或者内容格式有严重错误(" + ex.Message + "),请检查!");
}
}
public DataTable ReadEachExcelSheetData(OleDbConnection connection, string table, string fields)
{
try
{
string sql = string.Empty;
string query = string.Empty;
if (fields.IndexOf(',') > 0)
{
foreach (string column in fields.Split(','))
{
query += "[" + column + "],";
}
query = query.Trim(',');
}
else
{
query = fields;
}
sql = @"
SELECT
{0}
FROM
[{1}]
";
sql = string.Format(sql, query, table);
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
adapter.Fill(ds, table);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return ds.Tables[0];
}
}
catch (Exception ex)
{
throw ex;
}
return null;
}
private DataSet GetDataFromExcel(string filename,string fields)
{
DataSet ds = new DataSet();
string cnn = string.Empty;
string ext = Path.GetExtension(filename).ToLower();
if (ext == ".xls")
{
cnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filename + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;
}
else if (ext == ".xlsx")
{
cnn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filename + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;
}
else
{
throw new Exception("读取失败,非excel文件格式。");
}
using (OleDbConnection connection = new OleDbConnection(cnn))
{
connection.Open();
DataTable tables = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tableName = tables.Rows[0]["TABLE_NAME"].ToString();
string sql = "select * from [{0}]";
sql = string.Format(sql, tableName);
OleDbCommand command = connection.CreateCommand();
command.CommandText = sql;
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
return ds;
}
}
#endregion
读取Excel中数据
原创
©著作权归作者所有:来自51CTO博客作者好学Ace的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Python使用openpyxl库读取Excel工作表数据实践
Python使用opendpyxl读取Excel工作表实践
Python openpyxl 读取Excel工作表 -
MATLAB读取excel文件中的数据
MATLAB读取excel中的数据读取sheet1中的数据 [num,tx
数据 desktop 读取excel -
用Python读取excel中的数据
198 10208006 宋马小薇 女 医学院 预防医学 医学院 临床医学。200 10207018 祖丽皮亚 女 医学院 制药工程 医学院 临床医学
python excel 开发语言 easy_install 安装方法 -
EasyExcel读取excel中数据
需要导入的jar包 //实体类 //器 //执行类 ...
实体类 jar包 监听器 其他