Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how to achieve Excel, notepad data import into the database", in the daily operation, I believe that many people have doubts on how to achieve Excel, notepad data import into the database, the editor consulted all kinds of information, sorted out a simple and easy-to-use method of operation, hope to answer "how to achieve Excel, notepad data import to the database" doubt helpful! Next, please follow the editor to study!

Foreground code:

The copy code is as follows:

Select a file

File example

Background code

The copy code is as follows:

/ / upload on Excel

Protected void btnExcel_Click (object sender, EventArgs e)

{

String backStr= UploadFile (FileUpload1, "Excel", 1)

If (backStr = "- 1")

{

/ / the return message is put here, which is changed to the corresponding way to put the message, and the class Js will not be uploaded.

Js.ShowSureMsgBox (this.Page, "Please select an Excel file to submit")

Return

}

Else if (backStr = "- 2")

{

Js.ShowSureMsgBox (this.Page, "Please select .xls or .xlsx type file")

Return

}

Else

{

String url = backStr; / / absolute path

DataTable dt = ExcelToDataSet (url)

If (dt.Rows.Count > 0)

{

For (int I = 0; I

< dt.Rows.Count; i++) { //读取每行数据 string phoneNum= dt.Rows[i][0].ToString(); } } else { Js.ShowSureMsgBox(this.Page, "文件内容为空"); } File.Delete(url); //删除上传的文件 } } //记事本上传 protected void btnNotepad_Click(object sender, EventArgs e) { string backStr = UploadFile(FileUpload2, "TXT",2); if (backStr == "-1") { Js.ShowSureMsgBox(this.Page, "请选择要提交的TXT文件"); return; } else if (backStr == "-2") { Js.ShowSureMsgBox(this.Page, "请选择.txt类型文件"); return; } else { string url = backStr; //绝对路径 DataTable dt = ReadTXT(url); if (dt.Rows.Count >

0)

{

For (int I = 0; I < dt.Rows.Count; iTunes +)

{

/ / read each row of data

String phoneNum= dt.Rows [I] [0] .ToString ()

}

}

Else

{

Js.ShowSureMsgBox (this.Page, "File content is empty")

}

File.Delete (url); / / Delete uploaded files

}

}

/ / upload files

Public string UploadFile (FileUpload FileUploadName, string varfilename,int type)

{

If (FileUploadName.HasFile) / / determine whether a file has been uploaded

{

String fileExtension = System.IO.Path.GetExtension (FileUploadName.FileName) .ToLower (); / / get the suffix name of the file

If (type = = 1)

{

If (fileExtension! = ".xls" & & fileExtension! = ".xlsx")

{

Return "- 2"

}

}

If (type = = 2)

{

If (fileExtension! = ".txt")

{

Return "- 2"

}

}

String fpath = System.Web.HttpContext.Current.Server.MapPath ("/ Manager/Uploadfiles/" + varfilename + "/"); / / Image storage folder path, which needs to be modified according to different needs

If (! Directory.Exists (fpath)) / / check whether the file in the storage path exists

{

Directory.CreateDirectory (fpath); / / create a folder and upload files

}

String time = DateTime.Now.ToString ("yyyyMMddhhmmssfff"); / / use time to define the name of the uploaded image

String picturename = time + fileExtension

String newFilePath = fpath + picturename; / / File Save path

FileUploadName.SaveAs (newFilePath)

Return newFilePath; / / absolute path

}

Else

{

Return "- 1"; / / No file

}

}

/ / read Excel data

Public DataTable ExcelToDataSet (string filename)

{

String strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + filename + "; Extended Properties=Excel 8.0"

OleDbConnection conn = new OleDbConnection (strCon)

Conn.Open ()

/ / returns the schema of Excel, including the name, type, creation time and modification time of each sheet table.

DataTable dtSheetName = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object [] {null, "Table"})

/ / an array of strings containing table names in excel

String [] strTableNames = new string [dtSheetName.Rows.Count]

For (int k = 0; k < dtSheetName.Rows.Count; knot +)

{

StrTableNames [k] = dtSheetName.Rows [k] ["TABLE_NAME"] .ToString ()

}

OleDbDataAdapter myCommand = null

DataTable dt = new DataTable ()

/ / query data from the specified indication, you can first list all the indications for the user to choose from.

String strExcel = "select * from [" + strTableNames [0] + "]"

MyCommand = new OleDbDataAdapter (strExcel, strCon)

MyCommand.Fill (dt)

Conn.Close ()

Return dt

}

/ / read notepad data

Public DataTable ReadTXT (string dirTXT)

{

StreamReader objReader = new StreamReader (dirTXT)

System.Data.DataTable dt = new System.Data.DataTable ()

Dt.Columns.Add ("DN", System.Type.GetType ("System.String"))

String sLine = ""

While (sLine! = null)

{

SLine = objReader.ReadLine ()

If (sLine! = null & &! sLine.Equals (""))

{

DataRow dr = dt.NewRow ()

Dr [0] = sLine

Dt.Rows.Add (dr)

}

}

ObjReader.Close ()

Return dt

}

At this point, the study on "how to implement Excel and import notepad data into the database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.