locked
Import and scan excel file RRS feed

  • Question

  • User-419423739 posted

    Hi,
    what's the best method to import an excel file and scan it sheet by sheet to find some values.
    I'm trying to use interop excel but I don't know if it's the fast and best method. 


    Tuesday, November 30, 2010 2:47 AM

All replies

  • User1710623990 posted

    This might help

    http://dotnetperls.com/excel-interop


    Tuesday, November 30, 2010 6:48 AM
  • User-481631678 posted

    Excel introp is not the best method when you are developing server application and multiple users will be uploading files to your server. Here are some good samples which will give you a quick start:-


    http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/ef11a193-54f3-407b-9374-9f5770fd9fd7 http://www.dreamincode.net/code/snippet1413.htm

    Wednesday, December 1, 2010 1:51 AM
  • User-419423739 posted

    tnx to PassHours and Sirdeo.
    I know http://dotnetperls.com/excel-interop, I start from this for my actually code.
    Maybe I don't understand the links of Sirdeo. Are they about interop, right ?

    Then, if I understand, Interop is the best way to import and scan excel also for you

    Wednesday, December 1, 2010 4:51 AM
  • User-481631678 posted

    Interop is not the best way for the purpose, but your reqirements are quite different, your data is not in a tabular format and you need to access data by cell index.


    If you are implementing server side solution then interop is not recommended.  If you are implementing it on server side then you can use EP Plus component. But it only supports .xlsx format like this:-


    http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx

    Wednesday, December 1, 2010 9:31 AM
  • User-419423739 posted

    unfortunately I must use interop because I need to upload xls and scan, sheet by sheet and cell by cell, to find some values Undecided

    Wednesday, December 1, 2010 10:41 AM
  • User-419423739 posted

    another question about this process..
    when I start the import and scan everything works but after some minutes it stop without any message...
    the code is into try{} catch{} but only "Internet explor cannot display the webpage".
    Where I can find the real reason for this ? server's event viewer?

    Wednesday, December 1, 2010 4:09 PM
  • User-1060488027 posted

    Hi,


    I just need the code how u did to scan the excel file sheets empty spaces and wrong data using c#.net desktop application.


    Regards

    Suresh

    sureshmandadi@gmail.com

    Sunday, January 23, 2011 11:38 PM
  • User793200426 posted

    Hi,

    you can easily accomplish this task with this Excel .NET library.

    Here is an Excel C# code:

    private static IEnumerable<ExcelCell> FindAllCells()
    {
        ExcelFile ef = new ExcelFile();
    
        ef.LoadXls("MySpreadsheet.xls");
    
        foreach (ExcelWorksheet ws in ef.Worksheets)
            foreach (ExcelCell cell in ws.GetUsedCellRange())
                if (object.Equals(cell.Value, "value I am looking for"))
                    yield return cell;
    }

    Tuesday, March 29, 2011 5:18 AM
  • User1867929564 posted

    why don't you export all the record to dataset and find the value.
    wht is the final requirement/goal ? 

    Thursday, May 19, 2011 6:36 AM
  • User-977583538 posted
    protected void btImport_Click(object sender, EventArgs e)
        {
            string str = FileUpload1.PostedFile.FileName;
    
            string file = System.IO.Path.GetFileName(str);
    
                        OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + str + "; Extended Properties=\"Excel 8.0;HDR=Yes\"");
                DBConnection.Open();
                string SQLString = "SELECT * FROM [Sheet1$]";
                //OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection); 
    
                //OleDbDataReader DBReader = DBCommand.ExecuteReader();
                OleDbDataAdapter adp = new OleDbDataAdapter(SQLString, DBConnection);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();
                //DBReader.Close(); 
                DBConnection.Close();
    
                foreach (DataRow dRow in ds.Tables[0].Rows)
                {
                    string col1 = dRow[0].ToString();
                    string col2 = dRow[1].ToString();
                    string col3 = dRow[2].ToString();
                    string col4 = dRow[3].ToString();
                    string col5 = dRow[4].ToString();
    
                   
                    // SqlConnection conn = new SqlConnection(Common.GetConnectionString("SqlConnectionString"));
                    conn.Open();
                    SqlCommand comm = new SqlCommand("INSERT INTO table(Column1,Column2,Column3,Column4,Column5) values ('" + col1+ "','" +
                        col2 + "','" + col3 + "','" + col4 + "','" + col5 + "')", conn);
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
            else
            {
            }
        }

    The above code is used to import records from excel, export into sql server database and diplay into datagrid.

    Thursday, May 19, 2011 7:09 AM