我有以下代码供Excel阅读 [英] I have following code for excel reading

问题描述

OleDbConnection conn = new OleDbConnection();
    OleDbCommand cmd = new OleDbCommand();
 
    OleDbDataAdapter da = new OleDbDataAdapter();
    DataSet ds = new DataSet();
    System.Data.DataTable dt = null;
    //string query = null;
    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""";
   
    protected void Page_Load(object sender, EventArgs e)
    {
        //ConfigurationSettings.AppSettings[FileExtension];
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string validFileExtensions = Config.Get("ValidFileExtension");


        int uploadSize = Convert.ToInt32( Config.Get("DocUploadValidationSize"));
        string validationMEssage = Config.Get("DocUploadValidationMessage");

        connectionString = string.Format(connectionString, @"d:\R and D\Book1.xlsx");
          if (FileUpload1.HasFile)
          {
            string fileExt =
               System.IO.Path.GetExtension(FileUpload1.FileName);

            if (validFileExtensions.IndexOf(fileExt)>=0)
            {
                try
                {

                  if (FileUpload1.PostedFile.ContentLength >= uploadSize)
                    {

                        Literal1.Text = validationMEssage;
                        return;
                    }
        
                        FileUpload1.SaveAs(@"C:\Users\user5\Desktop" +
                        FileUpload1.FileName);
                        Label1.Text = "File name: " +
                        FileUpload1.PostedFile.FileName + "<br>" +
                        FileUpload1.PostedFile.ContentLength + " kb<br>" +
                        "Content type: " +
                        FileUpload1.PostedFile.ContentType;


                        using (OleDbConnection conn = new OleDbConnection(connectionString))
                        { 
                            conn.Open();

                            OleDbCommand cmd = conn.CreateCommand();
                            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                             
                            

                            String[] excelSheets = new String[dt.Rows.Count];
                            int i = 0;

                            // Add the sheet name to the string array.

                            foreach (DataRow row in dt.Rows)
                            {
                                  excelSheets[i] = row["TABLE_NAME"].ToString();
                                i++;
                            }

                            // Loop through all of the sheets if you want too...

                            for (int j = 1; j < excelSheets.Length; j++)
                            {

                                if (j == 1)
                                {
                                    cmd.CommandText = "SELECT ID,UserName,Designation FROM [" + excelSheets[1] + "]";
                                }
                                
                                else
                                    if (j == 2)
                                    {
                                        cmd.CommandText = "SELECT ID,CITY,STATE FROM [" + excelSheets[2] + "]";
                                    }


                                else
                                        if (j == 3)
                                        {
                                            cmd.CommandText = "SELECT ID,Client,City FROM [" + excelSheets[3] + "]";
                                        }
                                 
                                //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet2$]";
                                //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet3$]";





                                //using (OleDbCommand cmd = conn.CreateCommand())
                                // {


                                using (OleDbDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        Console.WriteLine(reader["WorkSheet1"].ToString());
                                        Console.WriteLine(reader["WorkSheet2"].ToString());
                                        Console.WriteLine(reader["WorkSheet3"].ToString());
                                    }

                                }
                            }
                                Console.Read();
                            //}
                        }


                }

                catch (Exception ex)
                {
                    Label1.Text = "ERROR: " + ex.Message.ToString();
                }
            }
            else
            {
                Label1.Text = "Upload Valid File!";
            }
        }
        else
        {
            Label1.Text = "You have not specified a file.";
        }

 }




}

当我调试代码时，具有用于读取多个标签形式excelsheet的代码，它不会出现在下面的部分中

have this code for reading multiple tag form excelsheet when i debuging code it not goes in following part

while (reader.Read())
 {
 Console.WriteLine(reader["WorkSheet1"].ToString());
 Console.WriteLine(reader["WorkSheet2"].ToString());
 Console.WriteLine(reader["WorkSheet3"].ToString());
 }
 }
 }

并给出以下错误没有为一个或多个必需参数提供值.
如果正确，则上面的代码

and gives following errors No value given for one or more required parameters.
if correct the above code

推荐答案

; // cmd.CommandText =从[WorkSheet3

"; //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet3

; // 使用(OleDbCommand cmd = conn.CreateCommand()) // { 使用(OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader [" ].ToString()); Console.WriteLine(reader [" ].ToString()); Console.WriteLine(reader [" ].ToString()); } } } Console.Read(); // } } } 捕获(例外) { Label1.Text = " + ex.Message.ToString(); } } 其他 { Label1.Text = " ; } } 其他 { Label1.Text = " ; } } }

"; //using (OleDbCommand cmd = conn.CreateCommand()) // { using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["WorkSheet1"].ToString()); Console.WriteLine(reader["WorkSheet2"].ToString()); Console.WriteLine(reader["WorkSheet3"].ToString()); } } } Console.Read(); //} } } catch (Exception ex) { Label1.Text = "ERROR: " + ex.Message.ToString(); } } else { Label1.Text = "Upload Valid File!"; } } else { Label1.Text = "You have not specified a file."; } } }

当我调试代码时，具有用于读取多个标签形式excelsheet的代码，它不会出现在下面的部分中

have this code for reading multiple tag form excelsheet when i debuging code it not goes in following part

while (reader.Read())
 {
 Console.WriteLine(reader["WorkSheet1"].ToString());
 Console.WriteLine(reader["WorkSheet2"].ToString());
 Console.WriteLine(reader["WorkSheet3"].ToString());
 }
 }
 }

并给出以下错误没有为一个或多个必需参数提供值.
如果正确，则上面的代码

and gives following errors No value given for one or more required parameters.
if correct the above code

在您的代码周围放置一个try/catch块，并在调试器下运行它.另外，请尝试以下操作:

Put a try/catch block around your code, and run it under the debugger. Also, try this instead:

Console.WriteLine(reader.GetString(reader["WorkSheet1"]));

这篇关于我有以下代码供Excel阅读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！