string filepath = @"F:\Web\Uploads\KclExcelFile\20100716161503.xls";
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [20100716161503$]";
System.Data.OleDb.OleDbDataAdapter ExcelDA = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
DataSet dsxml = new DataSet();
ExcelDA.Fill(dsxml, "ds");
//添加自增ID
DataColumn Col = new DataColumn("id", System.Type.GetType("system.int32", true, true));
DataTable ExcelDT = dsxml.Tables[0];
ExcelDT.Columns.Add(Col);
int flagid = 0;
foreach (DataRow ExcelDR in ExcelDT.Rows)
{
ExcelDR["id"] = flagid;
flagid += 1;
}
ExcelDT.Columns[0].ColumnName = "flh";
ExcelDT.Columns[1].ColumnName = "qzh";
ExcelDT.Columns[2].ColumnName = "dnumber";
ExcelDT.Columns[3].ColumnName = "rollnumber";
ExcelDT.Columns[4].ColumnName = "ztm";
ExcelDT.Columns[5].ColumnName = "zrz";
ExcelDT.Columns[6].ColumnName = "btime";
ExcelDT.Columns[7].ColumnName = "etime";
ExcelDT.Columns[8].ColumnName = "publictime";
ExcelDT.Columns[0].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[1].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[2].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[3].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[4].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[5].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[6].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[7].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[8].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Rows[0].Delete();//第一行数据不要
dsxml.Tables[0].TableName = "fr";
DataSet ds = new DataSet();
ds = dsxml;
SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString);
DataSet SqlDS = new DataSet();
SqlDataAdapter drfr = new SqlDataAdapter("select top 1 * from xd_k ", connection);
SqlCommandBuilder CB = new SqlCommandBuilder(drfr);
DataSet dsfr = new DataSet();
drfr.Fill(dsfr, "fr");
ds.Merge(dsfr);
drfr.Update(ds, "fr");
ds.AcceptChanges();
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [20100716161503$]";
System.Data.OleDb.OleDbDataAdapter ExcelDA = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
DataSet dsxml = new DataSet();
ExcelDA.Fill(dsxml, "ds");
//添加自增ID
DataColumn Col = new DataColumn("id", System.Type.GetType("system.int32", true, true));
DataTable ExcelDT = dsxml.Tables[0];
ExcelDT.Columns.Add(Col);
int flagid = 0;
foreach (DataRow ExcelDR in ExcelDT.Rows)
{
ExcelDR["id"] = flagid;
flagid += 1;
}
ExcelDT.Columns[0].ColumnName = "flh";
ExcelDT.Columns[1].ColumnName = "qzh";
ExcelDT.Columns[2].ColumnName = "dnumber";
ExcelDT.Columns[3].ColumnName = "rollnumber";
ExcelDT.Columns[4].ColumnName = "ztm";
ExcelDT.Columns[5].ColumnName = "zrz";
ExcelDT.Columns[6].ColumnName = "btime";
ExcelDT.Columns[7].ColumnName = "etime";
ExcelDT.Columns[8].ColumnName = "publictime";
ExcelDT.Columns[0].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[1].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[2].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[3].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[4].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[5].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[6].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[7].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Columns[8].DataType = System.Type.GetType("System.String", true, true);
ExcelDT.Rows[0].Delete();//第一行数据不要
dsxml.Tables[0].TableName = "fr";
DataSet ds = new DataSet();
ds = dsxml;
SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString);
DataSet SqlDS = new DataSet();
SqlDataAdapter drfr = new SqlDataAdapter("select top 1 * from xd_k ", connection);
SqlCommandBuilder CB = new SqlCommandBuilder(drfr);
DataSet dsfr = new DataSet();
drfr.Fill(dsfr, "fr");
ds.Merge(dsfr);
drfr.Update(ds, "fr");
ds.AcceptChanges();
[mgid] [int] IDENTITY(1,1) NOT NULL,
[qzh] [nvarchar](200) NULL,
[rollnumber] [nvarchar](200) NULL,
[publictime] [nvarchar](200) NULL,
[btime] [nvarchar](200) NULL,
[ztm] [nvarchar](200) NULL,
[dnumber] [nvarchar](200) NULL,
[zrz] [nvarchar](200) NULL,
[etime] [nvarchar](200) NULL,
[flh] [nvarchar](200) NULL,
CONSTRAINT [PK_xd_k] PRIMARY KEY CLUSTERED
(
[mgid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]错误:违反并发性: DeleteCommand 影响了预期 1 条记录中的 0 条。
求解
下面这个方法是连接excel public static DataSet ImportExcel(string file)
{
FileInfo fileInfo = new FileInfo(file);
if (!fileInfo.Exists)
return null;
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(strConn);
DataSet dsExcel = new DataSet();
try
{
objConn.Open();
string strSql ="select * from [Sheet1$]";
OleDbDataAdapter odbcExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
odbcExcelDataAdapter.Fill(dsExcel);
//关闭excel数据源
objConn.Close(); return dsExcel;
}
catch (Exception ex)
{
//这里该如何控制呢!当格式不符合需求的时候????????????
MessageBox.Show(ex.Message,"提示");
return dsExcel;
//throw ex;
}
}
#endregion下面这个方法是获取数据到ds中
DataSet ds;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
ds = ImportExcel(this.openFileDialog1.FileName);//获取excel
if (ds.Tables.Count <=0)
{
//MessageBox.Show("表中没有数据或者预期格式不对!\n请检查","提示");
return;
}
}
else
{
return;
} int product_id = int.Parse(P_idLabel.Text.ToString());
if (product_id.ToString() == "" || product_id.ToString() == null)
{
MessageBox.Show("请新建项目或者选择项目","提示");
return;
} string InsertResult = "处理结果如下:\r\n\r\n";
int dsLength = ds.Tables[0].Rows.Count;//获取excel中数据的长度
for (int i = 1; i < dsLength; i++)
{
//判断获取到的数据是不是数字!如果不是数字就弹出提示
string pattern = @"^\d+(\.\d)?$";
if(!System.Text.RegularExpressions.Regex.IsMatch(ds.Tables[0].Rows[i][3].ToString(),pattern))
{
MessageBox.Show("请检查你的EXCEL表列的顺序是不是\n物料名称、规格型号、单位、数量、生产商","提示");
return;
} string err = "";
string item_note = ds.Tables[0].Rows[i][0].ToString().Trim();
string item_grade = ds.Tables[0].Rows[i][1].ToString().Trim();
string unit_note = ds.Tables[0].Rows[i][2].ToString().Trim();
double qty_req = Convert.ToDouble(ds.Tables[0].Rows[i][3].ToString().Trim());
string supplier = ds.Tables[0].Rows[i][4].ToString().Trim();
//string item_type = ds.Tables[0].Rows[i][5].ToString();
string re = ds.Tables[0].Rows[i][5].ToString().Trim();
…………
问题可能在这里 你往自动编号字段插入数据
我把数据弄成XML就不会出现这样的问题
FileStream fin = new FileStream(@"F:\程序\工程管理\XD\兴达\Web\Uploads\KclExcelFile\民国档案2009-10-25-23-22-11.xml", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
DataSet dsxml = new DataSet();
dsxml.ReadXml(new StreamReader(fin));
DataColumn Col = new DataColumn("id", System.Type.GetType("system.int32", true, true));
DataTable dtxml = dsxml.Tables[0];
dtxml.Columns.Add(Col);
int BeginID = 0;
int flagid = 0;
foreach (DataRow drxml in dtxml.Rows)
{
drxml["id"] = BeginID + flagid;
flagid += 1;
}
dsxml.Tables[0].TableName = "fr";
SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString);
DataSet SqlDS = new DataSet();
SqlDataAdapter drfr = new SqlDataAdapter("select top 1 * from xd_k ", connection);
SqlCommandBuilder CB = new SqlCommandBuilder(drfr);
DataSet dsfr = new DataSet();
drfr.Fill(dsfr, "fr");
dsxml.Merge(dsfr);
drfr.Update(dsxml, "fr");
dsxml.AcceptChanges();
这个这么解释,麻烦帮帮忙哈