#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
转载于:https://www.cnblogs.com/zoro-zero/p/4106135.html
相关资源：JAVA上百实例源码以及开源项目
