从Excel中读取指定的单元格并插入到SQL表中 [英] Read Specified Cells from the Excel and insert into SQL table
本文介绍了从Excel中读取指定的单元格并插入到SQL表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下结构的Execl Sheet（2007）。
在Excel工作表中Sno。，详细信息，State1，State2，Name，College，Address，Department
是标题
------------ --------------------------------
Sno。 |详情| STATE1 | STATE2
----------------------------------------- ---
1 | NAME |约翰|彼得
2 |大学| AAA | BBB
3 |地址| YY | ZZ
4 |部门| IT | ECE
我想将STATE1，STATE2插入我的数据库。
您能否提供以下execl的表结构和
代码，仅选择STATE1，STATE2列并将STATE1，STATE2 colunm详细信息插入数据库。
解决方案
可以将状态2的详细信息插入数据库...
看我们先将数据插入表中创建一个数据表，然后在数据行中我们从excel文件中填充数据。在那个时候只给你想插入数据库的行的索引。
索引意味着Sno。 - > 0，详情 - > 1喜欢这个
例如。
DataTable dt = new DataTable（）;
#region datatable
dt.Columns.Add（ SerialNo）; // 0
dt.Columns.Add（ Details）; // 1
dt.Columns.Add（ State2）; // 2
#endregion
string strQuery = truncate table EmployeeMaster1;
SqlCommand MyCommand3 = new SqlCommand（）;
MyConnection1 = new SqlConnection（connectionString）;
if （MyConnection1.State！= ConnectionState.Open）
{
MyConnection1.Open（）;
// MyConnection1.Open（）;
}
MyCommand3.CommandText = strQuery;
MyCommand3.Connection = MyConnection1;
MyCommand3.ExecuteNonQuery（）;
if （MyConnection1.State == ConnectionState.Open）
{
MyConnection1.Close（）;
}
尝试
{
string 行;
string m_CSV = FileUploadControl1.FileName; // fileCSV.FileName;
FileUploadControl1.PostedFile.SaveAs（Server。 MapPath（ m_CSV.csv））;
// string m_Path = Server.MapPath（〜）+/+ m_CSV;
string m_Path = Server.MapPath（ m_CSV.csv）;
char [] seps = {' ， '};
if （CheckFileType（FileUploadControl1.FileName））
{
// StreamReader sr = new StreamReader（Server.MapPath（〜）+\\+ m_CSV）;
StreamReader sr = new StreamReader（m_Path）;
line = sr.ReadLine（）; // 阅读第一行文字
while （（line = sr.ReadLine（））！= null ） // < span class =code-comment>继续阅读，直至到达文件末尾
{
String [] fields = line.Split（SEPS）;
DataRow dr = dt.NewRow（）;
#region datarow
dr [ 0 ] = fields [ 0 ]。ToString（）。Trim（）;
dr [ 1 ] = fields [ 1 ]。ToString（）。Trim（）;
dr [ 2 ] = fields [ 3 ]。ToString（）。Trim（）; // < big> state2 index< / big>
#endregion
dt.Rows.Add（dr）;
}
sr.Close（）;
DataSet ds = new DataSet（）;
ds.Tables.Add（dt）;
我希望它对你有用.............
您好，
您可以使用Open Office XML SDK。
http://www.microsoft.com/en-us/download/details.aspx?id=5124 [ ^ ]
下面你可以找到一个用C＃编写的静态方法，它允许你从某个单元格中获取值：
public static string XLGetCellValue（ string fileName， string sheetName， string addressName）
{
string value = 空;
使用（ var document = SpreadsheetDocument.Open（fileName， false ））
{
var wbPart = document.WorkbookPart;
// 找到包含所提供名称的工作表，然后使用该工作表
// 用于检索对相应工作表的引用的对象。
< span class =code-keyword> var theSheet = wbPart.Workbook.Descendants< Sheet>（）。FirstOrDefault（s = > s.Name == sheetName）;
if （theSheet == null ）
{
throw new ArgumentException（ < span class =code-string> sheetName）;
}
// 检索对工作表部分的引用，然后使用
// 工作表属性，用于获取 $的单元格的引用b $ b // 地址与您提供的地址相符：
var wsPart =
（WorksheetPart）（wbPart.GetPartById（theSheet.Id））;
var theCell = wsPart.Worksheet.Descendants< Cell>（）。FirstOrDefault（c = > c.CellReference == addressName）;
// 如果单元格不存在，则返回一个空字符串：
if （theCell！= null ）
{
value = theCell.InnerText;
// 如果单元格代表一个数值，那么就完成了。
// 对于日期，此代码返回
// 表示日期。代码单独处理字符串和布尔
// 。对于共享字符串，代码在共享字符串表中查找
// 对应的值。对于布尔值，
// 代码将值转换为单词TRUE或FALSE。如果（theCell.DataType！= null ）
{
switch （theCell.DataType.Value）
{
case CellValues .SharedString：
// 对于共享字符串，请在共享 $ b中查找值$ b // 字符串表。
var stringTable = wbPart。
GetPartsOfType< SharedStringTablePart>（）。FirstOrDefault（）;
// 如果缺少共享字符串表，则
// 错误。返回在单元格中找到的索引。
// 否则，请查找正确的表格中的文字。
if （stringTable！= null ）
{
value = stringTable.SharedStringTable。
ElementAt（ int .Parse（ value ））。InnerText;
}
break ;
case CellValues。 Boolean ：
switch （ value ）
{
case 0：
value = FALSE;
break ;
默认：
value = TRUE;
break ;
}
break ;
}
}
}
}
返回 值;
}
如何调用它：
var valueQuantity = XLGetCellValue（fileName，Sheet1，Q+ index ）;
在已发布的数据库中插入数据的代码。
享受。
我测试它的工作就像魅力一样，只是根据你的要求创建了这个DEMO：
如果你有任何问题，请告诉我。
在.aspx页面上：
< html xmlns = http://www.w3.org/1999/xhtml >
< head runat = server >
< title> < / title >
< / head >
< body>
< form id = form1 runat = server >
< div>< table style = height：70px; width：404px;>< tbody>
< tr> < td class = style1 >选择日期：< / td > < td class = style2 > < / td > < / tr < span class =code-keyword>>
< tr> < td class = style1 >上传Excel文件：< / td > < td class = style2 >
< asp：fileupload id = FileUpload1 runat = server xmlns ：asp = ＃unknown > < / asp：fileupload > < / td > < / tr >
< / tbody > < / table > < / div > < asp： button id = btnUpload onclick = btnUpload_Click runat = server text = 上传Excel文件 xmlns：asp = ＃unknown >
< / asp：按钮 >
< asp：gridview id = ExcelGridView runat = server xmlns：asp = #unknown >
< / asp：gridview >
< / 表格 >
< ; / 正文 >
< / html >
< / html >
on .cs页面：
使用系统;
使用 System.Collections.Generic;
使用 System.Linq;
使用 System.Web;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.Data;
使用 System.Data.OleDb;
public partial class ReadDataFromExcel：System.Web.UI.Page
{
protected void Page_Load（ object sender，EventArgs e）
{
}
protected void btnUpload_Click（ object sender，EventArgs e）
{
if （FileUpload1.FileName.ToString（）！= && FileUpload1.ToString（）。包含（ 。 ））
{
字符串 filepath = Server.MapPath（ UPLOADEDFILES< /跨度>）;
FileUpload1.SaveAs（filepath + \\ + FileUpload1.FileName.Split （' \\'）[FileUpload1.FileName.Split（' \\'）。长度 - 1 ]）;
string excelPath =（filepath + \\ \\\ + FileUpload1.FileName.Split（' \\'） [FileUpload1.FileName.Split（' \\'）。长度 - 1 ]）;
var dataset = new DataTable（）;
dataset = GetExcelData（excelPath）; ;
ExcelGridView.DataSource = GetExcelData（excelPath）;
ExcelGridView.DataBind（）;
foreach （DataRow row in dataset.Rows）
{
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection（ 数据源= GIRIJESH-PC;初始目录= TestData;集成安全性=真）;
string temp1 = ' + Convert.ToString（row [ 0 ]）+ ';
string temp2 = ' + Convert.ToString（row [ 1 ]）+ ;
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand（）;
cmd.CommandType = System.Data.CommandType.Text;
string text = INSERT INTO [TestData ]。[dbo]。[UserInfo]（[年龄]，[州]）VALUES（ + temp1 + ， + temp2 + ）;
cmd.CommandText = text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open（）;
cmd.ExecuteNonQuery（）;
sqlConnection1.Close（）;
}
}
}
public DataTable GetExcelData（ string ExcelFilePath）
{
string OledbConnectionString = string 。空的;
OleDbConnection objConn = null ;
OledbConnectionString = Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + ExcelFilePath + ;扩展属性= Excel 8.0;;
objConn = new OleDbConnection（OledbConnectionString）;
if （objConn.State == ConnectionState.Closed）
{
objConn.Open（）;
}
OleDbCommand objCmdSelect = new OleDbCommand（ 从[Sheet1
Hi,
I have an Execl Sheet (2007) in the following structure.
In the Excel sheet Sno.,Details, State1, State2,Name,College,Address,Department
are headings
--------------------------------------------
Sno. | DETAILS | STATE1 | STATE2
--------------------------------------------
1 | NAME | John | Peter
2 | COLLEGE | AAA | BBB
3 | ADDRESS | YY | ZZ
4 | DEPARTMENT | IT | ECE
I want to insert STATE1,STATE2 into my database.
Can you please provide table structure for the following execl and
Code to select only STATE1,STATE2 Column and insert STATE1,STATE2 colunm details into the database.
解决方案
It is possible to insert state 2 details into Database...
look while inserting data into table we first create a data table and then in data row we fill data from excel file. In that time give index of only that rows which you want to insert into database.
indexes means Sno. ->0, Details ->1 like this
for eg.
DataTable dt = new DataTable(); #region datatable dt.Columns.Add("SerialNo");//0 dt.Columns.Add("Details");//1 dt.Columns.Add("State2");//2 #endregion string strQuery = "truncate table EmployeeMaster1"; SqlCommand MyCommand3 = new SqlCommand(); MyConnection1 = new SqlConnection(connectionString); if (MyConnection1.State != ConnectionState.Open) { MyConnection1.Open(); // MyConnection1.Open(); } MyCommand3.CommandText = strQuery; MyCommand3.Connection = MyConnection1; MyCommand3.ExecuteNonQuery(); if (MyConnection1.State == ConnectionState.Open) { MyConnection1.Close(); } try { string line; string m_CSV = FileUploadControl1.FileName; // fileCSV.FileName; FileUploadControl1.PostedFile.SaveAs(Server.MapPath("m_CSV.csv")); //string m_Path = Server.MapPath("~") + "/" + m_CSV; string m_Path = Server.MapPath("m_CSV.csv"); char[] seps = { ',' }; if (CheckFileType(FileUploadControl1.FileName)) { // StreamReader sr = new StreamReader(Server.MapPath("~") + "\\" + m_CSV); StreamReader sr = new StreamReader(m_Path); line = sr.ReadLine(); //Read the first line of text while ((line = sr.ReadLine()) != null) //Continue to read until you reach end of file { String[] fields = line.Split(seps); DataRow dr = dt.NewRow(); #region datarow dr[0] = fields[0].ToString().Trim(); dr[1] = fields[1].ToString().Trim(); dr[2] = fields[3].ToString().Trim();//<big>state2 index</big> #endregion dt.Rows.Add(dr); } sr.Close(); DataSet ds = new DataSet(); ds.Tables.Add(dt);
I hope it is useful to you.............
Hi,
You can use Open Office XML SDK.
http://www.microsoft.com/en-us/download/details.aspx?id=5124[^]
Below you can find a static method wrote in C# which allows you to get the value from a certain cell:
public static string XLGetCellValue(string fileName, string sheetName, string addressName) { string value = null; using (var document = SpreadsheetDocument.Open(fileName, false)) { var wbPart = document.WorkbookPart; // Find the sheet with the supplied name, and then use that Sheet // object to retrieve a reference to the appropriate worksheet. var theSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName); if (theSheet == null) { throw new ArgumentException("sheetName"); } // Retrieve a reference to the worksheet part, and then use its // Worksheet property to get a reference to the cell whose // address matches the address you supplied: var wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); var theCell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == addressName); // If the cell does not exist, return an empty string: if (theCell != null) { value = theCell.InnerText; // If the cell represents a numeric value, you are done. // For dates, this code returns the serialized value that // represents the date. The code handles strings and Booleans // individually. For shared strings, the code looks up the // corresponding value in the shared string table. For Booleans, // the code converts the value into the words TRUE or FALSE. if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: // For shared strings, look up the value in the shared // strings table. var stringTable = wbPart. GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); // If the shared string table is missing, something is // wrong. Return the index that you found in the cell. // Otherwise, look up the correct text in the table. if (stringTable != null) { value = stringTable.SharedStringTable. ElementAt(int.Parse(value)).InnerText; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } } return value; }
And how to call it:
var valueQuantity = XLGetCellValue(fileName, "Sheet1", "Q" + index);
Code for inserting data in DB already posted.
Enjoy.
Hi,i tested its working like charm, just created this DEMO as ur requirement:
and if u have any problem plz let me know.
and on .aspx page :
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div><table style="height: 70px; width: 404px;"><tbody> <tr> <td class="style1">Select Date :</td> <td class="style2"></td> </tr> <tr> <td class="style1">Upload Excel File :</td> <td class="style2"> <asp:fileupload id="FileUpload1" runat="server" xmlns:asp="#unknown"></asp:fileupload></td> </tr> </tbody></table></div><asp:button id="btnUpload" onclick="btnUpload_Click" runat="server" text="Upload Excel File" xmlns:asp="#unknown"> </asp:button> <asp:gridview id="ExcelGridView" runat="server" xmlns:asp="#unknown"> </asp:gridview> </form> </body> </html> </html>
on .cs page :
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.OleDb; public partial class ReadDataFromExcel : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.FileName.ToString() != "" && FileUpload1.ToString().Contains(".")) { String filepath = Server.MapPath("UploadedFiles"); FileUpload1.SaveAs(filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]); string excelPath = (filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]); var dataset = new DataTable(); dataset = GetExcelData(excelPath); ; ExcelGridView.DataSource = GetExcelData(excelPath); ExcelGridView.DataBind(); foreach (DataRow row in dataset.Rows) { System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("Data Source=GIRIJESH-PC;Initial Catalog=TestData;Integrated Security=True"); string temp1 = "'"+Convert.ToString(row[0])+"'"; string temp2 = "'"+Convert.ToString(row[1])+"'"; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; string text = "INSERT INTO [TestData].[dbo].[UserInfo]([Age],[State])VALUES(" + temp1 + "," + temp2 + ")"; cmd.CommandText = text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); } } } public DataTable GetExcelData(string ExcelFilePath) { string OledbConnectionString = string.Empty; OleDbConnection objConn = null; OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;"; objConn = new OleDbConnection(OledbConnectionString); if (objConn.State == ConnectionState.Closed) { objConn.Open(); } OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1
这篇关于从Excel中读取指定的单元格并插入到SQL表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
