如何使用C＃.Net从Excel工作表上载员工出勤到SQL Server表 [英] How do I upload the employees attendance to SQL Server Table from excel sheet using C#.Net

问题描述

我有一个Excel文件，其中包含Employee的数据

我的Excel包含的列为Sr_No EMP_ID，EMP_NAME Total_Leaves，Total_Presents，

Day_1，Day_2至Day_31



我使用以下查询在SQL Server中创建一个表

I have a Excel file which contains the data of Employee
My Excel contains the columns as Sr_No EMP_ID, EMP_NAME Total_Leaves, Total_Presents,
Day_1,Day_2 up to Day_31

I create a table in SQL Server using following Query

create table attendance_sample(
	Sr_No varchar,
	EMP_ID varchar(100),
	EMP_NAME varchar(100),
	Total_leaves varchar,
	total_presents varchar,
	Day_1 varchar(5), Day_2 varchar(5), Day_3 varchar(5), Day_4 varchar(5), Day_5 varchar(5), Day_6 varchar(5), Day_7 varchar(5), Day_8 varchar(5), Day_9 varchar(5), Day_10 varchar(5), 
	Day_11 varchar(5), Day_12 varchar(5), Day_13 varchar(5), Day_14 varchar(5), Day_15 varchar(5), Day_16 varchar(5), Day_17 varchar(5), Day_18 varchar(5), Day_19 varchar(5), Day_20 varchar(5),
	Day_21 varchar(5),Day_22 varchar(5),	Day_23 varchar(5),	Day_24 varchar(5),	Day_25 varchar(5),	Day_26 varchar(5),	Day_27 varchar(5),	Day_28 varchar(5),	Day_29 varchar(5),	Day_30 varchar(5),
	Day_31 varchar(5)
)

现在我的设计只包含文件上传控件和一个按钮将excel的数据提交到SQL服务器表



我的代码上传数据如下

Now My Design contains file upload control only and one button to submit the excel's data to SQL server table

My code to upload data is as follows

string fname = FileUpload1.FileName.ToString();
       string fpath = "~/temp/" + fname;
       FileUpload1.SaveAs(Server.MapPath(fpath));
       con.Open();
       string qry = "insert into temp(fname,fpath) values('" + fname + "','" + fpath + "')";
       cmd = new SqlCommand(qry, con);
       cmd.ExecuteNonQuery();
       con.Close();


               try
               {
                   string path = string.Concat(Server.MapPath("~/temp/" + FileUpload1.FileName));
                   FileUpload1.SaveAs(path);

                   // Connection String to Excel Workbook
                   string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                   OleDbConnection connection = new OleDbConnection();
                   connection.ConnectionString = excelConnectionString;
                   OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                   connection.Open();
                   // Create DbDataReader to Data Worksheet
                   DbDataReader dr = command.ExecuteReader();

                   // SQL Server Connection String
                   string sqlConnectionString = @"Data Source=ITBITSDT003-PC;Initial Catalog=prajakta_erp_dhanshree;Integrated Security=True";

                  // Bulk Copy to SQL Server
                   SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                   bulkInsert.DestinationTableName = "attendance_sample";
                   bulkInsert.WriteToServer(dr);
                   lbl_msg.Text = "Contents Added Successfully...!";
               }
               catch(Exception ex)
               {
                   lbl_msg.Text = ex.Message;
               }

       }

此代码给出了以下错误

This code gives me the following error

Received an invalid column length from the bcp client for colid 5.

请帮忙我要解决这个错误，并提前感谢大家

Please help me to solve this error and thanks in advance to you all

推荐答案

，连接）;
connection.Open（）;
//创建DbDataReader到数据工作表
DbDataReader dr = command.ExecuteReader（）;

// SQL Server连接字符串
字符串sqlConnectionString = @数据源= ITBITSDT003-PC;初始目录= prajakta_erp_dhanshree; Integrated Security = True;

//批量复制到SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy（sqlConnectionString）;
bulkInsert.DestinationTableName =attendance_sample;
bul kInsert.WriteToServer（DR）;
lbl_msg.Text =内容已成功添加......！;
}
catch（Exception ex）
{
lbl_msg.Text = ex.Message;
}

}

", connection); connection.Open(); // Create DbDataReader to Data Worksheet DbDataReader dr = command.ExecuteReader(); // SQL Server Connection String string sqlConnectionString = @"Data Source=ITBITSDT003-PC;Initial Catalog=prajakta_erp_dhanshree;Integrated Security=True"; // Bulk Copy to SQL Server SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString); bulkInsert.DestinationTableName = "attendance_sample"; bulkInsert.WriteToServer(dr); lbl_msg.Text = "Contents Added Successfully...!"; } catch(Exception ex) { lbl_msg.Text = ex.Message; } }

此代码给出了以下错误

This code gives me the following error

Received an invalid column length from the bcp client for colid 5.

请帮忙我要解决这个错误，并提前感谢大家

Please help me to solve this error and thanks in advance to you all

需要用varchar指定长度，否则需要30作为默认长度。所以只需给出适当的长度，如varchar（Max）或varchar（1000）

Need to specify length with varchar otherwise it takes 30 as default length. So just give proper lenght like varchar(Max) or varchar(1000)

这篇关于如何使用C＃.Net从Excel工作表上载员工出勤到SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！