Some time ago with ASP.net do a simple game management background, which involves uploading Excel import data function, originally in the local development is good, can have uploaded the server on the tragedy. Server is Aliyun Windows Server 2008 R2 (X64), after troubleshooting the server does not install Office this thing, there will be no OLE DB driver, do not want to install Office, especially Office 2010, looking at the size of the headache, how to do?
Baidu Google found that in fact, as long as the installation of Microsoft Access 2010 Database Engine Redistributable Package to implement the Microsoft Office System file and non-Microsoft Office applications transfer data between, support the existing Microsoft Office files (such as Microsoft Office Access *.mdb and *.accdb) files and Microsoft Office Excel (*.xls, *.xlsx, and *.XLSB) files) to transfer data between other data sources, such as Microsoft SQL Server. also supports establishing connections to existing text files. In addition, ODBC and OLE DB drivers are installed for application developers to use when developing applications that are connected to Office file formats. The installation package download address: http://www.microsoft.com/zh-cn/download/details.aspx?id=13255.
After loading the package, a piece of code is done:
Copy Code code as follows:
<summary>
Connect Excel to read Excel data and return to DataSet data collection
</summary>
<param name= "filepath" >excel server path </param>
<param name= "tablename" >excel table name </param>
<returns></returns>
public static System.Data.DataSet Excelsqlconnection (string filepath, string tablename)
{
String Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' ";
String Strcon = "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 ' ";
OleDbConnection excelconn = new OleDbConnection (Strcon);
Try
{
String strcom = String. Format ("select * from [sheet1$]");
Excelconn.open ();
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, excelconn);
DataSet ds = new DataSet ();
Mycommand.fill (ds, "[" + tablename + "$]");
Excelconn.close ();
return DS;
}
Catch
{
Excelconn.close ();
return null;
}
}
