none
asp.net mvc excel导入 RRS feed

全部回复

  •   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#
    2010年8月9日 1:28
  • 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文件物理路径;

    2010年8月9日 2:59
  • 用的是asp.net mvc做的啊 控制器里该杂写呢???

    2010年8月9日 14:35