The code is all pasted out, mainly because the data in the Excel table must match the data type in the database.
Here, the fields in the Excel table are:
Name, gender, class, student ID, initial password
The fields in the SQL Server table tb_users are;
Realname, sex, inclass, question, answer
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="TEST_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
Using system; using system. collections. generic; using system. LINQ; using system. web; using system. web. ui; using system. web. UI. webcontrols; using system. data. oledb; using system. data; using USTC; using system. drawing; public partial class test_default: system. web. UI. page {protected void page_load (Object sender, eventargs e) {} protected void button#click (Object sender, eventargs e) {// obtain the file path string filepa Th = This. fileupload1.postedfile. filename; If (filepath! = "") {If (filepath. contains ("xls") // determines whether the file exists {inputexcel (filepath);} else {response. write ("Please check whether the selected file is an Excel file! Thank you! ") ;}} Else {response. Write (" select the import file before importing it! Thank you! ") ;}} Private void inputexcel (string ppath) {string conn =" provider = Microsoft. jet. oledb.4.0; Data Source = "+ ppath +"; extended properties = 'excel 8.0; HDR = false; IMEX = 1' "; oledbconnection olecon = new oledbconnection (conn); olecon. open (); string SQL = "select * from [sheet1 $]"; oledbdataadapter mycommand = new oledbdataadapter (SQL, olecon); dataset DS = new dataset (); mycommand. fill (DS, "[she ET1 $] "); olecon. close (); int COUNT = Ds. tables ["[sheet1 $]"]. rows. count; For (INT I = 0; I <count; I ++) {string trealname, tsex, tinclass, tquestion, tanswer; trealname = Ds. tables ["[sheet1 $]"]. rows [I] ["name"]. tostring (). trim (); tsex = Ds. tables ["[sheet1 $]"]. rows [I] ["gender"]. tostring (). trim (); tinclass = Ds. tables ["[sheet1 $]"]. rows [I] ["class"]. tostring (). trim (); tquestion = Ds. tables ["[sheet1 $]"]. rows [I] ["Student ID"]. tostring (). trim (); tanswer = Ds. tables ["[sheet1 $]"]. rows [I] ["initial password"]. tostring (). trim (); string excelsql = "insert into tb_users (realname, sex, inclass, question, answer) values ('" + trealname + "', '" + tsex + "', '"+ tinclass +"', '"+ tquestion +"', '"+ tanswer + "')"; try {// import to SQL Server dm = new DM (); dm.exe csql (excelsql); response. write ("<script language = 'javascript '> alert ('data imported successfully! '); Window. location = 'default. aspx '</SCRIPT> ");} catch (exception) {response. write ("<script language = 'javascript '> alert ('data import failed! '); Window. Location = 'default. aspx' </SCRIPT> ");}}}}