Asp.net implements the method of importing multiple sheet data in Excel to SQLSERVER,

Asp.net implements the method of importing multiple sheet data in Excel to SQLSERVER,

This example describes how to import multiple sheet data in Excel to SQLSERVER using asp.net. We will share this with you for your reference. The details are as follows:

Public DataSet GetDataSet (string filePath) {string Connstr = string. format ("Provider = Microsoft. jet. OLEDB.4.0; Data Source = '"+ filePath +"'; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1' "); OleDbConnection Conn = new OleDbConnection (Connstr ); // create an ArrayList object to store all the sheetname ArrayList sheetNamelist = new ArrayList (); // obtain the total number of sheets in the configuration Excel file (which is configured according to project requirements) if you want to import all sheet data in an Excel table, delete this code int sheetCount = Conv Ert. toInt32 (ConfigurationManager. appSettings ["sheetCount"]. toString (); DataSet dsExcel = new DataSet (); try {if (Conn. state = ConnectionState. closed) {Conn. open ();} DataTable dtExcelSchema = Conn. getOleDbSchemaTable (OleDbSchemaGuid. tables, new object [] {null, "Table"}); string sheetName = string. empty; if (dtExcelSchema. rows. count> sheetCount) {Page. registerStartupScript (""," <Mce: script type = "text/javascript"> <! -- Alert ('sorry! You cannot upload more than 10 sheet files to an Excel file ..!! ') // --> </Mce: script> "); return;} else {for (int j = 0; j <dtExcelSchema. rows. count; j ++) {sheetName = String. format ("Sheet {0} $", j + 1); sheetNamelist. add (sheetName) ;}} catch (Exception ex) {throw new Exception (ex. message. toString (), ex);} finally {Conn. close ();} try {string strSQL = string. empty; for (int I = 0; I <sheetNamelist. count; I ++) {strSQL = "select * from [" + sheetNamel Ist [I]. toString () + "]"; OleDbDataAdapter da = new OleDbDataAdapter (strSQL, Conn); DataTable dtExcel = new DataTable (sheetNamelist [I]. toString (); da. fill (dtExcel); dsExcel. tables. add (dtExcel);} return dsExcel;} catch (Exception ex) {throw new Exception (ex. message. toString (), ex) ;}// extract data from the Excel table and insert the obtained data into the database. public void InsertData (DataSet ds) {string strSQL = string. empty; if (ds. tables [0 ]. Rows. count> 0) {for (int j = 0; j <ds. tables. count; j ++) {for (int I = 0; I <ds. tables [j]. rows. count; I ++) {DataRow dr = ds. tables [j]. rows [I]; // group name string groupname = dr ["group name"]. toString (). trim (); // contact string contactName = dr ["Contact"]. toString (). trim (); // mobile phone number string mobile = dr ["mobile phone number"]. toString (). trim (); // company name string companyName = dr ["company name"]. toString (). trim (); // public number string officeNum = dr ["office number"]. to String (). trim (); // family number string homeNum = dr ["family number"]. toString (). trim (); // Email string Email = dr ["Mailbox"]. toString (). trim (); // contact address string address = dr ["contact address"]. toString (). trim (); // creation time string createtime = dr ["Creation Time"]. toString (). trim (); // gender string Sex = dr ["gender"]. toString (). trim (); // mobile phone package type string mobileType = dr ["mobile phone package type"]. toString (). trim (); // whether to enable the communication assistant string isOpen = dr ["enable the communication assistant"]. toString (). trim ();/ /SQL statement strSQL = "insert into msm_Excel (groupName, Mobile, Name, companyName, officeNum, homeNum, Emial, address, Createtime, Sex, mobileType, isOpen) values ('"+ groupname +"', '"+ mobile +"', '"+ contactName +"', '"+ companyName + "', '"+ officeNum +"', '"+ homeNum +"', '"+ Email +"', '"+ address +"', '"+ createtime + "', '"+ Sex +"', '"+ mobileType +"', '"+ isOpen +"') "; try {int n = SQLHelper. S QlDataExecute (strSQL); if (n> 0) {Page. RegisterStartupScript ("", "<mce: script type =" text/javascript "> <! -- Alert ('data inserted successfully! ') // --> </Mce: script> "); Label1.Text =" All inserted successfully "+ ds. tables [j]. rows. count. toString () + "data entries";} else {Page. registerStartupScript ("", "<mce: script type =" text/javascript "> <! -- Alert ('server is busy! Please try again later ..! ') // --> </Mce: script> ") ;}} catch (Exception ex) {throw ex ;}}} else {Page. registerStartupScript ("", "<mce: script type =" text/javascript "> <! -- Alert ('the Excel file contains no data !!! ') // --> </Mce: script> ") ;}// call // obtain the Upload File Name string fileName = FileUpload1.FileName; // determine whether an uploaded file exists if (FileUpload1.PostedFile. fileName. length = 0) {Page. registerStartupScript ("", "<mce: script type =" text/javascript "> <! -- Alert ('select the Excel file you want to upload !! ') // --> </Mce: script> ");} // determines whether the uploaded file type is correct. else if (! Path. GetExtension (FileUpload1.PostedFile. FileName). ToLower (). Equals (". xls ")&&! Path. GetExtension (FileUpload1.PostedFile. FileName). ToLower (). Equals (". xlsx") {Page. RegisterStartupScript ("", "<script> alert ('sorry! The file type you uploaded is incorrect! Only Excel files can be uploaded! ') </Script. ");} else {// obtain the uploaded file path filePath = Server. mapPath ("TxtFiles //") + DateTime. now. toString ("yyyyMMddhhmmss") + fileName; this. fileUpload1.PostedFile. saveAs (filePath); ds = GetDataSet (filePath); InsertData (ds );}

I hope this article will help you design your asp.net program.

Articles you may be interested in:

• Asp.net: How to import data from a able to an Excel file and create a table
• Asp.net implements Gradview binding database data and exporting Excel
• How to obtain the content of an Excel table on the asp.net page
• Preview ASP. NET files in Word, Excel, and TXT files online
• How to generate an Excel Table from a Table in asp.net
• How to import EXCEL data to a database in asp.net
• Asp.net + ajax + sqlserver auto-completion full-function for parsing
• Asp.net (c #) implements code for accessing binary images from sqlserver
• Asp.net code (Sqlserver) for fast data insertion)
• How to import file instances to SQLServer2008 under ASP. NET