如何从excel第6行读取数据到SQL服务器? [英] How to read data from excel 6th row to SQL server?
问题描述
我想从第6行excel导入数据。 excel的第1排到第5排有标题。当我导入数据时,该值为null,因为我在excel的第二行中没有任何数据。
public partial class Excelasp:System.Web.UI.Page
{
protected void 上传(对象发件人,EventArgs e)
{
// 上传并保存文件
string excelPath = Server.MapPath( 〜/ Doc /)+ Path。用GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string .Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (扩展名)
{
案例 。xls: // Excel 97-03
conString = ConfigurationManager.ConnectionStrings [ Excel03ConString 跨度>]的ConnectionString。
break ;
case 。xlsx: // Excel 07或更高版本
conString = ConfigurationManager.ConnectionStrings [ Excel07 + ConString]。ConnectionString;
break ;
}
conString = string .Format(conString,excelPath);
使用(OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null )。行[ 0 ] [ TABLE_NAME]。ToString( );
DataTable dtExcelData = new DataTable();
// [可选]:建议不要将数据视为字符串默认情况下。
dtExcelData.Columns.AddRange( new DataColumn [ 3 ] { new DataColumn( PersonId, typeof ( int )),
new DataColumn( 名称, typeof ( int )),
new DataColumn( 薪水, typeof (十进制))});
使用(OleDbDataAdapter oda = new OleDbDataAdapter( SELECT * FROM [ + sheet1 + ],excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings [ 构造跨度>]的ConnectionString。
使用(SqlConnection con = new SqlConnection(consString))
{
使用(SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
// 设置数据库表名
sqlBulkCopy.DestinationTableName = dbo.tblPerson;
// [可选]:将Excel列映射到数据库表的列
sqlBulkCopy.ColumnMappings.Add( PersonId, PersonId);
sqlBulkCopy.ColumnMappings.Add( 名称, 名称);
sqlBulkCopy.ColumnMappings.Add( 薪水, 薪水);
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
}
}
我尝试过:
excel_con.Open();
string sheet1 = excel_con。 GetOleDbSchemaTable(OleDbSchemaGuid.Tables将,NULL).Rows [1] [ TABLE_NAME]的ToString();
的DataTable dtExcelData =新数据表();
基于 Excel连接字符串 - ConnectionStrings.com [ ^ ](标准备选部分),您必须更改通过定义获取数据的范围来选择
语句,例如:
SELECT *
FROM [SheetName
A6:D]sqlBulkCopy.WriteToServer(dtExcelData.AsEnumerable()。Skip( 5 ));
I want to import data from 6th row of excel. There are headings from 1 to 5th row of excel. When I import data the value is null because I do not have any data in 2nd row of excel.
public partial class Excelasp : System.Web.UI.Page
{
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Doc/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("PersonId", typeof(int)),
new DataColumn("Name", typeof(int)),
new DataColumn("Salary",typeof(decimal)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPerson";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("PersonId", "PersonId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
}
}
What I have tried:
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[1]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
Based on example provided on Excel connection strings - ConnectionStrings.com[^] (Standard alternative section), you have to changeselect
statement by defining a range to grab data, for example:
SELECT * FROM [SheetName
A6:D]
or by skiping[^] first 5 rows in code (using Linq):
sqlBulkCopy.WriteToServer(dtExcelData.AsEnumerable().Skip(5));
这篇关于如何从excel第6行读取数据到SQL服务器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!