如何使用asp.net在多个sql表中导入excel文件的多个工作表? [英] how to import multiple worksheets of excel file in multiple sql tables using asp.net ?
问题描述
我有一个带有2个不同工作表的excel文件作为Sheet1和Sheet2.Now我想将这2个不同的工作表值导入2个不同的sql表(k2_Sheet1,k2_Sheet2)。我只能在使用1时才能导入sheet和1表,但是我希望在按钮单击事件上同时在两个表上导入两个工作表值。
我的代码如下:
使用System.Data.SqlClient;
使用System.Data.OleDb;
命名空间ex2sl
{
public partial class _Default:System.Web.UI.Page
{
SqlConnection conn = new SqlConnection(Data Source = User-PC; Initial Catalog = master; Integrated Security = True);
DataSet ds;
DataTable Dt;
protected void Page_Load(object sender,EventArgs e)
{
if(!IsPostBack)
{
BindGrid();
}
}
private void ImporttoDatatable()
{
试试
{
if(FlUploadcsv.HasFile)
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath(〜/ Document /+ FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection(Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +; Extended Properties = Excel 12.0;);
OleDbCommand cmd = new OleDbCommand(SELECT * FROM [Sheet1 $],OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables [0];
}
}
catch(Exception ex)
{
}
}
private bool ValidateDate(字符串日期)
{
尝试
{
string [] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32( dateParts [2]),Convert.ToInt32(dateParts [1]),Convert.ToInt32(dateParts [0]));
返回true;
}
catch
{
返回false;
}
}
private void InsertData()
{
for(int i = 0;我< Dt.Rows.Count; i ++)
{
DataRow row = Dt.Rows [i];
int columnCount = Dt.Columns.Count;
string [] columns = new string [columnCount];
for(int j = 0; j< columnCount; j ++)
{
columns [j] = row [j] .ToString();
}
conn.Open();
string sql =INSERT INTO EmpImport(EmployeeID,Name,Nameation,DateOfBirth,City);
sql + =VALUES('columns + 0] +','+ columns [1 ] +','+ columns [2] +',Convert(varchar(10),'+ columns [3] +',103),'+ columns [4] +');
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
受保护void btnIpload_Click(object sender,EventArgs e)
{
ImporttoDatatable();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr =从EmpImport中选择*;
SqlDataAdapter adp = new SqlDataAdapter(cmdstr,conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind ();
ds.Dispose();
conn.Close();
}
}
}
Am having one excel file with 2 different worksheets as Sheet1 and Sheet2.Now I want to import these 2 different sheet values into 2 different sql tables(k2_Sheet1,k2_Sheet2).I can able to do import only if am working with 1 sheet and and 1 table,but I want two sheets values to be imported on two tables at the same time on button click event.
My code below:
using System.Data.SqlClient;
using System.Data.OleDb;
namespace ex2sl
{
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=User-PC;Initial Catalog=master;Integrated Security=True");
DataSet ds;
DataTable Dt;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void ImporttoDatatable()
{
try
{
if (FlUploadcsv.HasFile)
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmpImport";
SqlDataAdapter adp = new SqlDataAdapter(cmdstr, conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind();
ds.Dispose();
conn.Close();
}
}
}
推荐答案
,OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables [0];
}
}
catch(Exception ex)
{
}
}
private bool ValidateDate(字符串日期)
{
尝试
{
string [] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts [2]),Convert.ToInt32(dateParts [ 1]),Convert.ToInt32(dateParts [0]));
返回true;
}
catch
{
返回false;
}
}
私有空InsertData()
{
for(int i = 0;我< Dt.Rows.Count; i ++)
{
DataRow row = Dt.Rows [i];
int columnCount = Dt.Columns.Count;
string [] columns = new string [columnCount];
for(int j = 0; j< columnCount; j ++)
{
columns [j] = row [j] .ToString();
}
conn.Open();
string sql =INSERT INTO EmpImport(EmployeeID,Name,Nameation,DateOfBirth,City);
sql + =VALUES('columns + 0] +','+ columns [1 ] +','+ columns [2] +',Convert(varchar(10),'+ columns [3] +',103),'+ columns [4] +');
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
受保护void btnIpload_Click(object sender,EventArgs e)
{
ImporttoDatatable();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr =从EmpImport中选择*;
SqlDataAdapter adp = new SqlDataAdapter(cmdstr,conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind ();
ds.Dispose();
conn.Close();
}
}
}
", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmpImport";
SqlDataAdapter adp = new SqlDataAdapter(cmdstr, conn);
adp.Fill(ds);
empdet.DataSource = ds;
empdet.DataBind();
ds.Dispose();
conn.Close();
}
}
}
这篇关于如何使用asp.net在多个sql表中导入excel文件的多个工作表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!