Background code:
Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. Web;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. oledb;
Using system. IO;
Public partial class Excel: system. Web. UI. Page
{
Int COUNT = 0;
Int counts = 0;
Protected void page_load (Object sender, eventargs E)
{
}
Protected void btnexcel_click (Object sender, eventargs E)
{
String filepath = "";
// Directoryinfo mydir = new directoryinfo (@ "F: \ excel ");
Directoryinfo mydir = new directoryinfo (server. mappath ("file /"));
Fileinfo [] files = mydir. getfiles ();
For (INT I = 0; I <files. length; I ++)
{
// Filepath = "F: \ Excel \" + files [I]. tostring ();
Filepath = server. mappath ("file/" + files [I]. tostring ());
// String xlsname = files [I]. tostring (). Replace (". xls ","");
String connstring03 = "provider = Microsoft. jet. oledb.4.0; Data Source = "+ filepath +"; extended properties = 'excel 8.0; HDR = no; IMEX = 1; '"; // HDR = No or yes: whether to enter the title (such as name, gender, =) IMEX = 1: Indicates whether to forcibly convert to text
// String connstring07 = "provider = Microsoft. Ace. oledb.12.0; Data Source =" + filepath + "; extended properties = Excel 12.0; HDR = yes; IMEX = 1 ;'";
Oledbconnection excelconn = new oledbconnection (connstring03 );
Excelconn. open ();
String excelsel = "select * from [sheet1 $]";
Oledbcommand excelcmd = new oledbcommand (excelsel, excelconn );
Oledbdataadapter excelda = new oledbdataadapter ();
Excelda. selectcommand = excelcmd;
Dataset excelds = new dataset ();
Excelda. Fill (excelds );
Datatable dt = excelds. Tables [0];
Dataview myview = new dataview (DT );
Try
{
Foreach (datarowview mydrv in myview)
// For (Int J = 0; j <excelds. Tables [0]. Rows. Count; j ++)
{
Count ++;
String pro_excel = "excel_insert ";
Sqlparameter [] Param = {New sqlparameter ("@ name", sqldbtype. nvarchar ),
New sqlparameter ("@ Info", sqldbtype. nvarchar ),
New sqlparameter ("@ address", sqldbtype. nvarchar)
};
// Param [0]. value = excelds. Tables [0]. Rows [J] [0]. tostring (). Trim ();
// Param [1]. value = excelds. Tables [0]. Rows [J] [1]. tostring (). Trim ();
// Param [2]. value = excelds. Tables [0]. Rows [J] [2]. tostring (). Trim ();
Param [0]. value = mydrv [0]. tostring (). Trim ();
Param [1]. value = mydrv [1]. tostring (). Trim ();
Param [2]. value = mydrv [2]. tostring (). Trim ();
Counts = sqlhelper. executenonquery (sqlhelper. Conn, commandtype. storedprocedure, pro_excel, Param );
}
If (counts> 0)
{
Clientscript. registerstartupscript (this. GetType (), "", "<SCRIPT> alert ('imported successfully') </SCRIPT> ");
}
Else
{
Clientscript. registerstartupscript (this. GetType (), "", "<SCRIPT> alert ('Operation failed') </SCRIPT> ");
}
}
Catch
{
Clientscript. registerstartupscript (this. getType (), "", "<SCRIPT> alert ('nth" + count. tostring () + "data error") </SCRIPT> ");
Excelconn. Close ();
}
Excelconn. Close ();
}
}
}
// Front-end
<% @ Page Language = "C #" autoeventwireup = "true" codefile = "Excel. aspx. cs" inherits = "Excel" %>
<% @ Register src = "webusercontrol. ascx" tagname = "webusercontrol" tagprefix = "uc1" %>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Uc1: webusercontrol id = "webusercontrol1" runat = "server"/> <asp: button id = "btnexcel" runat = "server" text = "Import excel" onclick = "btnexcel_click"/>
</Div>
</Form>
</Body>
</Html>
// Stored Procedure
Create proc excel_insert
(
@ Name nvarchar (50 ),
@ Info nvarchar (50 ),
@ Address nvarchar (50)
)
As
Insert into Excel (name, info, address) values (@ name, @ info, @ address)
Add a sentence for Import
Insert into projhinfo_bas select * From OpenRowSet ('Microsoft. Ace. oledb.122.16', 'excel 5.0; HDR = yes; database = "+
URL + "', sheet1 $)
The URL is an Excel address. Note: The data table column is the same as the Excel column.