How to import EXCEL data to the database in asp.net, asp.net
This document describes how to import EXCEL data to a database in asp.net. Share it with you for your reference. The specific analysis is as follows:
Excel is a very commonly used office form in the office, but we usually need to directly import the excel data into the database during development, here is an example of importing EXCEL data to a database in asp.net for your reference.
Note: The first row in EXCEL cannot be imported.
The following is the source code: IntoExcel. aspx:
Copy codeThe Code is as follows: <% @ Page AutoEventWireup = "true" CodeFile = "Excel. aspx. cs" Inherits = "study_IntoExcel" %>
<! 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 id = "Head1" runat = "server">
<Title> No title page </title>
<Script language = "javascript" type = "text/javascript"> <! --
// <! CDATA [
Function check (){
Var k = // S +/. [xls]/;
If (! K. test (document. getElementById ("fileId"). value ))
{
Alert ("only files in xls format last time ");
Return false;
}
Return true;
}
// --> </Script>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<P>
<Asp: FileUpload ID = "fileId" runat = "server"/>
<Asp: Button ID = "Button1" runat = "server" Text = "Upload" OnClientClick = "return check ()" onclick = "button#click"/> </p>
</Div>
</Form>
</Body>
</Html>
Excel. aspx. cs
Copy codeThe Code is as follows: using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Web;
Using System. Web. UI;
Using System. Collections;
Using System. Configuration;
Using System. Data;
Using System. Web. Security;
Using System. Web. UI. HtmlControls;
Using System. Web. UI. WebControls. WebParts;
Using System. IO;
Using System. Data. OleDb;
Using System. Data. SqlClient;
Using System. Web. UI. WebControls;
Public partial class study_IntoExcel: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
}
/// <Summary>
/// Upload a file
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void button#click (object sender, EventArgs e)
{
String fileName = fileId. FileName;
String savePath = Server. MapPath ("~ /File /");
FileOperatpr (fileName, savePath );
FileId. SaveAs (savePath + fileName );
DataOperator (fileName, savePath );
}
/// <Summary>
/// Data Operations
/// </Summary>
/// <Param name = "fileName"> </param>
/// <Param name = "savePath"> </param>
Private void DataOperator (string fileName, string savePath)
{
String myString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + savePath + fileName + "; Extended Properties = Excel 8.0 ";
OleDbConnection oconn = new OleDbConnection (myString );
Oconn. Open ();
DataSet ds = new DataSet ();
OleDbDataAdapter oda = new OleDbDataAdapter ("select * from [Sheet1 $]", oconn );
Oda. Fill (ds );
Oconn. Close ();
DataSetOperator (ds, savePath + fileName );
}
/// <Summary>
/// Dataset operation
/// </Summary>
/// <Param name = "ds"> </param>
Private void DataSetOperator (DataSet ds, string filePath)
{
SqlConnection conn = new SqlConnection ("Data Source = SONYSVR; Initial Catalog = IAR_Factory_811; User ID = sa; Password = P @ ssword ");
Conn. Open ();
SqlTransaction str = conn. BeginTransaction (); // use transaction processing to prevent interruption
Int k = 0;
If (ds. Tables [0]. Rows. Count <1)
{
Response. Write ("<script> alert ('No data! ') </Script> ");
Return;
}
Try
{
For (int I = 0; I <ds. Tables [0]. Rows. Count; I ++)
{
String <strong> <a href = "http://www.bkjia.com" title = "SQL" target = "_ blank"> SQL </a> </strong> Str = "insert into Excel (Tname, tage, Taddress) values ";
SqlStr + = "('" + ds. Tables [0]. Rows [I] [0]. ToString () + "',";
SqlStr + = ds. Tables [0]. Rows [I] [1]. ToString () + ",";
SqlStr + = "'" + ds. Tables [0]. Rows [I] [2]. ToString () + "')";
SqlCommand cmd = new SqlCommand (sqlStr, conn, str );
Cmd. Transaction = str;
K + = cmd. ExecuteNonQuery ();
}
Str. Commit ();
}
Catch (Exception ex)
{
Response. Write ("exception occurred, data has been rolled back/n information/n" + ex. Message );
Str. Rollback ();
}
Finally
{
Response. Write ("uploaded successfully" + k + "items ");
File. Delete (filePath );
}
}
/// <Summary>
/// File Operations
/// </Summary>
/// <Param name = "fileName"> </param>
/// <Param name = "savePath"> </param>
Private void FileOperatpr (string fileName, string savePath)
{
If (! Directory. Exists (savePath ))
{
Directory. CreateDirectory (savePath );
}
If (File. Exists (savePath + fileName ))
{
File. Delete (savePath + fileName );
}
}
}
Provider = Microsoft. Jet. OLEDB.4.0; Data Source = "+ savePath +"; Extended Properties = 'excel 8.0; HDR = YES
Provider = Microsoft. Jet. OLEDB.4.0; // connection driver
Data Source = "+ savePath +"; // database address
Extended Properties = 'excel 8.0; // The connection is Excel8.0
HDR = YES; // There are two values: YES/NO. The two values indicate whether you can directly read the column name. NO, you can only read the subscript.
IMEX = 1; // solves the problem of abnormal identification when numbers and characters are mixed.
This is not the best way to read data into the database. The office component should be used.
Select * from [Sheet1 $] // reference the content of the sheet 1 worksheet in the EXCLE File
The OleDB control uses the OleDb driver to access various databases.
Fields in the database:
Copy codeThe Code is as follows: create table Excel
(
Tid int identity (1, 1) primary key,
Tname varchar (50 ),
Tage int,
Taddress varchar (200 ),
)
The SQL control uses a dedicated driver to efficiently access the SQL Server database.
SQLConnection can only access SQL Server, while OleDbConnection can access all databases.
If you only access SQL Server, SQL is faster than OleDb.
I hope this article will help you design your asp.net program.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
