如何读取excel中的数据
发布网友
发布时间：2022-04-22 19:04
共1个回答
热心网友
时间：2022-04-10 09:55
给你个网址，bbs.csdn.net/topics/390824686 自己去看看。就是下面的内容：
ASP.NET中Excel数据批量导入导出SQL Server
C# code?
protected void btnUpload_Click(object sender, EventArgs e) { DataSet ds = GetExcelData(); InsertDB(ds); } /// <summary> /// 该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表,此用Sheet1； /// </summary> /// <param name="ds">ds</param> private void InsertDB(DataSet ds) { SqlConnection _con = new SqlConnection(@"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True"); SqlCommand cmd = new SqlCommand(); cmd.Connection = _con; StringBuilder sb = new StringBuilder(); if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { sb.Append(" INSERT INTO bookInfo(bookID,bookName,author,publisher,price,readerID,readerType,bookStatus) VALUES('"); sb.Append(ds.Tables[0].Rows[i].ItemArray[0].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[1].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[2].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[3].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[4].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[5].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[6].ToString() + "','"); sb.Append(ds.Tables[0].Rows[i].ItemArray[7].ToString() + "' ) "); cmd.CommandText = sb.ToString(); } } _con.Open(); int j = cmd.ExecuteNonQuery(); _con.Close(); if (j > 0) { lblMessage.Text = "Insert into DB table Sucessfully!"; } } /// <summary> /// get data source from excel file /// </summary> /// <returns>dataset ds</returns> private DataSet GetExcelData() { DataSet ds = new DataSet(); string filePath = inputFile.PostedFile.FileName; string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ; string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'"; string queryStr = "SELECT * FROM [Sheet1$]"; OleDbConnection conn03 = new OleDbConnection(connStr03); OleDbConnection conn07 = new OleDbConnection(connStr07); if (inputFile.HasFile) { string fileExt = System.IO.Path.GetExtension(inputFile.FileName); if (fileExt == ".xls") { OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03); myAdapter.Fill(ds); } else if (fileExt == ".xlsx") { OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03); myAdapter.Fill(ds); } else { lblMessage.Text = "The file is not exist!"; } } return ds; }
