<%@ Page autoeventwireup= "true" codefile= "IntoExcel.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 ">
var k=//s+/. [xls]/;
if (!k.test (document.getElementById) ("Fileid"). Value)
<asp:button id= "Button1" runat= "server" text= "upload" onclientclick= "return Check ()" onclick= "Button1_Click"/>< /p>
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>
Uploading files
</summary>
<param name= "Sender" ></param>
<param name= "E" ></param>
protected void Button1_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 manipulation
</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>
Data set operations
</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 ()//using Transaction 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.jb51.net" title= "sql" target= "_blank" >sql</a></strong> STR = "INSERT into Intoexcel (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 ("Upload success" + k + "bar");
File.delete (FilePath);
}
}
<summary>
File actions
</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 Drive
Data source= "+ Savepath +"; Database address
Extended properties= ' Excel 8.0; The connection is Excel8.0.
hdr=yes;//has two values: yes/no, these 2 values, said you can directly read the column name, NO, can only read subscript
imex=1;//to solve the problem when mixing numbers and characters, and identifying abnormal situations.
This is not the best way to read the database, you should use Office components
SELECT * FROM [sheet1$]//reference the contents of the Sheet1 worksheet in the Excle file
SQL controls are dedicated drivers that enable efficient access to SQL Server databases
SqlConnection can access only SQL Server, while OleDbConnection has access to all databases.
SQL Server is faster than OLE DB if you are only accessing SQL Servers.
I hope this article will help you with the ASP.net program design.
