将Excel中的数据插入数据库

本文关键字：插入 数据库 数据 Excel | 更新日期: 2023-09-27 18:28:26

我有一张excel表，我想要的是将excel表记录上传到数据库表中。但在

  1. Emp_Code是身份

  2. Qns是身份

我试过了，

protected void btnUpload_Click(object sender, EventArgs e)
{
    DataTable dtExcel = new DataTable();
    dtExcel.Clear();
    string StrCount = String.Empty;
    string connString = "";
    HttpPostedFile File = FileUpload1.PostedFile;
    string strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
    string path = FileUpload1.PostedFile.FileName;
    string Filename = path.Substring(path.LastIndexOf("''") + 1, path.Length - path.LastIndexOf("''") - 1);
    path = Server.MapPath(@"~/Excels/" + "/" + Filename.ToString());
    File.SaveAs(path);
    if (strFileType.Trim() == ".xls")
    {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='"Excel 8.0;HDR=Yes;IMEX=2'"";
        // connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='"Excel 12.0;HDR=Yes;IMEX=2'"";
    }
    else if (strFileType.Trim() == ".xlsx")
    {
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='"Excel 12.0;HDR=Yes;IMEX=2'"";
    }
    string query = "SELECT * FROM [Sheet 1$]";
    OleDbConnection conn = new OleDbConnection(connString);
    conn.Close();
    if (conn.State == System.Data.ConnectionState.Closed)
        conn.Open();
    OleDbCommand cmd = new OleDbCommand(query, conn);
    OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
    StringBuilder StrPubBldg = new System.Text.StringBuilder();
    XmlWriter xw = XmlWriter.Create(StrPubBldg);
    string ExcelfileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    xw.WriteStartElement("DocumentElement");
    {
        xw.WriteStartElement("Emp_Eval_Proc_hdr");
        xw.WriteElementString("mkey", (0 + 1).ToString());
        xw.WriteElementString("Emp_Code", dtExcel.Rows[1][("Emp Code")].ToString());  //  Identity
        xw.WriteElementString("Emp_Name", dtExcel.Rows[2][("Emp Name")].ToString());
        xw.WriteElementString("Qns_No", dtExcel.Rows[3][("Qns No")].ToString()); //  Identity
        xw.WriteElementString("Self", dtExcel.Rows[0][("Self")].ToString());
        xw.WriteElementString("AS1", dtExcel.Rows[0][("AS1")].ToString());
  }

但作为出现错误

位置1没有行。

怎么做？？

将Excel中的数据插入数据库

dtExcel，您创建了一个新的DataTable，然后立即清除它，然后尝试访问数据。你应该遵循这个概念模型。它不一定要精确，原则是将数据加载到最小的容器中，这是一个DataTable，检查是否有行，然后根据需要进行迭代。

删除了第一个代码样本，根据海报的要求更换为新的代码样本

using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Xml;
namespace Example
{
    /// <summary>
    /// Create instance of this class,
    /// pass in file name and if excel sheet has headers e.g. YES or NO
    /// execute Work method, is currently incomplete
    /// </summary>
    public class StackOverFlowDemo
    {
        private string CreateConnectionString(string FileName, string Header)
        {
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
            if (System.IO.Path.GetExtension(FileName).ToUpper() == ".XLS")
            {
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX=2;HDR={0};", Header));
            }
            else
            {
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX=2;HDR={0};", Header));
            }
            Builder.DataSource = FileName;
            return Builder.ConnectionString;
        }
        public string ConnectionString { get; set; }
        public StackOverFlowDemo(string FileName, string Header)
        {
            ExcelTable = new DataTable();
            CreateConnectionString(FileName, Header);
            LoadData();
        }
        public DataTable ExcelTable { get; set; }
        public void LoadData()
        {
            using (OleDbConnection cn = new OleDbConnection { ConnectionString = ConnectionString })
            {
                cn.Open();
                using (OleDbCommand cmd = new OleDbCommand { CommandText = "SELECT * FROM [Sheet 1$]", Connection = cn })
                {
                    OleDbDataReader dr = cmd.ExecuteReader();
                    ExcelTable.Load(dr);
                }
            }
        }
        public void Work()
        {
            StringBuilder StrPubBldg = new System.Text.StringBuilder();
            XmlWriter xw = XmlWriter.Create(StrPubBldg);
            // continue logic
        }
    }
}
相关文章：