C#怎么读取Excel中的数据啊求大神指导
我想把Excel中的数据读出来就简单的显示出来就行 就是不知道怎么读的 最好能给出源码 谢谢！ c#
[解决办法]
这样的话，你的设置ODBC啊。控制面板-》管理工具-》ODBC。
[解决办法]
在程序里引用Microsoft.Office.Interop.Excel..
public static DataSet LoadDataFromExcel()
{
try
{
var ofd = new OpenFileDialog()
{
Filter = "Microsoft Office Excel 工作簿(*.xls)
[解决办法]
*.xls",
Multiselect = false
};
if (ofd.ShowDialog() == DialogResult.Cancel) return null;
string filePath = ofd.FileName;
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet1");
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
GetExcelDataTable(null, excelFileName, null);
public static DataTable GetExcelDataTable(string workSheetName, string excelFileName, List<string> columnsToRead)
{
DbConnection dbconn = BuildConnection(excelFileName);
return GetDataTable(BuildSelectSqlForWorkSheet(dbconn, workSheetName, columnsToRead), dbconn, new SqlParameter[0]);
}
private static DbConnection BuildConnection(string excelFileName, bool includeHeader, bool writemode)
{
DbConnection connection;
string str = "";
try
{
FileInfo info = new FileInfo(excelFileName);
string connectionString = string.Empty;
string str3 = string.Empty;
if (includeHeader)
{
str3 = "HDR=YES;";
}
if (!writemode)
{
str3 = str3 + "IMEX=1;";
}
if (string.Compare(info.Extension.ToLower(), ".xls", true) == 0)
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0;{1}"", excelFileName, str3);
}
else
{
if (string.Compare(info.Extension.ToLower(), ".xlsx", true) != 0)
{
throw new Exception(string.Format("Input FileType [{0}] is NOT Supported", info.Extension));
}
if (writemode)
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0{2};{1}"", excelFileName, str3, " Xml");
}
else
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0{2};{1}"", excelFileName, str3, string.Empty);
}
}
connection = new OleDbConnection(connectionString);
}
catch (Exception exception)
{
throw new Exception(exception.Message + "\r\n" + str, exception);
}
return connection;
}
public static DataTable GetDataTable(string selectSql, string tableName, bool isStoredProcedure, DbConnection dbconn, params SqlParameter[] sqlParameters)
{
DataTable table2;
string str = "";
try
{
using (DbCommand command = CreateDbCommand(selectSql, isStoredProcedure, dbconn, sqlParameters))
{
DataTable table = new DataTable(tableName);
dbconn.Open();
DbDataReader reader = command.ExecuteReader();
table.Load(reader);
reader.Close();
table2 = table;
}
}
catch (Exception exception)
{
throw new Exception(str + "\r\nSql: " + selectSql + "\r\n" + exception.Message, exception);
}
finally
{
dbconn.Close();
dbconn.Dispose();
}
return table2;
}
