Excel Import Error in ASP.net

Question

I am trying to import an excel to database from asp.net website through dataset.

Here is my first part.

 int xlColCount = 1;           
    wb = app.Workbooks.Open(FilePath, 0, false, 5, "", "", true, Ex.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    ws = (Ex.Worksheet)wb.ActiveSheet;            
    xlColCount = ws.Columns.Count;
    xlColCount = ws.UsedRange.Columns.Count;        


    ws.Columns.ClearFormats();

    xlColCount = ws.UsedRange.Columns.Count;

    //creating datacolumns
                for (int i = 0; i < xlColCount; i++)
                {

                    try
                    {
                        DSname.Tables[0].Columns.Add(((Ex.Range)ws.Cells[1,strColArr[i]]).Value2.ToString().Trim());
                    }
                    catch(Exception ex)
                    {
                        //error occured
                    }
                }

First I am creating the column name based on the excel heading into dataset column. here xlColCount=198 (total no.of columns from the excel template and all are filled ) but when reaching 172th column (i=172) it is giving index out of range error.

What could be the reason? I need to create a dataset with column names from all excel column names.

Answer 1

Check your strColArr[i] array capacity..may be initialized with 172 or less in size.

Answer 2

string filePath = "":

        OleDbCommand cmd = new OleDbCommand(); ;
        OleDbDataAdapter oleda = new OleDbDataAdapter();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        UserBase loginUser = (UserBase)Session["LoggedUser"];
        SearchFilter filter = new SearchFilter();
        string action = "ExportDocumentType";
        filter.DocumentTypeID = Convert.ToInt32(cmbDocumentType.SelectedValue);
        filter.DepartmentID = Convert.ToInt32(cmbDepartment.SelectedValue);
        try
        {
            Logger.Trace("Started Extracting Soft Data", Session["LoggedUserId"].ToString());
            // need to pass relative path after deploying on server
            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            filePath + ";Extended Properties='Excel 12.0;';");
            try
            {
                oledbConn.Open();
            }
            catch
            {
                string con = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;HDR=Yes;IMEX=1";
                oledbConn = new OleDbConnection(con);
                oledbConn.Open();
            }

            // Get the data table containg the schema guid.
            dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                throw new Exception(" No sheets available!");
            }

            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++;
            }

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            // Get column names of selected document type
            string SelectCommand = getIndexFieldsList();
            SelectCommand = "SELECT " + SelectCommand + " FROM [" + excelSheets[0] + "]";
            cmd.CommandText = SelectCommand;
            oleda = new OleDbDataAdapter(cmd);
            try
            {
                oleda.Fill(ds);
            }
            catch
            {
                throw new Exception("Selected file is not matching to " + cmbDocumentType.SelectedItem.Text + ".");//Bug Wrtier DMS ENHSMT 1-1012 M
            }



            string strXml = string.Empty;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
               // note: Do ur code here.. i prefer to create a insert satement from here using looping it out
            }

            else
            {
                throw new Exception(" No data available in uploaded file!");//Bug Wrtier DMS ENHSMT 1-1012 M
            }

        }
        catch (Exception ex)
        {
            Logger.Trace("Exception:" + ex.Message, Session["LoggedUserId"].ToString());
            throw new Exception(ex.Message.ToString());


        }
        finally
        {
            // Clean up.
            if (oledbConn != null)
            {
                oledbConn.Close();
                oledbConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
            if (ds != null)
            {
                ds.Dispose();
            }
        }
    }

Answer 3

May be you will have better results (and performance) with ADO.NET and System.Data.OleDb :

string filePath = @"C:\Workbook1.xls"; 
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0\";"; 
OleDbConnection connection = new OleDbConnection(connectionString); 
string cmdText = "SELECT * FROM [Sheet1$]"; 
OleDbCommand command = new OleDbCommand(cmdText, connection); 

command.Connection.Open(); 
OleDbDataReader reader = command.ExecuteReader(); 

if (reader.HasRows) 
{ 
    while (reader.Read()) 
    { 
        Console.WriteLine("{0}\t{1}", reader[0].ToString(), reader[1].ToString()); 
    } 
}