Question

我用C#在visual studio2008平台上写代码，实现从excel把数据导入到SQL数据库，我真的不知道怎么写下去了

protected void btnUpload_Click(object sender, EventArgs e)
{
string _filepath = System.IO.Path.GetFullPath(this.fuplFile.PostedFile.FileName);
string _sql = "SELECT netno,desc_,rec,rdate,modify,mdate,date,no,amount,remark,code,type,id,dept,chtype,curr,currrate,location,svtype,ref_,user_,rmbrate FROM [Sheet1$]";

DbDataReader _dr = Common.ExceltoDataReader("disbursement_charge", _filepath, _sql);
//CWCC_ClientDataContext _CWCC_CLIENT = new CWCC_ClientDataContext();

if (_dr != null)
{
（表名） disbursement_charge _disbursement_charge = new disbursement_charge();
while (_dr.Read())
{
_disbursement_charge.netno = _dr["netno"].ToString();
_disbursement_charge.desc_ = _dr["desc_"].ToString();
_disbursement_charge.rec = _dr["rec"].ToString();
_disbursement_charge.rdate = _dr["rdate"].ToString();
_disbursement_charge.modify = _dr["modify"].ToString();
_disbursement_charge.mdate = _dr["mdate"].ToString();
_disbursement_charge.date = _dr["date"].ToString();
_disbursement_charge.no = _dr["no"].ToString();
_disbursement_charge.amount = Convert.ToDecimal(_dr["amount"]);
_disbursement_charge.remark = _dr["remark"].ToString();
_disbursement_charge.code = _dr["code"].ToString();
_disbursement_charge.type = _dr["type"].ToString();
_disbursement_charge.id = Convert.ToInt32(_dr["id"]);
_disbursement_charge.dept = _dr["dept"].ToString();
_disbursement_charge.chtype = _dr["chtype"].ToString();
_disbursement_charge.curr = _dr["curr"].ToString();
_disbursement_charge.currrate = Convert.ToDecimal(_dr["currrate"]);
_disbursement_charge.location = _dr["location"].ToString();
_disbursement_charge.svtype = _dr["svtype"].ToString();
_disbursement_charge.@ref = _dr["ref"].ToString();
_disbursement_charge.user_ = _dr["user_"].ToString();
_disbursement_charge.rmbrate = Convert.ToDecimal(_dr["rmbrate"]);

}
ArrayList list = new ArrayList();
list.Add(_disbursement_charge);
foreach (string d in _disbursement_charge)
{

}我们经理给我的方法是：先把数据从execl中读书来，然后保存到一个list,然后插入到SQL中，其中还要检查表里每个字段的可用性，比如日期不能超过6位数等等。我是新手，根本不懂得编码，希望你能帮帮忙，谢谢谢谢~~~

Answer 1

数据从excel里面读出来如果用DataTab，就没有必要再插入存入List了，
---------------------------------------如下是我在asp.net ,C#上面用到的方法，给你参考
/// <summary>
/// 将excel文件上传到服务器，并返回存放路径和文件名
/// </summary>
/// <returns></returns>
protected String UpLoadFile() {
String onlyFileName = this.FileUpload1.FileName;
String pathFileName = this.FileUpload1.PostedFile.FileName;
int length = this.FileUpload1.PostedFile.ContentLength;
String fileType = this.FileUpload1.PostedFile.ContentType;
if (length == 0) {
Response.Write("<script>alert('不是有效的Excel文件');</script>");
return ""; }
//上传EXCEL文件到ExcelFile目录
string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string filePath = Request.PhysicalApplicationPath + "Reports\\Template\\TempFile\\" + NewFileName;
this.FileUpload1.SaveAs(filePath);
return filePath; }
/// <summary>
/// //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表；
/// </summary>
/// <param name="filepath"></param>
/// <param name="sheetname"></param>
/// <returns></returns>
protected DataSet ExcelDataSource(string filepath, string sheetname) {
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
return ds; }
/// <summary>
/// 获得Excel中的所有sheetname。
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
protected ArrayList ExcelSheetName(string filepath) {
ArrayList al = new ArrayList();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close();
foreach (DataRow dr in sheetNames.Rows) {
al.Add(dr[2]); }
return al; }
--------------------------------------------------
private void ImportData() {
//从excel中得到的数据 DataTable or DataSet
DataSet ds = ExcelDataSource(filepath, arr[0].ToString());
SqlConnection cn = new SqlConnection(Function.ConnectionString);
try {
SqlCommand cmd = new SqlCommand();
if (cn.State != ConnectionState.Open)
{cn.Open(); }
cmd.Connection = cn;
cmd.CommandTimeout = 900;
int inum = 0;
foreach (DataRow dr in ds.Tables[0].Rows) {
cmd.CommandText = "";
cmd.Parameters.Clear();
cmd.CommandText = "insert into mytable(a,b,c) values(@a,@b,@c)"; cmd.Parameters.AddWithValue("@a",dr["a"]); cmd.Parameters.AddWithValue("@b",dr["b"]); cmd.Parameters.AddWithValue("@c",dr["c"]);
try {
inum += cmd.ExecuteNonQuery(); }
catch (Exception err) { } } }
catch (Exception err) { throw err; }
finally {
if (cn.State != ConnectionState.Closed)
cn.Close(); } }

Answer 2

你能读取execl读取出来放入dataset 里面，不过在读取每行数据的时候 if判断下数据的完整性就好咯 然后直接用dataset 导入到数据库就好了啊，

Answer 3

foreach (string d in _disbursement_charge)
{ }
这句话就不对了吧 _disbursement_charge 这个是实体

追问

那到底要怎么写呢？实现把list插入到数据库的表中。