ASP.NET中如何用程序将excel中的数据导入到数据库中（C#）

Question

大致原理：先将EXCEL导入GridView,然后再操作GridView中的数据存入数据库相应表中！
在这里不能给你提供原码了，但你可以在Google内收到很多！

Answer 1

用的是vs.net2003啊，应该如何？

Answer 2

以下是我的一个真实的例子,供参考!  
<tr>  
<td align="right"> 选择附件：  </td>  
<td>  
<INPUT id="File1" type="file" size="15" name="File1" runat="server">  <FONT face="宋体"> &nbsp;  </FONT>  
<asp:button id="btnReadData" runat="server" Width="55px" Text="读取">  </asp:button>  <FONT face="宋体"> &nbsp;  </FONT>  <asp:button id="btnSave" runat="server" Width="59px" Text="导入">  </asp:button>  </td>  
</tr>  
<tr>  
<td align="center" valign="top">  <asp:datagrid id="dgExportProject" runat="server" Width="95%" BorderWidth="1px" BackColor="White"  
BorderColor="#CC9966" BorderStyle="None" CellPadding="4">  
<FooterStyle ForeColor="#330099" BackColor="#FFFFCC">  </FooterStyle>  
<SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66">  </SelectedItemStyle>  
<ItemStyle ForeColor="#330099" BackColor="White">  </ItemStyle>  
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000">  </HeaderStyle>  
<PagerStyle HorizontalAlign="Center" ForeColor="#330099" BackColor="#FFFFCC">  </PagerStyle>  
</asp:datagrid>  </td>  
</tr>  private void btnReadData_Click(object sender, System.EventArgs e)  
{  
// 获取Excep文件的完整路径  
string fileName = System.IO.Path.GetFileName(File1.PostedFile.FileName);   
string filePath = "";  
if(this.File1.Value == "")  
{  
Comm.Jscript.Alert("请先选择您要导入的文件！");  
}  
else  
{  
int index = fileName.LastIndexOf(".");  
if(index >  0)  
{  
if(fileName.Substring(index) == ".xls")  
{  
DateTime now = DateTime.Now;   
fileName = now.ToShortDateString() + now.ToLongTimeString();   
fileName = fileName.Replace("-","").Replace(":","").Replace(" ","");  
filePath = @"../uploads/" + fileName + ".xls";  
this.File1.PostedFile.SaveAs(Server.MapPath(filePath));  }  
else  
{  
Comm.Jscript.Alert("读入的文件不是XLS");  
}  
}  
}  
if(filePath != "")  
{  
fileName = Request.MapPath(filePath);  string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";  
string query = "SELECT * FROM [Sheet1$]";  
OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(strConn));  
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);  
DataSet myDataSet = new DataSet();  // 将 Excel 的[Sheet1]表内容填充到 DataSet 对象  
try  
{  
oleAdapter.Fill(myDataSet,"[Sheet1$]");  
// 数据绑定  
this.dgExportProject.DataSource = myDataSet;  
this.dgExportProject.DataMember = "[Sheet1$]";  
this.dgExportProject.DataBind();  this.dgExportProject.Visible = true;  
this.btnSave.Visible = true;  
}  
catch(Exception exx)  
{  
Response.Write(exx.Message);  
Comm.Jscript.Alert("注意：请用默认的Sheet1$页名称！");  
}  
finally  
{  
if(File.Exists(filePath))  
{  
File.Delete(filePath);  
}  
}  
}  
}  private void btnSave_Click(object sender, System.EventArgs e)  
{  
//dgExportProject取出所有数据，保存到项目/客户数据库中  
if(this.ddlProjectType.SelectedIndex   < 1)  
{  
Comm.Jscript.Alert("请选择项目类型!");  
return;  
}  
else  
{  
string projectId = String.Empty;  
string projectName = String.Empty;  
string toucher = String.Empty;  
string tel = String.Empty;  
string email = String.Empty;  
string manager = String.Empty;  
string area = String.Empty;  
int projectTypeId = int.Parse(this.ddlProjectType.SelectedItem.Value);  
dccsData.ProjectInfo project = new dccsData.ProjectInfo();  
using(SqlConnection sqlConn = new SqlConnection(dccsData.Config.StrConn))  
{  
try  
{  
if(sqlConn.State == ConnectionState.Closed)  
{  
sqlConn.Open();  
}  foreach(DataGridItem item in this.dgExportProject.Items)  
{  
if(item.Cells.Count == 7)  
{  
projectId = item.Cells[0].Text.Trim().Replace("&nbsp;"," ");  
projectName = item.Cells[1].Text.Trim().Replace("&nbsp;"," ");  
toucher = item.Cells[2].Text.Trim().Replace("&nbsp;"," ");  
tel = item.Cells[3].Text.Trim().Replace("&nbsp;"," ");  
email = item.Cells[4].Text.Trim().Replace("&nbsp;"," ");  
manager = item.Cells[5].Text.Trim().Replace("&nbsp;"," ");  
area = item.Cells[6].Text.Trim().Replace("&nbsp;"," ");  
//将上述值写入到数据库  
if(projectId != "&nbsp;" && projectId != String.Empty)  
{  
project.Insert(projectId,projectName,toucher,tel,email,projectTypeId,manager,area,sqlConn);  
}  
}  
else  
{  
Comm.Jscript.Alert("注意：请按照模板格式导入项目信息！");  
return;  
}  
}  }  
catch(Exception exx)  
{  
Response.Write(exx.Message);  
}  
finally   
{  
if(sqlConn.State == ConnectionState.Open)  
{  
sqlConn.Close();  
}  
}  
}  this.dgExportProject.Visible = false;  
this.btnSave.Visible = false;  
}  
}  
  
  
思路是先把 excel文件传到服务器再读,读完删除 
见http://topic.csdn.net/u/20071224/09/0527e1b5-7c2c-4ee0-97d1-961f8c8c2cf4.html 

Answer 3

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Threading;
using System.IO;using Model;
using BLL;
namespace DbTools
{
    internal class DbToolFunc
    {        
        public delegate void ErrorHandler(string errorMessage);
        public event ErrorHandler errorMessage;
        public delegate void DataCompletedHandler(DataSet data);
        public event DataCompletedHandler dataCompleted;
        public delegate void ImportDataCompletingHandler(string infoMessage);
        public event ImportDataCompletingHandler importDataCompling;
        public delegate void ImportDataCompletedHandler(string infoMessage);
        public event ImportDataCompletedHandler importDataCompled;        private Thread _threadShow;
        private Thread _threadImport;        public DbToolFunc() { }        public DbToolFunc(string xlsPathName)
        {
            _xlsPathName = xlsPathName;
        }               private string _xlsPathName = null;
        public string xlsFileName
        {
            set { this._xlsPathName = value; }
        }        private DataSet _data = null;
        public DataSet ImportData
        {
            set { this._data = value; }
        }        private DataSet _errordata = null;
        public DataSet ErrorData
        {
            get { return this._errordata; }
        }        private void GetDataProc()
        {
            OleDbConnection objConn = null;
            try
            {
                DataSet ds = new DataSet();
                if (String.IsNullOrEmpty(_xlsPathName))
                {
                    if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件名没有指定!"); }
                    return;
                }
                if (!File.Exists(_xlsPathName))
                {
                    if (this.errorMessage != null) { if (this.errorMessage != null) this.errorMessage("文件不存在!"); }
                    return;
                }
                string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source="
                    + _xlsPathName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";                objConn = new OleDbConnection(strConn);
                objConn.Open();
                DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                OleDbDataAdapter sqlada = new OleDbDataAdapter();
                foreach (DataRow dr in schemaTable.Rows)
                {
                    string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
                    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                    sqlada.SelectCommand = objCmd;
                    sqlada.Fill(ds, dr[2].ToString().Trim());
                }
                objConn.Close();
                if (this.dataCompleted != null) this.dataCompleted(ds);
            }
            catch (Exception ex)
            {
                objConn.Close();
                if (this.errorMessage != null) this.errorMessage(ex.Message);
            }
        }        public void GetDataSetFromExcel()
        {
            if (this._threadShow != null && this._threadShow.ThreadState == ThreadState.Running)
            {
                return;
            }
            this._threadShow = new Thread(new ThreadStart(GetDataProc));            this._threadShow.Start();
        }        private void ImportDataProc()
        {
            try
            {
                if (_data == null) { if (this.errorMessage != null) this.errorMessage("没有数据要导入!"); }                /*Area,Adscription,Province,Attribute,IMEINo,PackingCode
                 * ,CustomerID,ClassCode,ModelCode,ColorCode,Quantity,CreateTime*/
                DataTable tb = _data.Tables[0];
                string Area = tb.Rows[0][0].ToString();
                string Adscription = tb.Rows[0][1].ToString();
                string Province = tb.Rows[0][2].ToString();
                string Attribute = tb.Rows[0][3].ToString();
                string CustomerID = tb.Rows[0][9].ToString();
                string ClassCode = tb.Rows[0][6].ToString();
                string ModelCode = tb.Rows[0][7].ToString();
                string ColorCode = tb.Rows[0][8].ToString();
                int Quantity = Convert.ToInt32(tb.Rows[0][10]);
                DateTime CreateTime = Convert.ToDateTime(tb.Rows[0][11]);                ptShipMent bll = new ptShipMent();
                string Code = bll.BuildptShipMentNo(Area, Province, Adscription, Attribute);
                if (importDataCompling != null) importDataCompling("得到出货单号:" + Code);                ptShipMentInfo master = new ptShipMentInfo();
                master.CODE = Code;
                master.AREA = Area;
                master.ADSCRIPTION = Adscription;
                master.ATTRIBUTE = Attribute;
                master.PROVINCE = Province;
                master.MODELCODE = ModelCode;
                master.CLASSCODE = ClassCode;
                master.COLORCODE = ColorCode;
                master.CUSTOMERID = CustomerID;
                master.QUANTITY = Quantity;
                master.CREATETIME = CreateTime;
                master.CREATEUSER = "admin";
                master.LASTTIME = CreateTime;
                master.LASTUSER = "admin";
                master.STATUS = "1";
                master.REMARK = "导入";                if (importDataCompling != null) importDataCompling("增加主体数据...");
                List<ptShipmentListInfo> details = new List<ptShipmentListInfo>();
                foreach (DataRow row in tb.Rows)
                {
                    ptShipmentListInfo item = new ptShipmentListInfo();
                    item.CODE = Code;
                    item.IMEINO = row[4].ToString();
                    item.PACKINGCODE = row[5].ToString();
                    details.Add(item);
                    if (importDataCompling != null) importDataCompling("增加明细数据..." + item.IMEINO + " " + item.PACKINGCODE);
                }
                if (importDataCompled != null) importDataCompled("开始导入数据...");
                bll.InsertptShipMentNoDTC(master, details);
                if (importDataCompled != null) importDataCompled("导入成功!");
            }
            catch (Exception ex)
            {
                if (this.errorMessage != null) this.errorMessage(ex.Message);
            }
        }        public void ImportDataToSQLServer()
        {
            if (this._threadImport != null && this._threadImport.ThreadState == ThreadState.Running)
            {
                return;
            }
            this._threadImport = new Thread(new ThreadStart(ImportDataProc));            this._threadImport.Start();
        }
    }
}参考一下吧，自己写的导入类用到了自己的数据，但思路是不变的。