asp.net mvc excel导入

Question

asp.net mvc excel导入sql2005 望高手指点 最好给出源码(详细点)

Answer 1

  public void ImportToServer(string filename, string sql, string tableName)
  {
    // Connection String to Excel Workbook
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";

    using (OleDbConnection con = new OleDbConnection(excelConnectionString))
    {
      OleDbCommand sqlcom = new OleDbCommand(sql + " FROM [Sheet1$]", con); 
      con.Open();

      // Create DbDataReader to Data Worksheet
      using (DbDataReader dr = sqlcom.ExecuteReader())
      {
        string M_str_sqlcon = "Data Source=(local);Database=DB;user Id=sa;PWD=";

        //Bulk Copy to SQL Server 
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(M_str_sqlcon))
        {
          bulkCopy.DestinationTableName = tableName; 
          bulkCopy.WriteToServer(dr);
        }
      }
    }
  }

  protected void btnImport_Click(object sender, EventArgs e)
  {
    string insert_sql = "SELECT 编号,姓名,年龄,部门"; //这些字段,是excel文件里的列名称
    string filename = File1.PostedFile.FileName;
    string tablename = "你的目标表名称";
    try
    {
      this.ImportToServer(filename, insert_sql, tablename);
      Response.Write("<script>alert('导入成功');</script>");
    }
    catch(Exception ex)
    {
      Response.Write("<script>alert('导入失败');</script>");
    }
  }

---

If this is helpful
  { Please Mark as Answered }
else if it is not helpful
  { Un-Mark as Answered }

Best Regards
Huan Li, Code Blog: Small is New Big In C#

Answer 2

public DataTable GetTableFromExcel(string sheetName, string filePath)
        {
            const string connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
            DataTable dt = null;
            if (!System.IO.File.Exists(filePath))
            {
                // don't find file
                return null;
            }
            OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
            try
            {
                conn.Open();
                if (sheetName == null || sheetName.Trim().Length == 0)
                {
                    DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                }
                else
                {
                    sheetName += "$";
                }

                string strSQL = "Select * From [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return dt;
        }

sheetName：excel中单个sheet的名称；

filePath：excel文件物理路径；

Answer 3

用的是asp.net mvc做的啊 控制器里该杂写呢???