如何通过验证将数据从excel导入数据库？ [英] How do I import data from excel to database with validation ?
本文介绍了如何通过验证将数据从excel导入数据库？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
I am having excel data which contains 38 columns and 308 rows and I wrote validation and inserting to datatable so while am running the code data is not inserting into it and error index out of range exception caught and only 7 coloumns are showing so please help me am struck there
我尝试过：
What I have tried:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.IO;
using System.Text;
using System.Data.OleDb;
using Oracle.DataAccess.Client;
namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
string consString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
OracleConnection conn = new OracleConnection("Data Source=TEST;User Id=Test;Password=Test");
DataSet dase;
DataTable Data;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
OracleConnection con = new OracleConnection(consString);
DataSet dase = new DataSet();
OracleDataAdapter da = new OracleDataAdapter("select AGENCY_DESC from MF_AGENCY", con);
da.Fill(dase);
DropDownList1.DataSource = dase.Tables[0];
DropDownList1.DataTextField = "AGENCY_DESC";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem(" ---SELECT---", "0"));
BindGrid();
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string p = Convert.ToString(DropDownList1.SelectedValue);
OracleConnection con = new OracleConnection(consString);
try
{
con.Open();
DataSet dase = new DataSet();
OracleDataAdapter dataadap = new OracleDataAdapter("select MF_Files.FILE_DESC from MF_AGENCY,MF_Files where MF_AGENCY.AGENCY_CD=MF_Files.AGENCY_CD AND MF_AGENCY.AGENCY_DESC='" + p + "' ", con);
dataadap.Fill(dase);
DropDownList2.DataSource = dase.Tables[0];
// the items to be displayed in the list items
DropDownList2.DataTextField = "FILE_DESC";
DropDownList2.DataBind();
}
catch (OracleException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
con.Close();
}
}
private void ImporttoDatatable()
{
try
{
if (FileUpload1.HasFile)
{
string FileName = FileUpload1.FileName;
string path = string.Concat(Server.MapPath("~//" + FileUpload1.FileName));
FileUpload1.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);
dase = new DataSet();
objAdapter1.Fill(dase);
Data = dase.Tables[0];
}
}
catch (Exception ex)
{
}
}
private void CheckData()
{
try
{
for (int i = 0; i < Data.Rows.Count; i++)
{
if (Data.Rows[i][0].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter amc_code in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Data.Rows.Count; i++)
{
if (Data.Rows[i][1].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter folio_no in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Data.Rows.Count; i++)
{
string date = DateTime.Parse(Data.Rows[i][38].ToString()).ToString("dd/MM/yyyy");
if (!ValidateDate(date))
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true);
return;
}
}
}
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()
{
try
{
for (int i = 0; i < Data.Rows.Count; i++)
{
DataRow row = Data.Rows[i];
int columnCount = Data.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string Ora = "INSERT INTO WBR70(AMC_CODE,FOLIO_NO,INVESTOR_NM,PROD_CODE,PROD_NAME,TRXN_TYPE,TRXN_NO,BROK_DLR_CODE,AE_CODE,TRADE_DATE,PLOT_UNITS,PLOT_AMOUNT,BROKER_PERC,TOTAL_UPFRONT,DEFER_UPFRONT,ADDL_PER,TOT_ADDL_UPFRONT,ADDL_UPFRONT,SPL_PERC,TOT_SPL_UPFRONT,SPL_UPFRONT,PAYABLE_INST,TRXN_CHARG,TER_LOC,DEFER_FREQ,SYS_REG_DATE,AUTO_TRXN_NO,DEFER_NO_OF_INST,SUB_BROKER_ARN,ARN_EMP_CODE,EUIN_VALID,USER_CODE,USER_TRXNNO,TRXN_NATURE,PAN,POSTED_DATE,PAYABLE,BRK_POSTED,BILL_DT)";
Ora += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "','" + columns[3] + "','" + columns[4] + "','" + columns[5] + "','" + columns[6] + "','" + columns[7] + "','" + columns[8] + "','" + columns[9] + "','" + columns[10] + "','" + columns[11] + "','" + columns[12] + "','" + columns[13] + "','" + columns[14] + "','" + columns[15] + "','" + columns[16] + "','" + columns[17] + "','" + columns[18] + "','" + columns[19] + "','" + columns[20] + "','" + columns[21] + "','" + columns[22] + "','" + columns[23] + "','" + columns[24] + "','" + columns[25] + "','" + columns[26] + "','" + columns[27] + "','" + columns[28] + "','" + columns[29] + "','" + columns[30] + "','" + columns[31] + "','" + columns[32] + "','" + columns[33] + "','" + columns[34] + "','" + columns[35] + "','" + columns[36] + "','" + columns[37] + "','" + columns[38] + "')";
OracleCommand cmd = new OracleCommand(Ora, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
}
}
protected void Button1_Click(object sender, EventArgs e)
{
ImporttoDatatable();
CheckData();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet dase = new DataSet();
//conn.Open();
string cmdstr = "Select * from WBR70";
OracleDataAdapter adp = new OracleDataAdapter(cmdstr, consString);
adp.Fill(dase);
Mutual_fund.DataSource = dase;
Mutual_fund.DataBind();
dase.Dispose();
// conn.Close();
}
}
}
推荐答案
，OleDbcon）;
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter（cmd）;
dase = new DataSet（）;
objAdapter1.Fill（dase）;
Data = dase.Tables [0];
}
}
catch（Exception ex）
{
}
}
private void CheckData（）
{
try
{
for（int i = 0; i< Data.Rows.Count; i ++）
{
if（Data.Rows [i] [0] .ToString（）== ）
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript（Page，Page .GetType（），InvalidArgs，alert（'请输入行中的amc_code+ RowNo + '）;，true）;
返回;
}
}
for（int i = 0;我< Data.Rows.Count; i ++）
{
if（Data.Rows [i] [1] .ToString（）==）
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript（Page，Page.GetType（），InvalidArgs，alert（'请输入folio_no in row+ RowNo +'）;，true）;
返回;
}
}
for（int i = 0; i< Data.Rows.Count; i ++）
{
string date = DateTime。解析（Data.Rows [I] [38]的ToString（））的ToString（ DD / MM / YYYY）;
if（！ValidateDate（date））
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript（Page，Page.GetType（），InvalidArgs，alert（'行中错误的日期格式+RowNo +'）;，true）;
返回;
}
}
}
catch（exception ex）
{
}
}
private bool ValidateDate（字符串日期）
{
try
{
string [] dateParts = date.Split（'/'）;
DateTime testDate = new DateTime（Convert.ToInt32（dateParts [2]），Convert.ToInt32（dateParts [1]），Convert.ToInt32（dateParts [0]））;
返回true;
}
捕获
{
返回false;
}
}
private void InsertData（）
{
try
{
for（int i = 0; i< Data。 Rows.Count; i ++）
{
DataRow row = Data.Rows [i];
int columnCount = Data.Columns.Count;
string [] columns = new string [columnCount];
for（int j = 0; j< columnCount; j ++）
{
columns [j] = row [j] .ToString（）;
}
conn.Open（）;
string Ora =INSERT INTO WBR70（AMC_CODE，FOLIO_NO，INVESTOR_NM，PROD_CODE，PROD_NAME，TRXN_TYPE，TRXN_NO，BROK_DLR_CODE，AE_CODE，TRADE_DATE，PLOT_UNITS，PLOT_AMOUNT，BROKER_PERC，TOTAL_UPFRONT，DEFER_UPFRONT，ADDL_PER，TOT_ADDL_UPFRONT，ADDL_UPFRONT，SPL_PERC， TOT_SPL_UPFRONT，SPL_UPFRONT，PAYABLE_INST，TRXN_CHARG，TER_LOC，DEFER_FREQ，SYS_REG_DATE，AUTO_TRXN_NO，DEFER_NO_OF_INST，SUB_BROKER_ARN，ARN_EMP_CODE，EUIN_VALID，USER_CODE，USER_TRXNNO，TRXN_NATURE，PAN，POSTED_DATE，应付，BRK_POSTED，BILL_DT）;
Ora + =VALUES（'+ columns [0] +'，'+ columns [1] +'，'+ columns [2] +'，'+ columns [3] +'，'+ columns [4] +'，'+ columns [5] +'，'+ columns [6] +'，'+ columns [7] +'，' + columns [8] +'，'+ columns [9] +'，'+ columns [10] +'，'+ columns [11] +'，'+ columns [12] + '，'+ columns [13] +'，'+ columns [14] +'，'+ columns [15] +'，'+ columns [16] +'，'+ columns [17] +'，'+ columns [18] +'，'+ columns [19] +'，'+ columns [20] +'，'+ columns [21] + '，'+ columns [22] +'，'+ columns [23] +'，'+ columns [24] +'，'+ columns [25] +'，'+ columns [26] +'，'+ columns [27] +'，'+ columns [28] +'，'+ columns [29] +'，'+ columns [30] +' ，'+ columns [31] +'，'+ columns [32] +'，'+ columns [33] +'，'+ columns [34] +'，'+ columns [ 35] +'，'+ columns [36] +'，'+ c olumns [37] +'，'+ columns [38] +'）;
OracleCommand cmd = new OracleCommand（Ora，conn）;
cmd.ExecuteNonQuery（）;
conn.Close（）;
}
}
catch（exception ex）
{
}
}
protected void Button1_Click（object sender，EventArgs e）
{
ImporttoDatatable（）;
CheckData（）;
InsertData（）;
BindGrid（）;
}
private void BindGrid（）
{
DataSet dase = new DataSet（）;
//conn.Open（）;
string cmdstr =从WBR70中选择*;
OracleDataAdapter adp = new OracleDataAdapter（cmdstr，consString）;
adp.Fill（dase）;
Mutual_fund.DataSource = dase;
Mutual_fund.DataBind（）;
dase.Dispose（）;
// conn.Close（）;
}
}
}
", OleDbcon); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd); dase = new DataSet(); objAdapter1.Fill(dase); Data = dase.Tables[0]; } } catch (Exception ex) { } } private void CheckData() { try { for (int i = 0; i < Data.Rows.Count; i++) { if (Data.Rows[i][0].ToString() == "") { int RowNo = i + 1; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter amc_code in row " + RowNo + "');", true); return; } } for (int i = 0; i < Data.Rows.Count; i++) { if (Data.Rows[i][1].ToString() == "") { int RowNo = i + 1; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter folio_no in row " + RowNo + "');", true); return; } } for (int i = 0; i < Data.Rows.Count; i++) { string date = DateTime.Parse(Data.Rows[i][38].ToString()).ToString("dd/MM/yyyy"); if (!ValidateDate(date)) { int RowNo = i + 2; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true); return; } } } 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() { try { for (int i = 0; i < Data.Rows.Count; i++) { DataRow row = Data.Rows[i]; int columnCount = Data.Columns.Count; string[] columns = new string[columnCount]; for (int j = 0; j < columnCount; j++) { columns[j] = row[j].ToString(); } conn.Open(); string Ora = "INSERT INTO WBR70(AMC_CODE,FOLIO_NO,INVESTOR_NM,PROD_CODE,PROD_NAME,TRXN_TYPE,TRXN_NO,BROK_DLR_CODE,AE_CODE,TRADE_DATE,PLOT_UNITS,PLOT_AMOUNT,BROKER_PERC,TOTAL_UPFRONT,DEFER_UPFRONT,ADDL_PER,TOT_ADDL_UPFRONT,ADDL_UPFRONT,SPL_PERC,TOT_SPL_UPFRONT,SPL_UPFRONT,PAYABLE_INST,TRXN_CHARG,TER_LOC,DEFER_FREQ,SYS_REG_DATE,AUTO_TRXN_NO,DEFER_NO_OF_INST,SUB_BROKER_ARN,ARN_EMP_CODE,EUIN_VALID,USER_CODE,USER_TRXNNO,TRXN_NATURE,PAN,POSTED_DATE,PAYABLE,BRK_POSTED,BILL_DT)"; Ora += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "','" + columns[3] + "','" + columns[4] + "','" + columns[5] + "','" + columns[6] + "','" + columns[7] + "','" + columns[8] + "','" + columns[9] + "','" + columns[10] + "','" + columns[11] + "','" + columns[12] + "','" + columns[13] + "','" + columns[14] + "','" + columns[15] + "','" + columns[16] + "','" + columns[17] + "','" + columns[18] + "','" + columns[19] + "','" + columns[20] + "','" + columns[21] + "','" + columns[22] + "','" + columns[23] + "','" + columns[24] + "','" + columns[25] + "','" + columns[26] + "','" + columns[27] + "','" + columns[28] + "','" + columns[29] + "','" + columns[30] + "','" + columns[31] + "','" + columns[32] + "','" + columns[33] + "','" + columns[34] + "','" + columns[35] + "','" + columns[36] + "','" + columns[37] + "','" + columns[38] + "')"; OracleCommand cmd = new OracleCommand(Ora, conn); cmd.ExecuteNonQuery(); conn.Close(); } } catch (Exception ex) { } } protected void Button1_Click(object sender, EventArgs e) { ImporttoDatatable(); CheckData(); InsertData(); BindGrid(); } private void BindGrid() { DataSet dase = new DataSet(); //conn.Open(); string cmdstr = "Select * from WBR70"; OracleDataAdapter adp = new OracleDataAdapter(cmdstr, consString); adp.Fill(dase); Mutual_fund.DataSource = dase; Mutual_fund.DataBind(); dase.Dispose(); // conn.Close(); } } }
您遇到的问题是因为您指的是列[ 38 ]在你的SQL语句中。该数组中只有38个条目，它们从 0 开始......所以列[38]将是 39 条目 - 它不存在，因此错误。
你应该使用参数化查询 - 不要连接字符串来生成sql语句 - 参见给我参数化的SQL，或者给我死亡 [ ^ ]
整个函数在其他方面也可以简单得多 - 没有必要将数据转换到另一个数组，并且在Catch块中没有任何东西的Try-Catch块是一个令人发指的罪行 - 你只会吞下任何错误并失去宝贵的价值信息。
尝试这样的事情：
You are getting the problem because you are referring to columns[38] in your poorly formed SQL statement. There are only 38 entries in that array and they start at 0 ... so columns[38] would be the 39th entry - it doesn't exist, hence the error.
You should use parameterised queries - don't concatenate strings to generate sql statements - see Give me parameterized SQL, or give me death[^]
That whole function can be much simpler in other ways too - there is no need to transpose the data into another array and having a Try-Catch block with nothing in the Catch block is a heinous crime - you will just "swallow" any errors and lose valuable information.
Try something like this:
private void InsertData()
{
for (int i = 0; i < Data.Rows.Count; i++)
{
conn.Open();
string Ora = "INSERT INTO WBR70(AMC_CODE,FOLIO_NO,INVESTOR_NM,PROD_CODE,PROD_NAME,TRXN_TYPE,TRXN_NO,BROK_DLR_CODE,AE_CODE,TRADE_DATE,PLOT_UNITS,PLOT_AMOUNT,BROKER_PERC,TOTAL_UPFRONT,DEFER_UPFRONT,ADDL_PER,TOT_ADDL_UPFRONT,ADDL_UPFRONT,SPL_PERC,TOT_SPL_UPFRONT,SPL_UPFRONT,PAYABLE_INST,TRXN_CHARG,TER_LOC,DEFER_FREQ,SYS_REG_DATE,AUTO_TRXN_NO,DEFER_NO_OF_INST,SUB_BROKER_ARN,ARN_EMP_CODE,EUIN_VALID,USER_CODE,USER_TRXNNO,TRXN_NATURE,PAN,POSTED_DATE,PAYABLE,BRK_POSTED,BILL_DT)";
Ora += "VALUES(@c0,@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24,@c25,@c26,@c27,@c28,@c29,@c30,@c31,@c32,@c33,@c34,@c35,@c36,@c37)";
var cmd = new OracleCommand(Ora, conn);
for (var j = 0; j < Data.Columns.Count; j++)
{
cmd.Parameters.AddWithValue(String.Format("@c{0}", j), Data.Rows[i][j].ToString());
}
cmd.ExecuteNonQuery();
conn.Close();
}
}
这篇关于如何通过验证将数据从excel导入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
